MySQL2 DML和SQL约束

SQL-DML

数据操作的语言，主要对MySQL进行增加、删除、修改

1.添加数据

INSERT INTO 表名(列名1,列名2,....) VALUES (值1,值2,....)
INSERT INTO 表名(列名1,列名2,....) VALUES (值1,值2,....),(值1,值2,....)....
1
2

--添加数据,单行添加部分列
INSERT INTO  dept(deptno,dname) VALUES (1,'设计部');
INSERT INTO  dept(deptno,dname) VALUES (2,'研发部');

--添加数据,单行添加所有列
INSERT INTO  dept(deptno,dname,location) VALUES (3,'研发部','西安');
INSERT INTO  dept(deptno,dname,location) VALUES (4,'研发部',NULL);
INSERT INTO  dept VALUES (5,'研发部',NULL);

-- 添加多行数据
INSERT INTO `dept` VALUES(6,'测试部','上海'),(7,'人事部','武汉');
1
2
3
4
5
6
7
8
9
10
11

2.修改数据

UPDATE 表名 SET 列名1=值1,列名2=值2...
1

UPDATE 表名 SET 列名1=值1,列名2=值2... WHERE 条件表达式
1

修改表中符合条件的某些列
UPDATE emp SET deptno=30 WHERE empno=2296;
修改表中的所有列
UPDATE emp SET sal=sal-1000,deptno=deptno-10;
1
2
3
4

3.删除

将表中的数据均删除

 DELETE FROM 表名
1

删除表中符合条件表达式的记录

 DELETE FROM 表名 WHERE 条件表达式
1

删除部分行
删除job为ABC的行
DELETE FROM emp WHERE ename='ABC';
删除所有的行
DELETE FROM emp;
1
2
3
4
5

TRUNCATE和DELETE的区别：

TRUNCATE删除表和表中的数据，重新创建的表和原来的表的结构一样
取消自增的默认值,从默认值1开始(自定义的默认值也不存在)
TRUNCATE TABLE emp;


DELETE删除的是表中的数据,可以跟条件
DELETE FROM 表名 WHERE 条件表达式
自增的默认值是从断点的位置自增的数据的值上自增
1
2
3
4
5
6
7
8

SQL-约束Primary Key

主键不可重复，主键不能为NULL

一个表中只能有一个主键(包含联合主键)

添加主键的列的值不能为空，也不能重复

唯一标识表中的一行数据，提高查询效率

创建主键约束

单列主键

方式1：

列名 数据类型 PRIMARY KEY

-- 添加主键的约束方式1
-- 列名 数据类型 PRIMARY KEY
CREATE TABLE  test1(
	NO INT PRIMARY KEY,
	NAME VARCHAR(10),
	age INT
);
1
2
3
4
5
6
7

方式2：

创建表完成后，修改表结构

ALTER TABLE 表名 ADD PRIMARY KEY(列名);

-- 添加主键的约束方式2
-- ALTER TABLE 表名 ADD PRIMARY KEY(列名);
CREATE TABLE test2(
	NO INT,
	NAME VARCHAR(10),
	age INT
);
ALTER TABLE test2 ADD PRIMARY KEY(NO);
1
2
3
4
5
6
7
8

单列主键的约束规则：

一张表只能有一个单列主键,不能重复,不能为空

联合主键

创建方式1

-- 创建方式1:
-- [CONSTRAINT pk1] PRIMARY KEY(列名1,列名2,...)
CREATE TABLE emp1(
	NAME VARCHAR(10),
	eid INT,
	sal INT,
	CONSTRAINT pk1 PRIMARY KEY(NAME,eid)
)

DESC emp1;
1
2
3
4
5
6
7
8
9
10

创建方式2

-- 创建方式2:创建好后添加主键约束
CREATE TABLE emp2(
	NAME VARCHAR(10),
	eid INT,
	sal INT
)
    
ALTER TABLE emp2 ADD PRIMARY KEY(NAME,eid);
DESC emp2;
1
2
3
4
5
6
7
8
9

验证主键约束

-- 验证主键约束
INSERT INTO emp1(eid,NAME,sal) VALUES(1,'张三',2000);
INSERT INTO emp1(eid,NAME,sal) VALUES(2,'李四',2000);
-- 不能添加,主键不能重复
INSERT INTO emp1(eid,NAME,sal) VALUES(2,'李四',2000);
-- 不能添加,主键不能为null
INSERT INTO emp1(eid,NAME,sal) VALUES(NULL,'王五',2000);
INSERT INTO emp1(eid,NAME,sal) VALUES(3,NULL,2000);
-- 注意,这里的'NULL'为字符串,可以添加
INSERT INTO emp1(eid,NAME,sal) VALUES(3,'NULL',2000);
1
2
3
4
5
6
7
8
9
10

