SQL Server 数据库的查询和视图.pptx

《SQL Server 数据库的查询和视图.pptx》由会员分享，可在线阅读，更多相关《SQL Server 数据库的查询和视图.pptx（86页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、4.1 数据库的查询下面介绍SELECT语句，它是T-SQL的核心。语法主体格式如下：SELECT/*指定查询结果输出列*/INTO新表/*指定查询结果存入新表*/FROM,./*指定查询源：表或视图*/WHERE/*指定查询条件*/GROUPBY/*指定查询结果分组条件*/HAVING/*指定查询结果分组统计条件*/ORDERBY/*指定查询结果排序顺序*/第1页/共86页4.1.1 选择查询结果输出列1选择所有列选择所有列使用“*”表示选择一个表或视图中的所有列。【例4.1】查询pxscj数据库中xsb表的所有记录。T-SQL命令如下：USEpxscjSELECT*FROMxsb第2页/共

2、86页4.1.1 选择查询结果输出列执行结果如图4.1所示。第3页/共86页4.1.1 选择查询结果输出列2选择一个表中指定的列选择一个表中指定的列可选择一个表中的部分列，各列名之间要以逗号分隔。【例4.2】查询xsb表中计算机专业学生的学号、姓名和总学分。T-SQL命令如下：SELECT学号,姓名,总学分FROMxsbWHERE专业=计算机第4页/共86页4.1.1 选择查询结果输出列3定义列别名定义列别名当希望查询结果中的列使用自己选择的列标题时，可以AS更改列标题名，该列标题称为该列的别名。【例4.3】查询xsb表中计算机系同学的学号、姓名和总学分，查询结果中各列的标题分别指定为numb

3、er、name和mark。T-SQL命令如下：SELECT学号ASnumber,姓名ASname,总学分ASmarkFROMxsbWHERE专业=计算机执行结果如图4.2所示。第5页/共86页4.1.1 选择查询结果输出列也可以使用“列别名=表达式”更改列标题。例如：SELECTnumber=学号,name=姓名,mark=总学分FROMxsbWHERE专业=计算机当自定义的列标题中含有空格时，必须使用引号将标题括起来。例如：SELECTStudentnumber=学号,姓名ASStudentname,mark=总学分FROMxsbWHERE专业=计算机第6页/共86页4.1.1 选择查询结果

4、输出列4替换查询结果中的数据替换查询结果中的数据在对表进行查询时，有时希望对所查询的某些列得到的数据进行变换。要替换查询结果中的数据，则可使用CASE表达式，格式为：CASEWHEN条件1THEN表达式1WHEN条件2THEN表达式2ELSE表达式END第7页/共86页4.1.1 选择查询结果输出列【例4.4】查询xsb表中计算机系各同学的学号、姓名和总学分，对其总学分按以下规则进行替换，列标题更改为“等级”。若总学分为空值，则替换为“尚未选课”；若总学分小于50，则替换为“不及格”；若总学分在50与52之间，则替换为“合格”；若总学分大于52，则替换为“优秀”。T-SQL命令如下：SELEC

5、T学号,姓名,等级=CASEWHEN总学分ISNULLTHEN尚未选课WHEN总学分=50and总学分50分人数FROMxsbWHERE总学分50;GO第18页/共86页4.1.2 选择查询条件：WHERE子句1表达式比较表达式比较比较运算符用于比较两个表达式值，共有9个，分别是=（等于）、（小于）、（大于）、=（大于等于）、（不等于）、!=（不等于）、!（不大于）。比较运算的格式为：表达式1比较运算符表达式2其中，“表达式”是除text、ntext和image以外类型的表达式。【例4.11】查询xsb表中通信工程专业总学分大于等于42的同学的情况。T-SQL命令如下：SELECT*FROMx

6、sbWHERE专业=通信工程AND总学分=42第19页/共86页4.1.2 选择查询条件：WHERE子句2模式匹配模式匹配LIKE谓词用于指出字符串是否与指定的字符串相匹配，返回逻辑值TRUE或FALSE。格式为：表达式NOTLIKE模式串ESCAPE转义符说明：说明：（1）表达式：一般为字符串表达式，在查询语句中可以是列名。（2）模式串：可以使用通配符，表4.2列出了LIKE谓词可以使用的通配符及其说明。（3）转义符：应为有效的SQLServer字符，没有默认值，且必须为单个字符。当模式串中含有与通配符相同的字符时，应通过该字符前的转义符指明其为模式串中的一个匹配字符。使用ESCAPE可指定

