MySQL复合查询(重点)

前面我们讲解的mysql表的查询都是对一张表进行查询，在实际开发中这远远不够。

基本查询回顾

查询工资高于500或岗位为MANAGER的雇员，同时还要满足他们的姓名首字母为大写的J

mysql> select * from emp where (sal>500 or job='MANAGER') and ename like 'J%';//用where逻辑筛选

 按照部门号升序而雇员的工资降序排序

mysql> select * from emp order by deptno,sal desc;

 使用年薪进行降序排序(月薪*12+奖金)

因为有的雇员的comm(奖金)为NULL，而NULL不参与运算，所以对于comm为NULL的奖金为0用到函数ifnull();

mysql> select ename,sal*12+comm 年薪 from emp where ename='SMITH' order by 年薪 desc;//对于SMITH他的年薪不为NULL，但为什么最后是NULL，因为comm是NULL，任何数与NULL计算最后都是NULL，所以导致年薪最后为NULL
mysql> select ename,sal*12+ifnull(comm,0) 年薪 from emp order by 年薪 desc;//先有数据才能排序，所以别名能用

 显示工资最高的员工的名字和工作岗位(先找到最高工资，然后找到最高工资的人)

mysql> select ename,job from emp where sal=(select max(sal) from emp);

sql中允许在一条sql内部执行对应的select查询，叫做子查询

select max(sal) from emo本身返回数值，让他的值等于where的sal查询即可（里部的select叫做子查询）

 显示工资高于平均工资的员工信息(用到子查询)

mysql> select * from emp where sal>(select avg(sal) from emp);//子查询查到平均工资，然后外部where筛选

 显示每个部门的平均工资和最高工资

每个部门的注定了要分组了，按照部门分组再求平均和最高(就是将emp这一张表逻辑上分成几组子表在进行聚合统计)

mysql> select deptno,avg(sal),max(sal) from emp group by deptno;
如果觉得平均工资显示的不优雅，可以使用format(avg(sal),2)保留两位精度

select deptno, format(avg(sal), 2) , max(sal) from emp group by deptno;

 显示平均工资低于2000的部门号和它的平均工资

首先把每个部门的平均工资算出(注定要分组)，再筛选出低于两千(注定用having筛选，用where的话不对)

mysql> select deptno,avg(sal) 平均 from emp group by deptno having avg(sal)<2000;

 显示每种岗位的雇员总数，平均工资

mysql> select job,count(*),format(avg(sal),2) from emp  group by job;

多表查询 

实际开发中往往数据来自不同的表，所以需要多表查询。本节我们用一个简单的公司管理系统，有三张 表EMP,DEPT,SALGRADE来演示如何进行多表查询。

案例

显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

select count(*) from emp,dept=(count(*) from emp)*(select count(*) from dept;)

如果不经过筛选的话得到的信息是多余的且错误的；其实我们只要emp表中的deptno = dept表中的deptno字段的记录

mysql> select * from emp,dept where emp.deptno=dept.deptno;//筛选

如果是唯一的话就不用  表名.列名  直接用列明即可

 显示部门号为10的部门名，员工名和工资

mysql> select ename, sal,dname from emp, dept where dept.deptno=dept.deptno and  dept.deptno = 10;

 显示各个员工的姓名，工资，及工资级别

mysql> select ename, sal,grade from emp, salgrade where sal between losal and hisal;//这个where筛选相当于在进行笛卡尔积的时候进行过滤过滤掉不符合的信息

 笛卡尔积本质就是穷举，如有筛选就筛选；

 自连接

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

不能将同一张表写两次：mysql> select * from salgrade,salgrade;
ERROR 1066 (42000): Not unique table/alias: 'salgrade'

但是能将重命名后查询：

mysql> select * from salgrade t1,salgrade t2;
mysql> select * from salgrade t1,salgrade;
mysql> select * from salgrade ,salgrade t;//只要重命名任意一个就行，不要在sql中出现一样的表名

 案例

 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

1.先找到领导的编号：mysql> select mgr from emp where ename='FORD';

2.再根据编号找信息：mysql> select ename,empno from emp where empno=7566;

方法1(子查询)：mysql> select ename,empno from emp where empno=(select mgr from emp where ename='FORD');//使用子查询

方法2(多表查询--自查询)：mysql> select leader.empno,leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename='FORD';//这里from重命名where能用并且select也能用因为sql语句先执行from；因为要先做笛卡尔积，所以别名可以先识别

子查询

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

 单行子查询

返回一行记录的子查询

显示SMITH同一部门的员工

