对查询结果统计（课件）-《MySQL数据库技术与应用》同步教学（清华）

第6章查询本章学习目标：掌握T-SQL作为查询语句的语法与应用掌握WHERE、ORDERBY、GROUPBY、HAVING子句的使用掌握基本的多表查询掌握内连接、外连接、交叉连接和联合查询的使用掌握多行和单值子查询的使用掌握嵌套子查询的使用

工作场景导入

教务处工作人员小李在工作中经常需要查询数据库中的数据。例如有如下查询需求：(1)查询学生表所有学生的学号、姓名和所在院系。(2)查询所在院系为“计算机科学”的学生学号、姓名、性别。(3)查询年龄大于19岁的学生信息。(4)查询名字包含“孙”这个字的所有学生的信息。(5)查询选修了“1001”号课程的所有学生的相关信息。(6)查询院系人数大于20的院系信息。(7)查询不在信息工程学院上课的学生。(8)查询和“张玲”在一个系上课的学生姓名。(9)查询成绩低于该门课程平均成绩的学生编号、课程编号和成绩。(10)查询选修了“2014056101”学生选修的所有课程的学生的信息。

引导问题：(1) 如何查询存储在数据库表中的记录？(2) 如何对原始记录进行分组统计？(3) 如何对来自多个表的数据进行查询？(4) 如何保留连接不成功的记录？(5) 如何动态设置选择记录的条件？6.4.4对查询结果统计1.使用聚合函数在SELECT语句中可以使用统计函数进行统计，并返回统计结果。聚合函数用于处理单个列中所选的全部值，并生成一个结果值。常用的聚合函数（也称统计函数）包括COUNT()、AVG()、SUM()、MAX()、和MIN()等。【例】统计查询学生总人数，以及参加选课的学生的人数。T-SQL语句如下：--学生总人数SELECTCOUNT(*)FROM学生--参加选课的学生人数SELECTCOUNT(DISTINCT学号)FROM选课常用的聚合函数有：【例】查询选修“1002”课程学生的最高分，最低分和平均分。T-SQL语句如下：SELECTMAX(分数)AS‘最高分’,MIN(分数)AS‘最低分’,AVG(分数)AS'平均分'FROM选课WHERE课程号='1002'2.对结果进行分组对数据进行检索时，经常需要对结果进行汇总统计计算。在T-SQL中通常使用聚合函数和GROUPBY子句来实现统计计算。GROUPBY子句用于对表或视图中的数据按字段分组，还可以利用HAVING短语按照一定的条件对分组后的数据进行筛选。GROUPBY子句格式为：GROUPBY[ALL]分组表达式[HAVING查询条件]需要注意的是，当使用HAVING短语指定筛选条件时，HAVING短语必须与GROUPBY配合使用。【例】求每个学生选课的门数。T-SQL语句为：

SELECT学号,COUNT(*)AS选课数FROM选课GROUPBY学号【例】查询选课表中选修了两门以上课程，并且分数均超过90分的学生的学号。分析：首先将选课表中的分数超过90分的学生按照学号进行分组，再对各个分组进行筛选，找出记录数大于等于2的学生学号，进行结果输出。T-SQL语句为：

SELECT学号FROM选课

WHERE分数>90 GROUPBY学号HAVINGCOUNT(*)>=26.4.5对查询结果生成新表在实际的应用系统中，用户有时需要将查询结果保存成一个表，这个功能可以通过SELECT语句中的INTO子句来实现，用以表明查询结果的去向。如果需要将查询得到的结果存入新的数据表中，就需要使用INTO语句：INTO<新表名>，来创建新表，存储记录。INTO新表名其中：新表名是被创建的新表，查询的结果集中的记录将添加到此表中；新表的字段由结果集中的字段列表决定；如果表名前加“#”则创建的表为临时表；用户必须拥有该数据库中建表的权限；INTO子句不能与COMPUTE子句一起使用。【例】查询每门课程的平均分、最高分、最低分，将结果输出到一个表中保存。分析：首先将选课表中的记录按照课程号进行分组，再对各个分组进行统计，找出每个小组的平均值、最大值和最小值，进行结果输出到一个新表课程成绩表中。T-SQL语句如下：SELECT课程号,AVG(分数)平均分,MAX(分数)最高分,MIN(分数)最低分into课程成绩表FROM选课GROUPBY课程号--查看课程成绩表SELECT*FROM课程成绩表

6.5连接查询以上我们介绍的都是单表查询，在实际应用中，经常需要把两个或者多个表按照给定的条件进行连接而形成新的表。若一个查询同时涉及两个以上的表，则称之为连接查询。连接查询是关系数据库中最主要的查询，连接查询主要包括交叉连接查询、内连接查询和外连接查询。多表连接使用FROM子句指定多个表，连接条件指定各列之间（每个表至少一列）进行连接的关系。连接条件中的列必须具有一致的数据类型。在T-SQL中，连接查询有两大类实现形式，一类是使用等值连接形式，另一类是使用关键字JOIN连接形式。接下来介绍等值连接和JOIN连接两个实现形式。

