『 MySQL篇 』：MySQL表的聚合与联合查询

目录

• • • • 一. 聚合查询
      • • 1.1 聚合函数
        • 1.2 `GROUP BY`子句
        • 1.3 `HAVING`子句
      • 二 . 多表关系
      • 三、联合查询
      • • 3.1 内连接
        • 3.2 外连接
        • 3.3 自连接
        • 3.4 合并查询
        • 3.4 子查询

一. 聚合查询

1.1 聚合函数

一般情况下，我们需要的聚合数据(总和，平均数，最大最小值等)并不总是存储在表中。 但是，可以通过执行存储数据的计算来获取它。

MySQL提供了许多聚合函数，包括AVG，COUNT，SUM，MIN，MAX等。除COUNT函数外，其它聚合函数在执行计算时会忽略NULL值 , 同时 , 聚合函数不允许嵌套使用 .

以上的聚合函数可以在列名和表达式之前加上 distinct, 先对查询到的数据进行去重, 再进行计算

下面,我们将创建一组示例数据,对以上的聚合函数进行演示,

--- 创建学生成绩表mysql> create table exam(    -> id int primary key comment '学号',    -> name varchar(20) comment '学生姓名',    -> email varchar(50) not null comment '电子邮箱',    -> chinese decimal(4,1) comment '语文成绩',    -> english decimal(4,1) comment '英语成绩',    -> math decimal(4,1)  comment'数学成绩'    -> );Query OK, 0 rows affected (0.02 sec)--- 插入学生成绩数据mysql> insert into exam values(202301,'张华','123452@163.com',69,112,110),    -> (202302,'李三','1452563@163.com',115.5,120,89),    -> (202303,'宋七','36215465@qq.com',110,113,66),    -> (202304,'王五','15547522@163.com',89,65,78),    -> (202305,'赵四','15623355@163.com',90,112,130),    -> (202306,'李八','18625222@163.com',null,null,null);Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0---学生成绩表结构mysql> desc exam;+---------+--------------+------+-----+---------+-------+| Field   | Type         | Null | Key | Default | Extra |+---------+--------------+------+-----+---------+-------+| id      | int(11)      | NO   | PRI | NULL    |       || name    | varchar(20)  | YES  |     | NULL    |       || email   | varchar(50)  | NO   |     | NULL    |       || chinese | decimal(4,1) | YES  |     | NULL    |       || english | decimal(4,1) | YES  |     | NULL    |       || math    | decimal(4,1) | YES  |     | NULL    |       |+---------+--------------+------+-----+---------+-------+6 rows in set (0.00 sec)--- 数据详表mysql> select * from exam;+--------+------+------------------+---------+---------+-------+| id     | name | email            | chinese | english | math  |+--------+------+------------------+---------+---------+-------+| 202301 | 张华 | 123452@163.com   |    69.0 |   112.0 | 110.0 || 202302 | 李三 | 1452563@163.com  |   115.5 |   120.0 |  89.0 || 202303 | 宋七 | 36215465@qq.com  |   110.0 |   113.0 |  66.0 || 202304 | 王五 | 15547522@163.com |    89.0 |    65.0 |  78.0 || 202305 | 赵四 | 15623355@163.com |    90.0 |   112.0 | 130.0 || 202306 | 李八 | 18625222@163.com |    NULL |    NULL |  NULL |+--------+------+------------------+---------+---------+-------+6 rows in set (0.01 sec)

• count()函数

使用 count()函数 可以返回数据表中的行数 , 允许对表中所有符合特定条件的所有行进行计数 .

#计算表中的总行数mysql> select count(*) from exam;+----------+| count(*) |+----------+|        6 |+----------+1 row in set (0.01 sec#计算表中chinese中的总行数mysql> select count(math) from exam;+-------------+| count(math) |+-------------+|           5 |+-------------+1 row in set (0.00 sec)#计算表中english列的总行数,并去重mysql> select count(distinct english) from exam;+-------------------------+| count(distinct english) |+-------------------------+|                       4 |+-------------------------+1 row in set (0.01 sec)

COUNT(*)函数计算包含NULL和非NULL值的行，即：所有行 , 而count (列名) 进行计算时 , 不会将值为NULL的行计算在内, 即所有的非空行 . count(distinct 列名) 则会返回不包含空值的唯一行数 .

