mysql基础(一)三范式与约束

数据库三范式：
1.必有主键，各个字段原子性不可再分。
2.在1之上要求所有非主键完全依赖主键。
3.在2之上要求所有非主键直接依赖主键

五种约束：主键、唯一、非空、默认、外键。
首先创建两张表，一张主表，一张从表，如下图（红字为注释）：

两张表的主键、外键、唯一约束都没有用CONSTRAINT指定约束名，接下来就查看下两张表中的KEY。
输入：SHOW KEYS FROM fubiao 查询父表中的key，结果如图

输入SHOW KEYS FROM zibiao 查询子表中的KEY，结果如图

查询语句用index代替KEY结果也是一样，如show index from fubiao,说明带KEY的约束创建的同时也有了索引。
从以上结果可知，在未使用constraint指定约束名的情况下，主键KEY名默认为PRIMARY，而唯一约束和外键约束KEY名默认为字段名，因为主键只能有一个所以默认名为primary,而唯一约束和外键可以有多个，所以默认为字段名。接下来，我们对约束进行删除和添加测试。
首先使用drop和add进行删除和添加：
输入ALTER TABLE fubiao DROP PRIMARY KEY删除父表中的主键，结果报错：

因为父表中的主键和子表中外键相关联了，先得删掉子表中的外键才能删除父表中的主键。
输入ALTER TABLE zibiao DROP FOREIGN KEY wj_id删除子表中的外键，结果又报错，如图：

这是为什么呢？
其实KEY名（索引名）和约束名根本不是一回事，输入SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='zibiao'查看子表中的约束名和约束类型，结果如下图：

查询出的两行数据，第一行是主键约束名，和KEY名相同，第二行就是外键约束名，与KEY名是不同的。一定要注意，根据约束名删除而不是KEY名，所以指定约束名很重要。
输入ALTER TABLE zibiao DROP FOREIGN KEY zibiao_ibfk_1删除成功，再次输入SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM information_schema.TABLE_CONSTRAINTS WHERE TABLE_NAME='zibiao'查询子表中的约束类型和约束名，结果显示只剩主键约束，如下图：

子表外键已经删除成功，接下来我们可以删除父表中的主键了，输入

ALTER TABLE fubiao DROP PRIMARY KEY

删除成功，再依次删除其它约束，

ALTER TABLE fubiao ALTER COLUMN sex DROP DEFAULT

删除父表中的默认约束成功，注意语句变化，多了ALTER COLUMN sex 。
输入

SELECT constraint_type,constraint_name FROM information_schema.table_constraints WHERE table_name='fubiao'

查询父表中的约束类型和约束名，得知唯一约束默认名为字段名，如下图：

输入ALTER TABLE fubiao DROP UNIQUE KEY bianhao报错，删除唯一约束的时候是不用带UNIQUE关键字的，再次输入

ALTER TABLE fubiao DROP  KEY bianhao

删除成功。
至此我们已经成功的删除了子表中的外键，父表中的主键、默认约束、唯一约束。
接下来再用ADD添加刚才删除了的约束。
有了父表中的主键才能有子表中的外键，所以我们先从主表开始。
添加主键：

ALTER TABLE fubiao ADD PRIMARY KEY(id)

添加默认约束：

ALTER TABLE fubiao ALTER COLUMN sex SET DEFAULT'男'

注意，添加默认约束不是用add而是set。
添加唯一约束：

ALTER TABLE fubiao ADD UNIQUE(bianhao)

添加子表外键约束：

ALTER TABLE zibiao ADD CONSTRAINT waijian FOREIGN KEY(wj_id) REFERENCES fubiao(id)

还有个主表中的非空约束没有做增删测试，因为非空必须用modify增删。
删除主表name字段非空约束：

ALTER TABLE fubiao MODIFY NAME VARCHAR(10)

添加name字段非空约束：

ALTER TABLE fubiao MODIFY NAME VARCHAR(10) NOT NULL