7、转义符。（4）NOTLIKE：使用NOTLIKE与LIKE的作用相反。通 配 符说 明%代表倒数_（下画线）代表单个字符 指定范围（如a-f、0-9）或集合（如abcdef）中的任何单个字符指定不属于范围（如 a-f、0-9）或集合（如abcdef）的任何单个字符第20页/共86页4.1.2 选择查询条件：WHERE子句【例4.12】查询xsb表中姓“王”且单名的学生情况。T-SQL命令如下，执行结果如图4.9所示。SELECT*FROMxsbWHERE姓名LIKE王_第21页/共86页4.1.2 选择查询条件：WHERE子句【例4.13】查询xsb表中学号倒数第5个数字为9，且倒数第1个数在

8、15之间的学生学号、姓名及专业。T-SQL命令如下，执行结果如图4.10所示。SELECT学号,姓名,专业FROMxsbWHERE学号LIKE%9_1-5第22页/共86页4.1.2 选择查询条件：WHERE子句【例4.13】查询xsb表姓名中倒数第2个字为玉的学生学号、姓名及专业。T-SQL命令如下，执行结果如图4.10所示。SELECT学号,姓名,专业FROMxsbWHERE学号LIKE%玉_第23页/共86页4.1.2 选择查询条件：WHERE子句3范围比较范围比较（1）当要查询的条件是某个值的范围时，可以使用BETWEEN关键字。BETWEEN关键字指出查询范围，格式为：表达式NOTB

9、ETWEEN表达式1AND表达式2当不使用NOT时，若“表达式”的值在“表达式1”与“表达式2”之间（包括这两个值），则返回TRUE，否则返回FALSE；当使用NOT时，返回值刚好相反。【例4.14】查询xsb表中不在1995年出生的学生情况。T-SQL命令如下，执行结果如图4.11所示。SELECT学号,姓名,专业,出生时间FROMxsbWHERE出生时间NOTBETWEEN1995-1-1and1995-12-31也可使用下列命令：SELECT学号,姓名,专业,出生时间FROMxsbWHEREWHEREyear(出生时间)!=1995第24页/共86页4.1.2 选择查询条件：WHERE子

10、句图4.11执行结果第25页/共86页4.1.2 选择查询条件：WHERE子句（2）使用IN关键字可以指定一个值表，值表中列出所有可能的值，当与值表中的任一个匹配时，即返回TRUE，否则返回FALSE。格式为：表达式IN(表达式,)【例4.15】查询xsb表中专业为“软件工程”或“通信工程”学生情况。T-SQL命令如下：SELECT*FROMxsbWHERE专业IN(软件工程,通信工程)下列T-SQL命令等价：SELECT*FROMxsbWHERE专业=计算机OR专业=通信工程第26页/共86页4.1.2 选择查询条件：WHERE子句4空值比较空值比较当需要判定一个表达式的值是否为空值时，使用

11、ISNULL关键字，格式为：表达式ISNOTNULL当不使用NOT时，若表达式的值为空值，则返回TRUE，否则返回FALSE；当使用NOT时，结果刚好相反。【例4.16】查询总学分尚不定的学生情况。SELECT*FROMxsbWHERE备注ISNULL第27页/共86页4.1.2 选择查询条件：WHERE子句5子查询子查询1）IN子查询子查询IN子查询用于进行一个给定值是否在子查询结果集中的判断，格式为：表达式NOTIN(子查询)当表达式与子查询的结果表中的某个值相等时，IN谓词返回TRUE，否则返回FALSE；若使用了NOT，则返回的值刚好相反。【例4.17】查询选修了课程号为206的课程的

