第11章高级检索技术Word文档下载推荐.docx

1、统计一列中值的个数SUM （DISTINCT|ALL计算一列数值型值的总和AVG （DISTINCT|ALL计算一列数值型值的平均值MAX （DISTINCT|ALL求一列值的最大值MIN （DISTINCT|ALL1990注意：（1）GROUP BY 子句中，不支持列的别名统计每个年龄的学生人数select datediff（yy,birthdate,getdate（） 年龄,count（*）group by 年龄消息207，级别16，状态1，第4 行列名年龄 无效。应改为：group by datediff（yy,birthdate,getdate（）（2）GROUP BY子句中，不支持使

2、用了统计函数的列select count（cno）,min（socre）,max（score）group by count（cno）消息144，级别15，状态1，第1 行在用于GROUP BY 子句分组依据列表的表达式中，不能使用聚合或子查询。（3）SELECT指定的字段如果该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中,则会出现语法错误。select sno 学号,cno 课程号,sum（成绩）group by sno消息8120，级别16，状态1，第2 行选择列表中的列o 无效，因为该列没有包含在聚合函数或GROUP BY 子句中。使用HAVING子句可以对查询或者统计后的

3、结果进行进一步的筛选。【格式】GROUP BY 分组列Having 条件查询平均分在80分以上的学生的学号、平均分、最高分和最低分。select sno,avg（score）,min（score）,max（score）having avg（score）80按学号统计至少选修了2门课程的学生的选修课程门数及平均成绩。select sno,count（*）,avg（score）having count（*）=2WHERE和HAVING的比较HAVING 子句对 GROUP BY 子句设置条件的方式与 WHERE 子句和 SELECT 语句交互的方式类似。（1）WHERE 子句搜索条件在进行分组操作

4、之前应用；而 HAVING 搜索条件在进行分组操作之后应用。（2）HAVING 语法与 WHERE 语法类似，但 HAVING 可以包含聚合函数。HAVING 子句可以引用选择列表中出现的任意项。All的使用统计成绩大于的学生的学号，平均分select sno,avg（score）where scoregroup by all sno比较2者结果集区别。ROLLUP和CUBE关键字在GROUP BY子句中，可以使用ROLLUP或CUBE关键字获得附加的分组数据，这些附加的分组数据是通过各组之间的组合得到的。使用ROLLUP关键字可以得到各组的单项组合，而CUBE关键字可以得到各组之间的任意组合

5、。在结果集中，通过组组合起来的组名称是NULL，可以使用GROUPING函数来判断该组是否为经过组合得到的。实际上，使用CUBE关键字可以生成多维数据。create table items（itemname varchar（20）,color char（2）,num intinsert into itemsselect 桌子,红,20 union all椅子,30 union all蓝,111 union all,222统计每种物品的数量select itemname,color,sum（num）from itemsgroup by itemname,colorwith rollupwith

6、cubeCOMPUTE子句中的聚合COMPUTE子句使用聚合函数计算聚合值，并且可以依然保持原有的明细值，新的聚合值作为特殊的列出现。COMPUTE子句有两种形式，一种形式是不带BY子句，另一种形式是带BY子句。COMPUTE子句中如果没有包含BY子句，表示对所有的明细值计算聚合值；如果包含了BY子句，则表示按照BY子句的要求对明细值分组，然后给出每一组的聚合值。【例】：统计所有学生的总成绩和平均成绩select * from scoreorder by snocompute sum（score）,avg（score）compute sum（score）compute avg（score）（1

7、）聚合列必须和选择列对应select sno,cno from score -择列中无成绩列compute sum（score） 出现错误（2）可以没有order by 子句统计每个学生的总成绩和平均成绩by sno比较：select sno,sum（score）,avg（score）（1） COMPUTE带BY子句时，必须配合ORDER BY排序子句使用，且紧跟ORDER BY之后。（2） BY后的列名是要分组的字段，可以不在SELECT指定的字段中，但必须包含在ORDER BY子句中，而且必须是第一顺序。（3）COMPUTE子句不能与INTO子句或GROUP BY子句同时使用。9.3 连接

8、技术实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术，或者说连接技术是指对两个或两个以上表中数据执行乘积运算的技术。在设计表时，为了提高表的设计质量，经常把相关数据分散在不同的表中。但是，在使用数据时，需要把这些数据集中在一个查询语句中。连接技术可以满足这种客观需求。在Microsoft SQL Server 2005系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。下面分别介绍这些连接技术。交叉连接交叉连接也被称为笛卡尔乘积，返回两个表的乘积。在检索结果集中，包含了所连接的两个表中所有行的全部组合。例如，如果对A表和B表执行交叉连接

