练习26-34：日期函数，ifnull函数

表结构回顾

26. 列出在每个部门工作的员工数量、平均工资和平均服务年限,假设每年都是365天

SELECT
	deptNo,
	COUNT(*) AS emp_cnt,
	AVG(sal) AS avg_sal,
	AVG((TO_DAYS(NOW()) - TO_DAYS(hireDate)) / 365) AS avg_year
FROM emp 
GROUP BY deptNo;
1
2
3
4
5
6
7

注：to_days()接受一个日期类型的参数，返回的是距离公元0年的天数

27. 列出所有员工的姓名、及其所在部门名称和工资

SELECT
	e.eName,
	d.dName,
	e.sal
FROM emp e
LEFT JOIN dept d
ON e.deptNo = d.deptNo;
1
2
3
4
5
6
7

28. 列出所有部门的详细信息和人数

由于需要的是所有部门的信息，所以是左连接，部门表左连接员工表，看每个部门都有哪些人，因为题目要求详细信息，所以部门的全部字段都需要，又由于要对部门分组，结果中除了聚合结果外，还需要有其他字段，所以这些其他字段也需要出现在group by中

SELECT
	d.*,
	COUNT(e.empNo) -- 这里不能用count(*),count(*)会统计null行
FROM dept d
LEFT JOIN emp e
ON d.deptNo = e.deptNo
GROUP BY d.deptNo,d.dName,d.loc;
1
2
3
4
5
6
7

如果使用count(*)则结果如下

SELECT
	d.*,
	COUNT(*) 
FROM dept d
LEFT JOIN emp e
ON d.deptNo = e.deptNo
GROUP BY d.deptNo,d.dName,d.loc;
1
2
3
4
5
6
7

29. 列出各种工作的最低工资，及从事此工作的雇员姓名

第一步：获得每个工作的最低工资

SELECT job,MIN(sal) AS min_sal FROM emp GROUP BY job;
1

第二步：将第一步获得的结果作为临时表，使用员工表与其内连接，连接条件就是job和相同，sal和min_sal相同

SELECT
	e.eName,
	e.job,
	e.sal,
	t.min_sal
FROM emp e
JOIN (SELECT job,MIN(sal) AS min_sal FROM emp GROUP BY job) t
ON e.job = t.job AND e.sal = t.min_sal;
1
2
3
4
5
6
7
8

30. 列出各个部门manager的最低薪资

第一步：筛出所有的mgr及其所属的部门

SELECT DISTINCT mgr,deptNo FROM emp;
1

第二步：将第一步的结果关联员工表获得mgr的薪资

SELECT
	t.*,
	e.sal
FROM (SELECT DISTINCT mgr,deptNo FROM emp) t
JOIN emp e
ON t.mgr = e.empNo;
1
2
3
4
5
6

第三步：按照部门分组，获得每个部门mgr的最低薪资

SELECT
	t.deptNo,
	MIN(e.sal) AS min_sal
FROM (SELECT DISTINCT mgr,deptNo FROM emp) t
JOIN emp e
ON t.mgr = e.empNo
GROUP BY t.deptNo;
1
2
3
4
5
6
7

还有一种简单的写法就是，员工表的job列已经表明了哪些人是manager，所以筛出这些人然后分组聚合即可

SELECT
	deptNo,
	MIN(sal) AS min_sal
FROM emp
WHERE job = 'MANAGER'
GROUP BY deptNo;
1
2
3
4
5
6

31. 列出所有员工的年工资，按年薪从低到高排序

注：我们一直没有用到的一列comm为每月补助，不是每个员工都有补助，所以需要用到ifnull方法， 此外，年薪 = （月薪 + 每月补助）* 12

SELECT eName,(sal + IFNULL(comm,0)) * 12 AS year_sal FROM emp ORDER BY year_sal;
1

32. 求出员工领导的薪水超过3000的员工名称和领导名称

自关联，将员工信息和领导信息放到同一行即可

SELECT
	e1.eName,
	e2.eName AS mgr_name
FROM emp e1
JOIN emp e2
ON e1.mgr = e2.empNo
WHERE e2.sal > 3000;
1
2
3
4
5
6
7

33. 求部门名称中带“S"字符的部门员工的工资合计、部门人数

第一步：员工表关联部门表，获得部门名称

SELECT
	e.*,
	d.dName
FROM emp e
JOIN dept d
ON e.deptNo = d.deptNo;
1
2
3
4
5
6

第二步：筛出想要的部门，然后聚合统计

SELECT
	d.deptNo,
	d.dName,
	SUM(e.sal) AS total_dept_sal,
	COUNT(e.empNo) AS total_dept_emp
FROM emp e
JOIN dept d
ON e.deptNo = d.deptNo
WHERE d.dName LIKE '%S%' -- 从关联的结果中用where继续过滤
GROUP BY d.deptNo,d.dName;
1
2
3
4
5
6
7
8
9
10

34. 给任职超过30年的员工加薪10%

SELECT 
	eName,
	sal,
	sal * 1.1 AS new_sal,
	(TO_DAYS(NOW()) - TO_DAYS(hireDate))/365 AS year_hire 
FROM emp 
WHERE (TO_DAYS(NOW()) - TO_DAYS(hireDate))/365 > 30;
1
2
3
4
5
6
7