资源简介

(共49张PPT)
第七单元 索引和视图
任务7.1创建和管理索引
任务7.2创建与管理视图
7.1.1 情景描述
7.1.2问题分析
7.1.3 解决方案
7.1.4 知识总结
7.1.5 应用实践
任务7.1 创建和管理索引
在学生信息管理系统的实际应用中，课程信息量会随着时间越来越大，因此数据库开发人员需要提高查询课程信息的速度。在系统里经常需要按照课程名称和课程性质来查询课程信息，那么可以在“课程”表的“名称”字段和“课程性质”字段上创建索引。
7.1.1 情景描述
为了解决上述问题，需要完成以下任务：
1．写出在“课程”表的“课程名称”列上创建唯一非聚集索引“IX_课程_课程名称”的语句。
2．写出在“课程”表的“课程性质”列上创建非聚集索引“IX_课程_课程性质”的语句。
3．执行上述语句。
7.1.2问题分析
1．打开SQL Server Management Studio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“学生管理”；
2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；
3．在“查询编辑器”上输入以下代码：
7.1.3 解决方案
4．单击工具栏上的【执行】按钮，如图所示。
CREATE UNIQUE NONCLUSTERED INDEX IX_课程_课程名称
ON 课程(课程名称)
CREATE NONCLUSTERED INDEX IX_课程_课程类型
ON 课程(课程性质)
索引的用途
索引的分类
创建索引
查看索引
禁用索引
激活索引
重命名索引
删除索引
7.1.4 知识总结
索引是为了加快对表中数据的检索速度而创建的一种单独的、物理的数据结构。数据库中的索引类似于字典的部首查字法和拼音查字法，通过索引，可以使得应用程序像快速从字典中查找你要的字一样，而不必整个表中的记录一个个查看才能找到所需记录。
使用索引能够改善数据库的性能，主要有以下几个方面：
可以加快数据的查询速度。
唯一索引，可以保证记录的唯一性。
可以加快表与表之间的连接。
在排序、分组的时候，可以减少排序、分组的时间。
索引的用途
按照索引的存储结构，索引可以分为聚集索引和非聚集索引。
按照索引取值可以划分为唯一索引和非唯一索引。
索引的分类
使用CREATE INDEX语句创建索引的语法格式如下：
创建索引
CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX 索引名
ON 表名 (列名)
参数说明如下：
UNIQUE：指定创建唯一索引，如果省略，则表示创建非唯一索引。
CLUSTERED、NONCLUSTERED：指定创建聚集索引还是非聚集索引，两者只能取一个值，如果省略，则表示创建非聚集索引。
表名：指定索引存在的表。
列名：索引所在的列的名字，可以指定两个或多个列名。
CREATE CLUSTERED INDEX IX_学生_学号 ON 学生(学号)
【例7-1】在“学生”表的“学号”字段上建立聚集索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
在学生表上，学号是主键，创建主键的时候自动生成了名为“PK__学生__1CC396D20CBAE877”的聚集索引，一个表中聚集索引只能有一个，所以，再创建聚集索引就会报错。
可以删除主键产生的聚集索引，再执行语句。
CREATE UNIQUE NONCLUSTERED INDEX IX_学生_姓名 ON 学生(姓名)
【例7-2】在“学生”表的“姓名”字段上建立非聚集唯一索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
NONCLUSTERED关键字可以省略。
IX_学生_姓名是索引名字，这么命名的好处是以索引INDEX的缩写开头，中间是表名，最后是包含索引的字段名，这样可以达到见名识意的效果，可以看出索引所在的表名及创建索引的字段。
使用系统存储过程sp_helpindex查看表中包含索引的语法格式如下：
sp_helpindex 表名
查看索引
sp_helpindex 学生
【例7-3】查看“学生”表的索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
表名可以加单引号，也可以不加单引号。
在结果栏中显示索引的名字，索引的类型，创建索引的字段，其中第一行是【例7-2】创建的唯一非聚集索引，第二行是创建主键时自动生成的索引，第三行和第四行分别是在家庭联系电话和个人联系电话字段上创建唯一约束时自动产生的唯一非聚集索引。
如果索引被禁用，索引还存在，只是用户不能访问索引。禁用一个表的聚集索引，可以防止用户对数据进行访问，数据仍然存在表中，但用户不能对表中的数据进行操作。
使用带DISABLE子句的ALTER INDEX语句禁用索引，语法格式如下：
ALTER INDEX 索引名 ON 表名 DISABLE
禁用索引
ALTER INDEX PK__学生__1CC396D20CBAE877 ON 学生 DISABLE
【例7-4】禁用“学生”表 中名为“PK__学生__1CC396D20CBAE877” 的索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
索引名不加单引号。
禁用一个表的聚集索引，导致引用这个表的其他表上的外键约束也被禁用。
禁用一个表的聚焦索引，导致用户不能访问表中的数据，使用“SELECT * FROM 学生”命令来查看表中的数据，会报错“查询处理器无法生成计划，因为表或视图‘学生’的索引‘PK__学生__1CC396D20CBAE877” 的索引’被禁用”，要想访问表中的数据，则要激活聚集索引。
可以使用带REBUILD子句的ALTER INDEX语句来重新启用被禁止的索引，语法格式如下：
ALTER INDEX 索引名 ON 表名 REBUILD
激活索引
ALTER INDEX PK__学生__1CC396D20CBAE877 ON 学生 REBUILD
【例7-5】激活启用“学生”表中名为“PK__学生__1CC396D20CBAE877” 的索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
索引名不加单引号。
启用一个表的聚集索引，使用“SELECT * FROM 学生”命令，可以正常查看表中的数据。
索引名称在表中是唯一的，重命名索引的时候也要满足这一要求，语法格式如下：
sp_rename ‘表名.索引名’ , ’新索引名’,’INDEX’
重命名索引
sp_rename '学生.IX_学生_姓名' ,'ixstudentname' , 'INDEX'
【例7-6】把“学生”表中的名为“IX_学生_姓名”的索引改为“ixstudentname”。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
当不再需要一个索引时，为了节约存储空间，可以将其从数据库中删除。使用DROP INDEX语句删除索引的语法格式如下：
DROP INDEX 表名.索引名
参数说明如下：
表名：指定要删除的索引所在的表。
索引名：指定删除的索引，表名和索引名中间用点来连接，也可以一次删除多个索引，索引之间用逗号分隔。
删除索引
DROP INDEX 学生.ixstudentname
【例7-7】删除“学生”表的名为“ixstudentname”的索引。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
为了提高按照商品名称查询商品信息的效率，在“销售”数据库里“商品”表的“名称”字段创建索引“IX_商品_名称”，然后把索引名改为“ixproductname”，最后删除该索引。
1．打开SQL Server Management Studio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“销售”；
2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；
3．在“查询编辑器”上输入以下代码：
7.1.5 应用实践
CREATE INDEX IX_商品_名称 ON 商品(名称)
sp_rename '商品.IX_商品_名称','ixproductname','INDEX'
DROP INDEX 商品.ixproductname
注意：以上三条命令不能同时执行，要分三次单个执行。
7.2.1 情景描述
7.2.2问题分析
7.2.3 解决方案
7.2.4 知识总结
7.2.5 应用实践
任务7.2 创建和管理视图
为了对教师数据分类统计，数据库开发人员想要在“学生管理”数据库里显示学历为“博士研究生”的教师编号、教师姓名、性别、职称、学历、系部名称信息，并且要将结果长期保存在数据库中。
7.2.1 情景描述
为了完成上述问题，需要做以下任务：
1．要显示的信息在两张表中，需要写出连接查询的语句。
2．用创建视图的命令将连接查询的结果保存在视图中。
3．执行创建的命令。
4．查询视图中的数据。
7.2.2问题分析
7.2.3 解决方案
CREATE VIEW vwteacher
AS
SELECT 教师编号,教师姓名,性别,职称,学历,系部名称
FROM 教师 JOIN 系部 ON 教师.系部代码=系部.系部代码
WHERE 学历='博士研究生'
WITH CHECK OPTION
1．打开SQL Server Management Studio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“学生管理”；
2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；
3．在“查询编辑器”上输入以下代码：
4．单击工具栏上的【执行】按钮，如图所示。
5．在SQL编辑器上输入语句“SELECT * FROM vwteacher”，点击【执行】按钮，运行结果和生成视图的SELECT语句的结果一样。
视图的概念
创建视图
查看视图信息
修改视图
更新视图数据
删除视图
重命名视图
7.2.4 知识总结
视图是从一个或多个基本表通过SELECT语句导出来的虚拟表，具有数据表的特征。视图一旦定义，就可以像表一样进行查询、修改、删除和更新；但是视图是虚拟的表，并不真正保存数据，而是保存提取数据的相关命令。
视图中的数据来自定义视图的查询所引用的基本表，并且在引用视图时动态生成，当基本表中的数据发生变化时，从视图中查询出的数据也会随之改变，数据库表中数据的更改不会影响用户对数据库的使用，用户也不必了解复杂的数据库中的表结构，屏蔽了数据库的复杂性。
视图的概念
使用CREATE VIEW语句创建视图的语法规则如下：
创建视图
CREATE VIEW 视图名
WITH ENCRYPTION
AS SELECT语句
WITH CHECK OPTION
参数说明如下：
视图名：指定视图的名称，命名建议用vw开始。
WITH ENCYPTION：指定视图的创建文本被加密。
SELECT 语句：指定定义视图的SELECT语句，要求不能使用ORDER BY子句。
WITH CHECK OPTION：指定在视图上修改数据的时候要满足SELECT语句指定的限制条件，可以保证对视图中数据的修改仍然符合视图的定义。
CREATE VIEW vwstu
AS
SELECT 学号,姓名,性别,出生日期
FROM 学生
WHERE 性别='女'
【例7-8】创建一个名为vwstu的视图，视图中包含女生的学号、姓名、性别、出生日期。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwstu”，点击【执行】按钮，运行结果和生成视图的SELECT语句的查询结果相同。
CREATE VIEW vwscore
WITH ENCRYPTION
AS
SELECT xs.学号,姓名,kc.课程编号,课程名称,成绩
FROM 选课 AS xk , 学生 AS xs ,课程 AS kc
WHERE xk.学号=xs.学号 AND xk.课程编号 =kc.课程编号
【例7-9】创建一个名为vwscore的视图，视图中包含学号、姓名、课程编号、课程名称、成绩，要求视图的文本信息加密。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwscore”，点击【执行】按钮，运行结果和生成视图的SELECT语句的查询结果相同。
说明：
用户使用视图，就不需要了解复杂的表连接过程。
如果视图加密，则不能用sp_helptext来查看视图信息。
CREATE VIEW vwcourse
AS
SELECT *
FROM 课程
WHERE 课程性质='专业必修课'
WITH CHECK OPTION
【例7-10】创建一个名为vwcourse的视图，视图包含专业必修课程的信息，要求使用WITH CHECK OPTION子句。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwcourse”，点击【执行】按钮，运行结果和生成视图的SELECT语句的查询结果相同。
说明：
使用了WITH CHECK OPTION子句，更新视图的数据的时候，更新后的数据必须满足SELECT语句中WHERE子句指定的条件。
CREATE VIEW vwcompute
AS
SELECT 课程编号,MAX(成绩) AS '最高分' ,
MIN(成绩) AS '最低分' ,AVG(成绩) AS '平均分'
FROM 选课
GROUP BY 课程编号
【例7-11】创建一个名为vwcompute的视图，视图包含每门课程的最高分，最低分，平均分。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwcompute”，点击【执行】按钮，运行结果和生成视图的SELECT语句的结果一样。
说明：
视图中的计算列必须指定列名。
计算列是不能通过更新视图数据的方式修改的。
查看视图信息
sp_help 用于显示数据库对象的基本信息，基本语法格式如下：
sp_help 对象名
sp_helptext 用于显示数据库对象的定义信息，基本语法格式如下：
sp_helptext 对象名
sp_help vwscore
【例7-12】查看视图vwscore的名称、所有者、创建时间、以及视图的列名、数据类型、长度等信息。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
sp_helptext vwstu
【例7-13】查看视图vwstu的定义信息。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
sp_helptext vwscore
【例7-14】查看视图vwscore的定义信息。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
sp_helptext可以查看视图的定义文本信息。
vwscore视图在定义的时候用WITH ENCRYPTION加密，所以不能查看。
使用ALTER VIEW来修改视图的语法规则和创建视图的语法规则相同，如下所示：
修改视图
ALTER VIEW 视图名
WITH ENCRYPTION
AS SELECT语句
WITH CHECK OPTION
ALTER VIEW vwstu
AS
SELECT 学号,姓名,性别,出生日期,班级名称
FROM 学生 JOIN 班级 ON 学生.班级编号=学生.班级编号
WHERE 性别='女'
【例7-15】修改视图vwstu，视图包含女生的学号、姓名、性别、出生日期、班级名称。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwstu”，点击【执行】按钮，运行结果和生成视图的SELECT语句的结果一样。
说明：
修改的视图名称必须是已经存在的视图。
视图中包含的班级名称字段在班级表中，用两个表的连接实现视图的定义。
更新视图中数据的语法和更新表的数据的语法格式相同。视图中的数据来自于基本表，基本表中数据的改变会影响到视图，对视图中数据的更新也会影响到基本表，视图和基本表的数据是统一的，但是，对视图中数据更新的时候有以下限制：
如果一次更新视图中的数据来自于多个基本表，就不允许更新。由于视图的数据终究还是来自基本表，所以对视图中数据的更新，只能针对一个基本表中的数据进行。
如果视图中的某列数据是计算得到的列，也不允许更新。
更新视图数据
UPDATE vwstu SET 姓名='赵丹丹' WHERE 学号=12
【例7-16】更新视图vwstu的数据，将学号为12的学生的姓名改为“赵丹丹”。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③在SQL编辑器上输入语句“SELECT * FROM vwstu WHERE 学号=12”，点击【执行】按钮，在视图中，可以看到学号为12的学生的姓名更改为“赵丹丹”。
④在SQL编辑器上输入语句“SELECT * FROM 学生 WHERE 学号=12”，点击【执行】按钮，在学生表中，可以看到学号为12的学生的姓名更改为“赵丹丹”。
说明：
更新视图数据的命令和表中数据的更新命令相同。
对视图的一次更新只可以影响到一个基本表的数据，视图和基本表中的数据是相通的，如果修改学生表中的数据，通过视图查看到的就是修改后的新数据。
UPDATE vwstu SET 姓名='赵丹丹',班级名称='计算机'
WHERE 学号=12
【例7-17】更新视图vwstu的数据，将学号为12的学生的姓名改为“赵丹”，班级名称由原来的“计算机1402”改为“计算机1401”。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
③对视图的更新一次不能影响多个基本表，由于这次更新的数据姓名字段在学生表，班级名称字段在班级表，所以数据更新不成功。修改办法：在SQL编辑器上输入语句“UPDATE vwstu SET 姓名='赵丹' WHERE 学号=12”和语句“UPDATE vwstu SET 班级名称='计算机' WHERE 学号=12”，点击【执行】按钮。
④在SQL编辑器上输入语句“SELECT * FROM vwstu”，点击【执行】按钮，可以看出姓名和班级名称修改成功。
UPDATE vwcompute SET 最高分=99 WHERE 课程编号=1
【例7-18】更新视图vwcompute的数据，修改课程编号为1的最高分为99分。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：视图的列是计算列，则不能更新。
UPDATE vwcourse SET 课程性质='专业选修课'
WHERE 课程编号=1
【例7-19】更新视图vwcourse的数据，把课程编号为“1”的课程性质修改为“专业选修课”。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
说明：
视图vwcourse在定义的时候指定了WITH CHECK OPTION子句，则要求对视图数据的更新要满足vwcourse定义的时候指定的WHERE 条件，WHERE条件中指定视图是专业必修课的课程信息，那么修改视图数据的时候，更新后的数据如果不满足这个条件，则不能对数据进行更新。
如果视图不再需要，则要删除视图，以免浪费存储空间，DROP VIEW删除视图的语法规则如下：
删除视图
DROP VIEW 视图名的列表
参数说明如下：
视图名列表：指定视图的名称，必须是数据库中存在的视图名称，如果一次要删除多个视图，视图名之间用逗号分隔。
DROP VIEW vwscore ,vwcompute
【例7-20】删除视图vwscore和vwcompute。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
可以使用sp_rename来对视图进行改名，语法格式如下：
sp_rename 视图原名, 视图新名称
参数说明如下：
视图原名：指定数据库中存在的视图的名称。
视图新名称：指定视图更改后的名称。
重命名视图
sp_rename 'vwstu','vwgirlstu'
【例7-21】把名称为vwstu的视图重新命名为vwgirlstu。
①打开SQL Server Management Studio，在工具栏上单击“新建查询”按钮，打开SQL编辑器，编写如下代码：
②单击工具栏上的【执行】按钮，运行结果如图所示。
定义视图vwsales，视图包含顾客ID，顾客姓名，性别，联系方式，商品ID，商品名称，价格，类别名称。
1．打开SQL Server Management Studio，单击“对象资源管理器”中的“数据库”文件夹下的数据库“销售”；
2．单击工具栏上的“新建查询”命令，打开“查询编辑器”；
3．在“查询编辑器”上输入以下代码：
7.2.5 应用实践
4．单击工具栏上的【执行】按钮，如图所示。
5．在SQL编辑器上输入语句“SELECT * FROM vwsales”，点击【执行】按钮，可以查看视图中的数据和SELECT语句的结果相同。
CREATE VIEW vwsales
AS
SELECT xs.顾客ID,gk.姓名,gk.性别,gk.联系方式,
xs.商品ID,sp.名称,sp.价格,splx.类别名称
FROM 销售 xs JOIN 顾客 gk ON xs.顾客ID=gk.顾客ID
JOIN 商品 sp ON xs.商品ID=sp.商品ID
JOIN 商品类型 splx ON splx.类别ID=sp.类别ID
1．索引的概念。
2．索引的类型。
3．CREATE INDEX命令创建索引的语法。
4．sp_helpindex查看索引。
5．禁用索引和激活索引。
6．删除索引。
7．重命名索引。
8．CREATE VIEW命令创建视图的语法。
9．ALTER VIEW命令修改视图的语法。
10．查看视图。
11．重命名视图。
12．删除视图。
本章小结

展开更多......

收起↑