数据库概论——SQL练习二（employees数据库）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了数据库概论——SQL练习二（employees数据库），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含9546字，纯文字阅读大概需要14分钟。
内容图文
MySQL 8.0.19
MySQL Workbench 8.0
准备工作
1.下载employees数据库：https://github.com/datacharmer/test_db
2.将整个文件夹放到工作的目录下
例如：mac - 放在桌面上，则文件夹的路径为Desktop/test_db-master/
windows - 放在D盘上，则文件夹的路径为D:/test_db-master/
3.修正文件路径信息
找到employees.sql文件，在文件尾部找到所有的source行，更改文件路径
例如：把source load_departments.dump改为source Desktop/test_db-master/load_departments.dump
4.安装employees数据库
在MySQL中执行：source Desktop/test_db-master/employees.sql
5.测试employees数据库是否正确安装
在MySQL中执行：source Desktop/test_db-master/test_employees_md5.sql
如果正确安装，应当输出数据库中的表格信息
tips:
(1)MySQL Workbench中使用ctrl+enter（mac：command+return）可以快捷运行sql语句
(2)在Workbench中找到(mac)"MySQLWorkbench"或(Linux)"Edit" -> "Preferences" -> "SQL Editor" -> "MySQL Session"，可以设置timeout的时间，以避免出现"Error Code: 2013. Lost connection to MySQL server during query"的报错
employees库表结构
问题和解答
1.查询每个部门的经理信息。输出格式为（部门名，经理姓，经理名），按部门名升序输出。
1 select departments.dept_name, employees.last_name, employees.first_name
2 from departments, dept_manager, employees
3 where departments.dept_no = dept_manager.dept_no
4 and dept_manager.emp_no = employees.emp_no
5 order by departments.dept_name asc;
View Code
2. 找出那些至少拥有三个title并且至少在两个部门工作过的员工，要求按升序输出前十个员工号。
1 select emp_no
2 from
3 (select @rownum:=@rownum+1 as rownum, emp_no
4 from
5 (select emp_no
6 from employees join titles using(emp_no)
7 join dept_emp using(emp_no)
8 group by employees.emp_no
9 having count(titles.title)>=3
10 and count(dept_emp.dept_no)>=2
11 order by employees.emp_no asc) as d1
12 , (select @rownum:=0) as t
13 ) as d2
14 where rownum<=10;
View Code
注:(1) MySQL不支持oracle的rownum，需要手动加上@rownum，简易版本如下
1 select @rownum:=@rownum+1 as rownum, *
2 from (select @rownum:=0) as r, my_table ;
(2) 本题代码思路：三重子查询
内层：查询符合题目要求的员工并按员工号排序，命名为d1（derived table 1）
中层：在d1的基础上添加含有rownum列的表t，命名为d2
外层：输出rownum<=10的行的员工号
(3) 每个派生表必须拥有自己的名字（即使用不到），否则会收到“1248 - Every derived table must have its own alias”的提示
3. 找出比其部门经理工资高的员工，要求输出列是（员工号，经理员工号），按员工号升序列出前十个。注意，有些员工会在多个部门任职，部门经理也会轮换，所以两者在同一部门工作的日期必须有交集才算。
1 select emp_no, mng_no 2 from 3 ( 4 select @rownum:=@rownum+1 as rownum, emp_no, mng_no 5 from 6 ( 7 select distinct es.emp_no emp_no, ms.emp_no mng_no 8 from 9 ( 10 select s1.emp_no, dm.dept_no, s1.salary, s1.from_date, s1.to_date 11 from salaries s1 join dept_manager dm on 12 (s1.emp_no = dm.emp_no 13 and s1.from_date >= dm.from_date 14 and s1.to_date <= dm.to_date) 15 ) ms 16 join 17 ( 18 select s2.emp_no, de.dept_no, s2.salary, s2.from_date, s2.to_date 19 from salaries s2 join dept_emp de on 20 (s2.emp_no = de.emp_no 21 and s2.from_date >= de.from_date 22 and s2.to_date <= de.to_date) 23 ) es 24 on 25 (ms.dept_no = es.dept_no and ms.salary < es.salary 26 and ((ms.from_date <= es.from_date and es.from_date <= ms.to_date) 27 or (ms.from_date <= es.to_date and es.to_date <= ms.to_date)) 28 ) 29 order by es.emp_no 30 ) as d2, 31 (select @rownum:=0) as t 32 ) as d3 33 where rownum <= 10;完整代码
问题比较复杂，用了四重子查询（由内而外分别记为1234层），其中34层与第2问的思路相同。
第1层：(1)合并经理与工资表，记为ms；(2)合并员工与工资表，记为es
第2层：合并ms和es，并按照es.emp_no升序排列，命名为d2（derived table layer 2）
第3层：在d2的基础上添加含有rownum列的表t，命名为d3
第4层：输出rownum<=10的行
关于按照工作日期合并的问题：
第1层：保证工资表的时间段包含于经理表/员工表的时间段，并按照前者输出
即，s1.from_date >= dm.from_date and s1.to_date <= dm.to_date
(s1=salaries, dm=dept_manager)
第2层：保证ms与es的时间段有交集，即es的开始时间或结束时间包含于ms的时间段内
即，(ms.from_date <= es.from_date and es.from_date <= ms.to_date)
or (ms.from_date <= es.to_date and es.to_date <= ms.to_date)
4. 将每个部门的员工的姓串接在一列中，要求以逗号分隔，按姓升序排序，仅包括前5个员工。输出格式为（部门号，员工姓），按部门号升序输出。
1 create view dept_emp_name 2 as 3 (select distinct de.dept_no, emp.last_name 4 from dept_emp de join employees emp using(emp_no) 5 ); 6 select dept_no, group_concat(last_name order by last_name asc separator ‘, ‘) 7 from 8 ( 9 select d2.dept_no, d2.last_name 10 from dept_emp_name d1 left join dept_emp_name d2 11 on d1.dept_no = d2.dept_no 12 and d1.last_name <= d2.last_name 13 group by d2.dept_no, d2.last_name 14 having count(d1.last_name)<=5 15 ) d 16 group by dept_no 17 order by dept_no asc;完整代码
思路:(1)创建视图create view dept_emp_name便于重复使用(dept_no, last_name)
(2)双重循环实现“排序-取前五-拼接”
内层：按照last_name的字典序把dept_emp_no跟自己做个连接，
筛选出排在前五的last_name
外层：按照dept_no排序并分类，用group_concat()排序并连接last_name
group_concat(last_name order by last_name asc separator ‘, ‘)
注：删除视图drop view dept_emp_name
5. 列出每个部门中的最高和最低工资的员工姓名及其工资。输出格式为（部门号，最高工资员工号，最高工资，最低工资员工号，最低工资），按部门号升序输出。
step 1:创建视图“部门-员工-工资”
1 create view dept_emp_salary
2 as
3 (select de.dept_no, de.emp_no, s.salary
4 from dept_emp de join salaries s
5 on de.emp_no = s.emp_no
6 and de.from_date <= s.from_date and s.to_date <= de.to_date
7 );
创建视图dept_emp_salary
step 2：分别查询(部门，最高工资员工号，最高工资)和(部门，最低工资员工号，最低工资)，然后按相同部门号合并
1 select s_max.dept_no, s_max.emp_no max_sal_emp_no, s_max.salary max_sal, 2 s_min.emp_no min_sal_emp_no, s_min.salary min_sal 3 from 4 (select d1.dept_no, d1.emp_no, d1.salary 5 from dept_emp_salary d1 6 where not exists 7 (select * 8 from dept_emp_salary d2 9 where d1.dept_no = d2.dept_no 10 and d1.salary < d2.salary 11 ) 12 ) s_max 13 join 14 (select d1.dept_no, d1.emp_no, d1.salary 15 from dept_emp_salary d1 16 where not exists 17 (select * 18 from dept_emp_salary d2 19 where d1.dept_no = d2.dept_no 20 and d1.salary > d2.salary 21 ) 22 ) s_min 23 on s_max.dept_no = s_min.dept_no 24 order by s_max.dept_no asc;方法一：not exists
1 select s_max.dept_no, s_max.emp_no max_sal_emp_no, s_max.salary max_sal, 2 s_min.emp_no min_sal_emp_no, s_min.salary min_sal 3 from 4 (select d1.dept_no, d1.emp_no, d1.salary 5 from dept_emp_salary d1 6 where d1.salary >= all 7 (select d2.salary 8 from dept_emp_salary d2 9 where d1.dept_no = d2.dept_no 10 ) 11 ) s_max 12 join 13 (select d1.dept_no, d1.emp_no, d1.salary 14 from dept_emp_salary d1 15 where d1.salary <= all 16 (select d2.salary 17 from dept_emp_salary d2 18 where d1.dept_no = d2.dept_no 19 ) 20 ) s_min 21 on s_max.dept_no = s_min.dept_no 22 order by s_max.dept_no asc;方法二：all
注：(1)方法一用时436.8sec，方法二用时491.7sec，可见not exists方法略优于all方法，差别不大
(2)如果使用默认timeout(30sec)则会遇到报错"Error Code: 2013. Lost connection to MySQL server during query"，解决方法见上面的tips
6. 查询最高工资所在的部门中的最低工资是多少？输出格式为（部门号，最高工资，最低工资），按部门号升序输出。
step 1:创建视图
1 create view dept_salary 2 as 3 (select de.dept_no, s.salary 4 from dept_emp de join salaries s 5 on de.emp_no = s.emp_no 6 and de.from_date <= s.from_date and s.to_date <= de.to_date 7 );创建视图“dept_salary”
1 create view dept_maxsal 2 as 3 (select d1.dept_no, d1.salary 4 from dept_salary 5 where d1.salary >= all 6 (select d2.salary 7 from dept_salary d2) 8 );创建视图“dept_maxsal”
step 2:按照dept_maxsal中得到的信息，找对应部门的最低工资
1 select dm.dept_no, dm.salary max_sal, ds.salary min_sal 2 from dept_maxsal dm join dept_salary ds 3 on dm.dept_no = ds.dept_no 4 where ds.salary <= all 5 (select d.salary 6 from dept_salary d 7 where d.dept_no = ds.dept_no 8 ) 9 order by dm.dept_no asc;主体
注：虽然结果只有一行，但还是需要加上那句order by以符合题意。
7(选做). 比较男女职工的平均工资差异。一个人的生涯平均工资这样计算：先将某段时间内的年薪换算成日薪（统一用一年360天），再乘以在这个年薪水平下所工作的天数，得到收入，将所有不同时段的收入加起来，再除以总天数，这样就得到总平均日薪了，求所有男女员工的平均日薪也是类似做法。输出格式为（男职工平均日薪，女职工平均日薪，平均日薪差）。
(待完成)
8(选做). 11027号员工有过三个不同的title， 将其按如下表格形式输出：
(待完成)
数据库概论——SQL练习二（employees数据库）
标签：ima tac weight rom 语句 back last img 自己的
本文系统来源：https://www.cnblogs.com/tanshiyin-20001111/p/12797455.html
内容总结
以上是互联网集市为您收集整理的数据库概论——SQL练习二（employees数据库）全部内容，希望文章能够帮你解决数据库概论——SQL练习二（employees数据库）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。