数据库应用技术教程 课件 第5、6章 SQL 2022中视图和索引的应用、数据库编程技术基础

知识回顾查询没有选修的1号课程的学生的学号和姓名。第5章SQL2022中视图和索引的应用学习目标理解视图的作用；掌握视图的概念、特点和类型；掌握创建视图、修改视图和删除视图的方法；掌握查看和加密视图定义文本；掌握通过视图修改基表中的数据；掌握使用图形工具管理视图口。理解索引的优点和缺点；了解聚集索引和非聚集索引的特点；掌握索引与约束的关系；掌握使用CREATEINDEX语句创建索引的方式；掌握查看、删除和修改索引；掌握分析和维护索引。5.1.1视图概述

视图的定义：视图是一种常用的数据库对象，可以把它看成从一个或几个基本表导出的虚表或存储在数据库中的查询。视图概述-视图的作用

简化操作提高数据安全性屏蔽数据库的复杂性数据即时更新说明：视图一经定义后，就可以像基本表一样可以被查询、删除。视图为查看和存取数据提供了另外一种途径。5.1.2创建视图使用ManagementStudio使用CreateView视图设计器关系图窗格条件窗格SQL窗格结果窗格

创建视图--使用ManagementStudio【例5.1】创建视图Stu_sc1，要求显示学生的学号、姓名、性别和选课的课程号、成绩。

创建视图--使用CreateView语法格式：CREATEVIEW视图名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]参数说明如下｡Column：表示视图中的列名。WITHENCRYPTION：对包含CREATEVIEW语句文本的条目进行加密。AS：表示视图要执行的操作。select_statement：定义视图的SELECT语句。WITHCHECKOPTION：强制针对视图执行的所有数据修改语句都必须符合在select_statement中设置的条件。创建视图--使用CreateView（续）【例5.2】在学生选课数据库中，建立信息系学生的的学号、姓名、性别和年龄视图。CREATEVIEWIS_Student AS SELECTSno,Sname,Ssex,Sage FROMStudentWHERESdept=‘信息系'创建视图--使用CreateView（续）【例5.3】在学生选课数据库中，创建学生选课视图stu_sc2，视图包含学生学号、姓名、课程号、成绩，并对创建视图文本进行加密。代码如下：Createviewstu_sc2(学号,姓名,课程号,成绩)WithencryptionasSelectStudent.sno,sname,cno,gradeFromstudentinnerjoinscOnStudent.sno=sc.sno练习1创建一个视图，使其统计每个学生的考试平均成绩和修课总门数。练习2创建一个包含每门课程的课程号，课程名，选课人数，平均成绩的视图。练习3创建每个系的平均年龄的视图sdept_avg_sage。在创建或使用视图时的限制情况如果视图中某一列是函数、数学表达式、常量或来自多个表的列名相同，则必须为列定义名字。当通过视图操作数据时，SQLServer不仅要检查视图引用的表是否存在，是否有效，而且还要验证对数据的修改是否违反了数据的完整性约束。5.1.3视图的管理修改视图删除视图查看视图应用视图修改视图

通过ManagementStudio

