SQL Server教程（第6版）(2008版) 课件 第4章 数据库的查询和视图

第4章

数据库的查询和视图——关系运算关系运算1．选择选择运算的记号为

F(R)。其中，

是选择运算符，下标F是一个条件表达式，R是被操作的表。例如，若要在T表（表）中找出T1<20的行形成一个新表，则运算式为：

F(T)上式中F为T1<20，该选择运算的结果如表。T1T2T3T4T5

T1T2T3T4T51A133M

1A133M2B120N

2B120N3A21212O

3A21212O5D1024P

5D1024P20F14Q

100A328N

关系运算2．投影投影也是单目运算，该运算从表中选出指定的属性值组成一个新表，记为ΠA(R)。其中，A是属性名（即列名）表，R是表名。例如，在T表中对T1、T2和T5投影，运算式为：

T1,T2,T5(T)该运算得到如表所示的新表。T1T2T5T1T2T51A1M5DP2B1N20FQ3A20100A3N关系运算3．连接连接是把两个表中的行按照指定的条件进行拼接而形成新表，记为。其中，R、S是被操作的表，F是条件。例如，若表A和表B分别如表1和表2所示，则如表3所示，其中F为T1=T3。T1T2T1T2T1T21A6F2BT3T4T5T3T4T513M20NT1T2T3T4T51A13M2B20N表1A表表2B表表3关系运算数据库应用中最常用的是“自然连接”。进行自然连接运算要求两个表有共同属性（列），自然连接运算的结果表是在参与操作的两个表的共同属性上进行等值连接后，再去除重复的属性后所得的新表。自然连接运算记为，其中R和S是参与运算的两个表。例如，若表A和B分别如表1和表2所示，则

如表3所示。T1T2T3T1T2T3T1T2T310A1B15A1C220D2C2T1T4T5T6T1T4T5T61100A1D1200A2D11002B2C1510A2C2T1T2T3T4T5T65A1C210A2C220D2C20A2D1表1A表表2B表表3第4章

数据库的查询和视图——数据库的查询数据库的查询当用户登录到SQLServer后，即被指定一个默认数据库，通常是master数据库。使用“USE数据库名”可以选择当前要操作的数据库。例如，要选择xscj为当前数据库，可以使用如下语句。USExscj一旦选择了当前数据库后，若不对操作的数据库对象加以限定，则其后的命令均是针对当前数据库中的表或视图等进行的。下面介绍SELECT语句，它是T-SQL的核心。其语法格式如下。SELECT列名,…[INTO新表] /*（1）*/ FROM表名|视图名 /*（2）*/ [WHERE条件] /*（3）*/ [GROUPBY条件] /*（4）*/ [HAVING 条件] /*（5）*/ [ORDERBY…] /*（6）*/说明：（1）指定查询结果所要输出的列，还可以用INTO指定将查询结果存入新表。（2）指定查询源：表或视图。（3）指定查询条件。（4）指定查询结果分组条件。（5）指定查询结果分组统计条件。（6）指定查询结果排序顺序。01选择输出项选择指定的列替换查询结果中的数据计算列值消除结果集中的重复行限制结果集返回行数聚合函数选择输出项1．选择指定的列SELECT*|列名[AS]别名|表达式[AS]别名，...说明：（1）选择表中的部分列，各列名之间要以逗号分隔，“*”表示所有列。（2）用AS子句可更改查询结果的列标题，当自定义的列标题中含有空格时，必须使用引号将标题括起来。不允许在WHERE子句中使用列别名。【例4.1】分别查询学生表计算机专业所有记录和通信工程专业中学生的学号姓名和总学分。在SSMS窗口主菜单下面的工具栏，单击“新建查询”按钮，系统新建一个文本编辑窗口，在其中输入下列T-SQL语句：USExscj;SELECT*FROMxsbWHERE专业='计算机'; /*(a)*/SELECT学号ASID,姓名ASNAME,总学分AS'Totalcredit’ /*(b)*/ FROMxsb WHERE专业='通信工程';选择输出项单击工具栏“！执行”按钮”，查询结果如图。

选择输出项2．替换查询结果中的数据语句如下：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2……ELSE表达式nEND选择输出项【例4.2】把成绩表百分制成绩分成等级。USExscj;SELECT学号,成绩, CASE WHEN成绩>=90THEN'优秀' WHEN成绩>=80AND成绩<90THEN'良好' WHEN成绩>=70AND成绩<80THEN'中等' WHEN成绩>=60AND成绩<70THEN'及格' ELSE'不及格' ENDAS等级 FROMcjb WHERE课程号='206';查询结果如图。选择输出项3．计算列值在结果中可以输出对包含列在内的表达式计算后的值，即可使用表达式作为输出项。【例4.3】查询2022年入学学生年龄。SELECT学号,姓名,出生日期,year(getdate())-year(出生日期)+1AS年龄 FROMxsb WHEREleft(学号,2)='22'查询结果如图。选择输出项4．消除结果集中的重复行对表查询输出部分列时，可能会出现重复行。可以使用DISTINCT或DISTINCTROW关键字消除结果集中的重复行。【例4.4】查询学生表包含的专业和成绩表已有课程号。语句如下：SELECTDISTINCT专业 FROMxsbSELECTDISTINCT课程号 FROMcjb查询结果如图。