结合where语句进行使用

#查询exam表当中, 英语成绩为112的人数mysql> select count(english) from exam where english = 112;+----------------+| count(english) |+----------------+|              2 |+----------------+1 row in set (0.00 sec)

• sum() 函数

SUM()函数返回一组值的总和，SUM()函数忽略NULL值。如果找不到匹配行，则SUM()函数返回NULL值。

#计算数学成绩的总和, 并命名为emathmysql> select sum(math) as emath from exam;+-------+| emath |+-------+| 473.0 |+-------+1 row in set (0.00 sec)#计算英语成绩低于120分的成绩总和mysql> select sum(english) from exam where english < 120;+--------------+| sum(english) |+--------------+|        402.0 |+--------------+1 row in set (0.00 sec)

• avg()函数

AVG()函数计算一组值的平均值。 它计算过程中是忽略NULL值的 , 使用 avg() 函数可以的到 一组数据的平均值 .

# 计算所有同学的语文成绩的平均值mysql> select avg(chinese) from exam;+--------------+| avg(chinese) |+--------------+|     94.70000 |+--------------+1 row in set (0.03 sec)#计算总分的平均分mysql> select avg(math+chinese+english) as 总均分 from exam;+-----------+| 总均分    |+-----------+| 293.70000 |+-----------+1 row in set (0.00 sec)

• MAX()函数

MAX()函数返回一组值中的最大值。MAX()函数在许多查询中非常方便，例如查找最大数量，最昂贵的产品以及客户的最大付款数等 .

# 查询总分最高的同学总分mysql> select max(math+english+chinese) as 总成绩 from exam;+--------+| 总成绩 |+--------+|  332.0 |+--------+1 row in set (0.00 sec)#查询单科英语成绩的最高分的姓名mysql>  select max(english) from exam;+--------------+| max(english) |+--------------+|        120.0 |+--------------+1 row in set (0.00 sec)

不使用max()函数的情况 , 也可以使用order by对数据集进行降序,并使用limit字句对结果集进行降序排序

mysql> select (chinese+math+english) as total  from exam order by total desc limit 1;+-------+| total |+-------+| 332.0 |+-------+1 row in set (0.00 sec)

如果要找到总成绩最高的同学的全部信息 , 需要使用子查询进行

# 查询总成绩最高的同学信息mysql> select *    -> from exam    -> where chinese+math+english = (select max(chinese+math+english)    ->                               from exam    ->                              );+--------+------+------------------+---------+---------+-------+| id     | name | email            | chinese | english | math  |+--------+------+------------------+---------+---------+-------+| 202305 | 赵四 | 15623355@163.com |    90.0 |   112.0 | 130.0 |+--------+------+------------------+---------+---------+-------+1 row in set (0.00 sec)

• Min()函数

Min()函数用于返回一组值当中的最小值 , 通常用于 查询一组数据当中最小的值 , 如 最小单价, 最小分数 等 , 和 max() 函数的用法类似 。

# 查询最小的总分mysql> select min(math + english + chinese) as total from exam;+-------+| total |+-------+| 232.0 |+-------+1 row in set (0.00 sec)#查询最小总分的全部信息mysql> select * from exam        where chinese+english+math = (select min(math + english + chinese) as                                      total from exam);+--------+------+------------------+---------+---------+------+| id     | name | email            | chinese | english | math |+--------+------+------------------+---------+---------+------+| 202304 | 王五 | 15547522@163.com |    89.0 |    65.0 | 78.0 |+--------+------+------------------+---------+---------+------+1 row in set (0.00 sec)

MySQL 当中除了一些常用的聚合函数外 , 还包括一些字符串函数 , 日期时间函数 , 控制流函数等 , 下面再列举一些比较常见的其他函数 .

• IFNULL函数

IFNULL函数 是MySQL控制流函数之一，它接受两个参数 ， 如果不是 NULL ，则返回第一个参数，否则IFNULL函数 返回第二个参数

IFNULL(expression_1,expression_2);

如果expression_1不为NULL，则IFNULL函数返回expression_1; 否则返回expression_2的结果。

示例表如下所示：

