熟能生巧，全部代码在最后！！！
一、多表关系
一对一关系、一对多关系、多对多关系
注意多对多关系必须有中间表进行关联
多对多的关系就相当于是两个一对多关系
二、创建外键约束
专门用于多表操作的一种约束方式
控制的那个表是主表，被控制的表是从表，外键列的值受主键列的值约束
就是让外键列受到主键列的约束，主键列控制外键列，主键列外键列类型要一致
关键字：foreign key
constraint是给表起别名，可以不使用这一步
记得要先创建主键
一般来说使用第一种方式，直接在创建表的时候创建外键约束
特点：主表的数据可以随便添加，从表的外键列数据必须依赖主表
主表的数据如果被从表依赖，则不能删除，而从表的数据可以随便删除
三、外键约束-一对多关系-验证外键约束的作用
注意：必须先给主表添加数据，在给从表添加数据时，外键列的值不能随便写，必须依赖主表的主键列
外键列里的必须是主键列里有的
删除数据：
主表的数据被从表依赖时，不能删除，否则可以删除
从表的数据可以随便删除
删除表：
必须先删除从表，在删除主表
1001不可以删除，但是1004可以删除
删除外键约束：
删除外键约束后，删除数据也可以了
四、外键约束-多对多关系-构建外键约束
修改和删除时，中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除或者修改
五、多表联合查询
外键约束只是对增删改起作用，对多表查询并无影响。
数据准备
交叉连接查询
形成一个笛卡尔集：就是用一张表的每一行去匹配另外一张表的每一行
我们只需要让左表的数据等于右表的数据即可得到想要的数据
内连接查询
求的是交集
-- 查询每个部门的所属员工
-- 隐世内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 d,emp3 e where d.deptno = e.dept_id;
-- 显世内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id;
-- 查询研发部门的所属员工
-- 隐世内连接
select * from dept3 a ,emp3 b where a.deptno = b.dept_id and name = '研发部';
-- 显世内连接
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name = '研发部';
-- 查询研发部和销售部的所属员工
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name in('研发部','销售部');
-- 查询每个部门的员工数，并升序排序
select a.deptno,a.name,count(1) from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name;
-- 查询人数大于等于2的部门，并按照人数降序排序
select a.deptno,a.name,count(1) as total_cnt from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name having total_cnt >= 2 order by total_cnt desc;
外连接查询
outer可以省略
左外连接：
会把左表的数据全部输出，右表的数据如果有对应的就输出，没有就补null
如果有多张表：select * from a left join b on 条件1 left join c on 条件2 left join d on 条件3；
右外连接：
会把右表的数据全部输出，左表的数据如果有对应的就输出，没有就补null
如果有多张表：select * from a right join b on 条件1 right join c on 条件2 right join d on 条件3；
满外连接：
就是把左表和右表一起输出
实现满外连接：full join
mysql不支持full join
union 是把上下的代码拼起来，相当于把左右两个结果并起来，并去重
union all 是把左右两个结果放一起，没有去重
基本子查询
单行单列，可以作为一个值使用
多行多列
一般来说关联效率更高一点
括号里面是2个结果，所以不能用=要使用in
多行多列
子查询的表必须要设置别名
子查询关键字 - all
all是要大于所有的值
就是and
关键字 - any和some
就是和最值比较
这两个是一样的，只是一个别名罢了
any是比任意一个值大就好了
关键字 - in
就是or
关键字 - exists
就是看有没有结果
exists比in的运算效率高，在实际开发中，特别是大数据容量时，推荐使用exists关键字
全表输出，只要exists关键字后面的是正确的就正常执行
因为没有大于60岁的所以输出null
因为有大于20分的所以全部输出
如果查询时没有使用别名就是整个表查询，如果使用了别名就是每一行一行查询
查询有所属部门的员工信息
自关联查询
需要对自身进行关联查询时使用，把一张表当成多张表来用，注意自关联时表必须给表起别名
对自身进行关联查询，结构示意图
六、MySQL多表操作-总结
思维导图
七、练习
数据准备
练习：
八、全部代码
create database mydb3;
use mydb3;
create table if not exists dept(
detpno varchar(20) primary key, -- 部门号
name varchar(20) -- 部门名字
);
create table if not exists emp(
eid varchar(20), -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20), -- 员工所属部门
constraint emp_fk foreign key(dept_id) references dept(detpno) -- 外键约束
);
use mydb3;
create table if not exists dept2(
detpno varchar(20) primary key, -- 部门号
name varchar(20) -- 部门名字
);
create table if not exists emp2(
eid varchar(20), -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 员工年龄
dept_id varchar(20) -- 员工所属部门
);
alter table emp2 add constraint emp2_fk foreign key(dept_id) references dept(detpno);
insert into dept values('1001','研发部');
insert into dept values('1002','销售部');
insert into dept values('1003','财务部');
insert into dept values('1004','人事部');
insert into emp values('1','琼安奉',20,'1001');
insert into emp values('2','阿坎德',21,'1001');
insert into emp values('3','阿达啊',23,'1001');
insert into emp values('4','阿达尔',18,'1001');
insert into emp values('5','凤飞飞',25,'1001');
insert into emp values('6','发发哇',35,'1001');
insert into emp values('7','阿强啊',33,'1005');
delete from dept where detpno = '1001'; -- 不可以删除
delete from dept where detpno = '1004'; -- 可以删除
delete from emp where eid = '7'; -- 可以删除
alter table emp2 drop foreign key emp2_fk;
insert into dept2 values('1001','研发部');
insert into dept2 values('1002','销售部');
insert into dept2 values('1003','财务部');
insert into dept2 values('1004','人事部');
insert into emp2 values('1','琼安奉',20,'1001');
insert into emp2 values('2','阿坎德',21,'1001');
delete from dept2 where detpno = '1001';
use mydb3;
create table if not exists student(
sid int primary key auto_increment,
name varchar(20),
age int,
gender varchar(20)
);
create table course(
cid int primary key auto_increment,
cidname varchar(20)
);
-- 从表
create table score(
sid int,
cid int,
score DOUBLE
);
alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);
insert into student values(1,'ikun',18,'女'),(2,'zhangji',29,'男'),(3,'xiji',32,'男');
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
insert into score values(1,1,78),(1,2,76),(2,1,87),(2,3,75),(3,2,57),(3,3,96);
use mydb3;
create table if not exists dept3(
deptno varchar(20), -- 部门号
name varchar(20) -- 部门名字
);
create table if not exists emp3(
eid varchar(20) primary key, -- 员工编号
ename varchar(20), -- 员工名字
age int, -- 年龄
dept_id varchar(20) -- 所属部门
);
insert into dept3 values('1001','研发部');
insert into dept3 values('1002','人事部');
insert into dept3 values('1003','销售部');
insert into dept3 values('1004','财务部');
insert into dept3 values('1006','财务部');
insert into emp3 values('1','琼安奉',20,'1001');
insert into emp3 values('2','阿达啊',22,'1002');
insert into emp3 values('3','阿达发',18,'1003');
insert into emp3 values('4','阿尔法',24,'1004');
insert into emp3 values('5','阿飞发',33,'1001');
insert into emp3 values('6','爱蜂窝',29,'1005');
select * from dept3,emp3;
-- 查询每个部门的所属员工
-- 隐世内连接
select * from dept3,emp3 where dept3.deptno = emp3.dept_id;
select * from dept3 d,emp3 e where d.deptno = e.dept_id;
-- 显世内连接
select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 join emp3 on dept3.deptno = emp3.dept_id;
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id;
-- 查询研发部门的所属员工
-- 隐世内连接
select * from dept3 a ,emp3 b where a.deptno = b.dept_id and name = '研发部';
-- 显世内连接
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name = '研发部';
-- 查询研发部和销售部的所属员工
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
select * from dept3 a inner join emp3 b on a.deptno = b.dept_id and name in('研发部','销售部');
-- 查询每个部门的员工数，并升序排序
select a.deptno,a.name,count(1) from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name;
-- 查询人数大于等于2的部门，并按照人数降序排序
select a.deptno,a.name,count(1) as total_cnt from dept3 a inner join emp3 b on a.deptno = b.dept_id group by a.deptno,name having total_cnt >= 2 order by total_cnt desc;
use mydb3;
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id;
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;
select * from dept3 a full join emp3 b on a.deptno = b.dept_id;
select * from dept3 a left outer join emp3 b on a.deptno = b.dept_id
union all
select * from dept3 a right outer join emp3 b on a.deptno = b.dept_id;
select max(age) from emp3;
select * from emp3 where age = 33;
-- 化简语法
select * from emp3 where age = (select max(age) from emp3); -- 单行单列，可以作为一个值使用
-- 方式1：关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
-- 方式2：子查询
select * from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');
-- 方式1：关联查询
select * from dept3 a join emp3 b on a.deptno = b.dept_id and (name = '研发部' and age < 30);
-- 方式2：子查询
select * from (select * from dept3 where name = '研发部') t1 join (select * from emp3 where age < 30) t2 on t1.deptno = t2.dept_id;
select * from emp3 where age > all(select age from emp3 where dept_id = '1002');
select * from emp3 where dept_id != all(select deptno from dept3);
select * from emp3 where age > any(select age from emp3 where dept_id = '1003') and dept_id != '1003';
select eid,ename from emp3 where dept_id in (select deptno from dept3 where name = '研发部' or name = '销售部');
select * from emp3 where exists(select * from emp3);
select * from emp3 where exists(select 1);
select * from emp3 where exists(select age from emp3 where age > 60);
select * from emp3 where exists(select age from emp3 where age > 20);
select * from emp3 a where exists(select age from emp3 where a.age > 20);
select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);
use mydb2;
create table t_sanguo(
eid int primary key,
ename varchar(20),
manager_id int,
foreign key (manager_id) references t_sanguo(eid)
);
insert into t_sanguo values(1,'刘协',NULL);
insert into t_sanguo values(2,'刘备',1);
insert into t_sanguo values(3,'关羽',2);
insert into t_sanguo values(4,'张飞',2);
insert into t_sanguo values(5,'曹操',1);
insert into t_sanguo values(6,'许诸',5);
insert into t_sanguo values(7,'典韦',5);
insert into t_sanguo values(8,'孙权',1);
insert into t_sanguo values(9,'周瑜',8);
insert into t_sanguo values(10,'鲁肃',8);
-- 1.查询每个三国人物及他的上级信息
select * from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
select a.ename,b.ename from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
-- 2.查询所有人物及上级
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
-- 3.查询所有人物的上级和上上级
select a.ename,b.ename,c.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid left join t_sanguo c on b.manager_id = c.eid;
create database test1;
use test1;
create table dept(
deptno int primary key, -- 部门编号
dname varchar(14), -- 部门名称
loc varchar(13) -- 部门地址
);
insert into dept values (10,'accounting','new york');
insert into dept values (20,'research','dallas');
insert into dept values (30,'sales','chicago');
insert into dept values (40,'operations','boston');
create table emp(
empno int primary key, -- 员工编号
ename varchar(10), -- 姓名
job varchar(9), -- 工作
mgr int, -- 直属领导编号
hiredate date, -- 入职时间
sal double,-- 工资
comm double, -- 奖金
deptno int -- 对应dept表的外键
);
-- 添加部门和员工 之间的主外键关系
alter table emp add constraint foreign key emp(deptno) references dept(deptno);
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-07-03',3000,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-07-13',1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','clerk',7782,'1981-01-23',1300,null,10);
create table salgrade(
grade int, -- 等级
losal double, -- 最低工资
hisal DOUBLE -- 最高工资
);
insert into salgrade values(1,700,1200);
insert into salgrade values(2,1201,1400);
insert into salgrade values(3,1401,2000);
insert into salgrade values(4,2001,3000);
insert into salgrade values(5,3001,9999);
-- 1 、返回拥有员工的部门名、部门号
select d.dname,d.deptno from dept d join emp e on d.deptno = e.deptno;
select distinct d.dname,d.deptno from dept d join emp e on d.deptno = e.deptno; -- 去重
-- 2、工资水平多余smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');
-- 3、返回员工和所属经理的姓名
select a.ename,b.ename from emp a,emp b where a.mgr = b.empno;
-- 4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select a.empno,a.hiredate,b.empno,b.hiredate from emp a,emp b where a.mgr = b.empno and a.hiredate < b.hiredate;
-- 5、返回员工姓名及其所在的部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
-- 6、返回从事clerk工作的员工姓名和所在部门名称
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno and e.job = 'clerk';
-- 7、返回部门号及其本部门的最低工资
select deptno,min(sal) from emp group by deptno;
-- 8、返回销售部（sales）所有员工的姓名
select e.ename from emp e join dept d on e.deptno = d.deptno and d.dname = 'sales';
-- 9、返回工资水平多于平均工资的员工
select e.ename from emp e where sal > (select avg(sal) from emp);
-- 10、返回与scott从事相同工作的员工
select e.ename,e.job from emp e where job = (select job from emp where ename = 'scott');
-- 11、返回工资高于30部门所有员工工资水平的员工信息
select * from emp where sal > all(select sal from emp where deptno = 30);
-- 12、返回员工工作及其从事此工作的最低工资
select job,min(sal) from emp group by job;
-- 13、计算出员工的年薪，并且以年薪排序
select ename,(sal * 12 + ifnull(comm,0)) from emp order by (sal * 12 + ifnull(comm,0)) desc;
-- 14、返回工资处于第四级别的员工的姓名
select ename from emp where sal between (select losal from salgrade where grade = 4) and (select hisal from salgrade where grade = 4);
-- 15、返回工资为二等级的职员名称、部门所在地
select * from dept a join emp b on a.deptno = b.deptno join salgrade c on grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;
select * from dept a,emp b,salgrade c where a.deptno = b.deptno and grade = 2 and b.sal >= c.losal and b.sal <= c.hisal;