外键：
准备：创建两张表，一个班级表和一个学生表 #建库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);（1）为表添加外键约束
#student添加外键约束alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);
#查看学生表和班级表desc grade;desc student;
#查看表的详细结构show create table student; （2）删除外键约束 #student中的外键约束删除alter table student drop foreign key FK_ID;
2.操作关联表
（1）添加数据 #为表student和表grade中添加外键约束来建立两个表的关联关系alter table student add constraint FK_ID foreign key(gid) REFERENCES grade (id);
#添加数据INSERT INTO grade(id,name)VALUES(1,'软件一班');INSERT INTO grade(id,name)VALUES(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); （2）在grade表中查询出班级名称为“软件一班”的id SELECT id FROM grade WHERE name='软件一班'; （3）在student表中，查询gid=1的学生，即为软件一班的学生， SELECT sname FROM student WHERE gid=1; （4）将软件一班的所有学生全部删除 delete from student where sname='王红';delete from student where sname='李强'; （5）在grade表中，将软件一班删除 delete from grade where id=1;
3.连接查询
准备：
#建表 USE chapter05; CREATE TABLE department( did int(4) NOT NULL PRIMARY KEY, dname varchar(36));
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(5,'人事部');
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); （1）使用交叉连接查询部门表和员工表中所有的数据 SELECT * FROM department CROSS JOIN employee; （2）在department和employee 表之间使用内连接查询 SELECT employee.name, department.dname FROM department JOIN employee ON department.did=employee.did; （3）在department和employee 表之间使用WHRER SELECT employee.name, department.dname FROM department,employee WHERE department.did=employee.did; （4）在department和employee 表之间使用自连接查询 SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did=p2.did WHERE p2.name='王红'; （5）在department和employee 表之间使用左连接查询 SELECT department.did,department.dname,employee.name FROM department LEFT JOIN employee on department.did=employee.did; （6）在department和employee 表之间使用右连接查询 SELECT department.did,department.dname,employee.name FROM department RIGHT JOIN employee ON department.did=employee.did; （7）在department和employee 表之间使用内连接查询，并将查询结果按照年龄从大到小进行排序 SELECT employee.name, employee.age, department.dname FROM department JOIN employee ON department.did=employee.did order by age; （8）查询存在年龄为20岁的员工部门 SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20); （9）查询不存在年龄为20岁的员工部门 SELECT * FROM department WHERE did NOT IN(SELECT did FROM employee WHERE age=20);（10）查询employee表中是否存在年龄大于21岁的员工，如果存在，则查询department表中的所有记录
SELECT * FROM department WHERE EXISTS(select did from employee where age > 21);
（11）使用带ANY关键字的子查询，查询满足条件的部门 SELECT * FROM department WHERE did>any(select did from employee); （12）使用带ALL关键字的子查询，查询满足条件的部门 SELECT * FROM department WHERE did>all(select did from employee); （13）使用带比较运算符的子查询，查询赵四是哪个部门的员工 SELECT * FROM department WHERE did=(select did from employee where name='赵四');