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的分组查询），这两张表进行连接查询
*/
SELECT
z.*,d.dname,d.loc
FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
WHERE z.deptno=d.deptno;</span>

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

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

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

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

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

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

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

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

<span style="font-size:18px;">/*
列：员工表所有列、部门名称
表：emp, dept
要求列出没有员工的部门，这说明需要以部门表为主表使用外连接
*/
SELECT e.*, d.dname
FROM emp e RIGHT JOIN dept d
ON 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.cnt
FROM 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.cnt
FROM 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.deptno
WHERE 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.dname
FROM emp e
  INNER JOIN dept d ON e.deptno=d.deptno
WHERE e.job='文员'


/*
2. 查询部门编号、部门人数
*/
SELECT deptno, COUNT(*)
FROM emp 
GROUP BY deptno


/*
3. 把一、二两步的结果集连接在一起, 去积：使用deptno
*/
SELECT z1.ename, z1.dname, z2.cnt
FROM 
	(
	SELECT e.deptno, e.ename ename, d.dname dname
	FROM emp e
	  INNER JOIN dept d ON e.deptno=d.deptno
	WHERE e.job='文员'
	) z1,
	(
	SELECT deptno, COUNT(*) cnt
	FROM emp 
	GROUP BY deptno
	) z2
WHERE 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.grade
FROM emp e,dept d,emp m,salgrade g
WHERE 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.grade
FROM 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.hisal
WHERE 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.dname
FROM emp e, dept d
WHERE 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.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno AND sal>ALL(SELECT sal FROM emp WHERE deptno=30)</span>