【MySQL】开发技术深度探索：mysql数据库复合查询全面详解

前言：本节内容为mysql的符合查询。本节内容是基本查询的进阶，是对内置函数，基本查询的集合使用。友友们多多利用文章中的案例练习进行掌握。

ps：本节内容还是使用员工表进行演示，没有员工表的友友可以去网络上找一找。然后就可以放心观看喽！

基本查询回顾

多表查询

自连接

子查询

单行子查询

多行子查询

多列子查询

在from子句中使用子查询

合并查询

union

Union all

基本查询回顾

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

select * from emp where (sal > 500 or job = 'MANAGER') and ename like 'J%';

这条sql语句应该先写出 select * from emp where sal > 500 or job = 'MANAGER', 查出所有的工资高于500或者岗位为MANAGER的员工。然后再将工资高于500或者岗位为MANAGER这个条件括起来。再and后面的条件。

也可以使用字符串函数，结果相同：

select * from emp where (sal > 500 or job = 'MANAGER') and substring(ename, 1, 1) = 'J';

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

select * from emp order by deptno asc, sal desc;

按照年薪进行降序排序:

select * from emp order by (sal * 12 + ifnull(comm, 0)) desc;//comm有可能是null， null不参与运算， 所以ifnull用来判断是否是null。

查询工资最高的人的名字和工作岗位：

工资最高其实就是要进行聚合统计。我们如何找到工资最高，就是使用max函数：

select max(sal) from emp;

查询出最高工资是多少了之后，就将这个结果放到where字句当中。 ——也就是说， where子句允许再次进行内部查询：

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

显示工资高于平均工资的员工信息：

select avg(sal) 平均工资 from emp;

先拎出来平均工资。然后再放到where子句中：

select * from emp where sal > (select avg(sal) 平均工资 from emp);

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

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

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

select avg(sal) from emp group by deptno having avg(sal) < 2000;

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

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

多表查询

我们可以从多张表中获取数据：

如果我们对两张表做查询，那么查询到的结果我们可以看到，就是拿着第一张表的第一条信息，和第二张表的四条信息做组合。再拿着第二条信息和第二张表的四个信息组合。所以我们就能知道，两张表查询，是将数据进行穷举组合 -- 把这种组合方式叫做笛卡尔积。

向上main的查询结果，我们可以看到第一条：SMITH这个人有两个deptno信息，这样的结果是没有意义的。所以我们就要将这些没有意义的数据删除：

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

然后再来做其他需求。

下面看一些示例：

显示雇员名、雇员工资以及所在部门的名字：

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

我们查询的时候，显示的数据如果在复合的表中只有一列，那么就可以直接写他的列名。否则就需要指定是哪张表中的某一列，比如：emp.ename。（注，这里都是一列，不需要加emp.，也可以加上）

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

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

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

select ename, sal, grade from emp, salgrade where sal between losal and hisal;

自连接

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

我们直接看样例：
显示员工FORD的上级领导的编号和姓名(mar是员工领导的编号 --empno):

我们先使用子查询：

 select empno, ename from emp where emp.empno = (select mgr from emp where ename = 'FORD');

select mgr from emp where ename = 'FORD'是查出emp里面的叫做FORD的人的领导编号。然后查询emp里面的编号是mgr的人的姓名和编号。

现在使用多表查询，即自链接：

select leader.empno, leader.ename from emp leader, emp worker where leader.empno = worker.mgr and worker.ename = 'FORD';

这里就是先使用from自连接。将两个emp 重命名 leader和worker。其中leader的员工编号为worker的领导编号。并且worker的名字叫做FORD。

子查询

单行子查询

显示SMITH同一部门的员工：

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

多行子查询

使用in关键字，查询和10号部门的工作岗位相同的雇员的名字，岗位，工资，部门号，但是不包含10号自己的。

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

其中in后面的括号里面的就是子查询， <>是不等于的意思。

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

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

子查询就是使用all函数，里面的就是子查询。

使用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) asal, deptno dt from emp group by deptno) tmp where emp.sal > tmp.asal and emp.deptno = tmp.dt;

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

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

合并查询

union

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

下面为案例：

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

select ename, sal, job from emp where sal > 2500 union select ename, sal, job from emp where job = 'MANAGER';

Union all

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

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

select ename, sal, job from emp where sal > 2500 union all select ename, sal, job from emp where job  = 'MANAGE';

——————以上就是本节全部内容哦，如果对友友们有帮助的话可以关注博主，方便学习更多知识哦！！！