选择输出项5．限制结果集返回行数如果SELECT语句返回的结果集的行数非常多，那么可以使用TOP选项限制其返回的行数。TOP选项的语法格式如下。TOP表达式[PERCENT]指示只能从查询结果集返回指定数目的行或指定百分比数目的行。“表达式”可以是指定数目或百分比数。若带PERCENT关键字，则表示返回结果集的前（表达式值）％行。【例4.5】对xscj数据库的xsb表选择姓名、专业和总学分，返回结果集的前6行。语句如下：SELECTTOP6姓名,专业,总学分 FROMxsb选择输出项6．聚合函数聚合函数常常用于对一组值进行计算，然后返回单个值。聚合函数通常与GROUPBY子句一起使用。如果一个SELECT语句中有一个GROUPBY子句，则这个聚合函数对所有列起作用，如果没有，则SELECT语句只产生一行作为结果。SQLServer所提供的聚合函数列于表中。函

数

名说

明AVG求组中值的平均值BINARY_CHECKSUM返回对表中的行或表达式列表计算的二进制校验值，可用于检测表中行的更改CHECKSUM返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引CHECKSUM_AGG返回组中值的校验值COUNT求组中项数，返回int类型整数COUNT_BIG求组中项数，返回bigint类型整数GROUPING产生一个附加的列GROUPING_ID为聚合列列表中的每一行创建一个值以标识聚合级别MAX求最大值MIN求最小值SUM返回表达式中所有值的和STDEV返回给定表达式中所有值的统计标准偏差STDEVP返回给定表达式中所有值的填充统计标准偏差VAR返回给定表达式中所有值的统计方差VARP返回给定表达式中所有值填充的统计方差选择输出项【例4.6】求选修101号课程的学生人数、最高分、最低分和平均分，并统计学生总数和专业个数。语句如下：SELECT'计算机导论',COUNT(成绩)人数,MAX(成绩)最高分,MIN(成绩)最低分,AVG(成绩)平均分 FROMcjb WHERE课程号='101' /*(a)*/SELECTCOUNT(*)AS'学生总数',COUNT(DISTINCT专业)AS'专业个数' FROMxsb /*(b)*/查询结果如图。

02查询条件表达式比较模式匹配范围比较空值比较EXCEPT和INTERSECT全文索引查询条件1．表达式比较语法格式如下：表达式1比较运算符表达式2说明：（1）“表达式”是除text、ntext和image以外类型的表达式。比较运算符共有9个。（2）当两个表达式的值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）。而当两个表达式的值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。【例4.7】查询xsb表中通信工程专业总学分大于等于42的同学的情况。语句如下：SELECT* FROMxsb WHERE专业='通信工程'AND总学分>=42查询结果如图。查询条件2．模式匹配LIKE谓词用于指定字符串与指定的字符串相匹配，返回逻辑值TRUE或FALSE。LIKE谓词表达式的语法格式如下。表达式[NOT]LIKE模式串[ESCAPE转义符]说明：（1）表达式：一般为字符串表达式，在查询语句中可以是列名。（2）NOTLIKE：使用NOTLIKE与LIKE的作用相反。（3）模式串：可以使用通配符，表列出了LIKE谓词可以使用的通配符及其说明。通

配

符说

