数据库应用技术教程 课件 第4章 数据查询2

课前练习1、查询所有选课学生的学号2、查询考试成绩有不及格的学生的学号3、查询既不姓郑又不姓王的学生学号和姓名4、查询信息系和数学系的学生姓名和所在系（6）多重条件查询在WHERE子句中可以使用逻辑运算符AND和OR来组成多条件查询。用AND连接的条件表示必须全部满足所有的条件的结果才为True；用OR连接的条件表示只要满足其中一个条件结果即为True。例24．查询计算机系年龄在20岁以下的学生姓名。

SELECTSnameFROMStudentWHERESdept='计算机系'

ANDSage<20

示例例25．查询计算机系和信息系年龄大于等于21岁的学生姓名、所在系和年龄。SELECTSname,Sdept,SageFROMStudentWHERE(Sdept='计算机系'

ORSdept='信息系')ANDSage>=21或：SELECTSname,Sdept,SageFROMStudentWHERESdeptIN('计算机系','信息系')ANDSage>=21

4.2简单查询对查询结果进行排序

对查询结果进行排序(orderby)排序子句为：

ORDERBY<列名>[ASC|DESC][,<列名>…]说明：按<列名>进行升序（ASC）或降序（DESC）排序，默认是按升序进行排序。示例例26．将学生按年龄的升序排序。

SELECT*FROMStudentORDERBYSage例27．查询选修了3号课程的学生的学号及其成绩，查询结果按成绩降序排列。SELECTSno,GradeFROMSC WHERECno='3'

ORDERBYGradeDESC

例28.查询全体学生的信息，查询结果按所在系的系名升序排列，同一系的学生按年龄降序排列。SELECT*FROMStudent

ORDERBYSdept,SageDESC

4.2简单查询使用聚合函数汇总数据

使用计算函数汇总数据SQL提供的聚合函数有：COUNT（*）：统计表中元组个数；COUNT（[DISTINCT]<列名>）：统计本列列值个数；SUM（[DISTINCT]<列名>）：计算列值总和；AVG（[DISTINCT]<列名>）：计算列值平均值；MAX（[DISTINCT]<列名>）：求列值最大值；MIN（[DISTINCT]<列名>）：求列值最小值。上述函数中除COUNT（*）外，其他函数在计算过程中均忽略NULL值。示例例29．统计学生总人数。

SELECTCOUNT(*)FROMStudent

例30．统计选修了课程的学生的人数。

SELECTCOUNT(DISTINCTSno)FROMSC例31．统计95002号学生的考试总成绩之和。SELECTSUM(Grade)FROMSCWHERESno='95002'

示例例32．计算选修1号课程学生的考试平均成绩。

SELECTAVG(Grade)FROMSCWHERECno='1'例33．查询1号课程的考试最高分和最低分。

SELECTMAX(Grade),MIN(Grade)FROMSCWHERECno='1'注意：计算函数不能出现在WHERE子句中示例例34．查询“95001”学生的选课门数、考试课程门数以及考试最高分、最低分和平均分。SELECTCOUNT(*)AS选课门数,

COUNT(Grade)AS考试门数,MAX(Grade)AS最高分,

MIN(Grade)AS最低分,AVG(Grade)AS平均分FROMSCWHERESno='95001'4.2简单查询对查询结果进行分组统计

对查询结果进行分组统计

作用：可以控制计算的级别：对全表还是对每一分组。目的：细化计算函数的作用对象。分组语句的一般形式： GROUPBY<分组依据列>[,…n]

[HAVING<组选择条件>]（1）使用GROUPBY例35．统计每门课程的选课人数，列出课程号和人数。SELECTCnoas课程号,

COUNT(Sno)as选课人数FROMSCGROUPBYCno

对查询结果按Cno的值分组，所有具有相同Cno值的元组为一组，然后再对每一组使用COUNT计算，求得每组的学生人数。SnoCnoGrade95001187950012769500137995001480950021899500418395004256CnoCount(Sno)13223141SnoCnoGrade95001187950021899500418395001276950042569500137995001480例36．查询每个学生的选课门数和最高分。SELECTSnoas学号,

COUNT(*)as选课门数,

MAX(Grade)as最高成绩FROMSCGROUPBYSno注意GROUPBY子句中的分组依据列必须是表中存在的列名，不能使用AS指派的列别名。例如，例36中不能将GROUBY子句写成：GROUPBY学号。带有GROUPBY子句的SELECT语句的查询列表中只能出现分组依据列或聚合函数，因为分组后每个组只返回一行结果。示例例37．统计每个系的学生人数和平均年龄。SELECTSdept,COUNT(*)AS学生人数,

AVG(Sage)AS平均年龄

FROMStudentGROUPBYSdept示例例38.带WHERE子句的分组。统计每个系的男生人数。SELECTSdept,Count(*)男生人数

FROMStudentWHERESsex='男'

GROUPBYSdept示例例39.按多列分组。统计每个系的男生人数和女生人数，以及男生的最大年龄和女生的最大年龄。结果按系名的升序排序。SELECTSdept,Ssex,Count(*)人数,

Max(Sage)最大年龄FROMStudentGROUPBYSdept,SsexORDERBYSdept（2）使用HAVINGHAVING用于对分组进行筛选，它有点象WHERE子句，但它用于分组而不是对单个记录。如：查询修了3门以上课程的学生的学号

SELECTSnoFROMSCGROUPBYSno

HAVINGCOUNT(*)>3

示例例40.查询选修了3门以上课程的学生的学号和选课门数。SELECTSno,Count(*)选课门数

FROMSCGROUPBYSnoHAVINGCOUNT(*)>3处理过程：先执行GROUPBY子句对SC表数据按Sno进行分组，然后再用统计函数COUNT分别对每一组进行统计，最后筛选出统计结果满足大于3的组。分组统计筛选处理过程示意图示例例41．查询修课门数等于或大于4的学生的平均成绩和选课门数。

SELECTSno,AVG(Grade)平均成绩,

COUNT(*)修课门数

FROMSCGROUPBYSno

HAVINGCOUNT(*)>=4

几个子句比较WHERE子句用来筛选FROM子句中指定的数据源所产生的行数据。GROUPBY子句用来对经WHERE子句筛选后的结果数据进行分组。HAVING子句用来对分组后的结果数据再进行筛选。示例例42.查询计算机系和数学系的学生人数。方法1：SELECTSdept,COUNT(*)FROMStudentGROUPBYSdeptHAVINGSdeptIN('计算机系','数学系')方法2：SELECTsdept,COUNT(*)FROMStudentWHERESdeptIN('计算机系','数学系')GROUPBYSdept第二种写法先筛选数据，再分组，效率更高。示例例43.查询每个系年龄小于等于21岁的学生人数。SELECTSdept,COUNT(*)FROMStudentWHERESage<=21