数据检索主题知识讲座

第四章数据检索本章中我们将讨论怎样使用Transact-SQLSELECT语句从一个或多个表中获取数据方法，然后使用WHERE来限定要返回数据。在获取了所需数据之后，我们还将介绍怎样使用GROUPBY、HAVING、ORDERBY来规范化、组织这些数据。数据检索主题知识讲座第1页第四章数据检索*学习关键点*熟悉SELECT语法格式掌握WHERE、ORDERBY、GROUPBY、HAVING子句使用掌握基本多表查询掌握内连接、外连接、交叉连接和联合查询使用了解自连接使用掌握多行和单值子查询使用了解嵌套子查询使用数据检索主题知识讲座第2页第四章数据检索4.1select子句选择目标列4.2带条件检索4.3从多表中查询数据1.union操作2.子查询4.4select其它语法数据检索主题知识讲座第3页4.1select子句选择目标列语法格式SELECTselect_list[INTOnew_table][FROMtable_source][WHEREsearch_condition][GROUPBYgroup_by_expression][HAVINGsearch_condition][ORDERBYorder_expression[ASC|DESC]][computebycomute_expression]数据检索主题知识讲座第4页4.1select子句选择目标列1.能够以常量为目标列2.“*”表示选择表或视图中全部列3.能够利用“as”或“=”定义列别名4.利用“distinct”消除结果集中重复行5.能够输出经计算列值6.能够使用case表示式查询数据7.能够利用“top”限制结果集返回行8.能够对目标列使用聚集函数数据检索主题知识讲座第5页4.1Select子句选择目标列1.以常量为目标列

比如：select1数据检索主题知识讲座第6页4.1Select子句选择目标列2.“*”表示选择表或视图中全部列，也能够选择一些列

比如：select*from班级信息表数据检索主题知识讲座第7页4.1Select子句选择目标列3.能够利用“as”或“=”定义列别名【例】查询“班级信息表”中入学班级名称和人数，结果中指定标题分别为class_name,class_totalSelectclass_name=班级名称,人数asclass_totalfrom班级信息表where入学年份like‘%%’数据检索主题知识讲座第8页4.1Select子句选择目标列注意：1.当自定义标题中含有空格时，必须用单引号将标题括起来。2.不允许在where子句中使用列别名。数据检索主题知识讲座第9页4.1Select子句选择目标列4.利用“distinct”消除结果集中重复行【例】查询“班级信息表”中入年份和人数Select入年份,人数from班级信息表注意：distinct只写一次即可正确写法Selectdistinct

入年份,人数from班级信息表数据检索主题知识讲座第10页4.1Select子句选择目标列5.能够输出经计算列值【例】查询各班级毕业年份。Select班级名称,毕业年份=year(入年份)+4

from班级信息表符合实际情况吗？数据检索主题知识讲座第11页4.1Select子句选择目标列6.能够使用case表示式查询数据【例】查询各班级毕业年份。Select班级名称,毕业年份=casewhen班级名称like‘___1%’thenyear(入年份)+5elseyear(入年份)+4endfrom班级信息表数据检索主题知识讲座第12页4.1Select子句选择目标列Case表示式格式说明：CASEwhen条件1then表示式1when条件2then表示式2……

else表示式

end数据检索主题知识讲座第13页4.1Select子句选择目标列7.能够利用“top”限制结果集返回行Topexpression[percent]表示只能从查询结果集返回指定第一组行货指定百分比数目标行。Expression能够是指定数目或百分比数目标行。【例】对班级信息表，查询表中前6行数据。selecttop6*from班级信息表数据检索主题知识讲座第14页4.1Select子句选择目标列8.能够对目标列使用聚集函数【例】统计班级信息表中总人数。selectsum(人数)from班级信息表注意：sum(人数)计算时忽略人数为null行。说明：1.惯用聚集函数还有max,min,avg,count等，使用方法同sum。2.能够用distinct去掉重复行统计。数据检索主题知识讲座第15页【课堂练习】对班级信息表（班级名称，入年份，所属系别，专业，人数），完成以下查询：1.统计“信息技术工程学院”班级数目和总人数。2.查询全部系别，但只输出前6个结果。3.查询各班班级名称、专业和人数，对人数按以下规则处理：若人数为空值，则输出“还未招生”；若人数小于30，则输出“微型班”；若人数在30与45之间，则输出“正常班”；若人数在45以上，则输出“超级班”。数据检索主题知识讲座第16页【课堂练习】参考答案1.selectcount(all班级名称),sum(人数)from班级信息表where所属系别='信息技术工程学院‘2.selectdistincttop6所属系别from班级信息表3.select班级名称,专业,班级性质=casewhen人数isnullthen‘还未招生'when人数<30then'微型班'when人数>30and人数<45then'正常班'else'超级班'endfrom班级信息表数据检索主题知识讲座第17页4.2带条件检索1.where子句检索信息时能够经过where子句指定检索条件。惯用查询条件以下表所表示：数据检索主题知识讲座第18页4.2带条件检索查询条件运算符意义比较=,>,<,>=-,<=,!=,<>,!>,!<;NOT+上述运算符比较大小确定范围BETWEENAND,NOTBETWEENAND判断值是否在范围内确定集合IN,NOTIN判断值是否为列表中值字符匹配LIKE,NOTLIKE判断值是否与指定字符通配格式相符空值ISNULL,ISNOTNULL判断值是否为空多重条件AND,OR,NOT用于多重条件判断数据检索主题知识讲座第19页4.2带条件检索说明：（1）比较表示式中，数据类型不能是text、ntext和images类型。（2）null和notnull需要用“is”，而不是“=”。（3）模式匹配运算以下，返回true或false。

