【SQL server】视图和索引的创建与管理

本实验数据来源课参照一下本专栏文章：

【SQL server】进行简单查询分组、连接查询子查询和汇总（含teaching数据库创建及实验拓展）_Deep-sea shark的博客-CSDN博客_sql 分组汇总

在SSMS中创建视图

视图是一张虚表，数据库中只存储视图的定义，而不存储视图对应的数据（除非是索引视图）。

视图可作为一种安全机制，通过使用视图可以集中、简化和定制用户的数据库显示，用户可以通过视图访问数据，而不被授予直接访问视图基础表的权限。

实验7-1在SSMS中创建视图

实验要求

在“teaching”数据库创建所有在年龄大于22岁（含22岁）的所有男生的学生信息视图，视图中

只显示学号、姓名、性别和年龄，视图命名为view_s。

实验步骤

（1）在“对象资源管理器”中展开“teaching”数据库，展开“视图”选项。右键单击“视图”选

项，在弹出的快捷菜单中选择“新建视图”菜单，此时弹出“添加表”对话框，选择“表”选项卡中的student表，然后单击“添加”按钮，就可以将其添加到视图设计界面中了。

（2）添加表后，单击“关闭”按钮，进入视图设计器。该界面分为四个子窗口，最上面的子窗口显

示添加的表结构，读者可以通过勾选列名前的复选框让某列显示在创建的视图中，此处选择sno，sname，ssex和sage四列。第二个子窗口显示用户选择的列、列的别名、表、是否输出、排序类型、排序顺序、筛选器等属性。通过设置第二个子窗口，可以进一步设置视图显示的内容，对显示内容进行筛选。根据实验要求，在第二个窗口“筛选器”中把ssex设置为“=男”，把sage设置为“>=22”。

使用T-SQL语句创建视图

实验要求

1．在“teaching”数据库创建“S_C_SC_XJ”视图，包括“信息计算”专业的学生的学号、姓名，和他们选修的课程号、课程名和成绩。

2．创建male_view视图，用于存放所有男生的学生信息，同时使用WITH CHECK OPTION选项。

实验步骤

在查询命令窗口中输入以下的T-SQL语句：

use teaching
go
create view S_C_SC_XJ
as
select student.sno,sname,COURSE.cno,cname,score
from student,sc,COURSE
where student.sno=sc.sno and sc.cno=COURSE.cno
go

3．单击单击（分析）按钮，分析有无语法错误，当在结果窗口中显示“命令已成功完成”时，表示创建视图的T-SQL语句没有语法错误。否则，如有语法错误，则要修改创建视图的T-SQL语句。当没有语法错误时，单击执行按钮，完成视图的创建。

4．在查询命令窗口中输入以下T-SQL语句，并执行。

create view male_view2  
as
select sno,sname,ssex,sage,en_time,specialty,grade
from student where ssex='男'
with check option

分析with check option的作用：

暂时不分析，自主完成。

使用T-SQL语句修改视图

实验要求

在“teaching”数据库，修改“S_C_SC_XJ”视图，要求包括每个学生的学号、姓名和选修的课程总数。

实验步骤

1.打开SSMS，单击“新建查询”按钮，打开查询命令窗口。

2.在查询命令窗口中输入以下T-SQL语句，并执行。

use teaching
go
alter view S_C_SC_XJ
as
    select student.sno,sname,count(sc.cno) as 课程总数
    from student,sc
    where student.sno=sc.sno
    group by student.sno,sname
go

执行结束后，通过查看并执行视图，可以明显看到视图发生了变化，与之前创建的视图明显的不同。

使用视图

实验要求

1．在查询窗口中查询View_1视图，统计男生的平均年龄。

2．分别通过male_view视图插入一条男生信息和一条女生信息。

实验步骤

1．在查询命令窗口中输入以下T-SQL语句，并执行。

USE teaching
SELECT avg(sage) as 平均年龄
FROM view_s
GO

可以看到，在View_1视图中男生的平均年龄为22岁。

2．在查询命令窗口中输入以下T-SQL语句，并执行。

INSERT INTO male_view VALUES ('20195301', '张三', '男', 20)
--该语句能顺利执行。
INSERT INTO male_view VALUES ('20195312', '李思', '女',17)
--请读者注意该语句将不能执行。

分析原因可知筛选器中的限制条件会限制视图的插入功能，必须满足要求才能插入成功。

消息 550，级别 16，状态 1，第 34 行
试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一个或多个结果行又不符合 CHECK OPTION 约束。
语句已终止。

这个报错主要意思就是，性别不符合约束条件。

删除视图

实验要求

1．在SSMS删除视图view_s

2．在查询窗口中执行T-SQL语句删除视图S_C_SC_XJ

实验步骤

1．在SSMS的“对象资源管理器”选中要删除的视图，单击右键,在弹出菜单中选择“删除”命令，进入“删除对象”对话框，单击“确定”按钮就能删除视图。

2．在查询命令窗口中输入以下T-SQL语句，并执行。

        USE teaching
        GO 
        DROP VIEW S_C_SC_XJ
        GO 