使用ALTERVIEW语句修改视图语法格式如下。ALTERVIEW视图名[(column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]修改视图（续）【例5.4】将例5.1中的视图Stu_sc1，修改为男学生选课视图。代码如下：AlterviewStu_sc1AsSELECTstudent.Sno学号,Sname姓名,Ssex性别,Cno课程号,Grade成绩FROMscINNERJOINstudentONsc.Sno=student.SnoWhereSsex='男'删除视图使用Managementstudio使用DROPVIEW语句语法格式如下。DROPVIEW视图名[,…n]【例5.5】删除视图sc_count视图。代码如下：

DROPVIEWsc_count查看视图系统存储过程sp_help

用来返回有关数据库对象的详细信息，如果不针对某一特定对象，则返回数据库中所有对象信息。系统存储过程sp_depends

返回系统表中存储的任何信息，该系统表指出该对象所依赖的对象。系统存储过程sp_helptext（若试图加密了，就无法看了）检索出视图､触发器､存储过程的文本。5.1.4视图的应用1、利用视图查询数据【例5.6】在学生选课数据库中，查询平均年龄超过22岁的系别和平均年龄。代码如下：USE学生选课GOSELECT系别名称,平均年龄Fromsdept_avg_sageWhere平均年龄>22视图的应用2、利用视图更新数据【例5.7】在学生选课数据库中，利用已有视图IS_Student(sno,sname,ssex,sage,sdept)，增加一个新的女同学“李娜”，年龄22，信息系，学号“95088”。Insertintois_studentvalues(‘95088’,‘李娜’,‘女’,22,‘信息系')5.2索引-索引的作用

索引是一种重要的数据对象，它由一行行的记录组成，而每一行记录都包括数据表中一列或若干列值的集合，而不是数据表中的所有记录，因而能够提高数据的查询效率。此外，索引还可以用来确保列的惟一性，从而保证数据的完整性。索引的分类

聚集索引非聚集索引惟一索引包含性列索引索引视图全文索引XML索引其中，聚集索引和非聚集索引是数据库引擎最基本的索引索引的分类1、聚集索引（也称簇索引或簇集索引）在聚集索引中，表中的行的物理存储顺序和索引顺序完全相同(类似于图书目录和正文内容之间的关系)。聚集索引对表的物理数据页，按列进行排序，然后再重新存储到磁盘上。2、非聚集索引（也称非簇索引或非簇集索引）非簇索引具有与表的数据行完全分离的结构，非聚集索引的叶节点存储了组成非聚集索引的关键字值和一个指针，指针指向数据页中的数据行，该行具有与索引键值相同的列值，非聚集索引不改变数据行的物理存储顺序，因而一个表可以有多个非聚集索引。索引的分类3、惟一索引如果为了保证表或视图的每一行在某种程度上是惟一的，可以使用惟一索引，也就是说索引值是惟一的。创建数据表时如果设置了主键，则SQLServer2022就会默认建立一个惟一索引。4、包含性列索引使用包含性列索引，可以通过将非键列添加到非聚集索引的叶级来扩展其功能，创建覆盖更多查询的非聚集索引。索引的分类5、视图索引视图索引是为视图创建的索引。其存储方法与带聚集索引的表的存储方法相同。6、全文索引全文索引是一种特殊类型的基于标记的功能性索引，由MicrosoftSQLServer全文引擎(MSFTESQL)服务创建和维护。7、XML索引

XML索引是XML数据关联的索引形式，是XML二进制BLOB的已拆分持久表示形式，可分为主索引和辅助索引。索引和约束的关系

对列定义PRIMARYKEY约束和UNIQUE约束时，会自动创建索引。1、PRIMARYKEY约束和索引如果创建表时，将一个特定列标识为主键，自动对该列创建PRIMARYKEY约束和惟一聚集索引。2、UNIQUE约束和索引默认情况下，创建UNIQUE约束，自动对该列创建惟一非聚集索引。当用户从表中删除主键约束或惟一约束时，创建在这些约束列上的索引也会被自动删除。3、独立索引使用CREATEINDEX语句或SQLServerManagementStudio对象资源管理器中的【新建索引】对话框创建独立于约束的索引创建索引使用ManagementStudio使用CREATEINDEX语句CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]/*索引的类型*/INDEX索引名ON{表名|视图名}列名[ASC|DESC][,...n])创建索引【例5.8】在学生表上创建学生学号的聚集索引。操作步骤如下。（1）启动ManagementStudio。（2）在【对象资源管理器】中，展开【学生选课】|【表】|dbo.Student】|【索引】。在【索引】节点下，可以发现系统已默认依据设置的主键自动产生了一个聚集索引“PK_student”。说明：当用户在Student表中创建主键约束，则SQLServer2022数据库引擎自动对该列创建PRIMARYKEY约束和惟一聚集索引。创建索引【例5.9】在学生选课数据库中，经常要使用学生的姓名进行查询，为提高查询效率，请创建姓名列为非聚集索引。代码如下：CREATEINDEXIX_snameONstudent(sname)删除索引使用ManagementStudio删除独立于约束的索引使用DROPINDEX语句删除独立于约束的索引【例5.10】删除student表的索引Sname_index。Dropindexstudent.Sname_index说明：由于PK_student聚集索引是由student表在创建主键约束时自动创建的索引，所以无法利用DROPINDEX语句删除索引。查看索引使用ManagementStudio用系统存储过程sp_helpindex

可以返回表的所有索引信息，它的语法结构如下。

sp_helpindex[@objname=]’name’重命名索引

利用系统存储过程Sp_rename更改索引的名称，语法格式如下。

