第九章SQLServer数据库基础课件

第九章

SQLServer数据库基础

1编辑版pppt本章要点客户/服务器体系结构SQLServer2000应用环境概述Transact-SQL程序设计存储过程触发器2编辑版pppt9.1客户/服务器体系结构从技术角度看，客户/服务器结构本身是一个非常简单的概念。它是将计算机应用的大任务分解成多个子任务，由多台计算机协同完成。客户机接收客户的数据和处理要求，执行相应程序，把其中的服务请求发给服务器，即向服务器提出对某种信息或数据的服务请求，系统将选择最适宜完成该任务的服务器完成处理，服务器将结果作为服务响应返回客户。在这一过程中，多任务之间存在多种交互关系，即“服务请求/服务响应”关系。因此客户/服务器不应理解为是一种硬件结构，而是一种计算（处理）模式。3编辑版pppt9.1客户/服务器体系结构

客户/服务器具有以下几个特征：服务位置透明性可扩展性4编辑版pppt9.2SOLServer2000应用环境概述SQLServer是一个关系数据库管理系统，最初由Microsoft，Sybase和Ashton-Tate三家公司联合开发的，于1988年推出了第一个OS/2版本。后来，Ashton-Tate公司退出了SQLServer的开发。而在WindowsNT推出后，Microsoft与Sybase在SQLServer的开发上就分道扬镳了，Microsoft将SQLServer移植到WindowsNT系统上，专注于开发推广SQLServer的WindowsNT版本；Sybase则较专注于SQLServer在UNIX操作系统上的应用。1996年，Microsoft推出了SQLServer6.5版本，1998年，SQLServer7.0版本和用户见面；我们介绍的SQLServer2000是Microsoft公司2000推出的最新版本。5编辑版pppt9.2SOLServer2000应用环境概述MicrosoftSQLServer2000包括四个常见版本：⑴企业版（EnterpriseEdition）⑵标准版（StandardEdition）⑶个人版（PersonalEdition）⑷开发者版（DeveloperEdition）此外，SQLServer2000还有DesktopEngine（桌面引擎）和在WindowsCE设备上进行数据存储的WindowsCE版，用户可以根据实际情况选择所要安装的SQLServer2000版本。6编辑版pppt9.2.2SQLServer数据库组成每个SQL服务器都有两种类型的数据库：系统数据库（SystemDatabase）和用户数据库（UserDatabase）,如图9.2所示。系统数据库一般用于存储SQLServer的系统信息，SQLServer用系统数据库来操作和管理系统。用户数据库是用户所创建的数据库。

7编辑版pppt9.2.2SQLServer数据库组成系统数据库mastermodeltempdbmsdbUser1Northwindpubs图9.2MicrosoftSQLServer数据库用户数据库8编辑版pppt系统数据库和用户数据库在结构上完全相同。MicrosoftSQLServer数据库有两种主要的结构：物理结构和逻辑结构。数据库的物理存储结构是讨论数据库文件是如何在磁盘上存储的，数据库的逻辑存储结构指的是数据库是由哪些性质的信息组成。图9.3中的Table就是组成数据库的逻辑成分，而物理实现部分的mdf文件构成了数据库的物理结构。9编辑版pppt物理结构是指实际存储的数据，其构架包括文件，文件组，页，盘区等。文件包括数据库文件和事物日志文件，SQLServer的数据和日志文件可以放置在FAT或NTFS文件系统中，但不能放在压缩文件系统中。物理结构图9.3MicrosoftSQLServer数据库结构简化示意图10编辑版pppt⑴页和盘区页是基本存储单位，大小为8KB/页。页包含了数据本身以及数据的物理存放位置的信息。每页的开始96字节就是用于存储系统信息的，如页大小，剩余空间大小和该页所属对象的ID。在SQLServer2000数据库系统中，页的类型有8种，如表9.1所示。表9.1SQLServer2000页的类型11编辑版pppt表9.1SQLServer2000页的类型页类型内容数据引擎文本和图象全局分配图，辅助全局分配图页剩余空间索引分配图批修改图变化图包含数据行中除text,ntext和image数据外的所有数据索引属性text,ntext和image数据有关盘区分配的信息关于页剩余空间的信息有关表或索引所使用的盘区的信息从上次执行BASKUPLOG语句到批操作之间所修改的盘区信息从上次执行BACKUPDATABASE到现在的更改的盘区的信息12编辑版pppt在数据页上，数据行紧接着页首按顺序放置。在页尾有一个行偏表。在行偏表中，页面上的每一行都有一个条目，每个条目记录那一行的第一个字节与页首的距离。行偏移的条目序列与页中行的序列相反，见图9.4。在SQLServer中，行不能跨页。数据页结构首页数据1数据2可用空间123数据3图9.4MicrosoftSQLServer数据页的结构13编辑版pppt盘区首页数据1数据2可用空间123数据3图9.4MicrosoftSQLServer数据页的结构盘区是为表和索引分配存储空间的基本单元，大小为8个相邻的页，即64KB。为了使空间指派更有效。SQLServer2000对只含少量数据的表不分配完整的盘区。SQLServer2000有两种类型的盘区：统一扩展盘区，由单个对象所有，8页全部只能由拥有该盘区的对象使用；混合扩展盘区，最多可由8个对象共享。通常从混合扩展盘区中向新表或新索引分配页。当表或索引增长到8页时，就变成统一扩展盘区，如果在现有表上创建索引，并且该表包含的行足以在索引中生成8页，则对该索引的所有分配都在统一扩展盘区内进行。14编辑版pppt数据库文件（DatabaseFile）是存放数据库和数据库对象的文件。一个数据库可以有一个或多个数据库文件，一个数据库文件只属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（PrimaryDatabaseFile），扩展名为.mdf，它用来存储数据库的启动信息和部分或全部信息。一个数据库只能有一个主数据库文件。其他数据库文件被称为次数据库文件（SecondaryDatabaseFile）,扩展名为.ndf,用来存储主文件没存储的其他数据。在数据庞大时，次数据库文件可以帮助存储数据。⑵数据库文件15编辑版pppt采用多个数据库文件来存储数据的优点体现在：数据库文件可以不断扩充而不受操作系统文件大小的限制。可以将数据库文件存储在不同的硬盘中，这样可以同时对几个硬盘做数据存取，提高了数据处理的效率，这对于服务型的计算机尤为有用。事务日志文件事务日志文件（TransactionLogFile）是用来记录数据库更新情况的文件，扩展名.ldf。例如，使用INSERT，UPDATE，DELETE等对数据库进行更改的操作都会记录在此文件中，而如SELECT等对数据库内容不会有影响的操作则不会记录在案。一个数据库可以有一个或多个事务日志文件。SQLServer中采用“提前写日志（Write-AheadLogging）”方式，即对数据库的修改先写入事务日志中，再写入数据库。

