MySQL_SQL常用语句自我测试练习-2

 12子查询4：查询雇员中有哪些人是经理人(有四个人是经理人7902，7698，7499，7900)：：

select ename from emp where empno in(select distinct mgr from emp);
或者：
select ename from emp join
(select distinct mgr from emp) t
on(emp.empno=t.mgr);

 

select e.ename

from emp e join (select mgr  from emp) t on e.empno=t.mgr

这样的话 有重复的名字，因为多个职员只有一个经理select mgr  from emp，通过这种方法，mgr有重复的项，但是下面没有 distinct 好像用 in的时候不会取重复的

select ename

from emp

where empno in(select mgr from emp)

 

13子查询5：不准用库函数，求雇员表中薪水的最高值。
思路：两张完全相同的雇员表，左边一张，右边一张。拿左表中的薪水和右表中的薪水进行比较，左表中的最高薪水值必定不可能小于右表中的某一薪水值。
先求出emp表中最高薪水以下的所有薪水值
select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal);

select  e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal);

如果没有 distinct，则记录为28条，1+2+3+4+5+6+7，加上记录为7 条
不在此列的薪水值即为最高薪水值
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));

 distinct 可以不要。

14子查询6：平均薪水最高的部门的部门编号
　①：先求出每个部门的平均薪水和部门号（把这个看成一张表）
select avg(sal) avg_sal,deptno from emp group by deptno;
　②：再求出平均薪水最高值（把这个看成一个值）
select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t;

　③：对①表使用②条件进行查询即可
select avg_sal,deptno from
 (select avg(sal) avg_sal,deptno from emp group by deptno) t1
where avg_sal=
 (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2);

说明:在Oracle中可以使用组函数嵌套来稍微简化SQL语句的复杂程度（最多是两层嵌套）：
select max(avg(sal)) from emp group by deptno;来求出平均薪水最高值
注意：这种组函数嵌套在MySQL中不可以用

15子查询7：求平均薪水最高的部门的部门名称（在子查询6的基础之上）
说明：在实际应用中，达到这种程度的SQL语句已经算比较复杂了。
select dname from dept where deptno = 
( select deptno from
     (select avg(sal) avg_sal,deptno from emp group by deptno) t1
  where avg_sal=
     (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) t2)
);

在做题时，先仔细分析题目，查看题目分类，一步一步完成。

首先是看 数据是在一张表还是在多张表中

单张表简单查询

单张表复杂查询

 

多张表查询， joinon 是连接条件,where 条件过滤

单张表的自连接

 

组函数的使用，要使用一个字段用于分组，而且只能有一个普通字段，组函数的条件判断用having

基本结构：

Select      //要取的字段

From tableName //单个表，或多张表

Where     //简单判断

Group By //用于分组

Having    //用于对组函数进行判断

Order By //排序

Limit      //分页

 

 

好了继续做练习：

1．列出至少有一个员工的所有部门

   >select * from dept

   >where deptno in

   >(select deptno from emp group by deptno having count(*) > 0);

select distinct dname

from emp e join dept d on e.deptno = d.deptno

不清楚 上面两种有多大的区别！如果改为至少有两个员工的话 上面的方法比较容易修改！

select distinct dname

from emp e join dept d on e.deptno = d.deptno

where d.deptno in

(select  deptno from

emp group by deptno having count(deptno) >=2)

2．列出薪金比“SMITH”多的所有员工。

   >SELECT * FROM emp

   >WHERE sal >

   >(SELECT sal FROM emp WHERE ename = 'SMITH');

3．列出所有员工的姓名及其直接上级的姓名。(自连接)\

   >SELECT worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,manager.ename m_ename

   >FROM emp worker,emp manager

   >WHERE worker.mgr = manager.empno;

select e1.ename,e2.ename from emp e1

join emp e2 on e1.mgr = e2.empno

4．列出受雇日期晚于其直接上级的所有员工。(自连接)

   >SELECT worker.empno w_empno,worker.ename w_ename,manager.empno m_empno,manager.ename m_ename,

    worker.hiredate w_hiredate,manager.hiredate m_hiredate

   >FROM emp worker,emp manager

   >WHERE worker.mgr = manager.empno AND worker.hiredate > manager.hiredate;

select e1.ename,e2.ename from emp e1

join emp e2 on e1.mgr = e2.empno

where e1.hiredate > e2.hiredate

5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(外连接)

   >SELECT e.empno,e.ename,e.job,d.deptno,d.dname

   >FROM emp e RIGHT OUTER JOIN dept d

   >ON e.deptno = d.deptno;

select dname,ename

from dept left join emp on dept.deptno=emp.deptno

6．列出所有“CLERK”（办事员）的姓名及其部门名称。

   >SELECT e.ename,e.job,d.deptno,d.dname

   >FROM emp e,dept d

   >WHERE e.deptno = d.deptno AND e.job = 'CLERK';

select ename ,job,dname

from emp join dept on emp.deptno=dept.deptno

where job = 'CLERK'

   可以应用 子查询

  select (select dname from dept where deptno=a.deptno) as dname ，ename

   from emp a

   where job=‘CLERK‘;

7．列出最低薪金大于1500的各种工作。

   >SELECT job FROM emp GROUP BY job HAVING MIN(sal) > 1500;

select min(sal) msal ,job from emp group by job having msal>1500

