mysql 修改表的文法詳解

標籤：

mysql 修改表的詳細文法介紹

 

ALTER [IGNORE] TABLE tbl_name

    [alter_specification [, alter_specification] ...]

    [partition_options]

 

algorithm_option:

    ALGORITHM [=] {DEFAULT|INPLACE|COPY}

 

lock_option:

    LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

 

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

  | ALGORITHM [=] {DEFAULT|INPLACE|COPY}

  | ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

  | CHANGE [COLUMN] old_col_name new_col_name column_definition

        [FIRST|AFTER col_name]

  | LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

  | 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|AS] new_tbl_name

  | RENAME {INDEX|KEY} old_index_name TO new_index_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

  | FORCE

  | ADD PARTITION (partition_definition)

  | DROP PARTITION partition_names

  | DISCARD PARTITION {partition_names | ALL} TABLESPACE

  | IMPORT PARTITION {partition_names | ALL} TABLESPACE

  | TRUNCATE PARTITION {partition_names | ALL}

  | COALESCE PARTITION number

  | REORGANIZE PARTITION partition_names INTO (partition_definitions)

  | EXCHANGE PARTITION partition_name WITH TABLE tbl_name [{WITH|WITHOUT} VALIDATION]

  | ANALYZE PARTITION {partition_names | ALL}

  | CHECK PARTITION {partition_names | ALL}

  | OPTIMIZE PARTITION {partition_names | ALL}

  | REBUILD PARTITION {partition_names | ALL}

  | REPAIR PARTITION {partition_names | ALL}

  | REMOVE PARTITIONING

 

 

常用的操作的例子：

1、修改列名，列類型，列名： 下面語句將列名為old_column的列名修改成new_column，並且資料類型為int

 

>alter table tablename change old_column new_column int ;

 

2、修改列的資料類型：將column_name 這個列的資料類型修改成為new_datatype 

 

>alter table tablename modify column_name new_datatype ;

 

3、添加欄位

 

>alter table tablename add column_name column_datatype ;

 

4、刪除主鍵

 

>alter table tablename  DROP PRIMARY KEY

 

5、刪除分區

 

>alter table tablename  DROP PARTITION partition_names

 

6、刪除索引

 

>alter table tablename DROP INDEX index_name

 

7、刪除列

 

>alter table tablename DROP  col_name

 

java企業級通用許可權安全架構源碼 SpringMVC mybatis or hibernate+ehcache shiro druid bootstrap HTML5

【java架構源碼下載】

mysql 修改表的文法詳解