MySQL多表联查

联合查询

mysql只支持并集操作: union union all

进行联合查询, 要求每个查询的结果集的列的数量以及列名要一样

实际开发中,联合查询使用的不多

并集操作:  union : 去重

SELECT * FROM  emp WHERE deptno=20
UNION
SELECT * FROM emp WHERE job = 'SALESMAN';

union all : 不去重

SELECT * FROM  emp WHERE deptno=20
UNION ALL
SELECT * FROM emp WHERE sal>2000;

mysql实现交集, 差集

Oracle数据库提供的交集 intersect,差集操作 minus

mysql不支持

交集实现:  in子查询

SELECT * 
FROM emp 
WHERE deptno=20
AND empno IN (SELECT empno
FROM emp
WHERE sal>2000
);

差集   not in

SELECT * 
FROM emp 
WHERE deptno=20
AND empno IN (SELECT empno
FROM emp
WHERE sal>2000
);

表连接的查询 

自然连接

没意义, A与B表连接, A表中的每一行记录都要与B表每行记录连接一次, 得到的一个笛卡尔积: A(10) * B(4) = 40

内连接

A 内连接B  A中的一条记录 与B中的一条记录对应

结果集中记录, 都是满足条件的

标准sql:   A [inner]  join B   on 连接条件

两张表的连接, 连接条件个数 >=1

如果两张表有相同名字的列, 需要使用别名/表名.列名区分

SELECT * FROM  emp  JOIN dept  
ON emp.deptno = dept.deptno  AND emp.job = 'CLERK';

给表名取别名

select * from  emp e join dept d  
on e.deptno = d.deptno;

非标准写法

select * from  emp e , dept d  
where e.deptno = d.deptno;

自连接:  表与自己连接,属于内连接或者外连接的一种

 查询员工的姓名以及他的领导的姓名

select e1.ename 员工姓名,  e2.ename 直接领导  from  emp e1
join emp  e2
on e1.mgr   =  e2.empno;

 外连接

左外连接

select * from emp e  left join dept d
on e.deptno = d.deptno;

右外连接

select * from emp e  right join dept d
on e.deptno = d.deptno;

全连接

select * from emp e  left join dept d
on e.deptno = d.deptno
union
select * from emp e  right join dept d
on e.deptno = d.deptno;

非关联子查询

select * from emp where (job,sal) in (
  select job,sal from emp where ename = 'MARTIN' 
) and ename <> 'MARTIN';

* 先执行子查询
* 再执行主查询
* 进行循环判断

子查询使用的位置:

 1. from   作为被查询的一条表  配合表连接查询

2. where  作为条件的一部分   条件的值

3. select 作为被查询的一列

标量子查询

子查询结果的单行单列

大部分作为条件的一部分

配合比较运算符: = <> > >= < <=

#查询比ALLEN工资高的员工
#1. 查询ALLEN的工资  1600
select sal from emp where ename = 'ALLEN';
#2.查询比ALLEN工资高的员工
select * from emp where sal > 1600;

#合并, 把1600使用上面的查询提交
SELECT * FROM emp WHERE sal >( SELECT sal FROM emp WHERE ename = 'ALLEN' );

列子查询

子查询返回的结果是一列（可以是多行）

配合运算符: in, not in , any  all

#查询比30部门所有员工工资都高员工
# 查询大于30部门最高工资的员工
# 1.查询30部门最高工资
select max(sal) from emp where deptno = 30;
# 2. 大于1的信息
select * from emp where sal >  (
  select max(sal)  from emp where deptno = 30
);

使用all(子查询)  all: 所有

> all()  大于所有  间接的含义: 大于最大值  

<all() 小于所有  间接的含义: 小于最小值  

select * from emp where sal >  all(
  select sal  from emp where deptno = 30
);

# 查询有奖金的员工信息
# 1. 查询有奖金的员工编号
select empno from emp where comm is not null;
# 2. 根据员工编号查询员工信息
# 注意: 比较运算符 后面的值只能单个值
# = 后面是多个值, 使用in替换
# <> != 后面是多个值, 使用not in替换
# > <后面多个值,  >/< all/any
select * from emp where empno in (
  select empno from emp where comm is not null
);
# 查询没有奖金的员工信息
select * from emp where empno not in (
  select empno from emp where comm is not null
);

any/some  任意一个

>/< any   >any(子查询)  大于任意一个   间接含义: 大于最小值

<any(子查询) 小于任意一个    小于最大值

#查询比30部门任意一个员工工资都低的员工信息
select * from emp where sal < any(
  select sal from emp where deptno = 30
);

行子查询

子查询返回的结果是一行（可以是多列）

常用的操作符：= 、<> 、IN 、NOT IN

#1. 查询MARTIN的工作和工资
select job,sal from emp where ename = 'MARTIN';
#2. 查询工作与薪水与1一样的员工
select * from emp where job = (
  select job from emp where ename = 'MARTIN'
)
and sal = (
   select sal from emp where ename = 'MARTIN'
)
and ename <> 'MARTIN';

# 简化 =替换为in   多列使用小括号括起来
select * from emp where (job,sal) in (
  select job,sal from emp where ename = 'MARTIN' 
) and ename <> 'MARTIN';

表子查询

子查询返回的结果是多行多列

一般为表使用, 在from 中使用

#查询每个部门的人数 (emp), 以及部门名称(dept)
select dname,count(1) from emp e join dept d
on e.deptno = d.deptno
group by e.deptno

#1. 查询每个部门的人数
select deptno, count(1) from emp group by deptno;

把1与dept表进行表连接

注意: 如果子查询作为表使用, 一定要给子查询取别名

如果子查询查询的列使用函数, 一定要给函数列取别名

SELECT
	d.dname,
	t.num
FROM
	dept d
	left JOIN ( SELECT deptno, count(1) num FROM emp GROUP BY deptno ) t ON d.deptno = t.deptno;

exists(子查询)   是否存在, 是否有结果 如果有, 条件为true, 如果没有,条件为false

# 查询有人的部门的名称
#使用内连接
select distinct dname from  emp  e  join  dept  d
on e.deptno = d.deptno;

# 使用exists的子查询
select dname from dept d
where EXISTS( select * from emp e where e.deptno = d.deptno );

关联子查询运行原理

select d.dname, (select count(1) from emp e group by deptno having e.deptno = d.deptno ) num from dept d;

* 先查询主查询
* 循环获取主查询的一条记录
* 执行子查询

子查询与主查询有关联的,不能独立运行