删除主键约束

ALTER TABLE 表名 DROP PRIMARY KEY;

-- 删除主键
ALTER TABLE emp2 DROP PRIMARY KEY; 
DESC emp2;
1
2
3

设置主键自增AUTO_INCREMENT

注意:无论SQL执行是否成功，都会自增

方式1

类名 数据类型 PRIMARY KEY AUTO_INCREMENT

-- 主键自增并设置起始值方式1
CREATE TABLE student3(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sno INT,
	NAME VARCHAR(10)
)AUTO_INCREMENT=100;

DESC student3;
-- 添加数据查看自增从指定的100开始,每次自增1
INSERT INTO student3(sno,NAME) VALUES(1,'爪爪');
INSERT INTO student3(sno,NAME) VALUES(2,'猫猫');

SELECT * FROM student3;
1
2
3
4
5
6
7
8
9
10
11
12
13

方式2

ALTER TABLE 表名 MODIFY COLUMN 字段名 字段数据类型 AUTO_INCREMENT PRIMARY KEY;

-- 主键自增并设置起始值方式2
CREATE TABLE student4(
	id INT,
	sno INT,
	NAME VARCHAR(10)
);

ALTER TABLE student4 MODIFY COLUMN id INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE student4 AUTO_INCREMENT = 20; 


DESC student4;
-- 添加数据查看自增从指定的100开始,每次自增1
INSERT INTO student4(sno,NAME) VALUES(1,'爪爪');
INSERT INTO student4(sno,NAME) VALUES(2,'猫猫');

SELECT * FROM student4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

delete和truncate删除后，主键的自增

delete在删除之后从断点开始自增

DELETE FROM student4 WHERE id=21;--表中最后一个
INSERT INTO student4(sno,NAME) VALUES(2,'猫猫');-- id=22

DELETE FROM student4;-- 删除表
INSERT INTO student4(sno,NAME) VALUES(3,'李郁');-- id=23
1
2
3
4
5

truncate数据之后自动增长默认从起始值1开始,和表之前创建时或者设置的指定自增无关

TRUNCATE student4;-- 截断表,实际上是删除表和表中的数据然后复制表结构
INSERT INTO student4(sno,NAME) VALUES(1,'爪爪');-- id=1
INSERT INTO student4(sno,NAME) VALUES(2,'猫猫');-- id=2
1
2
3

SQL-唯一约束UNIQUE

唯一约束,该约束的键所在的列不能重复,但可以为null

元素不能重复，但是值可以为NULL

一个表之中可以有多列为唯一约束

创建唯一约束

方式1

字段名 数据类型 UNIQUE

icard CHAR(18) UNIQUE
1

CREATE TABLE student5(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sno INT,
	NAME VARCHAR(10),
	icard CHAR(18) UNIQUE
);
DESC student5;
SELECT * FROM student5;
-- 添加的元素只要不重复,均可添加,也可以为null
INSERT INTO student5(sno,NAME,icard) VALUES(1001,'猫猫','610721200509160985');
INSERT INTO student5(sno,NAME,icard) VALUES(1002,'猫猫','610721200509160986');
INSERT INTO student5(sno,NAME,icard) VALUES(1003,'猫猫',NULL);
INSERT INTO student5(sno,NAME) VALUES(1004,'猫猫');
1
2
3
4
5
6
7
8
9
10
11
12
13

修改唯一约束

方法2

修改表中的sno字段,添加唯一约束,要注意不然是空表,要不然里面的数据符合唯一约束

ALTER TABLE 表名 ADD UNIQUE(字段名);

ALTER TABLE student6 ADD UNIQUE(sno);
1

-- 给sno添加唯一约束
CREATE TABLE student6(
	id INT PRIMARY KEY AUTO_INCREMENT,
	sno INT,
	NAME VARCHAR(10),
	icard CHAR(18) UNIQUE
);
-- 修改表中的sno字段,添加唯一约束,要注意不然是空表,要不然里面的数据符合唯一约束
ALTER TABLE student6 ADD UNIQUE(sno);
DESC student6;

