七、修改数据表–列的增删,约束的增删等操作
1、添加列
添加单列:
ALTER TABLE 表名 ADD [COLUMN] 列名,列的数据类型 [FIRST | AFTER 其他列名]
FIRST: 添加的这一字段在最前面
AFTER 字段名: 置于字段名的后面。如果省略两者,则默认位于最后面
Eg: [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mcS2jZAs-1577615019758)(https://img-blog.csdn.net/20170825082953929?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
添加多列时,无法指定位置关系,只能在最下方。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LDTKjhPN-1577615019760)(https://img-blog.csdn.net/20170825083108928?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
删除
注意:增删这两个操作可以写一起,”,”分割即可。
2、添加约束
①、添加主键约束 ----PRIMARY KEY
本质上是在字段列表最后加了主键约束:
②、唯一约束 UNIQUE KEY
③、外键约束 FOREIGN KEY(列名) REFERENCES(列名)
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QvW0TSwL-1577615019762)(https://img-blog.csdn.net/20170825083527848?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
添加时,数据类型不同:
1005 - Can't create table 't1.#sql-591_11' (errno: 150)
原因:当时id是INT UNSIGNED。 class_id是SMALLINT NOT NULL;.
另外:添加外键约束时若没有指定外键约束的名称,则系统会自动添加外键约束名:表名_ibfk_n(表示第n个外键约束)
④、添加/删除默认约束 -----DEFAULT
3、删除约束
①、删除主键:
ALTER TABLE 表名 DROP PRIMARY KEY;
②、删除唯一约束:
ALTER TABLE 表名 DROP {INDEX|KEY} index_name;
查看唯一约束名字:
SHOW INDEX FROM 表名\G; \\ 加\G(以网格的形式显示)查看更方便。
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A0XaRWrR-1577615019762)(https://img-blog.csdn.net/20170825111327124?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
找到Key_name:约束名 (找UNIQUE KEY)
删除: ALTER TABLE 表名 DROP INDEX(或KEY) name;
③:删除外键约束
查看外键约束:
SHOW CREATE TABLE 表名;
找到:
删除:ALTER TABLE 表名 DROP FOREIGN KEY student3_ibfk_1;
虽然外键删除了,但是索引还是存在的 所有在key一列会有MUL(多值索引)显示:
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hI1weibe-1577615019763)(https://img-blog.csdn.net/20170825111606149?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
如果要删除索引,也得先知道索引名: SHOW CREATE TABLE 表名;
删除:
ALTER TABLE 表名 DROP INDEX 索引名;
4、修改列定义-----修改字段名称、类型、位置
格式:
ALTER TABLE 表名 MODIFY [COLUMN] 列名 column_definition [FIRST| AFTER 列名]
修改字段位置:
ALTER TABLE student3 MODIFY INT NOT NULL FIRST;
//FIRST:放在第一列 AFTER 列名:放在改列后面
//如果什么都不加,则默认放到所有列的最后
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BoGH11Ym-1577615019763)(https://img-blog.csdn.net/20170825113744556?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvSEJsb2Nr/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/SouthEast)]
修改数据类型:
第一次修改报错的原因是主键只有一个,不能再设置主键。
注意:在修改类型时,由大类型到小类型会造成数据的丢失。
修改列名称:
注意:CHANGE不仅可以修改列定义还可以修改列名称,CHANGE的功能要远大于MODIFY。
5、修改数据表名
数据表更名:
//方法1
ALTER TABLE 表名 RENAME [TO|AS] NEW 新表名
//方法2
RENAME TABLE 表名 TO 新表名[,tbl_name2 TO new_tbl_name2]......
使用方法二可以为多张数据表更名。
尽量少使用数据列和数据表的更名,因为我们以后创建了索引或者是曾经使用了视图或存储过程的话, 表名和列名曾经被引用的情况下,如果表名和列名被修改了,可能会导致某些视图或存储过程无法正常工作。
其中,默认、非空约束不存在表级约束。 其余则二者都有。