复合查询

复合查询

 

1、单表查询

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

        select empno,ename,sal,job from emp 

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

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

        select deptno,sal,ename from emp 

        order by deptno asc, sal desc;

• 使用年薪进行降序排序

        select ename, sal*12+ifnull(comm, 0) as nianxin from emp 

        order by nianxin desc;

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

        select ename, job, sal from emp 

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

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

        select ename, job, sal from emp 

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

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

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

        group by deptno;

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

        select deptno, avg(sal) from emp 

        group by deptno having avg(sal) < 2000;

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

        select count(empno), avg(sal), job from emp 

        group by job;

 

2、多表查询

  有三张表EMP,DEPT,SALGRADE

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

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

            select emp.ename, emp.sal, dept.dname from emp, dept 

            where emp.deptno=dept.deptno;

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

            select dept.dname, dept.deptno, emp.ename, emp.sal from emp, dept 

            where emp.deptno=dept.deptno and emp.deptno = 10;

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

            select  emp.ename, emp.sal, salgrade.grade from emp, salgrade     

            where sal between losal and hisal;    

• 显示员工FORD的上级领导的编号和姓名（mgr是员工领导的编号--empno）

            1.select empno, ename from emp 

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

            2.select l.empno, l.ename from emp as l, emp as w 

                where l.empno=w.mgr and  w.ename='FORD';

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

•  单行子查询---返回一行记录的子查询

  • 显示SMITH同一部门的员工姓名，工作，编号，工资和部门号

                        select ename,job,empno,sal,deptno from emp 

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

 

• 多行子查询---返回多行记录的子查询，使用关键字in,all,any

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

                        select ename, job, sal, deptno from emp 

                        where job in (select job from emp where deptno=10);

• • all关键字---显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号

                        1.select ename,sal,deptno from emp 

                            where sal > all(select sal from emp where deptno=30);            //子查询单行

                        2.select ename,sal,deptno from emp 

                            where sal > (select max(sal) from emp where deptno=30);        //子查询多行

• • any关键字---显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号

                        1.select ename,sal,deptno from emp 

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

                        2.select ename,sal,deptno from emp 

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

 

• 多列子查询---查询返回多个列数据的子查询语句

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

                        select ename,sal,empno,deptno,job from emp 

                        where (deptno, job)=

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

 

•  在from子句中使用子查询---把一个子查询当做一个临时表使用

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

                    每个部门的平均工资表：select deptno dt, avg(sal) lsal from emp group by deptno;        //临时表

                        select emp.ename, emp.deptno, emp.sal,  tmp.lsal

                        from emp, (select deptno dt, avg(sal) lsal from emp group by deptno)  tmp

                        where emp.deptno=tmp.dt and sal > tmp.lsal;

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

                 每个部门的最高工资表：select deptno dt, max(sal) msal from emp group by deptno;        //临时表

                        select emp.ename, emp.deptno, emp.sal,  tmp.msal

                        from emp, (select deptno dt, max(sal) msal from emp group by deptno)  tmp

                        where emp.deptno=tmp.dt and sal = tmp.msal;

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

           每个部门的人员数量表：select deptno, count(empno) tatal from emp group by deptno;         //临时表

                    1.select dept.dname, dept.deptno, dept.loc, tmp.tatal 

                        from dept, (select deptno, count(empno) tatal from emp group by deptno)  tmp

                        where dept.deptno=tmp.deptno;

                    // 多表查询

                    2.select dept.dname, dept.loc, dept.deptno, count(*) from dept, emp

                        where dept.deptno=emp.deptno

                        group by dept.dname, dept.deptno, dept.loc;        

• 合并查询

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

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

                // 1.union all 不去重

                    select ename from emp where sal > 2500 

                    union all

                    select ename from emp where  job='MANAGER';

                // 2.union 去重.当使用该操作符时，会自动去掉结果集中的重复行。

                    select ename from emp where sal > 2500 

                    union 

                    select ename from emp where  job='MANAGER';