12、学生情况。T-SQL命令如下：SELECT*FROMxsbWHERE学号IN(SELECT学号FROMcjbWHERE课程号=206)本例中，先执行子查询：SELECT学号FROMcjbWHERE课程名=206第28页/共86页4.1.2 选择查询条件：WHERE子句【例4.18】查询未选修离散数学的学生学号和姓名。T-SQL命令如下：SELECT学号,姓名FROMxsbWHERE学号NOTIN(SELECT学号FROMcjbWHERE课程号IN(SELECT课程号FROMkcbWHERE课程名=离散数学)第29页/共86页4.1.2 选择查询条件：WHERE子句2）比较子查询）比较子查询这种

13、子查询可以认为是IN子查询的扩展，它使表达式的值与子查询的结果进行比较运算，格式为：表达式比较运算符ALL|SOME|ANY(子查询)其中，ALL、SOME和ANY说明对比较运算的限制。【例4.19】查询选修了离散数学的学生学号和姓名。T-SQL命令如下：SELECT学号FROMcjbWHERE课程号=(SELECT课程号FROMkcbWHERE课程名=离散数学);第30页/共86页4.1.2 选择查询条件：WHERE子句【例4.20】查询比所有计算机系的学生年龄都大的学生。T-SQL命令如下：SELECT*FROMxsbWHERE 出生时间ALL(SELECT出生时间FROMxsbWHERE

14、专业=计算机)【例4.21】查询206课程号成绩不低于101课程号最低成绩的学生学号。T-SQL命令如下：SELECT学号FROMcjbWHERE 课程号=206AND成绩!=50)ASstudentWHERE性别=1执行结果如图4.13所示。第37页/共86页4.1.3 指定查询对象：FROM子句【例4.28】在xsb表中查询在1995年1月1日以前出生的学生的姓名和专业。T-SQL命令如下，执行结果如图4.14所示。SELECTstudent.name,student.specialityFROM(SELECT*FROMxsbWHERE出生时间=80第41页/共86页4.1.4 连接（2）

15、多表连接。）多表连接。有时，用户所需要的字段来自两个以上的表，那么就要对两个以上的表进行连接，称之为多表连接。【例4.32】查询选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。T-SQL命令如下，执行结果如图4.16所示。SELECTxsb.学号,姓名,课程名,成绩FROMxsb,kcb,cjbWHERExsb.学号=cjb.学号ANDkcb.课程号=cjb.课程号AND课程名=计算机基础AND成绩=80第42页/共86页4.1.4 连接2以以JOIN关键字指定的连接关键字指定的连接T-SQL扩展了以JOIN关键字指定连接的表示方式，使表的连接运算能力有所增强。FRO

16、M子句的表示将多个表连接起来。格式如下：:=ON|CROSSJOIN|左表源CROSS|OUTERAPPLY右表源|()第43页/共86页4.1.4 连接说明：（1）：准备要连接的表。（2）：表示连接类型。格式为：:=INNER|LEFT|RIGHT|FULLOUTERJOIN其中，INNER表示内连接，OUTER表示外连接。（3）ON：用于指定连接条件，为连接的条件。（4）APPLY运算符：使用APPLY运算符可以为实现查询操作的外部表表达式返回的每个行调用表值函数。（5）CROSSJOIN：表示交叉连接。第44页/共86页4.1.4 连接因此，以JOIN关键字指定的连接有三种类型：内连接、

17、外连接、交叉连接。内连接。指定了INNER关键字的连接是内连接，内连接按照ON所指定的连接条件合并两个表，返回满足条件的行。【例4.33】查询每个学生的情况以及选修的课程情况。T-SQL命令如下：SELECT*FROMxsbINNERJOINcjbONxsb.学号=cjb.学号执行的结果将包含xsb表和cjb表的所有字段（不去除重复字段学号）。【例4.34】查询选修了206课程且成绩在80分以上的学生姓名及成绩。T-SQL命令如下：SELECT姓名,成绩FROMxsbJOINcjbONxsb.学号=cjb.学号WHERE课程号=206AND成绩=80第45页/共86页4.1.4 连接【例4.3

18、5】查询选修了“计算机基础”课程且成绩在80分以上的学生学号、姓名、课程名及成绩。T-SQL命令如下：SELECTxsb.学号,姓名,课程名,成绩FROMxsbJOINcjbJOINkcbONcjb.课程号=kcb.课程号ONxsb.学号=cjb.学号WHERE课程名=计算机基础AND成绩=80作为一种特例，可以将一个表与它自身进行连接，称为自连接。【例4.36】查询不同课程成绩相同的学生的学号、课程号和成绩。T-SQL命令如下：SELECTa.学号,a.课程号,b.课程号,a.成绩FROMcjbaJOINcjbbONa.成绩=b.成绩ANDa.学号=b.学号ANDa.课程号!=b.课程号执行

19、结果如图4.17所示。第46页/共86页4.1.4 连接外连接。指定了OUTER关键字的为外连接，外连接的结果表不但包含满足连接条件的行，还包括相应表中的所有行。外连接包括以下三种：LEFTOUTERJOIN（左外连接）：结果表中除了包括满足连接条件的行外，还包括左表的所有行。RIGHTOUTERJOIN（右外连接）：结果表中除了包括满足连接条件的行外，还包括右表的所有行。FULLOUTERJOIN（完全外连接）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行。第47页/共86页4.1.4 连接【例4.37】查询所有学生情况，以及他们选修的课程号。T-SQL命令如下，执行结果如图4.

20、18所示。SELECTxsb.*,课程号FROMxsbLEFTOUTERJOINcjbONxsb.学号=cjb.学号第48页/共86页4.1.4 连接【例4.38】查询被选修了的课程的选修情况和所有开设的课程名。SELECTcjb.*,课程名FROMcjbRIGHTJOINkcbONcjb.课程号=kcb.课程号第49页/共86页4.1.4 连接交叉连接。交叉连接实际上是将两个表进行笛卡儿积运算，结果表是由第一个表的每一行与第二个表的每一行拼接后形成的表，因此结果表的行数等于两个表的行数之积。【例4.39】列出学生所有可能的选课情况。T-SQL命令如下，执行结果如图4.19所示。SELECT学

21、号,姓名,课程号,课程名FROMxsbCROSSJOINkcb第50页/共86页4.1.5 指定查询结果分组方法：GROUP BY子句这里介绍ISO标准的GROUPBY子句。语法格式如下：GROUPBY|ROLLUP()|CUBE()|GROUPINGSETS()第51页/共86页4.1.5 指定查询结果分组方法：GROUP BY子句【例4.40】在pxscj数据库xsb表中分别用GROUPBYROLLUP、GROUPBYCUBE和GROUPBYGROUPINGSETS对（专业,性别）产生一个结果集。（1）采用）采用GROUP BY ROLLUP。SELECT专业,性别,COUNT(*)AS人

22、数FROMxsbGROUPBYROLLUP(专业,性别)（2）采用）采用GROUP BY CUBE。SELECT专业,性别,COUNT(*)AS人数FROMxsbGROUPBYCUBE(专业,性别)第52页/共86页4.1.5 指定查询结果分组方法：GROUP BY子句（3）采用）采用GROUP BY GROUPING SETS。执行结果如图4.20所示。SELECT专业,性别,COUNT(*)AS人数FROMxsbGROUPBYGROUPINGSETS(专业,性别)(a)(b)(c)图4.20 执行结果 第53页/共86页4.1.6 指定查询结果分组后筛选条件：HAVING子句使用GROUP

23、BY子句和聚合函数对数据进行分组后，还可以使用HAVING子句对分组数据做进一步的筛选。格式为：HAVING其中，与WHERE子句的查询条件类似，不过HAVING子句中可以使用聚合函数，而WHERE子句中不可以。【例4.41】查询平均成绩在85分以上的学生的学号和平均成绩。T-SQL命令如下，执行结果如图4.21所示。SELECT学号,AVG(成绩)AS平均成绩FROMcjbGROUPBY学号HAVINGAVG(成绩)=85第54页/共86页4.1.6 指定查询结果分组后筛选条件：HAVING子句【例4.42】查询选修课程超过2门且成绩都在80分以上的学生的学号。T-SQL命令如下：SELEC

24、T学号FROMcjbWHERE成绩=80GROUPBY学号HAVINGCOUNT(*)2第55页/共86页4.1.6 指定查询结果分组后筛选条件：HAVING子句【例4.43】查询通信工程专业平均成绩在85分以上的学生的学号和平均成绩。T-SQL命令如下，执行结果如图4.22所示。SELECT学号,AVG(成绩)AS平均成绩FROMcjbWHERE学号IN(SELECT学号FROMxsbWHERE专业=通信工程)GROUPBY学号HAVINGAVG(成绩)=85第56页/共86页4.1.7 指定查询结果排序顺序：ORDER BY子句在应用中经常要对查询的结果排序输出，如将学生成绩由高到低排序。

25、在SELECT语句中，使用ORDERBY子句对查询结果进行排序。格式为：ORDERBY排序表达式COLLATE排序名ASC|DESC第57页/共86页4.1.7 指定查询结果排序顺序：ORDER BY子句【例4.44】将通信工程专业的学生按出生时间先后顺序排序。T-SQL命令如下，执行结果如图4.23所示。SELECT*FROMxsbWHERE专业=通信工程ORDERBY出生时间第58页/共86页4.1.7 指定查询结果排序顺序：ORDER BY子句【例4.45】将计算机专业学生的“计算机基础”课程成绩按降序排列。T-SQL命令如下，执行结果如图4.24所示。SELECT姓名,课程名,成绩FR

26、OMxsb,kcb,cjbWHERExsb.学号=cjb.学号ANDcjb.课程号=kcb.课程号AND课程名=计算机基础AND专业=计算机第59页/共86页4.1.8 SELECT语句的其他语法1INTO使用INTO子句可以将SELECT查询所得的结果保存到一个新建的表中。INTO子句的格式为：INTO新表其中，“新表”是要创建的新表名。【例4.46】由xsb表创建“计算机学生”表，包括学号和姓名。T-SQL命令如下：SELECT学号,姓名INTO计算机学生FROMxsbWHERE专业=计算机第60页/共86页4.1.8 SELECT语句的其他语法同样再创建一个“通信工程学生”表。完成后，刷

27、新表后的对象资源管理器如图4.25所示。第61页/共86页4.1.8 SELECT语句的其他语法2UNION使用UNION子句可以将两个或多个SELECT查询的结果合并成一个结果集，其格式为：|()UNIONALL|()UNIONALL|()其中，和都是SELECT查询语句。使用UNION组合两个查询的结果集的基本规则是：（1）所有查询中的列数和列的顺序必须相同。（2）数据类型必须兼容。第62页/共86页4.1.8 SELECT语句的其他语法【例4.47】在“计算机学生”表和“通信工程学生”表中查询学号为191301和学号为221301的两位同学的姓名。T-SQL命令如下：SELECT*FRO

28、M计算机学生WHERE学号=191301UNIONALLSELECT*FROM通信工程学生WHERE学号=221301执行结果如图4.26所示。第63页/共86页4.1.8 SELECT语句的其他语法3EXCEPT和和INTERSECTEXCEPT和INTERSECT用于比较两个查询的结果，返回非重复值。语法格式如下：|()EXCEPT|INTERSECT|()其中，和都是SELECT查询语句。【例4.48】查询计算机专业女学生的信息。T-SQL命令如下：SELECT*FROMxsbWHERE专业=计算机EXCEPTSELECT*FROMxsbWHERE性别=1执行结果如图4.27所示。第64

29、页/共86页4.1.8 SELECT语句的其他语法【例4.49】查询总学分大于42的男学生信息。T-SQL命令如下：SELECT*FROMxsbWHERE总学分42INTERSECTSELECT*FROMxsbWHERE性别=1第65页/共86页4.1.8 SELECT语句的其他语法4CTE在SELECT语句的最前面可以使用一条WITH子句来指定临时结果集，语法格式如下：WITH,.SELECT.其中：:=表达式名(列名,.)AS(CTE查询定义)第66页/共86页4.1.8 SELECT语句的其他语法【例4.50】使用CTE从cjb表中查询选了101课程的学生学号、成绩，并定义新的列名为nu

30、mber、point。再使用SELECT语句从CTE和xsb中查询姓名为“王林”的学生学号和成绩情况。T-SQL命令如下，执行结果如图4.28所示。USEpxscjGOWITHcte_stu(number,point)AS(SELECT学号,成绩FROMcjbWHERE课程号=101)SELECTnumber,pointFROMcte_stu,xsbWHERExsb.姓名=王林ANDxsb.学号=cte_stu.number第67页/共86页4.2 视 图4.2.1 视图概念视图一经定义以后，就可以像表一样被查询、修改、删除和更新。使用视图有下列优点：（1）为用户集中数据，简化用户的数据查询和

31、处理。有时，用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户进行数据查询和处理。（2）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用。（3）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。（4）便于数据共享。各用户不必都定义和存储自己所需的数据，而可共享数据库的数据，这样，同样的数据只需存储一次。（5）可以重新组织数据以便输出到其他应用程序中。第68页/共86页4.2.2 创建视图1通过界面创建视图通过界面创建视图下面以在一个数据库中创建视图为例说明在“SSMS”中创

32、建视图的过程。【例4.51】在pxscj数据库学生表(xsb)中创建名为cxs视图，视图中仅仅包含计算机专业学生。1）创建名）创建名cxs视图视图步骤如下：（1）在“对象资源管理器”中展开“数据库”“pxscj”，选择其中的“视图”项，右击鼠标，在弹出的快捷菜单上选择“新建视图”菜单项。（2）在随后出现的“添加表”对话框中，添加视图所需要关联的对象。这里在“表”选项卡中选择“xsb”表，单击“添加”按钮，单击“关闭”按钮。第69页/共86页4.2.2 创建视图（3）在窗口下部“输出”栏中去除创建的视图中不所需的字段，在“排序类型”栏中指定列的排序方式，在“筛选器”栏中指定创建视图的规则。本例在

33、“专业”字段的“筛选器”栏中填写“计算机”，如图4.29所示。（4）完成后，单击面板上的“保存”按钮，在“保存视图”对话框中输入视图名“cxs”，并单击“确定”按钮，便完成了视图的创建。第70页/共86页4.2.2 创建视图2）显示）显示cxs视图视图刷新pxscj数据库下的视图，展开“数据库”“pxscj”“视图”，选择“dbo.cxs”，右击鼠标，在弹出的快捷菜单中选择“设计”菜单项，可以查看并修改视图结构，选择“编辑前200行”菜单项，将可查看视图数据，如图4.30所示。第71页/共86页4.2.2 创建视图3）视图的好处）视图的好处创建视图可以向最终用户隐藏复杂的表连接，简化了用户的S

34、QL程序设计。通过在创建视图时指定限制条件和指定列来限制用户对基本表的访问。例如，若限定某用户只能查询视图cxs，实际上就是限制了它只能访问xsb表的专业字段值为“计算机”的行。第72页/共86页4.2.2 创建视图2通过命令创建视图通过命令创建视图T-SQL中用于创建视图的语句是CREATEVIEW语句，其格式为：CREATEVIEW视图名WITH,.ASSELECT语句;WITHCHECKOPTION1）语句主体）语句主体主体结构说明如下。（1）架构名：数据库架构名。（2）列名：视图中包含的列，可以有多个列名。若使用与源表或视图相同的列名，则不必给出列名。（3）WITH：指出视图的属性。（

35、4）SELECT语句：用来创建视图的语句，源表可以是基本表，也可以是视图。（5）WITHCHECKOPTION：指出在视图上所进行的修改都要符合SELECT语句所指定的限制条件，这样可以确保数据修改后，仍可通过视图看到修改的数据。第73页/共86页4.2.2 创建视图2）定义定义定义的具体格式如下：:=ENCRYPTIONSCHEMABINDINGVIEW_METADATA（1）ENCRYPTION：在系统表syscomments中存储CREATEVIEW语句时进行加密。（2）SCHEMABINDING：将视图与其所依赖的表或视图结构相关联。（3）VIEW_METADATA：当引用视图的浏览模

36、式的元数据时，向DBLIB、ODBC或OLEDBAPI返回有关视图的元数据信息，而不返回基表的元数据信息。第74页/共86页4.2.2 创建视图【例4.52】创建ccj视图，包括计算机专业各学生的学号、其选修的课程号及成绩。要保证对该视图的修改都符合专业为计算机的这个条件。T-SQL命令如下：CREATEVIEWccjWITHENCRYPTIONASSELECTxsb.学号,课程号,成绩FROMxsb,cjbWHERExsb.学号=cjb.学号AND专业=计算机WITHCHECKOPTION【例4.53】创建计算机专业学生的平均成绩视图ccj_avg，包括学号（在视图中列名为num）和平均成绩

37、（在视图中列名为score_avg）。T-SQL命令如下：CREATEVIEWccj_avg(num,score_avg)ASSELECT学号,AVG(成绩)FROMccjGROUPBY学号第75页/共86页4.2.2 创建视图3）分区视图在一般情况下，如果视图为下列格式，则称其为分区视图。CREATEVIEW视图名ASSELECTFROMT1UNIONALLSELECTFROMT2UNIONALL.SELECTFROMTn第76页/共86页4.2.2 创建视图3视图查询视图查询视图定义后，就可以像查询基本表那样对视图进行查询了。【例4.54】显示ccj和ccj_avg视图中的内容。T-SQL

38、命令如下：SELECT*FROMccj执行结果如图4.31(a)所示。SELECT*FROMccj_avgWHEREscore_avg=75执行结果如图4.31(b)所示。第77页/共86页4.2.3 更新视图1可更新视图可更新视图（1）满足以下条件的视图。）满足以下条件的视图。创建视图的SELECT语句中没有聚合函数，且没有TOP、GROUPBY、UNION子句及DISTINCT关键字。创建视图的SELECT语句中不包含从基本表列通过计算所得的列。创建视图的SELECT语句的FROM子句中至少要包含一个基本表。（2）可更新的分区视图。）可更新的分区视图。在实现分区视图之前，必须先实现水平分区

39、表。原始表被分成若干个较小的成员表，每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。（3）通过）通过INSTEAD OF触发器创建的可更新视图。触发器创建的可更新视图。第78页/共86页4.2.3 更新视图2插入数据使用INSERT语句通过视图向基本表插入数据。【例4.55】向cxs视图中插入以下记录：191315,刘明仪,1,1996-3-2,50T-SQL命令如下：INSERTINTOcxsVALUES(191315,刘明仪,1,1996-3-2,50)执行结果如图4.32所示。第79页/共86页4.2.3 更新视图3修改数据使

40、用UPDATE语句可以通过视图修改基本表的数据。（1）对一个基本表视图修改数据。【例4.56】将cxs视图中所有学生的总学分增加1。T-SQL命令如下：UPDATEcxsSET总学分=总学分+1该语句实际上是将cxs视图所依赖的基本表xsb中所有专业为“计算机”的记录的总学分字段值在原来的基础上增加1。UPDATEcxsSET总学分=总学分-1第80页/共86页4.2.3 更新视图（2）一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。【例4.57】将ccj视图中学号为191301的学生的101课程成绩改为90。T-SQL命令如下：UPDATEccjSET成绩=90WHERE

41、学号=191301AND课程号=101因为视图ccj依赖于两个基本表（xsb和cjb），对该视图的一次修改只能改变学号（源于xsb表）或者课程号和成绩（源于cjb表）。以下的修改是错误的：UPDATEccjSET学号=191320,课程号=208WHERE学号=191301AND课程号=101第81页/共86页4.2.3 更新视图4删除数据删除数据使用DELETE语句可以通过视图删除基本表的数据。但要注意，对于依赖于多个基本表的视图，不能使用DELETE语句。【例4.58】删除cxs中女同学的记录（实际不操作）。T-SQL命令如下：DELETEFROMcxsWHERE性别=0第82页/共86页

42、4.2.4 修改视图的定义1以界面方式修改视图以界面方式修改视图在“对象资源管理器”中右击视图“dbo.cxs”，在弹出的快捷菜单中选择“设计”菜单项，进入视图修改窗口。该窗口与创建视图的窗口类似，其中可以查看并可修改视图结构，修改完后单击“保存”图标即可。2使用命令修改视图使用命令修改视图语法格式：ALTERVIEW视图名WITH,.ASSELECT语句;WITHCHECKOPTION第83页/共86页4.2.4 修改视图的定义（1）修改视图定义。【例4.59】将cxs视图修改为只包含计算机专业学生的学号、姓名和总学分。T-SQL命令如下：ALTERVIEWcxsASSELECT学号,姓名,

43、总学分FROMxsbWHERE专业=计算机（2）修改使用ENCRYPTION属性定义的视图。【例4.60】视图ccj是加密存储视图，修改其定义：包括学号、姓名、选修的课程号、课程名和成绩。T-SQL命令如下：ALTERVIEWccjWITHENCRYPTIONASSELECTxsb.学号,xsb.姓名,cjb.课程号,kcb.课程名,成绩FROMxsb,cjb,kcbWHERExsb.学号=cjb.学号ANDcjb.课程号=kcb.课程号AND专业=计算机WITHCHECKOPTION第84页/共86页4.2.5 删除视图1通过对象资源管理器删除视图通过对象资源管理器删除视图在对应数据库中的“视图”下选择需要删除的视图，右击鼠标，在弹出的快捷菜单上选择“删除”菜单项，出现“删除”对话框，单击“确定”按钮即删除了指定的视图。2以以T-SQL命令方式删除视图命令方式删除视图语法格式：DROPVIEW视图名.,使用DROPVIEW可删除一个或多个视图。例如：DROPVIEWcxs,ccj将删除视图cxs和ccj（实际不操作）。第85页/共86页谢谢您的观看！第86页/共86页