⑵数据库文件16编辑版pppt为了便于分配和管理，SQLServer允许将多个文件归纳为一组，并赋予此组一个名称，这就是文件组（FileGroup），文件组是将多个数据库文件集合起来形成的一个整体。每个文件组有一个组名。一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用。与数据库文件一样，文件组也分为主文件组和次文件组，主文件中包含了所有的系统表。当建立数据库时，主文件组包括主数据库文件和未明确指定组的其他文件。文件组中可以指定一个缺省文件组，那么在创建数据库文件时，如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定缺省文件组，则主文件组为缺省文件组。只有数据文件才能作为文件组的成员，日志文件不能作为文件组的成员。

(3)文件组17编辑版pppt逻辑结构对应存储数据的抽象表示。SQLServer的数据库不仅仅是数据的存储，所有与数据处理操作相关的信息都存储在数据库中，是一个存放数据的表和支持这些数据的存储、检索、安全性和完整性的逻辑成分所组成的集合。组成数据库的所有逻辑成分称为数据库的对象，如：关系图、表、视图、存储过程、用户、角色、规则、默认值、用户自定义数据类型和用户自定义函数等。SQLServer的对象名可以是对象的全名（完全限定名），也可以是对象的省略名（部分限定名）。完全限定名包含四个标识符：服务器名，数据库名，所有者名和数据库对象名，顺序如下：服务器名、数据库名、所有者名、数据库对象名（Server.database.owner.object）,包含全部这四部分的对象名叫做全名。

逻辑结构18编辑版pppt⑴表结构和视图表9.2常用的系统表sysloginssysdatadasessysmessagessysuserssysobjectsmastermastermaster全部全部系统表数据库功能每个连接到SQL服务器的登录帐号，都有相应的一行每个SQL服务器返回的警告或系统错误，都有相应的一行SQL服务器中每个数据库，都有相应的一行每个Windows用户，Windows工作组，SQL服务器用户和SQL服务器角色，都有相应的一行数据库中每个对象，都有相应的一行19编辑版pppt⑵数据类型