等值连接等值连接的连接条件是在WHERE子句中给出的，只有满足连接条件的行才会出现在查询结果中。这种形式也被称为连接谓词表示形式，是SQL语言早期的连接形式。等值连接的连接条件格式：[<表1或视图1>.]<列1>=[<表2或视图2>.]<列2>【例】从学生表和选课表中，查询所有不及格的学生的学号、学生姓名、所属院系、所选的课程号和成绩。T-SQL语句为：

SELECT学生.学号,姓名,系别,课程号,分数 FROM学生,选课 WHERE学生.学号=选课.学号and分数<60JOIN关键字连接多个表T-SQL扩展了连接的形式，引入了JOIN…ON关键字连接形式，从而使表的连接运算能力得到了增强。JOIN…ON关键字放在FROM子句中，命令格式如下：FROM<表名1>[INNER]|{|LEFT|RIGHT|FULL}[OUTER]]JOIN<表名2>ON<连接条件>这种连接形式通过FROM给出连接类型，用JOIN表示连接，用ON短语给出连接条件。JOIN提供了多种类型的连接方法：内连接、外连接和交叉连接。它们之间的区别在于：从相互关联的不同表中选择用于连接的行时所采用的方法不同。6.5.1交叉连接(CROSSJOIN)交叉连接也称非限制连接，即两个表的笛卡尔积，返回结果是由第一个表的每行与第二个表的所有行组合后形成的表，因此，数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。交叉连接关键字CROSSJOIN后不跟ON短语引出的连接条件。交叉连接的语法格式如下：SELECT列表列名FROM表名1CROSSJOIN表名2【例】查询学生表和系部表的交叉连接。T-SQL语句为：SELECT学号,姓名,性别,学生.系部编号,出生日期,高考入学成绩,少数民族否,系部.系部编号,系部名称FROM学生crossjoin系部6.5.2内连接(INNER)通过前面例子发现交叉连接会产生很多冗余的记录，那么如何筛选出有用的连接呢？内连接是最常见的一种连接，也被称为普通连接或自然连接，它是系统默认形式，在实际使用中可以省略INNER关键字。也可以改写成如下形式实现：SELECT学生.学号,姓名,系别,课程号,分数FROM学生JOIN选课ON

学生.学号=选课.学号WHERE分数<60使用JOIN…ON连接词替换了上例中的WHERE子句的连接条件。内连接与等值连接效果相同相同，仅当两个表中都至少有一行符合连接条件时，内连接才返回行。1.等值连接等值连接的连接条件是在WHERE子句中给出的，只有满足连接条件的行才会出现在查询结果中。这种形式也被称为连接谓词表示形式，是SQL语言早期的连接形式。等值连接的连接条件格式：[<表1或视图1>.]<列1>=[<表2或视图2>.]<列2>【例】从学生表和选课表中，查询所有不及格的学生的学号、学生姓名、所属院系、所选的课程号和成绩。T-SQL语句为：

SELECT学生.学号,姓名,系别,课程号,分数 FROM学生,选课 WHERE学生.学号=选课.学号and分数<60说明：本例中，WHERE子句既有查询条件（分数<60），又有连接条件（学生.学号=选课.学号）；连接条件中的两个字段称为连接字段，它们必须是具有一致的数据类型。如本例中连接字段分别为学生表的“学号”字段和选课表中的”学号“字段；在单表查询中，所有的字段都来自于同一张表，故在SELECT语句中不需要特别说明。但是在多表查询中，有的字段（如“学号”字段）在几个表中都出现了，引用时就必须说明其来自那个表，否则就可能引起混乱，造成语法错误；连接条件中使用的比较符可以是<、<=、=、>、>=、!=、<>、!<和!>。当比较符为“=”时，就是等值连接。2.非等值连接当连接条件中的关系运算符使用除“=”以外的其他关系运算符时，这样的内连接称为非等值连接。等值连接的连接条件格式：[<表1或视图1>.]<列1>关系运算符[<表2或视图2>.]<列2>3.自身连接连接操作不仅可以两个表之间进行，也可以是一个表与其自身进行连接，称为表的自连接。由于连接的两个表其实是同一个表，为了加以区分，需要为表起别名。【例】使用教师表查询与“赵权”在同一个系任课的教师编号、教师姓名和教师的职称，要求不包括“赵权”本人。T-SQL代码如下：SELECTY.教师编号,Y.姓名,Y.职称FROM教师X,教师YWHEREX.系部编号=Y.系部编号ANDX.姓名='赵权'ANDY.姓名!='赵权'6.5.3外连接OUTER外连接是指连接关键字JOIN后面表中指定列连接在前一表中指定列的左边或者右边，如果两表中指定列没有匹配行，则返回空值。外连接的结果不但包含满足连接条件的行，还包含相应表中的所有行。外连接有三种形式，其中的OUTER可以省略： （1）左外连接（LEFTOUTERJOIN或LEFTJOIN）：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部满足条件的行。 （2）右外连接（RIGHTOUTERJOIN或RIGHTJOIN）：包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部满足条件的行。 （3）全外连接（FULLOUTERJOIN或FULLJOIN）：包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行，其实全外连接将返回两个表的所有行。在现实生活中，参照完整性约束可以减少对于全外连接的使用，一般情况下左外连接就足够了。但当在数据库中没有利用清晰、规范的约束来防范错误数据情况下，全外连接就变得非常有用了，你可以使用