MySQL--Day03

2021/12/22 2:20:21

编程Tag： mysql select id -- employees salary department day03

本文主要是介绍MySQL--Day03，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

使用工具：SQLyog
使用语言：SQL
学习日期：03
学习内容：

Day03

聚合函数
- 聚合函数练习
子查询
- 单行子查询
- 多行子查询

聚合函数

-- 聚合函数
-- 常用的聚合函数
/*
AVG / SUM

MAX / MIN

COUNT
*/
-- 1.1 AVG / SUM  只适用于数值类型的字段或者变量
SELECT AVG(salary),SUM(salary)
FROM employees;

-- 1.2 MAX / MIN 求最大最小值 可以适用于数值类型，字符串类型，日期时间类型的字段和变量
SELECT MAX(salary),MIN(salary)
FROM employees;

-- COUNT 计算指定字段在查询结果中出现的个数
-- 注意：COUNT不计算null值
SELECT COUNT(salary),COUNT(1),COUNT(*)
FROM employees;

-- 如果需要计算表中的记录数，使用COUNT(*),COUNT(1),COUNT(具体字段)哪一个效率更高
-- 如果使用的是MyISAM引擎，则三者效率相同都是O(1)
-- 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(具体字段)
 

-- GROUP BY的使用(分组操作)
-- 练习：查询各个部分的平均工资，最高工资
SELECT AVG(salary),SUM(salary),department_id
FROM employees
GROUP BY department_id;

-- 查询各个department_id job_id的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id

-- 结论：SELECT中出现的非组函数的字段必须声明在GROUP BY中，反之GROUP BY中声明的字段可以不出现在SELECT中
-- 结论：GROUP BY声明在FROM后面，WHERE后面，ORDER BY前面，LIMINT前面
-- 结论：MySQL中GROUP BY中使用WITH ROLLUP
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id  WITH ROLLUP;

-- 查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) 'avg_salary'
FROM employees
GROUP BY department_id
ORDER BY avg_salary ASC;

-- HAVING的使用(用来过滤数据)
-- 1，如果过滤条件中使用了聚合函数，必须使用HAVING来替换WHERE，否则报错
-- 2，HAVING必须声明在GROUP BY的后面
-- 3，开发中我们使用HAVING的前提是SQL中使用了GROUP BY
-- 查询各个部门中最高工资比一万高的部门信息
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000

-- 查询部门ID未10，20，30，40中的最高工资比10000高的部门信息
-- 方式1:推荐使用，执行效率高于方式2
SELECT MAX(salary),department_id
FROM employees
WHERE department_id IN (10,20,30,40)
GROUP BY department_id
HAVING MAX(salary) > 10000;


-- 方式2
SELECT MAX(salary),department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000
AND department_id IN (10,20,30,40);

-- 结论：当过滤条件中有聚合函数时，则此过滤条件蓄婢声明在HAVING中
-- 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中和HAVING中都可以，建议声明在WHERE中
/*
WHERE与HAVING的对比

1，适用范围：HAVING的范围更广
2，如果过滤条件没有聚合函数，WHERE的执行效率要高于HAVING

*/

-- SQL的底层原理
-- 1，SELECT语句的完整结构
/*
#SQL92写法
SELECT ......（存在聚合函数）
FROM .....
WHERE 多表的连接  AND  过滤条件，不包含聚合函数的
GROUP BY....
HAVING ...（过滤条件，可以包含聚合函数）
ORDER BY .....（ASC/DESC）
LIMIT ....
2
#SQL99写法
SELECT ......（存在聚合函数）
FROM .....（LEFT / RIGHT）JOIN .....ON多表的连接条件
（LEFT / RIGHT）JOIN .....ON .....
WHERE 多表的连接  AND  过滤条件，不包含聚合函数的
GROUP BY....
HAVING ...（过滤条件，可以包含聚合函数）
ORDER BY .....（ASC/DESC）
LIMIT ....

*/
-- 2，SQL语句的执行过程：
-- FROM ...--> ON --> (LEFT / RIGHT  JOIN) --> WHERE --> GROUP BY --> HAVING --> SELECT --> DISTINCT --> ORDER BY --> LIMIT