执行完成后刷新视图可以看见S_C_SC_XJ已经不见了，说明此时删除成功了。

在SSMS中创建带索引的视图

实验要求

在“teaching”数据库为“student”表创建在sname上的非聚集不唯一的索引。

实验步骤

1．启动SSMS，在“对象资源管理器”面板中，展开teaching数据库中的student表前面的“+”号，选中“索引”选项右击，在弹出的快捷菜单中选择“新建索引”命令。

2．选择“新建索引”命令，选择“非聚集索引”，进入“新建索引”对话框。由于student表中已经建立主键，自动建立了聚集索引，一个表只能有一个聚集索引，因此这里只能建非聚集索引。

3．在“新建索引”对话框中的“常规”选择页中可以创建索引，在“索引名称”文本框中输入索引名称，确定是否选择“唯一”复选框等。例如输入“索引名称”为“index_sname”。

4．通过选择索引设置按钮，可以为新建的索引添加、删除、移动索引列。例如，选择“添加”按钮，进入“添加索引列”窗口。选中“sname”列前的多选按钮，单击“确定”按钮即可添加一个按“sname”列升序排序的非聚集索引。再选择“确定”按钮，索引创建完成。

5．索引创建完成后，在SSMS的“对象资源管理器”面板中，选择创建了索引的表(student表)，展开student表的“索引”选顶前面的“+”号，就会出现新建的索引“index_sname”.

使用T-SQL创建视图

实验要求

在“teaching”数据库为“student”表,根据姓名sname列和专业specialty创建一个名为index_sname_specialty的唯一索引，要求姓名升序排序，专业降序排序。

实验步骤

1）打开SSMS，单击“新建查询”按钮，打开查询命令窗口。

1. 在查询命令窗口中输入以下T-SQL语句，并执行。

use teaching
go --nonclustered表示非聚集的
create unique nonclustered index index_name_specialty
on student(sname asc,sage desc)
go

创建索引视图。

实验要求

创建一个“female_view”视图，该视图用于显示女生的学生信息，并为该视图按“sno”升序创建一个具有唯一性的聚集索引。

实验步骤

1.创建视图，在查询命令窗口中输入以下T-SQL语句，并执行。

USE teaching 
GO
CREATE VIEW female_view
WITH schemabinding --模式绑定
AS
SELECT sno,sname,ssex,specialty FROM dbo.student
WHERE ssex='女'

2.创建索引，在查询命令窗口中输入以下T-SQL语句，并执行。

CREATE UNIQUE CLUSTERED INDEX index_female ON female_view(sno)

查看索引信息

使用T_SQL语句查看索引信息

在查询命令窗口中输入以下T-SQL语句，使用系统存储过程sp_helpindex，并执行。

USE teaching
GO
EXEC  sp_helpindex  student
或使用系统存储过程sp_help，
USE teaching
GO
EXEC sp_help student

删除索引

使用T-SQL语句删除“student”表中的“Index_sname”索引

在查询命令窗口中输入以下T-SQL语句，并执行。

USE teaching
DROP  INDEX student.Index_sname
GO

---

使用T-SQL语句创建触发器

在查询命令窗口中输入以下T-SQL语句并执行，将在student表上创建DML触发器NO_UP_S，禁止修改student表的数据。

use teaching
go
create trigger NO_UP_S on student
for update
as 
    print '禁止修改student表！'
    rollback
go

验证触发器，在查询窗口中输入修改sc表的语句：

update student
set sage=20
where sno='20190211'

由于此时发生了UPDATE事件，自动触发NO_UP_S触发器。触发器执行后，查询窗口的消息栏将显示触发器消息。

在查询命令窗口中输入以下T-SQL语句并执行，为course表中创建DML触发器C_NO_UP_CNOCNAME，禁止修改课程号cno和课程名cname字段。

use teaching 
go 
create trigger C_NO_UP_CNOCNAME on COURSE
for update
as    
    if update(cno) or update(cname)
    begin
        print '亲，不能修改课程号和课程名哦！'
        rollback
    end
go

在查询命令窗口中输入以下T-SQL语句并执行，为course表创建一个DML触发器C_XS，在插入和更新数据时自动显示提示信息。

use teaching
go 
create trigger C_XS on COURSE
after insert,update
as    
    print'你正在给course表插入或更新数据！'
go

在查询命令窗口中输入以下T-SQL语句并执行，为course表创建一个DML触发器C_PRINT，在插入和修改数据时，都会自动显示所有学生的信息。

use teaching
go
create trigger C_PRINT on COURESE
for insert,update
as 
    select * from COURSE
go

在查询命令窗口中输入以下T-SQL语句并执行，在学生表course上创建一个DELETE类型的触发器C_DEL_COU，删除数据时显示删除记录的个数。

use teaching
go
create trigger C_DEL_COU on COURSE
for delete 
as 
    declare @count varchar(50)
    select  @count=str(@@rowcount)+'个课程被删除'
    select  @count
return