1. 复合查询

1.1 多表查询

select * from `tb_1`, `tb_2`
	where dept.deptno = emp.deptno;

不加过滤条件，得到的结果被称为笛卡尔积。本质是取主表的每一条记录拼接上从表的每一条记录。

多表查询中一定会包含where条件，将主键和外键对应，可以将无意义的记录抛弃。

1.2 自连接

同一张表自己和自己拼接的叫做自连接。给两张相同的表起别名，就可以放到一起。

select * from emp wkr, emp ldr;
	where wkr.mgr = ldr.empno;

自连接的话，需要根据实际需求筛去不满足条件的数据。

1.3 子查询

子查询也称嵌套查询，也就是将select的结果作为另一个select的条件。

单行子查询

返回一行记录的查询称为单行子查询。

select * from emp where deptno=(select deptno from emp where ename='SMITH');

多行子查询

返回多行记录的查询称为多行子查询。关键字有：in，all，any。

• in 表示是否存在于集合中，存在即满足条件。
• all 表示整个集合的每一个结果
• any 表示集合中的任意一个结果

# 显示和10号部门具有的工作岗位相同的员工
select * from emp where job in (select distinct job from emp where deptno=10);
# 显示工资比10号部门所有人工资都高的员工
select * from emp where sal > all (select distinct sal from emp where deptno=10);
# 显示工资比10号部门任意员工工资高的员工
select * from emp where sal > any (select distinct sal from emp where deptno=10);

不管是单行还是多行子查询，都叫做单列子查询，返回的都是单列的一个字段的数据。

多列子查询

# 查询和SMITH的部门和岗位完全相同的所有雇员
select * from emp where (deptno, job)=(select deptno, job from emp where ename='SMITH');

from中使用子查询

from跟是的表名，既然我们查询出来的记录都可以看作表结构。这里就是一个数据查询的技巧，把子查询当作临时表使用。

# 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select ename, emp.deptno, sal, myavg
	from emp,
			 (select deptno, avg(sal) as myavg from emp group by deptno) as avg_tb
	where emp.deptno=avg_tb.deptno and sal>myavg;

# 显示每个部门的部门名，编号，地点和人员数量
select dname, dept.deptno, loc, cnt
	from emp, dept,
			 (select deptno, count(*) cnt from emp group by deptno) as cnt_tb
	where cnt_tb.deptno=emp.deptno and cnt_tb.deptno=dept.deptno;

合并查询

合并查询是就是合并多个 select 的查询结果，可使用集合操作符 union，union all。

关键字	解释
union	取并集，将多个 select 结果合并到一起，自动去掉重复行
union all	取并集，将多个 select 结果合并到一起，但不去重

select * from emp where sal > 2500 union     select * from emp where job='MANAGER';
select * from emp where sal > 2500 union all select * from emp where job='MANAGER';

1.4 OJ连接

• https://www.nowcoder.com/practice/23142e7a23e4480781a3b978b5e0f33a
• https://www.nowcoder.com/practice/355036f7f0c8429a85281f7ac05b457a
• https://www.nowcoder.com/practice/32c53d06443346f4a2f2ca733c19660c
• https://www.nowcoder.com/practice/e50d92b8673a440ebdf3a517b5b37d62

 

2. 内外连接

2.1 内连接

内连接就是利用where对笛卡尔集进行筛选，我们前面使用的就是内连接。这样的写法更加规范。

select `field` from `tb_1` inner join `tb_2` on 连接条件 and 其他条件;

2.2 外连接

外连接分为左外连接和右外连接。左外连接以左表为主，右外连接以右表为主。

副表中缺少和主表匹配的数据，则该部分显示为空。附表中多余而匹配不上的数据，抛弃不作显示。

select `field` from `tb_1` left  join `tb_2` on 连接条件 and 其他条件;
select `field` from `tb_1` right join `tb_2` on 连接条件 and 其他条件;

外连接就是为了显示出匹配不上的数据，从而确定哪些数据不满足条件。

2.3 OJ练习

• https://leetcode.com/problems/rank-scores/
• https://leetcode.com/problems/exchange-seats/description/