MySQL--复合查询

之前学过了基本的查询，虽然已经够80%的使用场景了，但是依旧需要了解剩下的20%。

一、多表笛卡尔积（多表查询）

  以前我们使用基本查询的时候，from后面就跟一张表名，在多表查询这里，from后面可以跟多张表名。

这里有两张测试表，一张是用户的成绩表，还有一张是用户的信息表。

假设我们直接在from后面跟上这两张表

我们发现它们的组合方式就是拿其中一张表的某一条数据，依次跟另一张表的所有数据进行组合。就是穷举组合。

但是我们发现，如果id不相等的话，查出来的结果是没有意义的，所以我们可以加判断条件，使查询结果合理，并且id列是重复的，我们可以指定显示哪些列。

select exam_result.id,name,chinese,qq,age from exam_result,user where exam_result.id = user.id;

在mysql下，一切皆为表。

比如，我们再来查询，年龄在13岁到15岁之间的。

二、自连接 

自连接是指在同一张表连接查询。

这样子写是不行的。

我们可以对表进行重命名后，再作查询。

 

案例1：找到数学成绩比孙悟空低的同学

方法1：可以用子查询的方式

select * from exam_result where math < (select math from exam_result where name = '孙悟空');

 

方法2：自连接。直接在同一张表里面自连接查询，记得重命名。 

select * from exam_result t1,exam_result t2 where t2.name='孙悟空' and t1.math<t2.math;

 另外我们其实只需要t1表中的信息，那么可以指定一下

三、子查询 

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询。

单行子查询 

 比如之前的案例：找到数学成绩比孙悟空低的同学

select * from exam_result where math < (select math from exam_result where name = '孙悟空');

因为在子查询那里查出来的条件是单列单行的，所以叫做单行子查询

多行子查询 

与单行不同的是，虽然多行的子查询条件还是只有一列，但是有多行。

这里主要用到三个关键字：in，all，any。

以之前的员工表为例：
in案例：查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10自己

select ename,job,sal,deptno from emp where job in (select distinct job from
emp where deptno=10) and deptno<>10;

 

all关键字案例：显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

select ename, sal, deptno from EMP where sal > all(select sal from EMP where
deptno=30);

 any关键字：

显示工资比部门 30 的任意员工的工资高的员工的姓名、工资和部门号（包含自己部门

的员工）

select ename, sal, deptno from EMP where sal > any(select sal from EMP where
deptno=30);

多列子查询

单行子查询是指子查询只返回单列，单行数据；多行子查询是指返回单列多行数据，都是针对单列而言的，而多列子查询则是指查询返回多个列数据的子查询语句

案例： 查询和 SMITH 的部门和岗位完全相同的所有雇员，不含 SMITH 本人

select ename from EMP where (deptno, job)=(select deptno, job from EMP
where ename='SMITH') and ename <> 'SMITH';

总结：任何时刻，我们查出来的临时结构，本质在逻辑上也是表结构。

在from子句中使用子查询

子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用。 

案例：显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资

//获取各个部门的平均工资，将其看作临时表
select ename, deptno, sal, format(asal,2) from EMP,
(select avg(sal) myavg, deptno dt from EMP group by deptno) tmp
where EMP.sal > tmp.myavg and EMP.deptno=tmp.dt;

 

案例：查找每个部门工资最高的人的姓名、工资、部门、最高工资 

select EMP.ename, EMP.sal, EMP.deptno, ms from EMP,
(select max(sal) mymax, deptno from EMP group by deptno) tmp
where EMP.deptno=tmp.deptno and EMP.sal=tmp.mymax;

 案例：显示每个部门的信息（部门名，编号，地址）和人员数量

-- 1. 对EMP表进行人员统计
select count(*), deptno from EMP group by deptno;
-- 2. 将上面的表看作临时表
select DEPT.deptno, dname, dept_num, loc from DEPT,
(select count(*) dept_num, deptno from EMP group by deptno) tmp
where DEPT.deptno=tmp.deptno;

这里不仅使用了子查询，还使用了多表查询。

总结：解决多表问题的本质就是：想办法将多表转化成单表，在mysql中，所有的select问题全都可以转化成单表问题。（多表查询的指导思想）

四、合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。

跟多表查询还是不同的。

union：

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

 

如图上，就是将第一个数学大于70的结果表跟第二个语文大于85的结果的表合并在一起。

union all就是不去掉重复的行。 

五、表的内外连接（拓展）

1.内连接 

内连接实际上就是利用 where 子句对两种表形成的笛卡儿积进行筛选，我们前面学习的查询都是内连接，也是在开发过程中使用的最多的连接查询。

语法：

select 字段 from 表1 inner join 表2 on 连接条件 and 其他条件；

也就是说，之前的笛卡尔积就是内连接的一种。

比如之前的员工表，案例：显示SMITH的名字和部门名称

 之前的写法：

select ename, dname from EMP, DEPT where EMP.deptno=DEPT.deptno and
ename='SMITH';

标准内连接写法：

select ename, dname from EMP inner join DEPT on EMP.deptno=DEPT.deptno and
ename='SMITH';

其中and换成where也可以。

 

2.左外连接 

外连接分为左外连接和右外连接

如果联合查询，左侧的表完全显示我们就说是左外连接。

 语法：

select 字段名 from 表名1 left join 表名2 on 连接条件

先创建一个学生表和成绩表，并插入数据

-- 建两张表
create table stu (id int, name varchar(30)); -- 学生表
insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');
create table exam (id int, grade int); -- 成绩表
insert into exam values(1, 56),(2,76),(11, 8);

并且我们注意到，id是有关联性的，但是两张表的信息不是完全吻合的，就是为了方便测试外连接。 

案例：查询所有学生的成绩，如果这个学生没有成绩，也要将学生的个人信息显示出来

select * from stu left join exam on stu.id=exam.id;

 

我们发现3号和4号即便没有成绩也会显示出来。如果我们用内连接的话，就不会显示出来了

 

 

3.右外连接 

如果联合查询，右侧的表完全显示我们就说是右外连接。

语法：

select 字段 from 表名1 right join 表名2 on 连接条件;

其实从功能上来说，不需要右外连接也行，因为我们只需要把顺序换一下就可以用左外连接实现右外连接的功能。 

 案例：

对 stu 表和 exam 表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要

显示出来

 

select * from stu right join exam on stu.id=exam.id;