MYSQL学习笔记(三)
修改数据表
添加单列:
ALTER TABLE tbl_name ADD [COLUMNS] col_name column_definition [FIRST | AFTER col_name]
添加多列:
ALTER TABLE tbl_name ADD [COLUMNS] (col_name column_definition,...)
添加多列不能指定位置关系
删除列
ALTER TABLE tbl_name DROP [COLUMNS] col_name
删除和增加列的操作可同时进行:
添加主键约束:
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type] (index_col_name,...)
添加唯一约束:
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...)
添加外键约束:
ALTER TABLE tbl_name ADD [CONSTRAINT[symbol]] FORIEGIN KEY [index_name] (index_col_name,...) reference_definnition
添加/删除默认约束:
ALTER TABLE tbl_name ALTER[COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
添加/删除默认约束:
ALTER TABLE tbl_name ALTER[COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
删除主键约束:
ALTER TABLE tbl_name DROP PRIMARY KEY ;
注:删除主键注意主键是否有AUTO_INCREMENT约束,如果有先修改主键的约束才能进行删除,因为AUTO_INCREMENT只能用在主键
删除唯一约束:
ALTER TABLE tbl_name DROP {index | KEY} index_name
删除外键约束:
ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol
修改列定义:
ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]
修改列名称:
ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST |AFTER col_name]
数据表更名:
1 ALTER TABLE tbl_name1 RENAME [TO|AS] tbl_name2
2 RENAME TABLE tbl_name to new_tbl_name [,tbl_name2 TO new_tbl_name2]...