SQLServer2000的数据类型分为系统数据类型与自定义数据类型。系统数据类型如表9.3(详见教材)所示，只有字符与二进制数据类型需要指定长度，其他如整型，日期时间，浮点数据类型，定义时不用指定长度，即长度是默认值。自定义数据类型是建立在SQLServer系统数据类型基础上的。需要指定该类型的名称，建立在其上的系统数据类型及是否允许为空。20编辑版pppt⑶存储过程存储过程是一组编译在单个执行计划中的Transat-SQL语句。可以从客户端应用程序，或从其他存储过程和触发器来调用它。存储过程帮助在不同的应用程序之间实现一致的逻辑。在一个存储过程中，可以设计，编码和测试执行某个常用任务所需的SQL语句和逻辑。之后，每个需要执行该任务的应用程序只须执行此存储过程即可。将业务逻辑编入单个存储过程还提供了单个控制点，以确保业务规则正确执行。存储过程还可以提高性能。许多任务以一系列SQL语句来执行。对前面SQL语句的结果所应用的条件逻辑决定后面执行的SQL语句。如果将这些SQL语句和条件逻辑写入一个存储过程，它们就成为服务器上一个执行计划的一部分，所有工作都可以在服务器上完成。应用程序不必传输存储过程中的所有SQL语句：它们只需传输包含过程名和参数值的EXECUTE。21编辑版pppt⑷用户自定义函数在SQLServer2000中，用户可以创建自定义函数。在SQLServer2000中用户自定义函数是作为一个数据库对象来管理的。可以使用企业管理器或Transat-SQL命令来创建，修改，删除。22编辑版pppt⑸约束，规则，默认值和触发器表中列的属性除了包含数据类型和长度外，还有一些重要的信息，共同维护数据完整性。用于维护数据完整性的对象包括：约束，规则，默认值和触发器。其中，约束在第三章有简单的介绍。规则是一个向后兼容的功能，用于执行一些与CHECK约束相同的功能。CHECK约束是用来限制列值的首选标准方法。CHECK约束比规则更简明，一个列只能应用一个规则，但可以应用多个CHECK约束。CHECK约束作为CREATETABLE语句的一部分进行指定，而规则以单独的对象创建，然后绑定到列上。如果在插入行时没有指定列的值，那么将使用默认值。默认值可以是任何取值为常量的对象，例如，常量，内置函数和数学表达式。在MicrosoftSQLServer2000中，触发器是一类特殊的存储过程，被定义为在表或视图发出UPDATE，INSERT或DELETE语句时自动执行。触发器是功能强大的工具，使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以扩展SQLServer约束，默认值和规则的完整性逻辑检查，但只要约束和默认值提供了全部所需的功能，就应使用约束和默认值。在9.5节将详细介绍触发器。23编辑版pppt企业管理器查询分析器服务管理器（ServerManager）客户端网络实用工具（ClientNetworkUtility）服务器网络实用工具（ServerNetworkUtility）导入和导出数据（ImportandExportData）在IIS中设置SQLXML支持（ConfigureSQLXMLSupportinIIS）事件探查器（Profiler）联机丛书（BookOnline）SQLServer的升级向导9.2.3SQLServer工具24编辑版pppt9.3Transact-SQL程序设计Transact-SQL是SQLServer2000支持的数据库语言，不但遵从入门级的SQL-92标准。而且还支持SQL-92标准的一些强大的扩展。Transact-SQL也有类似于SQL语言的分类，不过做了许多扩充，包括数据定义语言DDL（DataDefinitionLanguage）、数据操纵语言DML（DataManipulationLanguage）、数据控制语言DCL（DataControlLanguage）、系统存储过程(SystemStoredProcedure)和一些附加的语言元素等。在第3章中，我们曾介绍了标准SQL语言的语法及其基本使用方法（包括DDL、DML和DCL）。在此只介绍Transact-SQL语言中与SQL-92不同的一些语句及格式，此外还介绍一些附加的语言元素，包括注释、变量、运算符、函数和流程控制语句。25编辑版pppt1.创建和管理数据库创建数据库包括：定义数据库名；确定数据库文件及其大小；确定事务日志文件的位置和大小。创建数据库使用CREATEDATABASE语句，其语法为：9.3.1Transact-SQL的数据定义功能及实例 26编辑版ppptCREATEDATABASE<数据库名>[ON[PRIMARY]([NAME=<逻辑数据文件名>]FILENAME=‘<操作数据文件路径和文件名>’[，SIZE=<文件长度>][，MAXSIZE=<最大长度>][，FILEROWTH=<文件增长率>])[，…n]][LOGON([NAME=<逻辑日志文件名>，]FILENAME=‘<操作日志文件路径和文件名>’[，SIZE=<文件长度>][，FILEROWTH=<文件增长率>])[，…n]][FORRESTORE]9.3.1Transact-SQL的数据定义功能及实例 27编辑版ppptCreateDatabase学生选课on(Name=学生选课_DAT,FileName='d:\学生选课\学生选课_DAT.MDF',Size=2MB,FileGrowth=10%)Logon(Name=学生选课_LOG,FileName='d:\学生选课\学生选课_LOG.LDF',Size=1MB,FileGrowth=10%)例9.1创建一个学生选课数据库28编辑版pppt数据库的选择使用USE命令，其格式为：USE<数据库名>注意：在TransactSQL中没有语法结束符号。语句结束后按<Enter>键，并另起一行输入GO语句，再按<Enter>键。否则语句不会执行。（2）选择数据库29编辑版pppt删除数据库的语法为：

DROPDATABASE<数据库名1>[,...数据库名n]（3）删除数据库30编辑版pppt2.定义表定义基本表使用CREATETABLE命令，其功能是定义表名、列名、数据类型、初始值和步长等，还包括定义表的完整性约束和缺省值。定义基本表的格式为：

