【MYSQL】复合查询

基本查询

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

select ename from emp where (sal > 500 OR job = 'MANGER') and substring(ename,1,1) = 'J';    
1

按照部门号升序、工资降序排序

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

使用年薪降序排序

select sal*12+ifnull(comm,0) as sum from emp order by sum desc;
1

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

select * from emp where sal = (select max(sal) from emp);
1

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

select * from emp where sal > (select avg(sal) from emp);
1

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

select deptno,format(max(sal),2) 最高 , format(avg(sal),2) 平均 from emp group by deptno;
1

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

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

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

select job,count(*) 人数,avg(sal) 平均工资 from emp group by job;
1

多表查询

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

两张表进行笛卡尔积。即用第一张表的一条数据和第二张表的所有数据进行组合。

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

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

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

自连接

select * from salgrade as t1,salgrade as t2;
1

显示员工FORD的上级领导的编号和姓名

//子查询
select mgr from emp where ename = 'FORD';

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

//自连接
select * from emp e1,emp e2 where e1.name='FORD' and e1.mgr=e2.empno;
1
2
3
4
5
6
7

子查询

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

单行子查询

显示与SMITH同一部门的员工

select dptno from emp where ename = 'SMITH';

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

1
2
3
4

多行子查询

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

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

---

还要员工的部门名字：

select * from (select ename,job,sal,deptno from emp where job in (select distinct job from emp where deptno=10) and deptno <> 10) as tmp , dept where dept.deptno=tmp.deptno;
1

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

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

select * from emp where sal > all(select distinct sal from emp where deptno=30);
1
2
3

显示工资比30号部门的任意员工的工资高的员工的姓名工资和部门号

select * from emp where sal > any(select distinct sal from emp where deptno=30);
1

多列子查询

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

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

在from语句中使用子查询

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

//1.每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;

//2.进行笛卡尔积
select * from emp,(select deptno,avg(sal) from emp group by deptno) as tmp;

//3.筛选正确的部门
select * from emp,(select deptno,avg(sal) from emp group by deptno) as tmp where emp.deptno = tmp.deptno;

//4.单表查询
select * from emp,(select deptno,avg(sal) myavg from emp group by deptno) as tmp where emp.deptno = tmp.deptno and emp.sal > tmp.myavg;
1
2
3
4
5
6
7
8
9
10
11

---

查看这一些人的办公地点：

1.上面的查询结果和dept表进行笛卡尔积
2.筛选合适的行
3.按条件得到要求的
1
2
3

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

//1.每个部门的最高工资
select deptno,max(sal) from emp group by deptno;

//2.和emp表笛卡尔积,找到谁是最高的那个人
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;

//3.
1
2
3
4
5
6
7

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

• 使用子查询

//各部门人员数量
select deptno,count(*) dept_num from emp group by deptno;

//跟dept表进行笛卡尔积
select t1.dname,t1.loc,t2.dept_num,t1.deptno from dept t1,(select deptno,count(*) dept_num from emp group by deptno) t2 where t1.deptno=t2.deptno;

1
2
3
4
5
6

多表查询的本质：将多表转换成单表。

合并查询

union

该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果集中的重复行(自动去重)。要拼接的时候要满足两个列一致，否则不能拼。

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

select * from emp where job = 'MANNAGER' union select * from emp where job = 'MANAGER';
1

union all

该操作符不会对满足多个条件的数据进行去重，而是保留。