9.MySQL高阶查询方法——聚合查询 / 联合（多表）查询

￡神魔★判官ぃ 2022-10-31 05:26 20阅读 0赞

## 1. 聚合查询 ##

一般需要搭配MySQL中的一些内置“函数”

**1）count：用来计算结果的行数**

**2）sum**

<mysql> select * from exam_result;
    +------+-----------+---------+------+---------+
    | id   | name      | chinese | math | english |
    +------+-----------+---------+------+---------+
    |    1 | 唐三藏    |    28.5 | 80.0 |    67.8 |
    |    3 | 沙和尚    |    61.7 | 78.6 |    48.5 |
    |    4 | 猪八戒    |    66.0 | 80.0 |    67.8 |
    |    5 | 孙仲谋    |    21.0 | 23.0 |    91.5 |
    |    6 | 曹孟德    |    67.5 | 75.5 |    67.8 |
    |    7 | 刘玄德    |    66.9 | 80.0 |    69.8 |
    +------+-----------+---------+------+---------+
    6 rows in set (0.00 sec)
    
    <mysql> select sum(math) from exam_result;
    +-----------+
    | sum(math) |
    +-----------+
    |     417.1 |
    +-----------+
    1 row in set (0.00 sec)

**3）avg**

<mysql> select avg(math) from exam_result;
    +-----------+
    | avg(math) |
    +-----------+
    |  69.51667 |
    +-----------+
    1 row in set (0.00 sec)

**4）max**

<mysql> select max(math) from exam_result;
    +-----------+
    | max(math) |
    +-----------+
    |      80.0 |
    +-----------+
    1 row in set (0.03 sec)

**5）min**

<mysql> select min(math) from exam_result;
    +-----------+
    | min(math) |
    +-----------+
    |      23.0 |
    +-----------+
    1 row in set (0.00 sec)

求小于80的同学的平均分

<mysql> select avg(math) from exam_result where math < 80;
    +-----------+
    | avg(math) |
    +-----------+
    |  59.03333 |
    +-----------+
    1 row in set (0.00 sec)

**6）group by**  
把得到的查询结果集按照一定的规律分组（可以分成多个组）

select [列] from [表名] group by [前面的列]；

使用以上语句，就可以将这一列重复的类型压缩成一个

如果要求平均值

select [列] ，avg(列) from [表名] group by [前面的列]；

如果针对group by进行筛选，使用having

select [列] ，avg(列) from [表名] group by [前面的列] having avg(列) > 250;

## 1. 联合/多表查询 ##

实现联合查询的基本机制：笛卡尔积

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70]  
多表查询的过程就是先计算两张表的笛卡尔积，再根据一些条件对笛卡尔积中的记录进行筛选

如果针对两个比较大的表进行联合查询，笛卡尔积的计算开销会很大，最终的查找效率也比较低，在生产环境中，不应该对达标进行联合查询。

例子：

已经创建了四个表结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 1]

#### 1）内连接 ####

语法：

> select 字段 from 表1 别名1 \[inner\] join 表2 别名2 on 连接条件 and 其他条件  
> select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

**a）查找名字为“许仙”的分数**

select student.id,student.name,score.student_id,score.score from student,score where student.id = score.student_id and student.name ='许仙';

把where后面的条件称之为：连接条件  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 2]  
另外一种写法为：  
（join on 方式）

select student.id,student.name,score.student_id,score.score from student inner join score on student.id = score.student_id and student.name ='许仙';

**b）查找所有同学的总成绩以及基本信息**

select student.id,student.name,sum(score.score) from student,score where student.id = score.student_id group by student.id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 3]  
**b）查找所有同学的每一科的成绩以及基本信息**

select student.name, course.name, score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 4]

#### 2）自连接 ####

自连接是指在同一张表连接自身进行查询

**a）“计算机原理”分数大于“java”的分数的同学**

select s1.student_id, s1.score, s1.course_id, s2.student_id, s2.score, s2.course_id from score s1,score s2 
     where s1.student_id = s2.student_id and s1.course_id = 3 and s2.course_id = 1 and s1.score > s2.score;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 5]

#### 3）子查询 ####

**a）和某一同学同班的同学**

select name from student where classes_id = (select classes_id from student where name = '不想毕业');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 6]  
b）查询语文或者英文课程所对应的成绩

借助in：

select * from score where course_id in (select id from course where name = '语文' or name = '英文');

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 7]  
借助exist也可以：

select * from score where exists (select score.course_id from course where (name = '语文' or name = '英文') and course.id = score.course_id);

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 8]

#### 4）合并查询 ####

相当于把多个表查询的结果集合合并成一个集合（需要保证多个结果集之间的字段和数目都得一致）  
**a）查询id<3或者是英文课程**  
方法一：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 9]

方法二：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 10]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70]: /images/20221024/2e11f607ed33424088626ea4d33b0333.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 1]: /images/20221024/b3ccc6839314424d9e0e3ac6b5ff7d15.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 2]: /images/20221024/95cff02bc68347fe82030d93e3c5b993.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 3]: /images/20221024/d19491415d92453c80c686f40ff4c6e0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 4]: /images/20221024/b452024601b74e219908273022c46e09.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 5]: /images/20221024/80076247d76b459c82114d4fc8dd0806.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 6]: /images/20221024/b0ad50529e6d4f27b534e09b21ae7b3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 7]: /images/20221024/e91403b509254633a239f6b3f0b74930.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 8]: /images/20221024/bf48c3ab8be546bd88266a8e6d7c2430.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 9]: /images/20221024/fa9658d4e86d40f5907a1646b9b3fcf8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ1MTM2MTg5_size_16_color_FFFFFF_t_70 10]: /images/20221024/cc1466302e2342a0acc82d22b78a0159.png