MySQL 高级查询（内连接、外连接、自然连接、子查询）

• 1、关联查询,连接查询,内连接
• • 1.1、where 子句
  • 1.2、inner join…on…
  • 1.3、inner join…using…
• 2、自然连接
• 3、外连接
• • 3.1、左外连接:left [outer] join…on…
  • 3.2、右外连接:right [outer] join…on…
• 4、子查询
• 5、集合查询

Test Table emp and dept:

链接：https://pan.baidu.com/s/12p039KI59UDAJ9llXAIk7Q

提取码：58ry

dept table：

字段名	释义
deptno	部门编号
dname	部门名
loc	地址

emp table：

字段名	释义
empno	职工编号
ename	职工姓名
job	工作岗位
mgr	与职工编号的上下级关系
hiredate	聘用日期
sal	工资
comm	佣金
deptno	部门编号

1、关联查询,连接查询,内连接

笛卡尔积：

emp 表 15 条记录,dept 表 4 条记录。

连接查询的笛卡尔积为 60 条记录。

内连接:

不区分主从表，与连接顺序无关。两张表均满足条件则出现结果集中。

1.1、where 子句

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

1.2、inner join…on…

select * from emp
inner join dept
on emp.deptno = dept.deptno;
           

1.3、inner join…using…

select * from emp
INNER JOIN dept
using(deptno);
           

2、自然连接

寻找两表中字段名称相等的字段进行连接，会自动去重重复列。

3、外连接

有主从表之分，与连接顺序有关。以驱动表为依据，匹配表依次进行查询；匹配表中找不到数据，则以null填充。

3.1、左外连接:left [outer] join…on…

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

3.2、右外连接:right [outer] join…on…

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

4、子查询

即嵌套查询，将一个查询结果作为另一个查询条件或组成部分的查询。

（1）单行子查询：

# 查询工资大于7788号员工的所有员工信息
select * from emp where sal >(select sal from emp where empno = 7788);
           

（2）多行子查询：

返回多值可以使用 any 或 all 来修饰。

=any 相当于 in, <any 小于最大值，>any 大于最小值；

<>all 相当于 not in, >all 大于最大值，<all 小于最小值。

in 和 exists 的区别：

in 先执行子查询，在执行主查询; exists 先执行主查询;

exists 子查询不返回具体结果，返回 true 值出现在结果集，否则不出现。

# 查询超过所在部门平均工资的员工信息
select * from emp e1 
where sal > (
select avg(sal) from emp e2 
where e1.deptno = e2.deptno);

# 查询薪水大于2000的部门名称
select dname from dept d where deptno in
(select deptno from emp e where sal > 2000);
# 或
select dname from dept d where EXISTS 
(select * from emp e where sal > 2000 and d.deptno=e.deptno);

           

5、集合查询

UNION：

并集，所有的内容都查询，重复的显示一次。

UNION ALL：

并集，所有的内容都显示，包括重复的。

select * from emp where deptno = 20
UNION ALL
select * from emp where sal <=2000;