#查找某位同学的联系方式 （如果电话为空，使用邮箱）mysql> select name,ifnull(phone,email) as 联系方式 from exam;+------+------------------+| name | 联系方式         |+------+------------------+| 张华 | 13225631456      || 李三 | 15698475235      || 宋七 | 15236486952      || 王五 | 15547522@163.com || 赵四 | 13562698745      || 李八 | 18625222@163.com |+------+------------------+6 rows in set (0.00 sec)#其中，王五和李八的电话为空，则使用其电子邮箱作为其联系方式。

• 日期、时间函数

mysql> select year('2018-01-01');+--------------------+| year('2018-01-01') |+--------------------+|               2018 |+--------------------+1 row in setmysql> select dayname('2018-01-01') dayname;+---------+| dayname |+---------+| Monday  |+---------+1 row in set

1.2 GROUP BY子句

GROUP BY子句通过列或表达式的值将一组行分组为一个小分组的汇总行记录。 GROUP BY子句为每个分组返回一行。换句话说，它减少了结果集中的行数，当GROUP BY子句与聚合函数相结合时， 可以返回每个分组的单个值。

#示例职工表mysql> create table emp(    ->  id int primary key auto_increment,    ->  name varchar(20) not null,    ->  role varchar(20) not null,    ->  salary numeric(11,2)    -> );Query OK, 0 rows affected (0.05 sec)mysql> insert into emp(name, role, salary) values    -> ('张三','科员', 2000.54),    -> ('宋七','副厅', 8996.99),    -> ('赵四','科员', 1800.11),    -> ('李八','科长', 4540.5),    -> ('宋九','科员', 2356.33);Query OK, 5 rows affected (0.01 sec)Records: 5  Duplicates: 0  Warnings: 0mysql> select * from emp;+----+------+------+---------+| id | name | role | salary  |+----+------+------+---------+|  1 | 张三 | 科员 | 2000.54 ||  2 | 宋七 | 副厅 | 8996.99 ||  3 | 赵四 | 科员 | 1800.11 ||  4 | 李八 | 科长 | 4540.50 ||  5 | 宋九 | 科员 | 2356.33 |+----+------+------+---------+5 rows in set (0.00 sec)

对职工表进行分组查询 ， 查询已知数据当中每个职位的最低和最高工资

mysql> select role,max(salary),min(salary) from emp group by role;+------+-------------+-------------+| role | max(salary) | min(salary) |+------+-------------+-------------+| 副厅 |     8996.99 |     8996.99 || 科员 |     2356.33 |     1800.11 || 科长 |     4540.50 |     4540.50 |+------+-------------+-------------+3 rows in set (0.00 sec)

1.3 HAVING子句

GROUP BY子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要使用 HAVING子句

再次对上述的职工表进行操作，找出最高工资大于4000的职工职位角色和工资

mysql> select role,max(salary) from emp group by role having max(salary) > 4000;+------+-------------+| role | max(salary) |+------+-------------+| 副厅 |     8996.99 || 科长 |     4540.50 |+------+-------------+2 rows in set (0.00 sec)

条件筛选可以使用where, order by, limit等来实现，也可以不使用

HAVING子句和where语句的使用区别

• 分组之前指定条件, 也就是先筛选再分组, 使用where关键字.
• 分组之后指定条件, 也就是先分组再筛选, 使用group by关键字

where和group by语法上要注意区分, where语句紧跟在表名后, 而having跟在group by后 .

示例：查询工资小于5000的职工中，各职工角色的平均工资（保留两位小数）

mysql> select role,round(avg(salary)) from emp where salary < 5000 group by role;+------+--------------------+| role | round(avg(salary)) |+------+--------------------+| 科员 |               2052 || 科长 |               4541 |+------+--------------------+2 rows in set (0.01 sec)

二 . 多表关系

• 概述

​ 项目开发当中,在进行数据库的表结构设计时,会根据业务需求和业务模块之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着某种联系 , 基本上分为三种: 一对多/多对多/一对一.

• 一对多(多对一)

案例 : 班级与学生的关系

关系 : 一个班级拥有多个学生 , 一个学生属于一个班级

实现 : 在多的 一方(学生) 建立外键, 指向另一方的主键

• 多对多

案例 : 学生与课程的关系

关系 :一名学生可以选修多门课程 , 一门课程也可以供多个学生来选择 .