mysql> select * from emp where deptno=(select deptno from emp where ename='SMITH');//内部查询出的结果作为外部查询的条件

多行子查询

返回多行记录的子查询

查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的;

首先查出10号部门的工作岗位：mysql> select job from emp where deptno=10;//如有重复的加distinct即可

in关键字:只要满足集合的一个条件即可

查找：mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10);//查出所有的

最终：mysql> select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10;

那如果查询出后再查出部门名称怎么做呢？

mysql> select * from (select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10) t1,dept where t1.deptno=dept.deptno;

一个sql的查询结果本身就是表结构，我们也可以将查出来的表结构进行进行笛卡尔积；

子查询不仅能出现在where后面充当判断条件也可以出现在from后面充当表

最后：mysql> select ename,job,sal,dname from (select ename,job,sal,deptno from emp where job in (select job from emp where deptno=10) and deptno <> 10) t1,deppt where t1.deptno=dept.deptno;

 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

首先筛选出部门30的所有员工的最高工资：mysql> select max(sal) from emp where deptno=30;
结果：筛选出工资大于部门30的所有员工的最高工资的人：mysql> select * from emp where sal > (select max(sal) from emp where deptno=30);

all关键字：都大于集合的才满足

结果：mysql> select * from emp where sal > all (select sal from emp where deptno=30);//这里不挑选出最大的，而是列出所有30部门员工的sal，只有员工的薪水都大于all集合里的薪水才满足条件；

用all相当于我比你们班任何一个人的成绩都要高相当于我比你们班的第一名成绩还要高(学霸思想)

最终查到的都比部门30的员工任意sal都要高

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

all关键字：都大于集合的才满足

结果：mysql> select * from emp where sal > any (select sal from emp where deptno=30);

用any相当于我只要比你们班一个人高就行(学渣思想)

最终查到的最低sal只要比部门30的最低sal高就行

多行子查询也是单列

多列子查询

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

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

首先找到SMITH的部门和岗位：mysql> select deptno,job from emp where ename='SMITH';
最终：mysql> select * from emp where (deptno,job) = (select deptno,job from emp where ename='SMITH') and ename <> 'SMITH';//多列用括号，得一一对应

目前全部的子查询，全部都在where语句中充当判断条件；

任何时刻查询出来的临时结构本质上也是表结构； 

在from子句中使用子查询

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

案例

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

先找出每个部门的平均工资(分组与聚合)：mysql> select deptno,avg(sal) from emp group by deptno;
筛选：mysql> select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) tmp where emp.deptno=tmp.deptno and emp.sal>tmp.myavg;

注意：子查询语句出现在from子句中一定要起别名

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

mysql> select ename,sal,t1.deptno,mymax from emp t1,(select deptno,max(sal) mymax from emp group by deptno) t2 where t1.deptno=t2.deptno and t1.sal=t2.mymax;

 显示每个部门的信息（部门名，编号，地址）和人员数量(聚合出的)

先找到每个组的人数：mysql> select count(1) from emp group by deptno;

使用子查询：mysql> select * from dept,(select deptno,count(1) from emp group by deptno) t1 where dept.deptno=t1.deptno;//几乎所有的笛卡尔积后都要加判断条件

使用多表:select dept.dname, dept.deptno, dept.loc,count(*) '部门人数' from emp, dept where emp.deptno=dept.deptno group by dept.deptno,dept.dname,dept.loc;

//为什么已经按照deptno分过组了还要按照dname和loc分组？

一方面是防止按照deptno分过后结果不对再按照dname分组；另一方面mysql不允许在分组中出现未被分组的列，例如：select dept.dname, dept.deptno, dept.loc,count(*)  from emp, dept where emp.deptno=dept.deptno group by dept.deptno;这样就不行，要求在使用 GROUP BY 时，SELECT 中的列要么是聚合函数，要么是在 GROUP BY 子句中明确列出的列。

解决多表问题的本质：想办法将多表转化为单表，所以在MySQL中，所有的select的问题全部都可以转化成单表问题！--多表查询的指导思想 

合并查询

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

union

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

将工资大于2500或职位是MANAGER的人找出来

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

mysql> select * from emp where sal> 2500 union select * from emp where job ='MANAGER';//自动去重

 union all

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

 将工资大于25000或职位是MANAGER的人找出来

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

两个表拼接合集的话列属性得一样

mysql> select ename,job,sal from emp where sal> 2500 union all select * from emp where job ='MANAGER';
不能左边的表是筛选出三列跟右边的全列信息拼接，这样拼不上