8．列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

   >SELECT e.ename, e.job, d.dname, d.deptno FROM emp e, dept d

   >WHERE e.deptno in

   >(select d.deptno from dept d WHERE d.dname = 'SALES') //到这里会产生笛卡尔积

   > AND d.dname = 'SALES';

select ename from emp where deptno = (

select deptno from dept where dname='SALES')

9．列出薪金高于公司平均薪金的所有员工。

   >SELECT empno,ename,job,sal FROM emp WHERE sal >

   >(SELECT AVG(sal) FROM emp);

 

10．列出与“Smith”从事相同工作的所有员工。

   >SELECT empno,ename,job FROM emp WHERE job =

   >(SELECT job FROM emp WHERE ename = ' Smith')

   >AND ename <> ' Smith';

 

11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

   >SELECT ename,job,sal FROM emp

   >WHERE sal in (select sal FROM emp WHERE deptno = 30);

  

12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。 >ALL

   >SELECT ename,job,sal FROM emp

   >WHERE sal >ALL(select sal FROM emp WHERE deptno = 30);

 

13．列出在每个部门工作的员工数量、平均工资和平均服务期限。

   >SELECT deptno,COUNT(*) count_num,avg(sal),avg(SYSDATE - hiredate) FROM emp

   >GROUP BY deptno;

 

14．列出所有员工的姓名、部门名称和工资。 内连接

  1。

   >SELECT e.ename,d.dname,e.job,e.sal

   >FROM emp e,dept d

   >WHERE e.deptno = d.deptno;

  2.

   >SELECT e.ename,d.dname,e.job,e.sal

   >FROM emp e NATURAL JOIN dept d;   --  两个表必须含有同名且类型相同的列

  3.

   >SELECT e.ename,d.dname,e.job,e.sal

   >FROM emp e JOIN dept d

   >USING(deptno);                    --  两个表中相同列的类型必须相同

  4.

   >SELECT e.ename,d.dname,e.job,e.sal

   >FROM emp e JOIN dept d

   >ON(e.deptno = d.deptno)           --  各种条件

15．列出所有部门的详细信息和部门人数。

   >SELECT dept.*,(select count(*) FROM emp WHERE deptno = dept.deptno) sum

   >FROM dept;

16．列出各种工作的最低工资。

   >SELECT job,MIN(sal) min_sal FROM EMP GROUP BY job;

17．列出MANAGER（经理）的最低薪金。

   >SELECT sal,job FROM emp WHERE sal in

   >(SELECT min(sal) FROM emp GROUP BY job) AND job = 'MANAGER';

select min(sal),job

from emp

where job ='MANAGER'

group by job

 

18．列出所有员工的年工资，按年薪从低到高排序。

   >SELECT ename NAME,job JOB,sal * 12 MANUAL_salary FROM emp ORDER BY MANUAL_salary;

 

19. 查询职员表中的职员姓名、薪水、奖金，使用函数处理空值。 注：NULL参与运算，结果为NULL，所以有些情况需要处理NULL

   1。 nvl函数 nvl(expr1,expr2) 若expr1为NULL则取值expr2

     >SELECT ename,sal,comm, sal + nvl(comm,0) FROM emp;

   2。 nvl2（expr1,expr2,expr3) 若expr1为NULL，则取值expr3，若不为NULL ，则返回expr2

     >SELECT ename,sal,comm, nvl2(comm,sal+comm,sal) salary from emp;

   3。 coalesce(expr1[,expr2][,expr3.....) 返回第一个非空表达式的结果

     >SELECT ename,sal,comm, coalesce(sal+comm,sal) salary FROM emp；

 

20.在使用UNION这类集合操作符时，如果select列表的列名不同，必须使用列位置来排序。

   查询部门表和职员表，列出所有的部门编码和部门名字，以及所有的职员编码和职员名字，并以部门彪马和职员编码

   作为升序排列的标准

   >SELECT deptno, dname FROM dept

   >UNION

   >SELECT empno,ename FROM emp

   >ORDER BY 1;

21.多列分组

   计算每个部门每个职位的平均薪水和最高薪水

既对部门分组，又要对职位进行分组。。

   >SELECT deptno,job,AVG(sal) avg_sal,MAX(sal) max_sal FROM emp GROUP BY deptno,job ORDER BY deptno;

22.查询出薪水比本部门平均薪水高的员工信息（怎么实现 本部门平均薪水）

select avg(sal) ,deptno from emp  group by deptno // 部门平均工资

select distinct deptno from emp

 

select ename,sal

from emp join (select avg(sal) asal,deptno from emp  group by deptno) t

on emp.deptno=t.deptno

where sal>(t.asal)

   1。

   >SELECT deptno, ename ,sal

   >FROM emp e

   >WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = e.deptno)//根据部门号区分，SELECT AVG(sal) FROM emp WHERE deptno = 40，e.deptno 代表本部门编号，然后计算出本部门工资，然后进行比较。

   >ORDER BY deptno;

   2。行内视图，出现在FROM短语中的子查询

   >SELECT e.deptno, e.ename, e.sal

   >FROM emp e,

   >(SELECT deptno,AVG(sal) avg_sal FROM emp GROUP BY deptno) x

   >WHERE e.deptno = x.deptno

   >and e.sal > x.avg_sal

   >ORDER BY e.deptno;