SQL-06高级查询.doc

练习6 复杂的结构化查询语句 6．1 上机目的 1、掌握SELECT语句的统计函数的作用和使用方法 2、通过练习SELECT语句的GROUP BY和ORDER BY字句的用法，理解其作用，掌握语句的写法。 3、通过练习涉及多张表的连接查询，掌握它的作用和写法 6．2 上机练习预备知识点 6．2．1汇总函数 函数语法说明 功能 SUM（[ALL|DISTINCT]表达式） 返回表达式的所有值的和 AVG（[ALL|DISTINCT]表达式） 返回表达式所有值的平均值 COUNT（[ALL|DISTINCT]表达式） 返回表达式中值的个数 MAX（表达式） 表达式的最大值 MIN（表达式） 表达式的最小值 说明： ①函数中DISTINCT的作用是统计计算的过程中去掉重复值。 ②函数中ALL的作用是统计计算全部的值包括重复值。可省略。 6．2．2 GROUP BY 子句 作用：将记录根据GROUP BY后所跟字段的值分成多个组，进行分组计算。一般情况GROUP BY 子句与汇总函数连用。 格式：GROUP BY（字段，…n） 例14 按照班级把学生信息表的数据分组,并且统计每个班级的人数 分析：Students表中班级人数这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再按各组进行统计计算。 SELECT class,人数= count(*) FROM Students GROUP BY class 注意： ①分组也可以根据多个字段； ②不能对数据类型为ntext,text,image或bit的字段使用GROUP BY 1、HAVING 子句 作用：HAVING子句将对GROUP BY 子句选择出来的结果进行再次筛选，最后输出符合HAVING子句条件的结果。HAVING子句必须与GROUP BY子句连用。 例15 查询平均入学总分在350分以上的班级 分析：Students表中没有平均分这一列，因此需要计算才能得到。因此需要先按class分组，class列中有几个不同的值就要分成几组，再进行统计计算，最后用HAVING子句筛选出AVG(mgrade) =350的记录。 SELECT class,AVG(mgrade) FROM Students GROUP BY class HAVING AVG(mgrade)=350 2、ALL关键字 作用：暂时忽略WHERE子句中的查询条件。如果使用了ALL关键字那么查询结果将包括由GROUP BY子句所产生的所有组，无论这些组中的记录是否符合WHERE子句中的查循条件。但是对于不符合WHERE子句中的查循条件的记录值不进行汇总计算。 例16 Select class,平均成绩=AVG(mgrade) from students where class ’03物流3’ group by ALL class 分析：使用了ALL关键字后，会出现表中所有班级的分组情况，但”03物流3班”的平均成绩为空，不再计算。 3、CUBE关键字 WITH CUBE关键字的主要作用是自动对GROUP BY子句中列出的每个字段都进行分组汇总运算。（注意有CUBE关键字和没有CUBE关键字的区别） 例17 SELECT class,ssex, 平均分=avg(mgrade) From students Group by class,ssex with cube 分析：在该例子的检索结果集不但包含按班级和性别的分组计算结果，而且包含按班级分组后的计算结果和按性别分组计算的结果，最后还包含整张表不分组的计算结果。 说明：正确理解WHERE子句、GROUP BY子句和HAVING子句的作用及其作用顺序。 ①WHERE子句用来筛选 FROM子句中指定的数据源的记录 ②GROUP BY子句将WHERE子句的结果集进行分组 ③HAVING子句将从经过分组后的中间结果集中筛选记录 6．2．3 ORDER BY子句 作用：在SELECT子句用ORDERBY 子句是对查询结果按照一列或多列进行排序[ASC｜DESC]。ASC表示升序排列，DESC表示降序排列。默认情况下为升序排列。 例18 检索学生信息表，并且按照入学总分由高到低输出查询结果 分析：入学总分由高到低排列，就表示检索结果要求按入学总分作降序排列，那么关键字为desc。降序排列不是默认的排列方式，需要特别表示。 SELECT sno,sname,mgrade FROM Students ORDER BY mgrade desc 注意： ①Order by 子句中可以指定多个字段，系统将根据子句中排序字段的顺序对查询结果进行嵌套排序。 ②Order by 子句中不能包含text,im