聚合函数练习

#1.where子句可否使用组函数进行过滤?
-- 不可以
#2.查询公司员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary)
FROM employees;

#3.查询各job_id的员工工资的最大值，最小值，平均值，总和
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary),job_id
FROM employees
GROUP BY job_id;

#4.选择具有各个job_id的员工人数
SELECT job_id,COUNT(*)
FROM employees
GROUP BY job_id;

# 5.查询员工最高工资和最低工资的差距（DIFFERENCE）
SELECT MAX(salary) - MIN(salary) AS 'DIFFERENCE'
FROM employees;

# 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内
SELECT 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)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,location_id

# 8.查询每个工种、每个部门的部门名、工种名和最低工资

SELECT d.department_name,e.job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON d.`department_id` = e.`department_id`
GROUP BY department_name,job_id

子查询

-- 子查询
-- 需求：谁的工资比Abel的高
-- 方式1：
SELECT last_name,salary
FROM employees
WHERE last_name = "Abel"
SELECT last_name,salary
FROM employees
WHERE salary > 11000;

-- 方式2：自连接
SELECT e2.last_name,e2.salary
FROM employees e1,employees e2
WHERE e2.salary > e1.salary -- 多表的连接条件
AND e1.last_name = 'Abel';

-- 方式3：子查询
SELECT last_name,salary
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE last_name = "Abel"
		);

-- 2:称谓的规范：外查询（主查询）和内查询（子查询）
/*
注意事项
子查询（内查询）在主查询之前一次执行完成。
子查询的结果被主查询（外查询）使用 。
子查询要包含在括号内
将子查询放在比较条件的右侧
单行操作符对应单行子查询，多行操作符对应多行子查询

*/
/*
 3，子查询的分类
 3.1：单行子查询  多行子查询（从内查询返回的结果条目数）
 3.2：相关子查询  不相关子查询  （从内查询是否被执行多次）
	例子：查询工资大于本部门平均工资的员工信息（相关子查询）
	例子：查询工资大于本公司平均工资的员工信息（不相关子查询）
*/
-- 子查询编写技巧：①：从里往外写  ②：从外往里写

单行子查询

-- 4，单行子查询
-- 4.1：单行操作符：= > < >= <= <>
-- 练习：查询工资大于149号员工工资的员工信息

SELECT last_name,salary,employee_id
FROM employees
WHERE salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 149
		);

-- 练习：返回job_id与141员工相同，salary比143号员工多的员工姓名，job_id和工资

SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
		SELECT job_id
		FROM employees
		WHERE employee_id = 141
		)
AND salary > (
		SELECT salary
		FROM employees
		WHERE employee_id = 143
		);

-- 练习：返回公司工资最少的员工的last_name,job_id和salary

SELECT last_name,job_id,salary
FROM employees
WHERE salary = (
		SELECT MIN(salary)
		FROM employees
		);

-- 练习：查询与141号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id

SELECT employee_id,manager_id,department_id
FROM employees
WHERE manager_id = (
			SELECT manager_id
			FROM employees
			WHERE employee_id = 141
			)
AND department_id = (
			SELECT department_id
			FROM employees
			WHERE employee_id = 141
			)
AND employee_id <> 141;
			
-- 练习：查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT department_id,MIN(salary)			
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
			SELECT MIN(salary)
			FROM employees
			WHERE department_id = 50
			);

-- 练习：显式员工的employee_id,last_name和location。其中，
-- 若员工department_id与location_id为1800
-- 的department_id相同，则location为’Canada’，其余则为’USA’。

SELECT employee_id,last_name,(CASE department_id WHEN (SELECT department_id
							FROM departments
							WHERE department_id = 1800) 
							THEN 'Canada'
						 ELSE 'USA' END) AS "location"
FROM employees;

多行子查询

-- 5,多行子查询
/*
多行子查询操作符：IN / ANY / ALL / SOME(同ANY)

也称为集合比较子查询
内查询返回多行
使用多行比较操作符
*/
-- IN 举例：
SELECT employee_id,last_name
FROM employees
WHERE salary IN (
		SELECT MIN(salary)
		FROM employees
		GROUP BY department_id
		);
