《SQLServer的使用》PPT课件.ppt

《《SQLServer的使用》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《SQLServer的使用》PPT课件.ppt（211页珍藏版）》请在装配图网上搜索。

1、第四章 SQL SERVER的使用,本章内容：介绍SQL SERVER的主要功能操作 示例1：Recruitment 示例2：GlobalToyz,查询数据库,关于招收新人的材料存储在本地SQL Server 的RECRUITMENT 数据库中。这些表格包括关于候选人、他们的资格和联系材料、 可提供的空缺等方面的数据，及招收新人代理需要的其它信息。 查询所有外部候选者的名字，城市，和电话号码的信息。,SELECT vFirstName,vLastName,cCity,cPhone FROM ExternalCandidate,显示Recruitment数据库中各种职位和当前人数。,SELECT

2、 cPositionCode,vDescription, iCurrentLength FROM Position,显示带用户自定义标题的列 当查询结果对照SELECT语句显示时，结果集的列名与表中的列名是一样的。当任何一种表达式作用在列上时，SQL SERVER隐藏列标题。 用户自定义的列标题可以代替缺省列标题。包括两种方法：一种列标题在列名之前，一种列标题在列名之后。,查询合同招聘人员的名字和他们的雇佣费用。要求查询结果的列标题作出改变，显示格式如下：,方法1： （其中as可以省略） SELECT cName as 招聘人员名字,siPercentageCharge as 雇佣薪金 FRO

3、M ContractRecruiter,方法2： SELECT 招聘人员名字=cName, 雇佣薪金= siPercentageCharge FROM ContractRecruiter,使用通配符 给Recruitment雇员（Employee）表的cPhone列增加一个约束，要求该列的字符串格式为（*）*-*，其中*为数字字符。如(120)903-3989就是符合条件的一个例子。,ALTER TABLE Employee ADD CONSTRAINT CK_Employee_cPhone CHECK (cPhone like (0-90-90-9)0-90-90-9-0-90-90-90-

4、9),根据测试的成绩，成绩最高的三位外部候选人将获得面试的机会。测试的时间是2001年3月份进行的。先需要列出满足条件的外部候选人的左右信息。,显示顶部几行 与SELECT语句一起使用的TOP子句限制了结果集合中返回的行数。,语法： SELECT TOP nPERCENT 列名,列名 FROM 表名 WHERE 查询条件 ORDER BY 列名,列名,根据测试的成绩，成绩最高的三位外部候选人将获得面试的机会。测试的时间是2001年3月份进行的。先需要列出满足条件的外部候选人的左右信息。查询语句如下：,语法： SELECT TOP 3 * FROM ExternalCandidate WHERE

5、 dTestDate=2001-3-1 AND dTestDate=3/31/2001 ORDER BY siTestScore DESC,GROUP BY ALL GROUP BY子句中的关键字ALL用于显示所有的组，包括那些被WHERE子句排除的组。,例子： SELECT Sno,AVG(Grade) FROM SC WHERE Sno in(95001,95002) GROUP BY ALL Sno,使用COMPUTE和COMPUTE BY子句 使用带SELECT语句的COMPUTE子句，在查询结果中使用聚合函数产生汇总行。COMPUTE子句用于对于整个查询结果产生总计值，COMPUTE

6、 BY子句对于每个组产生小计值。,语法： SELECT 列名,列名 FROM 表名 WHERE 查询条件 ORDER BY 列名,列名 COMPUTE 聚合函数（列名）,聚合函数(列名) BY 列名,列名,打印出所有的选修记录，并在查询结果的底部打印汇总所有学生的平均成绩，总成绩。,SELECT * FROM SC COMPUTE AVG(Grade),SUM(Grade),打印出每个学生的选修记录，并在每个学生查询结果的尾部打印汇总每个学生的平均成绩，总成绩。,SELECT * FROM SC ORDER BY Sno COMPUTE AVG(Grade),SUM(Grade) BY Sno

7、,对于pubs数据库，打印每类书籍的类型名称和预付款项，在每种类型书籍记录的尾部汇总每类书籍的预付款总额,SELECT Tyte,Advance FROM Titles ORDER BY Type COMPUTE SUM(Advance) BY Type,COMPUTE和COMPUTE BY子句的使用规则 COMPUTE子句所用到的所有列必须出现在SELECT列表中 使用COMPUTE BY子句，必须同时使用ORDER BY子句 在COMPUTE BY子句列出的列，必须与ORDER BY子句使用的列匹配 在COMPUTE BY子句中，不同聚合函数可以用于一个以上的列,针对上述规则中的第三条和第