CTEATETABLE<表名>（<列名><类型>|AS<表达式>[<字段级约束>][，……][<记录级约束>]）上述格式有以下问题需要说明。31编辑版pppt2.定义表上述格式有以下问题需要说明。（1）字段级约束字段级约束指只涉及到单个字段的约束，可以使用以下短语定义：1）[NOTNULL|NULL]：定义不允许或允许字段值为空。2）[PRIMATYKEYCLUSTERED|NONCLUSTERED]：定义该字段为主码并建立聚集或非聚集索引。3）[REFERENCES<参照表>（<对应字段>）]：定义该字段为外码，并指出被参照表及对应字段4）[DEFAULT<缺省值>]：定义字段的缺省值。5）[CHECK(<条件>)]：定义字段应满足的条件表达式。6）[IDENTITY(<初始值>，<步长>)]：定义字段为数值型数据，并指出它的初始值和逐步增加的步长值。32编辑版pppt2.定义表（2）记录级约束记录级约束指涉及到表中多个字段的约束，其格式为：

CONSTRAINT<约束名><约束式>

约束式主要有以下几种。

1）[PRIMARYKEY[CLUSTERED|NONCLUSTERED](<列名组>)]：定义表的主码并建立主码的聚集或非聚集索引。

2）[FOREIGNKEY(<外码>)REFERENCES<参照表>(<对应列>)]：指出表的外码和被参照表。3）[CHECK(<条件表达式>)]：定义记录应满足的条件。4）[UNIQUE(<列组>)]：定义不允许重复值的字段组。33编辑版pppt（1）修改基本表修改基本表是指修改列的数据类型、长度、列的完整性约束、增加一个新列或删除列，修改表还包括对修改记录级完整性约束。修改表的语法可分为4种。1）修改字段的定义。修改字段的定义主要为修改字段宽度和字段约束，不能改动字段名称。修改字段的语法为：

ALTERTABLE<表名>ALTERCOLUMN<列名><新类型>[NULL|NOTNULL]<约束定义>3.基本表的维护34编辑版pppt2）增加字段和表约束规则。增加字段和表约束规则的格式为：

ALTERTABLE<表名>ADD{<列定义>|[<表约束定义>]}….3）删除字段或约束规则。删除字段或表级约束规则的格式为：

ALTERTABLE<表名>DROP{[CONSTRAINT]<约束名>|COLUMN<列名>}4）使约束有效或无效。使原表定义的约束暂时有效或无效的格式为：

ALTERTABLE<表名>{CHECK|NOCHECK}CONSTRAINT{ALL|<约束名组>}其中：CHECK为使约束有效，NOCHECK为使约束无效；ALL指全部约束。（2）删除基本表删除基本表的语法为：

DROPTABLE<表名>3.基本表的维护35编辑版pppt数据操纵语句主要包括SELECT（查询）、INSERT（插入）、UPDATE（修改）和DELETE（删除）4种语句。Transact-SQL对SQL-92的数据操纵语句细节作了改进，使之功能更强大，使用更方便。本节对其扩展部分着重讲述。9.3.2Transact-SQL的数据操纵功能及实例36编辑版pppt1.数据检索语句的语句格式在SQL-92中，组成SELECT语句的子句有SELECT、FROM、WHERE、GROUPBY和ORDERBY，其作用分别是指定列、表、元组条件、分组条件和排序要求。Transact-SQL语句则有更多的子句，其语法为：

SELECT<查询列>[INTO<新表名>][FROM<数据源>][WHERE<元组条件表达式>][GROUPBY<分组条件>][HAVING<组选择条件>][ORDERBY<排序条件>][COMPUTE<统计列组>][BY<表达式>]9.3.2Transact-SQL的数据操纵功能及实例37编辑版pppt下面以子句为例，对上述语句进行解释。（1）SELECT子句SELECT子句的功能是确定结果列。SELECT子句的语法为：

SELECT[ALL|DISTINCT][TOP<数值>[PERCENT]]<查询列组>其中的查询列可以表示为：

<查询列>::=*|<表或视图>.*|<列名或表达式>[AS]<列别名>|<列别名>=<表达式>上述SELECT子句语法中：1）ALL|DISTINCT选项：ALL为返回结果集中的所有行；DISTINCT为仅显示结果集中的唯一行。该项不选时，ALL是缺省值。9.3.2Transact-SQL的数据操纵功能及实例38编辑版pppt2）TOP<数值>选项：仅返回结果集中的前<数值>行。如果有[PERCENT]，则返回结果集中的百分之<数值>行记录。3）“*”：指明返回在FROM子句中包括的表和视图的全部列。4）<表或视图>.*：指明返回指定表或视图的全部列。5）<表达式>：结果列表达式可以是一个由列名、常量、函数，通过操作符（或者子查询）连接起来的数据表达式。6）<列别名>：用来代替出现在结果集中的列名或表达式，别名可以在ORDERBY子句中出现，但不能在WHERE、GROUPBY或HAVING子句中出现。9.3.2Transact-SQL的数据操纵功能及实例39编辑版pppt（2）INTO子句INTO子句用于创建一个表，并将查询结果添加到该表中。INTO不能与COMPUTE子句同时使用。如果创建的是临时表，则在表名前加“#”字符。（3）FROM子句FORM子句用于指定查询的数据源表，FROM子句一般为必选子句，其语法格式为：

