SQL数据定义语句语法

内容来自实验楼：MySQL 基础课程 

1. alter table：

   ALTER [IGNORE] TABLE tbl_name
       alter_specification [, alter_specification] ...
   
   alter_specification:
       ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
     | ADD [COLUMN] (column_definition,...)
     | ADD INDEX [index_name] [index_type] (index_col_name,...)
     | ADD [CONSTRAINT [symbol]]
           PRIMARY KEY [index_type] (index_col_name,...)
     | ADD [CONSTRAINT [symbol]]
           UNIQUE [index_name] [index_type] (index_col_name,...)
     | ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
     | 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 column_definition
           [FIRST|AFTER col_name]
     | MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
     | DROP [COLUMN] col_name
     | DROP PRIMARY KEY
     | DROP INDEX index_name
     | DROP FOREIGN KEY fk_symbol
     | DISABLE KEYS
     | ENABLE KEYS
     | RENAME [TO] new_tbl_name
     | ORDER BY col_name
     | CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
     | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
     | DISCARD TABLESPACE
     | IMPORT TABLESPACE
     | table_options
     | 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

    

2. create database：

   CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
       [create_specification [, create_specification] ...]
   
   create_specification:
       [DEFAULT] CHARACTER SET charset_name
     | [DEFAULT] COLLATE collation_name

    

3. create index：

   CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
       [USING index_type]
       ON tbl_name (index_col_name,...)
   
   index_col_name:
       col_name [(length)] [ASC | DESC]
   CREATE INDEX被映射到一个ALTER TABLE语句上，用于创建索引。

   以下语句创建了customer表中name列的前十个字符的索引part_of_name：
   CREATE INDEX part_of_name ON customer (name(10));
   如果对列中的前10个字符进行索引，索引速度应该不会慢于整个name列的索引速度。此外，使用列的前缀建立索引可以使得索引文件更小，这样可以节省大量的磁盘空间，还可以加速INSERT操作。

4. create table：

   CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
       [(create_definition,...)]
       [table_options] [select_statement]
   
   or：
   
   CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
       [(] LIKE old_tbl_name [)];
   
   create_definition:
       column_definition
     | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
     | KEY [index_name] [index_type] (index_col_name,...)
     | INDEX [index_name] [index_type] (index_col_name,...)
     | [CONSTRAINT [symbol]] UNIQUE [INDEX]
           [index_name] [index_type] (index_col_name,...)
     | [FULLTEXT|SPATIAL] [INDEX] [index_name] (index_col_name,...)
     | [CONSTRAINT [symbol]] FOREIGN KEY
           [index_name] (index_col_name,...) [reference_definition]
     | CHECK (expr)
   
   column_definition:
       col_name type [NOT NULL | NULL] [DEFAULT default_value]
           [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
           [COMMENT 'string'] [reference_definition]
   
   type:
       TINYINT[(length)] [UNSIGNED] [ZEROFILL]
     | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
     | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
     | INT[(length)] [UNSIGNED] [ZEROFILL]
     | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
     | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
     | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
     | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
     | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
     | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
     | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
     | DATE
     | TIME
     | TIMESTAMP
     | DATETIME
     | CHAR(length) [BINARY | ASCII | UNICODE]
     | VARCHAR(length) [BINARY]
     | TINYBLOB
     | BLOB
     | MEDIUMBLOB
     | LONGBLOB
     | TINYTEXT [BINARY]
     | TEXT [BINARY]
     | MEDIUMTEXT [BINARY]
     | LONGTEXT [BINARY]
     | ENUM(value1,value2,value3,...)
     | SET(value1,value2,value3,...)
     | spatial_type
   
   index_col_name:
       col_name [(length)] [ASC | DESC]
   
   reference_definition:
       REFERENCES tbl_name [(index_col_name,...)]
                  [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
                  [ON DELETE reference_option]
                  [ON UPDATE reference_option]
   
   reference_option:
       RESTRICT | CASCADE | SET NULL | NO ACTION
   
   table_options: table_option [table_option] ...
   
   table_option:
       {ENGINE|TYPE} = engine_name
     | AUTO_INCREMENT = value
     | AVG_ROW_LENGTH = value
     | [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
     | CHECKSUM = {0 | 1}
     | COMMENT = 'string'
     | CONNECTION = 'connect_string'
     | MAX_ROWS = value
     | MIN_ROWS = value
     | PACK_KEYS = {0 | 1 | DEFAULT}
     | PASSWORD = 'string'
     | DELAY_KEY_WRITE = {0 | 1}
     | ROW_FORMAT = {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
     | UNION = (tbl_name[,tbl_name]...)
     | INSERT_METHOD = { NO | FIRST | LAST }
     | DATA DIRECTORY = 'absolute path to directory'
     | INDEX DIRECTORY = 'absolute path to directory'
   
   partition_options:
       PARTITION BY
              [LINEAR] HASH(expr)
           |  [LINEAR] KEY(column_list)
           |  RANGE(expr)
           |  LIST(column_list)
       [PARTITIONS num]
       [  SUBPARTITION BY
              [LINEAR] HASH(expr)
            | [LINEAR] KEY(column_list)
         [SUBPARTITIONS(num)]
       ]
       [(partition_definition), [(partition_definition)], ...]
   
   partition_definition:
       PARTITION partition_name
           [VALUES {
                     LESS THAN (expr) | MAXVALUE
                   | IN (value_list) }]
           [[STORAGE] ENGINE [=] engine-name]
           [COMMENT [=] 'comment_text' ]
           [DATA DIRECTORY [=] 'data_dir']
           [INDEX DIRECTORY [=] 'index_dir']
           [MAX_ROWS [=] max_number_of_rows]
           [MIN_ROWS [=] min_number_of_rows]
           [TABLESPACE [=] (tablespace_name)]
           [NODEGROUP [=] node_group_id]
           [(subpartition_definition), [(subpartition_definition)], ...]
   
   subpartition_definition:
       SUBPARTITION logical_name
           [[STORAGE] ENGINE [=] engine-name]
           [COMMENT [=] 'comment_text' ]
           [DATA DIRECTORY [=] 'data_dir']
           [INDEX DIRECTORY [=] 'index_dir']
           [MAX_ROWS [=] max_number_of_rows]
           [MIN_ROWS [=] min_number_of_rows]
           [TABLESPACE [=] (tablespace_name)]
           [NODEGROUP [=] node_group_id]
   
   select_statement:
       [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

   复制表（创建一个含有相应列属性和索引相同的另外一个空表）：
   CREATE TABLE new_tbl LIKE orig_tbl;
   完全克隆一个表的内容以及结构：
   CREATE TABLE new_tbl SELECT * FROM orig_tbl;

5. DROP DATABASE：DROP {DATABASE | SCHEMA} [IF EXISTS] db_name

6. DROP INDEX：DROP INDEX index_name ON tbl_name

7. DROP TABLE：DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE]

8. RENAME TABLE：RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...