《SQLServer数据库应用与维护》-第7讲 SQL Server对象管理2

上讲内容回顾4.1数据库的创建与管理

4.1.1创建数据库 4.1.2管理数据库4.2数据表的创建与管理4.2.1创建数据表4.2.2修改数据表4.2.3删除数据表4.3数据库完整性

4.3.1完整性 4.3.2约束上讲内容提问如何用T-SQL语句创建数据库，有哪些关键字？查看数据库信息的存储过程是？修改数据库的常用到关键字有？如何T-SQL语句创建数据表，有哪些关键字？数据完整性有哪三种类型？约束我们上节课一共讲了哪几个？

第四章SQLServer对象管理第四章SQLServer对象管理目的与要求：掌握SQLServer数据库的对象管理，包括创建数据库，创建数据表，数据完整性与约束，视图与索引等。重点与难点：创建数据库与数据表（重点）数据完整性与约束（难点）视图（重点）索引（难点）第7讲SQLServer对象管理24.4

视图

4.4.1视图概述 4.4.2创建视图4.4.3管理视图4.4.4利用视图管理数据4.5索引4.5.1索引概述4.5.2创建索引4.5.3管理索引★★4.4视图4.4

数据库的创建与管理视图概述创建视图管理视图利用视图管理数据4.4.1视图概述4.4.1

视图概述视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表，视图的结构和数据是对数据表进行查询的结果；视图的特点：1.视图能够简化用户的操作，从而简化查询语句；2.视图使用户能以多种角度看待同一数据，增加可读性；3.视图对重构数据库提供了一定程度的逻辑独立性；4.视图能够对机密数据提供安全保护；5.适当的利用视图可以更清晰的表达查询。4.4.1

视图概述

使用视图的注意事项：只能在当前数据库中创建视图；视图的命名必须遵循标识符命名规则，不可与表同名；如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。当视图引用基表或视图被删除，该视图也不能再被使用。不能在视图上创建全文索引，不能在规则、默认的定义中引用视图。一个视图最多可以引用1024个列。视图最多可以嵌套32层。4.4.2创建视图4.4.2

创建视图1.利用对象资源管理器创建视图2.利用T-SQL语句创建视图

4.4.2

创建视图1.利用对象资源管理器创建视图第一步：在SQLServerManagementStudio中创建视图的方法主要在视图设计器中完成。第二步：添加好各表后，单击关闭按钮关闭【添加表】对话框。以后需要用可以在关系图窗口的空白处右击。4.4.2

创建视图1.利用对象资源管理器创建视图第三步：在关系图窗口中，可以建立表与表之间的联系，只需要将相关联的字段拖动到要连接的字段上即可。第四步：在每个表列名前的复选框选择，可以设置视图需要输出的字段，在条件窗格里还可以设置要过滤的查询条件。4.4.2

创建视图1.利用对象资源管理器创建视图第五步：单击【执行SQL】执行按钮，运行select语句，查看运行结果。第六步：测试正常后，单击【保存】，在弹出的对话框中输入视图名称，完成视图的创建。4.4.2

创建视图2.利用T-SQL语句创建视图CREATEVIEW[schema_name.]view_name

[(column[,...n])]

[WITHENCRYPTION]

ASSELECT_statement

[WITHCHECKOPTION]参数说明如下：schema_name：视图所属架构名view_name：视图名column：视图中所使用的列名。WITHENCRYPTION：加密视图。WITHCHECKOPTION：指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。4.4.2

创建视图2.利用T-SQL语句创建视图参数说明如下：ASSELECT_statement：用来创建视图的SELECT语句。但对SELECT语句有以下的限制：①定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。

②不能使用COMPUTE或COMPUTEBY子句。（2012版本已经不用）

③不能使用ORDERBY子句。

④不能使用INTO子句。

⑤不能在临时表或表变量上创建视图。4.4.2

创建视图2.利用T-SQL语句创建视图1）创建基于一个或多个基表的视图。2）创建基于视图的视图。3）创建带有表达式的视图。4）创建分组视图。4.4.2

创建视图2.利用T-SQL语句创建视图——1）创建基于基表的视图【例1】创建一个视图（v_student），用于查看学生学号、姓名和性别信息，并修改其字段名。CREATE

VIEW

v_student

(sid,

sname,

sex)ASSELECT

学号,

姓名,

性别FROM

学生表4.4.2

创建视图【例2】建立计算机系选修了1号课程的学生视图stu_is_c1。CREATE

VIEW

stu_is_c1(学号,姓名,成绩)AS

SELECT

选课表.学号,

姓名,

成绩FROM

学生表,选课表WHERE

班级

like

'计%'

AND

课程号=

1AND

学生表.学号=选课表.学号2.利用T-SQL语句创建视图——1）创建基于基表的视图4.4.2

创建视图【例3】创建一个视图（v_Test），用于查看学生学号、姓名、课程和成绩信息，并用WITHENCRYPTION加密。CREATE

VIEW

v_TestWITH

ENCRYPTIONASSELECT

学生表.学号,姓名,课程名,成绩FROM