明%代表倒数_（下划线）代表单个字符[]指定范围（如[a-f]、[0-9]）或集合（如[abcdef]）中的任何单个字符[^]指定不属于范围（如[^a-f]、[^0-9]）或集合（如[^abcdef]）的任何单个字符查询条件（4）转义符：应为有效的SQLServer字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定转义符。【例4.8】查询xsb表中姓“王”且单名的学生情况。语句如下：SELECT* FROMxsb WHERE姓名LIKE'王_'查询结果如图。查询条件【例4.9】查询xsb表中学号20打头，倒数第1个数在1～5之间的学生学号、姓名及专业。语句如下：SELECT学号,姓名,专业 FROMxsb WHERE学号LIKE'20%_[1-5]'查询结果如图。查询条件3．范围比较（1）当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，其语法格式如下。表达式[NOT]BETWEEN表达式1AND表达式2当不使用NOT时，若“表达式”的值在“表达式1”与“表达式2”之间（包括这两个值），则返回TRUE，否则返回FALSE；当使用NOT时，返回值刚好相反。【例4.10】查询xsb表中不在2002年10～12月出生的学生情况。语句如下：SELECT学号,姓名,专业,出生日期 FROMxsb WHERE出生日期NOTBETWEEN'2002-10-1'AND'2002-12-31'查询结果如图。查询条件（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的语法格式如下。表达式IN(表达式,…)【例4.11】查询xsb表中专业为“软件工程”或“通信工程”的学生情况。语句如下：SELECT* FROMxsb WHERE专业IN('软件工程','通信工程')该语句与下列语句等价。SELECT* FROMxsb WHERE专业='软件工程'OR专业='通信工程'查询条件4．空值比较当需要判定一个表达式的值是否为空值时，使用ISNULL关键字，其语法格式如下。表达式IS[NOT]NULL当不使用NOT时，若表达式的值为空值，则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例4.12】查询包括备注内容的学生情况。语句如下：SELECT姓名,备注 FROMxsb WHERE备注ISNOTNULL查询结果如图。查询条件5.EXCEPT和INTERSECTEXCEPT和INTERSECT用于比较两个查询的结果，返回非重复值。语法格式如下。查询EXCEPT|INTERSECT查询说明：（1）使用EXCEPT和INTERSECT比较两个查询的规则和UNION语句一样。（2）EXCEPT从左边的查询中返回右边查询没有找到的所有非重复值。INTERSECT返回INTERSECT左右两边查询都返回的所有非重复值。（3）EXCEPT或INTERSECT返回的结果集的列名与左侧的查询返回的列名相同。（4）ORDERBY子句中的列名或别名必须引用左侧查询返回的列名。查询条件【例4.13】查找计算机专业出生日期不大于2004-1-1的学生信息。语句如下。SELECT*FROMxsbWHERE专业='计算机’ EXCEPT SELECT*FROMxsbWHERE出生日期>'2004-1-1'查询结果如图。查询条件6．全文索引1）CONTAINS谓词CONTAINS谓词用于在表中搜索指定的字符串，可以是精确匹配，也可以是模糊匹配，还可以是加权匹配，这样可以在表中搜索指定的单词、短语或近义词等。新建全文索引步骤如下：（1）启动全文搜索服务这项服务默认是禁用的。在Windows命令行键入“SQLServerManager13.msc”按“Enter键”，打开“SqlServerConfigurationManager（SQLServer配置管理器）”窗口，选择启动全文搜索服务。（2）创建全文目录在“对象资源管理器”面板中展开数据库“xscj”，选择“存储”目录下的“全文目录”，右击，在弹出的快捷菜单中选择“新建全文目录”命令，在打开的“新建全文目录-xscj”窗口中填写全文目录名称，如“fulltext”，单击“确定”按钮。查询条件（3）注册全文索引表在全文目录中注册需要全文索引的表。在“全文目录”目录下右击刚刚新建的全文目录“fulltext”，在弹出的快捷菜单中选择“属性”命令，打开“全文目录属性-fulltext”窗口。选择“表/视图”选项卡，选择需要全文索引的表，如“dbo.xsb”，单击按钮，在“合格列”选项组中会显示能够进行全文索引的列，勾选需要的列（这里勾选“姓名”和“备注”）复选框，单击“确定”按钮。在全文目录中注册了需要全文索引的表后，即可使用CONTAINS谓词对表进行包含式查询。CONTAINS谓词语法格式如下：CONTAINS(列名|*,'包含查询条件')查询条件说明：（1）“列名”表示在指定的列中搜索，可以指定多个列，类型为char、varchar、nchar、nvarchar、text、ntext、image、xml和varbinary(MAX)的列是可进行全文搜索的有效列；“*”表示在所有列中搜索。（2）包含查询条件指定要在列中搜索的文本和匹配条件。【例4.14】使用CONTAINS谓词搜索xsb表中包含字符“计算机”的所有行。语句如下：SELECT* FROMxsb WHERECONTAINS(*,'计算机')查询条件2）FREETEXT谓词与CONTAINS谓词类似，FREETEXT谓词也用于在一个表中搜索单词或短语，并要求表已建立全文索引。FREETEXT的查询精度没有CONTAINS高，并不要求对它们进行严格的模式匹配。FREETEXT对所查询的串也没有写法要求，因此FREETEXT也称为自由式查询。语法格式如下：FREETEXT(列名|列表|*,'搜索字符串')【例4.15】使用FREETEXT谓词搜索xsb表中包含字符“琳”的所有行。语句如下：SELECT* FROMxsb WHEREFREETEXT(*,'琳')03子

查

询IN子查询比较子查询EXISTS子查询其他子句使用子查询子查询1．IN子查询IN子查询用于进行一个指定值是否在子查询结果集中的判断。语法格式如下：表达式[NOT]IN(子查询)说明：（1）当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。（2）IN和NOTIN子查询只能返回一列数据。对于较复杂的查询，可以使用嵌套的子查询。子查询【例4.16】查找未选修离散数学的学生情况。语句如下：SELECT*FROMxsb /*(c)*/ WHERE学号NOTIN ( SELECT学号 /*(b)*/ FROMcjb WHERE课程号IN ( SELECT课程号 /*(a)*/ FROMkcb WHERE课程名='离散数学' ) )子查询2．比较子查询这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，其语法格式如下。表达式比较运算符[ALL|SOME|ANY](子查询)其中，ALL、SOME和ANY说明对比较运算的限制。【例4.17】查找比所有计算机专业的学生年龄都大的学生。语句如下：SELECT* FROMxsb WHERE出生日期<=ALL ( SELECT出生日期 FROMxsb WHERE专业='计算机' )查询结果如图。子查询【例4.18】查找206号课程成绩不低于101号课程最低成绩的学生学号和姓名。语句如下：SELECT学号,姓名 /*(c)*/ FROMxsb WHERE学号IN ( SELECT学号 /*(b)*/ FROMcjb WHERE课程号='206'AND成绩!<ANY ( SELECT成绩 /*(a)*/ FROMcjb WHERE课程号='101' ) )查询结果如图。子查询3．EXISTS子查询EXISTS谓词用于测试子查询的结果是否为空表。语法格式如下：[NOT]EXISTS(子查询)若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。NOTEXISTS返回值与EXISTS刚好相反。【例4.19】查找选修206号课程的学生姓名。语句如下：SELECT学号,姓名

