【MySQL-手记】数据表的修改(添加/删除数据表，添加/删除约束)

落日映苍穹つ 2022-06-16 00:15 295阅读 0赞

**![20170430143347802][]**

**【添加/删除数据表】**

**----------------------------------------------------------------------------------------------------------------------------------------------------------**

ALTER TABLE users ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10;//添加单列

![Center][]

ALTER TABLE users ADD password VARCHAR(30) NOT NULL AFTER username ;//添加有序单列

![Center 1][]

**ALTER TABLE users ADD truename VARCHAR(30) NOT NULL FIRST ;//添加有序单列**

**![Center 2][]**

ALTER TABLE users DROP truename;      //删除单列

**![20170430131410020][]**

**ALTER TABLE users DROP password,DROP age;      //删除多列**

![20170430131659680][]

**----------------------------------------------------------------------------------------------------------------------------------------------------------**

**【添加/删除约束】**

**----------------------------------------------------------------------------------------------------------------------------------------------------------**

# 添加主键约束 #

**创建一个users2表格。**

CREATE TABLE users2(
    username VARCHAR(20) NOT NULL,
    pid SMALLINT UNSIGNED
    );

![20170430132500699][]

**先增加一个新列： ALTER TABLE users2 ADD id SMALLINT UNSIGNED FIRST;**

**![20170430132858962][]**

**为新增ID添加主键约束：ALTER TABLE users2 ADD CONSTRAINT PK\_users2\_id PRIMARY KEY (id);![20170430133508111][]**

**删除主键约束：**

**ALTER TABLE users2 DROP PRIMARY KEY;**

**![20170430140432923][]**

# 添加唯一约束 #

为usename添加唯一约束： ALTER TABLE users2 ADD UNIQUE (username);

![20170430134107740][]

**删除唯一约束前先查看，哪些字段有约束，删除的是约束而不是字段！！！**

SHOW INDEXES FROM users2;//查看索引

**![20170430141316660][]**

ALTER TABLE users2 DROP INDEX  username;

![20170430141536052][]

# 添加外键约束 #

给pid添加外键约束，已知外键列父表的结构：![20170430134459937][]

**ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces(id);**

**![20170430135201918][]**

**删除外键约束，先查看：SHOW CREATE TABLE users2;**

**![20170430141952065][]**

ALTER TABLE users2 DROP FOREIGN  KEY users2\_ibfk\_1;    //删除之后，查看下

![20170430142339025][]

# 添加／删除默认约束 #

新建一个字段ａｇｅ，给ａｇｅ添加默认约束

![20170430135736029][]

**ALTER TABLE users2 ALTER age SET DEFAULT 15;**

**![20170430135946749][]**

**删除默认约束：**

**ALTER TABLE users2 ALTER age DROP DEFAULT;**

**![20170430140149219][]**

**——————————————————————————————————————————**

**//添加唯一约束  
ALTER TABLE getUserImage ADD UNIQUE (openID );  
//修改数据表字段的名称  
alter table getUserImage change province region varchar(60);  
  
//修改数据库格式  
alter database weixin\_getUserImage character set utf8;   
//修改数据表编码  
 ALTER TABLE  getUserImage DEFAULT CHARACTER SET utf8;  
//该命令用于将表中字段的编码方式改为utf8  
ALTER TABLE getUserImage CHANGE sex sex VARCHAR(10) CHARACTER SET utf8 NOT NULL; **

[20170430143347802]: /images/20220616/1c615a15a4a645f49974b9e9d653afef.png
[Center]: /images/20220616/c20f9c21fb2c4fc28d3f80722d42c904.png
[Center 1]: /images/20220616/ce32ba49f078425088968b0fa9669f3d.png
[Center 2]: /images/20220616/afabaecada344e24bb587a5e7c49fb73.png
[20170430131410020]: /images/20220616/a9db64418b4f4ceca0c9f203713ee03a.png
[20170430131659680]: /images/20220616/8a51ffac816a480e9fbafc99404dd60b.png
[20170430132500699]: /images/20220616/42a6c9fa3dc24b389d6bd92e4d877cba.png
[20170430132858962]: /images/20220616/a80d3ac9d27145459e8af8008b7cb485.png
[20170430133508111]: /images/20220616/612f881570b2489cbbeff254be4f84fd.png
[20170430140432923]: /images/20220616/f30380e1466a448abdb2c3a53dfab213.png
[20170430134107740]: /images/20220616/204b18d5155a485f8527a679c961bef2.png
[20170430141316660]: /images/20220616/22fd5e1fde7c4be4ac85c17bca0fc399.png
[20170430141536052]: /images/20220616/7211dd0824744bd28632458202fa2623.png
[20170430134459937]: /images/20220616/a755916e4ccb45e9aa0d6c1b98445fae.png
[20170430135201918]: /images/20220616/3eb32ecf3b8e402a86ac66b35e78a8f7.png
[20170430141952065]: /images/20220616/e6d9f1d7de014090b1517d3c8d6ce59b.png
[20170430142339025]: /images/20220616/6e15c8159ebb4edaabf898213ba06419.png
[20170430135736029]: /images/20220616/d28849724c9f4c948e03cc247bdeee0d.png
[20170430135946749]: /images/20220616/6a5c8d7bae5649889cd286a7e1bc6083.png
[20170430140149219]: /images/20220616/d06755e592794512a088fde85efc7e0f.png