FROM<数据源组>9.3.2Transact-SQL的数据操纵功能及实例40编辑版pppt数据源的语法为：

<数据源>::=<表名>[[AS]<表别名>][WITH（<表线索组>）]|<视图名>[[AS]<视图别名>]|<行集合函数>[[AS]<别名>]|<嵌套SELECT语句>[[AS]<别名>]|<连接表>上面的语法中，数据源包括表、视图、行集合函数表示的数据集合、嵌套的SELECT语句表示的结果集以及数据连接表的结果集。连接表的语法为：

<连接表>::=<数据源><连接类型><数据源>ON<连接条件>|<数据源>CROSSJOIN<数据源>|<连接表><连接类型>::=[INNER|{{LEFT|RIGHT|FULL}[OUTER]}]JOIN9.3.2Transact-SQL的数据操纵功能及实例41编辑版pppt其中：1）INNER为内连接，它返回所有连接匹配的行。内连接是连接类型的缺省值。2）LEFT[OUTER]为左外连接。左外连接的结果集中不但包括了内连接返回的行，而且还包括了左边表中不满足连接条件的行。那些不满足连接条件的行所对应的右边表的列将会显示NULL值。3）RIGHT[OUTER]为右外连接。右外连接的结果集中不但包括内连接返回的行，而且还包括右边表中不满足连接条件的行。那些不满足连接条件的行所对应的左边表的列将会显示NULL值。4）FULL[OUTER]为完全外连接。完全外连接的结果集中不但包括了内连接返回的行，同时也包括左边表和右边表中的所有不满足条件的行，但是与其对应的右边表或左边表的列将会显示NULL值。9.3.2Transact-SQL的数据操纵功能及实例42编辑版pppt5）ON<连接条件>用于指定连接条件。连接条件表达式为：

<列名><比较符><列名>条件表达式中的两个列名并不一定是相同的名字或具有相同的数据类型，但是两者必须是相兼容的或者SQLServer可以对它们做隐式转换的。如果它们不可以进行隐式转换，则必须使用专门的函数（如CAST函数）进行显式转换。6）CROSSJOIN为交叉连接，即对两个表进行笛卡儿积运算。其结果是返回两个表的交叉匹配结果。交叉连接也可以通过在FROM子句中指定连接表而在WHERE子句中不指明连接条件来表达。9.3.2Transact-SQL的数据操纵功能及实例43编辑版pppt（4）WHERE子句WHERE子句用于指定查询条件以及数据连接条件。WHERE子句的语法为：

WHERE<查询条件>|<旧格式外连接理条件><旧格式外连接条件>::=<列名>{*=|=*}<列名>虽然旧格式的外连接条件也可以表示表间的左连接（*=）或右连接（=*），但由于这种表示存在二义性，所以外连接最好通过FROM子句表示。9.3.2Transact-SQL的数据操纵功能及实例44编辑版pppt（5）GROUPBY子句GROUPBY子句用于对结果集分组。分组会影响到统计函数的结果：如果有GROUPBY子句，则按组进行数据统计；否则，对全部数据统计。GROUPBY的语法为：

GROUPBY[ALL]<分组表达式组>其中：1）ALL为在结果集中包括所有的组，其中包括一组不满足WHERE子句指定条件的记录。这个由不满足条件的元组构成的组，其总和将返回一个空值。2）分组表达式是分组所基于的表达式，该表达式一般为列名。SELECT语句可以有多个分组表达式。9.3.2Transact-SQL的数据操纵功能及实例45编辑版pppt（6）HAVING子句HAVING子句用于指定分组或汇总筛选条件，HAVING子句的格式为：

HAVING<分组或汇总筛选条件>（7）ORDERBYORDERBY子句用于指明排序项和排序要求，其语法为：

ORDERBY<排序项>[ASC|DESC][，…n]（8）COMPUTE子句COMPUTE子句的作用是产生汇总值，并在结果集后将汇总值放入摘要列，COMPUTE与BY配合，将起到换行控制和分段小计的作用。COMPUTE子句的语法如下：