FROMxsb WHEREEXISTS ( SELECT* FROMcjb WHERE学号=xsb.学号AND课程号='206' )子查询【例4.20】查找选修了全部课程的同学的姓名。语句如下：SELECT姓名 FROMxsb WHERENOTEXISTS ( SELECT* FROMkcb WHERENOTEXISTS ( SELECT* FROMcjb WHERE学号=xsb.学号AND课程号=kcb.课程号 ) )子查询4.其他子句使用子查询子查询还可以用在SELECT语句的其他子句中，如输出项、FROM子句。【例4.21】从xsb表中查找所有女学生的学号、姓名及其与“221101”号学生的年龄差距。语句如下：SELECT学号,姓名,YEAR(出生日期)-YEAR( ( SELECT出生日期 FROMxsb WHERE学号='221101' ))AS年龄差 FROMxsb WHERE性别=0查询结果如图。04指定查询对象表或视图名导出表行集函数表值函数行转列表和列转行表指定查询对象1．表或视图名表或视图名指定SELECT语句要查询的表或视图，表和视图可以是一个或多个。在查询对象包含多表时，引用同名列名需要加“表名”作为前缀。如果表用AS指定别名，则只能采用“别名”作为前缀。【例4.22】查找“221101”号学生计算机导论课的成绩。语句如下：SELECT成绩 FROMcjb,kcb WHEREcjb.课程号=kcb.课程号 AND学号='221101'AND课程名='计算机导论'查询结果为80。指定查询对象【例4.23】查找与221101所选修的全部课程相同的同学学号。语句如下：SELECTDISTINCT学号 FROMcjbASCJ1 WHERENOTEXISTS ( SELECT* FROMcjbASCJ2 WHERECJ2.学号='221101'ANDNOTEXISTS ( SELECT* FROMcjbASCJ3 WHERECJ3.学号=CJ1.学号 ANDCJ3.课程号=CJ2.课程号 ) )指定查询对象2．导出表导出表是由子查询中SELECT语句的执行而返回的表，但必须使用AS关键字作为子查询产生的中间表定义一个别名。【例4.24】从xsb表中查找总学分大于45的男同学的姓名和学号。语句如下：SELECT姓名,学号,总学分 FROM ( SELECT姓名,学号,性别,总学分 FROMxsb WHERE总学分>=45 )ASstudent WHERE性别=1查询结果如图。指定查询对象【例4.25】在xsb表中查找2003年1月1日以前出生的学生学号、姓名、专业、出生日期和总学分。语句如下：SELECTnum,name,speciality,birthday,score FROM ( SELECT学号,姓名,出生日期,专业,总学分FROMxsbWHERE出生日期<'20030101' )ASstudent(num,name,birthday,speciality,score)查询结果如图。指定查询对象3．行集函数行集函数通常返回一个表或视图。主要的行集函数有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。另外，OPENROWSET函数还可以用于插入图片文件、文本文件、Word文件、Excel文件等内容。这里以插入图片文件为例，具体的操作步骤如下。（1）建立测试表，语句如下。USEtest1CREATETABLEtab1( id int IDENTITY(1,1), name varchar(50), data varbinary(MAX))指定查询对象（2）使用OPENROWSET函数将图片文件导入数据库表列，语句如下。INSERTINTOtab1(name,data) SELECT'picture',BulkColumn FROMOPENROWSET(Bulk'E:\picture.jpg',SINGLE_BLOB)ASBLOB（3）查询导入数据。若上述脚本执行成功，则可以通过下述查询语句来查询表中插入的数据。SELECT*FROMtab1查询结果如图。指定查询对象4．表值函数所谓表值函数就是返回一个表的用户自定义函数，本书第7章将介绍有关内容。5．行转列表和列转行表1）行转列表语法格式如下：表源PIVOT(

聚合函数名(值列) FOR转换列 IN(列表))AS表别名指定查询对象【例4.26】查找xsb表中2003年1月1日以前出生的学生的姓名和总学分，并列出其属于计算机专业还是通信工程专业的情况，1表示是，0表示否。语句如下：USExscjSELECT姓名,总学分,计算机,通信工程 FROMxsb PIVOT ( COUNT(学号) FOR专业 IN(计算机,通信工程) )ASpvt WHERE出生日期<'2003-01-01'查询结果如图。指定查询对象【例4.27】将kcb表中的开课学期和学分列转换为行输出。语句如下：SELECT课程号,课程名,选项,内容 FROMkcb UNPIVOT (

内容 FOR选项IN(学分,开课学期) )unpvt WHERE课程号>300查询结果如图。05表

