6数据查询2

查询和更新学生选课系统中的数据能够使用简单的SQL语句查询数据库表中的数据能够使用SQL语句进行多表数据的查询能够使用SQL中的子查询完成复杂查询能够使用SQL语句对数据库表中的数据进行插入、修改和删除复习:SELECT语句格式、功能SELECT[DISTINCT][TOPN[PERCENT]]字段列表FROM表名WHERE子句中的查询条件比较运算符（如=，，，等）逻辑运算符（AND，OR，NOT）范围运算符（BETWEEN…AND…）列表运算符（IN）模糊查询条件（LIKE）空值判断符（ISNULL）要评奖学金了，班主任想要查看咱们班某门课的成绩，有哪些同学考的比较好？1、ORDERBY子句使用ORDERBY子句重新排列检索结果【升序】ASC【降序】DESC可以在ORDERBY子句中指定多个列,ORDERBY子句写在WHERE子句之后。【例】检索课程表的教师名、课程号、课程名，要求检索结果首先按教师名降序排列，教师名相同时，则按课程号的升序排列Selectteacher,couno,counamefromcourseOrderbyteacherdesc,couno1、ORDERBY子句2、检索报名人数多于限制选课人数的课程信息，要求显示课程名称、报名人数和限制选课人数之比，并按该比例的降序排列。selectcouname,willnum/limitnumas‘二者之比'fromcoursewherewillnumlimitnumorderbywillnum/limitnumdesc要评奖学金了，班主任想要查看咱们班每个同学的上学期的总成绩。在select查询语句中，使用的聚合函数如下：查询课程表中最少的报名人数、最多报名人数以及平均报名人数。查询共有多少系部。Selectsum(willnum)'最多报名人数',avg(willnum)'平均报名人,min(willnum)'最少报名人数'fromcourseselectcount(*)fromdepartment2、COMPUTE子句用来计算总计或进行分组小计【问题】检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算平均报名人数2、COMPUTE子句[说明]该子句用在WHERE子句之后,总计值或小计值作为附加新行出现在检索结果中。从检索结果可以看到：检索结果中有两个结果集（1）包含选择的列的所有明细行（2）包含COMPUTE子句中所指定的聚合函数的合计[使用聚合函数]AVG()、MAX()、MIN()、SUM()聚合函数也可以出现在SELECT子句中[扩展]（1）只检索课程分类为“信息技术”的课程信息(where条件)2、COMPUTE子句（2）可以在检索结果中显示多个聚合函数的合计selectcouno,couname,kind,willnum,limitnumfromcoursecomputeavg(willnum),sum(limitnum)【练习1】检索周二晚上课的课程名称和教师名，并对符合条件的课程门数进行统计selectcouname,teacherfromcoursewhereschooltime='周二晚'computecount(couname)3、COMPUTEBY子句【例】按类检索课程表中的课程编码、课程名称、课程分类、报名人数，并计算每类课程的平均报名人数从检索结果可以看到：检索结果中有多个组，每个组有两个结果集这两个结果集中的内容同上[注意]必须使用ORDERBY排序要分组的列【练习2】按上课时间分类检索课程名称、教师名和授课时间，并对符合条件的课程门数进行统计3、COMPUTEBY子句selectcouname,teacher,schooltimefromcourseorderbyschooltimecomputecount(couname)byschooltime问题COMPUTE子句是在一个明细行的基础上增加一个总计或小计,但如果只想统计每类课程的平均报名人数，该如何处理？4、GROUPBY子句【问题】按课程分类统计每类课程的平均报名人数[说明]GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUPBY子句中的列或者包含在聚合函数中。练习：统计每个学生所报的选修课程数。显示学号、课程号、志愿号，并统计每个学生所报的选修课程数。selectstuno,count(couno)fromstucougroupbystunoselectstuno,couno,willorderfromstucouorderbystunocomputecount(couno)bystuno总结computeby和groupby分组统计显示结果：computeby既显示查询结果的明细行，又显示汇总行；语句用法：computeby必须使用orderby排序要分组的列；groupby要求select子句中选项列表中出现的列，只能是groupby子句中的列或者包含在聚合函数中。复习:computeby和groupby语句两个语句区别？显示结果：computeby既显示查询结果的明细行，又显示汇总行；语句用法：computeby必须使用orderby排序要分组的列；groupby要求select子句中选项列表中出现的列，只能是groupby子句中的列或者包含在聚合函数中。4、GROUPBY子句【问题】按课程分类统计每类课程的平均报名人数[说明]GROUPBY子句经常用于SELECT子句中包含有聚合函数的情况。此时，SELECT子句中选项列表中出现的列，只能是GROUPBY子句中的列或者包含在聚合函数中。5、HAVING子句【问题】上面的例子中，如果只统计“信息技术”类课程的平均报名人数[说明]HAVING经常用在GROUPBY子句之后，用于限定结果集中的分组,也可以使用WHERE子句。可以看到，HAVING子句是对结果进行过滤，而WHERE是对原始记录进行过滤。[注意]HAVING子句中的列只能是GROUPBY子句中或者聚合函数中的列【练习】检索平均报名人数大于30人的课程类和每类平均报名人数[注意]可以在SELECT子句和HAVING子句中使用聚合函数，但是不能在WHERE子句中使用他们。6、使用子查询在一个查询中包含另一个查询【问题】检索报名人数大于平均报名人数的课程编码、课程名称和报名人数STEP1：如何求平均报名人数STEP2：完成题目要求平均报名人数表示为：AVG(willnum)，查询分析器中运行命令：Select‘课程编码’=couno，‘课程名称’=couname，‘报名人数’=willnumFromcourseWherewillnum(selectavg(willnum)fromcourse)6、使用子查询【练习】：检索报名人数最多的课程编码、课程名称、报名人数。selectcouno,couname,willnumfromcoursewherewillnum=(selectmax(willnum)fromcourse)go多表数据查询如果问：“计算机应用工程系”下属的班级有哪些，应该查询哪些表格？这就是多表查询了。如果在多个表中进行查询，结果会怎样？SELECT*FROMDepartment,Class为了得到所需要的检索结果，必须给出连接条件。多表数据查询连接的类型有：内连接、外连接和交叉连接内连接：只包含满足连接条件的数据行；外连接：是连接运算的扩展，用它可以处理查询时的信息缺失。包括做外连接、右外连接和全连接三种；交叉连接：是一种很少使用的连接，交叉连接的结果就是笛卡儿积。如：SELECT*FROMDepartment,Class内连接：内连接：只包含满足连接条件的数据行select*from班级表,学生表where班级表.编号=学生表.编号select*from班级表innerjoin学生表on班级表.编号=学生表.编号内连接：带有连接条件【1】查询“林斌”同学是哪个班的，要求显示姓名，班级名称。【2】查询学生选课信息，要求显示姓名，课程名称、志愿号，按姓名和志愿号排序。selectstuname,classnamefromstudent,classwherestudent.classno=class.classnoandstuname='林斌'goselectstuname,couname,willorderfromstudent,stucou,coursewherestudent.stuno=stucou.stunoandstucou.couno=course.counogo内连接：【3】检索计算机应用工程系开设的选修课情况，显示信息包括姓名、课程号、课程名称、授课教师。selectstuname,course.couno,couname,teacherfromstudent,stucou,course,departmentwherestudent.stuno=stucou.stunoandstucou.couno=course.counoandcourse.departno=department.departnoanddepartname='计算机应用工程系'[注意]在引用的多表中，如果列名在多个表中同名，为了避免列名不明确，在SELECT子句中必须在列名前加上表的前缀，即“表名.列名”。[注意]为查询是对列的引用、简化连接条件的书写，可以使用别名。在from子句中，先写出使用的别名，用空格隔开，然后给出定义的别名。练习：检索“林斌”同学的班级信息（班级编号、班级名称）检索“林斌”同学的选修课信息（课程编号、课程名称、上课时间）查询每个系所开课程的门数，显示系部名称、开课门数练习（答案）(可用表别名)selectclass.classno,classnamefromclass,studentwhereclass.classno=student.classnoandstudent.stuname='林斌'selectcourse.couno,couname,schooltimefromcourse,student,stucouwherestudent.stuno=stucou.stunoandstucou.couno=course.counoandstudent.stuname='林斌'selectdepartname,count(couname)as'开课门数'fromcourse,departmentwherecourse.departno=department.departnogroupbydepartname复习多表查询注意：连接条件、查询条件如果列名在多个表中同名，为了避免列名不明确“course.couno”表与表的连接类型有哪些？内连接：只包含满足连接条件的数据行；外连接：是连接运算的扩展，用它可以处理查询时的信息缺失。包括做外连接、右外连接和全连接三种；交叉连接：是一种很少使用的连接，交叉连接的结果就是笛卡儿积。如：SELECT*FROMDepartment,Class上面介绍的都是内连接。以下是外连接：主要解决信息丢失的问题为了帮助理解外连接，在stucou中增加一行数据学号课程编号志愿号状态020000600205报名【问题】查询开设的所有选修课的信息和所有学生选修课程的情况，要求显示学生学号、课程编号、课程名称自然连接会丢失信息：Course表的‘019’课程、StuCou表的‘020’课程左外连接右外连接、全连接外连接左外连接：通过左向外连接引用左表的所有行，如果左表的某行在右表中没有匹配行，则将右表返回空值。左外连接：From左表名leftjoin右表名on连接条件右外连接：From左表名rightjoin右表名on连接条件全连接：From左表名fulljoin右表名on连接条件练习：使用左外连接检索学生报名信息（课程号、