5.5、多表的课后练习
【题目】
# 1.显示所有员工的姓名,部门号和部门名称。
SELECT E.last_name,E.department_id,D.department_name
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id;
# 2.查询90号部门员工的job_id和90号部门的location_id
SELECT E.job_id,D.location_id
FROM employees E JOIN departments D
ON E.department_id = D.department_id
WHERE D.department_id =90;
# 3.选择所有有奖金的员工的 last_name , department_name , location_id , city
SELECT E.last_name,E.commission_pct,D.department_name,D.location_id,L.city
FROM employees E LEFT JOIN departments D
ON E.department_id = D.department_id
LEFT JOIN locations L
ON D.location_id = L.location_id
WHERE E.commission_pct IS NOT NULL;
# 4.选择city在Toronto工作的员工的 last_name , job_id , department_id , department_name
SELECT E.last_name,E.job_id,D.department_id,D.department_name,L.city
FROM employees E JOIN departments D
ON E.department_id = D.department_id
JOIN locations L
ON D.location_id = L.location_id
WHERE L.city = 'Toronto';
# 5.查询员工所在的部门名称、部门地址、姓名、工作、工资,其中员工所在部门的部门名称为’Executive’
SELECT D.department_name,L.city,E.last_name,E.job_id,E.salary
FROM employees E JOIN departments D
ON E.department_id = D.department_id
JOIN locations L
ON D.location_id = L.location_id
WHERE D.department_name = 'Executive';
# 6.选择指定员工的姓名,员工号,以及他的管理者的姓名和员工号,结果类似于下面的格式
# employees Emp
# manager Mgr
# kochhar 101 king 100
SELECT EMP.last_name ,EMP.employee_id AS "ENP" ,MRG.last_name,MRG.employee_id AS "MRG"
FROM employees EMP LEFT JOIN employees MRG
ON EMP.employee_id = MRG.employee_id
# 7.查询哪些部门没有员工
SELECT D.department_id
FROM departments D LEFT JOIN employees E
ON D.department_id = E.department_id
WHERE E.department_id IS NULL;
# 8. 查询哪个城市没有部门
SELECT L.city,D.department_id
FROM locations L LEFT JOIN departments D
ON L.location_id = D.location_id
WHERE D.location_id IS NULL;
# 9. 查询部门名为 Sales 或 IT 的员工信息
SELECT D.department_name,E.last_name
FROM departments D LEFT JOIN employees E
ON D.department_id = E.department_id
WHERE D.department_name IN('Sales','IT');
#1.所有有门派的人员信息
#( A、B两表共有)
SELECT *
FROM t_dept A JOIN t_emp B
ON A.id = B.id;
#2.列出所有用户,并显示其机构信息
#(A的全集)
SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.id = B.id;
#3.列出所有门派
#(B的全集)
SELECT *
FROM t_dept B;
#4.所有不入门派的人员
#(A的独有)
SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.id = B.id
WHERE B.id IS NULL;
#5.所有没人入的门派
#(B的独有)
SELECT *
FROM t_dept B LEFT JOIN t_emp A
ON A.deptId = B.id
WHERE A.deptId IS NULL;
#6.列出所有人员和机构的对照关系
#(AB全有)
SELECT *
FROM t_dept B RIGHT JOIN t_emp A
ON A.id = B.id;
#7.列出所有没入派的人员和没人入的门派
#(A的独有+B的独有)
SELECT *
FROM t_emp A LEFT JOIN t_dept B
ON A.id = B.id
WHERE B.id IS NULL
UNION
SELECT *
FROM t_emp A RIGHT JOIN t_dept B
ON A.deptId = B.id
WHERE A.deptId IS NULL;
6、函数
- 从实现功能的角度分为数值函数、字符串函数、日期函数、时间函数、流程控制函数、加密函数和解密函数
- 函数又分为单行函数和多行函数
6.1数值函数
![image-20220915104032704](https://ucc.alicdn.com/cquxq7ohwraeq_20230915_96748f9bef13405c82f87c07ffbe86ee.png?x-oss-process=image/resize,w_1400/format,webp)
- 函数类型有很多,需要什么去百度就行,重心是五大聚合函数
6.2、聚合函数
6.2.1、常见的几个聚合函数
- AVG / SUM (即平均数和总和):只适用于数值类型的字段(或变量)
SELECT AVG(salary)
FROM employees; #即平均工资
SELECT SUM(salary)
FROM employees; #即工资总和
SELECT AVG(last_name)
FROM employees; #这块写的是姓名的平均数,显然不合理,所以得出结论平均数和总和只能用于数值类型的字段
- MAX / MIN (即最大值和最小值):适用于所有数据类型
SELECT MAX(salary)
FROM employees; #即最高工资
SELECT MIN(salary)
FROM employees; #即最低工资
SELECT MIN(last_name)
FROM employees #这块写的是让字符串之间相互比较可行,所以得出记录最大值和最小值适用于所有数据类型
- COUNT 作用:计算指定字段在查询结构中出现的个数
#涉及到一个问题,如果要计算表中有多少条记录,如何实现?
#方式一:COUNT(*)
SELECT COUNT(*)
FROM employees;
#方式二:COUNT(1)
SELECT COUNT(1)
FROM employees;
#方式三:COUNT(具体字段) :但是不一定对!!因为计算指定字段出现的个数时,是不计算NULL值的,所有当表中数据有NULL值的时候就不精确
SELECT COUNT(salary)
FROM employees; #即查询表中有多少个字段的个数,跟表中数据并无关系
6.2.2、GROUP BY的使用
SELECT department_id,AVG(salary),sum(salary)
FROM employees
GROUP BY department_id; #即根据部门id来算平均工资,然后也查出了最高工资
- 需求二:根据 job_id进行分组,查询出部门的最高工资
SELECT job_id,AVG(salary)
FROM employees
GROUP BY job_id;
- 需求三:先根据部门id进行分组,再根据工种进行分组
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY department_id, job_id; #即用逗号隔开分组条件即可
6.2.3、HAVING的使用
- 作用:用来过滤数据的
- 同样从需求出发:查询各个部门中最高工资比10000高的部门信息
SELECT department_id,MAX(salary)
FROM employees
WHERE MAX(salary)>10000
GROUP BY department_id; #错误写法,由此得出结论,一旦过滤条件使用了函数或聚合函数,就不能用WHERE关键字去过滤数据
#正确写法如下:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000; #HAVING必须声明在GROUP BY后面
- 得出结论:开发中,使用HAVING的前提是SQL中使用了GROUP BY
- 练习:查询部门id为10,20,30,40这四个部门中最高工资比10000高的部门信息
#写法一:推荐使用,因为执行方式高于方式二
SELECT department_id,MAX(salary)
FROM employees
WHERE department_id IN(10,20,30,40)
GROUP BY department_id
HAVING MAX(salary)>10000;
#写法二:
SELECT department_id,MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 AND department_id IN(10,20,30,40);
- 结论:当过滤条件中有聚合函数时,则过滤条件必须声明在HAVING中
- 当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE中或HAVING中都可以,但是能使用最好使用在WHERE中
6.2.4、SQL92语法和SQL99语法的语法结构
![image-20220915161344605](https://ucc.alicdn.com/cquxq7ohwraeq_20230915_eba567239aac4e3bbc0f3fda62554826.png?x-oss-process=image/resize,w_1400/format,webp)
6.3、聚合函数的课后练习
#2、查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;
#3、查询各job_id的员工工资的最大值,最小值,平均值,总和
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees
GROUP BY job_id;
#4、选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;
#5、查询员工最高工资和最低工资的差距
SELECT MAX(salary)- MIN(salary) AS "DIFFERNCE"
FROM employees;
#6、查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内
SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;
#7、查询所有部门的名字,location_id,员工数量和平均工资,并按平均工资降序
SELECT department_name,location_id,COUNT(employee_id),AVG(salary) AS "AVG"
FROM departments D LEFT JOIN employees E
ON D.department_id = E.department_id
GROUP BY department_name, location_id
ORDER BY AVG DESC;
#8、查询每个工种、每个部门的部门名、工种名和最低工资
SELECT D.department_name,E.job_id,MIN(E.salary)
FROM departments D LEFT JOIN employees E
ON D.department_id = E.department_id
GROUP BY department_name,job_id;