数据库第9章-选择查询

第9章选择查询9.1聚合函数聚合函数用以在查询中生成对数据的统计结果。常用聚合函数CountSumAvgMaxMinCount用于对符合查询条件的记录进行计数。例1．统计姓名中含有“小”的学生人数。SELECTCount(*)FROM2003学生名单WHERE姓名Like"*小*"例2．统计学生总人数。SELECTCount(*)FROM2003学生名单Sum用来返回在查询的指定字段中所包含的一组值的总计。

例3．求031国贸1班的学生的入学成绩总和。 SELECT“031国贸1”AS班级,Sum(入学成绩)AS总成绩 FROM2003学生名单 WHERE班级=“031国贸1"Avg用于计算查询的指定字段中包含的一组值的算术平均值。执行时，先逐条记录进行检查，如果某记录的指定字段为空值或者某记录不符合筛选条件，就忽略该记录，否则参加计算。最后得到的结果是符合筛选条件的所有有效字段值的平均值。在没有任何符合条件的记录的情况下，该值为0。Avg例3．求所有学生的入学成绩的平均值。SELECTAvg(入学成绩)FROM2003学生名单本例中，放在SELECT后的Avg(入学成绩)为一表达式，描述将生成一个数据列，该列的值为对入学成绩字段求平均值。本例没有对记录进行条件筛选，因此是对全部学生的入学成绩求平均值。Avg例4．求“031国贸3班”的“男”同学的平均入学成绩。SELECTAvg(入学成绩)AS平均成绩FROM2003学生名单WHERE班级=“031国贸3"AND性别="男"Avg例5．求02科室男医生的平均年龄。SELECTAvg(Year(DATE())-Year(出生日期))AS平均年龄FROM医生WHERE科室号=“02”AND性别=“男"Max、MinMax、Min函数用来返回在查询的指定字段内所包含的一组值中的最大值或最小值。Max和Min函数的参数可以是文本、数字、日期等类型的字段或表达式。如果是表达式，将先进行指定的计算，然后用表达式的值进行比较选择。

例7．查询年龄最大和最小的学生的出生日期。SELECTMin(出生日期),Max(出生日期)FROM2003学生名单Max、Min例8．统计学生人数、最低入学成绩、最高入学成绩和平均成绩。SELECTCount(*)AS学生人数, Min(入学成绩)AS最低分, Max(入学成绩)AS最高分, Avg(入学成绩)AS平均分

FROM

2003学生名单本例同时进行了四项统计处理，其结果为四个值。9.2分组查询在进行统计处理时，往往需要进行分类统计，就是说按一定的规则将表中的数据记录分成若干组，对每组数据进行统计。完成分组统计时，每一组的统计结果形成一行数据，最终由这些数据行形成统计查询的结果集。例如，如果学生来自20个不同的班级，当按“班级”进行人数统计时，系统将会把学生记录按不同的班级值分成形成20个组，对每组中的记录进行计数，最后形成20条记录。9.2分组查询进行分组统计的关键在于如何对数据记录进行分组。

GROUPBY<分组字段表>分组字段表中可以有1到10个字段名。在结果集中，将形成与每个分组相应的记录，并在该记录行中存放规定的计算值。9.2.1分组查询示例例9．统计来自不同地区的学生人数。SELECT地区,Count(*)AS人数FROM学生情况

GROUPBY地区有多少个不同的地区，就把记录分成多少个组，最终每组的统计结果形成一行。在设计分组查询时，应该注意SELECT字段列表中单独出现的所有字段都必须包括在GROUPBY子句中，如果不包含则出错。9.2.1分组查询示例例10．统计来自不同科室、不同职称的男医生人数，结果按职称升序排序。SELECT科室号,职称,Count(*)AS人数FROM医生WHERE性别="男"GROUPBY科室号,职称ORDERBY职称如果在查询中使用了WHERE子句，分组时那些不符合查询条件的记录将被筛选掉。9.2.1分组查询示例例11．统计各班学生的人数、平均入学成绩，结果按入学成绩的平均值排序。SELECT班级,Count(*)AS人数,Avg(入学成绩)AS平均成绩FROM2003学生名单GROUPBY班级

ORDERBYAvg(入学成绩)在查询中可以使用多个聚合函数，并且可以在聚合函数中使用相关字段，在聚合函数中使用的字段不必出现在GROUPBY子句中。如果在GROUPBY子句中使用了某些字段，但没有在SELECT字段列表中列出，分组是可以进行的，只不过无法看到按什么字段进行的分组及分组值的具体情况了。所以，一般情况下，分组字段应该在SELECT字段列表中列出。9.2.1分组查询示例9.2.2筛选分组查询的结果在进行分组查询时，如果只对结果集中的某些数据感兴趣，可以再用HAVING子句对分组的结果记录再进一步筛选，使得最终在结果集中保留的是那些满足HAVING子句条件的那些记录。9.2.2筛选分组查询的结果例12．列举学生人数大于40人的班级。SELECT班级,Count(*)AS人数FROM

