mysql DQL exercise

前言：本文不以讲解为目的，专用做数据库操作练习。

************************************************************************************************************************************

1、查询每种工作的最高工资、最低工资、人数

<span style="font-size:18px;">/*列：部门、最高工资、最低工资、人数（其中最高工资、最低工资、人数，都是分组信息）表：emp条件：无分组：每种工资是分组信息，最高工资使用max(sal)，最低工资使用min(sal)，人数使用count(*)*/SELECT job, MAX(sal), MIN(sal), COUNT(1) FROM emp GROUP BY job;</span>

************************************************************************************************************************************

2、显示非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于50000，输出结果按月工资的合计升序排列

<span style="font-size:18px;">/*列：工作名称、工资和(分组信息)表：emp条件：无分组：从事同一工作的工资和，即使用job分组分组条件：工资合计>50000，这是分组条件，而不是where条件排序：工资合计排序，即sum(sal) asc*/SELECT job,SUM(sal) FROM emp GROUP BY job HAVING SUM(sal)>50000 ORDER BY SUM(sal) ASC;</span>

************************************************************************************************************************************

3、找出部门编号为10中所有经理，部门编号为20中所有销售员，还有即不是经理又不是销售员但其工资大或等于20000的所有员工详细资料。

<span style="font-size:18px;">/*分析：列：所有列表：emp条件：deptno=10 and job='经理', depnto=20 and job='销售员', job not in ('销售员','经理') and sal>=20000*/SELECT * FROM emp WHERE   (deptno=10 AND job='经理')   OR (deptno=20 AND job='销售员')   OR job NOT IN ('经理','销售员') AND sal>=20000;</span>

************************************************************************************************************************************

4、 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

<span style="font-size:18px;">/*列：部门编号、部门名称、部门位置、部门人数(分组)列：dept、emp(部门人数没有员工表不行)条件：没有分组条件：人数>1部门编号、部门名称、部门位置在dept表中都有，只有部门人数需要使用emp表，使用deptno来分组得到。我们让dept和（emp的分组查询），这两张表进行连接查询*/SELECTz.*,d.dname,d.locFROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) zWHERE z.deptno=d.deptno;</span>

************************************************************************************************************************************

5、列出薪金比阿飞高的所有员工。

<span style="font-size:18px;">/*列：所有表：emp条件：sal>阿飞的sal，其中阿飞的sal需要子查询*/SELECT *FROM emp eWHERE e.sal > (SELECT sal FROM emp WHERE ename='阿飞')</span>

************************************************************************************************************************************

6、列出所有员工的姓名及其直接上级的姓名。

<span style="font-size:18px;">/*列：员工名、领导名表：emp、emp条件：领导.empno=员工.mgremp表中存在自身关联，即empno和mgr的关系。我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名，所以不能用内连接，因为drogon是BOSS，没有上级，内连接是查询不到它的。*/SELECT e.ename, IFNULL(m.ename, 'BOSS') AS leadFROM emp e LEFT JOIN emp mON e.mgr=m.empno;</span>

************************************************************************************************************************************

7、列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

<span style="font-size:18px;">/*列：empno, ename, dname表：emp e, emp m, dept d条件；  1. 去积：e.mgr=m.empno, e.deptno=d.deptno  2. e.hiredate < m.hiredate*/SELECT e.empno,e.ename,d.dnameFROM emp e  INNER JOIN emp m ON e.mgr=m.empno  INNER JOIN dept d ON e.deptno=d.deptnoWHERE e.hiredate<m.hiredate</span>

************************************************************************************************************************************

8、列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。

<span style="font-size:18px;">/*列：员工表所有列、部门名称表：emp, dept要求列出没有员工的部门，这说明需要以部门表为主表使用外连接*/SELECT e.*, d.dnameFROM emp e RIGHT JOIN dept dON e.deptno=d.deptno;</span>

************************************************************************************************************************************

9、列出所有文员的姓名及其部门名称，部门的人数。

<span style="font-size:18px;">/*列：ename、dname、count(*)表：emp、dept条件：job='文员'*//*第一步：查询出所有文员的姓名*/SELECT ename FROM emp WHERE job='文员'/*第二步：查询出部门编号、部门人数*/SELECT deptno,COUNT(*) FROM emp GROUP BY deptno/*第三步：查询出部门名称、部门人数使用第二步和dept连接查询*/SELECT d.dname,z.cntFROM dept d   INNER JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z ON d.deptno=z.deptno/*第四步：把第三步与emp以及条件（job='文员'）连接在一起*/SELECT e.ename, d.dname,z.cntFROM dept d   INNER JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z ON d.deptno=z.deptno  INNER JOIN emp e ON e.deptno=d.deptnoWHERE e.job='文员'</span>

　　

　　or:

<span style="font-size:18px;">/*1. 查询出文员的姓名和部门名称列：e.ename, d.dname表：emp e, dept d条件：  1. 去积：e.deptno=d.deptno  2. 条件：e.job='文员'*/SELECT e.deptno, e.ename,d.dnameFROM emp e  INNER JOIN dept d ON e.deptno=d.deptnoWHERE e.job='文员'/*2. 查询部门编号、部门人数*/SELECT deptno, COUNT(*)FROM emp GROUP BY deptno/*3. 把一、二两步的结果集连接在一起, 去积：使用deptno*/SELECT z1.ename, z1.dname, z2.cntFROM (SELECT e.deptno, e.ename ename, d.dname dnameFROM emp e  INNER JOIN dept d ON e.deptno=d.deptnoWHERE e.job='文员') z1,(SELECT deptno, COUNT(*) cntFROM emp GROUP BY deptno) z2WHERE z1.deptno=z2.deptno</span>

************************************************************************************************************************************

10、列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

<span style="font-size:18px;">/*列：e.*, d.dname, m.ename,g.grade表：emp e, dept d, emp m, salgrade g条件：  去积：    1). e.deptno=d.deptno    2). e.mgr=m.empno    3). e.sal between g.losal and g.hisal  条件；sal > (select avg(sal) from emp)*/SELECT e.*, d.dname, m.ename, g.gradeFROM emp e,dept d,emp m,salgrade gWHERE e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN g.losal AND g.hisal  AND e.sal > (SELECT AVG(sal) FROM emp)  SELECT e.*, d.dname, m.ename, g.gradeFROM emp e  LEFT JOIN dept d ON e.deptno=d.deptno  LEFT JOIN emp m ON e.mgr=m.empno  LEFT JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisalWHERE e.sal > (SELECT AVG(sal) FROM emp)</span>

************************************************************************************************************************************

11、列出与阿飞从事相同工作的所有员工及部门名称。

<span style="font-size:18px;">/*列：e.*, d.dname表：emp e, dept d条件：  1. 去积：e.deptno=d.deptno  2. 条件；job=(阿飞的工作)*/SELECT e.*, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND job=(SELECT job FROM emp WHERE ename='阿飞')</span>

************************************************************************************************************************************

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

<span style="font-size:18px;">/*列：ename,sal,d.dname表：emp,dept条件：sal>all(30部门所有工资)*/SELECT e.ename,e.sal, d.dnameFROM emp e, dept dWHERE e.deptno=d.deptno AND sal>ALL(SELECT sal FROM emp WHERE deptno=30)</span>