外键

• 建立外键是为了保证数据的完整和统一性
• 外建所在表为从表

创建数据库

CREATE DATABASE chapter05;

选择数据库

USE chapter05;

创建班级表

CREATE TABLE grade(
id INT(4) NOT NULL PRIMARY KEY,
name VARCHAR(36) 
);

创建学生表

CREATE TABLE student(
sid INT(4) NOT NULL PRIMARY KEY,
sname VARCHAR(36),
gid INT(4) NOT NULL
);

为表添加外键约束

# 为表student添加外键约束
alert table student add constraint FK_ID foreign key(gid) REFERENCES grade(id);

在为表添加外键约束时，有些需要注意的地方

1. 建立外键的表必须时InnoDB型，不能时临时表。因为MySQL中只有InnoDB类型的表才支持外键。
2. 定义外键名时，不能加引号，如constraint 'FK_ID'或constraint "FK_ID"都是错误的。

删除外键约束

# 将表student中的外键约束删除
alter table student drop foreign key FK_ID;

---

操作关联表

关联关系

1. 多对一
2. 多对多
3. 一对一

添加数据

# 在表student和表grade中添加外键约束来建立两个表的关联关系
alter table student add constraint FK_ID foreign key (gid) REFERENCES grade (id);
# 如果要为两个表添加数据，就需要先为主表grade添加数据
INSERT INTO grade(id,name) VALUES(1,'软件一班');
INSERT INTO grade(id,name) VALUES(2,'软件二班');
# 向student表添加数据，由于student表的外键与grade表的主键关联，因此在为student表添加数据时，gid的值只能时1或2，不能使用其他的值
INSERT INTO student(sid,sname,gid) VALUES(1,'王红',1);
INSERT INTO student(sid,sname,gid) VALUES(2,'李强',1);
INSERT INTO student(sid,sname,gid) VALUES(3,'赵四',2);
INSERT INTO student(sid,sname,gid) VALUES(4,'郝娟',2);

删除数据

由于grade表和student表之间具有关联关系。参照列被参照的值是不能被删除的，因此，在删除软件一班时，一定要先删除该班级的所有学生，然后再删除班级。

# 将软件一班的所有学生全部删除
delete from student where sname='王红';
delete from student where sname='李强';
# 在grade表中，将软件一班删除
delete from grade where id=1;

---

链接查询

创建表department表

CREATE TABLE department(
did int(4) NOT NULL PRIMARY KEY,
dname varchar(36)
);

创建employee表

CREATE TABLE employee(
id int(4) NOT NULL PRIMARY KEY,
name varchar(36),
age int(2),
did int(4) NOT NULL
);

向两个表中插入数据

INSERT INTO department (did,dname) VALUES (1,'网络部');
INSERT INTO department (did,dname) VALUES (2,'媒体部');
INSERT INTO department (did,dname) VALUES (3,'研发部');
INSERT INTO department (did,dname) VALUES (4,'人事部');

INSERT INTO employee (id,name,age,did) VALUES (1,'王红',20,1);
INSERT INTO employee (id,name,age,did) VALUES (2,'李强',22,1);
INSERT INTO employee (id,name,age,did) VALUES (3,'赵四',20,2);
INSERT INTO employee (id,name,age,did) VALUES (4,'郝娟',20,4);

交叉连接

department表中有4个部门，employee表中有4个员工，那么交叉连接的结果就有4×4=16条数据。

# 使用交叉连接查询部门表和员工表中所有的数据
SELECT * FROM department CROSS JOIN employee;

内连接

在内连接查询中，只有满足条件的记录才能出现在查询结果中。

# 在department表和employee表之间使用内连接查询
SELECT employee.name,department.dname FROM department JOIN employee ON department.did=employee.did;
# 在department表和employee表之间使用WHERE
SELECT employee.name,department.dname FROM department,employee WHERE department.did=employee.did;
# 在department表和employee表之间使用自连接查询
SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did=p2.did WHERE p2.name='王红';

外连接

左连接

# 在department表和employee表之间使用左连接查询
SELECT department.did,department.dname,employee.name FROM department LEFT JOIN employee on department.did=employee.did;

右连接

# 在department表和employee表之间使用右连接查询
SELECT department.did,department.dname,employee.name FROM department  RIGHT JOIN employee ON department.did=employee .did;

复合条件连接查询

# 在department表和employee表之间使用内连接查询，并将查询结果按照年龄从小到大进行排序
SELECT employee.name,employee.age,department.dname FROM department JOIN employee ON department.did=employee.did order by age;

---

子查询

带IN关键字的子查询

# 查询存在年龄为20岁的员工部门
SELECT * FROM department WHERE did IN (SELECT did FROM employee WHERE age=20);
# 查询不存在年龄为20岁的员工部门
SELECT * FROM department WHERE did NOT IN (SELECT did FROM employee WHERE age=20);

带EXISTS关键字的子查询

EXISTS关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不产生任何数据，只返回TRUE或FALSE，当返回值为TRUE时，外层查询才会执行。

# 查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录
SELECT * FROM department WHERE EXISTS (select did from employee where age>21);

带ANY关键字的子查询

ANY关键字表示满足任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层查询中的任意一个比较条件，就返回一个结果作为外层查询条件。

# 使用带ANY关键字的子查询，查询满足条件的部门
SELECT * FROM department WHERE did>any(select did from employee);

上述语句在执行的过程中，首先子查询会将employee表中所有did查询出来，分别为1、1、2、4，然后将department表中did的值与之进行比较，只要大于employee .did中的任意一个值，就是符合条件的查询结果过。

带ALL关键字的子查询

ALL关键字与ANY有点类似，只不过带ALL关键字的子查询返回的结果需同事满足所有内层查询条件。

SELECT * FROM department WHERE did>all(select did from employee);

带比较运算符的子查询

# 使用带比较运算符的子查询，查询赵四是哪个部门的员工
SELECT * FROM department WHERE did=(select did from employee where name='赵四');