MySQL多表查询

2.多表查询

笛卡尔积：两个集合A,B的所有组合情况（多表查询时需要消除）

分类

连接查询

内连接：查询A,B交集

SELECT 字段列表 FROM 表1，表2 WHERE 连接条件...；---隐式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...；---显式内连接
​
--显示内连接
select emp.name,dept.name from emp join dept on emp.dept_id=dept.id;
​
--隐式内连接
select e.name,d.name from  emp e,dept d where e.dept_id=d.id;

外连接：

左外连接：查询左表所有数据以及两张表的交集部分数据

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 连接条件 ...; 

右外连接：查询右表所有数据以及两张表的交集部分数据

SELECT 字段列表 FROM 表1 RIGHT JOIN 表2 ON 连接条件...;

1.查询emp表的所有数据和对应部门信息
--左外连接---掌握
select e.*,d.* from emp e left join dept d on e.dept_id=d.id;
​
2.查询dept表的所有数据，和对应的员工信息
--右外连接---了解
select e.*,d.* from emp e right join dept d on e.dept_id=d.id;
​
--右外连接用左连接实现
select e.*,d.* from dept d  left join emp e on e.dept_id=d.id;

自连接：当前表与自身的连接查询，必须使用别名

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 连接条件...;
​
---自连接--一定要起别名--当成两个张表操作
​
--查询所有员工对应的领导
select a.name '员工',b.name '领导' from emp a join emp b where a.managerid=b.id;
​
​
----查询所有员工对应的领导，没有领导的员工也要查询出来
select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid=b.id;

联合查询

--联合查询--把查询结果合并返回
--union all---将全部数据直接合并在一起
--将薪资低于5000和年龄大于50岁的员工信息全部查出来
select * from emp where salary < 5000
union all
select * from emp where age > 50;
--union---会对合并之后的数据去重
select * from emp where salary < 5000
union 
select * from emp where age > 50;
--注意点：列数保持一致，字段类型保持一致

子查询---嵌套查询

SELECT * FROM T1 WHERE column1=(SELECT column1 FROM T2);
子查询外部语句可以式INSERT/UPDATE/DELETE/SELECT任何一个
​
位置分:WHERE之后、FROM之后、SELECT之后

1.标量子查询(子查询结果为单个值)

--1.查询销售部所有员工的信息
--a.查询销售部
select * from dept where name='销售部';
--b.查询员工信息
select * from emp where dept_id=(select id from dept where name='销售部');
​
--2.查询在方东白入职日期之后的员工信息
--a.查询方东白的入职信息
select entrydate from emp where name='方东白';
--b.查询员工信息
select * from emp where entrydate >(select entrydate from emp where name='方东白');

2.列子查询(子查询结果为一列)

--1.查询销售部和市场部的所有员工信息
--a.查询销售部和市场部
select id from dept where name='销售部' or name='市场部';
--b.查询员工信息
select * from emp where dept_id in(select id from dept where name='销售部' or name='市场部');
​
--2.查询比财务部所有员工工资都高的员工信息
select * from dept where name='财务部';
select salary from emp where dept_id=(select * from dept where name='财务部');
select * from emp where salary > all(select salary from emp where dept_id=(select * from dept where name='财务部'));
​
--3.查询比研发部任意一人工资高的员工信息
select * from dept where name='研发部';
select salary from emp where dept_id=(select id from dept where name='研发部');
select * from emp where salary >any (select salary from emp where dept_id=(select id from dept where name='研发部'));
​

3.行子查询(子查询结果为一行)

--1.查询与张无忌工资和领导相同的员工信息
select salary,managerid from emp where name='张无忌';
select * from emp where (salary,managerid)=(select salary,managerid from emp where name='张无忌');

4.表子查询(子查询结果为多行多列)

--1.查询与鹿杖客和宋远桥职位薪资相同的员工信息
select job,salary from emp where name='鹿杖客' or name='宋远桥';
select * from emp where (job,salary) in (select job,salary from emp where name='鹿杖客' or name='宋远桥');
​
--2.查询入职日期是2006-01-01之后入职的员工信息及其部门信息
select * from emp where entrydate >'2006-01-01';
select e.*,d.name from (select * from emp where entrydate >'2006-01-01')  e left join dept d on e.dept_id=d.id;

练习：