MySQL数据库聚合查询和联合查询操作(2)-木庄网络博客

以下示例都是通过下面 SQL 语句建的表来进行操作学习的，如果你想在后面的内容进行操作，可以直接复制使用

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

3.2 内连接

语法：

-- 方法一：
select 展示的列名 from 表1 [表1别名],表2 [表2别名] where 连接条件;

-- 方式二：使用 [inner] join on
select 展示的列名 from 表1 [表1别名] [inner] join 表2 [表2别名] on 连接条件;

补充：

使用多表查询时，由于有多个表，所以使用其中的列，方式为：表名.列名
可以使用表名别名的方式，将表名起一个别名使用
使用 [inner] join on 方式，如果省略 inner，则默认内连接

示例1：查询许仙同学每门课的成绩

3.3 外连接

外连接：分为左外连接和右外连接。如果使用联合查询，左侧的表完全显示就是用了左外连接；右侧的表完全显示就是用了右外连接

外连接其实和内连接差不多，都是使用了笛卡尔积。内连接是针对的两个表中的每一条数据都是一一对应的，那怎么就不是一一对应了呢？例如下面两个表 A、B

A 表：

id	name
1	张三
2	李四
3	王五

B 表：

student_id	score
1	90
2	80
4	70

我们发现经过笛卡尔积后建立的新表时，A 表的 id 为3的记录和 B 表中没有对应的数据，B 表中 student_id 为4的记录和 A 表中也没有对应的数据，因此这两个表就不能使用内连接的方式去查询，要使用外连接

如果使用左连接的方式，新表 C 为：

id	name	student_id	score
1	张三	1	90
2	李四	2	80
3	王五	null	null

如果使用右连接的方式，新表 D 为：

id	name	student_id	score
1	张三	1	90
2	李四	2	80
null	null	4	70

补充：

当两个表中的数据可以一一对应时，使用外连接和内连接是等价的
除了内连接、左外连接、右外连接，其实还有全外连接，但是 MySQL 中不支持全外连接操作

语法：

-- 左连接，表1完全显示
select 展示的列名 from 表1 [表1别名] [left] join 表2 [表2别名] on 连接条件;

-- 右连接，表2完全显示
select 展示的列名 from 表1 [表1别名] [right] join 表2 [表2别名] on 连接条件;

3.4 自连接

自连接：是指在同一张表中连接自身进行查询，使用自连接其实可以将”行转换成列“来进行操作

为什么自连接可以将行转换成列来进行操作呢？假设有一张表 A

student_id	course_id	score
1	1	70
1	2	90
1	3	80

如果我想找到原表中 student_id 为1，且其课程2成绩高于课程3的同学的信息时，就是要对行与行之间进行比较，但是一张表是不能进行该操作的

通过对自己进行笛卡尔积之后，得到新的表 B

student_id	course_id	score	student_id	course_id	score
1	1	70	1	1	70
1	2	90	1	2	90
1	3	80	1	3	80

此时我们发现，如果将原表进行笛卡尔积后，有了两张一样的表，就可以实施行与行之间操作

示例： 查询 Java 成绩高于计算机原理成绩的同学

3.5 子查询

子查询：是指嵌入在其它 SQL 语句中的 select 语句，也叫嵌套查询

分类：

单行子查询：返回一行记录的子查询
多行子查询：返回多行记录的子查询（使用 in 或者 exists）

补充：

用 in 进行多行查询过程：使用子查询时，先执行子查询，将查询的结果存放在内存里，再执行外层查询，根据内存里的结果进行筛选
用 exists 进行多行查询过程：先执行外层循环，这样就会得到很多记录，在针对每行记录将它带入到子查询中，符合条件的就保留（exists 就是检测子查询结果是否为空集合）

综上所述：

基于 in 的写法，速度快，适合子查询结果集合比较小的情况（较大内存装不下）
基于 exists 的写法，速度慢，适合子查询结果集合比较大，且外层查询结果数量比较少的情况

示例1：查询不想毕业同学的同班同学（首先要知道不想毕业同学的班级，然后通过班级筛选学生）