数据库的查询与视图

最新推荐文章于 2022-11-21 22:49:00 发布

weixin_30851409

最新推荐文章于 2022-11-21 22:49:00 发布

阅读量547

点赞数

文章标签：数据库前端后端 ViewUI

原文链接：http://www.cnblogs.com/javaTest/archive/2012/05/28/2589465.html

版权

第4章数据库的查询和视图

4.1.1 选择列

通过SELECT语句的<select_list>项组成结果表的列。

<select_list>::=

SELECT [ ALL |DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ]

{ * /*选择当前表或视图的所有列*/

| { table_name |view_name | table_alias } .* /*选择指定的表或视图的所有列*/

| { colume_name |expression | IDENTITYCOL | ROWGUIDCOL }

[ [ AS ]column_alias ] /*选择指定的列*/

| column_alias =expression /*选择指定列并更改列标题*/

} [ , … n ]

1. 选择一个表中指定的列

一般情况下，希望包含在结果表中的列表清单在SELECT关键字之后，就像前面用到的那样，当在SELECT关键字后面指定*时，就表示全部列都包含在结果表中。不指定*，可以列出一个表中的某些列，各列名之间要以逗号分隔。

4.1.1 选择列

【例4.1】查询XSCJ数据库的XS表中各个同学的姓名、专业名和总学分。

USE XSCJ

SELECT 姓名,专业名,总学分

FROM XS

4.1.1 选择列

【例4.2】查询XS表中计算机专业同学的学号、姓名和总学分。查询XS表中的所有列。

SELECT 学号，姓名，总学分

FROM XS

WHERE 专业名= ‘计算机’

SELECT *

FROM XS

当在SELECT语句指定列的位置上使用*号时，表示选择表的所有列。

执行后将列出XS表中的所有数据，如图4.2所示：

4.1.1 选择列

2. 修改查询结果中的列标题

当希望查询结果中的某些列或所有列显示时使用自己选择的列标题时，可以在列名之后使用AS子句来更改查询结果的列标题名。其中column_alias是指定的列标题。

【例4.3】查询XS表中计算机系同学的学号、姓名和总学分，结果中各列的标题分别指定为number、name和mark。

SELECT 学号 AS number, 姓名 AS name,总学分 AS mark

FROM XS

WHERE 专业名= ‘计算机’

该语句的执行结果如图4.3所示。

更改查询结果中的列标题也可以使用column_alias=expression的形式。例如：

SELECT number = 学号, name = 姓名, mark = 总学分

FROM XS

WHERE 专业名= ’计算机’

该语句的执行结果与上例的结果完全相同。

4.1.1 选择列

注意，当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：

SELECT ‘Student number’ = 学号，姓名 AS ‘Student name’， mark = 总学分

FROM XS

WHERE 专业名= ‘计算机’

4.1.1 选择列

3. 替换查询结果中的数据

在对表进行查询时，有时对所查询的某些列希望得到的是一种概念而不是具体的数据。例如查询XS表的总学分，所希望知道的是学习的总体情况，这时，就可以用等级来替换总学分的具体数字。

要替换查询结果中的数据，则要使用查询中的CASE表达式，格式为：

CASE

WHEN 条件1 THEN 表达式1

WHEN 条件2 THEN 表达式2

……

ELSE 表达式

END

SQL Server按照顺序计算每一个条件，如果找到条件为真的语句，SQL Server就执行TEHN关键字后的表达式；否则执行可选的ELSE关键字后的语句。若没有为真的条件，也没有关键字，则CASE表达式返回值为空。

4.1.1 选择列

【例4.4】查询XS表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换：若总学分为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50与52之间，替换为“合格”；若总学分大于52，替换为“优秀”。列标题更改为“等级”。

SELECT 学号, 姓名,

等级=

CASE

WHEN 总学分 IS NULL THEN ‘尚未选课’

WHEN 总学分 < 50 THEN ‘不及格’

WHEN 总学分 >=50 and 总学分<=52THEN ‘合格’

ELSE ‘优秀’

END

FROM XS

WHERE 专业名=’计算机’

4.1.1 选择列

4. 计算列值

使用SELECT对列进行查询时，在结果中可以输出对列值计算后的值，即SELECT子句可使用表达式作为结果，格式为：

SELECT expression[ , expression ]

【例4.5】按120分计算成绩显示。

SELECT 学号, 课程号,

成绩120=成绩*1.20

FROM XS_KC

4.1.1 选择列

计算列值使用算术运算符：+（加）、-（减）、*（乘）、/（除）和%（取余），其中5种算术运算符（+、-、*、/）可以用于任何数字类型的列，包括：int、smallint、tinyint、decimal、numeric、float、real、money和smallmoney；%可以用于上述除money和smallmoney以外的数字类型。

5. 消除结果集中的重复行

对表只选择其某些列时，可能会出现重复行。例如，若对XSCJ数据库的XS表只选择专业名和总学分，则出现多行重复的情况。可以使用DISTINCT关键字消除结果集中的重复行，其格式是：

SELECT DISTINCTcolumn_name [ , column_name…]

关键字DISTINCT的含义是对结果集中的重复行只选择一个，保证行的唯一性。

【例4.6】对XSCJ数据库的XS表只选择专业名和总学分，消除结果集中的重复行。

SELECT DISTINCT 专业名, 总学分

FROM XS

图4.6消除重复行

与DISTINCT相反，当使用关键字ALL时，将保留结果集的所有行。当SELECT语句中缺省ALL与DISTINCT时，默认值为ALL。

6. 限制结果集返回行数

如果SELECT语句返回的结果集的行数非常多，可以使用TOP选项限制其返回的行数。TOP选项的基本格式为：

TOP n [ PERCENT ]

其中n是一个正整数，表示返回查询结果集的前n行。若带PERCENT关键字，则表示返回结果集的前n%行。

4.1.1 选择列

【例4.7】对XSCJ数据库的XS表选择姓名、专业名和总学分，只返回结果集的前6行。

SELECT TOP 6 姓名，专业名，总学分

FROM XS

4.1.2 选择行

在SQL Server中，选择行是通过在SELECT语句中WHERE子句指定选择的条件来实现的。这一节将详细讨论WHERE子句中查询条件的构成。WHERE子句必须紧跟FROM子句之后，其基本格式为：

在SQL中，返回逻辑值（TRUE或FALSE）的运算符或关键字都可称为谓词。

1. 表达式比较

比较运算符用于比较两个表达式值，共有9个，分别是： =（等于）、<（小于）、<=（小于等于）、>（大于）、>=（大于等于）、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）。比较运算的格式为：

expression { = |< | <= | > | >= | <> | != | !< | !> } expression

其中expression是除text、ntext和image外类型的表达式。

当两个表达式值均不为空值（NULL）时，比较运算返回逻辑值TRUE（真）或FALSE（假）；而当两个表达式值中有一个为空值或都为空值时，比较运算将返回UNKNOWN。

【例4.8】查询XSCJ数据库XS表中通信工程专业总学分大于等于42的同学的情况。

SELECT *

FROM XS

WHERE专业名 = ‘通信工程’ and 总学分 >=42

4.1.2 选择行

2. 模式匹配

LIKE谓词用于指出一个字符串是否与指定的字符串相匹配，其运算对象可以是char、varchar、text、ntext、datetime和smalldatetime类型的数据，返回逻辑值TRUE或FALSE。LIKE谓词表达式的格式为：

string_expression[ NOT ] LIKE string_expression [ ESCAPE ‘escape_character’ ]

【例4.9】查询XSCJ数据库XS表中计算机系的学生情况。查询XSCJ数据库XS表中姓“王”且单名的学生情况。

SELECT *

FROM XS

WHERE 专业名 LIKE ‘计算机’

SELECT *

FROM XS

WHERE 姓名 LIKE ‘王_’

4.1.2 选择行

使用LIKE进行模式匹配时，常使用通配符，即可进行模糊查询。有关通配符的格式和含义请见附录中介绍T-SQL语言的相关说明。

执行结果如图4.8所示。

若要匹配的内容为通配符的字符（包括%、_、[] 、^），可使用关键字ESCAPE。以告诉系统其后的每个字符均作为实际匹配的字符，而不再作为通配符。

3. 范围比较

用于范围比较的关键字有两个：BETWEEN和IN。

当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：

expression [ NOT ]BETWEEN expression1 AND expression2

4.1.2 选择行

当不使用NOT时，若表达式expression的值在表达式expression1与expression2之间（包括这两个值），则返回TRUE，否则返回FALSE；使用NOT时，返回值刚好相反。

注意：expression1的值不能大于expression2的值。

使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。使用IN关键字指定值表的格式为：

expression IN (expression [,…n])

【例4.10】查询XSCJ数据库XS表中不在1989年出生的学生情况。查询XSCJ数据库XS表中专业名为“计算机”或“通信工程”或“无线电”的学生的情况。

SELECT *

FROM XS