学生表,

课程表,

选课表WHERE

学生表.学号=选课表.学号AND

课程表.课程号=选课表.课程号2.利用T-SQL语句创建视图——1）创建基于基表的视图4.4.2

创建视图【例4】我们之前创建了一个计算机系选修1号课程的学生信息视图（stu_is_c1），要求建立成绩在90分以上的学生的视图stu_sc_grade。2.利用T-SQL语句创建视图——2）创建基于视图的视图CREATE

VIEW

stu_sc_gradeASSELECT

学号,姓名,成绩FROM

stu_is_c1WHERE

成绩>=904.4.2

创建视图【例5】定义一个反映学生“出生年份”的视图。2.利用T-SQL语句创建视图——3）创建带表达式的视图CREATE

VIEW

stu_year(学号,姓名,出生年份)AS

SELECT

学号,姓名,'2020-年龄'FROM

学生表4.4.2

创建视图【例6】将学生的学号及其平均成绩定义为一个视图(S_G)。2.利用T-SQL语句创建视图——4）创建分组视图CREATE

VIEW

S_G(学号,平均成绩)AS

SELECT

学号,AVG(成绩)FROM

选课表GROUP

BY

学号上机实验任务一实验4-2第一题：【视图管理】1、创建视图（1）、(2)25分钟4.4.3管理视图4.4.3

管理视图查看视图修改视图更名视图删除视图4.4.3

管理视图视图创建之后，可以利用SQLServerManagementStudio或者T-SQL语句对视图进行管理。

例如，在视图使用过程中，可能经常会发生基表改变，而使视图无法正常工作的情况，那么就需要重新修改视图的定义。另外，一个视图如果不再具有使用价值，则可以将其删除。4.4.3

管理视图查看视图除了利用对象管理器查看视图定义外，SQLServer还提供系统存储过程查看视图定义。sp_help

用于返回视图的特征信息。sp_helptext

查看视图的定义文本。sp_depends查看视图对表的依赖关系和引用的字段。4.4.3

管理视图查看视图【例7】查找S_G视图的详细信息。sp_help

S_GGO4.4.3

管理视图查看视图【例8】查找S_G视图的定义文本。sp_helptext

S_GGO4.4.3

管理视图查看视图【例9】查找S_G视图的依赖关系和引用的字段。sp_depends

S_GGO4.4.3

管理视图2.修改视图【例10】将v_student视图修改为只包含学生学号、姓名。alter

view

v_studentasselect

学号,姓名

from

学生表4.4.3

管理视图2.修改视图【例11】将stu_is_c1视图修改为只包含计算机系学生学号、姓名及年龄。alter

view

stu_is_c1asselect

学号,姓名,年龄from

学生表where

班级

like

'计%'

4.4.3

管理视图3.更名视图利用系统提供的存储过程sp_rename可以对视图进行重命名。【例12】将stu_is_c1视图的名称修改为stu_is_test。sp_rename

'stu_is_c1','stu_is_test','VIEW'GO4.4.3

管理视图4.删除视图当一个视图不再需要时，可对其进行删除操作，以释放存储空间。删除视图的语法格式如下：

DROPVIEWview_name[，．．．n]

其中，view_name为所要删除的视图的名称。【例13】删除视图v_studentDROP

VIEW

v_student上机实验任务二实验4-2第一题：【视图管理】4、修改视图5、删除视图20分钟4.4.4利用视图管理数据4.4.4

利用视图管理数据在创建视图之后，可以通过视图来对基表的数据进行管理。但是无论在什么时候对视图的数据进行管理，实际上都是在对视图对应的数据表中的数据进行管理。利用视图查询数据利用视图插入数据利用视图更新数据利用视图删除数据4.4.4

利用视图管理数据利用视图查询数据利用T-SQL语言查询视图数据。【例14】查询学号为“2011101”学生的课程成绩。（从v_Test视图中查找）select

*from

v_Testwhere

学号=

'2011101'

4.4.4

利用视图管理数据2.利用视图插入数据使用insert语句通过视图向基本表插入数据。由于视图不一定包括表中的所有字段，所以在插入记录时可能会遇到问题。视图中那些没有出现的字段无法显式插入数据，假如这些字段不接受系统指派的null值，那么插入操作将失败。4.4.4

利用视图管理数据2.利用视图插入数据【例15】向视图v_student中插入一个新的学生记录，学号为2019101，姓名为王明，年龄为20。insert

into

v_studentvalues(2019101,'王明',20)因为v_student视图没有年龄列。insert

into

v_studentvalues(2019101,'王明')√4.4.4

利用视图管理数据2.利用视图插入数据【例16】向视图stu_is_c1中插入一个新的学生记录，学号为2019127，姓名为王明，年龄为20。insert

into

stu_is_c1Values('2019127','王明',20)4.4.4

利用视图管理数据2.利用视图插入数据【例17】向视图stu_sc_grade中插入一个新的学生记录，学号为2019127，姓名为王明，成绩为60。insert

into

stu_sc_gradeValues('2019127',