索引与自定义函数省名师优质课赛课获奖课件市赛课一等奖课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,*,本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。感谢您,单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,本资料仅供参考，不能作为科学依据。谢谢。本资料仅供参考，不能作为科学依据。感谢您,引入索引机制,是为了提升对数据库查询效率。,索引、创建与管理方法,6.1 索引,第1页,1,INSERT INTO ASD VALUES(7,F,79,22),INSERT INTO ASD VALUES(2,B,89,21),INSERT INTO ASD VALUES(5,E,94,20),INSERT INTO ASD VALUES(10,D,76,22),INSERT INTO ASD VALUES(6,A,64,20),INSERT INTO ASD VALUES(4,C,88,21),INSERT INTO ASD VALUES(1,H,91,22),INSERT INTO ASD VALUES(8,G,68,19),Create table ASD(,NO int not null,NAME nchar(4)not null,SCORE numeric(4,1)null,AGE int not null),go,先建立一个名为“,ASD,”表，并插入统计：,在未建立主键约束,并未建立索引时,统计显示次序是物理次序,第2页,2,1索引概念,(1)创建索引，能够防止全表扫描，从而提升查询速度.,(2)索引是数据库对象,分别用CREATE命令建立,用DROP命令删除,用ALTER命令修改.,(3)索引与与表（或视图）关联，并按表中指定列值排列次序映象表。建立索引后表存放由两部分组成:一是用来存放表数据页面;二是用来存放索引索引页面。索引就存放在索引页面上。,(4)SQL Server读取数据过程,首先确定是否存在索引，然后查询优化器（负责生成查询优化执行计划组件）从表扫描和使用索引这两种方法中，确定对于数据访问哪种方式更为有效。,(5)数据检索方式:先搜索索引页面，从中找到所需数据指针，再直接经过指针从数据页面中读取数据。,（6）应注意问题:不应该在每一个列上都创建索引,以免降低系统速度。插入、删除或更新索引列比非索引列要花更长时间。,第3页,3,索引次序,1 A,2 B,3 C,4 D,5 E,6 F,7 G,8 H,CREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD),索引页面,数据页面,行定位器,已按表中NAME字段建立索引表存放。,第4页,4,索引键值次序,被引用数据行存放次序,1,2,3,4,5,6,7,4,7,1,3,2,5,6,行定位器,索引页面,数据页面,第5页,5,2索引存放结构,(1)簇索引(Clusteredlndex),对表物理数据页中数据按列进行排序，然后再重新存放到磁盘上。,所以一个表只能有一个簇索引。簇索引查找数据很快。,（2)适于使用簇索引情况 经惯用于排序从表中检索数据列。经常次序访问列。,每个索引行包含一个键值和 一个指针，分别指向数据行。(3）表中按次序排列序列（如拼音次序、日期和数字次序）在查询时候不需要逐行查找，所以查询速度快。(4)以下数据类型列不可用作聚集索引键：ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image,第6页,6,CREATE CLUSTERED INDEX ASD_CLU_NOON ASD(NO ASC),建立簇索引后统计重新按指定列值排列了次序,且则行 定位器中存放是簇索引索引键：,索引页,行定位器,数据页,第7页,7,2)非簇索引(Nonclusteredlndex)。,非簇索引将行定位器按关键字值，用一定方式排序。则行定位器存放是指向数据行指针。,非簇索引检索效率较 低。,一个表最多能够建248个非簇索引。,索引列中数据频繁更改时应建立非簇索引。,第8页,8,索引次序,1 A,2 B,3 C,4 D,5 E,6 F,7 G,8 H,CREATE INDEX ASD_NAME_IND ON ASD(NAME,ASD),索引页面,数据页面,行定位器,在非簇索引中，行定位器存放是指向数据行指针。数据没有按索引键重新排列次序。,第9页,9,1,2,3,4,5,6,7,4,7,1,3,2,5,6,索引键值次序,被引用数据行存放次序,行定位器,第10页,10,CREATE CLUSTERED INDEX Asd_clu_nocf2 ON ASD(NO ASC),建立了聚集索引,后,统计按索引顺,序重新物理地排,列了统计次序.,一个表只能有一,种物理排列方式,所以一个表只能,建一个聚集索引.,第11页,11,3索引键组成,依据索引键组成，能够将索引分为三种类型。,1)唯一索引,创建唯一索引，可确保表内索引列中不包含重复数据内容。,2)复合索引,在一个表中，经过连接或附接两个或多个列值而创建索引。,3)覆盖索引,当索引中包含了需要全部信息时，这个索引称为覆盖索引。它键值包含了满足查询条件全部数据。,第12页,12,612 创建索引,1.创建索引时，要注意几点:,(1)当在表中创建主关键字约束或唯一性约束时，SQL Server自动创建一个唯一性索引。,(2)假如表中已经有数据，那么在创建索引时，SQL Server会检验数据正当性。当有不正当数据时，创建索引将失败。,(3)当有多个列作为关键字时，应创建复合索引，即索引包含有两个或多个列。,(4)基于相同列但列次序不一样复合索引也是不一样。,第13页,13,简单建立索引命令,CREATE INDEX 索引类型 ,ON (列名 排序方式，n),索引类型有:,CLUSTERED-簇索引,NONCLUSTERED-非簇索引,UNIQUE -唯一索引,第14页,14,2.创建索引完整格式：,CREATE 索引类型 INDEX ,ON (列名1ASC I DESC ,n ),WITH ,ON,其中:,索引类型有:UNIQUE、CLUSTERED、NONCLUSGTERED,索引选项为以下属性组合：,PAD INDEX,FILLFACTOR=填充因子,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDB,其中，,第15页,15,1)UNIQUE,创建唯一索引。创建唯一索引后，假如执行INSERT或UPDATE操作后会造成有重复索引值出现时，该INSERT或UPDATE操作会失败。,2)CLUSTERED,指明创建索引为簇索引。默认为创建索引为非簇索引。,3)NONCLUSTERED,创建索引为非簇索引。,第16页,16,4)PAD_INDEX,指定填充索引内部节点行数，最少应大于等于两行。,PAD_INDEX选项只有在 FILLFACTOR选项指定后才起作用，因为PAD_INDEX使用与FILLFACTOR相同百分比。,默认时SQLServer确保每个索引页最少有能容纳一条最大索引行数据空闲空间。假如FILLFACTOR指定百分比不够容纳一行数据，SQLServer会自动内部更改百分比。,第17页,17,5)FILLFACTOR；填充因子,它指定创建索引时每个索引页数据占索引页大小百分比。fillfactor值为1到100，它其实同时指出了索引页保留自由空间占索引页大小 百分比，即100-fillfactor。,第18页,18,6)IGNORE DUP KEY,此选项控制了包含一个唯一约束列中插入重复数据时SQLServer所作反应。,当选择此选项时，SOL Server返回一个错误信息，跳过此行数据插入继续执行下面插入数据操作。,当没选择此选项时，SQLServer不但会返回一个错误信息，还会回滚整个INSERT语句。,7)DROP EXISTING,指定要删除同名索引并重新创建。,第19页,19,例 在图书表中为出版社创建索引。,CREATE INDEX idx_press,ON book (press),例 创建出版社和作者复合索引。使用DROP EXISTING是因为前面例子已经创建了索引idx_press，所以先删除同名索引，然后再创建新索引。,CREATE INDEX idx_press,ON book (press，author),WITH DROP_EXISTING,第20页,20,例6-3创建唯一非簇索引。,CREATE UNIQUE INDEX idx_press,ON book (book_id),WITH DROP_EXISTING,例6-4使用填充因子创建唯一非簇索引。,CREATE UNIQUE INDEX idx_press,ON book (book_id),WITH PAD_INDEX,FILLFACTOR=50,DROP EXISTING,第21页,21,613 删除索引,1.索引删除语法：,DROP lNDEX 表名.索引名,n,2.几点说明：,(1)DROP lNDEX命令不能删除由CREATE TABLE或ALTER TABLE命令创建PRIMARY KEY或UNIQUE约束索引。,(2)不能删除系统表中索引。,(3)在删除簇索引时，表中全部非簇索引都将被重建。,第22页,22,614 索引优化,关于创建索引提议以下。,(1)将更新尽可能多行查询写入单个语句内，而不要使用多个查询更新相同行,(2)使用索引优化分析查询并取得索引提议。,(3)对聚集索引使用整型键，另外，在唯一列、非空列或标识列上创建聚集索引能够取得较佳性能。,(4)在查询经惯用到全部列上创建非聚集索引。,(5)检验列唯一性。,第23页,23,72 自定义函数,第24页,24,7.2.1 自定义函数基本概念,1.自定义函数:是用户为实当代码封装和重用,将一组T-SQL语句按一定格式定义得到,并有输入参数,运行后有返回值。,2.自定义函数优点：,(1)允许模块化程序设计。一次创建可屡次调用。,(2)执行速度更加快(首次优化编译，今后直接运行)。,(3)降低网络流量。,3.自定义函数与存放过程比较,项目,自定义函数,存放过程,参数,不允许输出参数,允许多个输入输出参数,返回值,有且只有一个返回值,能够没有返回值,调用,能够嵌入查询语句中使用,必须单独调用,第25页,25,722 自定义函数三种类型,1标量函数,(1)函数返回单个数值(返回值类型在RETURNS子句中指定)。,(2)函数返回单个数值，返回由BEGINEND块指定多个值。,返回值不可为text、ntext、image、cursor和timestamp数据类型。,第26页,26,2表值函数,(1)内嵌表值函数 返回由选择结果组成统计集表。它能够替换视图,且比视图逻辑功效愈加强大。,(2)多语句表值函数,返回由选择结果组成统计集表,函数需要由BEGINEND限定函数体。,第27页,27,多语句表值函数主体中只允许使用语句：,赋值语句。,除错误捕捉语句 TRYCATCH外流程控制语句。,定义局部数据变量和局部游标DECLARE语句。,SELECT语句，其中选择列表包含为局部变量分配值表示式。,游标操作，该操作引用在函数中申明、打开、关闭和释放局部游标。只允许使用以INTO子句向局部变量赋值FETCH语句；不允许使用将数据返回到客户端FETCH语句。,修改table局部变量INSERT、UPDATE和DELETE语句。,调用扩展存放过程EXECUTE语句。,第28页,28,723 创建与使用用户自定义函数,CREATE FUNCTION,(参数名 数据类型 =默认值 ,),RETURNS 返回值数据类型,WITH 选项,AS,BEGIN,SQL语句,RETURN 返回表示式,END,其中：选项有二：,（1）ENCRYPTION能够实现用户自定义函数加密；,（2）SCHEMA BINDING将自定义函数绑定到它所引用数据库对象。,第29页,29,例7-17自定义标量函数，实现对图书价格高与低评价。,USE,图书馆,If EXISTS(SELECT name FROM sysobjects,WHERE name=fc_price AND type=FN),DROP FUNCTION fc_price,GO,CREATE FUNCTION fc_price(priceinput money),RETURNS nvarchar(10),BEGIN,DECLARE returnstr nvarchar(10),If priceinput40,SET returnstr=,较贵图书,ELSE,SET returnstr=,廉价图书,RETURN returnstr,END,使用该函数。,SELECT top 20 book_name,price,dbo.fc_price(price)FROM,book,第30页,30,例7-18自定义内嵌表值函数fc_press，依据指定出版社参数查询该出版社出版图书，返回结果统计集。,USE library,GO,CREATE FUNCTION fc_press(press varchar(30),RETURNS table,AS,RETURN,(SELECT bookname，author，press，price FROM book,WHERE press =press ),GO,下面语句说明了怎样使用新建立内嵌表值函数来获取清华大学出版社出版图书信息。,select *From fc_press(,清华大学出版社,),第31页,31,Create function XY_fc(,专业,nvarchar(8),Returns table,AS,Return,select*from,班级,WHERE,专业,=,专业,GO,select*FROM XY_fc(,软件工程,),第32页,32,第33页,33,例7-19自定义多语句表值函数 fc_press_1，依据指定出版社参数查询该出版社出版图书，返回结果统计集。请注意与定义内嵌表值函数fc_press 对比。,USE library,GO,CREATE FUNCTION fc_press_1(press nvarchar(30),RETURNS tb_press table,(book_name nvarChar(30)NOT NULL，,author nvarchar(20)NULL，,press nvarChar(30)NULL，,plice money NULL,),AS,BEGIN,INSERT tb_Press,SELECT book_name,author,press,price,FROM book WHERE press=press,RETURN,END,GO,下面这两个语句分别调用多语句表值函数fcjoress_l宋查询清华大学出版社和科学出版社出版图书。,SELECT*from dbofc press l(清华大学出版社),GO,SELECT*from dbofc_press_l(科学出版社),GO,第34页,34,724 自定义函数管理,1查看自定义函数,EXEC sp_help ,如要查看fcrice信息详细语句为：,EXEC sp_help fc price,利用这个语句，能够看到函数名称及相关参数。但要看到函数详细定义，需要用系统存放过程sphelptext。其语法以下：,EXEC sp_helptext函数名,查看自定义函数fc price语句以下：,EXEC sp_helptext fc_price,第35页,35,2修改自定义函数,修改自定义函数经过ALTERFUNCTION关键字来完成。其语法以下：,ALTER FUNCTION函数名,(Le参数名参数数据类型 =默认值 ,.n),RETURNS返回值数据类型,WITH选项,AS,BEGIN,SQL语句,RETURN返回表示式,END,能够看出，修改自定义函数与创建自定义函数语法基本一样，各个参数意义与创建,自定义函数参数意义相同。,3删除自定义函数,DROP FUNCTION函数名,如要删除自定义函数fIrice能够使用以下语句：,DROP FUNCTION fc_price,第36页,36,7.2.4 自定义函数的管理,第37页,37,1查看自定义函数语句,（1）查看自定义函数信息(名称及相关参数):EXEC sp_help,比如：EXEC sp_help fc_price,（2）查看自定义函数定义（语句）：,EXEC sp_helptext ,比如：EXEC sp_helptext fc_price,第38页,38,2修改自定义函数,语法以下：,ALTER FUNCTION,(参数名 数据类型 =默认值 ,.n),RETURNS 返回值数据类型,WITH 选项,AS,BEGIN,SQL语句,RETURN 返回表示式,END,修改与创建自定义函数语法基本一样，参数意义相同。,第39页,39,3删除自定义函数,语法以下：,DROP FUNCTION,比如：,DROP FUNCTION fc_price,第40页,40,第41页,41,