实验7

实验7数据查询数据库查询是数据库的核心操作。SQL语言提供了SELECT语句进行数据库的查询，该语句具有灵活的使用方式和丰富的功能。【知识要点】1．SELECT语句的一般格式SELECT[ALL|DISTINCT][TOPN[PERCENT]]目标列表达式[别名][，目标列表达式[别名]]…FROM表名或视图名[别名][，表名或视图名[别名]]…[WHERE条件表达式][GROUPBY列名1[HAVING条件表达式][,列名2[HAVING条件表达式]…]][ORDERBY列名1[ASC|DESC],[列名2[ASC|DESC]]…]]实验7数据查询聚合函数描述AVG(expr)列值的平均值。该列只能包含数字数据。COUNT(expr),COUNT(*)列值的计数（如果将列名指定为expr）或是表或组中所有行的计数（如果指定*）。COUNT(expr)忽略NULL值，但COUNT(*)在计数中包含NULL值。MAX(expr)列中最大的值（文本数据类型中按字母顺序排在最后的值）。忽略NULL值。MIN(expr)列中最小的值（文本数据类型中按字母顺序排在最前的值）。忽略NULL值。SUM(expr)列值的合计。该列只能包含数字数据。忽略NULL值2．常用的聚合函数实验7数据查询3．查询条件列表查询条件谓词比较=，，，=，=，!=，，!，!确定范围BETWEEN…AND…，NOTBETWEEN…AND…确定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL多重条件AND，OR4．谓词LIKE在查询条件中的用法语法格式：[NOT]LIKE‘匹配串’[ESCAPE‘换码字符’]匹配符%（百分号）：代表任意长度的字符串。_（下横线）：代表任意单个字符。实验7数据查询【实验目的】•掌握从简单到复杂的各种数据查询。包括：单表查询、多表连接查询、嵌套查询、集合查询。•掌握用条件表达式表示检索条件。•掌握用聚合函数计算统计检索结果。实验7.1单表查询【实验目的】•掌握指定列或全部列查询；•掌握按条件查询；•掌握对查询结果排序；•掌握使用聚集函数的查询；•掌握对查询结果分组。实验7.1单表查询【实验内容】1.指定列或全部列查询：查询S表中全体学生的详细记录；查询所有学生的姓名及其出生年份。2.按条件查询及模糊查询：查询考试成绩有不及格的学生学号；查询年龄在20~23岁之间的学生姓名、系名、年龄。查询姓李的学生的姓名、学号和性别。查询名字中第2个字为“明”字的男学生的姓名和系名。3.对查询结果排序：查询信息系、计算机系学生的姓名、系名，结果按系名升序，姓名降序排序；查询所有有课程号C2成绩的学生的学号、课程号和成绩，并按成绩降序排序。4.使用聚集函数的查询：查询计算机系学生总人数；查询选修了微机原理课程的学生人数、平均成绩、最高成绩。5.对查询结果分组：查询各个课程号及相应的选课人数；查询选修了2门以上课程的学生学号、姓名和平均成绩。7数据查询【实验步骤】1.指定列或全部列查询。(1)查询S表中全体学生的详细记录；在查询窗口输入下面SQL语句：USEjxskGoSELECT*FROMSGO查询结果实验7数据查询（2）查询所有学生的姓名及其出生年份查询结果实验7数据查询2.按条件查询及模糊查询（1）查询考试成绩有不及格学生的学号。(1)查询结果（2）查询年龄在20~23岁之间的学生姓名、系名、年龄。(2)查询结果实验7数据查询（3）查询姓李的学生的姓名、学号和性别（4）查询名字中第2个字为“明”字的男学生的姓名和系名(4)查询结果(3)查询结果实验7数据查询3.对查询结果排序(1)查询信息系、计算机系学生的姓名、系名，结果按系名升序，姓名降序排序(2)查询所有有课程号C2成绩的学生的学号、课程号和成绩，并按成绩降序排序(2)查询结果(1)执行SQL语句实验7数据查询4.使用聚集函数的查询(1)查询计算机系学生总人数(2)查询选修了微机原理课程的学生人数、平均成绩、最高成绩(1)查询结果(2)图7-10查询结果实验7数据查询5.对查询结果分组(1)查询各个课程号及相应的选课人数(2)查询选修了2门以上课程的学生姓名和平均成绩(1)查询结果(2)查询结果实验7.2连接查询【实验目的】掌握涉及一个以上数据表的查询。多表之间的连接包括等值连接、自然连接、非等值连接、自身连接、外连接和复合条件连接。【实验内容】1.连接查询：•查询所有选课学生的学号、姓名、选课名称及成绩；•查询每门课程的课程号、任课教师姓名及其选课人数。2.自身连接：•查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资；•查询同时选修了“程序设计”和“微机原理”的学生姓名、课程名。3.外连接：•查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）。实验7数据查询【实验步骤】1．连接查询图7-13查询结果（1）查询所有选课学生的学号、姓名、选课名称及成绩（2）查询每门课程的课程号、任课教师姓名及其选课人数图7-13查询结果(2)查询结果(1)查询结果实验7数据查询2．自身连接（1）查询所有比“刘伟”工资高的教师姓名、工资和刘伟的工资（2）查询同时选修了“程序设计”和“微机原理”的学生姓名、系名(1)查询结果(2)执行结果实验7数据查询3．外连接查询所有学生的学号、姓名、选课名称及成绩（没有选课的同学的选课信息显示为空）执行结果实验7.3嵌套查询【实验目的】掌握嵌套查询使我们可以用多个简单查询构成复杂的查询，从而增强SQL的查询能力。【实验内容】1.返回一个值的子查询：查询与“刘伟”教师职称相同的教师号、姓名和职称。2.返回一组值的子查询：•使用ANY谓词查询讲授课程号为C5的教师姓名；•使用IN谓词查询讲授课程号为C5的教师姓名；•使用ALL谓词查询其他系中比计算机系所有教师工资都高的教师的姓名，工资和所在系；•使用EXISTS谓词查询没有讲授课程号为C5的教师姓名，所在系；•使用NOTEXISTS谓词查询至少选修了学生S9选修的全部课程的学生学号。实验7数据查询1.返回一个值的子查询（1）查询与“刘伟”教师职称相同的教师号、姓名和职称执行结果实验7数据查询2.返回一组值的子查询（1）使用ANY谓词查询讲授课程号为C5的教师姓名（2）使用“IN”谓词查询讲授课程号为C5的教师姓名图7-19执行结果(2)执行结果(1)执行结果实验7数据查询（3）使用“ALL”谓词查询其他系中比计算机系所有教师工资都高的教师的姓名、工资和所在系（4）使用“EXISTS”谓词查询没有讲授课程号为C5的教师姓名、所在系(3)执行结果(4)执行结果实验7数据查询（5）使用“NOTEXISTS”谓词查询至少选修了学生S2选修的全部课程的学生学号(5)执行结果实验7.4集合查询【实验目的】掌握使用UNION操作符将来自不同查询但结构相同的数据集合组合起来，形成一个具有综合信息的查询结果。【实验内容】查询计算机科学系的学生及年龄不大于19岁的学生。【实验步骤】查询计算机系的学生及年龄不大于19岁的学生。执行结果实验7习题【实验题】•内容与要求针对教学数据库“jiaoxuedb”如下表，实现下列SQL查询：1.查询成绩在80～90之间的记录。2.查询至少有4个同学选修的课程名。3.查询其他系中比“信息”系所有学生年龄都大的学生名单及年龄，并按年龄降序输出。4.查询与学生张建国同岁的所有学生的学号，姓名和系别。5.查询选修了2门以上课程的学生名单。6.查询至少有一门与“张建国”选课相同的学生的姓名、课程名和系别。7.查询成绩比该课程平均成绩高的学生的成绩表。8.查询选修课号为“01001”课程且成绩高于课程“01002”学生的姓名、此两门课程的课程名和成绩。9.查询所有未修“01001”号课程的学生名单。10.查询每个同学各门课程的平均成绩和最高成绩，按降序排列输出姓名、平均成绩，最高成绩。11.查询所有学生都选修了的课程号和课程名。12.查询选修了991102号学生选修了的课程的学生学号和姓名。实验7习题【思考题】1．针对教学数据库jxsk，下面查询语句是否正确，为什么？SELECTCN，TN，PROF，COUNT（SC.SNO）FROMC，T，TC，SCWHERET.TNO=TC.TNOANDC.CNO=TC.CNOANDSC.CNO=C.CNOGROUPBYSC.CNO2．用LIKE谓词表示查询条件WHERE中的表达式，下面哪个正确，为什么？（1）SNLIKE‘欧阳_’（2）SNLIKE‘欧阳__’（3）SNLIKE‘欧阳%_’（4）SNLIKE‘欧阳_%’3．解释下面ORDERBY子句的作用？（1）ORDERBYSN，SEX（2）ORDERBYAGE，PROFDESC4．解释下面GROUPBY子句的作用？（1）GROUPBYSEX（2）GROUPBYAGE，PROF实验7习题此实验时的各数据表内容：实验7习题