之前我們已經介紹資料庫(Database)、資料表(Table)、索引(Index)、檢視(View)等物件的創建(CREATE)指令, 在MySQL中還提供了很多資料庫物件的管理指令(Data Definition Statements)
我們把Data Definition Statements分成四類

1. 創建類
   1.1. CREATE DATABASE, 建立資料庫
   1.2. CREATE EVENT, 建立排程, 讓資料庫定期執行某項SQL指令或作業, 例如
   CREATE EVENT myevent
   ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
   DO
   UPDATE myschema.mytable SET mycol = mycol + 1;
   從現在開始, 每小時執行UPDATE指令一次.
   1.3. CREATE FUNCTION, 建立使用者定義函數, 我們日後討論Stored Procedure時再詳細說明
   1.4. CREATE INDEX, 建立索引
   1.5. CREATE LOGFILE GROUP, 建立Logfile的實體儲存結構
   1.6. CREATE PROCEDURE, 建立Stored Procedure
   1.7. CREATE SERVER, 建立一個遠端(非本機)資料庫, 這部份如果有機會再分享
   1.8. CREATE TABLE, 建立資料表
   1.9. CREATE TABLESPACE, 建立表空間, 和Logfile也是一樣, 管理資料庫實體儲存空間的指令, 我會在往後的分享中一起討論如何管理資料庫的實體空間(即硬碟空間管理)
   1.10. CREATE TRIGGER, 建立觸發器, 觸發器和資料表的內容異動有密切關係, 我將在討論Stored Procedure後接著討論Trigger
   1.11. CREATE VIEW, 建立檢視
2. 調整類
   資料庫物件創建後, 因為些因素要調整物件的屬性時, 可以用ALTER
   2.1. ALTER DATABASE
   2.2. ALTER EVENT
   2.3. ALTER LOGFILE GROUP
   2.4. ALTER FUNCTION
   2.5. ALTER PROCEDURE
   2.6. ALTER SERVER
   2.7. ALTER TABLE, 完整語法如下:
   ALTER [ONLINE | OFFLINE] [IGNORE] TABLE tbl_name
   alter_specification [, alter_specification] ...

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_option] ...
| ADD FULLTEXT [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD SPATIAL [INDEX|KEY] [index_name]
(index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| partition_options
| ADD PARTITION (partition_definition)
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION [partition_names INTO (partition_definitions)]
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
| REMOVE PARTITIONING
2.8. ALTER TABLESPACE
2.9. ALTER VIEW, 完整語法如下:
ALTER
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
各位有沒有發現ALTER類少了ALTER INDEX和ALTER TRIGGER兩個指令, MySQL建議Trigger應該要先刪掉再重建, 這在我們詳細討論Trigger時再談.
ALTER INDEX指令已經包含在ALTER TABLE中了, 但我建議也是先刪再重建, 這樣可獲得軟佳的效率
3. 刪除類
刪掉物件的指令, 刪前最好確定物件的相依關係, 例如View中引用的Table最好刪之前先確定View
3.1. DROP DATABASE
3.2. DROP EVENT
3.3. DROP FUNCTION
3.4. DROP INDEX
3.5. DROP LOGFILE GROUP
3.6. DROP PROCEDURE
3.7. DROP SERVER
3.8. DROP TABLE
3.9. DROP TABLESPACE
3.10. DROP TRIGGER
3.31. DROP VIEW
4. 更名類
只有Database和Table可變更名稱, 但更名前要檢查相關的各種物件
5.1. RENAME DATABASE
5.2. RENAME TABLE
我們今天大致瀏覽一下資料定義指令, 明天我們看看有那些資料維護指令(Data Manipulation Statements)