MySQL学习笔记（学习完之后的课后练习~）

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数值函数

• 函数类型有很多，需要什么去百度就行，重心是五大聚合函数

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语法的语法结构

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;