联合查询——数据库(全面一篇通）-酒店常州论坛

联合查询——数据库(全面一篇通）

📝 文章摘要

📋 目录

1. 引入
2. 聚合查询
- 2.1 聚合函数
- 2.2 GROUP BY子句
3. 联合查询
- 3.1 使用联合查询(表连接查询）的原因
- 3.2 多表联合查询时MySQL内部是如何进行计算的
- 3.3 通过指定列查询，来精减结果集
- 3.4 表联合查询的步骤
  - 3.4.1 一个完整的联合查询过程
- 3.5 内连接
- 3.6 外连接
- 3.7 自连接
- 3.8 子查询(嵌套查询）
- 3.9 合并查询
  - 3.9.1 Union
  - 3.9.2 Union all

正文

2.聚合查询

2.1聚合函数

聚合函数操作只针对某一列进行操作的；与表操作中的表达式查询不同，是对一行记录中的列于列之间进行运算。
语法：

SELECT聚合函数(列名或表达式)FROM表名[WHERE条件][GROUPBY分组列][HAVING分组后过滤];

COUNT：统计行数。COUNT(*) 统计所有行（含 NULL），COUNT(列名) 仅统计非 NULL 值。
语法：
-SUM：求和，适用于数值型列，自动过滤 NULL 值。
AVG：计算平均值，同样忽略 NULL 值。
英语English有一科是空的，count=7，avg=sum/7，而不是÷8.
MAX / MIN：分别返回最大值与最小值，可用于数值、日期或字符串类型，不是数字没有意义。

2.2GROUP BY子句

SELECT中使用GROUP BY子句可以对指定列进行分组查询。需要满足：使用GROUP BY进行分组查询时，SELECT指定的字段必须是“分组依据字段（要进行分组的列）”，其他字段若想出现在SELECT中则必须包含在聚合函数中。

此时对role进行分组有：
语法：

SELECT分组的列名,聚合函数(列名)FROM表名[WHERE条件]GROUPBY列名[HAVING聚合条件][ORDERBY列名];

注意：GROUP BY子句进行分组之后，需要对分组结果再进行条件过滤时，不能使用where语句，而需要使用having(即
where是对表中每一行的真实数据进行过滤
having是对group by之后计算出来的结果，进行过滤）

where用在from表名之后，也就是分组之前
having跟在group by之后
如果需求要对真实数据进行过滤，并且，需要对分组结果进行过滤，
那么在合适的位置写where和having即可

3.联合查询

3.1使用联合查询(表连接查询）的原因

在表的设计中，由于需要满足三大范式的要求，数据要被拆分到多个表中（为了消除表中字段的依赖关系，比如部分函数依赖，传递依赖），那么要查询一条数据的完整信息，就要从多个表中获取数据，如下图所示：要获取学生的基本信息和班级信息就要从学生表和班级表中获取，此时就要使用联合查询，这里的联合指的是多个表的组合。

3.2多表联合查询时MySQL内部是如何进行计算的

对多张表进行笛卡尔积的过程：

1.先从第一张表中取一条记录，再和第二张表中的第一条记录进行组合，生成一条新的记录。
2.再从第一张表中取一条记录，然后再与第二张表中的第二条记录进行组合，生成一条新的记录。
……
最后得到的结果就是一个全排列结果集。

createtableclass(class_idbigintprimarykeyauto_increment,namevarchar(30));insertintoclass(name)values('java'),('C++'),('Python');

createtablestudent(idbigintprimarykeyauto_increment,namevarchar(30)notnull,class_idbigint);insertintostudent(name,class_id)values('张三',1),('里斯',2),('王五',1),('老六',2),('孙悟空',3),('海绵宝宝',3);

联合查询的语法：

select*from表名,表名;

观察两张表取笛卡尔积之后，当student.class_id=class.class_id相同时数据是有效的，而有些数据是无效的，那么我们如何把无效数据过滤掉？
解决办法：通过连接条件过滤无效数据

两个表之间是有主外键关系的，只需要判断两个表中主外键字段是否相等即可。
反例：

class_id在两张表中都有，MySQL分不清当前语句中的class_id应该取那张表
解决办法：通过表名.列名的方式来解决,通过连接关系过滤出正确的结果。
正例：

select*fromstudent,classwherestudent.class_id=class.class_id;

3.3通过指定列查询，来精减结果集

查询列表中通过表名.列名的方式指定要查询的字段

selectid,student.name,class.namefromstudent,classwherestudent.class_id=class.class_id;

为了操作更简便，可以把表名简化，如student改为s,class改为c

selects.id,s.name,c.namefromstudent s,class cwheres.class_id=c.class_id;

3.4表联合查询的步骤

1.首先确定哪几张表要参与查询
2.取笛卡尔积
3.根据表与表之间的主外键关系，确定表与表之间的连接关系（可以使用select *from 表名+表名，也可以通过查看表结构确定连接关系）
4.确定查询的过滤条件
5.精减查询字段，得到想要的结果

3.4.1一个完整的联合查询过程

查询学生姓名为孙悟空的详细信息，包含学生个人信息和班级信息

#student学生表INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(1,'张三',18,'女',1);INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(2,'里斯',17,'男',2);INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(3,'王五',17,'男',1);INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(4,'老六',19,'男',2);INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(5,'孙悟空',20,'男',3);INSERTINTO`student`(`id`,`name`,`age`,`gender`,`class_id`)VALUES(6,'海绵宝宝',17,'男',3);

#class班级表INSERTINTO`class`(`class_id`,`name`)VALUES(1,'java');INSERTINTO`class`(`class_id`,`name`)VALUES(2,'C++');INSERTINTO`class`(`class_id`,`name`)VALUES(3,'Python');

- 1.确定参与查询的表，学生表和班级表
2.取笛卡尔积
3.确定连接关系，student表中的class_id与class表中的class_id的值相等
4.确定查询的过滤条件（姓名是孙悟空）

- 5.精减查询字段，得到想要的结果

3.5内连接

-语法：

# 法一select*from表名，表名where连接条件;

# 法二select字段from表1别名1,表2别名2where连接条件and其他条件;

# 法三select字段from表1别名1[inner]join表2别名2on连接条件where其他条件;

3.6外连接

外连接分为左外连接，右外连接和全外连接三种类型，MySQL不支持全外连接。
注意：全外连接结合了左外连接和右外连接的特点，返回左右表中的所有记录。如果某一边表中没有匹配的记录，则结果集中对应字段会显示为null。

观察上图可知，没有同学选择“C#”这门课。那么我们如何把班级表和学生表的完整数据展示出来呢？
下面介绍两种办法：右外连接和左外连接。
右外连接：与左外连接相反，返回右表的所有记录和左表中匹配的记录。如果左表中没有匹配的记录，则结果集中对应字段会显示为null。
- 语法：
```
select字段名from表名1rightjoin表名2on连接条件;
```
右外连接，是以join右边的表class为基准，这个表中的数据全部显示出来，左边的表student没有与之匹配的记录全部用null去填充。
- 左外连接：返回左表的所有记录和右表中匹配的记录。如果右表中没有匹配的记录，则结果集中对应字段会显示null。
- 语法：
```
select字段名from表名1leftjoin表名2on连接条件；
```

3.7自连接

自连接是自己与自己取笛卡尔积，可以把行转化成列，在查询的时候可以使用where条件对结果进行过滤，或者说实现行与行之间的比较。在做表连接时为表起不同的别名。

CREATETABLEcourse(course_idINTPRIMARYKEY,nameVARCHAR(50)NOTNULL);INSERTINTOcourse(course_id,name)VALUES(1,'Java'),(2,'中国传统文化'),(3,'计算机原理'),(4,'语文'),(5,'高阶数学'),(6,'英文');

CREATETABLEscore(score_idINTAUTO_INCREMENTPRIMARYKEY,student_idINTNOTNULL,course_idINTNOTNULL,scoreDECIMAL(5,2)NOTNULL);INSERTINTOscore(student_id,course_id,score)VALUES(1,1,70.50),(1,3,98.50),(1,5,33.00),(1,6,98.00),(2,1,60.00),(2,5,59.50),(3,1,33.00),(3,3,68.00),(3,5,99.00),(4,1,67.00),(4,3,28.00),(4,5,56.00),(4,6,72.00),(5,1,81.00),(5,5,37.00),(6,2,56.00),(6,4,43.00),(6,6,79.00),(7,2,80.00),(7,6,92.00);

需求：显示所有“计算机原理”成绩比“Java”高的成绩信息？

1.确定所涉及的表
- 课程表，成绩表
2.取笛卡尔积
3.确定连接条件为stud net_id相同，同一个学生才能比较计算机原理和Java成绩的高低。

4.确定过滤条件
5.最后的过滤条件，得到想要的结果——计算机原理成绩比Java高的信息

3.8子查询(嵌套查询）

子查询是把一条SQL语句的查询结果，当作另一条SQL语句的查询条件，可以嵌套很多层。

语法：

select*fromtable1wherecol_name1 {=|IN}(selectcol_name1fromtable2wherecol_name2 {=|IN}[(select...)]...)

可以看出，子查询是由很多条SQL语句组成的，也可以把子查询拆分成一条一条单独的语句去执行，最后再把结果和条件拼接在一起。
由于嵌套的层级没有固定限制，所以多层嵌套查询效率是不可控的，工作中谨慎使用。

3.8.1单行子查询

需求：找出 student_id = 1 的所有成绩中，分数高于他/她平均成绩的记录。

3.8.2多行子查询
需求：查询“语文”或“英文”课程的成绩表
- 1.确定所涉及的表
  课程表，成绩表
- 2.在课程表中获取’语文”和“英文”课程的编号
- 3.根据获取到的课程id，在成绩表中查询对应的课程分数
- 4.把以上分布查询的SQL语句拼接起来，变成子查询
3.8.3 NOT EXISTS关键字
语法：

select*from表名whereexists(select*from表名);

exists后面括号中的查询语句，如果有结果返回，则执行外层的查询，如果返回的是空结果，则不执行外层的查询。

3.8.4在from子句中使用子查询
在from子句中使用子查询：子查询语句出现在from子句中。这里要用到数据查询的技巧，把一个子查询当作一个临时表使用。
在这个临时表中，是由学生表和课程表组合而成的。
需求：找出所有分数高于“score”平均分的成绩记录。
- 确定平均分
- 把以上查询作为临时表，与真实表进行比较

3.9合并查询

在实际应用中，为了合并多个select操作返回的结果，可以使用集合操作符union，union all。

3.9.1Union
该操作适用于取得两个及以上结果集的并集。当使用该操作时，会自动去掉结果集中的重复行。
- 示例：返回score中score_id=16的同学和stud net_id=1的同学还有score.score>90的同学。
多个单个select操作返回的结果
合并多个select操作返回的结果
3.9.2 Union all
该操作适用于取得两个及以上结果集的并集。当使用该操作时，不会自动去掉结果集中的重复行。

企业官网建设流程全解析