MYSQL DDL (Data Definition Language)

(1) CREATE DATABASE

範例
CREATE DATABASE dbname;

範例
CREATE DATABASE dbname
CHARACTER SET utf8
COLLATE utf8_general_ci;

(2) CREATE TABLE

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

範例
CREATE TABLE customer (
cus_id int NOT NULL,
cus_name varchar(255) NOT NULL,
cus_address varchar(255),
cus_no char(3),
PRIMARY KEY (cus_id) );

範例
CREATE TABLE A LIKE B;
~ 此種方式在將表復制到A時候會將表B完整的字段結構和索引復制到表A中來。

範例
CREATE TABLE A AS SELECT x,x,x,xx FROM B LIMIT 0;
~ 此種方式只會將表B的字段結構復制到表A中來，但不會復制表B中的索引到表A中來。這種方式比較靈活可以在復制原表表結構的同時指定要復制哪些字段，並且自身復制表也可以根據需要增加字段結構。

如下所示，以LIKE方式去建立TABLE，會有Index。
但是如果以AS SELECT方式去建立TABLE，則以SHOW Index顯示為empty。

範例
CREATE TABLE customer ( 
C_Id INT NOT NULL, 
Name VARCHAR(50) NOT NULL, 
Address VARCHAR(255), 
Phone VARCHAR(20), 
UNIQUE (C_Id) );

範例
CREATE TABLE customer ( 
C_Id INT NOT NULL UNIQUE, 
Name VARCHAR(50) NOT NULL, 
Address VARCHAR(255), 
Phone VARCHAR(20) );

範例
CREATE TABLE product (
category INT NOT NULL,
id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)
) ENGINE=INNODB;

CREATE TABLE customer (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE product_order (
no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
INDEX (customer_id),

FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,

FOREIGN KEY (customer_id)
REFERENCES customer(id)
) ENGINE=INNODB;

更多參考
http://webdesign.kerthis.com/sql/sql_unique
http://www.hmes.kh.edu.tw/~jona/redhat/mysqlphp/mysqlsyntax.htm
http://dev.mysql.com/doc/refman/5.0/en/create-table.html
http://www.w3schools.com/sql/sql_foreignkey.asp

(3) DROP DATABASE

範例
DROP DATABASE dbname;

(4) DROP TABLE

範例
DROP TABLE tblname;

(5) ALTER DATABASE

範例
ALTER DATABASE dbname
CHARACTER SET utf8
COLLATE utf8_general_ci;

(6) ALTER TABLE

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

範例
ALTER TABLE mytable
DROP PRIMARY KEY ;

更多參考
http://www.mysql.tw/2013/03/sql-primary-key-constraint.html
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html

(7) RENAME

範例
RENAME TABLE mytable TO newtable ;

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

範例
TRUNCATE TABLE tblname ;