连

接WHERE子句中连接谓词FROM子句JOIN连接表连接1．WHERE子句中连接谓词可以在WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。连接谓词中的比较符可以是“<”、“<=”、“=”、“>”、“>=”、“!=”、“<>”、“!<”和“!>”，当比较符为“=”时，就是等值连接。【例4.28】查找xscj数据库中每个学生的情况及选修的课程情况。语句如下：SELECTxsb.*,cjb.* FROMxsb,cjb WHERExsb.学号=cjb.学号结果表将包含xsb表和cjb表的所有列共42个记录，因为cjb表为42条记录。表连接1）自然连接它在目标列中去除相同的列名。【例4.29】通过自然连接进行查询。语句如下：SELECTxsb.*,课程号,成绩 FROMxsb,cjb WHERExsb.学号=cjb.学号显示xsb表所有列和cjb表课程号和成绩列【例4.30】查找选修了206号课程且成绩在80分以上的学生姓名及成绩。语句如下。SELECTcjb.学号,姓名,成绩 FROMxsb,cjb WHERExsb.学号=cjb.学号AND课程号='206'AND成绩>=80查询结果如图。表连接2）多表连接对两个以上的表进行连接，称之为多表连接。【例4.31】查找选修了“计算机导论”课程且成绩在80分以上的学生学号、姓名及成绩。语句如下。SELECTxsb.学号,姓名,成绩 FROMxsb,kcb,cjb WHERExsb.学号=cjb.学号 ANDkcb.课程号=cjb.课程号 AND课程名='计算机导论' AND成绩>=80查询结果如图。表连接2．FROM子句JOIN连接T-SQL扩展了FROM子句以JOIN关键字的表示方式，可以将多个表连接起来。语法格式如下。FROM表源JOIN连接类型表源ON查询条件其中：表源为连接的表；ON用于指定连接条件；JOIN连接类型有3种——内连接、外连接和交叉连接。1）内连接指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。（1）内连接是系统默认的，可以省略INNER关键字。【例4.32】查找xscj数据库每个学生的情况及选修的课程情况。语句如下：SELECT* FROMxsbJOINcjb ONxsb.学号=cjb.学号表连接（2）使用内连接后仍可使用WHERE子句指定条件。【例4.33】查找选修了206号课程且成绩在80分以上的学生姓名及成绩。语句如下：SELECT姓名,成绩 FROMxsbJOINcjb ONxsb.学号=cjb.学号 WHERE课程号='206'AND成绩>=80表连接（3）内连接用于多个表的连接。【例4.34】查找选修了“计算机导论”课程且成绩在80分以上的学生学号、姓名及成绩。语句如下：SELECT xsb.学号,姓名,成绩 FROMxsbJOINcjbJOINkcb ONcjb.课程号=kcb.课程号 ONxsb.学号=cjb.学号 WHERE课程名='计算机导论'AND成绩>=80查询结果如图。表连接（4）可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列均要用别名限定。【例4.35】查找不同课程成绩相同的学生的学号、课程号和成绩。语句如下。SELECTa.学号,a.课程号,b.课程号,a.成绩 FROMcjbaJOINcjbb ONa.成绩=b.成绩ANDa.学号=b.学号ANDa.课程号!=b.课程号表连接2）外连接指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下3种。

左外连接（LEFTOUTERJOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行。

右外连接（RIGHTOUTERJOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。

完全外连接（FULLOUTERJOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。【例4.36】查找所有学生情况及他们选修的课程号，若学生未选修任何课，也要包括其情况。语句如下：SELECTxsb.*,课程号 FROMxsbLEFTOUTERJOINcjb ONxsb.学号=cjb.学号表连接【例4.37】查找课程的选修情况和所有开设的课程名。语句如下：SELECTcjb.*,课程名 FROMcjbRIGHTJOINkcb ONcjb.课程号=kcb.课程号本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩列值均为NULL。显示的后面部分记录如图。表连接3）交叉连接CROSSJOIN表示交叉连接，交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表的行数之积。【例4.38】列出学生所有可能的选课情况。语句如下：SELECT学号,姓名,课程号,课程名 FROMxsbCROSSJOINkcb交叉连接也可以使用ON子句进行条件限定。06查询结果分组查询结果分组GROUPBY子句有ISO标准和非ISO标准两种语法格式可用。这里介绍ISO标准的GROUPBY子句。语法格式如下。GROUPBY