COMPUTE<统计函数组>[BY<分组项>]其中：BY<分组项>表示在结果集中产生换行控制及分段小计。COMPUTEBY必须和ORDERBY配合使用，分组项应完全等于排序项。9.3.2Transact-SQL的数据操纵功能及实例46编辑版pppt查询语句中使用的运算符号Transact-SQL的查询语句中使用的运算符，如表9.4所示。9.3.2Transact-SQL的数据操纵功能及实例类别符号算术运算符+(加)，—(减)，*(乘)，/(除)，%(取余或模)比较运算符=(等于)，>(大于)，<(小于)，>=(大于等于或不小于)，!<(不小于)，<=(小于等于或不大于)，!>(不小于)，<>(不等于)，!=(不等于)范围运算符BETWEEN…AND….(在….之间)，NOTBETWEEN….AND….(不在….之间)子查询运算符IN(在…之中)，NOTIN(不在…之中)，<比较符>ALL(全部)，<比较符>ANY(任一)，<比较符>SOME(一些)，EXISTS(存在)，NOTEXISTS(不存在)字符串查询符+(连接)，LIKE(匹配)，NOTLIKE(匹配)未知值运算符ISNULL(是空值)，NOTISNULL(不是空值)逻辑运算符NOT(非)，AND(与)，OR(或)组合运算符UNION(并)，NUIONALL(并，允许重复的元组)47编辑版ppptLIKE是字符模式匹配操作符。LIKE操作符使用的通配符如表9.5所示。

9.3.2Transact-SQL的数据操纵功能及实例通配符描述示例%包含零个或更多字符的任意字符串。WHEREtitleLIKE'%computer%'将查找处于书名任意位置的包含单词computer的所有书名。_（下划线）任何单个字符。WHEREau_fnameLIKE'_ean'将查找以ean结尾的所有4个字母的名字（Dean、Sean等）。[<字符范围>]指定字符范围([a-f])或集合([abcdef])中的任何单个字符。WHEREau_lnameLIKE'[C-P]arsen'将查找以arsen结尾且以介于C与P之间的任何单个字符开始的作者姓氏，例如，Carsen、Larsen、Karsen等。[^<字符范围>]不属于指定范围([a-f])或集合([abcdef])的任何单个字符。WHEREau_lnameLIKE'de[^l]%'将查找以de开始且其后的字母不为l的所有作者的姓氏。48编辑版pppt在Transact-SQL还提供了一些常用函数以供表达式使用，见表9.6所示。9.3.2Transact-SQL的数据操纵功能及实例函数参数意义AVG([ALL|DISTINCT]<数值表达式>)求数值表达式的值，可以针对全部值或不重复值两种情况COUNT([ALL|DISTINCT]<表达式>)统计表达式的值，可以针对全部值或不重复值两种情况COUNT(*)统计记录数MAX(<表达式>)求表达式的最大值MIN(<表达式>)求表达式的最小值SUM([ALL|DISTINCT]<算术表达式>)求算术表达式的和，可有针对全部值或不重复值两种情况STDEV(<算术表达式>)求表中所有值的标准偏差STDEVP(<算术表达式>)求所有涉及数值的标准偏差VAR(<算术表达式>)求表中所有值的方差VARP(<算术表达式>)求所涉及数值的方差49编辑版pppt例9.2在学生选课数据库中定义学生表Suse学生选课--如果在sysobjects表中存在S表的定义，则首先进行删除处理if(selectcount(*)fromsysobjectswherename='S'andXtype='U')>0 droptable[dbo].[s]GOCREATETABLEs( snumchar(7)primarykey, snamechar(8)NOTNULL, ssexchar(2)default'男'check(ssexin('男','女')), sbirthdatetime, sphonechar(10), dnumchar(7))go50编辑版ppptselect*intoS_1fromSwhereSSex='女'go例9.3选择出S表中的女同学，并存入到S_1表中51编辑版pppt例9.4在SC表中使用Compute……By子句selectSnum,ScorefromSCorderbySnumcomputeAVG(Score)bySnum52编辑版pppt9.3.3

注释符与运算符注释是程序代码中不执行的文本字符串（也称为注释）。在SQLServer中，可以使用两种类型的注释字符：ANSI标准的注释符“--”用于单行注释。与C语言相同的程序注释符号，即“/*……*/”,“/*”用于注释文字的开头，“*/”用于注释文字的结尾，可在程序中标识多行文字为注释。例如：--单行注释/*多行注释

@SnumVARCHAR(4)：存放学号

@SnumVARCHAR(4)：存放姓名*/运算符是在关系的属性或变量之间进行各种运算的符号，包括算术运算符、位运算符、比较运算符、逻辑运算符等，另外，SQLServer使用算术运算符加（+）作字符串的连接运算。53编辑版pppt变量是一种语言中必不可少的组成部分。Transact-SQL语言中有两种形式的变量：一种是用户自己定义的局部变量；另外一种是系统提供的全局变量。局部变量局部变量被引用时要在其名称前加上标志“@”，而且必须先用DECLARE命令定义后才可以使用。其说明形式如下：DECLARE@变量名变量类型[，@变量名变量类型…]9.3.4