8、四条 当对Titles表查询时使用如下的ORDER BY子句时：,ORDER BY Type，Price, Advance,下面的COMPUTE BY子句，对于上面的ORDER BY子句来说是有效的,COMPUTE AVG(Advance),AVG(Price) BY Type，Price, Advance COMPUTE AVG(Advance),AVG(Price) BY Type，Price COMPUTE AVG(Advance),AVG(Price) BY Type,内连接查询。查询外部候选人的名字以及他们的招聘代理的名称。,SELECT vFirstName,vLastName,c

9、Name FROM ExternalCandidate JOIN RecruitmentAgencies ON ExternalCandidate.cAgencyCode= RecruitmentAgencies.cAgencyCode,思考：查询外部候选人和他们看招聘广告查询的报纸。要求的信息包括外部候选人的姓名和报纸的名称。,SELECT vFirstName，vLastName，cNewspaperName FROM ExternalCandidate JOIN NewsAd ON ExternalCandidate.cNewsAdNo=NewsAd.cNewsAdNo JOIN New

10、spaper ON NewsAd.cNewspaperCode= Newspaper.cNewspaperCode,外连接查询。列出所有外部候选人的名字，由招聘代理方式来的候选人显示他们的招聘代理的名字。,SELECT vFirstName,vLastName,cName FROM ExternalCandidate LEFT OUTER JOIN RecruitmentAgencies ON ExternalCandidate.cAgencyCode= RecruitmentAgencies.cAgencyCode,交叉连接。实现两个表的笛卡尔乘积的连接。,SELECT * FROM Stu

11、dent,SC,另一种形式的交叉连接：,SELECT * FROM Student CROSS JOIN SC,自身连接。并列输出价格为2.9900的一对书籍的书名。,SELECT * FROM titles t1 JOIN titles t2 ON t1.price=t2.price WHERE t1.price=2.99 AND t1.titlet2.title,子查询。列出满足条件的书籍的书名，书类型和预付款项，要求的书籍，在其所属类型里其预付值大于该类书籍的平均预付值。,SELECT Title,Type,Advance FROM titles t1 WHERE t1.Advance(

12、SELECT AVG(t2.Advance) FROM titles t2 WHERE t1.Type=t2.Type）,思考：列出学生选课记录，只列出那些高于自己平均分的那些记录的学号、课程号、成绩。,SELECT Sno，Cno，Grade FROM SC X WHERE X.Grade(SELECT AVG(Grade) FROM SC Y WHERE X.Sno=Y.Sno),抽取数据到另一个表中。需要2001年5月申请招聘的外部候选人的材料，抽取这些人的材料到一个新表中。,语法： SELECT 列名,列名 INTO 新表名 FROM 表名 WHERE 查询条件,SELECT * IN

13、TO #tempExternalCandidate FROM ExternalCandidate WHERE dDateOfApplication=2001-5-1,注：在SQL SERVER中，对象前加上符号#表示该对象为临时 对象，临时对象存储在数据库tempdb中。一个#表示为局部 临时表，两个#表示全局临时表。,思考：创建一个全局临时表abc，其中包括4个属性（编号、姓名、性别、年龄），其中编号为整数类型，且为自动编号类型，初始编号值为100，以后每一个增1。其余属性自定类型。,CREATE TABLE #abc( itemNo int IDENTITY(100,1), itemNam

14、e varchar(50), itemgender nchar(1), itemage int),IDENTITY：IDENTITY用于那些需要自动产生唯一系统值的列，该特性可用于产生有序列。具体用法：IDENTITY(SEED,INCREMENT) 其中SEED为初始值，INCREMENT为步长。 补充：timestamp数据类型，这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。timestamp一般用作给表行加版本戳的机制。存储大小为8字节。一个表只能有一个timestamp列。每次插入或更新包含timestamp列的行时，timestamp列中的值均会更新。,SQL S

15、ERVER的函数-串函数,思考：查询报纸的名称、联系人、电话号码，要求报纸的名称以大写形式显示。,SELECT UPPER(cNewspaperName) as NewspaperName, vContactPerson，cPhone FROM Newspaper,思考：查询位于Houston的招聘代理的名称、地址和联系电话。要求地址只显示前10个字符。,SELECT cName,LEFT(vAddress) as Address,cPhone FROM RecruitmentAgencies WHERE cCity=Houston,SQL SERVER函数-日期时间函数,注：datepart

