黑马程序员技术交流社区
标题: 修改数据表的sql语句 [打印本页]
作者: sishuidliunian 时间: 2016-6-1 23:18
标题: 修改数据表的sql语句
修改数据表:[]中内容可省略的,tbl_name表名,col_name列名,column_definition 属性
添加单列:ALTER TABLEtbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];
添加多列:ALTER TABLEtbl_name ADD [COLUMN] (col_name column_definition,...);
删除列: ALTER TABLE tbl_name DROP [COLUMN] col_name;
添加主键:ALTER TABLEtbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name,...);
删除主键:ALTER TABLEtbl_name DROP PRIMARY KEY;
添加唯一约束:ALTER TABLEtbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY][index_name][index_type](index_col_name,...)
删除唯一约束:ALTERTABLE tbl_name DROP {INDEX | KEY} index_name;
添加外键约束:ALTERTABLE tbl_name ADD[CONSTRAINT[symbol]]FOREIGN KEY [index_name](index_col_name,...) reference_definition;
删除外键约束:ALTERTABLE tbl_name DROP FOREIGN KEY fk_symbol;
注:fk_symbol为外键约束名称,通过查看数据表信息获得
添加/删除默认约束:ALTERTABLE tbl_name ALTER [COLUMN] col_name {SETDEFAULT literal |DROP DEFAULT };
修改列定义:ALTERTABLE tbl_name MODIFY [COLUMN] col_name col_definition [FIRST| AFTER col_name];
修改列属性:ALTERTABLE tbl_name CHANGE [COLUMN] old _col_name new_col_name
数据表改名:ALTERTABLE tbl_name RENAME [TO|AS] new _tbl_name ;
RENAMETABLE tbl_name TO new _tbl_name;
注:尽量少修改列名与表名。
欢迎光临 黑马程序员技术交流社区 (http://bbs.itheima.com/) |
黑马程序员IT技术论坛 X3.2 |