modify功能比较强大，对其它约束也可以进行增加操作。
先将子表外键删除、主表中的主键、唯一、默认等约束删除，再用modify测试增加约束。
其中默认约束可以用modify删除如ALTER TABLE fubiao MODIFY sex CHAR(10)。
增加父表主键约束：

ALTER TABLE fubiao MODIFY id INT PRIMARY KEY 

增加父表默认约束：

ALTER TABLE fubiao MODIFY sex CHAR(8) DEFAULT'女' 

增加父表唯一约束：

ALTER TABLE fubiao MODIFY bianhao BIGINT UNIQUE

以上测试都成功，但是无法用modify增加外键约束。

总结----创表之后对各种约束的增删方式如下：
一.删除方式
1.主键删除：

ALTER TABLE fubiao DROP PRIMARY KEY

语句格式：alter table 表名 drop primary key,需要注意的是有外键关联的情况下删除外键才能删除主键。`
2.外键删除：

ALTER TABLE zibiao DROP FOREIGN KEY zibiao_ibfk_1

语句格式：alter table 表名 drop foreign key 外键约束名.
3.唯一约束删除：

ALTER TABLE fubiao DROP  KEY bianhao

语句格式：ALTER TABLE 表名 DROP KEY 唯一约束名，注意删除的时候是不用unique关键字的。
4.默认约束删除：

ALTER TABLE fubiao ALTER COLUMN sex DROP DEFAULT

语句格式：ALTER TABLE 表名 ALTER COLUMN 字段名 DROP DEFAULT。
或者使用modify：

ALTER TABLE fubiao MODIFY sex CHAR(10)

语句格式：ALTER TABLE 表名 MODIFY 字段名 字段类型，将default省略就等于删除了默认约束，添加时加上default就行。
5.非空约束删除：

ALTER TABLE fubiao MODIFY NAME VARCHAR(10)

语句格式：ALTER TABLE 表名 MODIFY 字段名 字段类型。
二.添加方式
1.主键添加：

ALTER TABLE fubiao ADD PRIMARY KEY(id)

语句格式：ALTER TABLE 表名 ADD PRIMARY KEY(字段名)。
或者使用modify：

ALTER TABLE fubiao MODIFY id INT PRIMARY KEY 

语句格式：ALTER TABLE 表名 MODIFY 字段名 字段类型 PRIMARY KEY
2.外键添加：

ALTER TABLE zibiao ADD CONSTRAINT waijian FOREIGN KEY(wj_id) REFERENCES fubiao(id)

语句格式：ALTER TABLE 子表名 ADD CONSTRAINT 外键约束名 FOREIGN KEY(字段名) REFERENCES 主表名(主表主键字段名)，其中constraint 外键约束名可以省略。
3.唯一约束添加：

ALTER TABLE fubiao ADD UNIQUE(bianhao)

语句格式：ALTER TABLE 表名 ADD UNIQUE(字段名)

ALTER TABLE fubiao MODIFY bianhao BIGINT UNIQUE

语句格式：ALTER TABLE 表名 MODIFY 字段名 字段类型 UNIQUE
4：默认约束添加：

ALTER TABLE fubiao ALTER COLUMN sex SET DEFAULT'男'

语句格式：ALTER TABLE 表名 ALTER COLUMN 字段名 SET DEFAULT'默认值'，注意此处不能用add而是set。

ALTER TABLE fubiao MODIFY sex CHAR(8) DEFAULT'女' 

语句格式：ALTER TABLE 表名 MODIFY 字段名 字符类型 DEFAULT'默认值'
5.非空约束增加：

ALTER TABLE fubiao MODIFY NAME VARCHAR(10) NOT NULL

语句格式：ALTER TABLE 表名 MODIFY 字段名 字段类型 NOT NULL

非空约束只能用MODIFY进行增删；默认约束可以用set、drop或者modify进行增删；主键和唯一约束用drop进行删除，用add或modify进行添加。外键只能用add增加drop删除；
modify只是对存在的字段属性进行有效操作，不能对字段本身进行增删。
add、drop可以对字段本身进行操作，也就可以在增删字段的同时附带约束。