[not]like‘<匹配串>’[ESCAPE‘<换码字符>’]%：代表任意长度字符串（长度可为0）字符串。_:代表任意单个字符。[]：表示和方括号中列出任意一个字符相匹配。[^]:表示不在方括号中列出任意一个字符。数据检索主题知识讲座第20页4.2带条件检索比如：a[cf-h]tion－＞action,aftion,agtion,ahtion[0-5]ch－＞0ch,1ch,2ch,3ch,4ch,5chAb[^pl]c－＞Abahc，Abqkcetc.Escape作用范围是紧跟在其后字符。转义字符能够是程序员指定任意单个字符。数据检索主题知识讲座第21页4.2带条件检索【练习】对班级信息表（班级名称，入学年份，所属系别，专业，人数）完成以下查询：1.查询9月份以后入学班级信息。2.查询班级名称中包含下划线班级名称和专业。3.查询所属系别尚不确定班级名称。4.查询班级名称中倒数第2个字符为1，且倒数第1个字符在1~5之间班级信息。数据检索主题知识讲座第22页4.2带条件检索1.select*from班级信息表where入年份>='-9-30'2.

select*from班级信息表where班级名称like'%1_%'escape'1'3.

select班级数目=count(*)from班级信息表where所属系别isnull4.select*from班级信息表where班级名称like‘%1[1-5]’数据检索主题知识讲座第23页4.2带条件检索2.group…having子句

GROUPBY[ALL]列名[，列名]…[WITH{CUBE|ROLLUP}][HAVING

筛选条件表示式]having子句也能指定查询条件，使用与where子句类似。使用WITHCUBE会对GROUPBY所列出全部分组字段进行汇总运算

。使用WITHROLLUP会对GROUPBY所列出第一个分组字段进行汇总运算

。数据检索主题知识讲座第24页4.2带条件检索使用GROUPBY

子句时，应注意SQLServer

将为每一组计算一个汇总值，并把汇总值保留在一个字段中。对于指定一组，SQLServer

只生成一条统计，不返回详细信息。SQLServer

只对满足WHERE

子句统计进行分组和汇总。不要对可能包含空值字段使用GROUPBY

子句，因为空值也将被看成一组，而不论统计是否满足WHERE

子句条件。数据检索主题知识讲座第25页4.2带条件检索【例1】查询总人数多于200人学院名称和人数。Select所属系别，总人数=sum(人数)

from班级信息表groupby所属系别havingsum(人数)>200【例2】查询1996年以后入学总人数多于200人学院名称和人数。Select所属系别，总人数=sum(人数)

from班级信息表where入年份>’1996-12-31’

groupby所属系别havingsum(人数)>200数据检索主题知识讲座第26页4.2带条件检索【例3】统计每个学院每年入学总人数超出40人入年份、所属系别和总人数。Select入年份,所属系别,总人数=sum(人数)from班级信息表groupby入年份,所属系别

withcubeHavingSum(人数)>40数据检索主题知识讲座第27页4.2带条件检索Select入年份,所属系别,总人数=sum(人数)from班级信息表groupby入年份,所属系别

withrollupHavingSum(人数)>40注：NULL值表示两属性全部值，即全部学生和。数据检索主题知识讲座第28页4.2带条件检索3.compute及computeby子句COMPUTE子句对查询结果集中全部统计进行汇总统计，并显示全部参加汇总统计详细信息。当与by一起使用时，COMPUTE子句在结果集内生成控制中止和小计。可在同一查询内指定COMPUTE

BY和COMPUTE。使用语法格式为：

COMPUTE

