数据库的复合查询

复合查询

之前所说的mysql表的查询都是对一张表进行查询，但是在实际开发中还远远不够。

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

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

select * from EMP rorder by deptno,sal desc;

按照年薪进行降序排序

select ename,sal*12+ifnull(comm,0) as'年薪'from EMP order by 年薪 desc;

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

select ename,salfrom EMP where sal>(select avg(sal)from EMP);

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

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

多表查询

实际开发中往往数据来自不同的表，所以需要多表查询
案例分析
显示雇员名、雇员工资以及所在部门的名字因为上面的数据来自EMP和DEPT表，因此要联合查询

其实要的只是emp表中的deptno=dept表中的deptno字段的记录

select EMP.ename,EMP.sal,DEPT.dnamefrom EMP,DEPT where EMP.deptno=DEPT.deptno;

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

select ename,sal,dname from EMP,DEPT where EMP.deptno=DEPT.deptno and DEPT. deptno=10;

**

自连接

自连接是指在同一张表连接查询
案例：
显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号–empno）
使用的子查询

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

使用多表查询（自查询）

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

子查询
子查询是指嵌入在其他sql语句中的select语句，也叫嵌套语句
单行子查询：返回多行记录的子查询
in关键字：查询和10号部门的工作相同的雇员的名字，岗位，工资，部门号，但是不包含10自己的

select ename,sal,deptno from EMP where sal>all(select salfrom 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,deptnodt fromEMP 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 andEMP.sal=tmp.ms;

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

合并查询
在实际应用中，那个为了合并多个select的执行结果，可以使用集合操作符union,union all
union
该操作符用于取得两个结果集的并集，当使用该操作符时，会自动去掉结果 集中的重复行
案例：
将工资大于2500或职位是MANAGER的人找出来

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

union all
该操作符用于取得两个结果集的并集，当使用该操作符时，不会去掉结果集中的重复行
案例：将工资大于25000或职位为MANAGER的人找出来

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