Sp_rename'表名.原索引名称','新索引名称'第6章数据库编程技术基础上节知识回顾视图是什么？索引有什么用处？学习目标正确理解和掌握使用SQLServer变量；掌握编写顺序结构、选择结构和循环结构的程序；掌握SQLServer函数的使用；掌握SQLServer游标的使用。6、数据库编程技术基础6.1SQL编程基础6.2流程控制语句6.3函数6.4游标1.注释在Transact-SQL中，注释语句有“--”（双减号）和“/*…*/”两种表示方法。（1）嵌入行内的注释语句（2）块注释语句2.变量变量是被赋予一定的值的语言元素。在T-SQL中，变量分为全局变量和局部变量:全局变量：@@开始的变量局部变量：以@开始的变量。全局变量是由系统提供且预先声明的变量，用户一般只能查看不能修改全局变量的值。局部变量是用户用以保存特定类型的单个数据值的对象，它局部于一个语句批。变量的声明在SQLServer中，局部变量必须先声明，再使用。声明变量的语句格式：

DECLARE@局部变量名数据类型变量名最多可以包含128个字符。局部变量的数据类型可以是系统数据类型，也可以是用户自己定义的数据类型，但不能是text或image类型。使用DECLARE语句声明一个局部变量后，变量的值将被初始化为NULL。变量的赋值变量的赋值语句为：

SET@局部变量名=值|表达式

SELECT@局部变量名=值|表达式SET语句是对局部变量赋值的首选方法。说明：变量只能出现在使用常数的位置上。在标准的SQL语句中，变量不能用在表、字段或其他数据库对象的名称的位置上，也不能用在关键字的位置上。示例声明三个整型变量：@x、@y和@z，并给@x、@y变量分别赋予一个初值，然后将这两个变量的和值赋给@z，并显示变量@z的结果。 DECLARE@xint,@yint,@zint SET@x=10 SET@y=20 SET@z=@x+@y Print@z3.PRINT语句作用：将信息显示在显示器上。语法格式：PRINT字符串常量|@局部变量名|字符串表达式@局部变量名：是任意有效的字符类型的变量，此变量必须是char（或nchar）或varchar（或nvarchar）型的变量。字符串表达式：返回字符串的表达式。可包含串联的字面值和变量。消息字符串最多可有8000个字符，超过8000个字节的任何字符均被截断。6.2流程控制语句用于控制程序的流程，一般分为三类：顺序分支循环SQLServer2022也提供对这三种流程控制的支持。T-SQL提供的主要流程控制语句语

句描

述BEGIN…END定义语句块BREAK退出最内层的

WHILE循环CONTINUE重新开始

WHILE循环GOTO标签从标签所定义的标签之后的语句处继续进行处理IF…ELSE如果指定条件为真，执行一个分支，否则执行另一个分支RETURN无条件退出WHILE当指定条件为真时重复一些语句1.BEGIN…END语句块BEGIN语句1语句2…ENDBEGIN…END语句块通常是与流程控制语句IF…ELSE或WHILE一起使用的2.IF…ELSE语句“布尔表达式”表示一个测试条件，取值为True或False如果布尔表达式中包含SELECT语句，则必须将其用圆括号扩起来。IF布尔表达式语句块1[ELSE语句块2]处理过程为：

如果布尔表达式为True，则执行语句块1；

如果布尔表达式为False，则执行语句块2，如果有的话。3.WHILE语句用于设置重复执行的一个语句块。WHILE布尔表达式

语句块当布尔表达式为真时，重复执行语句块（称为循环体）；当布尔表达式为假时退出循环。示例例1：计算1+2+3+…+100的和。DECLARE@iint,@sumintSET@i=1SET@sum=0WHILE@i<=100BEGINSET@sum=@sum+@iSET@i=@i+1ENDPRINT@sum应用举例例2：查询选修3号课程的学生的平均成绩是否大于60分，输出相应的提示信息。例3：查询选课表中是否有数学系的学生的选课信息，输出相应的提示信息。ifexists(select*fromscwheresnoin(selectsnofromstudentwheresdept='数学系'))print'有数学系的同学的选课信息'elseprint'没有数学系的同学的选课信息'CASE结构如果对于一个条件来说可能有不同的多种情况，那么对于不同的情况就应该执行不同的操作，在程序设计中，遇到这样的情况，使用CASE语句就比较简单。CASE具有两种格式。1．简单CASE表达式CASE条件表达式

WHEN表达式值1THEN结果表达式1[WHEN表达式值2THEN结果表达式2[…]][ELSE结果表达式n]END其执行过程是：用条件表达式的值依次与每一个WHEN子句的表达式值比较，直到与一个表达式值完全相同时，便将该WHEN子句指定的结果表达式返回。如果没有任何一个WHEN子句的表达式值和条件表达式值相同这时，如果存在ELSE子句，便返回ELSE子句之后的结果表达式；如果不存在ELSE子句，便返回一个NULL值。简单CASE表达式示例例4使用简单CASE结构实现以下功能：输出课程号、课程名、开课学期，开课学期用“第几学期”表示代码如下：selectcno,cname,开课学期=casesemesterwhen'1'then'第一学期'when'2'then'第二学期'when'3'then'第三学期'when'4'then'第四学期'when'5'then'第五学期'endfromcourse