SELECT * FROM student6;
-- 检查唯一约束信息
INSERT INTO student6(sno,NAME,icard) VALUES(1001,'袁继峰','610721200506090145');
-- 第二条数据不能添加,sno也是唯一约束
INSERT INTO student6(sno,NAME,icard) VALUES(1001,'袁继峰','610721200506090146');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

删除唯一约束

ALTER TABLE 表名 DROP INDEX 字段名;

-- 删除唯一的约束
ALTER TABLE student6 DROP INDEX sno;
1
2

SQL-非空约束NOT NULL

特点:添加非空约束后,列不能为空

创建非空约束

方式1：创建时声明非空约束

字段名 数据类型 NOT NULL

CREATE TABLE student7(
	id INT PRIMARY KEY,
	sno INT,
	NAME VARCHAR(10) NOT NULL
);
1
2
3
4
5

方式2：修改表的结构为非空

注意:修改时不能存在不符合数据的情况

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

ALTER TABLE student7 MODIFY NAME VARCHAR(10) NOT NULL;
1

删除非空约束

-- 删除非空约束,将name字段的约束修改为NULL
ALTER TABLE student7 MODIFY NAME VARCHAR(10) NULL;
1
2

SQL-外键约束FOREIGN KEY

1.作用

限定某个表的某个字段的引用完整性，

比如：员工表的员工所在部门的选择，必须在部门表能找到对应的部分。

2.关键字：foreign key

3.主表和从表/父表和子表

主表（父表）：被引用的表，被参考的表

从表（子表）：引用别人的表，参考别人的表

例如：员工表的员工所在部门这个字段的值要参考部门表，

​ 部门表是主表，员工表是从表。

例如：学生表、课程表、选课表

​ 选课表的学生和课程要分别参考学生表和课程表，

​ 学生表和课程表是主表，选课表是从表。

4.特点

（0）创建外键时，主表的字段一定是主键约束

（1）在“从表”中指定外键约束，并且一个表可以建立多个外键约束

（2）创建(create)表时就指定外键约束的话，先创建主表，再创建从表

（3）删表时，先删从表（或先删除外键约束），再删除主表

（4）从表的外键列，必须引用/参考主表的键列（主键或唯一键）

为什么？因为被依赖/被参考的值必须是唯一的

（5）从表的外键列的数据类型，要与主表被参考/被引用的列的数据类型一致，并且逻辑意义一致。

例如：都是表示部门编号，都是int类型。

（6）外键列也会自动建立索引（根据外键查询效率很高，很多）

（7）外键约束的删除，所以不会自动删除，如果要删除对应的索引，必须手动删除

如何指定外键约束

（1）建表时

create table 主表名称(
	字段1  数据类型  primary key,
    字段2  数据类型
);

create table 从表名称(
	字段1  数据类型  primary key,
    字段2  数据类型,
    foreign key （从表的某个字段) references 主表名(被参考字段)
);
#(从表的某个字段)的数据类型必须与主表名(被参考字段)的数据类型一致，逻辑意义也一样
#(从表的某个字段)的字段名可以与主表名(被参考字段)的字段名一样，也可以不一样
1
2
3
4
5
6
7
8
9
10
11
12

（2）建表后（了解）

alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) 【on update xx】【on delete xx】;
1

5.如何查看外键约束名

desc 从表名称; #可以看到外键约束，但看不到外键约束名

show create table 从表名称; #可以看到外键约束名

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称
1
2
3
4
5
6
7
8
9

6.如何查看外键字段索引

show index from 表名称; #查看某个表的索引名
1

7.如何删除外键约束（了解）

删除外键约束，不会自动删除外键约束列的索引，需要单独删除。

(1)第一步先查看约束名和删除外键约束
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名

alter table 从表名 drop foreign key 外键约束名;

（2）第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名

alter table 从表名 drop index 索引名;
1
2
3
4
5
6
7
8
9

8.SQL演示