聚合函数（列名）[BY

列名]计算依据列表中全部表示式也必须同时出现在排序依据列表中。数据检索主题知识讲座第29页4.2带条件检索【例】查询班级名称为“计**”班级并按入年份分别计算它们总人数，最终计算出全部符合查询条件班级总人数。Select班级名称,入年份,人数From班级信息表Where班级名称like‘计%’orderby入年份Computesum(人数)by入年份Computesum(人数)数据检索主题知识讲座第30页4.2带条件检索数据检索主题知识讲座第31页4.2带条件检索COMPUTEBY子句使得用同一个SELECT语句既查看明细行，又查看汇总行。能够计算子组汇总值，也能够计算整个结果集汇总值。COMPUTE所生成汇总值在查询结果中显示为单独结果集。数据检索主题知识讲座第32页4.2带条件检索4.Groupby与computeby比较GROUPBY生成单个结果集。每个组都有一个只包含分组依据列和显示该组子聚合聚合函数行。选择列表只能包含分组依据列和聚合函数。COMPUTE生成多个结果集。一个结果集包含每个组明细行，其中包含选择列表中表示式。另一个结果集包含组子聚合，或SELECT语句总聚合。选择列表可包含除分组依据列或聚合函数之外其它表示式。聚合函数在COMPUTE子句中指定，而不是在选择列表中指定。数据检索主题知识讲座第33页4.2带条件检索Select班级名称,入年份,人数From班级信息表Where班级名称like'计%'orderby入年份Computesum(人数)by入年份Select入年份,sum(人数)From班级信息表Where班级名称like'计%’groupby入年份orderby入年份数据检索主题知识讲座第34页数据检索主题知识讲座第35页4.2带条件检索注意：1.Orderby与groupby子句连用放在最终，也能够不用。2.Orderby与compute

by子句连用放在前面，且必须使用。3.聚集函数与groupby连用放在select子句中；4.聚集函数与computeby连用放在compute子句中。数据检索主题知识讲座第36页【课堂练习】1查询每个学院班级总数。2按学院和入年份分组，统计总人数。3汇总每年入学人数超出100专业，并按入学人数升序排序4汇总每个学院总人数以及全部学校总人数。5汇总每个专业总人数以及按班级和入年份人数小计6返回计算机专业班级名称和入年份7按专业分组返回每个班级信息以及各班级人数总和对班级信息表（班级名称，入年份，所属系别，专业，人数）完成以下查询：数据检索主题知识讲座第37页1.select所属系别,总班数=count(*)from班级信息表groupby所属系别2.Select入年份,所属系别,总人数=sum(人数)from班级信息表groupby所属系别,入年份withcube3.数据检索主题知识讲座第38页4.3从多个表中查询数据在实际查询应用中，用户所需要数据并不全部都在一个表或视图中，而在多个表中，这时就要使用多表查询。多表查询用多个表中数据来组合，再从中获取出所需要数据信息。多表查询实际上是经过各个表之间共同列相关性来查询数据，是数据库查询最主要特征。数据检索主题知识讲座第39页4.3从多个表中查询数据1.连接查询2.嵌套查询3.union及其它集合操作数据检索主题知识讲座第40页4.3从多个表中查询数据1.连接查询（1）使用连接谓词（2）使用join关键字数据检索主题知识讲座第41页4.3从多个表中查询数据（1）使用连接谓词在where子句中使用比较运算符给出连接条件对表进行连接，称为连接谓词表示形式。比较运算符分为<,<=,>,>=,<>,=,!<,!>,!=。当比较符为“=”时就是等值连接。若在结果中去掉同名属性就是自然连接。数据检索主题知识讲座第42页4.3从多个表中查询数据【例】查询计0602班学生基本情况。Selecta.*from学生信息表a，班级信息表b

wherea.所在班级=b.班级名称and班级名称=‘计0602’注意：一旦指定了表别名，在整个SELECE中都必语句中须使用别名来代替表名。数据检索主题知识讲座第43页4.3从多个表中查询数据当一个表与其自已进行连接操作时，称为表本身连接。要查询内容均在同一表中，能够将表分别取两个别名，一个是X，一个是Y。将X，Y中满足查询条件行连接起来。这实际上是同一表本身连接。【例】查询大于“王楠”学号学生基本信息。SelectX.*from学生信息表X,学生信息表YwhereX.学号>Y.学号andY.姓名=‘王楠’数据检索主题知识讲座第44页4.3从多个表中查询数据(2)使用join关键字内连接(INNERJOIN)左连接(LEFTOUTERJOIN)右连接(RIGHTOUTERJOIN)全连接(FULLJION)SELECT<选择列表>FROM<左侧源表>连接操作符<右侧源表>ON<连接条件>WHERE<条件表示式>数据检索主题知识讲座第45页4.3从多个表中查询数据外连接是连接运算扩展。有三种：左外连接，右外连接和全外连接。左外连接取出左侧关系中全部与右侧关系中任一元组都不匹配元组，用空值null充填全部来自右侧关系属性，组成新元组，将其加入到自然连接结果中。右外连接将上述描述中左改为右即可。全外连接同时完成左外连接和右外连接操作。数据检索主题知识讲座第46页4.3从多个表中查询数据A)查询有班级学生姓名和班级信息B)查询全部学生姓名和班级信息C)查询全部班级信息及对应学生姓名D)查询全部学生信息和全部班级信息数据检索主题知识讲座第47页4.3从多个表中查询数据A)selecta.*,b.*from学生信息表ajoin