WHERE 出生时间 NOT BETWEEN ‘1989-1-1’ and ‘1989-12-31’

SELECT *

FROM XS

WHERE 专业名 IN (’计算机’, ’通信工程’, ’无线电’)

4.1.2 选择行

该语句与下列语句等价：

SELECT *

FROM XS

WHERE 专业名 = ’计算机’ or 专业名 = ’通信工程’ or 专业名 = ’无线电’

说明：IN关键字最主要的作用是表达子查询。

4. 空值比较

如果两个值当中有一个或者都为空，则对这两个值的比较结果是未知的。NULL谓词提供了一种方法，用来测试值为空或者非空。格式为：

expression IS [NOT ] NULL

当不使用NOT时，若表达式expression的值为空值，返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。

【例4.11】查询XSCJ数据库中总学分尚不定的学生情况。

SELECT *

FROM XS

WHERE 总学分 IS NULL

4.1.2 选择行

5. CONTAINS谓词

若需要在表中搜索指定的单词、短语或近义词等，可以使用CONTAINS谓词。CONTAINS谓词用于在表中搜索指定的字符串，可以是精确匹配，也可以是模糊匹配，还可以是加权匹配。要使用CONTAINS谓词，必须在操作的表上事先建立全文索引。

CONTAINS ( {column | * } , ‘<contains_search_condition>’)

上式中，column表示在指定的列中搜索，*表示在所有列中搜索； <contains_search_condition>为搜索的限定或说明。

<contains_search_condition>::=

{<simple_term> | <prefix_term> | <generation_term> |<proximity_term> | <weighted_term> }

| {(<contains_search_condition> ) { AND | AND NOT | OR }<contains_search_condition> […n] }

说明：

simple_term：用于说明搜索的是单词还是短语，格式为：word | “phrase”，word为单词，即不含空格和标点符号的字符串；短语是含一个或多个空格的字符串。如果搜索的是短语，则需要用双引号将其括起来。

4.1.2 选择行

prefix_term：给出了要搜索的单词或短语必须匹配的前缀，格式为：

{ “word*” |“phase*”}

其中word为单词，phase为短语，当查询的串是是短语时，需用双引号定界。

generation_term：说明搜索包含原词的派生词，所谓派生词是指原词的名词单、复数形式或动词的各种时态等。格式为：

FORMSOF (INFLECTIONAL , <simple_term> [,…n])

