Mysql中的复合查询详解

目录

• 1. 基本查询简单回顾
• 2. 多表查询
• 3. 自连接（Self-Join）
• 4. 子查询
• 4.1单行子查询
• 4.2多行子查询
• 4.3多列子查询
• 4.4在from中使用子查询
• 5. 合并查询
• 5.1UNION
• 5.2UNION ALL
• 总结

1. 基本查询简单回顾

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

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

使用年薪进行降序排序：

select ename ,job,sal*12+ifnull(comm,0) sarn  from emp order by sarn desc;

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

方案一：

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

方案二：

select ename ,job ,sal from emp order by sal desc limit 1;

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

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

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

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

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

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

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

select job ,count(*) from emp grhttp://www.devze.comoup by job;

2. 多表查询

将第一张表的每一条数据和第二章表的每一条数据相结合，不加过滤条件得到的结果被称为笛卡尔积

笛卡尔积：

显示部门为10的员工和部门的全部信息

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

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

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

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

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

3. 自连接（Self-Join）

将两张相同的表做笛卡尔积

• 显示员工及员工领导的个人信息

select * from emp e1, emp e2 where e1.empno=e2.mgr;

4. 子查询

4.1单行子查询

• 显示SMITH同一部门的员工

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

4.2多行子查询

返回多行记录的子查询

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

select ename ,job,sal,deptno from emp where 
job in(select 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);

4.3多列子查询

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

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

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

4.4在from中使用子查询

在mysql中每一次的查询结果都是一个子表。

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

select ename,deptno,sal,asal from emp t1,(select deptno dt,avg(sal) asal  
from phpemp group by dt)t2 where t1.sal>t2.asal and t1.deptno=t2.dt;

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

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

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

select *from dept,
(select deptno,count(deptno) from emp group by deptno)dept2 
where dept.deptno=dept2.deptno;

5. 合并查询

5.1UNION

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

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

5.2UNION ALL

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

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持编程客栈(www.devze.com)。