第11章高级检索技术.docx
《第11章高级检索技术.docx》由会员分享，可在线阅读，更多相关《第11章高级检索技术.docx（46页珍藏版）》请在冰豆网上搜索。
第11章高级检索技术
第11章高级检索技术
教学目标
●理解和掌握聚合技术
●理解和掌握分组技术，特别是ROLLUP和CUBE
●理解和掌握连接技术
●理解和掌握子查询技术
●理解和掌握集合运算技术
●理解和掌握CTE技术
教学难点和重点
●理解和掌握聚合技术，这是数据统计的基础。
理解和掌握分组技术，特别是ROLLUP和CUBE，这是提高数据统计效率的关键和难点。
理解和掌握连接技术，这是把多个表中数据连接在一个结果集中的基本手段。
理解和掌握子查询技术，这也是检索表中数据的最基本和最重要的手段之一。
理解和掌握集合运算技术，这是综合运用多个查询语句的基础。
理解和掌握CTE技术，这是实现递归、完成复杂操作的有效方式。
教学过程
●聚合技术
●分组技术
●连接技术
●子查询技术
●集合运算技术
●公用表表达式
●上机实验
11.1聚合技术
●聚合技术是指对一组数据进行聚合运算得到聚合值的过程。
在聚合运算中主要是使用聚合函数。
在MicrosoftSQLServer2005系统中，一般情况下，可以在3个地方使用聚合函数，即SELECT子句、COMPUTE子句和HAVING子句。
本节主要讲述如何在SELECT子句和COMPUTE子句中使用聚合函数，有关HAVING子句使用聚合函数的内容将在下一节介绍。
SELECT子句中的聚合
●在SELECT子句中可以使用聚合函数进行运算，运算结果作为新列出现在结果集中。
在聚合运算的表达式中，可以包括列名、常量以及由算术运算符连接起来的函数。
常用统计函数:
函数名称
函数功能
COUNT（[DISTINCT|ALL]*）
统计记录个数
COUNT（[DISTINCT|ALL]<列名>）
统计一列中值的个数
SUM（[DISTINCT|ALL]<列名>）
计算一列数值型值的总和
AVG（[DISTINCT|ALL]<列名>）
计算一列数值型值的平均值
MAX（[DISTINCT|ALL]<列名>）
求一列值的最大值
MIN（[DISTINCT|ALL]<列名>）
求一列值的最小值
【例】：
统计学号为XXXX的总成绩
selectsum（score）fromscore
wheresno='09100001'
【例】：
计算student表中所有学生的平均年龄
selectavg（year（getdate（））-year（birthdate））
fromstudent
比较上面语句和下面语句的区别：
selectavg（distinctdatediff（yy,birthdate,getdate（）））
fromstudent
【例】：
统计课程XXX的最高成绩，最低成绩和总成绩，并给列起列名。
selectmin（score）最低分,max（score）最高分,sum（score）平均分
fromscore
wherecno=1
【例】：
统计所有教职工的人数
selectcount（*）fromteacher
【例】：
统计软件15班的学生人数
selectcount（*）
fromstudent
whereclsno='r0215'
【例】：
count的不同使用方法
--返回非空的成绩的数据
selectcount（score）
fromscore
--返回非空且不重复的记录的行数
selectcount（DISTINCTscore）
fromscore
--返回所有记录的行数
SELECTCOUNT（*）FROMscore
试分析下列语句是否正确。
11.2分组技术
●聚合函数只能产生一个单一的汇总数据，使用GROUPBY子句，则可以生成分组的汇总数据。
GROUPBY子句把数据组织起来分成组。
一般情况下，可以根据表中的某一列进行分组，通过使用聚合函数，对每一个组可以产生聚合值。
如果希望过滤某些分组，可以使用HAVING子句。
●分组技术是指使用GROUPBY子句完成分组操作的技术。
如果在GROUPBY子句中没有使用CUBE或ROLLUP关键字，表示这种分组技术是普通分组技术。
【公式】
SELECT列名或聚合函数
FROM表名
WHERE条件
普通分组技术
●GROUPBY子句、HAVING子句和聚合函数一起完成对每一个组生成一行和一个汇总值。
●在SELECT子句中的非合计列必须出现在GROUPBY子句中。
●在HAVING子句中的列只返回一个值。
【例】：
统计每个班级的总人数
selectclsno班级编号,count（*）总人数
fromstudent
groupbyclsno
【例】：
统计每门课的平均成绩
selectcno课程编号,avg（score）平均成绩
fromscore
groupbycno
【例】：
统计每个班级男女生人数
selectclsno班级编号,gender性别,count（*）人数
fromstudent
groupbyclsno,gender
【例】：
统计每个班级90后出生的学生人数
selectclsno,count（*）
fromstudent
whereyear（birthdate）>1990
groupbyclsno
注意：
（1）GROUPBY子句中，不支持列的别名
【例】：
统计每个年龄的学生人数
selectdatediff（yy,birthdate,getdate（））年龄,count（*）
fromstudent
groupby年龄
消息207，级别16，状态1，第4行
列名'年龄'无效。
应改为：
selectdatediff（yy,birthdate,getdate（））年龄,count（*）
fromstudent
groupbydatediff（yy,birthdate,getdate（））
（2）GROUPBY子句中，不支持使用了统计函数的列
【例】：
selectcount（cno）,min（socre）,max（score）
fromscore
groupbycount（cno）
消息144，级别15，状态1，第1行
在用于GROUPBY子句分组依据列表的表达式中，不能使用聚合或子查询。
（3）SELECT指定的字段如果该列既不包含在聚合函数中，也不包含在GROUPBY子句中,则会出现语法错误。
【例】：
selectsno学号,cno课程号,sum（成绩）
fromscore
groupbysno
消息8120，级别16，状态1，第2行
选择列表中的列'o'无效，因为该列没有包含在聚合函数或GROUPBY子句中。
使用HAVING子句可以对查询或者统计后的结果进行进一步的筛选。
【格式】
GROUPBY分组列
Having条件
【例】：
查询平均分在80分以上的学生的学号、平均分、最高分和最低分。
selectsno,avg（score）,min（score）,max（score）
fromscore
groupbysno
havingavg（score）>80
【例】：
按学号统计至少选修了2门课程的学生的选修课程门数及平均成绩。
selectsno,count（*）,avg（score）
fromscore
groupbysno
havingcount（*）>=2
WHERE和HAVING的比较
HAVING子句对GROUPBY子句设置条件的方式与WHERE子句和SELECT语句交互的方式类似。
（1）WHERE子句搜索条件在进行分组操作之前应用；而HAVING搜索条件在进行分组操作之后应用。
（2）HAVING语法与WHERE语法类似，但HAVING可以包含聚合函数。
HAVING子句可以引用选择列表中出现的任意项。
All的使用
【例】：
统计成绩大于的学生的学号，平均分
selectsno,avg（score）
fromscore
wherescore>80
groupbysno
selectsno,avg（score）
fromscore
wherescore>80
groupbyallsno
比较2者结果集区别。
ROLLUP和CUBE关键字
●在GROUPBY子句中，可以使用ROLLUP或CUBE关键字获得附加的分组数据，这些附加的分组数据是通过各组之间的组合得到的。
使用ROLLUP关键字可以得到各组的单项组合，而CUBE关键字可以得到各组之间的任意组合。
●在结果集中，通过组组合起来的组名称是NULL，可以使用GROUPING函数来判断该组是否为经过组合得到的。
实际上，使用CUBE关键字可以生成多维数据。
createtableitems
（itemnamevarchar（20）,
colorchar
（2）,
numint
）
insertintoitems
select'桌子','红',20unionall
select'椅子','红',30unionall
select'桌子','蓝',111unionall
select'椅子','蓝',222
【例】：
统计每种物品的数量
selectitemname,color,sum（num）
fromitems
groupbyitemname,color
withrollup
selectitemname,color,sum（num）
fromitems
groupbyitemname,color
withcube
COMPUTE子句中的聚合
●COMPUTE子句使用聚合函数计算聚合值，并且可以依然保持原有的明细值，新的聚合值作为特殊的列出现。
COMPUTE子句有两种形式，一种形式是不带BY子句，另一种形式是带BY子句。
COMPUTE子句中如果没有包含BY子句，表示对所有的明细值计算聚合值；如果包含了BY子句，则表示按照BY子句的要求对明细值分组，然后给出每一组的聚合值。
●【例】：
统计所有学生的总成绩和平均成绩
select*fromscore
orderbysno
computesum（score）,avg（score）
select*fromscore
orderbysno
computesum（score）
computeavg（score）
（1）聚合列必须和选择列对应
selectsno,cnofromscore--择列中无成绩列
orderbysno
computesum（score）–出现错误
computeavg（score）
（2）可以没有orderby子句
【例】：
统计每个学生的总成绩和平均成绩
select*fromscore
orderbysno
computesum（score）,avg（score）
bysno
比较：
selectsno,sum（score）,avg（score）
fromscore
groupbysno
（1）COMPUTE带BY子句时，必须配合ORDERBY排序子句使用，且紧跟ORDERBY之后。
（2）BY后的列名是要分组的字段，可以不在SELECT指定的字段中，但必须包含在ORDERBY子句中，而且必须是第一顺序。
（3）COMPUTE子句不能与INTO子句或GROUPBY子句同时使用。
9.3连接技术
●实现从两个或两个以上表中检索数据且结果集中出现的列来自于两个或两个以上表中的检索操作被称为连接技术，或者说连接技术是指对两个或两个以上表中数据执行乘积运算的技术。
在设计表时，为了提高表的设计质量，经常把相关数据分散在不同的表中。
但是，在使用数据时，需要把这些数据集中在一个查询语句中。
连接技术可以满足这种客观需求。
●在MicrosoftSQLServer2005系统中，这种连接操作又可以细分为交叉连接、内连接、外连接等。
下面分别介绍这些连接技术。
交叉连接
●交叉连接也被称为笛卡尔乘积，返回两个表的乘积。
在检索结果集中，包含了所连接的两个表中所有行的全部组合。
例如，如果对A表和B表执行交叉连接，A表中有5行数据，B表中有12行数据，则结果集中可以有60行数据。
●交叉连接使用CROSSJOIN关键字来创建。
格式一：
SELECT列名1,列名2,..n
FROM表名1
{CrossJoin表名2}[…n]
格式二：
SELECT列名1,列名2,..n
FROM表名1,表名2[,…n]
【例】：
将表student和class做交叉连接。
select*
fromstudent,class
select*
fromstudentcrossjoinclass
Ø从行和列的角度分析交叉连接的结果集
Ø从交叉连接的结果得到什么结论？
Ø如何去掉无意义的信息？
内连接
●内连接把两个表中的数据连接生成第三个表，在这第三个表中，仅包含那些满足连接条件的数据行。
●在内连接中，使用INNERJOIN连接运算符，并且使用ON关键字指定连接条件。
内连接是一种常用的连接方式，如果在JOIN关键字前面没有明确指定连接类型，则默认的连接类型是内连接。
1、等值连接：
在连接条件中使用等于号（=）运算符比较被连接列的列值，其查询结果中列出被连接表中的所有列，包括其中的重复列。
【例】：
查找学生学号，姓名，电话号码，班级名称。
selectclsname,sno,sname,phone
fromstudentsjoinclassc
ons.clsno=c.clsno
【例】：
查找学生的学号,姓名,课程名称,成绩,补考成绩.
selectsc.sno,sname,cname,score,mk_score
fromstudentsjoinscoresc
ons.sno=sc.snojoincoursec
ono=o
【例】：
查找授课日期,教师姓名,班级名称,课程名称和评价.
selectdate,tname,clsname,cname,evalue
fromtccjointeachert
ontcc.tno=t.tnojoinclasscls
ontcc.clsno=cls.clsnojoincoursec
ono=o
【例】查询所有选课学生的
班级名称、学号、姓名、课程名称和成绩
selectclsname,s.sno,sname,cname,score
fromclassclsjoinstudents
oncls.clsno=s.clsno
joinscorescons.sno=sc.sno
joincoursecono=o
【例】查询2010年所有教师的授课信息：
授课时间，教师编号，教师姓名，课程名称，授课班级名称。
selectdate,tcc.tno,tname,cname,clsname
fromtccjointeachert1
ontcc.tno=t1.tno
joincoursec
ono=o
joinclasscls
ontcc.clsno=cls.clsno
whereyear（date）=2010
【例】查询班级名称、学生姓名、课程名称、成绩、补考成绩、班主任姓名、授课教师
selectclsname班级名称,sname学生姓名,
cname课程名称,score成绩,mk_score补考成绩,t1.tname班主任,t2.tname授课教师
fromstudentsjoinscoresc
ons.sno=sc.sno
joincoursecono=o
joinclassclsoncls.clsno=s.clsno
jointeachert1ont1.tno=cls.tno
jointccono=oandtcc.clsno=cls.clsno
jointeachert2ontcc.tno=t2.tno
【例】查找每个班的平均分
selectclsno,avg（score）
fromscorescjoinstudents
onsc.sno=s.sno
groupbyclsno
【例】查找每个班每门课的平均分
selectclsno,cno,avg（score）
fromscorescjoinstudents
onsc.sno=s.sno
groupbyclsno,cno
2、不等连接：
在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。
这些运算符包括>、>=、<=、<、!
>、!
<和<>。
创建表grade
向grade表中添加数据
【例】：
查找学生的学号,姓名,课程名称,成绩,等级.
selectsc.sno,sname,cname,score,gradename
fromscorescjoingradeg
onsc.score>=g.mingradeand
sc.score<=g.maxgrade
joinstudentsons.sno=sc.sno
joincoursecono=o
3、自然连接：
在连接条件中使用等于（=）运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。
在实际中等值连接一般以自然连接的形式出现。
select*fromscoresc
joinstudents
ons.sno=sc.sno
结果中会出现2个sno,如果去掉其中一个就成了自然连接。
selectsno,sname,cno,score
fromscoresc
joinstudents
ons.sno=sc.sno
消息209，级别16，状态1，第1行
列名'sno'不明确。
selectsc.sno,sname,cno,score
fromscoresc
joinstudents
ons.sno=sc.sno
自连接
【例】：
查找教师编号，教师姓名和上级领导姓名
selectt1.tno教师编号,t1.tname,上级领导=t2.tname
fromteachert1jointeachert2
ont1.uptno=t2.tno
【例】：
查找查询比“李宁”年龄大的学生的学号、姓名和出生年份，结果按出生年月升序排列。
selects2.sno学号,s2.sname姓名,
s2.birthdate出生日期
fromstudents1joinstudents2
ons1.sname='李宁'and
s1.birthdate>s2.birthdate
orderbys2.birthdate
【例】：
查询选修了2门课以上的学生的学号.
selectdistinctsc1.sno
fromscoresc1joinscoresc2
onsc1.sno=sc2.sno
whereo!
=o
【例】：
查询与张贵祥年龄相同的学生的姓名.
这样可以么?
selects2.sname
fromstudents1joinstudents2
ons1.sname='张贵祥'and
s1.birthdate=s2.birthdate
selects2.sname
fromstudents1joinstudents2
ons1.sname='张贵祥'and
year（s1.birthdate）=year（s2.birthdate）
1.自连接虽然使用一个表但有两个拷贝，在逻辑上是两个表而且字段完全相同，因此字段列表中字段名必须加上其中一个表的别名做前缀。
2.使用自连接会产生许多重复行，一般加关键字DISTINCT过滤掉重复行。
外连接
●内连接是保证两个表中所有的行都要满足连接条件，但是外连接则不然。
在外连接中，不仅仅是那些满足条件的数据，某些不满足条件的数据也会显示在结果集中。
也就是说，外连接只限制其中一个表的数据行，而不限制另外一个表中的数据。
●在MicrosoftSQLServer2005系统中，可以使用3种外连接关键字，即LEFTOUTERJOIN、RIGHTOUTERJOIN和FULLOUTERJOIN。
左向外联接
结果集包括LEFTOUTER子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。
如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值。
格式：
SELECT列名列表
FROM表名1left[outer]join表名2
ON表名1.列名=表名2.列名
【例】显示所有学生的选课情况
向学生表中插入2行学生信息.
insertintostudentvalues
（'08100003','张福之','371212199001011212',
'男','1990-1-1','山东滕州龙阳','123123','fuzi@',1231323,'搓麻将','r0110'）
insertintostudentvalues
（'08100004','吕录','372212199201011212',
'男','1992-1-1','山东兖州','123123','lvlu@',1223453,'搓麻将','r0110'）
selects.sno,sname,cno,score
fromstudentsleftjoinscoresc
ons.sno=sc.sno
看到leftjoin结果集同innerjoin的区别。
右向外联接是左向外联接的反向联接。
将返回右表的所有行。
如果右表的某行在左表中没有匹配行，则将为左表返回空值。
SELECT列名列表
FROM表名1right[outer]join表名2ON表名1.列名=表名2.列名
注意：
右外连接与左外连接只是表的顺序不一样，如果把左外连接中表的顺序变一下，再使用右外连接，其结果是相同的。
【例】：
显示所有课程的选课情况
insertintocoursevalues
（6,'数据结构与算法',70,35,'技能课',null）
insertintocoursevalues
（7,'心理学',50,0,'学历课',null）
selecto,cname,score
fromscorescrightjoincoursec
ono=o
等价于:
selecto,cna
升级会员 