列表达式 /*（a）*/ |ROLLUP(复合元素列表) /*（b）*/ |CUBE(复合元素列表) /*（c）*/ |GROUPINGSETS(分组集合项列表) /*（d）*/说明：（a）列表达式：指定分组的列名及由其列名和运算符构成的表达式。（b）ROLLUP：生成简单的聚合行、小计行或超聚合行，还生成一个总计行，返回的分组数等于“复合元素列表”中的表达式数+1。（c）CUBE：生成简单的聚合行、超聚合行和交叉表格行。CUBE针对“复合元素列表”中表达式的所有排列输出一个分组。（d）GROUPINGSETS：在一个查询中指定数据的多个分组。仅聚合指定组，而不聚合由CUBE或ROLLUP生成的整组聚合。查询结果分组【例4.39】生成一个结果集，包括每个专业的男生人数、女生人数、总人数及学生总人数。语句如下。SELECT专业,性别,COUNT(*)AS'人数' FROMxsb GROUPBYROLLUP(专业,性别)查询结果如图。查询结果分组【例4.40】生成一个结果集，包括每个专业的男生人数、女生人数、男生总数、女生总数和学生总人数。语句如下。SELECT专业,性别,COUNT(*)AS'人数' FROMxsb GROUPBYCUBE(专业,性别)查询结果如图。查询结果分组【例4.41】生成一个结果集，分别根据专业和性别对人数进行聚合。语句如下。SELECT专业,性别,COUNT(*)AS'人数' FROMxsb GROUPBYGROUPINGSETS(专业,性别)查询结果如图。07指定分组筛选条件指定分组筛选条件使用GROUPBY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进一步筛选。语法格式如下。HAVING条件其中，HAVING子句中条件可以使用聚合函数，而WHERE子句中不可以。【例4.42】查找平均成绩在85分以上的学生的学号和平均成绩。语句如下。SELECT学号,AVG(成绩)AS'平均成绩' FROMcjb GROUPBY学号 HAVINGAVG(成绩)>=85查询结果如图。指定分组筛选条件【例4.43】查找选修超过两门课程且成绩都在70分以上的学生的学号。语句如下：SELECT学号 FROMcjb WHERE成绩>=70 GROUPBY学号 HAVINGCOUNT(*)>2查询结果如图。指定分组筛选条件【例4.44】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。语句如下：SELECT学号,AVG(成绩)AS'平均成绩’ FROMcjb WHERE学号IN /*（a）*/ ( SELECT学号 FROMxsb WHERE专业='通信工程' ) GROUPBY学号 /*（b）*/ HAVINGAVG(成绩)>=85 /*（c）*/查询结果如图。08指定输出顺序指定输出顺序使用ORDERBY子句对查询结果进行排序。语法格式如下。ORDERBY排序表达式[ASC|DESC]其中，“排序表达式”是列名和包含列名由运算符构成的表达式；关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。【例4.45】将出生日期2003年后学生先按照专业名拼音排序，相同专业按照出生日期从小到大排序。语句如下：SELECT学号,姓名,专业,出生日期 FROMxsb WHERE出生日期>='2004-1-1' ORDERBY专业DESC,出生日期查询结果如图。指定输出顺序【例4.46】将计算机专业学生的“计算机导论”课程成绩按降序排列。语句如下：SELECTxsb.学号,姓名,成绩 FROMxsb,kcb,cjb WHERExsb.学号=cjb.学号

ANDcjb.课程号=kcb.课程号 AND课程名='计算机导论' AND专业='计算机' ORDERBY成绩DESC查询结果如图。09指定查询结果目的地生成子表复制表结构指定查询结果目的地SELECT查询所得的结果可保存到一个新建的表中。语法格式如下。INTO新表1.生成子表【例4.47】分别生成“计算机”和“通信过程”专业学生子表。语句如下。USExscjSELECT*INTOxsb_jsj FROMxsb WHERE专业='计算机'SELECT*INTOxsb_txgc FROMxsb WHERE专业='通信工程'指定查询结果目的地2.复制表结构【例4.48】在test2数据库中生成xsb空表。语句如下。USExscj SELECT*INTOtest2.dbo.xsb FROMxsbWHERE1=210表

联

合表联合常用于归档数据，如归档月报表形成年报表、归档各部门数据等。另外，UNION还可以与GROUPBY及ORDERBY一起使用，用来对合并所得的结果表进行分组或排序。语法格式如下。查询UNION[ALL]查询UNION[ALL]...[GROUPBY...][ORDERBY...]说明：（1）所有查询的列名、个数、顺序必须相同，数据类型必须相同或者兼容。（2）关键字ALL表示合并的结果中包括所有行，不去除重复行。（3）若不指定INTO子句，结果将合并到第一个表中。表联合【例4.49】查询“计算机”表和“通信工程”表中女同学，按出生日期从小到大排序。语句如下。USExscjSELECT* FROMxsb_jsj WHERE性别=0 UNIONALL SELECT* FROMxsb_txgc WHERE性别=0 ORDERBY出生日期查询结果如图。11公用表表达式（CTE）公用表表达式（CTE）在SELECT语句的最前面可以使用一条WITH子句来指定临时结果集，这种临时命名的结果集也称为公用表表达式（CommonTableExpression，CTE），其相当于一个临时表，只不过它的生命周期在该批处理语句执行完后就结束了。语法格式如下。WITHCTE名(列名,…) AS(CTE查询定义) SELECT…说明：“列名”与“CTE查询定义”返回的列名称和个数相同。若不定义，则就是查询列名称。SELECT语句可以直接查询CTE临时表数据。公用表表达式（CTE）【例4.50】使用CTE从cjb表中查询选了101号课程的学生学号、成绩，并定义新的列名为num、score。再使用SELECT语句从CTE和xsb表中查询姓名为“王林”的学生学号和成绩情况。语句如下：USExscj;WITHcte_stu(num,score) AS(SELECT学号,成绩FROMcjbWHERE课程号='101') SELECTnum,score FROMcte_stu,xsb WHERExsb.姓名='王林'ANDxsb.学号=cte_stu.num查询结果如图。第4章