变量54编辑版pppt同时，在Transact-SQL中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT或SET命令来设定变量的值。其语法如下：SELECT@局部变量1=变量值1[，……，@局部变量n=变量值n]SET@局部变量=变量值例9.5声明一个长度为4个字符的变量Snum,并赋值一个学号“S003”。解：declare@Snumchar(4)--存放学号

select@Snum=‘S003’--赋值9.3.4

变量55编辑版pppt全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQLServer的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。全局变量不是由用户的程序定义的，它们是在服务器级定义的，只能使用预先说明及定义的全局变量。局部变量的名称不能与全局变量的名称相同，否则会在应用中出错。引用全局变量时必须以“@@”开头。例9.6输出当前版本信息。解：print@@version--输出当前版本信息9.3.4

变量56编辑版pppt9.3.5

流程控制语句流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQLServer2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。Transact-SQL语言使用的流程控制命令与常见的程序设计语言类似，主要有以下几种控制命令。BEGIN…ENDBEGIN…END语句能够将多个Transact-SQL语句组合成一个语句块，并将在BEGIN…END内的所有程序视为一个单元处理。在条件语句（如IF…ELSE）和循环等控制流程语句中，当符合特定条件便要执行两个或者多个语句时，就需要使用BEGIN…END语句，其语法形式为：BEGIN<命令行或程序块>END在BEGIN…END中可嵌套另外的BEGIN…END来定义另一程序块。57编辑版ppptBegin…end说明一个语句块循环语句whiledeclare@positionint,@stringvarchar(8)set@position=1set@string='china'while@position<=datalength(@string)beginselectascii(substring(@string,@position,1)),char(ascii(substring(@string,@position,1)))set@position=@position+1end58编辑版pppt9.3.5

流程控制语句IF…ELSEIF…ELSE语句是条件判断语句。IF…ELSE语句用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。其语法如下：IF<条件表达式><命令行或程序块>[ELSE[条件表达式]<命令行或程序块>]其中，<条件表达式>可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”；ELSE子句是可选的，最简单的IF语句没有ELSE子句部分。IF…ELSE可以进行嵌套，在Transact-SQL中最多可嵌套32级。59编辑版pppt9.3.5

流程控制语句例9.7从SC（选修表）中求出学号为“S030101”的同学的平均成绩，如果此平均成绩大于或等于90分，则输出“优秀”字样。解：IF(selectavg(Score)fromSCwhereSnum=’S030101’groupbySnum)>=90BEGINprint’优秀’END60编辑版pppt9.3.5

流程控制语句CASECASE命令有两种语句格式：（1）CASE<运算式>WHEN<运算式>THEN<运算式>…WHEN<运算式>THEN<运算式>[ELSE<运算式>]END该语句的执行过程是：将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较，如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。61编辑版pppt9.3.5

流程控制语句例9.8从学生表S中，选取Snum，Ssex，如果Ssex为“男”则显示“M”，如果为“女”，显示“F”。解：SELECTSnum,CASESsexWHEN'男'THEN'M'WHEN'女'THEN'F'ENDAS'性别'FROMS62编辑版pppt9.3.5

流程控制语句（2）CASEWHEN<条件表达式>THEN<运算式>…WHEN<条件表达式>THEN<运算式>[ELSE<运算式>]END该语句的执行过程是：首先测试WHEN后的表达式的值。如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值。如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值，此时，如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。63编辑版pppt9.3.5

流程控制语句例9.9从SC表中查询所有同学选课成绩情况，将百分制转换为等级制；凡成绩为空者显示“未考”、小于60分显示“不及格”、60分至70分显示“及格”、70分至80分显示“中等”、80分至90分显示“良好”、大于或等于90分时显示“优秀”。解SELECTSnum,Cnum,CASEWHENScoreISNULLTHEN'未考'WHENScore<60THEN'不及格'WHENScore>=60ANDSCORE<70THEN'及格'WHENScore>=70ANDSCORE<80THEN'中等'WHENScore>=80ANDSCORE<90THEN'良好'WHENScore>=90THEN'优秀'ENDAS'等级'FROMSC64编辑版pppt在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQLServer的标准命令，可分为三种：可以像SQL语句中表引用一样使用的行集函数；对一组值操作，返回单一的汇总值的集合函数；对单一值操作，返回单一值的标量函数。表9.8列出了标量函数的分类。9.3.7函数65编辑版pppt9.3.7函数函数分类解释配置函数游标函数日期和时间函数数学函数元数据函数安全函数字符串函数系统函数系统统计函数文本和图象函数返回当前配置信息返回游标信息对日期和时间输入值执行操作，返回一个字符串、数字或日期和时间值作为函数参数提供的输入值执行计算，返回一个数字值返回有关数据库和数据库对象的信息返回有关用户和角色的信息对字符串输入值执行操作，返回一个字条串或数字值执行操作并返回有关MicrosoftSQLServer中的值、对象和设置的信息返回系统的统计信息对文本或图象输入值或列执行操作，返回有关这些值的信息66编辑版pppt在9.2.2节中，我们已经对存储过程做了简单介绍。本节将深入讨论存储过程的创建、管理等内容。9.4.1存储过程的类型存储过程有3种类型：系统存储过程、用户定义存储过程和扩展存储过程。9.4存储过程67编辑版pppt语法Createprocedure<过程名>[;<版本号>][@<参数名><参数类型>[=<缺省值>][output]…][withrecompile|encryption|recompile,encryption]As<SQL语句组>参数含义

