04-MySQL分组查询与多表查询_多表 group by

---

1. 分组查询

分组数据: GROUP BY 子句语法

可以使用GROUP BY子句将表中的数据分成若干组

SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];
1
2
3
4
5

明确：WHERE一定放在FROM后面

在SELECT 列表中所有未包含在组函数中的列都应该包含在GROUP BY 子句中。

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;
1
2
3

包含在GROUP BY 子句中的列不必包含在SELECT 列表中

SELECT AVG(salary)
FROM employees
GROUP BY department_id ;
1
2
3

1.1 简单的分组查询

（1）按照单个字段分组

# 查询每个工种的最高工资
SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id;

# 查询每个位置上的部门个数
SELECT COUNT(*), location_id
FROM employees
GROUP BY location_id;
1
2
3
4
5
6
7
8
9

（2）按表达式或函数分组

# 按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些
SELECT COUNT(*), LENGTH(last_name) len_name
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;
1
2
3
4
5

（3）按多个字段分组，在GROUP BY子句中包含多个列

# 查询每个部门每个工种的员工的总工资
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;
1
2
3
4

（4）添加排序

# 查询每个部门每个工种的员工的平均工资，并按照高低显示
SELECT department_id, job_id, AVG(salary)
FROM employees
GROUP BY department_id, job_id ;
ORDER BY AVG(salary) DESC;
1
2
3
4
5

3.2 分组前的筛选

# 查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

# 查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary), manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;
1
2
3
4
5
6
7
8
9
10
11

3.3 分组后的筛选

非法使用组函数：不能在WHERE 子句中使用组函数。可以在HAVING 子句中使用组函数。

过滤分组：HAVING 子句

# 查询哪个部门的员工个数>2
SELECT COUNT(*), department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;
1
2
3
4
5

# 查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT MAX(salary), job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;
1
2
3
4
5
6

3.4 总结

分组查询中的筛选条件分为两类：

（1）分组函数做条件肯定是放在HAVING子句中

（2）能用分组前筛选的，就优先考虑使用分组前筛选

4. 多表查询

多表查询按年代语法分为两类：

（1）SQL92标准：仅仅支持内连接

（2）SQL99标准【推荐】：支持内连接+外连接（左外、右外，全外）+交叉连接

按功能分为三类：

（1）内连接：等值连接、非等值连接、自连接

（2）外连接：左外连接、右外连接、全外连接

（3）交叉连接

4.1 SQL92语法

4.1.1 等值连接

（1）基本语法：

语法：select name,boyName from beauty,boys;

笛卡尔集的错误情况：

select count(*) from beauty; 假设输出12行

select count(*)from boys; 假设输出4行

最终结果：12*4=48行

笛卡尔集会在下面条件下产生: –省略连接条件 / –连接条件无效 / –所有表中的所有行互相连接

为了避免笛卡尔集，可以在WHERE 加入有效的连接条件，即语法为

SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 =table2.column2;
1
2
3

# 案例1：查询女神名和对应的男神名
SELECT name, boyName
FROM beauty,boys
WHERE beauty.boyfriend_id = boys.id;

# 案例2：查询员工名和对应的部门名
SELECT last_name,department_id
FROM employees,departments
WHERE employees.'department.id' = departments.'department.id';
1
2
3
4
5
6
7
8
9

（2）区分重复的列名：使用表名前缀在多个表中区分相同的列。

如果使用了表别名，则在select语句中需要使用表别名代替表名

SELECT bt.id, NAME, boyname  #此处必须用别名
FROM beauty bt, boys b;
WHERE bt.`boyfriend_id`=b.id ;
1
2
3

# 案例：查询员工名、工种号、工种名
SELECT e.last_name, e.job_id, j.job_title
FROM employees e, jobs j
WHERE e.'job_id' = j.'job_id';
1
2
3
4

（3）两个表的顺序是可以换的

（4）加筛选条件

# 查询有奖金的员工名、部门名
SELECT last_name, department_name, commission_pct
FROM employees e, departments d
WHERE e.'department_id' = d.'department_id'
AND e.'commission_pct' IS NOT NULL;
1
2
3
4
5

（5）可以加分组

# 查询每个城市的部门个数
SELECT COUNT(*), city
FROM departments d, locations l
WHERE d.'location_id' = l.'location_id'
GROUP BY city;
1
2
3
4
5

（6）可以加排序