数据库的查询和视图——视图视图使用视图有下列优点。（1）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将分散在多个表中的数据集中在一起，从而方便用户进行数据查询和处理。（2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且表结构非列名修改也不影响用户对数据库的使用。（3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。（4）便于数据共享。各用户不必都定义和存储自己所需的数据，即可共享数据库的表数据，这样，同样的数据只需存储一次。（5）可以重新组织数据以便输出到其他应用程序中。01创建视图创建视图分区视图界面创建视图创建视图1．创建视图语法格式如下。CREATEVIEW视图名[(列名,…)][WITH视图属性] AS SELECT语句 [WITHCHECKOPTION]说明：（1）(列名,…)：它是视图中包含的列，若使用与源表相同的列名，则不必给出列名表。（2）WITH视图属性：指出视图的属性。（3）SELECT语句：用来创建视图的SELECT语句，但定义视图的用户必须对源表有执行SELECT语句的权限。（4）WITHCHECKOPTION：指出在视图上所进行的修改都要符合SELECT语句所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。（5）创建视图的用户已被数据库所有者授权可以使用CREATEVIEW语句，并且有权操作视图所涉及的表或其他视图。创建视图【例4.51】创建学生的学号、姓名和备注视图xsv。USExscjGOCREATEVIEWxsvWITHENCRYPTIONAS SELECTxsb.学号,姓名,备注 FROMxsb WITHCHECKOPTIONGO说明：（1）命令方式创建视图语句必须是批处理中的第1个命令。（2）视图创建后，在对象资源管理器中，xscj数据库视图下就会显示xsv。创建视图【例4.52】创建计算机学生的学号、姓名、课程号及成绩视图cj_jsjv。语句如下：USExscjGOCREATEVIEWcj_jsjvWITHENCRYPTIONAS SELECTxsb.学号,姓名,课程号,成绩 FROMxsb,cjb WHERExsb.学号=cjb.学号AND专业='计算机'GO创建视图【例4.53】创建计算机专业学生的平均成绩视图cj_jsj_avgv，包括学号（列名为num）和平均成绩（列名为score_avg）。语句如下：CREATEVIEWcj_jsj_avgv(num,score_avg)AS SELECT学号,AVG(成绩) FROMcj_jsjv GROUPBY学号GO创建视图显示cj_jsjv和cj_jsj_avgv视图中的内容，语句如下，查询结果如图。SELECTTOP7学号,姓名,课程号,成绩 FROMcj_jsjvSELECT*FROMcj_jsj_avgv

创建视图2．分区视图分区视图在一台或多台服务器间水平连接一组成员表中的分区数据，使数据看起来就像来自同一个表。SQLServer可区分本地分区视图和分布式分区视图。在本地分区视图中，所有参与的表和视图都位于同一个SQLServer实例上；在分布式分区视图中，至少有一个参与表位于不同的（远程）服务器上。创建视图的语句的格式如下。CREATEVIEW视图名 AS SELECT列表1 FROM源表1 UNIONALL SELECT列表2 FROM源表2 UNIONALL … SELECT列表n FROM源表n创建视图3．界面创建视图在xscj数据库中创建学生成绩大于等于60的视图，包含学号、姓名、专业、总学分、课程名和成绩列。记录顺序先按课程号从小到大，课程号相同按成绩从大到小排列。操作步骤如下。（1）在对象资源管理器xscj下选择“视图”，右击，在弹出的快捷菜单中选择“新建视图”菜单。（2）在弹出的“添加表”对话框“表”选项卡中选择构建视图的基表（例如：cjb、kcb和xsb），单击“添加”按钮，如图。创建视图（3）单击“关闭”按钮关闭该对话框，系统在“查询分析器”中创建一个图形可视化选项卡，表之间相同列名自动建立了关联线。（4）在表中列名前勾选视图使用的列，下面对应表格生成相应的记录，表格栏目是描述对应列在视图中的属性。按照要求勾选列对应输出；选择排序类型+排序顺序；在筛选器中描述条件，视图的整体条件由不会空的筛选器条件通过“与构成”，如果是“或”的关系，需要写在“或…”栏中。如图。创建视图（5）当视图中需要一个与原列名不同的列名，或视图中包含了计算列时，可以在“别名”选项中指定。完成后，单击面板上的“保存”按钮。弹出“保存视图”对话框，在其中输入视图名“cj_kc_xsv1”，并单击“确定”按钮，便完成了视图的创建。（6）展开数据库→xscj→视图下，选择“cj_kc_xsv1”选项，右击，在弹出的快捷菜单中选择“设计”命令，可查看并修改视图，选择“编辑前200行”命令，可查看视图数据内容。如图。02查询视图查询视图【例4.54】查找平均成绩在80分以上的学生的学号和平均成绩。前面已经创建计算机专业学生平均成绩视图cj_jsj_avgv，包括学号（列名为num）和平均成绩（列名为score_avg）。这里对cj_jsj_avgv视图进行查询。SQL语句如下。USExscjSELECT* FROMcj_jsj_avgv WHEREscore_avg>=8003更新视图插入数据修改数据删除数据更新视图创建可更新视图需要注意以下几点。（1）创建视图的SELECT语句中没有聚合函数、TOP、GROUPBY、UNION子句及DISTINCT关键字；不包含从基表列通过计算所得的列；FROM子句中至少要包含一个基表。（2）更新视图语句不能同时影响多个基表，并且在创建视图包含“WITHCHECKOPTION”项时需要符合对应的条件。（3）对于可更新的分区视图，在实现分区视图之前，必须先实现水平分区表。原始表被分成若干个较小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。（4）通过INSTEADOF触发器创建的可更新视图。更新视图1．插入数据使用INSERT语句通过视图向基表插入数据。【例4.55】向cj_jsjv视图中插入“221191,林时”记录。语句如下：USExscjINSERTINTOcj_jsjv (学号,姓名)VALUES('221191','林时')SELECT* /*(a)*/ FROMcj_jsjv WHERE学号='221191'SELECT* /*(b)*/ FROMxsb WHERE学号='221191'查询结果如图。

