MY SQL

常见的DDL语句:

CREATEDATABASE

创建数据库:

CREATE{DATABASE|SCHEMA}db_name

[create_specification[,create_specification]...]

create_specification:

[DEFAULT]CHARACTERSETcharset_name

|[DEFAULT]COLLATEcollation_name

CREATETABLE

创建数据库表格:

CREATE[TEMPORARY]TABLEtbl_name

[(create_definition,...)]

[table_options][select_statement]

ALTERTABLE

修改数据库表格:

ALTERTABLEtbl_name

alter_specification[,alter_specification]...

alter_specification:

ADD[COLUMN]column_definition[FIRST|AFTERcol_name]

|ADD[COLUMN](column_definition,...)|ADDINDEX(index_col_name,...)

|ADD[CONSTRAINT[symbol]]

PRIMARYKEY(index_col_name,...)

|ADD[CONSTRAINT[symbol]]

UNIQUE(index_col_name,...)

|ADD(index_col_name,...)

|ADD[CONSTRAINT[symbol]]

FOREIGNKEY(index_col_name,...)

[reference_definition]

|ALTER[COLUMN]col_name{SETDEFAULTliteral|DROPDEFAULT}

|CHANGE[COLUMN]old_col_namecolumn_definition

[FIRST|AFTERcol_name]

|MODIFY[COLUMN]column_definition[FIRST|AFTERcol_name]

|DROP[COLUMN]col_name

|DROPPRIMARYKEY|DROPINDEXindex_name

|DROPFOREIGNKEYfk_symbol|DISABLEKEYS

|ENABLEKEYS|RENAME[TO]new_tbl_name

|ORDERBYcol_name

|CONVERTTOCHARACTERSETcharset_name[COLLATEcollation_name]

|[DEFAULT]CHARACTERSETcharset_name[COLLATEcollation_name]

|DISCARDTABLESPACE

|IMPORTTABLESPACE

|table_optionsDROPTABLE

删除数据库表格：

DROP[TEMPORARY]TABLE

tbl_name[,tbl_name]...

[RESTRICT|CASCADE]

CREATEVIEW

创建查询命令：

CREATE[ORREPLACE[ALGORITHM{UNDEFINED|MERGE|TEMPTABLE}]

VIEWview_name[(column_list)]

ASselect_statement

[WITH[CASCADED|LOCAL]CHECKOPTION]

ALTERVIEW

修改查询命令：

ALTER[ALGORITHM{UNDEFINED|MERGE|TEMPTABLE}]

VIEWview_name[(column_list)]

ASselect_statement

[WITH[CASCADED|LOCAL]CHECKOPTION]

DROPVIEW

删除查询命令：

DROPVIE

Wview_name[,view_name]...

[RESTRICT|CASCADE]

TRUNCATETABLE

删除数据表内容：

TRUNCATETABLEname[DROP/REUSESTORAGE]

DROPSTORAGE：显式指明释放数据表和索引的空间

REUSESTORAGE：显式指明不释放数据表和索引的空间