MySQL聚合查询实现多表查询，联合查询，合并查询

Author:老九
计算机专业
可控之事 沉重冷静 不可控之事 乐观面对
85180586@qq.com
😄 😆 😵 😭 😰 😅 😢 😤 😍 ☺️ 😎 😩

👍 👎 💯 👏 🔔 🎁 ❓ 💣 ❤️ ☕️ 🌀 🙇 💋 🙏 💦 💩 ❗️ 💢
————————————————
版权声明：本文为CSDN博主「浦上青天」的原创文章

知识点

聚合查询

COUNT

count 就是计算行的值，不算 null，这里查询的是有多少行：

select count(*) from exam_result;

mysql> select * from emp;
+----+---------+-----------+---------+
| id | name    | role      | salary  |
+----+---------+-----------+---------+
|  1 | 丫丫1   | 老师      |  200.00 |
|  2 | 丫丫2   | 老师      |  300.00 |
|  3 | 丫丫3   | 老师      |  400.00 |
|  4 | 丫丫4   | 老师      |  500.00 |
|  5 | 丫丫5   | 老师      |  600.00 |
|  6 | 丫丫6   | 班主任    |  700.00 |
|  7 | 丫丫7   | 班主任    |  800.00 |
|  8 | 丫丫8   | 教导员    |  900.00 |
|  9 | 丫丫9   | 教导员    | 1000.00 |
+----+---------+-----------+---------+
9 rows in set (0.01 sec)
//查询id有多少行
mysql> select count(id) from emp;
+-----------+
| count(id) |
+-----------+
|         9 |
+-----------+
1 row in set (0.01 sec)

SUM

• 把这一列的若干行相加 也不算 null 只能针对数字进行运算，不能对字符串进行运算。

mysql> select sum(salary) from emp;
+-------------+
| sum(salary) |
+-------------+
|     5400.00 |
+-------------+
1 row in set (0.00 sec)

聚合函数是可以搭配 where 来使用的。就是先执行筛选，后执行求和：

select sum(english) from exam_result where english > 70;

这里求的就是英语 > 70 的和，运行结果如下：

AVG

• 返回查询到的数据的平均值

mysql> select avg(salary) from emp;
+-------------+
| avg(salary) |
+-------------+
|  600.000000 |
+-------------+
1 row in set (0.00 sec)

MAX

• 返回查询到的数据的最大值

mysql> select max(salary) from emp;
+-------------+
| max(salary) |
+-------------+
|     1000.00 |
+-------------+
1 row in set (0.00 sec)

MIN

• 返回查询到的数据的最小值

mysql> select min(salary) from emp;
+-------------+
| min(salary) |
+-------------+
|      200.00 |
+-------------+
1 row in set (0.00 sec)

GROUP BY 语句

group by 就是先分组，然后在针对每个组来使用聚合函数。先创建一个表，然后插入数据：

create table emp(
                    id int primary key auto_increment,
                    name varchar(20) not null,
                    role varchar(20) not null,
                    salary numeric(11,2)
);
insert into emp(name, role, salary) values
                                        ('A','服务员', 1000.20),
                                        ('B','游戏陪玩', 2000.99),
                                        ('C','游戏角色', 999.11),
                                        ('D','游戏角色', 333.5),
                                        ('E','游戏角色', 700.33),
                                        ('F','董事长', 12000.66);

通过 group by 分组，这里来查询不同角色对应的 sum，min，avg 的值。代码如下：

select role,max(salary),min(salary),avg(salary) from emp group by role;

HAVING 语句

where：对分组前的数据进行操作。例如：求平均工资的时候，去掉 E 求的结果就不包含 E 的数据了，代码如下：

select role, avg(salary) from emp where name != 'E' group by role;

having：对分组之后的数据进行操作。例如：求平均工资，然后再筛选。代码如下：

select role, avg(salary) from emp group by role having avg(salary < 10000);

这里的意思就是先求到平均工资，然后再筛选出平均工资小于 10000 的。运行结果如下：

联合查询

笛卡尔积