过程名：局部临时过程前加#；全局临时过程前加##

版本号：区分同名的存储过程，定义存储过程组的目的是以便用一条DROPPROCEDURE语句删除一组存储过程。Output:指定参数从存储过程返回的信息。Recompile:每次运行该过程时，将对其重新编译Encrypiton:语句在syscomments表中加密存储9.4.2创建存储过程68编辑版pppt调用Exec[ute]<过程名>[[@<参数名>=]<参数>…[<版本号>]删除Dropprocedure<存储过程名组>9.4.3执行存储过程69编辑版pppt1.创建简单的存储过程例9.11创建一个存储过程，从学生选课数据库中查询学号,姓名,课程名和成绩Use学生选课9.4.4存储过程实例70编辑版ppptIFEXISTS(SELECT*FROMSYSobjectsWHEREname='proc_AllStudents'ANDtype='p')/*如果在系统表SYSobjects中存在存储过程proc_AllStudents则删除它*/DROPPROCEDUREproc_AllStudentsGOCREATEPROCEDUREproc_AllStudentsASSELECTs.snum,sname,cname,scorefroms,sc,cWheres.snum=sc.snumandum=umGO执行以上创建的存储过程：execproc_AllStudents9.4.4存储过程实例71编辑版pppt2.创建带输入参数的存储过程例9.12创建一个存储过程，完成向课程表（C）中插入一条记录，记录值为（’c07’,’java语言’,2），记录值通过输入参数传递到存储过程中。9.4.4存储过程实例72编辑版ppptIFEXISTS(SELECT*FROMSYSobjectsWHEREname='proc_Insert_C'ANDtype='p')/*如果在系统表SYSobjects中存在存储过程proc_AllStudents则删除它*/DROPPROCEDUREproc_Insert_CGOCREATEPROCEDUREproc_Insert_C(@Cnumchar(7),@Cnamechar(10),@Cfreqsmallint)asinsertintoC(Cnum,Cname,Cfreq)values(@Cnum,@Cname,@Cfreq)Go执行以上创建的存储过程：execproc_Insert_C@Cnum='c01',@Cname='java语言',@Cfreq=29.4.4存储过程实例73编辑版pppt3.创建带返回参数的存储过程在SQLServer2000中，从存储过程中返回数据主要有以下4种形式。输出参数，即OUTPUT参数。OUTPUT参数用来表明参数是返回参数，该参数值可以返回给EXECUTE。返回代码，即RETURN语句，它始终是整型值。SELECT语句的结果集。可以从存储过程外引用的全局游标返回数据。本例演示使用OUTPUT参数从存储过程返回数据。该存储过程既有输入参数，也有输出参数例9.13创建一个存储过程，该存储过程接受外部传入的Snum，在SC表中查找该Snum的选课记录，然后输出该学生所选课程的总成绩。9.4.4存储过程实例74编辑版ppptUSE学生选课goIFEXISTS(SELECT*FROMSYSobjectsWHEREname='proc_Search_sumScore'ANDtype='p')DROPPROCEDUREproc_Search_sumScoreGOCREATEPROCEDUREproc_Search_sumScore(@Snumchar(7),@sumScorefloatoutput)asselect@sumScore=sum(Score)fromSCwhereSnum=@Snumgo执行以上创建的存储过程：declare@sumScorefloatexecproc_Search_sumScore'S030101',@sumScoreoutputprint@sumScorego9.4.4存储过程实例75编辑版ppptcreateprocprocs3(@snumchar(8),@avgscorefloatoutput)asselect@avgscore=avg(score)fromscwheresnum=@snumgo76编辑版ppptdeclare@avgscorefloatexecprocs3's030101',@avgscoreoutputprint@avgscore77编辑版pppt触发器是近年来在关系数据库管理系统中应用得比较多的一种完整性保护措施，其功能一般比完整性约束要强得多。一般而言，在完整性约束功能中，当系统检查出数据中有违反完整性约束条件时，仅给出必要提示以通知用户。而数据库触发器（DatabaseTriggers）是响应插入、更新或删除数据库事件而执行的过程，可用于管理复杂的完整性约束，或监控对关系的修改，或通知其他程序关系已发生修改。8.5触发器78编辑版pppt9.5.