联合查询——数据库(全面一篇通 文章摘要本文系统梳理了 MySQL 数据库查询的三大核心技术聚合查询、联合查询和合并查询**。掌握这些技术是高效操作数据库、解决复杂业务需求的关键。**|聚合查询| 对数据进行统计和汇总 | 数据报表、统计分析、分组计算 | 使用聚合函数COUNT、SUM、AVG等和 GROUP BY 分组配合 HAVING 过滤分组结果 ||联合查询| 关联多个表获取完整信息 | 多表关联查询、数据关系分析 | 通过连接条件内连接、外连接、自连接消除笛卡尔积无效数据支持子查询嵌套 ||合并查询| 合并多个查询结果集 | 数据合并、结果去重或保留重复 | 使用 UNION去重或 UNION ALL保留重复合并结构相同的 SELECT 结果 | 目录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 Union3.9.2 Union all正文2.聚合查询2.1聚合函数聚合函数操作只针对某一列进行操作的与表操作中的表达式查询不同是对一行记录中的列于列之间进行运算。语法SELECT聚合函数(列名或表达式)FROM表名[WHERE条件][GROUPBY分组列][HAVING分组后过滤];COUNT统计行数。COUNT(*) 统计所有行含 NULLCOUNT(列名) 仅统计非 NULL 值。语法-SUM求和适用于数值型列自动过滤 NULL 值。AVG计算平均值同样忽略 NULL 值。英语English有一科是空的count7avgsum/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_idclass.class_id相同时数据是有效的而有些数据是无效的那么我们如何把无效数据过滤掉解决办法通过连接条件过滤无效数据两个表之间是有主外键关系的只需要判断两个表中主外键字段是否相等即可。反例class_id在两张表中都有MySQL分不清当前语句中的class_id应该取那张表解决办法通过表名.列名的方式来解决,通过连接关系过滤出正确的结果。正例select*fromstudent,classwherestudent.class_idclass.class_id;3.3通过指定列查询来精减结果集查询列表中通过表名.列名的方式指定要查询的字段selectid,student.name,class.namefromstudent,classwherestudent.class_idclass.class_id;为了操作更简便可以把表名简化如student改为s,class改为cselects.id,s.name,c.namefromstudent s,class cwheres.class_idc.class_id;3.4表联合查询的步骤1.首先确定哪几张表要参与查询2.取笛卡尔积3.根据表与表之间的主外键关系确定表与表之间的连接关系可以使用select *from 表名表名也可以通过查看表结构确定连接关系4.确定查询的过滤条件5.精减查询字段得到想要的结果3.4.1一个完整的联合查询过程查询学生姓名为孙悟空的详细信息包含学生个人信息和班级信息#student学生表INSERTINTOstudent(id,name,age,gender,class_id)VALUES(1,张三,18,女,1);INSERTINTOstudent(id,name,age,gender,class_id)VALUES(2,里斯,17,男,2);INSERTINTOstudent(id,name,age,gender,class_id)VALUES(3,王五,17,男,1);INSERTINTOstudent(id,name,age,gender,class_id)VALUES(4,老六,19,男,2);INSERTINTOstudent(id,name,age,gender,class_id)VALUES(5,孙悟空,20,男,3);INSERTINTOstudent(id,name,age,gender,class_id)VALUES(6,海绵宝宝,17,男,3);#class班级表INSERTINTOclass(class_id,name)VALUES(1,java);INSERTINTOclass(class_id,name)VALUES(2,C);INSERTINTOclass(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操作返回的结果可以使用集合操作符unionunion all。3.9.1Union该操作适用于取得两个及以上结果集的并集。当使用该操作时会自动去掉结果集中的重复行。示例返回score中score_id16的同学和stud net_id1的同学还有score.score90的同学。多个单个select操作返回的结果合并多个select操作返回的结果3.9.2 Union all该操作适用于取得两个及以上结果集的并集。当使用该操作时不会自动去掉结果集中的重复行。