• 实际开发中，往往数据来自不同的表，所以需要多表联合查询。笛卡尔积是多表查询的核心操作。
• 笛卡尔积计算过程：先拿第一张表的第一条记录，和第二张表的每个记录，分别组合，得到一组新的记录。然后再拿第一张表的第二条记录，和第二张表的每条记录，分别组合，又得到新的记录，最终得到的记录就是笛卡尔积。针对两张表计算笛卡尔积，笛卡尔积的列数，就是 A 的列数 + B 的列数。笛卡尔积的行数，就是 A 的行数 * B 的行数。
• 笛卡尔积用法：select * from 后面跟上多个表名，表名之间用逗号隔开。
• 两张表中都有 班级Id 这一列，班级Id 的值。班级Id 就是连接条件。如果笛卡尔积的两个列名相同，在写条件的时候，就可以通过 表名.列名 的方式来访问。如果列不会混淆，就可以省略表名。代码如下：

select student.name, class.name from student, class where student.classId = class.classId;

数据构造

drop table if exists classes;
drop table if exists student;
drop table if exists course;
drop table if exists score;

create table classes (id int primary key auto_increment, name varchar(20), `desc` varchar(100));

create table student (id int primary key auto_increment, sn varchar(20),  name varchar(20), qq_mail varchar(20) ,
        classes_id int);

create table course(id int primary key auto_increment, name varchar(20));

create table score(score decimal(3, 1), student_id int, course_id int);

insert into classes(name, `desc`) values 
('计算机系2019级1班', '学习了计算机原理、C和Java语言、数据结构和算法'),
('中文系2019级3班','学习了中国传统文学'),
('自动化2019级5班','学习了机械自动化');

insert into student(sn, name, qq_mail, classes_id) values
('09982','黑旋风李逵','xuanfeng@qq.com',1),
('00835','菩提老祖',null,1),
('00391','白素贞',null,1),
('00031','许仙','xuxian@qq.com',1),
('00054','不想毕业',null,1),
('51234','好好说话','say@qq.com',2),
('83223','tellme',null,2),
('09527','老外学中文','foreigner@qq.com',2);

insert into course(name) values
('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');