-- ANY 举例：
-- 返回其它job_id中比job_id为‘IT_PROG’部门任一工资低的员工的
-- 员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ANY(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		)

-- ALL 举例：
-- 返回其它job_id中比job_id为‘IT_PROG’部门所有工资低的员工的
-- 员工号、姓名、job_id 以及salary
SELECT employee_id,last_name,job_id,salary
FROM employees
WHERE job_id <> 'IT_PROG'
AND salary < ALL(
		SELECT salary
		FROM employees
		WHERE job_id = 'IT_PROG'
		)

-- 查询平均工资最低的部门id
-- FROM中也可以使用子查询
-- MySQL聚合函数不可以嵌套
-- 方式1
SELECT department_Id
FROM employees
GROUP BY department_id
HAVING AVG(salary) = (
			SELECT MIN(avg_sal)
			FROM (
				SELECT AVG(salary) AS avg_sal
				FROM employees
				GROUP BY department_id
				) t_dept_avg_sal
			)
-- 方式2
SELECT department_Id
FROM employees
GROUP BY department_id
HAVING AVG(salary) <= ALL(
			SELECT AVG(salary) AS avg_sal
			FROM employees
			GROUP BY department_id
			)
-- 5.3 空值问题
-- 例子
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			);
-- 改正后
SELECT last_name
FROM employees
WHERE employee_id NOT IN (
			SELECT manager_id
			FROM employees
			WHERE manager_id IS NOT NULL
			);

-- 6,相关子查询
-- 查询员工中工资大于本公司平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees
WHERE salary > (
		SELECT AVG(salary)
		FROM employees
		);

-- 查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name,salary,department_id
FROM employees e1
WHERE salary > (
		SELECT AVG(salary)
		FROM employees e2
		WHERE department_id = e1.`department_id`
		);
-- 方式2：在FROM中声明子查询
SELECT e.last_name,e.salary,e.department_id
FROM employees e,(
		SELECT department_id,AVG(salary) avg_sal
		FROM employees e2
		GROUP BY department_id
		) e2
WHERE e.`department_id` = e2.department_id
AND e.`salary` > e2.avg_sal

-- 查询员工的id,salary,按照department_name 排序
SELECT employee_id,salary
FROM employees e
ORDER BY (
	SELECT department_name
	FROM departments d
	WHERE e.`department_id` = d.`department_id`
	) DESC;

-- 结论：在SELECT中，除了GROUP BY和LIMIT中不可以写子查询，其他位置都可以

/*
若employees表中employee_id与job_history表中employee_id相同的数目不小于2，输出这些相同
id的员工的employee_id,last_name和其job_id
*/

SELECT employee_id,last_name,job_id
FROM employees e
WHERE 2 <= (
		SELECT COUNT(*)
		FROM job_history j
		WHERE e.`employee_id` = j.`employee_id` 
		)

--  EXISTS 与 NOT EXISTS关键字
-- 练习：查询公司管理者的employee_id，last_name，job_id，department_id信息
-- 方式1：
SELECT DISTINCT mgr.employee_id,mgr.last_name,mgr.job_id,mgr.department_id
FROM employees emp JOIN employees mgr
ON emp.manager_id = mgr.employee_id;

-- 方式2：子查询

SELECT employee_id,last_name,job_id,department_id
FROM employees
WHERE employee_id IN (
		SELECT DISTINCT manager_id
		FROM employees
		);

-- 方式3：使用EXISTS

SELECT employee_id,last_name,job_id,department_id
FROM employees e
WHERE EXISTS (
		SELECT *
		FROM employees e1
		WHERE e.`employee_id` = e1.`manager_id`
		);


-- 查询departments表中，不存在于employees表中的部门的department_id和department_name
-- 方式1
SELECT d.department_id,department_name
FROM employees e RIGHT  JOIN departments d
ON e.`department_id` = d.`department_id`
WHERE e.`department_id` IS NULL;

-- 方式2
SELECT d.department_id,department_name
FROM departments d
WHERE NOT EXISTS (
		SELECT *
		FROM employees e
		WHERE d.`department_id` = e.`department_id`
		);

这篇关于MySQL--Day03的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！