# 查询每个工种的工种名和员工的个数，并且按员工个数降序
SELECT job_title, COUNT(*)
FROM employee e,jobs j
WHERE e.'job_id' = j.'job_id'
GROUP BY job_title
ORDER BY COUNT(*) DESC;
1
2
3
4
5
6

（7）连接多个表

练习：查询出公司员工的last_name, department_name, city

SELECT last_name, department_name, city
FROM employees e, departments d, locations l
WHERE e.'department_id' = d.'department_id'
AND d.'location_id' = l.'location_id';
1
2
3
4

连接n个表,至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。

总结：

• 多表等值连接结果为多表的交集部分
• n表连接，至少需要n-1个连接条件
• 多表的顺序没有要求
• 一般需要为表其别名
• 可以搭配前面介绍的所以子句使用，比如排序，分组，筛选等等

4.1.2 非等值连接

# 查询员工的工资和工资级别
SELECT salary, grade_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.'lowest_sal' AND g.'highest_sal'
AND g.'grade_level'='A';
1
2
3
4
5

4.1.3 自连接

# 查询员工名和上级的名称
SELECT e.employee_id, e.last_name, m.employee_id, m.last_name
FROM employees e, employees m  #将一张表看成两张e m
WHERE e.'manager_id'=m.'employee_id';
1
2
3
4

4.2 SQL99语法

基本语法：

SELECT 查询列表
FROM 表1 别名 
【连接类型】JOIN 表2 别名
ON 连接条件
WHERE 筛选条件
GROUP BY 分组
HAVING 筛选条件
ORDER BY 排序列表
1
2
3
4
5
6
7
8

分类：

（1）内连接：inner

（2）外连接：左外：left【outer】、右外：right【outer】、全外：full【outer】

（3）交叉连接：cross

4.2.1 等值连接

# 案例1：查询员工名，部门名
SELECT last_name ,department_name
FROM departments d
INNER JOIN employees e
ON e.'department_id'=d.'department_id';

# 案例2：查询名字中包含e的员工名和工种名（添加筛选）
SELECT last_name, job_title
FROM employees e
INNER JOIN jobs j
ON e.'job_id'=j.'job_id'
WHERE e.'last_name' LIKE '%e%'

# 查询部门个数>3的城市名和部门个数（添加分组+筛选）
SELECT COUNT(*), city
FROM departments d
INNER JOIN locations l
ON d.'location_id' = l.'location_id'
GROUP BY city
HAVING COUNT(*)>3;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.2.2 非等值连接

# 查询工资级别的个数>20的个数，并且按工资级别降序
SELECT COUNT(*), grade_level
FROM employees e
JOIN job_grades g   (INNER可以省略)
ON e.salary BETWEEN g.'lowest_sal' AND g.'highest_sal'
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
1
2
3
4
5
6
7
8

4.2.3 自连接

# 查询员工名和上级的名称
SELECT e.last_name, m.last_name
FROM employees e
JOIN employees m  
ON e.'manager_id'=m.'employee_id';
1
2
3
4
5

4.2.4 外连接

应用场景：用于查询一个表中有，而另一个表中没有记录的情况

特点：

（1）外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的就显示匹配的值，如果从表中没有和它匹配的就显示null

​ 外连接查询结果 = 内连接结果 + 主表中有而从表中没有的纪录

（2）左外连接，left join 左边的是主表

​ 右外连接，right join 右边的是主表

（3）左外和右外交换两个表的顺序，可以实现相同的效果

# 查询男朋友不在男神表的女神名
#左连接
SELECT b.name
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.'boyfriend_id'= bo.'id'
WHERE bo.'id' IS NULL;
#右连接
SELECT b.name
FROM boys bo
RIGHT OUTER JOIN beauty b
ON b.'boyfriend_id'= bo.'id'
WHERE bo.'id' IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13

# 查询哪个部门没有员工
#左外连接
SELECT d.*, e.employee_id
FROM departments d
LEFT OUTER JOIN employees e
ON d.'department_id' = e.'department_id'
WHERE e.'employee_id' IS NULL;

#右外连接
SELECT d.*, e.employee_id
FROM employees e
LEFT OUTER JOIN departments d
ON d.'department_id' = e.'department_id'
WHERE e.'employee_id' IS NULL;
1
2
3
4
5
6
7
8
9
10
11
12
13
14

全外连接=内连接的结果+表1有但表2没有的+表2中有但表1没有的

交叉连接即笛卡尔乘积结果

SELECT b.*, bo.*
FROM beauty b
CROSS JOIN boys bo;
1
2
3

总结：