insert into score(score, student_id, course_id) values
-- 黑旋风李逵
(70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-- 菩提老祖
(60, 2, 1),(59.5, 2, 5),
-- 白素贞
(33, 3, 1),(68, 3, 3),(99, 3, 5),
-- 许仙
(67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-- 不想毕业
(81, 5, 1),(37, 5, 5),
-- 好好说话
(56, 6, 2),(43, 6, 4),(79, 6, 6),
-- tellme
(80, 7, 2),(92, 7, 6);

内连接

• 使用内连接的思路
  1.观察要查询的数据涉及到几张表
  2.多张表连接时使用的是笛卡尔积运算,所以会产生许多无用的数据,此时我们就应该加上表的连接条件去除掉无用的数据
  3.再用选择条件和字段去控制得到的数据表为我们所需要的

-- 查询许仙同学的成绩
mysql> select student.name,score.score from student,score where student.id = score.student_id and student.name = '许仙';
+--------+-------+
| name   | score |
+--------+-------+
| 许仙   |  67.0 |
| 许仙   |  23.0 |
| 许仙   |  56.0 |
| 许仙   |  72.0 |
+--------+-------+
4 rows in set (0.00 sec)

-- 和上面是等价的（from 表1 join 表2 on 条件 ）
mysql> select student.name,score.score from student join score on student.id = score.student_id and student.name = '许仙';
+--------+-------+
| name   | score |
+--------+-------+
| 许仙   |  67.0 |
| 许仙   |  23.0 |
| 许仙   |  56.0 |
| 许仙   |  72.0 |
+--------+-------+
4 rows in set (0.00 sec)

-- 查询所有同学的总成绩
mysql> select student.name , sum(score.score) as total from student,score where student.id = score.student_id group by student.id;
+-----------------+-------+
| name            | total |
+-----------------+-------+
| 黑旋风李逵      | 300.0 |
| 菩提老祖        | 119.5 |
| 白素贞          | 200.0 |
| 许仙            | 218.0 |
| 不想毕业        | 118.0 |
| 好好说话        | 178.0 |
| tellme          | 172.0 |
+-----------------+-------+
7 rows in set (0.00 sec)

-- 查询所有同学的总成绩，及同学的个人信息
mysql> select student.name,course.name,score.score from student,score,course where student.id = score.student_id and course.id = score.course_id;
+-----------------+--------------------+-------+
| name            | name               | score |
+-----------------+--------------------+-------+
| 黑旋风李逵      | Java               |  70.5 |
| 黑旋风李逵      | 计算机原理         |  98.5 |
| 黑旋风李逵      | 高阶数学           |  33.0 |
| 黑旋风李逵      | 英文               |  98.0 |
| 菩提老祖        | Java               |  60.0 |
| 菩提老祖        | 高阶数学           |  59.5 |
| 白素贞          | Java               |  33.0 |
| 白素贞          | 计算机原理         |  68.0 |
| 白素贞          | 高阶数学           |  99.0 |
| 许仙            | Java               |  67.0 |
| 许仙            | 计算机原理         |  23.0 |
| 许仙            | 高阶数学           |  56.0 |
| 许仙            | 英文               |  72.0 |
| 不想毕业        | Java               |  81.0 |
| 不想毕业        | 高阶数学           |  37.0 |
| 好好说话        | 中国传统文化       |  56.0 |
| 好好说话        | 语文               |  43.0 |
| 好好说话        | 英文               |  79.0 |
| tellme          | 中国传统文化       |  80.0 |
| tellme          | 英文               |  92.0 |
+-----------------+--------------------+-------+
20 rows in set (0.00 sec)

--和上面等价 from 表1 join 表2 on 条件 join 表3 on 条件
mysql> select student.name,course.name,score.score from student join score on student.id = score.student_id join course on score.course_id = course.id;
+-----------------+--------------------+-------+
| name            | name               | score |
+-----------------+--------------------+-------+
| 黑旋风李逵      | Java               |  70.5 |
| 黑旋风李逵      | 计算机原理         |  98.5 |
| 黑旋风李逵      | 高阶数学           |  33.0 |
| 黑旋风李逵      | 英文               |  98.0 |
| 菩提老祖        | Java               |  60.0 |
| 菩提老祖        | 高阶数学           |  59.5 |
| 白素贞          | Java               |  33.0 |
| 白素贞          | 计算机原理         |  68.0 |
| 白素贞          | 高阶数学           |  99.0 |
| 许仙            | Java               |  67.0 |
| 许仙            | 计算机原理         |  23.0 |
| 许仙            | 高阶数学           |  56.0 |
| 许仙            | 英文               |  72.0 |
| 不想毕业        | Java               |  81.0 |
| 不想毕业        | 高阶数学           |  37.0 |
| 好好说话        | 中国传统文化       |  56.0 |
| 好好说话        | 语文               |  43.0 |
| 好好说话        | 英文               |  79.0 |
| tellme          | 中国传统文化       |  80.0 |
| tellme          | 英文               |  92.0 |
+-----------------+--------------------+-------+
20 rows in set (0.00 sec)

外连接

左外连接

• 联合查询，左侧的表完全显示我们就说是左外连接
• select 列 from 表1 left join 表2 on 条件
• 以左侧的表为主，会尽可能的把左侧的表的记录都列出来，大不了后侧的表对应列填成NULL

mysql> create table student(id int, name varchar(20),classId int);
Query OK, 0 rows affected (0.01 sec)

mysql> create table class(id int,name varchar(20));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student values(1,'张三',1);
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values(2,'李四',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(3,'王五',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(4,'赵六',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+------+--------+---------+
| id   | name   | classId |
+------+--------+---------+
|    1 | 张三   |       1 |
|    2 | 李四   |       1 |
|    3 | 王五   |       2 |
|    4 | 赵六   |       3 |
+------+--------+---------+
4 rows in set (0.00 sec)

mysql> insert into class values(1,'java100');
Query OK, 1 row affected (0.00 sec)

mysql> insert into class values(2,'java101');
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+------+---------+
| id   | name    |
+------+---------+
|    1 | java100 |
|    2 | java101 |
+------+---------+
2 rows in set (0.00 sec)

mysql> select * from student,class where student.classId = class.id;
+------+--------+---------+------+---------+
| id   | name   | classId | id   | name    |
+------+--------+---------+------+---------+
|    1 | 张三   |       1 |    1 | java100 |
|    2 | 李四   |       1 |    1 | java100 |
|    3 | 王五   |       2 |    2 | java101 |
+------+--------+---------+------+---------+
3 rows in set (0.00 sec)

mysql> select * from student left join class on student.classId = class.id;
+------+--------+---------+------+---------+
| id   | name   | classId | id   | name    |
+------+--------+---------+------+---------+
|    1 | 张三   |       1 |    1 | java100 |
|    2 | 李四   |       1 |    1 | java100 |
|    3 | 王五   |       2 |    2 | java101 |
|    4 | 赵六   |       3 | NULL | NULL    |
+------+--------+---------+------+---------+
4 rows in set (0.00 sec)

右外连接

• select 列 from 表1 right join 表2 on 条件

mysql> create table student(id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)

mysql> create table score(student_id int,score int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student values(1,'张三');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(2,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(3,'王五');
Query OK, 1 row affected (0.00 sec)

mysql> insert into score values(1,90),(2,80),(4,70);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from student;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 李四   |
|    3 | 王五   |
+------+--------+
3 rows in set (0.00 sec)

mysql> select * from score;
+------------+-------+
| student_id | score |
+------------+-------+
|          1 |    90 |
|          2 |    80 |
|          4 |    70 |
+------------+-------+
3 rows in set (0.00 sec)

mysql> select * from student join score on student.id = score.student_id;
+------+--------+------------+-------+
| id   | name   | student_id | score |
+------+--------+------------+-------+
|    1 | 张三   |          1 |    90 |
|    2 | 李四   |          2 |    80 |
+------+--------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from student left join score on student.id = score.student_id;
+------+--------+------------+-------+
| id   | name   | student_id | score |
+------+--------+------------+-------+
|    1 | 张三   |          1 |    90 |
|    2 | 李四   |          2 |    80 |
|    3 | 王五   |       NULL |  NULL |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

mysql> select * from student right join score on student.id = score.student_id;
+------+--------+------------+-------+
| id   | name   | student_id | score |
+------+--------+------------+-------+
|    1 | 张三   |          1 |    90 |
|    2 | 李四   |          2 |    80 |
| NULL | NULL   |          4 |    70 |
+------+--------+------------+-------+
3 rows in set (0.00 sec)

子查询

• 在一个查询语句里套用查询语句称为子查询

单行子查询

• 子查询返回一条结果

-- 查找“不想毕业”同学的同班同学
mysql> select name from student where classes_id = (select classes_id from student where name = "不想毕业");
+-----------------+
| name            |
+-----------------+
| 黑旋风李逵      |
| 菩提老祖        |
| 白素贞          |
| 许仙            |
| 不想毕业        |
+-----------------+
5 rows in set (0.00 sec)

多行子查询(IN)

返回多行记录的子查询，要把=换成in

//查询“语文”或“英文”课程的成绩信息
mysql> select * from score where course_id in(select id from course where name='语文'or name = '英文');
+-------+------------+-----------+
| score | student_id | course_id |
+-------+------------+-----------+
|  98.0 |          1 |         6 |
|  72.0 |          4 |         6 |
|  43.0 |          6 |         4 |
|  79.0 |          6 |         6 |
|  92.0 |          7 |         6 |
+-------+------------+-----------+
5 rows in set (0.00 sec)

合并查询（union）

• 把多个查询语句的结果合并到一起，合并的前提是两个sql查询的列是对应的

mysql> select * from course where name = '英文' union select * from course where id < 3;
+----+--------------------+
| id | name               |
+----+--------------------+
|  6 | 英文               |
|  1 | Java               |
|  2 | 中国传统文化       |
+----+--------------------+
3 rows in set (0.00 sec)

也可以使用 or 实现，不过使用 or 的时候必须保证是针对同一个表来进行操作的。代码如下：

select * from course where name = '英文' or id < 3;

先赞后看，养成习惯！！！^ _ ^♥♥♥
每天都更新知识点哦！！！
码字不易，大家的支持就是我坚持下去的动力。点赞后不要忘记关注我哦！