mysql常用文法

--     

CREATE DATABASE DB_NAME;

--     

USE DB_NAME;

--       

SHOW DATABASES;

--     

DROP DATABASE DB_NAME;



--    

CREATE TABLE TABLE_NAME(

ID INT(9) PRIMARY KEY AUTO_INCREMENT,

SALARY FLOAT(10,2),

BIRTHDATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

NAME VARCHAR(50) NOT NULL UNIQUE KEY);

--       

SHOW TABLES;

--     

DROP TABLE TABLE_NAME;

--       

SELECT CONCAT( 'DROP TABLE ', TABLE_NAME, ';' ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'DAB_NTOS_%';



--      

ALTER TABLE TABLE_NAME ADD PRIMARY KEY(ID);

--      

ALTER TABLE TABLE_NAME DROP PRIMARY KEY;

--              

ALTER TABLE TABLE_NAME MODIFY ID INT AUTO_INCREMENT PRIMARY KEY;

--       

ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_NAME_UK UNIQUE KEY(NAME);

--      

ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_NAME_FK FOREIGN KEY(S_ID) REFERENCES TABLE_NAME2 (ID);

--      

ALTER TABLE TABLE_NAME ADD CONSTRAINT TABLE_NAME_NOT_NULL CHECK(COL_NAME IS NOT NULL);



--       

ALTER TABLE TABLE_NAME ALTER COLUMN COL_NAME DROP DEFAULT;

--       

ALTER TABLE TABLE_NAME ALTER COLUMN COL_NAME SET DEFAULT DEFAULT_VAL;