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 ;
0 留言