运行结果如下图所示。

2．搜索CASE表达式搜索CASE表达式语法格式为：CASEWHEN逻辑表达式1THEN结果表达式1[WHEN逻辑表达式2THEN结果表达式2[…]][ELSE结果表达式n]END其执行过程是：测试每个WHEN子句后的逻辑表达式，如果结果为TRUE，则返回相应的结果表达式，否则检查是否有ELSE子句，如果存在ELSE子句，便返回ELSE子句之后的结果表达式；如果不存在ELSE子句，便返回一个NULL值。例5使用搜索CASE表达式实现同样的功能。selectcno,cname,开课学期=casewhensemester='1'then'第一学期'whensemester='2'then'第二学期'whensemester='3'then'第三学期'whensemester='4'then'第四学期'whensemester='5'then'第五学期'endfromcourse练习：请尝试使用case语句实现以下效果。6.3函数例6输出当前日期。PRINTGETDATE()例7计算两个日期之间相关的天数。PRINTDATEDIFF(DAY,'11/11/2021','7/10/2001')输出结果如下：-7429

例8输出当前日期，显示为当前是XXXX年。print'当前是:'+convert(char(4),year(getdate()))+'年'例9显示当前数据库的名称和标识号。Use学生选课

Go SelectDB_Name() SelectDB_id()Go6.4游标6.4.1游标概念6.4.2游标的使用6.4.3游标应用示例6.4.1游标概念如何从某一结果集中逐一地读取一条记录？游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制游标总是与一条T_SQL选择语句相关联游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通…游标当前行指针游标结果集游标特点允许定位在结果集的特定行。从结果集的当前位置检索一行或多行。支持对结果集中当前位置的行进行数据修改。游标种类Transact_SQL游标 由DECLARECURSOR语法定义主要用在Transact_SQL脚本，存储过程和触发器中API游标 支持在OLEDBODBC以及DB_library中使用游标函数，主要用在服务器上。客户游标 主要是当在客户机上缓存结果集时才使用。6.4.2游标的使用是否声明游标打开游标提取数据处理完成？关闭游标释放资源声明游标DECLAREcursor_nameCURSOR[LOCAL|GLOBAL][FORWARD_ONLY|SCROLL][STATIC|KEYSET|DYNAMIC|FAST_FORWARD][READ_ONLY|SCROLL_LOCKS|OPTIMISTIC][TYPE_WARNING]FORselect_statement[FORUPDATE[OFcolumn_name[,…n]]]打开游标OPEN{cursor_name|cursor_variable_name}提取数据

FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{cursor_name|@cursor_variable_name}[INTO@variable_name[,...n]]@@FETCH_STATUS可以使用@@FETCH_STATUS全局变量判断数据提取的状态。@@FETCH_STATUS返回FETCH语句执行后的游标最终状态。返回值含义0FETCH语句成功。-1FETCH语句失败或此行不在结果集中。-2被提取的行不存在。例9：在学生选课数据库中逐行读取数据declare

cur_stu

cursor

forselect*from

studentopen

cur_stufetch

next

from

cur_stuwhile

@@FETCH_STATUS=0beginfetch

next

from

cur_stuend关闭游标CLOSE{cursor_name|cursor_variable_name}在使用CLOSE语句关闭某游标后，系统并没有完全释放游标的资源，并且也没有改变游标的定义，当再次使用OPEN语句时可以重新打开此游标。释放游标释放分配给游标的所有资源。

DEALLOCATE{cursor_name| cursor_variable_name}释放游标就释放了与该游标有关的一切资源，包括游标的声明，以后就不能再使用OPEN语句打开此游标了

6.4.3游标应用示例

例10：使用游标处理学生表中姓陈的同学信息。DECLAREname_curCURSORFORSELECTsnameFROMstudentWHEREsnameLIKE'陈%'ORDERBYsnameOPEN

name_cur--首先提取第一行数据FETCHNEXTFROMname_cur例10（续）WHILE@@FETCH_STATUS=0--若读取成功BEGINFETCHNEXTFROMname_curENDCLOSE

name_curDEALLOCATE

name_cur例11：将FETCH语句的输出存储在局部变量--声明用于存储FETCH返回结果的局部变量DECLARE@var_snochar(5),

@var_snamechar(20)DECLAREstu_cursorCURSOR