分组统计,嵌套查询与添加删除修改操作.doc

分组与计算查询 Group by短语用来对查询结果进行分组，把某一列的值相同的记录分在一组，通过计算每组产生一个结果。 基本格式： Select…from…[where联接条件and查询条件] Group by 字段名[having 分组条件表达式] 说明： group by 短语后面可以有很多个字段名，分组时把这些字段上值相同的记录分在一组。 having子句总是跟在group by 子句之后，用来限定分组必须满足的条件，不可以单独使用。 都接条件表达式，但并不矛盾，当一个查询命令中既有where短语同时又有having短语时，是先用where子句限定元组，然后进行分组，最后再用having子句限定分组。 例4.22 检索每个学生的平均成绩。 Select学号，avg（成绩）as平均分from score group by 学号 查询结果如图8-26所示。 说明：在这个查询中，首先按学号进行分组，将学号相同的学生记录放在一起，然后再计算每个学生所选修的所有课程的平均分。 例4.23 检索出每个学生的学号、姓名、总分、平均分、最高分和最低分，并将结果按总分降序排序。 Select score.学号,姓名,sum（成绩）as总分，avg（成绩）as平均分，； Max（成绩）as最高分，min（成绩）as最低分； 图8-26 例8.22查询结果 From student，score； Where student.学号=score.学号； Group by score.学号； Order by 总分 desc 查询结果如图8-27所示。 例4.24 检索出平均分在80分以上的学生的学号、姓名和平均成绩。并将结果按平均分升序排序。 Select score.学号，姓名，avg（成绩）as平均分； From student，score； Where student.学号=score.学号； Group by score.学号having avg （成绩）=80； Order by 平均分 查询结果如图8-28所示。 图8-27 例8.23查询结果 例4．25 检索至少有两名学生选修的课程的平均分，检索项包含课程编号和平均分。 SELECT 课程编号，AVG（成绩）AS 平均分 FROM SCORE； GROUP BY 课程编号； HAVING COUNT（*）=2 查询结果如图8-29所示 说明： order by短语后面只能用字段名或别名，不可以用表达式。 子句having count（*）=2表示：每一组的元组个数大于等于2时满足条件。 例4.26 检索选修了两门或两门以上课程的学生的平均成绩，检索项包含学号、平均分。 Select 学号，avg（成绩）as平均分from score； Group by 学号 having count（*）=2 查询结果如图8-30所示。 图8-28 例8.24查询结果 图8-29 例8.25查询结果 图8-30 例8.26查询结果 4.2.6 嵌套查询 接下来讨论另一类基于多个关系的查询，这类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。 下面介绍嵌套查询中子查询的常用谓词和量词。 1.带有in或not in运算符的子查询 带有in或not in运算符的子查询是指主查询与子查询之间用in或not in联接，判断某个属性列值是否在子查询的结果中。由于在嵌套查询中，子查询的结果往往是一个集合，所以运算符in和not in是嵌套查询中最经常使用的谓词。 基本格式： Select…from…where…in（select …from…where…） 说明： 在嵌套查询中有两层select…from…【where…】Select * from student ； Where 学号in（select学号 from score where 成绩90） 等价于： Select distinct student.*； From student，score； Where student.学号=score.学号 and 成绩90 查询结果如图8-39所示。 图8-39 例8.35查询结果 例4.36 检索没有选课的学生信息。 Select * from student where学号 not in （select 学号from score） 查询结果如图8-40所示。 图8-40 例8.36查询结果 2.带有比较运算符的子查询 带有比较运算符的子查询是指主查询与子查询之间用比较运算符进行联接。当