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

【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 teachinggocreate view S_C_SC_XJasselect student.sno,sname,COURSE.cno,cname,scorefrom student,sc,COURSEwhere student.sno=sc.sno and sc.cno=COURSE.cnogo

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

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

create view male_view2asselect sno,sname,ssex,sage,en_time,specialty,gradefrom student where ssex='男'with check option

分析with check option的作用:

暂时不分析,自主完成。

使用T-SQL语句修改视图

实验要求

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

实验步骤

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

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

use teachinggoalter view S_C_SC_XJasselect student.sno,sname,count(sc.cno) as 课程总数from student,scwhere student.sno=sc.snogroup by student.sno,snamego

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

使用视图

实验要求

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

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

实验步骤

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

USE teachingSELECT avg(sage) as 平均年龄FROM view_sGO

可以看到,在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 teachingGO DROP VIEW S_C_SC_XJGO 

执行完成后刷新视图可以看见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 teachinggo --nonclustered表示非聚集的create unique nonclustered index index_name_specialtyon student(sname asc,sage desc)go

创建索引视图。

实验要求

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

实验步骤

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

USE teaching GOCREATE VIEW female_viewWITH schemabinding --模式绑定ASSELECT sno,sname,ssex,specialty FROM dbo.studentWHERE ssex='女'

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

CREATE UNIQUE CLUSTERED INDEX index_female ON female_view(sno)

查看索引信息

使用T_SQL语句查看索引信息

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

USE teachingGOEXECsp_helpindexstudent或使用系统存储过程sp_help,USE teachingGOEXEC sp_help student

删除索引

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

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

USE teachingDROPINDEX student.Index_snameGO


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

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

use teachinggocreate trigger NO_UP_S on studentfor updateas print '禁止修改student表!'rollbackgo

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

update studentset sage=20where 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 COURSEfor updateasif update(cno) or update(cname)beginprint '亲,不能修改课程号和课程名哦!'rollbackendgo

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

use teachinggo create trigger C_XS on COURSEafter insert,updateasprint'你正在给course表插入或更新数据!'go

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

use teachinggocreate trigger C_PRINT on COURESEfor insert,updateas select * from COURSEgo

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

use teachinggocreate trigger C_DEL_COU on COURSEfor delete as declare @count varchar(50)select@count=str(@@rowcount)+'个课程被删除'select@countreturn