SQLServer使用入门二.ppt

《SQLServer使用入门二.ppt》由会员分享，可在线阅读，更多相关《SQLServer使用入门二.ppt（38页珍藏版）》请在三一办公上搜索。

1、Web技术与数据库,第九章、SQL Server使用入门二,2,课程回顾,如何定义变量并赋值EXISTS子查询IF、WHILE、CASE逻辑控制语句,3,课程目标,掌握如何创建并使用视图掌握如何创建索引掌握如何创建存储过程掌握如何调用存储过程,4,什么是视图,CREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo,基于学员信息表和成绩表创建视图,教员需要的视图：方便查看学员的成绩,班主任需要的视图：方便查看学员的档案,5,什么是视图,视图是一张虚拟表，它表示一张表的部分数据或多张表的综合数据，其结构和数据是

2、建立在对表的查询基础上视图中并不存放数据，而是存放在视图所引用的原始表（基表）中同一张原始表，根据不同用户的不同需求，可以创建不同的视图,6,什么是视图,视图的用途筛选表中的行防止未经许可的用户访问敏感数据降低数据库的复杂程度将多个物理数据库抽象为一个逻辑数据库,7,如何创建视图,使用T-SQL语句创建视图的语法,CREATE VIEW view_name AS,8,如何创建视图,创建方便教员查看成绩的视图,IF EXISTS(SELECT*FROM sysobjects WHERE name=view_stuInfo_stuMarks)DROP VIEW view_stuInfo_stuMa

3、rksGOCREATE VIEW view_stuInfo_stuMarks AS SELECT 姓名=stuName,学号=stuInfo.stuNo,笔试成绩=writtenExam,机试成绩=labExam,平均分=(writtenExam+labExam)/2 FROM stuInfo LEFT JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNoGOSELECT*FROM view_stuInfo_stuMarks,检测是否存在,创建视图,使用视图,删除视图,9,什么是索引,汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等我

4、们可以根据拼音或偏旁部首，快速查找某个字词,10,Indexes Use Key Values to Locate Data（根据索引键查找定位数据行）,Index Pages（索引页）,什么是索引,11,什么是索引,SQL Server中的数据也是按页（4KB）存放索引：是SQL Server编排数据的内部方法。它为SQL Server提供一种方法来编排查询数据。索引页：数据库中存储索引的数据页；索引页类似于汉语字（词）典中按拼音或笔画排序的目录页。索引的作用：通过使用索引，可以大大提高数据库的检索速度，改善数据库性能。,12,索引类型,唯一索引：唯一索引不允许两行具有相同的索引值主键索引：