16、为日期部件，具体取值见后续。,思考：校园招聘的建议截止日期是招聘过程开始后的第10天。需要显示一份包含公司正在访问的所有学院的学院代码、招聘起始日期、招聘的建议截止日期的报表。,SELECT cCollegeCode,dRecruitmentStartDate, DeadLine=DATEADD(dd,10,dRecruitmentStartDate) FROM CampusRecruitment,思考：需要一份面试进度表。打印出外部候选人的姓名，面试者的职位代码，面试的工作日（即星期几）。,SELECT vFirstName，vLastName, 面试日= DATENAME(dw,dInte

17、rviewDate) FROM ExternalCandidate,思考：对于pubs数据库的titles表，打印每本书的书名和出版日期。要求打印格式如：书籍Net Etiquette的出版日期是2008年8月6日。,SELECT 书籍+title+ 的出版日期是+ DATENAME(yyyy,pubdate)+ 年+ DATENAME(mm,pubdate)+ 月+ DATENAME(dd,pubdate)+ 日 FROM titles,SQL SERVER函数-数学函数,思考：查询外部候选人Helen White的测试成绩。要求成绩被舍入到最接近的整数。要求输出的信息包括她的姓名、电话号码

18、和按要求显示的测试成绩。,SELECT vFirstName，vLastName，cPhone， ROUND(siTestScore，0) FROM externalCandidate,SQL SERVER函数-系统函数,系统函数提供一种查询SQL SERVER系统表的方法。系统表是一组表、SQL SERVER使用它们存储用户、数据库、表和安全性的信息。,SQL SERVER函数-转换函数,CONVERT函数用于把数据从一种类型转换为另一种类型。(当 SQL SERVER不能隐含转换数据类型时需要该函数。) 语法：CONVERT(datatype(length),expression,styl

19、e) datatype 是系统定义的数据类型（不可用自定义）； length是char、varchar等数据类型的可选参数； expression是需要转换数据类型的有效表达式； style是把日期型数据转换为字符串类型时的数据表示式样 函数CAST提供和CONVERT相似的功能； 语法：CAST(expression as datatype)；,例子：将Titles表的Ytd_Sales字段的数据转换成字符数据类型输出。,SELECT Ytd_sales=CONVERT(CHAR(10),Ytd_sales) FROM Titles,SELECT Ytd_sales=CAST(Ytd_sal

20、es AS char(10) FROM Titles,几个数据类型转换的例子。,SELECT CONVERT(int，zip） FROM Authors,SELECT The rate of+Title+ is$ + CONVERT(varchar(10)，zip） FROM Titles,日期时间数据转换为字符数据类型的例子。,SELECT Title，CONVERT(char(10)，pubdate,2） FROM Titles,本例，pubdate由日期转换为字符类型，且日期以YY.MM.DD格式显示，这是因为在函数中指定的style的值为2。,CONVERT函数中使用style值，当数

21、据在字符和日期类型之间转换时，用于指出日期的格式。,SQL SERVER-管理数据库,系统数据库 SQL SERVER有以下系统数据库： master tempdb model msdb,master 主数据库记录了服务器系统的所有系统级的信息。包括授权用户、数据库、系统配置设置。另外、主数据库记录了所有其他数据库的信息，如数据库文件的位置、初始化信息等等。如果master数据库出现故障、则SQL SERVER服务无法启动。 主数据库保持了控制SQL SERVER操作的大部分关键数据，在主数据库上不给用户任何权限是明智的。并且，保持主数据库的定期备份是十分重要的。,tempdb tempdb是

22、临时数据库，用来存放临时数据。tempdb数据库可以保持的临时数据有：临时表、临时存储过程、变量、游标、排序的中间结果表、查询中间的结果等。 tempdb数据库的大小会随着操作的多少而变化，操作越多，临时数据库就越大。在SQL SERVER重启时，会自动重建tempdb数据库，使得系统在启动时自动会有一个干净的tempdb数据库。tempdb没有必要备份。,model model是模板数据库，当在SQL SERVER中创建新的数据库时，SQL SERVER都会以model数据库为模板创建新数据库。当数据库发出CREATE DATABASE语句创建数据库时，SQL SERVER会通过复制mode

23、l数据库的内容来创建数据库的第一部分，然后用空页来填充新的数据库的剩余部分。,msdb msdb也是SQL SERVER系统使用的数据库，通常由SQL SERVER代理用来计划警报和作业。另外有关数据库备份和还原的记录，也会写在该数据库里面。,用户数据库 SQL SERVER创建用户数据库的语法： CREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH := PRIMARY ( NAME = logical_file_name , FILENAME =

24、os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n := FILEGROUP filegroup_name ,.n ,数据库文件和文件组 每个SQL SERVER的数据库至少要包含两个文件：一个数据文件和一个日志文件。数据文件里包含的是数据库的数据和对象，如表、视图、索引等；日志文件里包含的是用来恢复数据库所需的信息。 SQL SERVER的文件类型： 主数据文件 mdf 辅助数据文件 ndf 事务日志文件 ldf,创建一个数据库test1，详细列明数

25、据文件和日志文件信息。,create database test1 on (name=test1data, filename=C:Program FilesMicrosoft SQL ServerMSSQL$COMPUTERDatatest1dat.mdf, size=12,maxsize=100,filegrowth=2 ) log on (name=test1log, filename=C:Program FilesMicrosoft SQL ServerMSSQL$COMPUTERDatatest1log.ldf, size=10,maxsize=200, filegrowth=1 ),

26、创建一个数据库test2，其中包括1个主文件，两个辅助文件和两个日志文件。,SQL SERVER修改数据库方式： 数据和日志文件的增加和删除 数据库可以扩大和缩小 文件组可以添加到数据库 数据库的配置设置可以改变 几个数据库有关操作的系统存储过程： sp_helpdb db_name 查看数据库信息 sp_renamedb oldname,new_name 重命名 sp_dboption dbname,optionname,value 例如： sp_dboption student,read only,ON,SQL SERVER-用户自定义的数据类型,用户定义的数据类型是由用户创建的，它基于系

27、统数据类型。如果要在多个表中存储一种特定的数据类型，那你可以创建用户定义数据类型，可以保证在它出现的所有表中相应的列都有相同的数据类型、长度和空值性。 创建用户定义的数据类型必须提供如下参数： 数据类型的名 新数据类型所基于的系统数据类型 数据类型的空值性,使用sp_addtype系统存储过程创建用户定义的数据类型 语法： sp_addtype typename,system_datatype,null type typename 是用户自定义的数据类型的名称 system_datatype 是用户类型所基于的系统的数据类型 null type 是数据类型为NULL或NOT NULL,自定义数

28、据类型的例子。,sp_addtype typmydate,datetime,NOT NULL,sp_addtype typmychar,char(10),注：如果NULL或NOT NULL条件已经在CREATE TABLE 语句中指 出，那么忽略null类型。,删除用户自定义数据类型的例子。,sp_droptype typmydate,sp_droptype typmychar,注：如果表正在使用用户自定义的数据类型，那么它不能被删除。,SQL SERVER-规则,规则为实施列或用户自定义数据类型的域完整性提供一种类似约束（CONSTRAINT）的检查机制。即规则对列或用户定义数据类型的值上设

29、置了限制。 规则可以在不改变表的结构的情况下实现检查约束。（规则不同于约束，其不属于表结构的一部分。）,例子：Newspaper表中的zip code应为字符类型，并有如下的模式：0-90-90-90-90-9-0-90-90-90-9，不改变表结构，如何实现该要求？,规则的创建 语法 CREATE RULE rul_name AS conditional_expression rul_name指定新规则的名字，它作为该规则的标示符 conditional_expression指定用于定义规则的条件。这些条件可以是任何能在WHERE子句中使用的有效表达式，这些表达式可以包含算术运算符、关系运算

30、符、IN、LIKE、BETWEEN。条件表达式中的变量必须以符号作为前缀。,例子：某列的取值只能在1825岁之间的规则 CREATE RULE rulage AS xage BETWEEN 18 AND 25,规则绑定 当规则被创建后，它必须绑定到列或用户定义的数据类型 使用规则的限制 一次只能使一个规则绑定一个列或用户定义数据类型 如果一个规则绑定用户定义的数据类型，它不可代替绑定到数据类型的列的规则； 如果对一个已经绑定了另一个规则的列或数据类型绑定以新的规则的话，新规则将替代老规则； 规则不能应用于已插入到表中的数据。表中现有值不必满足规则指定的准则；,使用sp_bindrule系统存储

31、过程绑定规则 语法： sp_bindrule rule_name,object_name,FUTUREONLY rule_name指定将被绑定的规则的名称 object_name以table_name.column_name的格式指定将绑定规则的列的名称，或指定用户定义数据类型的名称。 FUTUREONLY只对绑定到用户定义数据类型的规则才使用。它防止用户定义数据类型对现有的列并入此规则。这个规则只可应用于用户定义数据类型的新列。如果取NULL,则应用所有的列。,例子：将rulage绑定到学生表的年龄列，因为学生年龄需要一个这样的规则。 sp_bindrule rulage,Student.s

32、age,例子：创建用户自定义数据类型myint(源于int),创建规则rulex(变量取值14到28之间),将规则绑定到该用户自定义数据类型上。且该规则只用于以后用该数据类型定义的列。,sp_addtype myint,int CREATE RULE rulx AS x BETWEEN 14 AND 28 sp_bindrule rulx,myint,FUTUREONLY,注：不能够系统定义的数据类型定义规则,例子：Newspaper表中的zip code应为字符类型，并有如下的模式：0-90-90-90-90-9-0-90-90-90-9，不改变表结构，如何实现该要求？,CREATE RUL

33、E rulx AS x LIKE 0-90-90-90-9 0-9-0-90-90-90-9 sp_bindrule rulx, Newspaper.cZip,取消规则绑定 使用sp_unbindrule系统存储过程可取消列或用户自定义数据类型的规则。取消规则意味着规则将不再作用于列或该用户定义的数据类型。 语法： sp_unbindrule object_name ,FUTUREONLY object_name 是取消规则的表的列名或用户定义的数据类型 FUTUREONLY只能用于从用户定义数据类型取消规则。它防止了对现有的用户定义数据类型的列取消规则。,例子：取消Newspaper表的zi

34、p code列上的规则 sp_unbindrule Newspaper.cZip,例子：用户自定义数据类型myint已经绑定了规则rulex，现取消该绑定，但是当前使用myint类型的表列仍然保留该规则 sp_unbindrule myint,FUTUREONLY,SQL SERVER-缺省,缺省为赋予列的一个常量值，使用户不需要插入值到列。缺省能够约束到列或用户自定义的数据类型 缺省使用CREATE DEFAULT语句创建 语法： CREATE DEFAULT default_name AS constant_expression default_name指定缺省的名字，符合标示符规则的命名

35、 constant_expression指定一个只包含常量的表达式。,例子：缺省值为China CREATE DEFAULT defcountry AS China,例子：缺省值为计算机系 CREATE DEFAULT defdept AS 计算机系,绑定缺省 语法：使用系统存储过程sp_bindefault sp_bindefault default_name,object_name,FUTUREONLY default_name是使用CREATE DEFAULT语句创建的缺省 object_name 以table_name.column_name指出列名，或者指出要绑定缺省的用户自定义数据

36、类型 FUTUREONLY只能用于把缺省绑定到用户定义数据类型。它避免使用该用户定义数据类型的现有列继承这个新缺省，仅仅用于将来使用该数据类型的列。,例子：将defdept绑定到学生表的院系列，这样学生院系值为计算机系是无需输入，可节省时间。 sp_bindefault defdept, Student.sDept,例子：创建用户自定义数据类型mychar(源于varchar),创建缺省defcity(Los Angels),将缺省绑定到该用户自定义数据类型上。且该缺省用于所有使用该数据类型定义的表列。,sp_addtype mychar, varchar(50) CREATE DEFAULT

37、 defcity AS Los Angels sp_bindefault defcity,mychar,取消绑定缺省 语法：使用系统存储过程sp_unbindefault sp_unbindefault object_name,FUTUREONLY object_name 指出要取消绑定缺省的列或者用户自定义数据类型； FUTUREONLY只能用于取消用户定义数据类型中的缺省。它避免了用户定义数据类型的现有列中的缺省被取消。缺省将不再应用于用户自定义数据类型的新列。,例子：取消Student表的Sdept列上的规则 sp_unbindefault Student.sDept,例子：用户自定义数

38、据类型mychar已经绑定了缺省defcity，现取消该绑定，但是当前使用mychar类型的表列仍然保留该缺省 sp_unbindefault mychar,FUTUREONLY,SQL SERVER-索引,索引是SQL SERVER使用的一种内部表结构，它基于表中一个或多个列的值，提供对表中行的快速存取。SQL SERVER中的索引类似于书本后的速印，它能帮助你定位内容。,使用索引的优点-索引的主要目标是提高数据页的存取速度。服务器并非对所需数据扫描每一页，而是扫描索引，获得数据存取单元的地址，并直接存取信息。 提高查询的执行速度； 实施数据唯一性（UNQUE）； 加速表之间的连接；,使用索

39、引的缺点-如果设计合理、索引可以提高查询的性能。但是，为表中的每一列创建索引是不明智的。在创建索引时，需要考虑的因素： 创建索引需要花费时间； 每个创建的索引连同原先的数据源都需要空间存储数据； 每次修改表中的数据都需要更新索引；,索引的分类 聚集索引（簇索引）； 非聚集索引（非簇索引）； 唯一索引；,簇索引(聚集索引) 数据被物理地排序。在设置聚集索引时，数据表中的数据也会按照索引的顺序来存放。例如在一个数据表中，将编号设置为聚集索引，此时在该数据表中的数据将会按编号字段的内容来自动排序与存储。加入要插入编号为123的记录，那么数据库会将其放在122和124的记录之间。 每个表可允许创建一个

40、簇索引,簇索引是如何工作的 在簇索引中，数据存储在B-树的叶子层上。当SQL SERVER使用簇索引查找值时执行以下步骤： 1.SQL SERVER从sysindexes表中获得根页的地址； 2.查找值与根页的关键值进行比较； 3.找出小于或等于查找值的最大关键值的页； 4.页指针进到索引的下一层； 5.重复步骤3和4，直到找到数据页； 6.在数据页上查找数据行，直到找到查找值为止。如果在数据页上找不到查找值，则此查询无行返回。,Root Page,Data(Leaf) Pages,查找Eid为E005的行,非簇索引(非聚集索引) 记录行的物理顺序不同于索引的顺序； 非簇索引一般用在用于连接和

41、WHERE子句的列上创建，且它的值可能被经常修改。 每个表可多达249个非簇索引。,簇索引是如何工作的 SQL SERVER缺省情况下创建非簇索引。数据存储以随机顺序出现，担逻辑顺序按其索引给出。非簇索引树包含按排列顺序出现的索引键，其索引的叶子层包含数据页的指针和数据页中行号。 当SQL SERVER使用费簇索引查找值时，它执行以下步骤：,1.SQL SERVER从sysindexes表中获得根页的地址； 2.查找值与根页的关键值进行比较； 3.找出小于或等于查找值的最大关键值的页； 4.页指针进到索引的下一层； 5.重复步骤3和4，直到找到数据页； 6.对于所给出的值，在叶子页上查找行。如

42、果没有匹配的，则此表没有包含匹配的行。 7.如果找到匹配的，指针进到表中的数据页和行ID，现在检索请求的行。,Root Page,Data Pages,查找Eid为E006的行,唯一索引 唯一索引能确保索引无重复，换句话说，如果一个属性列设置了唯一索引，那么这个字段里的内容就是唯一的，不同记录中的同一字段的内容不能相同。无论是簇索引还是非簇索引，都可以将其设为唯一索引。 唯一索引通常都建立在主键字段上，当数据表中创建了主键之后，数据库会自动将该主键创建成为唯一索引，设置为唯一索引的字段通常只能有一条记录的值为NULL，因为NULL值也不能重复。,索引的特性 索引加速了连接表的查询、执行排序和分

43、组； 索引可用来实施行的唯一性； 索引对数据大多数是唯一的列很有用。索引对具有大量重复数据的列没有多大用处； 当修改索引列的数据时，相关索引会被自动更新； 需要时间和资源维护索引，不应创建不被经常使用的索引 簇索引应在非簇索引之前创建。 一般情况下，非簇索引在外键上创建；,索引的创建 语法 CREATE UNIQUECLUSTERED|NONCLUSTERED INDEX idxname ON table_name(column_name ASC|DESC,column_name ) UNIQUE指明每一行应包含不同值的索引； CLUSTERED指出了其数据在索引属性上进行排序的簇索引； NO

44、NCLUSTERED指明创建为非簇索引。只逻辑地组织数据； idxname指出创建的索引名称。 table_name(column_name)指出将要创建索引的表和列。,例子：在表test1上，要求索引表中的值按照itemno列的值的降序排列原则，创建一个聚集索引。 CREATE CLUSTERED INDEX idxitemno ON test1(itemno DESC),例子：在表test1上，要求索引表中的值按照itemno列的值的降序排列原则，创建一个非聚集索引。 CREATE NONCLUSTERED INDEX idxitemno ON test1(itemno DESC) 或者

45、CREATE INDEX idxitemno ON test1(itemno DESC),索引的删除 语法 DROP INDEX table_name.idxname,使用索引调节向导（实例说明） 已经在Department表中创建了以下索引： CREATE NONCLUSTERED INDEX idxdepcode ON Department(cDepartmentCode) CREATE NONCLUSTERED INDEX idxdepname ON Department(vDepartmentName) CREATE NONCLUSTERED INDEX idxdephead ON D

46、epartment(vDepartmentHead) CREATE NONCLUSTERED INDEX idxdeplocation ON Department(vLocation),执行下列查询，分析Department表中索引的使用情况： SELECT Requisition.cRequisitionCode, Position.vDescription,vDepartmentName FROM Position JOIN Requisition ON Requisition.cPositionCode=Position.cPositionCode JOIN Department ON

47、Requisition.cDepartmentCode=Department.cDepartmentCode,使用索引调节向导（实例说明） 已经在Department表中创建了以下索引： CREATE NONCLUSTERED INDEX idxdepcode ON Department(cDepartmentCode) CREATE NONCLUSTERED INDEX idxdepname ON Department(vDepartmentName) CREATE NONCLUSTERED INDEX idxdephead ON Department(vDepartmentHead) CR

48、EATE NONCLUSTERED INDEX idxdeplocation ON Department(vLocation),索引ENHANCEMENTS Fill Factor(填充因子) 填充因子是指在创建索引页时，每个叶子节点的填入数据的填满率。例如，填充因子设为80，则在创建索引时，每个叶子节点索引页中只使用80%的空间用来存放索引数据，剩下的20%的空间用于以后新增加的索引数据。 该项的默认值为0，表示将叶子节点索引页填满，等于100%。 通常来说，如果数据表中的数据很少变动，则将填充因子设得大一点；反之，则可以将填充因子设得低一点。,连接视图的更新,连接视图 由多个表连接构成的视

49、图。（可更新，但有限制） （回忆视图更新章节内容，对视图的更新最终会转化为对多个基本表的更新，且对于视图的更新存在很多的限制。）,例： 有如下视图 CREATE VIEW vwEmployeeCandidate AS SELECT E.cCandidateCode,vFirstName, vLastName,cPhone,siTestScore FROM Employee AS E JOIN InternalCandidate AS I ON E.cCandidateCode=I.cCandidateCode,续上例，对该视图进行更新，更新候选人代码为000018的测试成绩和电话号码，写出如下

50、的更新语句： UPDATE vwEmployeeCandidate SET cPhone=(614)324-5634,siTestScore=75 WHERE cCanidateCode=000018 该语句执行时会产生一个错误！原因是：视图只允许一次更新一个基表。,续上例，对连接视图的更新语句转化为一次只更新一个基本表，则前面的更新语句转换为下面两个语句： UPDATE vwEmployeeCandidate SET cPhone=(614)324-5634 WHERE cCanidateCode=000018 UPDATE vwEmployeeCandidate SET siTestSco

51、re=75 WHERE cCanidateCode=000018,SQL SERVER-编程,术语 批量 变量 打印消息 注解（注释） 控制流语句,批量批量是一起提交SQL SERVER执行的一组SQL语句。 批量被分析、优化、编译和执行。SQL SERVER把批量语句编译成称为执行计划的单个可执行单位，然后一次执行计划中的一个语句。如果批量中有语法错误，那么批量中没有语句被执行。,变量你可以使用变量存储临时值。变量用DECLARE声明，语法如下： DECLARE variable_name data_type 注意在变量符号前的符号 这个符号是必须的，查询处理器用其来识别变量； 变量的赋值可

52、以使用SET或SELECT赋值，其中SET用于给变量赋予某个具体的值，SELECT可通过查询将表中列的值赋给变量； 局部变量的生存期只在一个批量里；,例子：声明变量并赋值 DECLARE x int SET x=100 DECLARE charge int SELECT charge=MAX(siPercentageCharge) FROM ContractRecruiter DECLARE charge float SELECT charge=siPercentageCharge FROM ContractRecruiter,变量在SQL SERVER中有两类变量：局部变量和全局变量 局部变

53、量以一个开头，在批量中声明，当批量执行完后它就丢失。由于是由用户定义它们的，所以又叫做用户定义的变量。 全局变量以两个符号最为前缀 ，它们是由服务器声明的、通常由服务器赋值的变量。,常用的全局变量,打印消息 可以使用PRINT或者SELECT语句在屏幕上显示用户定义的消息或变量的内容。两者有细微的差别。,例子：打印消息 DECLARE myname char(50) SET myname=Helen White PRINT myname SELECT The number of rows is +CONVERT(varchar,rowcount) DECLARE var int SET var

54、=2 SELECT var PRINT var,注解（注释） 可以在批量中使用注释来描述代码的功能，帮助任何阅读代码的人理解代码。注释入口有两种方式： 单行注释入口 - (两个连字符)开始 多行注释入口包含在/* 和*/之间,控制流语句 控制流语句控制批量、存储过程、触发器和事务中SQL语句的执行流程。当语句需要转折或重复时，就要使用控制流语句。控制流语句使得标准SQL具备了编程语言的能力。控制流语句有： IFELSE CASE WHILE,例子：内部候选人被召集参加面试的最低测试成绩为80分。编写一个批量，对于职工号为000008的员工，如果他的测试成绩高于80分，则打印消息Called f

55、or interview和测试成绩，否则，打印消息Rejected-Not called for interview。,IFELSE语句 对于选择性的执行条件，可以使用IFELSE语句。语法： IF boolean_expression sql_statement|statement_block ELSE boolean_expression sql_statement|statement_block boolean_expression 是一个求值TRUE或FALSE的表达式 sql_statement是任意SQL语句 statement_block是一组SQL语句,BEGINEND语句 B

56、EGINEND通常是用来表示一个语句块，其内可以包含一组T-SQL语句，凡是在这个语句块里的所有代码，都属于同一个控制流。语法如下： BEGIN sql_statement|statement_block END,例子：如果Employee表中存在一个vFirstname为Angela的雇员，则打印The detail of the employee：，并且输出该雇员的详细信息。否则，打印消息Employee not found！,IF EXISTS( SELECT * FROM Employee WHERE vFirstName=Angela) BEGIN PRINT The detail

57、of the employee： SELECT * FROM Employee WHERE vFirstName=Angela END ELSE PRINT Employee not found!,例子：如果合同招聘人员的平均费用(siPercentageCharge)低于20，那么所有合同招聘人员的siPercentageCharge增加5，否则只增加3。,IF(SELECT AVG(siPercentageCharge) FROM ContractRecruiter)20 BEGIN UPDATE ContractRecruiter SET siPercentageCharge=siPer

58、centageCharge+5 END ELSE BEING UPDATE ContractRecruiter SET siPercentageCharge=siPercentageCharge+3 END,例子：内部候选人被召集参加面试的最低测试成绩为80分。编写一个批量，对于职工号为000008的员工，如果他的测试成绩高于80分，则打印消息Called for interview和测试成绩，否则，打印消息Rejected-Not called for interview。,DECLARE score int SELECT score=siTestScore FROM InternalCan

59、didate WHERE cEmployeeCode=000008 IF score80 PRINT Rejected-Not called for interview ELSE BEGIN PRINT Called for interview PRINT Test Score=+convert(char(2),score) END,WHILE语句 WHILE是循环语句。语法如下： WHILE Boolean_expression sql_statement|statement_block BREAK sql_statement|statement_block CONTINUE sql_sta

60、tement|statement_block ,WHILE语句语法解释： 当boolean_expression为true时，循环执行while语句块的代码，直到boolean_expression为false为止。如果要在中途停止循环的话，可以使用break或continue语句。break语句是跳出目前所执行的循环，continue种植执行代码，跳回到while的判断语句重新进行条件判断，再根据判断结果决定是否进入循环。,DECLARE count int SELECT count=11 WHILE count0 BEGIN SELECT count=count-1 IF count=4

61、BREAK IF count=6 CONTINUE PRINT count END,输出结果： 10 9 8 7 5,例子：计算1到100的累加和。,DECLARE count int DECLARE sum int SET count=1 SET sum=0 WHILE count=100 BEGIN SELECT sum=sum+count SELECT count=count+1 END PRINT 1到100的累加和= +CONVERT(CHAR(4),sum),CASE语句 CASE语句也是条件判断语句的一种，可以完成比IF语句更强的判断。在IF语句中，如果判断的条件很多，将会用到大

62、量IF的嵌套。,DECLARE mark int SELECT mark=grade FROM SC WHERE Sno=95001 AND Cno=1000 IF mark=60 and mark80 PRINT 学生95001的1000号课程成绩及格 ELSE PRINT 学生95001的1000号课程成绩良好 END,CASE语句 1.简单的CASE语法： CASE input_expression WHEN when_expression THEN result_expression n ELSE else_result_expression END,CASE语句 2.搜索的CASE语

63、法： CASE WHEN boolean_expression THEN result_expression n ELSE else_result_expression END,在简单的case语法代码中，系统会将input_expression的值与每一个when_expression的值作比较，如果相同的话，返回then语句之后的表达式，如果和所有when_expression的值都不相等的话，返回else之后的表达式，如果没有else语句，则返回null。,declare ssex varchar(20) declare output varchar(20) select ssex=sg

64、ender from student where sname=张三 select output= case ssex when 男 then boy when 女 then girl else not known end print 张三是个+output,简单case语法例程,SELECT sno,sname,sage,性别= case when sgender=男 then boy when sgender=女 then girl else 未知 end FROM Student,搜索case语法例程,例子：对于学生选修课程表成绩分析，如果成绩在90分以上，对应的分析结果为优秀，成绩在80

65、90分，分析结果为良好,成绩在7080分，分析结果为中等,成绩在6070分，分析结果为及格,成绩在60分以下，分析结果为不及格。打印格式示例如图：,select sno,cno,grade,分析结果= case when grade=90 then 优秀 when grade=80 and grade=70 and grade=60 and grade70 then 及格 else 不及格 end from sc,SQL SERVER-存储过程,创建存储过程 执行存储过程 向存储过程传递参数 修改存储过程 从存储过程返回值 从存储过程返回多个值 存储过程调用另一个存储过程 重编译存储过程 删除

66、存储过程,存储过程存储过程是一组预先写好的能实现某种功能的SQL程序，指定一个程序名并由SQL SERVER编译后将其存在SQL SERVER中，以后要实现该功能，则可以调用这个程序来完成。用户可以通过存储过程的名字并给出参数来执行它。,存储过程的优点 1.执行速度快效率高：因为SQL SERVER会事先将存储过程编译成二进制可执行代码，在运行时，SQL SERVER不需要再对存储过程进行编译，可以加快执行速度。 2.减少网络流量：由于存储过程是存在数据库服务器上的一组SQL语句，在客户端调用时，只需要使用一个存储过程名及参数即可，那么在网络上传送的流量比传送这一组完整的SQL程序（代码）要小得多，所以可以减少网络流量，提高运行速度。,存储过程的优点(续) 3.模块式编程：存储过程在创