数据库管理与开发项目教程---教案--06项目四：使用T-SQL查询表数据-任务2分类汇总.docx

项目四使用T-SQL查询表数据 任务1简单查询 任务2分类汇总 实训四使用SELECT语句进行基本查询 任务3连接查询 任务4子查询 实训五使用多表查询和复杂查it 实训六分析创建查询sale数据库复习：1、T-SQL语言由哪几部分构成？2、通常情况下，用户登录SQL Server时，被自动连接到哪些数据库上？如何将要使用的数 据库切合为当前数据库？3、写出SELECT语句的书写格式。简述每个子句后面应该填写什么内容？Select DISTINCT选择列表Into新的表From 表或视图Where检索的条件Group by字段名1 Having内部函数表达式Order by 字段名 2 ASC/DESC 4、写出WHERE子句中判读20到25之间的两种写法，找姓刘的人和第二个字不是宝的人两种 写法？任务2分类汇总一、函数的使用常用统计函数表4-3聚合函数下面详细介绍这五个函数的使用。函数名功 能SUM。返回表达式中所有值的和AVG()返回表达式中所有值的平均值MAX()求最大值MIN()求最小值COUNT()用于统计组中满足条件的行数或总行数COUNT(*)返回找到的行数SUM和AVG分别用于求表达式中所有值项的总和与平均值。 MAX和MIN分别用于求表达式中所有值项的最大值与最小值。语法格式： 函数(【ALL | DISTINCT 】expression) 说明:expression可以是常量、列、函数或表达式,其数据类型只能是：int, smallint, tinyint, bigint, decimal, numeric, float, real, money, smallmoneyo ALL 表示对所有值进行运算， DISTINCT表示对去除重复值后的值进行运算，默认为ALL。SUM/AVG忽略NULL值。【例1】求平均报名意愿。select AVG(WillOrder)from StuCou注意：使用聚合函数作为SELECT的选择列时，若不为其指定列标题，则系统将对该列输出 标题“(无列名)”。【例2】求总报名意愿。select sum(WillOrder)报名人数from StuCou与统计函数一起使用WHERE子句【例3】求选择001课程的人数。use Xkgoselect COUNT(*) from Stucou where couno=001【例4】求报名人数最多是多少。select max(willnum)from course【例5】求报名人数最多的课程名。use Xkg。select top 1 couname,willnumfrom courseorder by willnum desc与统计函数一起使用DISTINCT关键字【例6】求系部总数。select COUNT(distinct departno) from class二、查询结果排序【例7】求报名信息，并按人数升序排列。select couno,couname,willnumfrom Courseorder by willnum【例8】求报名信息，将报名人数提高10%,并按调整后成绩升序排列。use XkGoselect couno,couname,willnum+willnum: 0.1 人数from Course order by 1 人数，三、计算与汇总计算与汇总是生成合计作为附加的汇总行出现在结果集的最后，使用COMPUTE BY子句 实现。语法格式：COMPUTE (聚合函数名(expression) BY expression【，n】 说明：聚合函数见表6-3, expression是列名，COMPUTE将产生额外的汇总行。【例9】查找课程表中“人文”类课程信息，并计算报名人数。USE XkGOSELECT *FROM CourseWHERE Kind=人文，COMPUTE sum(WillNum)GO【例10】按类别显示课程信息，并计算各类课程报名人数。USE XkGOSELECT *FROM Courseorder by kindCOMPUTE sum(WillNum) by KindGO注意：(1) COMPUTE或COMPUTEBY子句中的表达式，必须出现在选择列表中，并且必须将其 指定为与选择列表中的某个表达式完全一样，不能使用在选择列表中指定的列的别名。(2) 在COMPUTE或COMPUTEBY子句中，不能指定为ntext、text、image数据类型。(3) 如果使用COMPUTE BY,则必须也使用ORDER BY子句。表达式必须与在ORDER BY 后列出的子句相同或是其子集，并且必须按相同的序列。例如，如果ORDER BY子句是： ORDER BY a,b,c ,则COMPUTE子句可以是下面的任意一个(或全部)：COMPUTE BY a,b,cCOMPUTE BY a,bCOMPUTE BY a(4) 在SELECT INTO语句中不能使用COMPUTEo因此，任何由COMPUTE生成的计算 结果不出现在用SELECT INTO语句创建的新表内。四、分组分组是按照某一列数据的值或某个列组合的值将查询出的行分成若干组，每组在指定列或列 组合上具有相同的值。分组可通过使用GROUP BY子句来实现。语法格式：GROUP BY group_by_expression说明：group_by_expression是用于分组的表达式，其中通常包含字段名。SELECT子句的列 表中只能包含在GROUP BY中指出的列或在聚合函数中指定的列。1.简单分组【例11】求各班级人数。USE XkGOSELECT classno 班级,count(stuno)人数FROM studentgroup by classnoGO【例12】求各类别课程数。USE XkGOSELECT Kind,count(couno)人数FROM coursegroup by KindGO【例13】求各类别各位老师的课程数。USE XkGOSELECT Kind,teacher,count(couno)课程数FROM coursegroup by Kind,teacherGO注意：(1) 在SELECT子句包含聚合函数时，GROUP BY计算每组汇总值。使用GROUP BY子句 时，在SELECT子句中出现的列名，必须出现在GROUP BY子句的后面，否则，执行SQL 语句会出现如下错误：“选择列表中的列course.Teacher,无效，因为该列没有包含在聚合函数或GROUP BY子句 中。”(2) 使用GROUP BY子句分组只能显示分组的汇总结果，不能显示分组的详细信息。如果 使用COMPUTE BY子句既能显示分组汇总结果，又能显示详细的分组信息。2 .使用HAVING筛选结果使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数 据进一步筛选。语法格式：【HAVING 说明：search_condition为查询条件，与WHERE子句的查询条件类似，并且可以使用聚合函 数。【例14查找“人文”类课程的报名人数。USE XkGOSELECT Kind,sum(willnum)人数FROM coursegroup by kind having Kind=人文，GO 或者 USE XkGOSELECT Kind,sum(willnum)人数FROM course where Kind=人文， group by kindGO注意：(1) 在此题中，使用HAVING和WHERE均查询到正确结果。两者的区别在于，前者先按 照课程类型进行分组，再对分组后的那些组判断是否是“人文”类课程；后者则先判断是否 是“人文”类课程，先将非“人文”类课程过滤掉，然后再进行分组，它减少了分组的行数。 故后者优先选择。(2) 可以在SELECT子句中和HAVING子句中使用聚合函数，但是在WHERE子句中不能 使用聚合函数。(3) 在SELECT语句中，当WHERE, GROUP BY与HAVING子句同时被使用时，要注意 它们的作用和执行顺序：WHERE用于筛选由FROM指定的数据对象，即从FROM指定的基 表或视图中检索满足条件的记录;GROUP BY用于对WHERE的筛选结果进行分组;HAVING 则是对使用GROUP BY分组以后的数据进行过滤。【例15】查找报名人数超过100人的课程。USE XkGOSELECT Kind,sum(willnum)人数FROM coursegroup by kindhaving SUM(willnum) 100GO【例16】查询选课人数超过20人，并且是姓李老师名字。USE XkGOSELECT teacher受欢迎李老师FROM coursewhere Teacher like 李group by teacher having SUM(willnum)20GO分析：本查询首先将course表中姓李老师的记录筛选出来，然后按教师名分组，再对每组记 录求和，选出记录和大于20的各组的受欢迎李老师形成结果表。实训二简单查询和分类汇总训练2. 6实训广9