MySQL基础入门【#】习题篇18 - 34

---

MySQL下载请查看：MySQL基础入门【1】下载与使用

Part1笔记：MySQL基础入门【2】

Part2笔记：MySQL基础入门【3】

---

动力节点入门视频链接：MySQL基础入门-mysql教程-数据库实战（MySQL基础+MySQL高级+MySQL优化+MySQL34道作业题）

数据及笔记下载链接：百度云链接
提取码：0r3s

---

18 列出薪水比smith多的所有员工信息

select e.ename
from emp e
where sal >(select sal from emp where ename = 'SMITH');
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

19 列出所有"CLERK"(办事员)的姓名及其部门名称,部门的人数.

这题有点麻烦感觉，我觉得要拉两张临时表

select t1.ename, d.dname, t2.cc
from
dept d
join
(select ename, deptno from emp where job = 'CLERK') t1
on t1.deptno = d.deptno
join
(select deptno, count(*) as cc from emp group by deptno) t2
on t2.deptno = d.deptno;
+--------+------------+----+
| ename  | dname      | cc |
+--------+------------+----+
| SMITH  | RESEARCH   |  5 |
| ADAMS  | RESEARCH   |  5 |
| JAMES  | SALES      |  6 |
| MILLER | ACCOUNTING |  3 |
+--------+------------+----+
4 rows in set (0.00 sec)

20 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数

select job, count(*)
from emp
where job in 
(select job from emp
group by job
having min(sal) > 1500)
group by job;
+-----------+----------+
| job       | count(*) |
+-----------+----------+
| MANAGER   |        3 |
| ANALYST   |        2 |
| PRESIDENT |        1 |
+-----------+----------+
3 rows in set (0.00 sec)

21 列出在部门"SALES"<销售部>工作的员工的姓名,假定不知道销售部的部门编号.

题目给我看懵了，不知道编号，那咋连接
看答案

select ename from emp where deptno = (select deptno from dept where dname ='SALES');
+--------+
| ename  |
+--------+
| ALLEN  |
| WARD   |
| MARTIN |
| BLAKE  |
| TURNER |
| JAMES  |
+--------+
6 rows in set (0.00 sec)

今天累了，明天再继续

22 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,雇员的工资等级

select e.ename ,d.dname,m.ename, s.grade 
from emp e 
join dept d 
on e.deptno = d.deptno 
left join emp m 
on e.mgr = m.empno 
join salgrade s 
on e.sal between s.losal and s.hisal 
where e.sal > (select avg(sal) from emp);

+-------+------------+-------+-------+
| ename | dname      | ename | grade |
+-------+------------+-------+-------+
| SCOTT | RESEARCH   | JONES |     4 |
| CLARK | ACCOUNTING | KING  |     4 |
| BLAKE | SALES      | KING  |     4 |
| JONES | RESEARCH   | KING  |     4 |
| FORD  | RESEARCH   | JONES |     5 |
| KING  | ACCOUNTING | NULL  |     5 |
+-------+------------+-------+-------+
6 rows in set (0.00 sec)

23 列出与"SCOTT"从事相同工作的所有员工及部门名称

记得要把SCOTT也删了

select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno where e.job = (select job from emp where ename ='SCOTT') and e.ename != 'SCOTT';
+-------+----------+
| ename | dname    |
+-------+----------+
| FORD  | RESEARCH |
+-------+----------+
1 row in set (0.00 sec)

24 列出薪金等于部门30中员工的薪金的其他员工的姓名和薪金

?问号。这题有问题吧，等于其他员工是什么意思，每个都等于吗。

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

这个简单，用一个max函数找到最高薪水，再比较。

26 列出在每个部门工作的员工数量,平均工资和平均服务期限.

嚯时间函数，新知识
计算某一时间点到现在的时间间隔

SELECT
    	TIMESTAMPDIFF(
    		YEAR,
    		'1997-05-31',
    		CURDATE()
    	) AS '年龄

select d.dname as '部门',count(t.ename) as '员工数量', avg(t.sal) as '平均薪水', avg(t.year) as '平均服务期限' 
from
(select ename,sal, TIMESTAMPDIFF(YEAR, HIREDATE, CURDATE() ) AS 'year', deptno from emp) t 
join dept d
on
t.deptno = d.deptno
group by d.deptno;
+------------+--------------+--------------+--------------------+
| 部门       | 员工数量     | 平均薪水     | 平均服务期限       |
+------------+--------------+--------------+--------------------+
| RESEARCH   |            5 |  2175.200000 |            37.4000 |
| SALES      |            6 |  1566.666667 |            39.5000 |
| ACCOUNTING |            3 |  2916.666667 |            39.3333 |
+------------+--------------+--------------+--------------------+
3 rows in set (0.00 sec)

27 列出所有员工的姓名、部门名称和工资。

一个join就行，跳过

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

跟26差不多，加一个deptno，跳过

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

select e.ename, t.* 
from emp e 
join 
(select job,min(sal) as minsal from emp group by job) t 
on e.job = t.job and e.sal = t.minsal;
+--------+-----------+---------+
| ename  | job       | minsal  |
+--------+-----------+---------+
| SMITH  | CLERK     |  800.00 |
| WARD   | SALESMAN  | 1250.00 |
| MARTIN | SALESMAN  | 1250.00 |
| CLARK  | MANAGER   | 2450.00 |
| SCOTT  | ANALYST   | 3000.00 |
| KING   | PRESIDENT | 5000.00 |
+--------+-----------+---------+
6 rows in set (0.00 sec)

30 列出各个部门的MANAGER(领导)的最低薪金

select deptno, min(t.sal) from
(select * from emp where job = 'MANAGER') t
group by deptno;
+--------+------------+
| DEPTNO | min(t.sal) |
+--------+------------+
|     20 |    2975.00 |
|     30 |    2850.00 |
|     10 |    2450.00 |
+--------+------------+
3 rows in set (0.00 sec)

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

注意ifnull

select ename, (sal+ifnull(0,comm))*12 as yearsal from emp order by yearsal;
+--------+----------+
| ename  | yearsal  |
+--------+----------+
| SMITH  |  9600.00 |
| JAMES  | 11400.00 |
| ADAMS  | 13200.00 |
| WARD   | 15000.00 |
| MARTIN | 15000.00 |
| MILLER | 15600.00 |
| TURNER | 18000.00 |
| ALLEN  | 19200.00 |
| CLARK  | 29400.00 |
| BLAKE  | 34200.00 |
| JONES  | 35700.00 |
| SCOTT  | 36000.00 |
| FORD   | 36012.00 |
| KING   | 60000.00 |
+--------+----------+
14 rows in set (0.00 sec)

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

先找出工资超过3000的人，然后自连接

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

like模糊查询，回忆一下

//找出带s的部门
select * from dept where dname like '%S%';
+--------+------------+---------+
| DEPTNO | DNAME      | LOC     |
+--------+------------+---------+
|     20 | RESEARCH   | DALLAS  |
|     30 | SALES      | CHICAGO |
|     40 | OPERATIONS | BOSTON  |
+--------+------------+---------+
3 rows in set (0.00 sec)

然后和emp表连接，count，sum

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

//更新表的语法：update table set ...... where ...;
update emp set sal = sal * 1.1 where  timestampdiff( year,hiredate,curdate()) >30;
Query OK, 14 rows affected (0.03 sec)
Rows matched: 14  Changed: 14  Warnings: 0

select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  880.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1760.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1375.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 3272.50 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1375.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 3135.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2695.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3300.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5500.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1650.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1210.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 1045.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3301.10 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1430.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

结束啦。