Mysql表的复合查询

1.基本查询

使用scott案列

—-来源csdn：

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

查询雇员，从emp表中查询，sal>500 or job==’MANAGER’表示查询工资高于500或岗位为MANAGER， ename like ‘J%’表示查询ename为J开头。

也可以使用函数进行截取ename来进行判断：

substring(ename,1,1)=’J’;

案例2：将雇员按部门号升序而工资降序进行排序：

—-表示将相同部门号的雇员按工资，以降序排列

order by deptno asc,sal desc;

不显示其他信息：

案例3：按年薪降序进行排序

年薪等于sal*12+comm comm为奖金

按年薪排：

加上奖金（comm可为null，当comm为null时，不能参加运算）

ifnull(comm,0)表示comm若为null就取0,若不为null就取comm

案例4：显示工资最高的员工的名字和工作岗位：

显示一个最高：

但是可能有多个员工工资都为最高工资：

通过子查询和聚合函数进行查找

先通过子查询select max（sal） from emp找到最高工资，再交给where进行筛选：

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

案例4：显示工资高于平均工资的员工名和工资

同样通过select子句找出平均工资，再交给where进行筛选：

select ename,sal from emp where sal>(select avg(sal) from emp);

案例5：显示每个部门的平均工资和最高工资,只显示整数部分

通过format进行格式化

通过将每个部门分组(group by)再寻找平均工资和最高工资

案例6:显示平均工资低于2000的部门号和它的平均工资：

先算每个部门的平均工资：

再通过having对聚合函数处理的数据进行筛选：

select deptno avg(sal) 平均工资 from emp group by deptno having 平均工资>2000;

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

先算每种岗位的员工总数：

select  count(*) from emp group by job;

再算平均工资：

select count(*),avg(sal) from emp group by job;

再优化显示：

select job 岗位，count(*) 岗位员工总数，format(avg(sal),0) 岗位平均工资 from emp group by job;

2.多表查询

实际开发中数据往往来自不同的表，所以需要多表查询。

同样引用

来源的soctt数据库进行演示：

案例1：显示雇员名，工资和部门名

其中的部门名不属于员工表，而是部门表，需要从两张表进行整合：

先拿到两张表的全部数据：select * from emp,dept;

发现是将emp表中的一个数据与dept表中的所有数据进行穷举：

每一个emp表中的数据都会与dept表中的所有数据进行组合。

——称为两张表中的笛卡尔积，将两张表中的所有数据都进行了组合。

但是一个emp表中的数据只有一个dept表中的数据对它有意义，所以要将对它没用的数据筛选出去：

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

只显示有意义的数据：

再筛选出要求打印的员工名，工资，部门名：

select ename,sel,dname from emp,dept where emp.deptno=dept.deptno;

案例2：显示部门号为10的部门名，员工名和工资

3.自连接

自连接时指在同一张表连接查询—–对一张表中的数据做笛卡尔积

如：

dept表：

将同一张表中的数据进行笛卡尔积，因为表名不能重复出现，所以用as进行重命名：

select * from dept as t1,dept as t2;

案例1：显示员工CLARK的上级领导的编号和姓名—mgr是上级编号，同时也是上级的empno

先在员工表中找CLARK的领导的编号：select mgr from emp where ename=’CLARK’;

再在员工表中找编号为7839的员工姓名：

select enam from emp where empno=7839;

就找到了CLARK的领导姓名

通过子查询来一条sql实现：

select ename,empno where empno=(select mgr from emp where ename=’CLARK’);

或通过自连接实现一条sql语句实现：

先做笛卡尔积，再筛选出员工CLARK与同一张表中所有员工组合的数据：

select * from emp e1,emp e2 where e1.ename=’CLARK’;

再筛选出与e1表中的CLARK的mgr编号对应的empno编号

select * from emp e1,emp e2 where e1.ename=’CLARK’ and e1.mgr=e2.empno;

再优化，仅打印领导编号和姓名：

select e2.empno,e2.ename from emp e1,emp e2 where e1.ename=’CLARK’ and e1.mgr=e2.empno;

