数据库基础教材37607

,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Click to edit Master title style,项目五 学生成绩查询,SQL Server 2005 数据库基础,项目五 学生成绩查询,模块,5-1,统计查询,学习目标,工作任务,任务,1,各类职称的教师人数的统计,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,teacher,。最后要显示的信息为两列，一列是职称的名称，一列为该类职称的教师人数。职称列在表中对应的字段为,ttitle,，而人数是需要统计的信息，这就需要用到聚合函数,COUNT,了。,任务,1,各类职称的教师人数的统计,【,程序代码,】,USE student,GO,SELECT,ttitle,AS,职称,COUNT(*)AS,人数,FROM teacher,GROUP BY,ttitle,任务,1,各类职称的教师人数的统计,【,执行结果,】,任务,2,统计各课程的最高分、最低分和平均成绩,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,choice,。最后要显示的信息为四列，即课程编号、最高分、最低分和平均分。其中最高分、最低分和平均分都不是表中的列，要利用聚合函数,MAX,、,MIN,和,AVG,显示信息。,【,程序代码,】,USE student,GO,SELECT,cno,AS,课程编号,MAX(grade,)AS,最高分,MIN(grade,)AS,最低分,AVG(grade,)AS,平均分,FROM choice,GROUP BY,cno,任务,2,统计各课程的最高分、最低分和平均成绩,【,执行结果,】,任务,2,统计各课程的最高分、最低分和平均成绩,任务,3,查询选修了,4,门以上（包括,4,门）选修课的学生,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,choice,。最后要显示学生学号和课程门数，这里要筛选出的学生是选修了四门课程及以上的学生学号。要完成这个任务，首先可以统计出每个学生选修的课程门数，然后将选修课程门数大于等于,4,的学生筛选出来。这样就要用到,GROUP BY,和,HAVING,子句，,HAVING,子句可以对分类汇总的结果进行筛选,【,任务代码,】,USE student,GO,SELECT,sno,AS,学号,COUNT(*)AS,课程门数,FROM choice,GROUP BY,sno,HAVING COUNT(*)=4,任务,3,查询选修了,4,门以上（包括,4,门）选修课的学生,【,执行结果,】,任务,3,查询选修了,4,门以上（包括,4,门）选修课的学生,知识说明,聚合函数,聚合函数属于系统内置函数之一，它与前面介绍的数学函数、字符串函数等内置函数不同，它能够对一组值执行计算并返回单一的值。聚合函数经常与,SELECT,语句的,GROUP BY,子句一同使用。除,COUNT,函数之外，聚合函数一般忽略空值。,知识说明,聚合函数的种类,表,51,常用聚合函数的功能,聚合函数,功能,AVG,返回组中值的平均值,COUNT,返回组中项目的数量,MAX,返回表达式的最大值,MIN,返回表达式的最小值,SUM,返回表达式中所有值的和,STDEV,返回表达式中所有值的统计标准偏差,VAR,返回表达式中所有值的统计标准方差,知识说明,GROUP BY,子句,GROUP BY,语句从英文的字面意义上理解就是“根据,(BY),一定的规则进行分组,(GROUP)”,。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。,HAVING,子句,用于在包含,GROUP BY,子句的,SELECT,语句中指定显示哪些分组记录。在,GROUP BY,对记录进行组合之后，将显示满足,HAVING,子句条件的,GROUP BY,子句进行分组的任何记录。,模块,5-2,多表查询,连接查询,学习目标,工作任务,任务,1,查询孙晓龙的所有选修课的成绩,【,任务分析,】,此查询要用到的数据库为,student,，用到的表为,student,和,choice,。最后要显示的信息为两列，一列是课程编号，一列为课程成绩。虽然这两列在,choice,表中都有，但是本例中要显示的是名叫孙晓龙的学生的选课信息。,解决这个问题可以将,student,和,choice,两表进行内连接操作，然后再筛选出满足条件的记录，即姓名为孙晓龙的学生的选课信息。,【,程序代码,】,USE student,GO,SELECT,cno,grade,FROM student INNER JOIN choice,ON,student.sno,=,choice.sno,WHERE,sname,=,孙晓龙,任务,1,查询孙晓龙的所有选修课的成绩,【,执行结果,】,任务,1,查询孙晓龙的所有选修课的成绩,任务,2,查询选修了课程编号为“,0101001”,的学生的姓名和成绩,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,student,和,choice,。最后要显示的信息为两列，一列是学生姓名，一列为课程成绩。,解决这个问题可以将,student,和,choice,两表进行内连接操作，然后再筛选出满足条件的记录，即课程编号为,0101001,的课程成绩。,【,程序代码,】,USE student,GO,SELECT,sname,AS,姓名,grade AS,成绩,FROM student INNER JOIN choice,ON,student.sno,=,choice.sno,WHERE,cno,=0101001,任务,2,查询选修了课程编号为“,0101001”,的学生的姓名和成绩,【,执行结果,】,任务,2,查询选修了课程编号为“,0101001”,的学生的姓名和成绩,任务,3,查询选修了“,Illustrator,平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,student,、,choice,和,course,。最后要显示的信息为两列，一列是学生姓名，一列为课程成绩。学生姓名在,student,表中，而课程成绩则在,choice,表中。,解决这个问题可以将,student,、,choice,和,course,三表进行内连接操作，然后再筛选出满足条件的记录，即课程名称为“,Illustrator,平面设计”的课程成绩。,【,任务代码,】,USE student,GO,SELECT,A.sname,AS,姓名,B.grade,AS,成绩,FROM student AS A JOIN choice AS B,ON,A.sno,=,B.sno,JOIN course AS C,ON,B.cno,=,C.cno,WHERE,C.cname,=Illustrator,平面设计,ORDER BY,B.grade,DESC,任务,3,查询选修了“,Illustrator,平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列,【,执行结果,】,任务,3,查询选修了“,Illustrator,平面设计”课程的学生的姓名和课程成绩，并按成绩降序排列,知识说明,连接查询,连接查询是关系型数据库中重要的查询类型之一，通过表间的相关字段，可以追踪各个表之间的逻辑关系，从而实现跨表间的查询。,知识说明,连接查询的种类,表,52 JOIN,关键字的含义,连接类型,连接符号,备注,左外连接,LEFT JOIN,外连接,右外连接,RIGHT JOIN,全外连接,FULL JOIN,交叉连接,CROSS JOIN,交叉连接,内连接,INNER JOIN,INNER,可省略,知识说明,内连接的种类,等值连接,在连接条件中使用等号,(=),运算符比较被连接列的列值，其查询结果中列出被连接表中的任何列，包括其中的重复列。,自然连接,自然连接是等值连接的一种特殊情况，即在连接条件中使用等于,(=),运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。,不等值连接,在连接条件使用除等于运算符以外的其他比较运算符比较被连接的列的列值。这些运算符包括,、,=,、,=,、,、,!ALL(SELECT,sscore,FROM student,WHERE,classno,=07010111),AND,classno,07010111,任务,2,查询比“,07010111”,班学生入学成绩都高的其他班的学生的学号和姓名,【,执行结果,】,任务,2,查询比“,07010111”,班学生入学成绩都高的其他班的学生的学号和姓名,任务,3,查询选修课考试不及格的学生的学号和姓名,【,任务分析,】,根据任务要求，此查询要用到的数据库为,student,，用到的表为,student,和,choice,。最后要显示的信息为两列，一列是学生学号，一列为学生姓名。,解决这个问题可以利用子查询，首先从,choice,表中找出考试不及格的学生的学号，然后再将此作为父查询的条件，从表,student,找出学生的姓名。,任务,4,查询选修了课程编号为“,0101001”,的学生的学号和姓名,【,任务代码,】,USE student,GO,SELECT,sno,sname,FROM student,WHERE EXISTS,(SELECT*,FROM choice,WHERE,student.sno,=,choice.sno,AND,cno,=0101001),【,执行结果,】,任务,4,查询选修了课程编号为“,0101001”,的学生的学号和姓名,知识说明,子查询,子查询也称为内部查询，而包含子查询的语句也称为外部查询或父查询。子查询是一个,SELECT,语句，它嵌套在一个,SELECT,语句、,SELECT.INTO,语句、,INSERT.INTO,语句、,DELETE,语句、或,UPDATE,语句或嵌套在另一子查询中。,子查询的,SELECT,查询总是使用圆括号括起来。它不能包含,COMPUTE,或者,FOR BROWSE,子句，如果同时指定了,TOP,子句，则只能包含,ORDER BY,子句。,知识说明,子查询的种类,带有比较运算符的子查询,带有比较运算符的子查询，子查询的结果是一个单一的值。常用的比较运算符包括,、,=,、,=,、,、,!,和,等。,ANY,或,ALL,子查询,如果子查询返回的值不是单一的值而是一个结果集，则可以使用带有,ANY,或,ALL,的子查询，但是运用该类查询时必须同时使用比较运算符。,ANY,的含义表示父查询与子查询结果中的某个值进行比较运算，而,ALL,的含义表示父查询与子查询结果中的所有值进行比较运算。,Thank You!,演讲完毕，谢谢观看！,