04-MySQL分组查询与多表查询

今天药忘吃喽~ 2022-09-08 11:50 207阅读 0赞

### 文章目录 ###

*   *  1. 分组查询
     *   *  1.1 简单的分组查询
         *  3.2 分组前的筛选
         *  3.3 分组后的筛选
         *  3.4 总结
     *  4. 多表查询
     *   *  4.1 SQL92语法
         *   *  4.1.1 等值连接
             *  4.1.2 非等值连接
             *  4.1.3 自连接
         *  4.2 SQL99语法
         *   *  4.2.1 等值连接
             *  4.2.2 非等值连接
             *  4.2.3 自连接
             *  4.2.4 外连接

--------------------

## 1. 分组查询 ##

分组数据: GROUP BY 子句语法

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

SELECT column, group_function(column)
    FROM table
    [WHERE condition]
    [GROUP BY group_by_expression]
    [ORDER BY column];

明确：WHERE一定放在FROM后面

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

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

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

SELECT AVG(salary)
    FROM employees
    GROUP BY department_id ;

### 1.1 简单的分组查询 ###

（1）按照单个字段分组

# 查询每个工种的最高工资
    SELECT MAX(salary), job_id
    FROM employees
    GROUP BY job_id;
    
    # 查询每个位置上的部门个数
    SELECT COUNT(*), location_id
    FROM employees
    GROUP BY location_id;

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

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

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

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

（4）添加排序

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

### 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;

### 3.3 分组后的筛选 ###

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

过滤分组：HAVING 子句

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

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

### 3.4 总结 ###

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

<table> 
 <thead> 
  <tr> 
   <th align="center"></th> 
   <th align="center">数据源</th> 
   <th align="center">位置</th> 
   <th align="center">关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">分组前筛选</td> 
   <td align="center">原始表</td> 
   <td align="center">group by 子句的前面</td> 
   <td align="center">WHERE</td> 
  </tr> 
  <tr> 
   <td align="center">分组后筛选</td> 
   <td align="center">分组后的结果集</td> 
   <td align="center">group by 子句的后面</td> 
   <td align="center">HAVING</td> 
  </tr> 
 </tbody> 
</table>

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

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

## 4. 多表查询 ##

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

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

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

按功能分为三类：

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

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

（3）**交叉连接**

### 4.1 SQL92语法 ###

#### 4.1.1 等值连接 ####

**（1）基本语法：**

语法：`select name,boyName from beauty,boys;`  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_34_color_FFFFFF_t_70_g_se_x_16]  
**笛卡尔集**的错误情况：

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：查询女神名和对应的男神名
    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';

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

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

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

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

（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;

（5）可以加分组

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

（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;

（7）连接多个表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_29_color_FFFFFF_t_70_g_se_x_16]

练习：查询出公司员工的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';

**连接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';

#### 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';

### 4.2 SQL99语法 ###

基本语法：

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

分类：

（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;

#### 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;

#### 4.2.3 自连接 ####

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

#### 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;

# 查询哪个部门没有员工
    #左外连接
    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没有的+表2中有但表1没有的

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

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

**总结：**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_37_color_FFFFFF_t_70_g_se_x_16]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_34_color_FFFFFF_t_70_g_se_x_16 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_34_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/40cf45df7e904267aefaea0f8f81d9f7.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_29_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/5c1571d8e5c446939fac6292824ff1a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_37_color_FFFFFF_t_70_g_se_x_16]: /images/20220829/fada74bd5f714093bc41788c320f88ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_Q1NETiBAU3dlZXRfcGlu_size_34_color_FFFFFF_t_70_g_se_x_16 1]: /images/20220829/6e3ecc4dc17841fb99de0f5f1272aff5.png