5、为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表只能有一个非聚集索引(Non-clustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个,13,聚集索引,索引叶节点就是数据页节点,14,非聚集索引,索引叶节点,数据页节点,15,如何创建索引 3-2,CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name

6、 ON table_name(column_name)WITH FILLFACTOR=x,UNIQUE表示唯一索引，可选CLUSTERED、NONCLUSTERED表示聚集索引还是 非聚集索引，可选FILLFACTOR表示填充因子，指定一个0到100之间的 值，该值指示索引页填满的空间所占的百分比,使用T-SQL语句创建索引的语法：,唯一索引,聚集索引或非聚集索引,填充因子（系数）:指定一个0100之间的值，表示索引页填充的百分比,16,索引的优缺点,优点加快访问速度加强行的唯一性缺点带索引的表在数据库中需要更多的存储空间操纵数据的命令需要更长的处理时间，因为它们需要对索引进行更新,17,创建

7、索引的指导原则,请按照下列标准选择建立索引的列。该列用于频繁搜索该列用于对数据进行排序请不要使用下面的列创建索引：列中仅包含几个不同的值。表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长,18,存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果,int sum(int a,int b)int s;s=a+b;return s;,存储过程相当于C语言中的函数,什么是存储过程 2-1,19,存储过程-,单个 SELECT 语句,SELECT 语句块,S

8、ELECT语句与逻辑控制语句,可以包含,什么是存储过程 2-2,存储过程可以包含数据操纵语句、变量、逻辑 控制语句等,20,执行速度更快允许模块化程序设计 提高系统安全性减少网络流通量,存储过程的优点,21,系统存储过程由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头用户自定义存储过程由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数,存储过程的分类,22,常用的系统存储过程 4-1,23,定义存储过程的语法 CREATE PROCEDURE 存储过程名 参数1 数据类型=默认值 OUTPUT,参数n 数据类型=默认

9、值 OUTPUT AS SQL语句 GO和C语言的函数一样，参数可选参数分为输入参数、输出参数 输入参数允许有默认值,如何创建存储过程,24,创建不带参数的存储过程 2-1,问题：请创建存储过程，查看本次考试平均分以及未通过考试的学员名单,25,创建不带参数的存储过程 2-2,CREATE PROCEDURE proc_stu AS DECLARE writtenAvg float,labAvg float SELECT writtenAvg=AVG(writtenExam),labAvg=AVG(labExam)FROM stuMarks print 笔试平均分：+convert(varch

10、ar(5),writtenAvg)print 机试平均分：+convert(varchar(5),labAvg)IF(writtenAvg70 AND labAvg70)print 本班考试成绩：优秀 ELSE print 本班考试成绩：较差 print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExam60 OR labExam60 GO,pr

11、oc_stu为存储过程的名称,笔试平均分和机试平均分变量,显示考试成绩的等级,显示未通过的学员,26,EXECUTE（执行）语句用来调用存储过程调用的语法EXEC 过程名 参数,调用存储过程,EXEC proc_stu,27,创建带参数的存储过程,存储过程的参数分两种：输入参数输出参数,int sum(int a,int b)int s;s=a+b;return s;,c=sum（5,8）,传入参数值,输入参数：用于向存储过程传入值，类似C语言的按值传递;,输出参数：用于在调用存储过程后，返回结果，类似C语言的 按引用传递;,返回结果,28,带输入参数的存储过程3-1,问题：修改上例：由于每次

12、考试的难易程度不一样，每次 笔试和机试的及格线可能随时变化（不再是60分），这导致考试的评判结果也相应变化。,分析：在述存储过程添加2个输入参数：writtenPass 笔试及格线 labPass 机试及格线,29,带输入参数的存储过程3-2,CREATE PROCEDURE proc_stu writtenPass int,labPass int AS print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.st

13、uNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,输入参数：笔试及格线,输入参数：机试及格线,查询没有通过考试的学员,30,带输入参数的存储过程3-3,EXEC proc_stu 60,55,调用带参数的存储过程 假定本次考试机试偏难，机试的及格线定为55分，笔试及格线定为60分,-或这样调用：EXEC proc_stu labPass=55,writtenPass=60,机试及格线降分后，李斯文（59分）成为“漏网之鱼”了,31,输入参数的默认值3-1,带参数的存储过程确实比较方便，调用者可根据试卷的难易

14、度，随时修改每次考试的及格线,问题：如果试卷的难易程度合适，则调用者还是必须如此调用：EXEC proc_stu 60,60，比较麻烦这样调用就比较合理：EXEC proc_stu 55EXEC proc_stu,笔试及格线55分，机试及格线默认为60分,笔试和机试及格线都默认为标准的60分,32,CREATE PROCEDURE proc_stu writtenPass int=60,labPass int=60 AS print-print 参加本次考试没有通过的学员：SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo

15、 INNER JOIN stuMarks ON stuInfo.stuNo=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass GO,笔试及格线：默认为60分,机试及格线：默认为60分,查询没有通过考试的学员,输入参数的默认值3-2,33,输入参数的默认值3-3,EXEC proc_stu-都采用默认值 EXEC proc_stu 64-机试采用默认值 EXEC proc_stu 60,55-都不采用默认值,调用带参数默认值的存储过程,-错误的调用方式：希望笔试采用默认值，机试及格线55分EXEC proc_stu,55,

16、-正确的调用方式：EXEC proc_stu labPass=55,34,带输出参数的存储过程 3-1,如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了,问题：修改上例，返回未通过考试的学员人数。,35,CREATE PROCEDURE proc_stu notpassSum int OUTPUT,writtenPass int=60,labPass int=60 AS SELECT stuName,stuInfo.stuNo,writtenExam,labExam FROM stuInfo INNER JOIN stuMarks ON stuInfo.stu

17、No=stuMarks.stuNo WHERE writtenExamwrittenPass OR labExamlabPass SELECT notpassSum=COUNT(stuNo)FROM stuMarks WHERE writtenExamwrittenPass OR labExamlabPass GO,输出（返回）参数：表示没有通过的人数,推荐将默认参数放后,带输出参数的存储过程 3-2,统计并返回没有通过考试的学员人数,36,/*-调用存储过程-*/DECLARE sum int EXEC proc_stu sum OUTPUT,64 print-IF sum=3 print

18、未通过人数：+convert(varchar(5),sum)+人,超过60%,及格分数线还应下调ELSE print 未通过人数：+convert(varchar(5),sum)+人,已控制在60%以下，及格分数线适中GO,调用带输出参数的存储过程,带输出参数的存储过程 3-3,调用时必须带OUTPUT关键字，返回结果将存放在变量sum中,后续语句引用返回结果,37,课后作业,1.查询指定月份的借书记录。参数：月份2.查询指定图书的入库数量。参数：图书名称3.查询指定出版社的所有图书出版数量。参数：出版社4.查询年龄只能在最小年龄到最大年龄之间的读者。参数：最小年龄，最大年龄5.查询指定年、月、日的图书还书情况。参数：年，月，日6.更新指定用户的密码。参数：用户名称，新密码7.写出多个存储过程完成作者表的维护（增、删、改、查）。参数：作者表的字段数8.借书还书。参数：读者编号，图书编号，借|还,38,创建并使用视图CREATE VIEW创建索引CREATE INDEX创建存储过程CREATE PROCEDURE 存储过程名调用存储过程EXEC 过程名 参数,本章小结,