#演示外键约束
/*
1、作用：
用来限定子表和父表的引用关系。
子表的外键字段值必须在父类被引用字段值的范围内。

2、外键约束一定是有两个表的角色。
其中一个作为子表（又称为从表），另一个作为父表（又称为主表）。

子表外键字段的值要从父表被引用字段的值中选择。

例如：员工表中记录员工信息，其中有一个字段是“部门编号”，表示这个员工所属的部门。
      部门的信息是记录在部门表中，
	  但是员工表的“部门编号”字段的值不能随意填写，需要参考/引用“部门表”的部门记录。
	  
	  员工表是子表，引用别人，受部门表的牵制/范围管辖。
	  部门表示父表，被别人引用。
	  
例如：商品表、用户表、订单表、订单明细表
		商品表里面记录了商品信息（商品名称、价格、库存量...）
		用户表里面记录了用户信息（用户名、密码、账户余额...）
		订单表里面记录了订单信息（订单编号、订单总价格、订单商品总数量、收获地址...）
		订单明细表记录了订单中每一个明细（商品编号、订单编号、价格、数量....）
		
		中午下了一个订单，买了“米粉”、“袜子”、“手机贴膜”、“高压锅”
		订单编号：112122123222222
		
		商品表：
		1    “米粉”    52.5	 ....
		2    “袜子”    23.0	 ....
		3    “手机贴膜”    15.0	 ....
		4    “高压锅”    125.0	 ....
		
		订单明细中：
		订单编号         商品编号  数量			价格	....
		112122123222222  1			2（盒米粉）  52.5	
		112122123222222  2			1（打）       23.0	
		112122123222222  3			1（张）       15.0		
		112122123222222  4			1（个）       125.0

		
		订单明细表是子表，商品表和订单表是父表。		

3、外键约束要求
（1）外键约束必须在子表中定义
（2）子表中要定义外键约束的字段的数据类型和逻辑意义，必须和父表中被引用字段的数据类型、逻辑意义一样。
例如：员工表中部门编号字段 引用  部门表中的部门编号字段
      订单明细表中的订单编号  引用  订单表中订单编号
      订单明细表中的商品编号  引用  商品表中商品编号
	
（3）子表中要定义外键约束的字段的名字和父表中被引用字段名称可以不同。
（4）父表中被引用的字段必须是键约束字段，一般都是主键或唯一键约束。
此时子表和父表的关系可以是一对一或一对多的关系。
父表是一，子表是一或多。

例如：父表（部门表）一条记录，对应子表（员工表）多条记录。
	  父表（订单表）一条记录，对应子表（订单明细表）一条或多条记录。
	  
（5）父表必须先存在，然后才能正确建立子表的外键约束
	  

4、关键字 foreign key 和 references  


5、外键约束约束了什么？
（1）建表的顺序，先建父表，再建子表
（2）删表的顺序，先删子表，再删父表，
或者先删掉外键约束，然后分别删除两个表（顺序就随意了）
（3）给子表添加数据、修改外键字段的数据，受影响，因为要从父表的被引用字段范围内选值。
（4）删除和修改父表的被引用字段的数据，受影响，因为要考虑这个值是否被子表引用了。
（5）是否建立外键，和查询无关。

比喻：孩子的消费行为要受约束，在爸爸的挣钱范围内。
      爸爸的行为也要受约束，被依赖，不是那么自由。
	  双方都会受影响。
*/

#建表时，指定外键约束
create table dept(
	did int primary key auto_increment,
	dname varchar(50) unique key not null
);

create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	departmentid int,  #子表中外键约束的字段名和父表的被引用字段名不要求一致，但是数据类型和逻辑意义要一样
	#外键约束只能在字段列表下面单独定义，不能在字段后面直接定义
	foreign key (departmentid) references dept(did)
);

#查看表结构
desc dept;
desc emp;

mysql> desc dept;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| did   | int         | NO   | PRI | NULL    | auto_increment |
| dname | varchar(50) | NO   | UNI | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> desc emp;
+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| id           | int         | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20) | NO   |     | NULL    |                |
| departmentid | int         | YES  | MUL | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

#查看表的定义
show create table dept;
show create table emp;


mysql> show create table dept\G
*************************** 1. row ***************************
       Table: dept