2003学生名单GROUPBY班级HAVINGCount(*)>40HAVING子句通常与GROUPBY子句配合使用，因为它的作用是对分组查询的结果进行筛选。HAVING子句中的表达式用来描述筛选条件，在表达式中应该用到SELECT字段列表中的一个或多个字段，如果是对聚合函数的计算值进行筛选，则应该直接用聚合函数构成条件表达式。9.2.2筛选分组查询的结果9.2.2筛选分组查询的结果例13．列举参加考试的学生人数少于30的课程，结果按考试人数的统计值排序。SELECT课程号,Count(*)AS考试人数FROM成绩GROUPBY课程号HAVINGCount(*)<30ORDERBYCount(*)基于“生源情况”表完成下列查询统计不同的姓的人数，列出：姓、人数，结果按人数降序排序。按地区分类统计“入学成绩”的平均值、最大值、最小值，并按平均值降序排序。找出年龄平均值最高的班级。列出学生人数少于100的地区。9.3联接查询学院（学院编号，学院名称）班级（班级编号，班级名称，学院编号，班级人数）学生（学号，姓名，性别，出生日期，班级编号，民族，地区，政治面貌）课程（课程编号，课程名称，学院编号，考核类型）学生考试（ID，学号，场次，课程编号，教室）教师（教师编号，姓名，学院编号）教室(教室，校区)场次（场次）教师监考（ID，教师编号，场次，课程编号，教室）9.3.1内部联接例14．查询所有教师数据，显示他们的教师编号、姓名和所在学院名称，结果按教师编号排序。教师（教师编号，姓名，学院编号）学院（学院编号，学院名称）教师编号，

姓名，

学院名称SELECT教师编号,姓名,学院名称FROM教师INNERJOIN学院

ON教师.学院编号=学院.学院编号ORDERBY教师编号9.3.1内部联接教师INNERJOIN学院

ON教师.学院编号=学院.学院编号在内部联接中描述联接条件时，通常使用等号来构成联接表达式，并且引用左、右表中具有相同意义和类型的字段。在处理具有一对多联系的表间的联接查询时，通常是通过两个表的主键和外键进行联接。内部联接要求联接双方具有完全对应的行。否则，一个表中存在的某行而在另一个表中找不到具有相同值的行时，这一行就不可能被查询到。左表右表联接条件9.3.1内部联接例15．查询所有来自四川地区的男同学的学号、姓名、班级名称和学院名称，结果按学号排序。SELECT学生.学号,学生.姓名,班级.班级名称,学院.学院名称FROM

(学生INNERJOIN班级

ON学生.班级编号=班级.班级编号)INNERJOIN学院

ON班级.学院编号=学院.学院编号WHERE学生.地区="四川"AND学生.性别="男"ORDERBY学生.学号三个表的内联接9.3.1内部联接例16．查询“商务学院”所有学生的各项数据及班级名称，结果按学号排序。SELECT班级.班级名称,学生.*FROM学生,班级,学院WHERE

(学生.班级编号=班级.班级编号)AND (班级.学院编号=学院.学院编号)AND

(学院.学院名称="商务学院")ORDERBY学生.学号在WHERE部分描述联接9.3.2外部联接在进行联接查询时，如果希望某个表中的记录在另一个表中找不到完全对应记录的情况下仍出现在结果集中，可以采用外部联接。SQL语言支持用来完成不完全对应的联接，此类联接称为外部联接。外部联接有左外部联接和右外部联接两种。9.3.2外部联接左外联接在左外部联接中，对于SQL语句FROM子句中的第一个（左）表，查询包括所有行；对于另一个（右）表，则只包括两个表的联接字段值能匹配的行。右外联接在右外部联接中，对于SQL语句FROM子句中的第二个（左）表，查询包括所有行；对于另一个（右）表，则只包括两个表的联接字段值能匹配的行。9.3.2外部联接例17．查询编号大于“13”的学院的班级目录SELECT学院.学院名称,班级.班级名称,学院.学院编号FROM学院LEFTOUTERJOIN班级

ON学院.学院编号=班级.学院编号WHERE学院.学院编号>="13"9.3.2外部联接例18．查询教师名单，显示教师编号、姓名和所在学院名称，结果按学院名称和教师编号排序。SELECT学院.学院名称,教师.教师编号,教师.姓名FROM学院RIGHTOUTERJOIN教师

ON学院.学院编号=教师.学院编号ORDERBY学院.学院名称,教师.教师编号9.3.3自联接从一个表中查询出与该表中某些记录有联系的其他记录。在同一个表上建立联接，以完成对一个表进行一些特殊的查询。查询与某同学同班的学生名单查询与某教师同一时间监考的其他教师9.3.3自联接例19．查询方晨所在班级的学生名单SELECT学生B.学号,学生B.姓名FROM学生AS学生AINNERJOIN学生AS学生BON学生A.班级编号=学生B.班级编号WHERE学生