修改数据结构
数据表的修改操作:无非就是添加列(ADD),删除列(DROP),添加约束(ADD),删除约束(DROP)。用的是ALTER,而INSERT是对数据表添加插入记录用的。
修改数据表:
- 针对字段:添加/删除字段,修改列定义,修改列名称等
- 针对约束:添加/删除各种约束
- 针对数据表:数据表的更名(两种)
- 添加单列
- ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name]
- 添加多列
- ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition,…)
- 删除列
- ALTER TABLE tbl_name DROP [COLUMN] col_name
- 添加/删除主键约束
- 添加 ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,…)
- 删除 ALTER TABLE tbl_name DROP PRIMARY KEY
- 添加/删除唯一约束
- 添加 ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,…)
- 删除 ALTER TABLE tbl_name DROP {INDEX|KEY} index_name
- 添加外键约束
- 添加ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,…) reference_definition
- 删除 ALTERE TABLE tab_name DROP FOREIGN KEY fk_symbol
- 添加/删除默认约束
- ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
- 添加:ALTER TABLE users2 ALTER age SET DEFAULT 15;
- 删除:ALTER TABLE users2 ALTER age DROP DEFAULT;
- ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
修改列定义和更名数据表
- 修改列定义 MODIFY
- ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST| ATTERE col_name]
- ALTER TABLE users MODIFY id SMALLINT UNSIGNED NOT NULL FIRST
- ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST| ATTERE col_name]
- 修改列名称 CHANGE
- ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST| ATTERE col_name]
- ALTER TABLE users CHANGE id ID SMALLINT UNSIGNED NOT NULL FIRST
- ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST| ATTERE col_name]
- 修改数据表名称 RENAME
- ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name
- RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2]