Create Table: CREATE TABLE `dept` (
  `did` int NOT NULL AUTO_INCREMENT,
  `dname` varchar(50) NOT NULL,
  PRIMARY KEY (`did`),
  UNIQUE KEY `dname` (`dname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show create table emp\G
*************************** 1. row ***************************
       Table: emp
Create Table: CREATE TABLE `emp` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `departmentid` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `departmentid` (`departmentid`),
  CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `dept` (`did`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)


#查看系统库的约束表
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';

mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | atguigu           | PRIMARY         | atguigu      | emp        | PRIMARY KEY     | YES      |
| def                | atguigu           | emp_ibfk_1      | atguigu      | emp        | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.00 sec)

#添加记录
#添加父表信息，没有影响
insert into dept values(null,'财务'),(null,'教学'),(null,'咨询'),(null,'后勤');

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   4 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
|   1 | 财务  |
+-----+-------+
4 rows in set (0.00 sec)

#添加子表信息，有影响，受到约束
insert into emp values(null,'张三',1);#成功
insert into emp values(null,'李四',1);#成功
insert into emp values(null,'王五',2);#成功
insert into emp values(null,'赵六',6); #失败
#因为departmentid=1或2，在父表dept中可以找到对应记录
#因为departmentid=6，在父表dept中找不到对应记录

mysql> insert into emp values(null,'赵六',6);
ERROR 1452 (23000): Cannot add（添加） or update（修改） a child(子表） row（记录/行）:
 a foreign key constraint fails 
 (`atguigu`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `dept` (`did`))

mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |            1 |
|  2 | 李四 |            1 |
|  3 | 王五 |            2 |
+----+------+--------------+
3 rows in set (0.00 sec)


#修改子表的外键字段的信息，有影响，受到约束
update emp set departmentid = 3 where id = 1;#成功
#因为departmentid = 3在父表dept中可以找到对应部门

update emp set departmentid = 6 where id = 3; #失败  
#因为departmentid = 6在父表dept中找不到对应部门

mysql> update emp set departmentid = 6 where id = 3;
ERROR 1452 (23000): Cannot add or update a child row: 
a foreign key constraint fails (`atguigu`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `dept` (`did`))


mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |            3 |
|  2 | 李四 |            1 |
|  3 | 王五 |            2 |
+----+------+--------------+
3 rows in set (0.00 sec)

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   4 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
|   1 | 财务  |
+-----+-------+
4 rows in set (0.00 sec)


#修改父表的被引用字段的值，受约束
update dept set did = 6 where did = 1;#失败
#因为did=1的部门被子表引用
update dept set did = 6 where did = 4;#成功
#因为 did=4的部门没有被子表引用

mysql> update dept set did = 6 where did = 1;
ERROR 1451 (23000): Cannot delete（删除） or update（修改） a parent（父表） row（记录/行）:
 a foreign key constraint fails 
 (`atguigu`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `dept` (`did`))


mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   6 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
|   1 | 财务  |
+-----+-------+
4 rows in set (0.00 sec)

#删除父表的记录，受约束
delete from dept where did = 6; #成功
#因为 did=6的部门没有被子表引用

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   3 | 咨询  |
|   2 | 教学  |
|   1 | 财务  |
+-----+-------+
3 rows in set (0.00 sec)

#删除父表的记录，受约束
delete from dept where did = 1; #失败
#因为 did=1的部门被子表引用

mysql> delete from dept where did = 1;
ERROR 1451 (23000): Cannot delete or update a parent row:
 a foreign key constraint fails (`atguigu`.`emp`, CONSTRAINT `emp_ibfk_1` FOREIGN KEY (`departmentid`) REFERENCES `dept` (`did`))

#删除子表的数据，不受约束
delete from emp where name ='王五'; #可以

mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |            3 |
|  2 | 李四 |            1 |
+----+------+--------------+
2 rows in set (0.00 sec)


#删除父表，受约束
drop table dept; #失败

mysql> drop table dept; #失败
ERROR 3730 (HY000): Cannot drop table 'dept' referenced by a foreign key constraint 'emp_ibfk_1' on table 'emp'.

#删除子表，不受约束
drop table emp;

#能不能在建表后添加外键约束，如何可以，怎么写？
alter table 子表名 add foreign key(子表的外键字段名) references 父表名(被引用字段);


create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	departmentid int
);

#给emp表（子表）增加外键约束
alter table emp add foreign key(departmentid) references dept(did);

#查看emp的约束信息
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
mysql> SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | ENFORCED |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
| def                | atguigu           | PRIMARY         | atguigu      | emp        | PRIMARY KEY     | YES      |
| def                | atguigu           | emp_ibfk_1      | atguigu      | emp        | FOREIGN KEY     | YES      |
+--------------------+-------------------+-----------------+--------------+------------+-----------------+----------+
2 rows in set (0.01 sec)

#只有是键约束（主键、唯一键、外键），都会自动创建索引。
#查看emp表的索引
show index from emp;

mysql> show index from emp;
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | departmentid |            1 | departmentid | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
#主键字段索引名是PRIMARY，删除主键时，会自动删除对应索引
#唯一键字段索引名是字段名，删除唯一键时，就是通过删除对应的索引方式来删除唯一键约束
#外键字段索引名是字段名，删除外键时，

#删除外键约束
alter table 表名称 drop foreign key 外键约束名;

#删除emp表的departmentid字段的外键约束
alter table emp drop foreign key emp_ibfk_1;

mysql> show index from emp;
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY      |            1 | id           | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| emp   |          1 | departmentid |            1 | departmentid | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)


#说明，删除外键约束时，不会自动删除外键字段的索引，因为它们的命名不一样，
#如果需要删除对应的索引，需要单独删 
alter table 表名称 drop index 索引名;

#删除emp表的departmentid字段的索引
alter table emp drop index departmentid;

mysql> show index from emp;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| emp   |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
1 row in set (0.00 sec)



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371

9.演示问题

（1）失败：不是键列

create table dept(
	did int ,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
	eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    deptid int,				#员工所在的部门
    foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint  原因是dept的did不是键列
1
2
3
4
5
6
7
8
9
10
11

（2）失败：数据类型不一致

create table dept(
	did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
	eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    deptid char,				#员工所在的部门
    foreign key (deptid) references dept(did)
);ERROR 1215 (HY000): Cannot add foreign key constraint  原因是从表的deptid字段和主表的did字段的数据类型不一致，并且要它俩的逻辑意义一致
1
2
3
4
5
6
7
8
9
10
11

（3）成功，两个表字段名一样

create table dept(
	did int primary key,		#部门编号
    dname varchar(50)			#部门名称
);

create table emp(
	eid int primary key,  #员工编号
    ename varchar(5),     #员工姓名
    did int,				#员工所在的部门
    foreign key (did) references dept(did)  
    #emp表的deptid和和dept表的did的数据类型一致，意义都是表示部门的编号
    #是否重名没问题，因为两个did在不同的表中
);
1
2
3
4
5
6
7
8
9
10
11
12
13

10.设置外键约束等级

• Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录

• Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null

• No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作

• Restrict方式：同no action, 都是立即检查外键约束

• Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

如果没有指定等级，就相当于Restrict方式。

/*
外键约束可以设置约束等级：
（1）No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
（2）Restrict方式：同no action, 都是立即检查外键约束
（3）Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null  
（4）Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录 
（5）Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别

on update No action | Restrict | Set null | Cascade 
on delete No action | Restrict | Set null | Cascade 
如果没有写on update 或on delete字句，表示选择了No action | Restrict。

*/
#父表
create table dept(
	did int primary key auto_increment,
	dname varchar(50) unique key not null
);

insert into dept values(null,'财务'),(null,'教学'),(null,'咨询'),(null,'后勤');

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   4 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
|   1 | 财务  |
+-----+-------+
4 rows in set (0.00 sec)

#子表
create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	departmentid int,
	foreign key (departmentid) references dept(did) on update cascade on delete set null
	#on delete set null要求departmentid字段没有not null约束
);

#添加子表时记录和  定义on update cascade on delete set null无关。还是要在主表能找到对应的记录。
insert into emp values(null,'张三',1);
insert into emp values(null,'李四',2);
insert into emp values(null,'王五',1);

mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |            1 |
|  2 | 李四 |            2 |
|  3 | 王五 |            1 |
+----+------+--------------+
3 rows in set (0.00 sec)

#修改子表， 和  定义on update cascade on delete set null无关。还是要在主表能找到对应的记录。
update emp set departmentid = 6 where name = '王五';
#失败，因为departmentid = 6在父表dept中找不到对应部门

#修改父表被引用的did值， 和  定义on update cascade on delete set null有关。
update dept set did = 6 where did = 1; 
#此时did=1的记录被子表引用了，可以修改，并且会同时修改子表的departmentid=1的字段值为6，级联修改

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   4 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
|   6 | 财务  |
+-----+-------+
4 rows in set (0.00 sec)

mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |            6 |
|  2 | 李四 |            2 |
|  3 | 王五 |            6 |
+----+------+--------------+
3 rows in set (0.00 sec)

#删除父表dept被引用的did的记录， 和  定义on update cascade on delete set null有关。
delete from dept where did = 6; #did=6的部门在子表中有引用

mysql> select * from dept;
+-----+-------+
| did | dname |
+-----+-------+
|   4 | 后勤  |
|   3 | 咨询  |
|   2 | 教学  |
+-----+-------+
3 rows in set (0.00 sec)

mysql> select * from emp;
+----+------+--------------+
| id | name | departmentid |
+----+------+--------------+
|  1 | 张三 |         NULL |
|  2 | 李四 |            2 |
|  3 | 王五 |         NULL |
+----+------+--------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107