第12章安全性

创建和管理数据库对象学习目标重点索引、视图、存储过程、触发器的创建事务和锁的概念3汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等1创建和管理索引可根据拼音或偏旁部首，快速查找某个字词目录第1章数据库技术基础………1…第2章SQLServer2005概述………22…2.4.2服务器管理………44…第3章数据库的基本操作………55第4章表的基本操作………78第5章数据的基本操作………99…5.1.3数据的删除……109…1创建和管理索引《数据库应用技术》…………38…………432.4.2服务器管理…………44√5.1.3数据的删除…………109√…………DELETE……110√5IndexesUseKeyValuestoLocateData（根据索引键查找定位数据行）DataPages（数据页）…IndexPages（索引页）什么是索引1创建和管理索引6什么是索引SQLServer中的数据也是按页（4KB）存放索引：是SQLServer编排数据的内部方法。它为SQLServer提供一种方法来编排查询数据。索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。1创建和管理索引借书证号姓名系114李菊计算机工程系113张涛计算机工程系115周洪发计算机工程系111李立军计算机工程系112李立军计算机工程系111112113114115非聚集索引读者111112113114115聚集索引读者借书证号姓名系111王维莉计算机工程系112李立军计算机工程系113张涛计算机工程系114李菊计算机工程系115周洪发计算机工程系1创建和管理索引不唯一索引李立军李立军周洪发张涛李菊李立军李立军周洪发张涛李菊唯一索引110王红计算机工程系110110王红计算机工程系1108索引类型唯一索引：唯一索引不允许两行具有相同的索引值主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个1创建和管理索引9如何创建索引CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameONtable_name(column_name…)UNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，可选使用T-SQL语句创建索引的语法：唯一索引聚集索引或非聚集索引1创建和管理索引10USEstuDBGOIFEXISTS(SELECTnameFROMsysindexesWHEREname='IX_writtenExam')DROPINDEXstuMarks.IX_writtenExam/*--笔试列创建非聚集索引：填充因子为30％--*/CREATENONCLUSTEREDINDEXIX_writtenExamONstuMarks(writtenExam)GO/*-----指定按索引IX_writtenExam查询----*/SELECT*FROMstuMarks(INDEX=IX_writtenExam)WHEREwrittenExamBETWEEN60AND90如何创建索引在stuMarks表的writtenExam列创建索引：检测索引是否存在：索引存放在系统表sysindexes中1创建和管理索引11索引的优缺点优点–加快访问速度–加强行的唯一性缺点–带索引的表在数据库中需要更多的存储空间–操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新1创建和管理索引12创建索引的指导原则请按照下列标准选择建立索引的列。–该列用于频繁搜索–该列用于对数据进行排序1创建和管理索引请不要使用下面的列创建索引：–列中仅包含几个不同的值。–表中仅包含几行。为小型表创建索引可能不太划算，因为SQLServer在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长借书证号姓名性别系111王维莉女计算机应用112李立军男计算机应用113张涛男计算机应用114李菊女计算机应用115周洪发男计算机应用116李雪女自动控制借书证号图书编号借书时间还书时间1111122662003-6-24NULL1114455012003-7-16NULL1121122672003-10-10NULL1124455022004-1-4NULL1124466012003-12-6NULL1141122672004-2-6NULLCREATEVIEWView_RASSELECT姓名,图书编号,借书时间FROM读者表,借阅表WHERE性别=‘女’AND读者表.借书证号=借阅表.借书证号姓名图书编号借书时间王维莉1122662003-6-24王维莉4455012003-7-16李菊1122672004-2-6View_R2创建和管理视图王维莉李菊1122664455012003-7-162003-6-241122672004-2-6读者借阅查询女性读者的姓名、所借图书的编号和借书时间14什么是视图视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是建立在对表的查询基础上2创建和管理视图视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图15视图的用途–筛选表中的行–防止未经许可的用户访问敏感数据–降低数据库的复杂程度2创建和管理视图162创建和管理视图表：Student视图1：v_Student1视图2：v_Student2视图3：v_Student3……17演示使用设计器创建视图2创建和管理视图SELECTStudent.StuName,Course.CouName,StuCou.StateFROMStudentJOINStuCouONStudent.StuNo=StuCou.StuNoJOINCourseONStuCou.CouNo=Course.CouNo18使用T-SQL语句创建视图的语法CREATEVIEWview_nameASselect语句2创建和管理视图CREATEVIEWv_Student1ASSELECT*FROMStudentWHEREClassNo='20000001'CREATEVIEWv_Student2ASSELECT*FROMStudentWHEREClassNo='20000002'EXECP_REXECP_REXECP_R姓名图书编号借书时间王维莉1122662003-6-24王维莉4455012003-7-16李菊1122672004-2-6CREATEPROCEDUREP_RASSELECT姓名,图书编号,借书时间FROM读者表,借阅表WHERE性别=‘女’AND读者表.借书证号=借阅表.借书证号3创建和管理存储过程查询女性读者的姓名和她们所借图书的编号及借书时间:姓名图书编号借书时间王维莉1122662003-6-24王维莉4455012003-7-16李菊1122672004-2-6姓名图书编号借书时间王维莉1122662003-6-24王维莉4455012003-7-16李菊1122672004-2-620存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果intsum(inta,intb){ints;s=a+b;returns;}存储过程相当于C语言中的函数3创建和管理存储过程什么是存储过程21存储过程------------------------单个SELECT语句SELECT语句块SELECT语句与逻辑控制语句可以包含存储过程可以包含数据操纵语句、变量、逻辑控制语句等3创建和管理存储过程什么是存储过程22执行速度更快允许模块化程序设计提高系统安全性减少网络流通量3创建和管理存储过程存储过程的优点23系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头3创建和管理存储过程存储过程的分类用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数24系统存储过程说明sp_databases列出服务器上的所有数据库。sp_helpdb报告有关指定数据库或所有数据库的信息sp_renamedb更改数据库的名称sp_tables返回当前环境下可查询的对象的列表sp_columns回某个表列的信息sp_help查看某个表的所有信息sp_helpconstraint查看某个表的约束sp_helpindex查看某个表的索引sp_stored_procedures列出当前环境中的所有存储过程。sp_password添加或修改登录帐户的密码。sp_helptext显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。3创建和管理存储过程常用的系统存储过程25EXECsp_databasesEXECsp_renamedb'Northwind','Northwind1'USEstuDBGOEXECsp_tablesEXECsp_columnsstuInfoEXECsp_helpstuInfoEXECsp_helpconstraintstuInfoEXECsp_helpindexstuMarksEXECsp_helptext'view_stuInfo_stuMarks'EXECsp_stored_procedures修改数据库的名称(单用户访问)列出当前系统中的数据库当前数据库中查询的对象的列表返回某个表列的信息查看表stuInfo的信息查看表stuInfo的约束查看表stuMarks的索引查看视图的语句文本查看当前数据库中的存储过程3创建和管理存储过程常用的系统存储过程26定义存储过程的语法CREATEPROC[EDURE]存储过程名@参数1数据类型[=默认值OUTPUT],……,@参数n数据类型[=默认值OUTPUT]ASSQL语句GO和C语言的函数一样，参数可选参数分为输入参数、输出参数输入参数允许有默认值3创建和管理存储过程如何创建存储过程27问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单3创建和管理存储过程创建不带参数的存储过程28CREATEPROCEDUREproc_stuASDECLARE@writtenAvgfloat,@labAvgfloatSELECT@writtenAvg=AVG(writtenExam),@labAvg=AVG(labExam)FROMstuMarksprint'笔试平均分：'+convert(varchar(5),@writtenAvg)print'机试平均分：'+convert(varchar(5),@labAvg)IF(@writtenAvg70AND@labAvg70)print'本班考试成绩：优秀'ELSEprint'本班考试成绩：较差'print'--------------------------------------------------'print'参加本次考试没有通过的学员：'SELECTstuName,stuInfo.stuNo,writtenExam,labExamFROMstuInfoINNERJOINstuMarksONstuInfo.stuNo=stuMarks.stuNoWHEREwrittenExam60ORlabExam60GOproc_stu为存储过程的名称笔