SQL commands of DDL (Data Definition Language)

DDL (Data Definition Language) 的SQL commands有:

(1)CREATE - to create objects in the database 

建立資料庫(database)

語法:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...

create_specification:
[DEFAULT] CHARACTER SET [=] charset_name |
[DEFAULT] COLLATE [=] collation_name

更多參考: http://dev.mysql.com/doc/refman/5.0/en/create-database.html

例如:
CREATE DATABASE mydatabase;

或是
CREATE DATABASE mydatabase
CHARACTER SET big5
COLLATE big5_chinese_ci ;

或是
CREATE DATABASE mydatabase
CHARACTER SET utf8
COLLATE utf8_chinese_ci ;

關於character set與collate，請參考
http://www.mysql.tw/2013/03/mysql-character-set-collation.html

建立表單(table)

語法:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]

使用temporary的意思是指建立暫時的表單，單connection結束自動刪除。

更多參考: http://dev.mysql.com/doc/refman/5.0/en/create-table.html

例如:
CREATE TABLE mytable
(field1 CHAR(10), field2 INT(10)) ;


(2) ALTER - alters the structure of the database 

語法:
ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
[alter_specification [, alter_specification] ...]
[partition_options]

更多參考: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

例如:
ALTER TABLE mytable
ADD PRIMARY KEY (`field1`) ;

ALTER TABLE table_name
ADD field_name field_type;

ALTER TABLE table_name
CHANGE old_field_name new_field_name field_type;

ALTER TABLE table_name
DROP field_name;

ALTER table tb_name
MODIFY id int auto_increment primary key;

更多參考:
http://fecbob.pixnet.net/blog/post/43281340-alter%E8%AA%9E%E5%8F%A5%E5%9C%A8mysql%E8%B3%87%E6%96%99%E5%BA%AB%E4%B8%8B%E7%9A%84%E7%94%A8%E6%B3%95
https://dev.mysql.com/doc/refman/5.7/en/alter-database.html
https://dev.mysql.com/doc/refman/5.7/en/alter-table.html

(3) DROP - delete objects from the database 

語法:
DROP [TEMPORARY] TABLE [IF EXISTS]
tbl_name [, tbl_name] ...
[RESTRICT | CASCADE]

更多參考: http://dev.mysql.com/doc/refman/5.5/en/drop-table.html

例如:
DROP TABLE IF EXISTS mytable1 , mytable2 ;

(4) TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed

語法:
TRUNCATE [TABLE] tbl_name

更多參考: http://dev.mysql.com/doc/refman/5.0/en/truncate-table.html

例如:
TRUNCATE TABLE mytable ;

(5) RENAME - rename an object

語法:
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...

更多參考: http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

例如:
RENAME TABLE mytable TO newtable ;