proximity_term：表示搜索包含NEAR或~运算符左右两边的词或短语，格式为： { <simple_term> | <prefix_term> } { { NEAR | ~ } {<simple_term> | <prefix_term> }[…n]

weight_term：指明本语句是加权搜索，即查询的数据与给定的权重进行加权匹配，格式为：

ISABOUT ( { {<simple_term> | <prefix_term> | <generation_term> |<proximity_term> } [ WEUGHT (weight_value) ] } [,…n] )

其中weight_value是一个0～1之间的数，表示权重。

6. FREETEXT谓词

与CONTAINS谓词类似，FREETEXT谓词也用于在一个表中搜索单词或短语，并要求表已建全文索引。格式为：

FREETEXT ( {column | * }, ‘freetext_string’ )

其中freetext_string是要搜索的字符串。

FREETEXT的查询精度没有CONTAINS高，他并不要求对它们进行严格的模式匹配。 FREETEXT对所查询的串也没有写法要求，因此FREETEXT也称为自由式查询。

4.1.2 选择行

7. 子查询

子查询通常与IN、EXIST谓词及比较运算符结合使用。

（1）IN子查询

IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：

expression [ NOT ]IN ( subquery )

其中subquery是子查询。当表达式expression与子查询subquery的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。

【例4.12】在XSCJ数据库中有描述课程情况的表KC和描述学生成绩表的表XS_KC，表的结构和样本数据见附录A。要查找选修了课程号为101的课程的学生的情况：

SELECT *

FROM XS

WHERE 学号 IN

( SELECT 学号 FROM XS_KC WHERE 课程号 = ‘101’ )

4.1.2 选择行

在执行包含子查询的SELECT语句时，系统先执行子查询，产生一个结果表，再执行查询。本例中，先执行子查询：

SELECT 学号

FROM XS_KC

WHERE 课程名 = ‘101’

SELECT *

FROM XS

WHERE 学号 NOT IN

( SELECT 学号

FROM XS_KC

WHERE 课程号 IN

( SELECT 课程号

FROM KC

WHERE 课程名 = '离散数学'

)

4.1.2 选择行

（2）比较子查询

这种子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：

expression { <| <= | = | > | >= | != | <> | !< | !> } { ALL | SOME | ANY} ( subquery )

其中expression为要进行比较的表达式，subquery是子查询。ALL、SOME和ANY说明对比较运算的限制。

ALL指定表达式要与子查询结果集中的每个值都进行比较，当表达式与每个值都满足比较的关系时，才返回TRUE，否则返回FALSE；

SOME或ANY表示表达式只要与子查询结果集中的某个值满足比较的关系时，就返回 TRUE，否则返回FALSE。

【例4.14】查找比所有计算机系的学生年龄都大的学生。

SELECT *

FROM XS

WHERE 出生时间 <ALL

( SELECT 出生时间

FROM XS

WHERE 专业名 = '计算机'

)

【例4.15】查找课程号206的成绩不低于课程号101的最低成绩的学生的学号。

SELECT 学号

FROM XS_KC

WHERE 课程号 = '206' AND 成绩 !< ANY

( SELECT 成绩

FROM XS_KC

WHERE 课程号 = '101'

)

4.1.2 选择行

（3）EXISTS子查询

EXISTS谓词用于测试子查询的结果是否为空表，若子查询的结果集不为空，则EXISTS返回TRUE，否则返回FALSE。EXISTS还可与NOT结合使用，即NOT EXISTS，其返回值与EXIST刚好相反。格式为：

[ NOT ] EXISTS (subquery )

【例4.16】查找选修206号课程的学生姓名。

SELECT 姓名

FROM XS

WHERE EXISTS

( SELECT *

FROM XS_KC

WHERE 学号 = XS.学号 AND 课程号 = ‘206’

)

分析：

① 本例在子查询的条件中使用了限定形式的列名引用XS.学号，表示这里的学号列出自表XS。

4.1.2 选择行

② 本例与前面的子查询例子不同点是，前面的例子中，内层查询只处理一次，得到一个结果集，再依次处理外层查询；而本例的内层查询要处理多次，因为内层查询与XS.学号有关，外层查询中XS表的不同行有不同的学号值。这类子查询称为相关子查询，因为子查询的条件依赖与外层查询中的某些值。

【例4.17】查找选修了全部课程的同学的姓名。

SELECT 姓名

FROM XS

WHERE NOT EXISTS

(SELECT *

FROM KC

WHERE NOT EXISTS

( SELECT *

FROM XS_KC

WHERE 学号=XS.学号 AND 课程号=KC.课程号

)

4.1.3 FROM子句

SELECT的查询对象由FROM子句指定，其格式为：

[ FROM{<table_source>} [,…n] ]

其中table_source指出了要查询的表或视图。

<table_source>::=

table_name [ [ AS] table_alias ] [ WITH ( <table_hint>[,…] ) ]

/*查询表，可以指定别名*/

| view_name [ [ AS] table_alias ] /*查询视图*/

| rowset_function[ [AS ] table_alias ] /*行集函数*/

| OPENXML /*XML文档*/

| derived_table [AS ] table_alias [ ( column_alias [,…n] ) ] /*子查询*/

|<joined_table> /*连接表*/

1. table_name

table_name指定SELECT语句要查询的表，从前面的例子中已经了解了其作用。可以使用AS选项为表指定别名，AS也可以省略，直接给出别名即可。别名主要用在相关子查询及连接查询中。

【例4.18】查找选修了学号为081102同学所选修的全部课程的同学的学号。

分析：本例即要查找这样的学号y，对所有的课程号x，若081102号同学选修了该课，那么y也选修了该课。

SELECT DISTINCT 学号

FROM XS_KC AS CJ1

WHERE NOT EXISTS

( SELECT *

FROM XS_KC AS CJ2

WHERE CJ2.学号 = ‘081102’ AND NOT EXISTS

( SELECT *

FROM XS_KC AS CJ3

WHERE CJ3.学号= CJ1.学号 AND CJ3.课程号 = CJ2.课程号

)

4.1.3 FROM子句

2. view_name

view_name为视图名，也可以为其指定别名。有关视图的介绍请见4.3节。

3. rowset_function

rowset_function是一个行集函数，行集函数通常返回一个表或视图。主要的行集函数有CONTAINSTABLE、FREETEXTTABLE、OPENDATASOURCE、OPENQUERY、OPENROWSET和OPENXML。

(1) CONTAINSTABLE函数

该函数与CONTAINS谓词相对应，用于对表进行全文查询，并且要求所查询的表上建立了全文索引。CONTAINSTABLE函数的语法格式为：

CONTAINSTABLE (table , { column | * } , ‘<contains_search_condition>’ [ , top_n_by_rank] )

其中table是进行全文查询的表，column指定被查询的列，*指对所有列进行查询。 contains_search_condition与CONTAINS谓词中的搜索条件完全相同。

CONTAINSTABLE函数返回包含满足匹配条件的行，共有两列：KEY和RANK，其中KEY是包含被检索的字符串的行的主键值，RANK是一个等级值，指明行与字符串匹配的情况。可选项top_n_by_rank，说明只返回按RANK降序排列的结果表的前n行，其中n是正整数。

4.1.3 FROM子句

CONTAINSTABLE函数常与INNER JOIN结合使用。

(2) FREETEXTTABLE函数

FREETEXTTABLE函数与FREETEXT谓词相对应，它的使用与CONTAINSTABLE函数类似，格式为：

FREETEXTTABLE (table , { column | * } , ‘freetext_string’ [ , top_n_by_rank ] )

该函数使用与FREETEXT谓词相同的搜索条件。

(3) OPENDATASOURCE函数

该函数使用户连接到服务器。格式为：

OPENDATASOURCE (provider_name , init_string )

其中provider_name是注册为用于访问数据源的OLE DB 提供程序的 PROGID 的名称，init_string是连接字符串，这些字符串将要传递给目标提供程序的 IDataInitialize 接口。

例如：

SELECT *

FROMOPENDATASOURCE

（ 'SQLOLEDB','Data Source=ServerName;UserID=MyUID;Password=MyPass'

).Northwind.dbo.Categories

4.1.3 FROM子句

(4) OPENQUERY函数

该函数在给定的链接服务器（一个 OLE DB 数据源）上执行指定的直接传递查询，返回查询的结果集。格式为：

OPENQUERY (linked_server , ‘query’ )

其中linked_server为连接的服务器名，query是查询命令串。

例如：

EXECsp_addlinkedserver 'OSvr', 'Oracle 7.3',

'MSDAORA',

'ORCLDB'

SELECT *

FROMOPENQUERY(OSvr, 'SELECT title, id FROM al.book')

该例使用为Oracle提供的OLE DB对Oracle数据库创建了一个名为Osvr的连接服务器，然后对该其进行检索。

(5) OPENROWSET函数

该函数与OPENQUERY函数功能相同。

(6) OPENXML函数

OPENXML 通过XML文档提供行集视图。

4.1.3 FROM子句

4. derived_table

derived_table是由SELECT查询语句的执行而返回的表，必须使用为其指定一个别名，也可以为列指定别名。

【例4.19】在XS表中查找1990年1月1日以前出生的学生的姓名和专业名，分别使用别名stu_name和speciality表示。

SELECTm.stu_name,m.speciality

FROM ( SELECT *FROM XS WHERE 出生时间<'19900101' ) AS m

(num,stu_name,speciality,sex,birthday,score,mem ,photo )

注意，若要为列指定别名，则必须为所有列指定别名。

执行结果如图4.11所示。

5. joined_table

joined_table为连接表，将在下一小节介绍。

4.1.4 连接

连接是两元运算，可以两个或多个表进行查询，结果通常是含有参加连接运算的两个表（或多个表）的指定列的表。例如在XSCJ数据库中需要查找选修了离散数学课程的学生的姓名和成绩，就需要将XS、KC和XS_KC三个表进行连接，才能查找到结果。

在T-SQL中，连接查询有两大类表示形式，一是符合SQL标准连接谓词表示形式，一是T-SQL扩展的使用关键字JOIN的表示形式。

1. 连接谓词

可以在SELECT语句的WHERE子句中使用比较运算符给出连接条件对表进行连接，将这种表示形式称为连接谓词表示形式。

【例4.20】查找XSCJ数据库每个学生的情况以及选修的课程情况。

SELECT XS.* ,XS_KC.*

FROM XS , XS_KC

WHERE XS.学号 = XS_KC.学号

4.1.4 连接

【例4.21】自然连接查询

SELECT XS.* ,XS_KC.课程号, XS_KC.成绩

FROM XS , XS_KC

WHERE XS.学号= XS_KC.学号

本例所得的结果表包含以下字段：学号、姓名、专业名、性别、出生时间、总学分、备注、课程号、成绩。

若选择的字段名在各个表中是唯一的，则可以省略字段名前的表名。如本例的 SELECT子句也可写为：

SELECT XS.* , 课程号, 成绩

FROM XS , XS_KC

WHERE XS.学号 = XS_KC.学号

【例4.22】查找选修了206课程且成绩在80分以上的学生姓名及成绩。

SELECT 姓名 , 成绩

FROM XS , XS_KC

WHERE XS.学号 = XS_KC.学号 AND 课程号 = ‘206’ AND 成绩 >= 80

4.1.4 连接

【例4.23】查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT XS.学号, 姓名, 课程名, 成绩

FROM XS , KC ,XS_KC

WHERE XS.学号 = XS_KC.学号 AND KC.课程号 = XS_KC.课程号

AND 课程名 = ‘计算机基础’ AND 成绩 >= 80

4.1.4 连接

2. 以JOIN关键字指定的连接

对两个表之间的简单内部连接（不匹配的行将从结果中删除），通过在FROM字句中列出多个，并在WHERE子句中加上连接条件即可完成这个功能。而对更复诊的外部连接（不匹配的行需要保留），或者多一个一个连接的查询中用到的表多于两个，那么在FORM子句中需要明确的连接句法。T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有了增强。FROM子句的joined_table表示将多个表连接起来。

4.1.4 连接

<joined_table>::=

<table_source><join_type> <table_source> ON <search_condition>

|<table_source> CROSS JOIN <table_source>

|<joined_table>

其中table_source为指定要在Transact-SQL语句中使用的表或视图（带或不带别名均可），join_type表示连接类型，ON用于指定连接条件。join_type的格式为：

[ INNER | { LEFT |RIGHT | FULL } [ OUTER ] [ <join_hint> ] JOIN

其中INNER表示内连接，OUTER表示外连接，join_hint是连接提示。

CROSS JOIN表示交叉连接。因此，以JOIN关键字指定的连接有三种类型：

(1) 内连接

内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。

【例4.24】查找XSCJ数据库每个学生的情况以及选修的课程情况。

SELECT *

FROM XS INNER JOINXS_KC

ON XS.学号 = XS_KC.学号

4.1.4 连接

结果表将包含XS表和XS_KC表的所有字段（不去除重复字段—学号）。若要去除重复的学号字段，可将SELECT子句改为：

SELECT XS.* , 课程号, 成绩

内连接是系统默认的，可以省略INNER关键字。使用内连接后仍可使用WHERE子句指定条件。

【例4.25】用FROM的JOIN关键字表达下列查询：查找选修了206课程且成绩在80分以上的学生姓名及成绩。

SELECT 姓名 , 成绩

FROM XS JOIN XS_KCON XS.学号 = XS_KC.学号

WHERE 课程号 = '206' AND成绩>=80

内连接还可以用于多个表的连接。

【例4.26】用FROM的JOIN关键字表达下列查询：查找选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。

SELECT XS.学号 , 姓名 , 课程名 , 成绩

FROM XS JOIN XS_KCJOIN KC ON XS_KC.课程号 = KC.课程号

ON XS.学号 = XS_KC.学号

WHERE 课程名 = '计算机基础' AND 成绩>=80

作为一种特例，可以将一个表与它自身进行连接，称为自连接。若要在一个表中查找具有相同列值的行，则可以使用自连接。使用自连接时需为表指定两个别名，且对所有列的引用均要用别名限定。

4.1.4 连接

【例4.27】查找不同课程成绩相同的学生的学号、课程号和成绩。

SELECT a.学号,a.课程号,b.课程号,a.成绩

FROM XS_KC a JOINXS_KC b

ON a.成绩=b.成绩 AND a.学号=b.学号 AND a.课程号!=b.课程号

图4.15查找不同课程成绩相同的学生的学号、课程号和成绩

(2) 外连接

外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括三种：

● 左外连接（LEFT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括左表的所有行；

● 右外连接（RIGHT OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括右表的所有行；

4.1.4 连接

● 完全外连接（FULL OUTER JOIN）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。

其中的OUTER关键字均可省略。

【例4.28】查找所有学生情况，及他们选修的课程号，若学生未选修任何课，也要包括其情况。

SELECT XS.* , 课程号

FROM XS LEFT OUTERJOIN XS_KC ON XS.学号 = XS_KC.学号

本例执行时，若有学生未选任何课程，则结果表中相应行的课程号字段值为NULL。

【例4.29】查找被选修了的课程的选修情况和所有开设的课程名。

SELECT XS_KC.* , 课程名

FROM XS_KC RIGHTJOIN KC ON XS_KC.课程号= KC.课程号

本例执行时，若某课程未被选修，则结果表中相应行的学号、课程号和成绩字段值均为NULL。

注意外连接只能对两个表进行。

(3) 交叉连接

交叉连接实际上是将两个表进行笛卡尔积运算，结果表是由第一个表的每行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表行数之积。

【例4.30】列出学生所有可能的选课情况。

SELECT 学号，姓名，课程号，课程名

FROM XS CROSS JOINKC

注意: 交叉连接不能有条件，且不能带WHERE子句。

4.1.5 数据汇总

对表数据进行检索时，经常需要对结果进行汇总或计算，例如在学生成绩数据库中求某门功课的总成绩、统计各分数段的人数等。本小节将讨论SELECT语句中用于数据统计的子句及函数。

1. 聚合函数

聚合函数用于计算表中的数据，返回单个计算结果。SQL Server 2000所提供的聚合函数列于表4.10中。

4.1.5 数据汇总

表4.10 聚合函数表

函数名	说明
AVG	求组中值的平均值。
BINARY_CHECKSUM	返回对表中的行或表达式列表计算的二进制校验值，可用于检测表中行的更改。
CHECKSUM	返回在表的行上或在表达式列表上计算的校验值，用于生成哈希索引。
CHECKSUM_AGG	返回组中值的校验值。
COUNT	求组中项数，返回int类型整数。
COUNT_BIG	求组中项数，返回bigint类型整数。
GROUPING	产生一个附加的列。
MAX	求最大值。
MIN	求最小值。
SUM	返回表达式中所有值的和。
STDEV	返回给定表达式中所有值的统计标准偏差。
STDEVP	返回给定表达式中所有值的填充统计标准偏差。
VAR	返回给定表达式中所有值的统计方差。
VARP	返回给定表达式中所有值的填充的统计方差。

4.1.5 数据汇总

下面对常用的几个聚合函数加以介绍。

（1）SUM和AVG

SUM和AVG分别用于求表达式中所有值项的总和与平均值，语法格式为：

SUM / AVG ( [ ALL| DISTINCT ] expression )

其中expression是常量、列、函数或表达式，其数据类型只能是：int、smallint、tinyint、bigint、decimal、numeric、float、real、money和smallmoney。ALL表示对所有值进行运算，DISTINCT表示去除重复值，缺省为ALL。SUM / AVG忽略NULL值。

【例4.31】求选修101课程的学生的平均成绩。

SELECT AVG(成绩) AS '课程101平均成绩'

FROM XS_KC

WHERE 课程号 = '101'

执行结果为：

课程101平均成绩

使用聚合函数作为SELECT的选择列时，若不为其指定列标题，则系统将对该列输出标题“（无列名）”。

（2）MAX和MIN

MAX和MIN分别用于求表达式中所有值项的最大值与最小值，语法格式为：

MAX / MIN ( [ ALL| DISTINCT ] expression )

4.1.5 数据汇总

其中expression是常量、列、函数或表达式，其数据类型可以是数字、字符和时间日期类型。ALL、DISTINCT的含义及缺省值与SUM/AVG函数相同。MAX/MIN忽略NULL值。

【例4.32】求选修101课程的学生的最高分和最低分。

SELECT MAX(成绩) AS '课程101的最高分' ,MIN(成绩) AS '课程101的最低分'

FROM XS_KC

WHERE 课程号 = '101'

执行结果为：

课程101的最高分课程101的最低分

95 62

（3）COUNT

COUNT用于统计组中满足条件的行数或总行数，格式为：

COUNT ( { [ ALL |DISTINCT ] expression } | * )

其中expression是一个表达式，其数据类型是除uniqueidentifier、text、image或ntext之外的任何类型。ALL、DISTINCT的含义及缺省值与SUM/AVG函数相同。选择*时将统计总行数。COUNT忽略NULL值。

【例4.33】求学生的总人数。

SELECT COUNT(*) AS'学生总数'

FROM XS

其中：COUNT(*) 不需要任何参数。

4.1.5 数据汇总

【例4.34】求选修了课程的学生总人数。

SELECTCOUNT(DISTINCT 学号)

FROM XS_KC

【例4.35】统计离散数学课程成绩在85分以上的人数。

SELECT COUNT(成绩) AS '离散数学85分以上的人数'

FROM XS_KC

WHERE 成绩 >= 85 AND 课程号 IN

( SELECT 课程号

FROM KC

WHERE 课程名 = '离散数学'

)

执行结果为：

离散数学85分以上的人数

COUNT_BIG函数的格式、功能与COUNT函数都相同，区别仅在于COUNT_BIG返回bigint类型值。

4.1.5 数据汇总

（4）GROUPING

GROUPING函数为输出的结果表产生一个附加列，该列的值为1或0，格式为：

GROUPING（ column_name ）

当用 CUBE 或 ROLLUP 运算符添加行时，附加的列输出值为1，当所添加的行不是由 CUBE 或 ROLLUP 产生时，附加列值为0。

该函数只能与带有CUBE或ROLLUP运算符的GROUP BY子句一起使用。

2. GROUP BY子句

GROUP BY子句用于对表或视图中的数据按字段分组，格式为：

[ GROUP BY [ ALL ]group_by_expression [,…n]

[ WITH { CUBE |ROLLUP } ] ]

说明:

group_by_expression:用于分组的表达式，其中通常包含字段名。指定ALL将显示所有组。使用GROUPBY子句后，SELECT子句中的列表中只能包含在GROUPBY中指出的列或在聚合函数中指定的列。WITH指定CUBE或ROLLUP操作符，CUBE或 ROLLUP与聚合函数一起使用，在查询结果中增加附加记录。

【例4.36】将XSCJ数据库中各专业名输出。

SELECT 专业名

FROM XS

GROUP BY 专业名

【例4.37】求XSCJ数据库中各专业的学生数。

SELECT 专业名,COUNT(*) AS '学生数'

FROM XS

GROUP BY 专业名

4.1.5 数据汇总

【例4.38】求被选修的各门课程的平均成绩和选修该课程的人数。

SELECT 课程号, AVG(成绩) AS '平均成绩',COUNT(学号) AS '选修人数'

FROM XS_KC

GROUP BY 课程号

4.1.5 数据汇总

【例4.39】在XSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及学生总人数。

SELECT 专业名, 性别 , COUNT(*) AS '人数'

FROM XS

GROUP BY 专业名,性别

WITH ROLLUP

4.1.5 数据汇总

【例4.39】在XSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及学生总人数。

SELECT 专业名, 性别 , COUNT(*) AS '人数'

FROM XS

GROUP BY 专业名,性别

WITH ROLLUP

4.1.5 数据汇总

【例4.40】在XSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩和所有课程的总平均成绩。

SELECT 课程名, 专业名, AVG(成绩) AS '平均成绩'

FROM XS_KC,KC,XS

WHERE XS_KC.课程号 = KC.课程号 AND XS_KC.学号 = XS.学号

GROUP BY 课程名, 专业名

WITH ROLLUP

4.1.5 数据汇总

【例4.41】在XSCJ数据库上产生一个结果集，包括每个专业的男生、女生人数、总人数及男生总数、女生总数、学生总人数。

SELECT 专业名, 性别 , COUNT(*) AS '人数'

FROM XS

GROUP BY 专业名,性别

WITH CUBE

4.1.5 数据汇总

执行结果如图4.22所示。

4.1.5 数据汇总

执行结果如图4.22所示。

图4.22 查询结果

分析：本例中用于分组的列（即GROUP BY子句中的列）为专业名和性别，在XS表中，专业名有两个不同的值（计算机、通信工程），性别也有两个不同的值（0、1），再加上NULL值，因此它们可能的组合有5种，因此生成5个汇总行。

4.1.5 数据汇总

【例4.42】在XSCJ数据库上产生一个结果集，包括各专业每门课程的平均成绩、每门课程的总平均成绩、每个专业的总平均成绩和所有课程的总平均成绩。

SELECT 课程名,专业名, AVG(成绩) AS '平均成绩'

FROM XS_KC, KC, XS

WHERE XS_KC.课程号 = KC.课程号 AND XS_KC.学号 = XS.学号

GROUP BY 课程名,专业名

4.1.5 数据汇总

【例4.43】统计各专业男生、女生人数及学生总人数，标志汇总行。

SELECT 专业名, 性别 , COUNT(*) AS '人数', GROUPING(专业名) AS'spec', GROUPING(性别) AS 'sx'

FROM XS

GROUP BY 专业名,性别

WITH CUBE

4.1.5 数据汇总

3. HAVING子句

使用GROUP BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据进行进一步的筛选。HAVING子句有一个与WHERE子句类似的形式。

例如查找XSCJ数据库中平均成绩在85分以上的学生，就是在XS_KC数据库上按学号分组后筛选出符合平均成绩大于等于85的学生。HAVING子句的格式为：

[ HAVING<search_condition> ]

其中search_condition为查询条件，与WHERE子句的查询条件类似，并且可以使用聚合函数。

【例4.44】查找XSCJ数据库中平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号 , AVG(成绩) AS '平均成绩'

FROM XS_KC

GROUP BY 学号

HAVING AVG(成绩) > =85

4.1.5 数据汇总

【例4.45】查找选修课程超过2门且成绩都在80分以上的学生的学号。

SELECT 学号

FROM XS_KC

WHERE 成绩 >= 80

GROUP BY 学号

HAVING COUNT(*)> 2

分析：本查询将XS_KC表中成绩大于80的记录按学号分组，对每组记录计数，选出记录数大于2的各组的学号值形成结果表。

【例4.46】查找通信工程专业平均成绩在85分以上的学生的学号和平均成绩。

SELECT 学号,AVG(成绩) AS '平均成绩'

FROM XS_KC

WHERE 学号 IN

( SELECT 学号

FROM XS

WHERE 专业名 = '通信工程'

)

GROUP BY 学号

HAVING AVG(成绩) > =85

4.1.5 数据汇总

分析：先执行WHERE查询条件中的子查询，得到通信工程专业所有学生的学号集；然后对XS_KC中的每条记录，判断其学号字段值是否在前面所求得的学号集中。若否，则跳过该记录，继续处理下一条记录，若是则加入WHERE的结果集。对XS_KC均筛选完后，按学号进行分组，再在各分组记录中选出平均成绩值大于等于85的记录形成最后的结果集。

4. COMPUTE子句

COMPUTE子句用于分类汇总。对于交互式的检索，SQL提供了计算部分和的能力。用户可以在包含几个选择表达式的SELECT语句后，带有几个使用合计函数的COMPUTE子句，计算不同层次的合计值。COMPUTE子句格式为：

[ COMPUTE { 聚合函数名（expression）} [ ,…n ] [ BY expression [ ,…n ] ] ]

其中聚合函数名见表4.10，expression是列名。

COMPUTE将产生额外的汇总行。

【例4.47】查找通信工程专业学生的学号、姓名、出生时间，并产生一个学生总人数行。

SELECT 学号,姓名, 出生时间

FROM XS

WHERE 专业名 = '通信工程'

COMPUTE COUNT(学号)

4.1.5 数据汇总

执行结果如图4.26所示。从图中可以看出，COMPUTE子句产生附加的汇总行，其列标题是系统自定的，对于COUNT函数为cnt，对于AVG函数为avg，对于SUM函数为sum，等等。

COMPUTE BY子句要与ORDER BY子句一起使用，将在下一小节介绍。

4.1.6 排序

在应用中经常要对查询的结果排序输出，例如学生成绩由高到低排序。在SELECT语句中，使用ORDERBY子句对查询结果进行排序。ORDER BY子句的格式为：

[ ORDER BY {order_by_expression [ ASC | DESC ] } [ ,…n ]

其中order_by_expression是排序表达式，可以是列名、表达式或一个正整数，当 expression是一个正整数时，表示按表中的该位置上列排序。

关键字ASC表示升序排列，DESC表示降序排列，系统默认值为ASC。

【例4.48】将通信工程专业的学生按出生时间先后排序。

SELECT *

FROM XS

WHERE 专业名 = '通信工程'

ORDER BY 出生时间

【例4.49】将计算机专业学生的“计算机基础”课程成绩按降序排列。

SELECT 姓名,课程名,成绩

FROM XS,KC,XS_KC

WHERE XS.学号 = XS_KC.学号 ANDXS_KC.课程号 = KC.课程号

AND 课程名 = '计算机基础' AND 专业名= '计算机'

ORDER BY 成绩 DESC

4.1.6 排序

ORDER BY子句可以与COMPUTE BY子句一起使用，在对结果排序的同时还产生附加的汇总行。

【例4.50】将学生按专业名排序，并汇总各专业人数和平均学分。

SELECT 学号,姓名,出生时间,总学分

FROM XS

ORDER BY 专业名

COMPUTE COUNT(学号),

AVG(总学分) BY 专业名

4.2.1 视图概念

视图一经定义以后，就可以象表一样被查询、修改、删除和更新。使用视图有下列优点：

为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。

屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。

简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。

便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。

可以重新组织数据以便输出到其它应用程序中。

使用视图时，要注意下列事项：

(1) 只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是唯一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。

(2) 不能把规则、默认值或触发器与视图相关联。

(3) 不能在视图上建立任何索引，包括全文索引。

4.2.2 创建视图

1. 在SQL Server Enterprise Manager中创建视图

以在XSCJ数据库中创建CS_XS（描述计算机专业学生情况）视图说明在SQL Server Enterprise Manager中创建视图的过程。

第1步打开SQL Server Enterprise Manager，在数据库XSCJ上单击鼠标右键，在弹出的快捷菜单上选择“新建”?“视图..”，

4.2.2 创建视图

第2步在所出现的窗口的第一个子窗口中单击鼠标右键，将弹出一个“增加表”快捷菜单，如图4.29所示。

“增加表”快捷菜单

4.2.2 创建视图

第3步在“增加表”快捷菜单中选择“添加表…”功能项，将出现如图4.30所示的“添加表”对话框。

第4步在图4.30所示的“添加表”对话框中选择与视图相关联的表、视图或函数，可以使用Ctrl或Shift键进行多选，选择完毕后，单击“添加”按钮。

添加表对话框

4.2.2 创建视图

第5步在图4.29所示的窗口的第2个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则（本例指定“专业名”字段的规则为“计算机”）等，如图4.31所示。注意当视图中需要一个与原字段名不同的字段名、或视图的源表中有同名的字段、或视图中包含了计算列时，需要为视图中的这样的列重新指定名称。

选择列

4.2.2 创建视图

第6步上一步完成后，单击保存按钮，出现保存视图对话框，在其中输入视图名“CS_XS”，并单击“确定“按钮，便完成了视图的创建。

视图一经创建成功，其中便包含了所选择的列数据。例如，若创建了CS_XS视图，则可查看其结构及内容：在CS_XS视图上单击鼠标右键，选择“设计视图”功能项可以查看并可修改视图结构，选择“打开视图”?“返回所有行”将可查看视图数据内容。

2. 通过视图向导创建视图

第1步打开SQL Server Enterprise Manager，选择“工具”菜单中的“向导”功能项，出现如图4.32所示的选择向导对话框。

选择向导对话框

4.2.2 创建视图

第2步在选择向导对话框中展开“数据库”，选择“创建视图向导”功能项，单击“确定”，将出现欢迎使用向导界面。

第3步在欢迎使用向导界面中单击“下一步”，出现选择数据库对话框。选择数据库名“XSCJ”，单击“下一步”，出现如图4.33所示的选择表对话框。

选择表

4.2.2 创建视图

第4步在选择表对话框中选择与视图相关联的表，单击“下一步”，出现如图4.34所示的选择列对话框。

第5步在选择列对话框中选择视图所需要的列，单击“下一步”，出现如图4.35所示的视图限制对话框。

第6步在视图限制对话框中输入对视图的限制条件，本例为：WHERE 专业名=‘计算机’，单击“下一步”，出现输入视图名对话框。

第7步在输入视图名对话框中输入视图名“XS_XS”，单击“下一步”，出现如图4.36所示的视图创建完成对话框，单击“完成”，则视图即创建完毕。

选择列输入视图限制

4.2.2 创建视图

视图创建完成

3. 使用CREATE VIEW语句创建视图

T-SQL中用于创建视图的语句是CREATE VIEW语句，例如用该语句创建视图CS_XS，其表示形式为：

USE XSCJ

CREATE VIEW CS_XS

SELECT *

FROM XS

WHERE 专业名 = '计算机'

4.2.2 创建视图

语法格式：

CREATE VIEW [<database_name>. ][ <owner>. ] view_name [ ( column_name [ ,…n ] )]

[ WITH <view_attribute>[,…n ] ]

ASselect_statement

[ WITH CHECKOPTION ]

说明：

database_name是数据库名；owner是所有者名；view_name是视图名。

column_name：列名，它是视图中包含的列，可以有多个列名，最多可引用1024个列。若使用与源表或视图中相同的列名时，则不必给出column_name。

WITHview_attribute: 指出视图的属性，view_attribute可以取以下值：

(1) ENCRYPTION：说明在系统表syscomments中存储CREATEVIEW语句时进行加密。

(2) SCHEMABINDING：说明将视图与其所依赖的表或视图结构相关联。

(3) VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，向DBLIB、 ODBC或OLEDB API返回有关视图的元数据信息，而不是返回给基表或其它表。

select_statement：用来创建视图的SELECT语句，可在SELECT语句中查询多个表或视图，以表明新创建的视图所参照的表或视图。但对SELECT语句有以下的限制：

4.2.2 创建视图

(1) 定义视图的用户必须对所参照的表或视图有查询（即可执行SELECT语句）权限；

(2) 不能使用COMPUTE或COMPUTE BY子句；

(3) 不能使用ORDER BY子句；

(4) 不能使用INTO子句；

(5) 不能在临时表或表变量上创建视图。

WITH CHECK OPTION:指出在视图上所进行的修改都要符合select_statement所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。例如对于CS_XS视图，只能修改除“专业名”字段以外的字段值，而不能把专业名字段的值改为“计算机”以外的值，以保证仍可通过CS_XS查询到修改后的数据。

注意：CREATE VIEW必须是批命令的第一条语句。

【例4.51】创建CS_KC视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都要符合专业名为计算机这个条件。

CREATE VIEW CS_KCWITH ENCRYPTION

SELECT XS.学号,课程号,成绩

FROM XS,XS_KC

WHERE XS.学号 = XS_KC.学号 AND 专业名 = '计算机'

WITH CHECK OPTION

4.2.2 创建视图

创建视图时，源表可以是基本表，也可以是视图。

【例4.52】创建计算机专业学生的平均成绩视图CS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。

CREATE VIEWCS_KC_AVG(num,score_avg)

SELECT 学号,AVG(成绩)

FROM CS_KC

GROUP BY 学号

4.2.3 查询视图

再对XS_KC_AVG视图进行查询。

SELECT *

FROM XS_KC_AVG

WHEREscore_avg>=80

视图定义后，就可以如同查询基本表那样对视图进行查询。

【例4.53】使用视图CS_KC查找计算机专业的学生学号和选修的课程号。

SELECT 学号,课程号

FROM CS_KC

【例4.54】查找平均成绩在80分以上的学生的学号和平均成绩。

本例首先创建学生平均成绩视图XS_KC_AVG，包括学号（在视图中列名为num）和平均成绩（在视图中列名为score_avg）。

CREATE VIEWXS_KC_AVG ( num,score_avg )

SELECT 学号,AVG(成绩)

FROM XS_KC

GROUP BY学号

4.2.3 查询视图

使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建视图才能查询到新字段。例如，若XS表新增了“籍贯”字段，那么在其上创建的视图CS_XS若不重建视图，那么以下查询：

SELECT * FROMCS_XS

结果将不包含“籍贯”字段。只有重建CS_XS视图后再对它进行查询，结果才会包含“籍贯”字段。

如果与视图相关联的表或视图被删除，则该视图将不能再使用。

的视图。

4.2.4 更新视图

通过更新视图（包括插入、修改和删除）数据可以修改基本表数据。但并不是所有的视图都可以更新，只有对满足可更新条件的视图，才能进行更新。

1. 可更新视图

要通过视图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是以下情形之一：

(1) 满足以下条件的视图：创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUP BY、UNION子句及DISTINCT关键字；创建视图的SELECT语句中不包含从基本表列通过计算所得的列；创建视图的SELECT语句的FROM子句中至少要包含一个基本表。

(2) 可更新的分区视图。

(3) 通过INSTEAD OF触发器创建的可更新视图。

前面创建的视图CS_XS、CS_KC是可更新视图，而CS_KC_AVG是不可更新

4.2.4 更新视图

【例4.55】在XSCJ数据库中使用以下的CREATE TABLE语句创建表XS1和XS2，它们在分区列（即键值）上的CHECK约束分别为BETWEEN‘084001’ AND ‘084050’和 BETWEEN '083001' AND '083050'，两个表在键值约束上是不重叠的。

CREATE TABLE XS1

(学号 char(6) PRIMARY KEY CHECK (学号 BETWEEN '084001' AND '084050'),

姓名 char(8) NOT NULL,

专业名 char(10) NULL,

性别 bit NOT NULL,

总学分 tinyint NULL

)

CREATE TABLE XS2

(学号 char(6) PRIMARY KEY CHECK (学号 BETWEEN '083001' AND '083050'),

姓名 char(8) NOT NULL,

专业名 char(10) NULL,

性别 bit NOT NULL,

总学分 tinyint NULL

)

4.2.4 更新视图

并向XS1表中加入以下的数据行：

084001王进外语 140

084002郭明外语 138

向XS2表中加入以下的数据行：

083001李玉物理 038

083002刘明物理 138

使用以下的CREATE VIEW语句创建的视图XS12_VIEW将是可更新的分区视图：

CREATE VIEWXS12_VIEW

SELECT *

FROM XS1

UNION ALL

SELECT *

FROM XS2

INSTEAD OF触发器将在后面的章节介绍。

对视图进行更新操作时，要注意基本表对数据的各种约束和规则要求。

4.2.4 更新视图

2. 插入数据

使用INSERT语句通过视图向基本表插入数据，有关INSERT语句的语法介绍见第3章。

【例4.56】向CS_XS视图中插入一条记录：

（ ‘081115’ , ‘刘明仪’, ‘计算机’, 1,‘1989-3-2’, 50 ,NULL,NULL）

INSERT INTO CS_XS

VALUES('081115', '刘明仪','计算机',1,'1989-3-2',50,NULL,NULL)

使用SELECT语句查询CS_XS依据的基本表XS：

SELECT * FROM XS

将会看到该表已添加了('081115', '刘明仪','计算机',1,'1989-3-2',50,NULL,NULL)行。

当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基表。例如不能向视图CS_KC插入数据，因为CS_KC依赖两个基本表：XS和XS_KC。

向可更新的分区视图中插入数据时，系统会按照插入记录的键值所属的范围，将数据插入到其键值所属的基本表中。

【例4.57】向XS12_VIEW视图中插入一条记录：

（ ‘084010’ , ‘蓝圆圆’, ‘外语’, 0, 40 ）

INSERT INTOXS12_VIEW

VALUES('084010','蓝圆圆','外语',0,40)

4.2.4 更新视图

由于插入记录的键值为084010，因此该记录实际上是插入到表XS1中，使用以下的SELECT查询语句：

SELECT * FROM XS1

3. 修改数据

使用UPDATE语句可以通过视图修改基本表的数据，有关UPDATE语句的语法介绍见第3章。

【例4.58】将CS_XS视图中所有学生的总学分增加8。

UPDATE CS_XS

SET 总学分 = 总学分+ 8

4.2.4 更新视图

该语句实际上是将CS_XS视图所依赖的基本表XS中所有专业名为“计算机”的记录的总学分字段值在原来基础上增加8。

若一个视图（非分区视图）依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。

【例4.59】将CS_KC视图中学号为081101的学生的101课程成绩改为90。

UPDATE CS_KC

SET 成绩=90

WHERE 学号='081101' AND 课程号='101'

本例中，视图CS_KC依赖于两个基本表：XS和XS_KC，对CS_KC视图的一次修改只能改变学号（源于XS表）或者课程号和成绩（源于XS_KC表）。以下的修改是错误的：

UPDATE CS_KC

SET 学号=’081120’,课程号=’208’

WHERE 成绩=90

对于可更新的分区视图，则一次修改可以变动其依赖的多个基本表。

4.2.4 更新视图

【例4.60】将XS12_VIEW视图中的每个学生的总学分增加10。

UPDATE XS12_VIEW

SET 总学分 = 总学分+ 10

用SELECT语句分别查询视图XS12_VIEW所依赖的基本表XS1和XS2：

SELECT * FROM XS1

ELECT * FROM XS2

4.2.4 更新视图

4. 删除数据

【例4.61】删除CS_XS中女同学的记录。

DELETE FROM CS_XS

WHERE 性别 = 0

可以通过可更新的分区视图删除其所依赖的基本表数据。

【例4.62】删除XS12_VIEW中学号为“084001”的学生记录。

DELETE FROMXS12_VIEW

WHERE 学号 = '084001'

【例4.63】通过SQLServer Enterprise Manager的界面对视图XS12_VIEW进行如下操作：

① 增加一条记录（’084003’,’汪小波’,’外语’,1,50）。

② 将学号为083001的学生的总学分改为50。

③ 删除学号为083002的学生记录。

4.2.4 更新视图

第1步展开数据库和视图，在XS12_VIEW视图上单击鼠标右键，在弹出的快捷菜单上依次选择“打开视图”?“返回所有行”

打开视图

更新视图

第2步添加记录，在所出现的如图4.42所示的表“XS12_VIEW”窗口中添加新记录，输入新记录各字段的值。

第3步修改记录，定位到需修改的学号为“083001”行的总学分字段，删除原值48，输入新值50。

第4步删除记录，如图4.43所示，定位到需删除的学号为“083002”的行，单击鼠标右键，在弹出的快捷菜单上选择“删除”，将出现确认是否删除的对话框，在其中单击“确定”。

通过视图插入数据

4.2.4 更新视图

通过视图删除数据

4.2.5 修改视图的定义

修改视图定义可以通过SQL Server Enterprise Manager的界面进行，也可使用T-SQL的ALTER VIEW语句。

1. 通过SQL Server Enterprise Manager修改视图

第1步在SQL Server Enterprise Manager中展开数据库和视图，在需修改的视图上单击鼠标右键，在弹出的快捷菜单上选择“设计视图”，将出现如图4.44所示的窗口。

第2步在窗口中对视图定义进行修改，修改完后单击保存图标按钮即可。

注意：对加密存储的视图定义不能在SQL Server Enterprise Manager中通过界面修改，例如对视图CS_KC不能用此法修改。

修改视图定义

4.2.5 修改视图的定义

2. 使用ALTER VIEW语句修改视图

语法格式：

ALTER VIEW [<database_name> . ][ <owner> . ] view_name [ ( column_name [ ,…n ]) ]

[ WITH<view_attribute>[,…n ] ]

ASselect_statement

[ WITH CHECKOPTION ]

其中view_attribute、select_statement等参数与CREATEVIEW语句中含义相同。

【例4.64】将CS_XS视图修改为只包含计算机专业学生的学号、姓名和总学分。

USE XSCJ

ALTER VIEW CS_XS

SELECT 学号,姓名,总学分

FROM XS

WHERE 专业名 = '计算机'

4.2.5 修改视图的定义

注意：和CREATE VIEW一样，ALTERVIEW也必须是批命令中的第一条语句。

使用ENCRYPTION属性定义的视图（即加密存储视图）可以使用ALTER VIEW语句修改。

【例4.65】视图CS_KC是加密存储视图，修改其定义：包括学号、姓名、选修的课程号、课程名和成绩。

ALTER VIEW CS_KCWITH ENCRYPTION

SELECT XS.学号,XS.姓名,XS_KC.课程号,KC.课程名,成绩

FROM XS,XS_KC,KC

WHERE XS.学号 = XS_KC.学号 ANDXS_KC.课程号 = KC.课程号

AND 专业名 = '计算机'

WITH CHECK OPTION

4.2.6 删除视图

删除视图同样也可以通过企业管理器界面和T-SQL语句两种方式来实现。

在企业管理器中删除视图的操作方法是：展开数据库和视图，在需删除的视图上单击鼠标右键，在弹出的快捷菜单上选择“删除”，出现如图4.45所示的对话框，在其中点击“全部除去”按钮即可删除指定的视图。

删除视图

语法格式：

DROP VIEW { view }[ , …n ]

其中view是视图名，使用DROPVIEW一次可删除多个视图。例如：

DROP VIEW CS_KC

将删除视图CS_KC。

4.3.1 游标概念

在SQL Server中，有两类游标可以用于应用程序中：前端（客户端）游标和后端（服务器端）游标。服务器端游标是由数据库服务器创建和管理的游标，而客户端游标是由ODBC 和 DB-Library支持，在客户端实现的游标。在客户端游标中，所有的游标操作都在客户端高速缓存中执行。最初实现 DB-Library 客户端游标时 SQLServer 尚不支持服务器游标，而ODBC 客户端游标，是为了用于仅支持游标特性默认设置的 ODBC 驱动程序。由于 DB-Library 和 SQLServer ODBC 驱动程序完全支持通过服务器游标的游标操作，所以我们应尽量不使用客户端游标。SQL Sever 2000中对客户端游标的支持也主要是考虑向后兼容。本节除非特别指明，所说的游标均为服务器游标。

SQL Server对游标的使用要遵循声明游标?打开游标?读取数据?关闭游标?删除游标。

4.3.2 声明游标

T-SQL中声明游标使用DECLARE CURSOR语句，该语句有两种格式，分别支持SQL-92标准和T-SQL扩展的游标声明。

1. SQL-92语法

语句格式：

DECLAREcursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR

FORselect_statement

[ FOR { READ ONLY| UPDATE [ OF column_name [ ,…n ] ] } ]

说明：

cursor_name：游标名，它是与某个查询结果集相联系的符号名，要符合SQL Server标识符命名规则。

INSENSITIVE：指定系统将创建供所定义的游标使用的数据的临时复本，对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。如果省略 INSENSITIVE，则任何用户对基表提交的删除和更新都反映在后面的提取中。

SCROLL：说明所声明的游标可以前滚、后滚，可使用所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）。如果省略SCROLL，则只能使用NEXT提取选项。

4.3.2 声明游标

select_statement：SELECT语句，由该查询产生与所声明的游标相关联的结果集。该SELECT语句中不能出现COMPUTE、COMPUTE BY、INTO或FOR BROWSE关键字。

READ ONLY：说明所声明的游标为只读的。UPDATE指定游标中可以更新的列，若有参数OF column_name [ ,…n ]，则只能修改给出的这些列，若在UPDATE中未指出列，则可以修改所有列。

以下是一个符合SQL-92标准的游标声明：

DECLARE XS_CUR1CURSOR

FOR

SELECT 学号,姓名,性别,出生时间,总学分

FROM XS

WHERE 专业名 = '计算机'

FOR READ ONLY

该语句定义的游标与单个表的查询结果集相关联，是只读的，游标只能从头到尾顺序提取数据，相当于下面所讲的只进游标。

4.3.2 声明游标

2. T-SQL扩展

语句格式：

DECLAREcursor_name CURSOR

[ LOCAL | GLOBAL ]/*游标作用域*/

[ FORWORD_ONLY |SCROLL ] /*游标移动方向*/

[ STATIC | KEYSET| DYNAMIC | FAST_FORWARD ] /*游标类型*/

[ READ_ONLY |SCROLL_LOCKS | OPTIMISTIC ] /*访问属性*/

[ TYPE_WARNING ]/*类型转换警告信息*/

FOR select_statement/*SELECT查询语句*/

[ FOR UPDATE [ OFcolumn_name [ ,…n ] ] ] /*可修改的列*/

说明：

cursor_name: 为游标名。

LOCAL与GLOBAL：说明游标的作用域。

4.3.2 声明游标

FORWARD_ONLY和SCROLL：说明游标的移动方向。FORWARD_ONLY表示游标只能从第一行滚动到最后一行，即该游标只能支持FETCH的NEXT提取选项。SCROLL含义与SQL-92标准中相同。

STATIC | KEYSET |DYNAMIC | FAST_FORWARD：用于定义游标的类型，T-SQL扩展游标有四种类型：

(1) 静态游标

关键字STATIC指定游标为静态游标，它与SQL-92标准的INSENSITIVE关键字功能相同。静态游标的完整结果集在游标打开时建立在tempdb中，一旦打开后，就不再变化。数据库中所做的任何影响结果集成员的更改（包括增加、修改或删除数据），都不会反映到游标中，新的数据值不会显示在静态游标中。静态游标只能是只读的。由于静态游标的结果集存储在tempdb的工作表中，所以结果集中的行大小不能超过 SQL Server 表的最大行大小。有时也将这类游标识别为快照游标，它完全不受其他用户行为的影响。

(2) 动态游标

关键字DYNAMIC指定游标为动态游标。与静态游标不同，动态游标能够反映对结果集中所做的更改。结果集中的行数据值、顺序和成员在每次提取时都会改变，所有用户做的全部UPDATE、INSERT和DELETE语句均通过游标反映出来，并且如果使用API函数（如SQLSetPos）或Transact-SQL WHERE CURRENT OF 子句通过游标进行更新，则它们也立即在游标中反映出来，而在游标外部所做的更新直到提交时才可见。动态游标不支持 ABSOLUTE 提取选项。

4.3.2 声明游标

(3) 只进游标

关键字FAST_FORWARD定义一个快速只进游标，它是优化的只进游标。只进游标只支持游标从头到尾顺序提取数据。对所有由当前用户发出或由其它用户提交、并影响结果集中的行的INSERT、UPDATE和 DELETE语句对数据的修改在从游标中提取时可立即反映出来的。但因只进游标不能向后滚动，所以在行提取后对行所做的更改对游标是不可见的。

(4) 键集驱动游标

关键字KEYSET定义一个键集驱动游标。顾名思义，这种游标是由称为键的列或列的组合控制的。打开键集驱动游标时，其中的成员和行顺序是固定的。键集驱动游标中数据行的键值在游标打开时建立在 tempdb 中。可以通过键集驱动游标修改基本表中的非关键字列的值，但不可插入数据。

游标类型与移动方向之间的关系：

● FAST_FORWARD不能与SCROLL一起使用，且FAST_FORWARD与FORWARD_ONLY只能选用一个。

● 若指定了移动方向为FORWARD_ONLY，而没有用STATIC、KETSET或DYNAMIC关键字指定游标类型，则默认所定义的游标为动态游标。

● 若移动方向FORWARD_ONLY和SCROLL都没有指定，那么移动方向关键字的默认值由以下条件决定：① 若指定了游标类型为STATIC、KEYSET或DYNAMIC，则移动方向默认为SCROLL；②若没有用STATIC、KETSET或DYNAMIC关键字指定游标类型，则移动方向默认值为FORWARD_ONLY。

4.3.2 声明游标

READ_ONLY |SCROLL_LOCKS | OPTIMISTIC：说明游标或基表的访问属性。READ_ONLY说明所声明的游标为只读的，不能通过该游标更新数据。SCROLL_LOCKS关键字说明通过游标完成的定位更新或定位删除可以成功。如果声明中已指定了关键字 FAST_FORWARD，则不能指定SCROLL_LOCKS。OPTIMISTIC关键字说明如果行自从被读入游标以来已得到更新，则通过游标进行的定位更新或定位删除不成功。如果声明中已指定了关键字FAST_FORWARD，则不能指定OPTIMISTIC。

TYPE_WARNING：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。

select_statement：SELECT查询语句，由该查询产生与所声明的游标相关联的结果集。该SELECT语句中不能出现COMPUTE、COMPUTE BY、INTO或FOR BROWSE关键字。

FOR UPDATE：指出游标中可以更新的列，若有参数OF column_name [ ,…n ]，则只能修改给出的这些列，若在UPDATE中未指出列，则可以修改所有列。

以下是一个T-SQL扩展游标声明：

DECLARE XS_CUR2CURSOR

DYNAMIC

FOR

SELECT 学号,姓名,总学分

FROM XS

WHERE 专业名 = '计算机'

FOR UPDATE OF 总学分

该语句声明一个名为XS_CUR2的动态游标，可前后滚动，可对总学分列进行修改。

4.3.3 打开游标

声明游标后，要使用游标从中提取数据，就必须先打开游标。在T-SQL中，使用OPEN语句打开游标，其格式为：

OPEN { { [ GLOBAL] cursor_name } | cursor_variable_name }

其中cursor_name是要打开的游标名，cursor_variable_name是游标变量名，该名称引用一个游标。GLOBAL说明打开的是全局游标，否则打开局部游标。

OPEN语句打开游标，然后通过执行在DECLARE CURSOR（或 SETcursor_variable）语句中指定的T-SQL语句填充游标（即生成与游标相关联的结果集）。

例如，语句：

OPEN XS_CUR1

打开游标XS_CUR1。该游标被打开后，就可以提取其中的数据。

如果所打开的是静态游标（使用 INSENSITIVE 或 STATIC 关键字），那么OPEN将创建一个临时表以保存结果集。如果所打开的是键集驱动游标（使用KEYSET关键字），那么OPEN将创建一个临时表以保存键集。临时表都存储在tempdb中。

打开游标后，可以使用全局变量@@CURSOR_ROWS查看游标中数据行的数目。全局变量@@CURSOR_ROWS中保存着最后打开的游标中的数据行数。当其值为0时，表示没有游标打开；当其值为-1时，表示游标为动态的；当其值为-m（m为正整数）时，游标采用异步方式填充，m为当前键集中已填充的行数；当其值为m（m为正整数）时，游标已被完全填充，m是游标中的数据行数。

4.3.3 打开游标

【例4.66】定义游标XS_CUR3，然后打开该游标，输出其行数。

DECLARE XS_CUR3CURSOR

LOCAL SCROLLSCROLL_LOCKS

FOR

SELECT 学号,姓名,总学分

FROM XS

FOR UPDATE OF 总学分

OPEN XS_CUR3

SELECT '游标XS_CUR3数据行数' =@@CURSOR_ROWS

结果为：

游标XS_CUR3数据行数

说明：本例中的语句SELECT '游标XS_CUR3数据行数' = @@CURSOR_ROWS用于为变量赋值。

4.3.4 读取数据

游标打开后，就可以使用FETCH语句从中读取数据。

语法格式：

FETCH

[ [ NEXT | PRIOR |FIRST | LAST | ABSOLUTE { n | @nvar } | RELATIVE { n | @nvar} ]

FROM ]

{ { [ GLOBAL ]cursor_name } | @cursor_variable_name }

[ INTO@variable_name [ ,…n ] ]

4.3.4 读取数据

说明：

cursor_name：要从中提取数据的游标名，@cursor_variable_name游标变量名，引用要进行提取操作的已打开的游标。

ABSOLUTE { n |@nvar }和RALATIVE { n | @nvar }给出读取数据的位置与游标头或当前位置的关系，其中n必须为整型常量，变量@nvar必须为 smallint、tinyint 或 int类型。

ABSOLUTE { n |@nvar }：若n或@nvar为正数，则读取从游标头开始的第n行并将读取的行变成新的当前行；若n或@nvar为负数，则读取游标尾之前的第n行并将读取的行变成新的当前行；若n或@nvar 为 0，则没有行返回。

RALATIVE { n |@nvar }：若n或@nvar为正数，则读取当前行之后的第 n 行并将读取的行置新的当前行；若n或@nvar为负数，则读取当前行之前的第n行并将读取的行变成新的当前行；如果n或@nvar为 0，则读取当前行。如果对游标的第一次提取操作时将 FETCH RELATIVE中的n或@nvar指定为负数或0，则没有行返回。

4.3.4 读取数据

INTO：说明将读取的游标数据存放到指定的变量中。

GLOBAL：全局游标。

【例4.67】从游标XS_CUR1中提取数据。设该游标已经打开。

FETCH NEXT FROMXS_CUR1

结果为：

学号姓名性别出生时间总学分

081101王林 11990-02-10 00:00:0058

分析：由于XS_CUR1是只进游标，所以只能使用NEXT提取数据。

【例4.68】从游标XS_CUR1中提取数据。设该游标已经打开。

FETCH FIRST FROMXS_CUR2

---读取游标第一行（当前行为第一行），结果为：

学号姓名总学分

081101王林 58

FETCH NEXT FROMXS_CUR2

--读取下一行（当前行为第二行），结果为：

学号姓名总学分

081102程明 58

4.3.4 读取数据

FETCH PRIOR FROMXS_CUR2

--读取上一行（当前行为第一行），结果为：

学号姓名总学分

081101王林 58

FETCH LAST FROMXS_CUR2

--读取最后一行（当前行为最后一行），结果为：

学号姓名总学分

081115刘明仪 58

FETCH RELATIVE -2FROM XS_CUR2

--读取当前行的上二行（当前行为倒数第三行），结果为：

学号姓名总学分

081108林一帆 60

分析：XS_CUR2是动态游标，可以前滚、后滚，可以使用FETCH语句中的除ABSOLUTE以外的提取选项。

FETCH语句的执行状态保存在全局变量@@FETCH_STATUS中，其值为0，表示上一个FETCH执行成功；为-1，表示所要读取的行不在结果集中；为-2，表示被提取的行已不存在（已被删除）。可以测试@@FETCH_STATUS来控制循环。

4.3.4 读取数据

例如接着上例继续执行如下语句：

FETCH RELATIVE 3FROM XS_CUR2

SELECT 'FETCH执行情况' = @@FETCH_STATUS

结果为：

FETCH执行情况

-1

4.3.5 关闭游标

游标使用完以后，要及时关闭。关闭游标使用CLOSE语句，格式为：

CLOSE { { [ GLOBAL] cursor_name } | @cursor_variable_name }

语句参数的含义与OPEN语句中相同。例如：

CLOSE XS_CUR2

将关闭游标XS_CUR2。

4.3.6 删除游标

游标关闭后，其定义仍在，需要时可用OPEN语句打开它再使用。若确认游标不再需要，就要释放其定义占用的系统空间，即删除游标。删除游标使用DEALLOCATE语句，格式为：

DEALLOCATE { { [GLOBAL ] cursor_name } | @cursor_variable_name }

语句参数的含义与OPEN和CLOSE语句中相同。例如：

DEALLOCATE XS_CUR2

将删除游标XS_CUR2。

习题

1. 试说明SELECT语句的作用。说明SELECT语句的FROM、WHERE、GROUP及ORDER子句的作用。

2．查询内容是多个字段的运算结果，是否可以查询？

3．查询结果显示的列名是否可以与字段名不同？

4．查询内容与多个表有关，表与表之间如何关联？

5．经常需要查询的内容是否可以用视图完成？

6. 写出T-SQL语句，对产品销售数据库(CPXS)进行如下操作：

(1) 查询单价在2000—2900元之间的商品名。

(2) 统计所有商品总数。

(3) 查询2007年12月产品销售总额。

(4) 查询客户名为“XXY”的客户在2007年12月18日所购买的产品清单。

(5) 在产品销售数据库上创建冰箱产品表的视图BXCP。

(6) 在BXCP视图上查询库存量在100台以下的产品编号和库存量。

7．什么时候需要使用游标？使用游标的步骤。

转载于:https://www.cnblogs.com/javaTest/archive/2012/05/28/2589465.html

weixin_30851409

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库的查询与视图

第4章数据库的查询和视图4.1.1 选择列通过SELECT语句的<select_list>项组成结果表的列。<select_list>::=SELECT [ ALL |DISTINCT ] [ TOP n [ PERCENT ] [ WITH TIES ] ]{ * /*选择当前表或视图的所有列*/| { table_name |view_name...
复制链接

扫一扫