更新视图说明：（1）视图由xsb表和cjb表通过学号连接构成，视图的学号列对应xsb.学号列，所以插入内容进入xsb表中而没有加入cjb表中。所以视图查询不到插入的记录，而xsb表查询到插入的记录。（2）采用下列语句对视图cj_jsjv的更新操作不能成功：INSERTINTOcj_jsjv (学号,姓名,课程号,成绩)VALUES('221191','林时时',301,80)执行上述语句更新操作会影响xsb和cjb两个基表。更新视图2．修改数据使用UPDATE语句可以通过视图修改基表的数据。【例4.56】通过xsv视图修改学号为221191学生的备注为“从南京工业大学转入”。语句如下：USExscjUPDATExsv SET备注='从南京工业大学转入' WHERE学号='221191'SELECT* FROMxsb WHERE学号='221191'查询结果如图。更新视图3．删除数据使用DELETE语句可以通过视图删除基表的数据。但要注意，对于依赖于多个基表的视图，不能使用DELETE语句。【例4.57】删除xsv视图中学号为221191学生记录。语句如下：DELETEFROMxsv WHERE学号='221191'SELECT* FROMxsb WHERE学号='221191'04修改视图定义修改视图定义语法格式如下。ALTERVIEW视图名[WITH视图属性] AS SELECT语句 [WITHCHECKOPTION]其中，视图属性、SELECT语句、WITHCHECKOPTION等与CREATEVIEW语句中的含义完全相同。05删除视图删除视图删除视图同样也可以通过“对象资源管理器”面板中的图形向导和T-SQL语句两种方式来实现。选择指定数据库下视图项，右击，在弹出的快捷菜单中选择“删除”命令项，弹出“删除”对话框，单击“确定”按钮即可删除指定的视图。删除视图语法格式如下。DROPVIEW视图名,…使用DROPVIEW可删除一个或多个视图，各待删的视图名之间以逗号分隔。第4章

数据库的查询和视图——游标01声明游标SQL-92标准T-SQL扩展游标声明游标1．SQL-92标准语法格式如下。DECLARE游标名CURSOR FOR SELECT语句 [FORREADONLY|UPDATE[OF列名,…]]说明：（1）游标名：它是与某个查询结果集相联系的符号名，要符合SQLServer标识符的命名规则。（2）SELECT语句：由该查询产生与所声明的游标相关联的结果集。该SELECT语句中不能出现COMPUTE、COMPUTEBY、INTO或FORBROWSE关键字。（3）READONLY：所声明的游标为只读的。（4）UPDATE…：指定游标中可以更新的列。若有参数“OF列名，…”，则只能修改给出的这些列；若在UPDATE中未指出列，则可以修改所有列。声明游标【例4.58】定义一个符合SQL-92标准的游标声明。语句如下。DECLARExs_cur1CURSOR FOR SELECT学号,姓名,性别,出生日期,总学分 FROMxsb WHERE专业='计算机' FORREADONLYGO说明：（1）该语句定义的游标与单个表的查询结果集相关联，是只读的，游标只能从头到尾顺序提取数据，相当于下面所介绍的只进游标。（2）游标是一个临时对象，声明后在SSMS的对象资源管理器中并不存在。声明游标2．T-SQL扩展游标语法格式如下。DECLARE游标名CURSOR [LOCAL|GLOBAL] /*（a）*/ [FORWARD_ONLY|SCROLL] /*（b）*/ [STATIC|DYNAMIC|FAST_FORWARD|KEYSET] /*（c）*/ [READ_ONLY|SCROLL_LOCKS|OPTIMISTIC] /*（d）*/ FOR SELECT语句 [FORUPDATE[OF列名,…]] /*（e）*/下面对扩展游标进行说明。（a）游标作用域。（b）游标移动方向。（c）游标类