MySQL: 表内容的查询---聚合查询和联合查询(进阶)

目录

1. 把查找结果作为另一张表的新增数据

2.聚合查询

2.1 聚合函数

2.2 group by 子句

2.3 having

3. 联合查询

3.1 内连接

3.2 外连接

3.3 自连接

3.4 子查询

3.5 合并查询(union)

---

1. 把查找结果作为另一张表的新增数据

2.聚合查询

把行之间的数据进行聚合,和列无关,也就是对每一行数据的某一列进行聚合.

2.1 聚合函数

MySQL中提供了一组现成的聚合函数:

① count(字符串和数字均可)

② sum ③avg ④max ⑤min(只能针对数字进行这些操作,不能针对字符串)

2.2 group by 子句

group  by : 针对数据进行分组,把值相同的记录归为一组.

2.3 having

用 group by 子句进行分组之后,不能再使用 where指定条件了,而是使用having指定条件.

也就是,聚合前使用 where 指定条件;聚合后,使用having指定条件.

3. 联合查询

在实际开发中,数据往往来自不同的表,所以需要多表联合查询.

其核心计算过程是:笛卡尔积,本质上就是排列组合,遍历第一张表,取出一条记录,和第二张表里的每一条记录分别进行组合,得到一张更大的表.

通过如下代码,就可以求出笛卡尔积(具体想要显示那一列,根据需要写,比如说我也可以写成  select aaa.a1,bbb.b1 from aaa,bbb;  )

在这里,我已经事先准备好了几张表: student, classes, score, course.如下:

下面分别对 内连接,外连接,自连接,子查询,以及合并查询 举栗来康康具体的语法格式~~

3.1 内连接

语法:

--如下两种方式,感觉第一种更简单一点吼吼~~
select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;
select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

a)  查询xuxian同学的成绩.

学生姓名是在 student 表中,学生成绩是在 score 表中,就需要把这两张表在一起进行联合查询.

思路: 首先得到笛卡尔积,然后一步一步添加条件去筛选.

b) 查询所有同学的总成绩,及同学的个人信息.

思路: 每个同学都有好几门课程,需要按照同学id进行 group by 来求总成绩.

c) 查询所有同学的成绩,及同学的个人信息.

希望能够查出每个同学每门课程的分数,希望能够查出该分数对应的课程名字,也就是要用到 student,course,score 这三张表,查出姓名 课程名字 分数.

3.2 外连接

外连接分为左外连接和右外连接.

上面内连接的语法为: ① from student,score ② from student inner join score on. 取两张表中都存在的数据.

左外连接(左连接)的语法:  from student left join score on .让左边表中能体现出来的内容全部都体现出来,即便右边的表中无此内容.

右外连接(右连接)的语法: from student right join score on .让右侧的表中能体现的记录全部都体现出来

比如现在有如下两张表:

左连接:           

右连接:

3.3 自连接

也就是自己和自己做笛卡尔积,有的时候在筛选数据的时候涉及到行和行之间的比较.

a) 显示所有计算机原理成绩比java成绩高的同学.

3.4 子查询

子查询分为两种:

• 单行子查询,子查询只返回一条结果
• 多行子查询,子查询返回多行结果.

a) 查询 buxiangbiye 的同班同学的姓名(单行子查询)

b) 查询 chinese 或 english 课程的成绩信息(多行子查询).

方法① 使用in(先执行内层查询,再执行外层查询)

思路:  如上面表可以看出,课程表中只有课程id,没有成绩,所以先在课程表中找到 语文和英语两门课程的id,然后再通过这些id去成绩表中查找.

方法② 使用exists 关键字(先执行外层查询,再执行内层查询)

总结:

方法①,使用 in 关键字,先执行子查询的sql,得到的结果放到内存中,再进行外层查询,就直接把course_id和刚才内存中保存的子查询结果进行比较即可,执行过程只有两次查询,结果执行过程比较清晰,而且效率也很高;

方法②,使用 exists 关键字,先执行外层查询,依次取每一条外层查询的记录,带入到里层查询中. 如果里层查询结果为空,就保留外层的查询结果;如果里层的查询结果不为空,就丢弃外层查询的结果.

使用 in 来进行查询,效率高,但是更依赖内存,适用于子查询结果集合比较小的情况.

使用 exists 来查询,效率比较低,但是不依赖于内存,适用于外层查询结果集合小,子查询结果集合大的情况.

3.5 合并查询(union)

查询id小于3,或者名字为 english的课程.