班级信息表bona.所在班级=b.班级名称数据检索主题知识讲座第48页4.3从多个表中查询数据B)selecta.*,b.*from学生信息表aleftjoin

班级信息表bona.所在班级=b.班级名称数据检索主题知识讲座第49页4.3从多个表中查询数据C)selecta.*,b.*from学生信息表arightjoin班级信息表bona.所在班级=b.班级名称数据检索主题知识讲座第50页4.3从多个表中查询数据D)selecta.*,b.*from学生信息表afulljoin班级信息表bona.所在班级=b.班级名称数据检索主题知识讲座第51页4.3从多个表中查询数据2.嵌套查询

在WHERE子句中包含一个形如SELECT-FROM-WHERE查询块，此查询块称为子查询或嵌套查询，包含子查询语句称为父查询或外部查询。(1)当子查询返回值只有一个时，能够使用比较运算符（=,>,<,>=,<=,!=）将父查询和子查询连接起来。(2)假如子查询返回值不止一个，而是一个集合时，则不能直接使用比较运算符，能够在比较运算符和子查询之间插入ANY或ALL，也能够用in或notin。数据检索主题知识讲座第52页4.3从多个表中查询数据2.嵌套查询1.使用比较运算符使用ANY.使用IN.使用ALL.使用EXISTS数据检索主题知识讲座第53页4.3从多个表中查询数据2.嵌套查询【例1】查询与自0801班人数相同其它班级信息。Select*from班级信息表where人数=(select人数from班级信息表where班级名称=‘自0801’【例2】查询计算机科学与技术专业班级名称和人数。Select班级名称,人数from班级信息表where班级名称=any（select班级名称from班级信息表wehre专业=‘计算机科学与技术’)数据检索主题知识讲座第54页4.3从多个表中查询数据【例3】查询年纪大于全部女同年纪男同学姓名。Select姓名from学生信息表where年纪>all(select年纪from学生信息表where性别=0)【例4】查询“信息技术工程学院”学生学号和姓名。Select学号,姓名from学生信息表a

whereexists(select*from班级信息表bwherea.所在班级=b.班级名称)数据检索主题知识讲座第55页4.3从多个表中查询数据【例5】查询选修了全部已开课程学生姓名。Select姓名from学生信息表awherenotexists(select*from开课信息表b

whereNotexists(select*from成绩表c

whereC.开课编号=b.开课编号andc.学号=a.学号))数据检索主题知识讲座第56页4.3从多个表中查询数据3.union(并)及其它集合操作经过union操作能够把两个或两个以上查询结果合并到一个结果集中。Union是一个二元运算，对包含两个以上查询表示式必须增加()来指定求值次序。不然按照从左到右次序运算。Union子句能够出现在insert-select命令中，但不能出现在createview命令或子查询中。与union类似操作还有intersect(交)和except(差)。数据检索主题知识讲座第57页4.3从多个表中查询数据【例】增加“教师信息表”，包含属性：教师工号，姓名，年纪，性别，职称，所属系别。查询全部教师和学生姓名。查询与学生重名教师姓名。查询没在学生姓名集中没出现教师姓名。数据检索主题知识讲座第58页4.3从多个表中查询数据Select姓名from学生信息表Union

Select姓名from教师信息表Select姓名from学生信息表intersect

Select姓名from教师信息表Select姓名from学生信息表exceptSelect姓名from教师信息表数据检索主题知识讲座第59页4.3从多个表中查询数据注意：使用union这类操作符，必须确保两个select子句中目标列个数和类型完全一致。数据检索主题知识讲座第60页4.4select其它语法1.into子句2.from子句3.SETROWCOUNTn4.控制结果显示宽度数据检索主题知识讲座第61页4.4select其它语法1.into子句使用into子句能够将select查询所得到结果保留到一个新建表中。Intonew_tableInto子句select语句执行后所创建表结构由select所选择列决定。新创建表中元组由select查询结果决定。数据检索主题知识讲座第62页4.4select其它语法【例】由学生信息表创建student表，包含学号和姓名。Select学号sno,姓名sname

intostudentfrom学生信息表数据检索主题知识讲座第63页4.4select其它语法2.from子句From子句中也可以使用子查询。必须使用as关键字为子查询产生中间表定义一个别名。子查询用于from子句时，也可认为列指定别名，但必须全部指出。数据检索主题知识讲座第64页4.4select其它语法【例1】查询总人数大于35人信息技术工程学院班级名称和专业。Select班级名称,专业from(select*from