4.子查询

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

select 和子查询后的表结构都能进行笛卡尔积

4.1单行子查询

返回一行记录的子查询—子查询返回的结果是单列单行的

案例：显示和KING同一部门的员工

4.2多行子查询

返回多行记录的子查询—-子查询返回的结果是多行单列的

4.2.1  in关键字使用，in是用来判断列值是否在某一个集合当中。

此处用于筛选是否在多行子查询返回的集合当中。

案例：查询和10号部门的员工的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包括10号部门本身的员工。

先找出10号部门员工去重后的工作岗位：

再找出与这些工作岗位相同的员工的名字，岗位，工资，部门号：

用in判断是否在子查询集合当中

再去除10号部门的员工本身。

用deptno <> 10 来表示deptno不等于10

4.2.2 all关键字—-与子查询返回的所有行进行比较

any关键字—与子查询返回的任意一行进行比较，只要有一个为真，就为真

案例：显示工资比部门号为30号的所有员工的工资都高的员工的姓名，工资和部门号

先拿到30号部门的员工最高工资：

select max(sal) from emp where deptno=30;

再筛选工资高于30部门最高工资的员工的姓名，工资和部门号：

select ename ,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30);

也可以通过多行子查询进行筛选：

先拿到30号部门所有人的工资：

select distinct sal from emp where demptno=30;

再找出比30号部门所有人工资都高的员工—通过all将数据与子查询返回的所有数据进行比较。

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

通过any语句找出比30号部门任意一员工工资高的员工，30号部门员工除外：

select ename,sal,deptno from emp where sal> any(select distinct sal from emp where deptno=30) and deptno <> 30;

4.3多列子查询

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

案列：查询和ALLEN员工的部门和岗位完全相同的员工，不包含ALLEN自身

先拿到ALLEN的部门和岗位：

select deptno,job from emp where ename=’ALLEN’;

包含两列信息

再查找与这两列信息都相同的员工

select * from emp where (deptno,job)=(select deptno,job from emp where ename=’ALLEN’);

再去除ALLEN自身：and ename <>’ALLEN’;

也可以通过in，all，any进行多列多行子查询。

目前全部的子查询，都是在where字句中充当判断条件，但是查询出来的临时表结构，在本质是也是表。

5.在from字句中使用子查询

子查询语句出现在from子句中—-将一个子查询返回的数据作为一个临时表使用。

案列1：显示每个高于自己部门平均工资的员工姓名，部门，工资，平均工资,最后拿到每个人的办公地点

先拿到每个部门的平均工资：

select avg(sal) from emp group by deptno;

再将部门平均工资表和员工表做笛卡尔积，

拿到员工部门号和平均工资部门号相同的，

再将工资进行比较

最后将打印信息进行筛选

再将上表交给from拿到这些人的办公地点

select t1.ename,dept.loc,t1.deptno from dept,(select  emp.ename,emp.deptno from emp,(select deptno,avg(sal) 部门平均工资 from emp group by deptno) tmp where  emp.deptno=tmp.deptno and emp.sal>tmp.部门平均工资)  t1 where  t1.deptno=dept.deptno;

案列2：查找每个部门工资最高的人的姓名，工资，部门，最高工资

先拿到每个部门工资最高的人

再将这张表交给from与emp表进行笛卡尔积

再除去工资不匹配的

再比对部门是否一致

最后除去无用信息

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

先拿到每个部门的人员数量

再将上表交给from与dept表进行笛卡尔积

再筛选出两边部门号一致的数据

得到结果

解决多表问题的本质：想办法将多表转换成为单表，所以MySQL中，所有select问题全部都可以转换成单表问题。

6.合并查询

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

6.1union

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

案例：将工资大于2500或职位时MANAGER的人展示

先找工资大于2500的员工

再找职位为MANAGER的员工

最后取得两个结果集的并集

6.2union all

取两结果的并集，不对结果中的重复行去除。

案例：同样将工资大于2500或职位为MANAGER的人展示

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