mysql--多表查询
一、联合查询
-
作用：合并结果集就是把两个select语句的查询结果合并到一起！
-
合并结果集有两种方式：
UNION：合并并去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
UNION ALL：合并但不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。
案例1，UNION
select * from emp where deptno=30
union
select * from emp where job='SALESMAN';
查询结果会去重：
案例2，UNION ALL
select * from emp where deptno=30
union all
select * from emp where job='SALESMAN';
查询结果不会去重：
注意：
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。
mysql实现交集效果：通过子查询
create table emp_clerk as select * from emp where job='CLERK';
create table emp_30 as select * from emp where deptno=30;
select * from emp_clerk where empno in(
select empno from emp_30
);
查询结果：
差集：
存在与30部门表中的数据, 但是不存在于文员表的记录
select * from emp_30 where empno not in(
select empno from emp_clerk
);
查询结果：
二、表连接查询
多张表之间进行两两连接，两张表的每条记录都相互连接一次，连接查询会产生笛卡尔积
笛卡尔积 tb_class:4条数据 tb_student：1 总记录 4*1=4条
select * from tb_class,tb_student;
查询结果：
数据太多，无法从中找到我们需要的数据，那么就要从笛卡尔积筛选需要的数据，添加条件
内连接，外连接：都连接条件，从笛卡尔积结果集筛选
（一）内连接
- [inner] join A内连接B 查询结果集中记录 满足条件才显示
- A的记录一定有一条与B对应
标准sql语法： select * / 列.. from 表1 [innner] join 表2
on 连接条件; 推荐
非sql标准语法： select * / 列.. from 表1 表2 where 连接条件;
（二）外连接
外连接的特点：让某些表的数据数据全部显示，不管是否满足条件
1.左外连接
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
join 左边(前面)的表全部显示 left [outer] join
举例：
查询部门以及员工信息, 所有部门都显示
select * from dept d left join emp1 e
on e.deptno = d.deptno;
2.右外连接
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
join 右边(前面)的表全部显示 right [outer] join
举例：
查询部门以及员工信息, 所有部门都显示
select * from emp1 e right join dept d
on e.deptno = d.deptno;
3.全外连接
两张表的数据全部显示 full outer join(MYSQL不支持它的写法)
mysql全连接： 把左外连接 联合(并集) 右外连接
select * from emp1 e left join dept d
on e.deptno = d.depton
union
select * from emp1 e right join dept d
on e.deptno = d.depton
去重关键字 distinct
distinct 列名,列名2... 多个列组合去重
select distinct dname from emp e join dept d
on e.deptno = d.deptno;
三、自连接
自己连接自己
SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ;
案例1：
列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称
1.找表： emp e1 emp e2 dept d
2.找条件：受雇日期早于直接上级的 连接条件 e1.mgr=e2.empno
emp e1=dept d连接条件：e1.deptno=d,deptno
3.找编号、姓名、部门
select e1.empno,e1.ename,d.dname from emp e1 join dept d
on e1.deptno=d.deptno
join emp e2
on e1.mgr=e2.empno and e1.hiredate < e2.hiredate;
结果：
案例2：
列出所有员工的姓名及其直接上级的姓名
select e.ename,w.ename
from emp e join emp w on e.empno=w.mgr;
结果：
四、子查询
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
语法:
SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 );
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。
分类:
子查询出现的位置：
-
where后，作为条件的一部分；
-
from后，作为被查询的一条表；
-
SELECT之后 ,作为被查询的一列;
当子查询出现在where后作为条件时，还可以使用如下关键字：
-
any
-
all
查询员工的信息,以及他的部门信息,以及该部门的人数 count(1)
- 找表: emp dept
- 找条件: 表连接条件
- 找列: 员工的信息,以及他的部门信息,以及该部门的人数
- 子查询作为表使用, 一定要给子查询取别名
select e.*,d.*,num from emp e join dept d
on e.deptno = d.deptno
join (select deptno,count(1) num from emp group by deptno) t
on e.deptno = t.deptno;
配合子查询使用的关键字
- any /some 子查询返回列表中，有任意一个满足即可
- all 子查询返回列表的所有值都必须满足
> any(select 子查询) > 最小值
< any(select 子查询) < 最大值> all(select 子查询) > 最大值
< all(select 子查询) < 最小值
案例1：
查询薪水比30部门任意一个小的员工信息
select * from emp where sal < any(select sal from emp where deptno = 30 );
案例2：
查询薪水比30部门所有人薪水都高的员工
select * from emp where sal > all(select sal from emp where deptno = 30);