9、，A表中有5行数据，B表中有12行数据，则结果集中可以有60行数据。交叉连接使用CROSS JOIN关键字来创建。格式一：SELECT 列名1,列名2,.nFROM 表名1 Cross Join 表名2 n 格式二：SELECT列名1,列名2,.nFROM 表名1, 表名2 , n 将表student和class做交叉连接。select *from student,classfrom student cross join class从行和列的角度分析交叉连接的结果集从交叉连接的结果得到什么结论？如何去掉无意义的信息？内连接内连接把两个表中的数据连接生成第三个表，在这第三个表中，仅包含那些满足连

10、接条件的数据行。在内连接中，使用INNER JOIN连接运算符，并且使用ON关键字指定连接条件。内连接是一种常用的连接方式，如果在JOIN关键字前面没有明确指定连接类型，则默认的连接类型是内连接。1、等值连接：在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。查找学生学号，姓名，电话号码，班级名称。select clsname,sno,sname,phonefrom student s join class con s.clsno=c.clsno查找学生的学号,姓名,课程名称,成绩,补考成绩.select sc.sno,sname,cn

11、ame,score,mk_scorefrom student s join score scon s.sno=sc.sno join course con o=o查找授课日期,教师姓名,班级名称,课程名称和评价.select date,tname,clsname,cname,evaluefrom tcc join teacher ton tcc.tno=t.tno join class clson tcc.clsno=cls.clsno join course c【例】查询所有选课学生的班级名称、学号、姓名、课程名称和成绩select clsname,s.sno,sname,cname,sco

12、refrom class cls join student son cls.clsno=s.clsnojoin score sc on s.sno=sc.snojoin course c on o=o【例】查询2010年所有教师的授课信息：授课时间，教师编号，教师姓名，课程名称，授课班级名称。select date,tcc.tno,tname,cname,clsnamefrom tcc join teacher t1on tcc.tno=t1.tnojoin course cjoin class clson tcc.clsno=cls.clsnowhere year（date）=2010【例】

13、查询班级名称、学生姓名、课程名称、成绩、补考成绩、班主任姓名、授课教师select clsname 班级名称,sname 学生姓名,cname 课程名称,score 成绩,mk_score 补考成绩,t1.tname 班主任,t2.tname 授课教师on s.sno=sc.snojoin class cls on cls.clsno=s.clsnojoin teacher t1 on t1.tno=cls.tnojoin tcc on o=o and tcc.clsno=cls.clsnojoin teacher t2 on tcc.tno=t2.tno【例】查找每个班的平均分select

14、clsno,avg（score）from score sc join student son sc.sno=s.sno【例】查找每个班每门课的平均分select clsno,cno,avg（score）group by clsno,cno2、不等连接： 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括、=、和=g.mingrade andsc.scores2.birthdateorder by s2.birthdate查询选修了2门课以上的学生的学号.select distinct sc1.snofrom score sc1 join score sc2on

15、sc1.sno=sc2.snowhere o!=o查询与 张贵祥 年龄相同的学生的姓名.这样可以么?select s2.sname张贵祥s1.birthdate=s2.birthdateyear（s1.birthdate）=year（s2.birthdate）1.自连接虽然使用一个表但有两个拷贝，在逻辑上是两个表而且字段完全相同，因此字段列表中字段名必须加上其中一个表的别名做前缀。2.使用自连接会产生许多重复行，一般加关键字DISTINCT过滤掉重复行。外连接内连接是保证两个表中所有的行都要满足连接条件，但是外连接则不然。在外连接中，不仅仅是那些满足条件的数据，某些不满足条件的数据也会显示在结

16、果集中。也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。在Microsoft SQL Server 2005系统中，可以使用3种外连接关键字，即LEFT OUTER JOIN、RIGHT OUTER JOIN和FULL OUTER JOIN。左向外联接结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。格式：SELECT 列名列表FROM 表名1 left outer join 表名2ON 表名1.列名=表名2 .列名【例】显示所有学生的选课情况向学生

17、表中插入2行学生信息.insert into student values（08100003张福之371212199001011212,男1990-1-1山东滕州龙阳123123fuzi,1231323,搓麻将r011008100004吕录3722121992010112121992-1-1山东兖州lvlu,1223453,select s.sno,sname,cno,scorefrom student s left join score sc看到left join 结果集同 inner join 的区别。右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。FROM 表名1 right outer join 表名2 ON 表名1.列名=表名2 .列名右外连接与左外连接只是表的顺序不一样，如果把左外连接中表的顺序变一下，再使用右外连接，其结果是相同的。显示所有课程的选课情况insert into course values（6,数据结构与算法,70,35,技能课,null）（7,心理学,50,0,学历课select o,cname,scorefrom score sc right join course c等价于:select o,cna