实现 : 建立第三张中间表, 中间表中至少包含两个外键,分别关联两表的主键

• 一对一

案例 : 用户与用户详情之间的关系

关系 : 多用于单表拆分 , 将一张表的基础字段放在一张表当中 , 其他详情字段放在另一张表当中,以提升操作效率

实现 : 在任意一方加入外键,关联另一方的主键 , 并设置外键是唯一的

三、联合查询

实际开发当中往往要使用多个表的的数据,所以需要多表联合查询 , 多表联合查询时是对多张表的数据取笛卡尔积 , 然后对联合表中筛选出需要的数据

对n条记录的表A和m条记录的表B进行笛卡尔积，一共会产生n*m条记录, 当两张表的数据量很大的时候, 这个操作就非常危险了 , 需要慎重使用

mysql> select * from class;+----------+------------+|    id    |   name     |+----------+------------+|        1 | 计科1班    ||        2 | 计科2班    |+----------+------------+2 rows in set (0.00 sec)mysql> select * from student;+------+--------+----------+| id   | name   | class_id |+------+--------+----------+|    1 | 张三   |        1 ||    2 | 李四   |        1 ||    3 | 王五   |        2 ||    4 | 赵六   |        2 |+------+--------+----------+#两个表进行笛卡尔集之后可以得到结果mysql> select * from student, class;+------+--------+----------+----------+------------+| id   | name   | class_id |   id     | name       |+------+--------+----------+----------+------------+|    1 | 张三   |        1 |        1 | 计科1班    ||    1 | 张三   |        1 |        2 | 计科2班    ||    2 | 李四   |        1 |        1 | 计科1班    ||    2 | 李四   |        1 |        2 | 计科2班    ||    3 | 王五   |        2 |        1 | 计科1班    ||    3 | 王五   |        2 |        2 | 计科2班    ||    4 | 赵六   |        2 |        1 | 计科1班    ||    4 | 赵六   |        2 |        2 | 计科2班    |+------+--------+----------+----------+------------+8 rows in set (0.00 sec)

上述两表进行笛卡尔集之后 ， 得到了 4 * 2 即 8 条的结果 ， 在这之中只有class_id相同的数据才是有效数据 ， 所以要添加限制条件 才能筛选出有效数据

3.1 内连接

内连接查询的是两张表交集的部分

• 隐式内连接

select 字段列表 from 表1 ，表2 where ... = .. .;

• 显式内连接

select  字段列表 from 表1 inner join 表2 on 连接条件

以上述两张学生表和课程表为例 ， 查询一名学生的姓名 ， 以及该学生所在的班级名称

#隐式内连接mysql> select student.name, class.name from class,student    ->       where student.class_id = class.id;+------+---------+| name | name    |+------+---------+| 张三 | 计科1班 || 李四 | 计科1班 || 王五 | 计科2班 || 赵六 | 计科2班 |+------+---------+4 rows in set (0.01 sec)# 也可以对表名起一个别名mysql> select s.name, c.name from class c,student s    ->   where s.class_id = c.id;+------+---------+| name | name    |+------+---------+| 张三 | 计科1班 || 李四 | 计科1班 || 王五 | 计科2班 || 赵六 | 计科2班 |+------+---------+4 rows in set (0.00 sec)#显式内连接mysql>  select student.name, class.name from student inner join class on student.class_id = class.id;+------+---------+| name | name    |+------+---------+| 张三 | 计科1班 || 李四 | 计科1班 || 王五 | 计科2班 || 赵六 | 计科2班 |+------+---------+4 rows in set (0.03 sec)

3.2 外连接

• 左外连接

相当于查询表1(左表)所有的数据包含表1和表2交集部分的数据

select 字段列表 from 表1 left join 表2 on 条件 ... ;

• 右外连接

相当于查询表2(右表)的所有数据 , 包含表1 和表2 交集部分的数据

select  字段列表 from 表1 right join 表2 on 条件 ....;

示例:

#表结构如下所示mysql> select * from class;+----+---------+| id | name    |+----+---------+|  1 | 计科1班 ||  2 | 计科2班 ||  3 | NULL    |+----+---------+3 rows in set (0.00 sec)mysql> select * from student;+----+------+----------+| id | name | class_id |+----+------+----------+|  1 | 张三 |        1 ||  2 | 李四 |        1 ||  3 | 王五 |        2 ||  4 | 赵六 |        2 ||  5 | 宋六 |     NULL |+----+------+----------+5 rows in set (0.01 sec)mysql> select * from student left join class on student.class_id = class.id;+----+------+----------+------+---------+| id | name | class_id | id   | name    |+----+------+----------+------+---------+|  1 | 张三 |        1 |    1 | 计科1班 ||  2 | 李四 |        1 |    1 | 计科1班 ||  3 | 王五 |        2 |    2 | 计科2班 ||  4 | 赵六 |        2 |    2 | 计科2班 ||  5 | 宋六 |     NULL | NULL | NULL    |+----+------+----------+------+---------+5 rows in set (0.00 sec)mysql> select * from class left join student on class.id = student.class_id;+----+---------+------+------+----------+| id | name    | id   | name | class_id |+----+---------+------+------+----------+|  1 | 计科1班 |    1 | 张三 |        1 ||  1 | 计科1班 |    2 | 李四 |        1 ||  2 | 计科2班 |    3 | 王五 |        2 ||  2 | 计科2班 |    4 | 赵六 |        2 ||  3 | NULL    | NULL | NULL |     NULL |+----+---------+------+------+----------+5 rows in set (0.00 sec)#使用左外连接或者右外连接的前提一般是 , 需求的列中部分元素为空值,直接使用内连接无法显示出来,所以采用左外连接或者 右外连接

3.3 自连接

自连接指的是在同一张表内连接自身进行查询 ,自连接查询可以是内连接查询也可以是外连接查询 . (自连接查询中表必需起别名)

select 字段列表 from 表A 别名A join 表A 别名B on 条件

案例 : 员工表中包含以下字段 : 员工 id , 员工姓名 name,员工年龄 age , 员工领导managerid

# 查询员工以及所属领导的名称 (内)select a.name,b.name  from emp a,emp b  where a.managerid = b.id;(查询的结果不包含领导为null的字段)# 查询所有员工及其领导的名字 , 如果员工没有领导, 也需要查询出来select a.name '员工',b.name '领导' from emp a left join emp b on a.managerid = b.id;#使用左外连接会将所有员工的姓名全部显示出来, 包括没有领导的

3.4 合并查询

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION和UNION ALL时，前后查询的结果集中，字段需要一致。

• union all

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行(合并后的结果中包含重复行)

select * from course where id<3union allselect * from course where name= '数据结构';# 当查询字段不一致时会报错

• union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行

select * from course where id<3union select * from course where name= '数据结构';

同理 or

select * from course where id < 3 or name = '数据结构';

union查询时，在索引列查询速度快,or,in采用的是全表扫描机制更适用于非索引列查找。

3.4 子查询

• 概念 : SQL语句中嵌套select语句 , 称为嵌套查询 , 又称子查询 .

select * from table1 where column1 = (select column1 fropm table2);

子查询外部的语句可以是 : INSERT / UPDATE / SELECT 中的任何一个

• 标量子查询

子查询返回的结果为单个值(数字,字符串,日期)等, 常用操作符: = > >= < <=

#查询张三同学的同班同学select * from student where class_id =(select class_id from student where name = '张三');

• 列子查询

子查询返回的结果是 一列(可以是多行) ,这种子查询称为列子查询 .

常用的操作符为 : IN , NOT IN , ANY ,ALL

#  查询选修课程名称为语文或者英语的分数信息 (in)select * from score where course_id in (select id from course where name='语文' or name='英文');#查询语文课程分数大于其他任意一名学生的信息(any)select * from score where grade>any(select grade from score where course_id = (select class_id from student where name = '语文'));

• exists关键字

exists( )内子查询语句返回结果不为空，说明where条件成立就会执行主sql语句。如果为空就表示where条件不成立，sql语句就不会执行。not exists 和 exists相反，子查询语句结果为空，则表示where条件成立，执行sql语句。否则不执行。

• 在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

-- 获取“计算机1班”的平均分，将其看作临时表select avg( sco.score ) score from score scojoin student stu ON sco.student_id = stu.idjoin classes cls ON stu.classes_id = cls.idwhere cls.NAME = '计算机1班';