2024年4月15日发(作者：)
SQL SERVER2000教程
第一章
第一节 简介
SQL Server 2000是微软公司开发的采用SQL语言的关系型数据库管理系统，它
拥有高弹性与多元化的结构，不仅符合业界的需要，更能与现今的互联网紧密集成，而
对Windows CE/98/NT/2000/XP/2003等操作系统的全面支持的优越性得到最终程序
开发人员的普遍认可。
SQL Server2000是服务器级的数据库管理系统，不论是客户机/服务器、多层结构，
还是Database Web应用程序，SQL Server 2000都起着后端数据库的角色。可以说，
SQL Server 2000是所有数据的汇总与管理
中心，是整个应用系统的枢纽。
第二节 数据库分类 逻辑数据库
(1)系统数据库:
系统数据库作为SQLSERVER默认安装的一部分而安装，由系统表和存储过程组成。系统数据
库、表以及存储过程共同为您提供
了设计、创建、部署和维护世界级SQLSERVER数据库的工具和支持。
a)Master数据库:
是追踪所有其它数据库和配置信息的关键数据库。它控制着用户数据库和SQLSERVER的操作。
Master数据库对你的信息绝对重要，所
以你应该总是保留Master数据库的最近备份。
b)Msdb数据库:
是SQLSERVERAgent的主要支持。SQLSERVERAgent负责复制、任务调度、警报、系统操作
员信息以及备份信息。因此，Msdb也是非常重
要的，哪怕你不使用SQLSERVERAgent,在Msdb中的备份信息也有助于数据库的恢复。
c)Model数据库:
是一个创建新数据库的模板，它包含了应该出现在每个用户数据库中的系统表。在创建一个数据库
时，SQLSERVER会把Model数据库的
全部内容复制到新数据库中。因此，如果你想每次创建数据库时自动地创建某些对象、权限和用户，
你就可以把它们放在Model数据库中。
d)Tempdb数据库:
保存所有临时表和临时存储过程。临时表可以被程序员显示的创建。当客户端连接终止时，这些临
时表也会被自动删除。默认大小为
VER支持两种类型的临时表:1、以##符号开头的全局临时表可为所有连接所用。在
一个全局表创建之后，所有用户都可以使用它。
2、以#符号开头的局部临时表仅在创建它们的连接中可见，并且仅能由表的所有者访问。在Tempdb
中创建临时表不需要在名称前加标志，并
且可以授予权限和废除权限。在Tempdb中直接创建的临时表必须显示的删除，否则它们会一直留
在Tempdb中，除非SQLSERVER重新启动。
(2)演示数据库:
PUBS和NORTHWIND，是SQLSERVER提供的示例数据库。该数据库及其中的表经常在
联机帮助 的文档内容所介绍的示例中使用。
(3)自定义数据库:
用户通过SQL命令自己创建的数据库。
第三节 数据库分类 物理数据库
（1）主要数据文件:
此文件是数据库的起始点。每个数据库必须有且仅有一个主要数据文件。主要数据文件的扩展名.mdf。
（2）次要数据文件:
这些文件是可选的，可以包含所有不在主要数据文件中的数据和对象。数据库可以有多个次要数据文
件。次要数据文件的扩展名为.ndf。
（3）日志文件:
这些文件包含所有用于恢复数据库的事务日志信息。每个数据库至少有一个日志文件。日志文件扩展
名为.ldf。
注意:数据库文件一词通常指的是三种文件类型中的任何一种。数据文件一词则指的是主要数据文件和次
要数据文件。
日志文件一词指的则是作为数据库事务日志一部分的文件。
第四节 数据的存储方式
1、页:
在SQLSERVER中页是最基本的存储单位。页包含数据本身以及数据的物理存放位置的信息。所
有页
的大小都是相同的:8KB(在7.0版本以前，页的大小是2KB)。
2、盘区:
分为混合盘区和统一盘区。当创建一个表时，在混合盘区上分配给该表引一个页。如果对该数据
库来说不存在混合盘区，就给它分配一个这样的盘区。混合盘区可以包含来自几个表的页。一个混合
盘区最多可包含来自8个表的页(因为一个盘区由8页组成，所以大小为64KB)。当一个表增加的页
超过
8页时，就作为统一盘区被保留为该表专用。
第五节 文件组
文件组是一个或多个文件的集合，这些文件组成分配和管理的单元。文件组可以在一开始创建数据库时创
建，以后多个文件添加到数据库中时再创建。但是，一旦文件添加到数据库中以后，就不能再将这些文件
移
动到其他不同的文件组中。文件组只能包含数据文件，不能有事务日志文件。一个文件不能属于多个文件
组。
文件组有三种类型:
1、主文件组:
这些文件组包含主要数据文件和未放入其他文件组的所有其他文件。系统表的所有页都是从主文件组分配
的。
2、用户定义文件组:
这些文件组是在CREATE DATABASE或ALTER DATABASE语句中，或企业管理器中的属性页中使
用FILEGROUP
关键字指定的。
3、默认值:
这些文件组包含所有表和索引的页，这些表和索引在创建时未指定文件组。在每个数据库中，一次只有
一个文件组为默认文件组。如果没有指定默认文件组，则默认文件组为主文件组。
注意:每个数据库最多只能创建256个文件组，而且文件组不能独立于数据库文件创建。文件组是对数据
库中
的文件进行分组的一种管理机制。
第二章 创建和管理数据库
第一节 创建数据库
---格式
Create database 数据库名
On [Primary]
（Name=逻辑文件名，
Filename=物理文件名，
Size=文件起始大小，
Maxsize=文件最大容量，
Filegrowth=文件增量），
……
Filegroup 文件组名
（Name=逻辑文件名，
Filename=物理文件名，
Size=文件起始大小，
Maxsize=文件最大容量，
Filegrowth=文件增长容量）
……
Log on
（Name=逻辑文件名，
Filename=物理文件名，
Size=文件起始大小，
Maxsize=文件最大容量，
Filegrowth=文件增长容量）
……
说明：
（1）Primary:指定下面文件为主文件组的文件。可省略。
（2）Filename:指定文件的实际存储位置。
（3）Size:指定文件的起始大小。
（4）Maxsize:指定文件可达到的最大容量。
（5）Filegrowth:定义的文件的增量。文件的增量设置不能超过Maxsize
设置。可以指定一个确切的增长数值，也可以指定增长的百分比
（起始值的百分比），默认为10%。
（6）Log on :指定下面为日志文件。
---实例
（1）创建只有一个数据文件和一个日志
文件的数据库
Create database student
On Primary
( Name=student_mdf,
Filename='d:sqlstudent_',
Size=2,
Maxsize=10,
Filegrowth=1
)
Log on
( Name=student_ldf,
Filename='d:sqlstudent_',
Size=1,
Maxsize=5,
Filegrowth=1
)
（2）创建有多个数据文件和日志文件的数据库
Create database score
On Primary
( Name=score_mdf,
Filename='d:sqlserverscore_',
Size=5MB,
Maxsize=50MB,
Filegrowth=5MB ),
( Name=score_ndf1,
Filename='d:sqlserverscore_',
Size=3MB,
Maxsize=30MB,
Filegrowth=20% ),
( Name=score_ndf2,
Filename='d:sqlserverscore_',
Size=6,
Maxsize=50,
Filegrowth=4 )
Log on
( Name=score_ldf1,
Filename='d:sqlserverscore_',
Size=8MB,
Maxsize=100MB,
Filegrowth=5MB ),
( Name=score_ldf2,
Filename='d:sqlserverscore_',
Size=10,
Maxsize=100,
Filegrowth=10 )
（3）创建带有多个文件组的数据库
Create database book
On Primary
( Name=book_mdf,
Filename='d:booksbook_',
Size=3,
Maxsize=30,
Filegrowth=3 ),
Filegroup group1
( Name=book_ndf1,
Filename='d:booksbook_',
Size=2MB,
Maxsize=20MB,
Filegrowth=25% ),
Filegroup group2
( Name=book_ndf2,
Filename='d:booksbook_',
Size=4,
Maxsize=30,
Filegrowth=4 )
Log on
( Name=book_ldf1,
Filename='d:booksbook_',
Size=5,
Maxsize=40,
Filegrowth=5 ),
( Name=book_ldf2,
Filename='d:booksbook_',
Size=6,
Maxsize=60,
Filegrowth=30% )
第二节 修改数据库
(1)添加数据文件:
a)格式:Alter database 数据库名 Add File
（ Name=逻辑文件名，
Filename=物理文件名，
Size=文件起始大小，
Maxsize=文件最大容量，
Filegrowth=文件增量 ）
[To FileGroup 文件组名]
b) 说明:To FileGroup :指定添加的数据文件到
哪个文件组中，该文件组必须存在，默认为主文
件组。
c)实例: Alter database student
add file
( Name=student_ndf,
Filename='d:sqlstudent_',
Size=1,Maxsize=5,
Filegrowth=1 )
(2)添加日志文件:
a)格式: Alter database 数据库名
Add Log File
（ Name=逻辑文件名，
Filename=物理文件名，
Size=文件起始大小，
Maxsize=文件最大容量，
Filegrowth=文件增量 ）
b)实例: Alter database student
Add Log File
( Name=student_ldf1,
Filename='d:sqlstudent_',
Size=4,
Maxsize=40,
Filegrowth=15% )
（3）添加文件组:
a)格式:Alter database 数据库名 Add Filegroup 文件
组名。
b)实例:Alter database student Add Filegroup group1
向该文件组中添加文件
Alter database student Add File
( Name=student_ndf2,
Filename='d:sqlstudent_',
Size=3,
Maxsize=30,
Filegrowth=3 )
To Filegroup group1
(4)修改文件(数据文件和日志文件):
a)格式:Alter database 数据库名 Modify File
( Name=逻辑文件名，
[Size=新的文件大小,]
[Maxsize=将要达到的容量,]
[Filegrowth=修改后的增量] ）
b)说明:修改数据文件和日志文件的格式是相同的,但逻辑
文件名和物理文件名不能修改。在修改文件时，必须指定
文件的逻辑名，用来标识将要修改的文件。而不必指定文
件的物理名，否则将出现错误。如果指定修改文件的
Size,则新的文件大小必须比当前文件大小要大。而修改
文件的Maxsize和Filegrowth时，数值可以增大，也可以
和原来的相同。
c)实例: Alter database student Modify File
( Name=student_ldf1,
Filegrowth=20% )
（5）修改文件组属性:
a)Alter database 数据库名 Modify Filegroup 文件组
名 readonly|readwrite|default
b)说明:当想修改文件组的属性时，必须保证该文件组中
存在文件。
Readonly为只读、readwrite为读写、default为默认设
置。一般不修改。
c)实例:Alter database student Modify Filegroup
group1 readwrite
（6）删除文件:
a)格式:Alter database 数据库名 Remove File 文件名
b)说明:不能删除主要数据文件和主要日志文件。
c)实例: Alter database student Remove File
student_ldf1
(7)删除文件组:
a)格式:Alter database 数据库名 Remove Filegroup 文
件组名
b)说明:要删除的文件组中不能包含数据文件。
c)实例: Alter database student Remove File
student_ndf2
Alter database student Remove Filegroup group1
第三节 删除数据库
（1）
（2）
格式:Drop database 数据库名
实例:Drop database student
第四节 设置数据库选项
1、格式:SP_dboption 数据库名 [，‘选项名’ [，ture|false]]
2、说明:SP_dboption:系统的存储过程，可以通过它设置数据库选项，它支持对所有
可用的数据库选项进行设置。
a)如果只设置{SP_dboption 数据库名}，那么将显示在本数据库中设置为True的选项。
b)如果设置{SP_dboption 数据库名，‘选项名’}，那么将显示指定的选项当前处于什么状态（ON
或者OFF）
3、实例:将student数据库的read only选项设置为True,即打开此选项。
SP_dboption student, 'read only','true'
第五节 清空数据库的LOG日志文件
1)格式:DUMP TRANSACTION 数据库名 WITH NO_LOG
(2)实例:DUMP TRANSACTION student WITH NO_LOG
第六节 压缩数据库
有时，人们可能为预期有一定程度活动的数据库分配了太多的空间，当意识到分配了太多空间时，可能决
定压缩分配的空间大小。SQLSERVER提供三种可以压缩数据库大小的方法，autoshrink数据库选项，“企
业
管理器”和“数据库一致性检查器（DBCC）”命令。
1、使用DBCC Shrinkdatabase压缩数据库:
DBCC Shrinkdatabase 命令在默认情况下会对数据库的数
据和日志部分都进行压缩。如果您只想减少数据和日志部
分，则您必须首先压缩整个数据库，然后使用
Alter database 语句来增加数据库的数据或日志部分。
a)、格式:DBCC Shrinkdatabase （数据库名，
压缩后可使用的百分比）
[NOTRUNCATE|TRUNCATEONLY]
b)、说明: NOTRUNCATE:不会将可用的空间释放给
操作系统，而是留给数据库文件。
TRUNCATEONLY:将数据文件未用的空间释放给操作系统。
c)、实例: DBCC Shrinkdatabase（student,20）
TRUNCATEONLY
2、使用“企业管理器”压缩数据库:
有两种方式:
a)在企业管理器中，选中要压缩的数据库，点击鼠标右
键，点击属性，从弹出的“数据库属性”的对话框中选
中“选项”标签，在复选框中选中“自动压缩”选项即可。
b)在企业管理器中，选中要压缩的数据库，点击鼠标右
键，选中“所有任务”，点击“收缩数据库”,填充相应
的选项即可。
第七节 维护数据库
Set ShowPlan_Text
Set ShowPlan_All
第三章 数据类型
第一节 系统数据类型
是SQLSERVER支持的内置数据类型。
1、字符型: char、nchar、varchar、nvarchar、text、ntext
a)char:固定长度的非Unicode字符数据，最大的长度为8000 字符。
b)nchar: 固定长度的Unicode数据，最大的长度为4000字符。
c)varchar:可变长度的非Unicode数据，最大的长度为8000字符。
d)nvarchar: 可变长度的Unicode数据，最大的长度为4000字符。
e)text: 可变长度的非Unicode数据，最大的长度为2^31-1个字符。
f)ntext: 可变长度的Unicode数据，最大的长度为2^30-1个字符
对于定义为char或nchar的列，SQLSERVER将用字符串来填满指定的字节数。
定义为varchar 或nvarchar的列只存储输入的实际长度，可能舍去尾部空间。SQLSERVER处理尾
部空间，取决于SET ANSI_PADDING,以及该列是固定长度还是可变长度。
根据SET ANSI_PADDING值处理尾部空间ANSI_PADDING char和nchar Varchar和 nvarchar
ON 串被空格填充到列的长度 串未被空格填充到列的长度，尾部空间被保留
OFF 串被空格填充到列的长度 串未被空格填充到列的长度，尾部空间被截掉
2、日期和时间型:smalldatetime、datetime。
a)smalldatetime:从1900年1月1日到2079年6月6日，精确到1分钟。
b)datetime:从1753年1月1日到9999年12月31日，精确到三百分之一秒，即3.33毫秒。
3、数据型数值类型
A、整型:smallint、int、tinyint。
a)smallint:从2^15到2^15-1。
b)int:从-2^31到2^31-1。
c)tinyint:从0到255。
B、近似数字数据类型:float和real。
a)float:浮点精度数字数据，从-1.79E+308到1.79E+308。
b)real: :浮点精度数字数据，从-3.40E+308到3.40E+308。
C、精确数字数据类型: decimal和numeric。
a) decimal:不带符号的整数，按10进位。
b) numeric:decimal（十近制）的同义词。
D、货币数据类型:money和smallmoney。
a) money:从-2^63到2^63-1,精确到每个货币单位的万分之一。
b) smallmoney:从-214，748.3648到+214，748.3647，精确到每个货币单位的万分之一。
4、逻辑数据类型:bit
Bit:整形数据，值为1或0。
第二节 用户自定义数据类型
是SQLSERVER支持的内置数据类型。
用户可以通过两个系统存储过程创建和删除用户定义类型。
（它们不是真正的新数据类型，而像是一种复合型数据类型或结构。）
Sp_addtype过程创建用户定义的数据类型，Sp_droptype过程删除定义的数据类型。
1、创建自定义的数据类型
(1)、格式:Sp_addtype 自定义数据类型名,系统的数据类型，’[null | not null]’
(2)、说明:A、用户自定义的数据类型是基于系统的数据类型创建的。
B、[null |not null]：指定该列是否为空。默认为null。
C、如果系统数据类型包括圆括弧，必须用引号把它括起来。
(3)、实例:Sp_addtype birthday，datetime， ’not null’
Create table stu1(sid int, sbirthday birthday)
2、删除自定义的数据类型
（1）、格式[:Exec] sp_droptype 自定义的数据类型。
（2）、实例:[Exec] sp_droptype birthday。
注意:我们还可以通过企业管理器创建自定义的数据类型。首先，选中一个数据库，点击右键，
选中“新建”，从弹开的菜单中选中“用户定义的数据类型”，在打开的窗体中填充相应的选
项即可。
第四章 创建和维护表
第一节 创建表、修改表、删除表
1、创建表
（1）、格式:Create table 表名 （ 字段名1 数据类型,………字段名n 数据类型）
（2）、说明: 表名的定名原则要遵循标识符的定名原则。字段和字段之间用逗号隔开。
（3）、实例:Use book
go
Create table book(bookid int ,bookname varchar(10))
2、修改表
(1)、增加字段:
a) 格式:Alter table 表名 Add 字段名 字段类型
b) 实例:Alter table book add providerid varchar(10),address varchar(20).
(2)、修改字段:
a) 格式:Alter table 表名 Alter column 字段名 字段类型
b) 实例: Alter table book Alter column address varchar(10)
(3)、删除字段:
a) 格式:Alter table 表名 Drop column 字段名
b) 实例: Alter table book drop column address
3、删除表
（1）、格式:Drop table 表名
（2）、说明:把不再起作用的表从数据库中删除。
（3）、实例:Drop table book
第二节 数据的完整性
是通过实现过程数据完整性和声明数据完整性来强制执行的。
1、Identity : identity属性可以生成唯一标识表中每一行的连续值。
(1）格式: Identity （初始值 ，增量）
(2)实例:Create table tab1(id int identity(1,1),name varchar(10))
(3)说明:
a)一个表中只能有一个identity标识的列，字段必须是整型。
b)不能更新定义有identity属性的列。
c)不能向定义有identity属性的列赋null值或附加默认约束。
2、Uniqueidentifier: 使用Uniqueidentifer和Newid函数也可以生成列的唯一值，与Identity属性
类似。
如果创建的列是uniqueidentifer 数据类型，则必须使用newid函数为它生成新值。
A、手动添加列值
(1)实例:Create table tab2 (id uniqueidentifier,name varchar(10))
(2)插入值:insert into tab2 values(newid(),'mary')
B、自动添加列值
(1)实例:Create table tab2 (
id uniqueidentifier NOT NULL ROWGUIDCOL CONSTRAINT [DF_tab2_ID] DEFAULT
(newid()),
name varchar(10))
(2)插入值:insert into tab2 values('mary')
说明：在创建ID列时直接设置列的值为自动填充，值的来源由newid()函数随机产生。
第三节 使用约束
(1)主键（primary key）:唯一标识每一行。
1) 说明:
a)一个表中只可以定义一个主键。
b)不能在主键列中输入null值。
c)最多可定义16列作为主键。
d)定义之后，则不能禁用primary key 约束.
2）实例:
a)创建单一主键:
Create table tab3(id int primary key, bid int)
b)创建复合主键:
Create table tab4(id int,bid int,constraint pk_id_bid primary key(id,bid))
(2)外键（foreign key）: Foreign key定义列值与另一个表的Primary key相匹配的列。
1）说明:
a)Foreign key约束必须引用另一个表的Primary key或Unique约束。
b)相关表中定义为主键的所有列必须作为Foreign key所包含在当前表中。
c)如果使用With nocheck 选项，将不会验证表中的现有数据。
2）实例:
Create table tab5(cid int primary key not null,id int,foreign key(id) references tab3(id))
（3）CHECK约束:根据指定值测试列中的输入值。每次再列中插入或更新数据时均要进行这一测试。
1）说明:
a)可以为Check约束定义Where字句中的类似条件，但它们不能包含子查询。
b)Check约束条件可以引用同一个表中的列。
c)Check约束条件必须对布尔表达式求值。
d)可以绑定有规则的列定义Check约束
2) 实例:
create table tab6(id int, sex char(2) constraint chk_tab6_sex check (sex in ('m','w') ) )
（4）默认约束（default）:此约束用于在用户未提供列值的情况下，提供一个自动添加的列值。
1）说明:
a)一个表中只能有一列定义有Default约束。
b)不能在数据类型为Timestamp的列或具有Identity属性的列中定义Default约束。
2）实例:
Create table tab7(id int, address varchar(20) constraint Def_tab7_add default '吉林省
长春市')
或者Alter table tab7 add constraint def_tab7_id default 100 for id
(5)唯一性约束(Unique):在列中应用unique约束以确保列中不输入重复值。列中所有行的值均不相同。
1）说明:
a)可以向表中的多列应用unique约束。
b)向现有表应用unique约束时，一直会验证现有数据。
c)可以向not null 列应用unique约束，但仅有其中一行能包含null值
2）实例:
Create table tab8(id int unique)
或者
Create table tab9(id int)
Alter table tab9 add constraint unq_id unique (id)
（6）删除约束:对不需要的约束从列中删除。
1）格式
Alter table 表名 drop constraint 约束名
2）实例:
Alter table tab9 drop constraint unq_id
说明：删除列前必须先删除列中的约束
第五章 处理数据
第一节Transact-SQL语言介绍
（1）SQL（structured query language）语言是一种结构化的查询语言。它的功能包括查询、
操作、定义和控制四个方面。它是一种综合的、通用的、功能强大的关系数据库语言；
（2）Transact-SQL:是SQL语言的一种版本，并且只能在SQLSERVER上使用。TSQL是SQLSERVER
功能的核心。不管应用程序的用户界面是什么形式，只要和数据库服务器连接最终都必然体现为
Transact-SQL.
第二节 检索数据
是数据库最频繁执行的活动。在SQL中，使用SELECT语句可以在需要的表单中检索数据。
格式:Select [All | Distinct ] 字段表列 from 表名
[Where 查询条件]
[Group by 字段表列（分组）]
[Having 分组条件（用于已分组的结果）]
[Order by 字段表列 [Asc（升序）| Desc（降序）]]
实例: Use student
go
Create table student (stuno int primary key ,stuname varchar(10) not null,
Class int , sex char(2) check (sex in (‘男’,’女’)))
插入值:
insert into student values(1,'tom',5,'男')
insert into student values(2,'rose’,6,'女')
insert into student values(3,'smith',6,'男')
insert into student values(4,'mary',5,'女')
1、基本语句检索数据
a)全表查询:Select * from student
b)选择字段查询:Select stuno ,stuname from student
c)排列数据:Select stuname,class from student order by stuno desc
d)消除重复项:Select class from student | Select distinct class from student
e)约束结果:Select stuname from student where stuno=1
2、选择语句检索数据
（1）比较运算符:
< 小于 > 大于 <= 小于等于 >= 大于等于 <> 或！= 不等于
实例:Select stuname from student where class <>2
（2）between……and …… 或 not between……and …… 运算符:
between 后是数值的下限，and 后是数值的上限。between……and ……包括上下限。
not between……and ……不包括上下限。
实例:Select * from student where stuno between 2 and 4
（3）In 运算符:查找属性值属于指定集合的元组。
实例:Select * from student where stuno in(1,3,4)
（4）like 运算符:属于字符串匹配条件查询。有两个通配符:
a)‘_’ （下划线）:代表任意单个字符。
实例:Select * from student where stuname like ‘t_m’
b) %（百分号）:代表任意长度的字符串。
实例:Select * from student where stuname like ‘%m’
（5）is [not]null 运算符（未知值）:
实例:Select * from student where class is not null
（6）and或者or运算符:多重条件查询。
实例:Select * from student where stuno=1 or stuno=2 or stuno=3
Select * from student where stuno=1 and stuname=’tom’
第三节 运算符
1、算术运算符
运算符 含义
+ (加) 加法
- (减) 减法
* (乘) 乘法
/ (除) 除法
% (模) 返回一个除法的整数余数
2、比较运算符
运算符 含义
= (等于) 等于
> (大于) 大于
< (小于) 小于
>= (大于或等于) 大于等于
<= (小于或等于) 小于等于
<> (不等于) 不等于
!= (不等于) 不等于 (非SQL_92标准)
!> (不大于) 不等于 (非SQL_92标准)
!< (不小于) 不小于(非SQL_92标准)
3、位运算符
运算符 含义
& (按位 AND) 按位 AND (两个操作数)
| (按位 OR) 按位 OR(两个操作数)
^ (按位互斥 OR) 按位互斥 OR(两个操作数)
4、一元运算符
运算符 含义
+ (正) 数值为正
- (负) 数值为负
~ (位NOT) 返回数字的补数
5、逻辑运算符
运算符 含义
ALL 如果一系列的比较都为TRUE,那么就为TRUE
AND 如果两个布尔表达式都为TRUE,那么就为TRUE
ANY 如果一系列的比较中任何一个为TRUE,那么就为TRUE
BETWEEN 如果操作数在某个范围之内,那么就为TRUE
EXISTS 如果子查询包含一些行,那么就为TRUE
IN 如果操作数等于表达式列表中的一个,那么就为TRUE
LIKE 如果操作数与一种模式相匹配,那么就为TRUE
NOT 对任何其他布尔运算符的值取反
OR 如果两个布尔表达式中的一个为TRUE,那么就为TRUE
SOME 如果在一系列比较中,有些为TRUE,那么就为TRUE
6、运算符优先顺序
当一个复杂的表达式有多个运算符时,运算符优先性决定执行运算的先后次序.执行的顺序可能严重地
影响所得到的值.
运算符有下面这些优先等级.在较低等级的运算符之前先对较高等级的运算符进行求值.
当一个表达式中的两个运算符有相同的运算符优先等级时,基于它们在表达式中的位置来对其从左到右进
行求值。
+ (正)、-(负) 、~(位 NOT)
*(乘)、/(除)、%（模）
+(加)、(+串联)、-(减)
=,>,<,>=,<=,<>,!=,!>,!< 比较运算符
^(位异或)、&(位与)、|(位或)
NOT
AND
ALL、ANY、BETWEEN、IN、LIKE、OR、SOME
= (赋值)
第四节 其他选择
3、其它选择
（1）使用常量:
实例:Select ‘姓名’ ,stuname from student
（2）计算列:
实例:Select stuno,class+2 from student
（3）给结果集起别名:
两种方式: a、字段名 as 别名
b、别名=字段名
实例:Select ‘姓名’=stuname , sex as ‘性别’ from student
第五节 函数
在SQL中，函数对数据或数据组执行操作，然后返回需要的值。函数表达式可以出现在SELECT列表中，
或者
在任何允许出现的位置上。SQL包含了七种函数:
(1)聚合函数:返回汇总值。
(2)转型函数:将一种数据类型转换为另外一种。
(3)日期函数:处理日期和时间。
(4)数学函数:执行算术运算。
(5)字符串函数:对字符串、二进制数据或表达式执行操作。
(6)系统函数:从数据库返回在SQLSERVER中的值、对象或设置的特殊信息。
(7)文本和图像函数:对文本和图像数据执行操作。
1、聚合函数:它对其应用的每个行集返回一个值。
函数 返回值
AVG（表达式） 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。
COUNT（表达式） 返回表达式中非NULL值的数量。可用于数字和字符列。
COUNT（*） 返回表中的行数（包括有NULL值的列）。
MAX（表达式） 返回表达式中的最大值，忽略NULL值。可用于数字、字符和日期时间列。
MIN（表达式） 返回表达式中的最小值，忽略NULL值。可用于数字、字符和日期时间
列。
SUM（表达式） 返回表达式中所有的总和，忽略NULL值。仅用于数字列。
2、转换函数:有CONVERT和CAST两种。
CONVERT实例: Select convert(varchar(10) ,stuno) as stuno,stuname from student
CAST实例: Select cast(stuno as varchar(10)) as stuno,stuname from student
注意:
1)如果没有指定表达式转换后的所得到的数据类型的长度，则SQLSERVER自动提供的长度为30。
2)转换为位（bit）类型时，会将任何非零值转换为1。
3)转换为money或smallmoney类型时，假定货币单位为整数。
4)仅当char或varchar数据类型表示数字时，可以转换为整数数据类型。
5)将char或varchar数据类型转换为money类型时，可包含小数点和美元符号（$）。
6)将char或varchar数据类型转换为浮点或实数类型时，可包含指数符号。
7)如果对新的数据类型的而言值过长，则值将被截断。
8)可以显示的方法将Text列转换为char或varchar列，以及将image列转换为binary或varbinary
列。但是，不能超过255个字符。
3、日期函数
由于不能直接执行算术函数，所以日期函数就十分有用。
它可以帮助您析取出日期值中的天、月和年，这样就可以分别处理它们。
在SQL中，日期的表示方法及有效范围，如下:
日期部分 缩写 值 日期部分 缩写 值
年 yy 1753-9999 周 wk 1-53
季度 qq 1-4 小时 hh 0-23
月 mm 1-12 分钟 mi 0-59
一年中的天 dy 1-366 秒 ss 0-59
一月中的天 dd 1-31 毫秒 ms 0-999
一周中的天 dw 1-7
日期函数如下:
函数 返回值
GETDATE 当前的系统日期。
DATEAD（datepart,number,date） 返回带有指定数字（number）的日期（date），该数字添加到
指定的日期部分（datepart）
DATEDIFF（datepart,date1,date2） 返回两个日期中指定的日期部分之间的差值。
DATENAME（datepart,date） 返回日期中日期部分的字符串形式。
DATEPART（datepart,date） 返回日期中指定的日期部分的整数形式。
YEAR(date) 返回指定日期的年份数值
MONTH（date)返回指定日期的月份数值
DAY（date)返回指定日期的天数值
注：当显示日期列的内容时如果只显示年月日部分，可以使用CONVERT转换函数对日期列进行转换
CONVERT（VARCHAR（10），日期字段名，120）/*120为日期格式YY-MM-DD
SELECT CONVERT(VARCHAR(10),盘点日期,120) AS 盘点日期 FROM 原材料盘点日期明细
表
4、数字函数:对数字值执行代数运算。
ABS（num_expr） 返回数值表达式的绝对值。
ACOS（float_expr） 返回角（以弧度表示），它的余弦值近似于指定的浮点表达式。
ASIN（float_expr） 返回角（以弧度表示），它的正弦值近似于指定的浮点表达式。
ATAN（float_expr） 返回角（以弧度表示），它的正切值近似于指定的浮点表达式。
ATN2（float_expr1, float_expr2） 返回角（以弧度表示），它的正切值在两个近似的浮点表达式之间。
GEILING（num_expr） 返回大于或等于数值表达式的最小整数。
COS（float_expr） 返回以浮点表达式表示的近似于指定角度（以弧度表示）的余弦三角函数的值。
COT（float_expr） 返回以浮点表达式表示的近似于指定角度（以弧度表示）的余切三角函数的值。
DEGREES（num_expr） 返回数值表达式表示的弧度值对应的度值。
EXP（float_expr） 根据指定的近似浮点表达式，返回指数值。
FLOOR（num_expr） 返回小于或等于数值表达式的最大整数
LOG（float_expr） 根据指定的近似浮点表达式，返回自然对数值。
LOG10（float_expr） 根据指定的近似浮点表达式，返回以10为底的对数。
PI（） 返回常量值3.9793
POWER（num_expr,y） 返回幂为y的数值表达式的值。
RADIANS（num_expr） 返回数值表达式表示的度值对应的弧度值。
RAND（[seed]） 随机返回的0到1之间的近似浮点值，可以对seed指定为整数表达式（可选）。
ROUND（num_expr,length） 对数值表达式截取指定的整数长度，返回四舍五入后的值。
SIGN（num_expr） 对正数执行+1操作，对负数和零执行-1操作。
SIN（float_expr） 返回以浮点表达式表示的近似于指定角度（以弧度表示）的正弦三角函数的值。
SQUARE（float_expr） 返回浮点表达式的平均值。
SQRT（float_expr） 返回指定的近似浮点表达式的平方根。
TAN（float_expr） 返回以浮点表达式表示的近似于指定角度（以弧度表示）的正切三角函数的值。
5、字符串函数:可用于binary 和varbinary数据类型列，但主要用于char和varchar数据类型。
Expr1+expr2 返回两个表达式的组合形式的字符串。
ASCⅡ（char_expr） 返回表达式最左边字符的ASCⅡ代码值。
CHAR（int_expr） 返回0到255之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内，
则返回NULL。
CHARINDEX（’pattern’,char_expr） 返回字符表达式中指定模式的起始位置。
DIFFERENCE（char_expr1,char_expr2） 根据比较两个字符表达式的相似度，返回1到4之间的值。
4表示匹配度最佳。
LEN（char_expr） 返回字符表达式的长度。
LOWER（char_expr） 将字符表达式全部转换为小写。
LTRIM（char_expr） 返回删除掉前面空格的字符表达式。
PATINDEX（’%pattern%’,expr） 返回表达式中模式第一次出现的起始位置。返回0表示不存在模式形
式。
REPLICATE（char_expr,int_expr） 返回重复指定次数的字符表达式产生的字符串。
REVERSE（char_expr） 反转字符表达式。
RIGHT（char_expr,int_expr） 返回从字符表达式最右端起根据指定的字符个数得到的字符。
RTRIM（char_expr） 返回删除掉其后空格的字符表达式。
SOUNDEX（char_expr） 评估两个字符串的相似度后得到的4位代码。
SPACE（int_expr） 返回包含指定空格数的字符串。
STR（float_expr[,length[,decimal]]） 返回浮点表达式的字符串表示法。
STUFF（char_expr1,start,length,char_expr2）使用字符表达式2替换字符表达式1的一部分字符，
从指定的位置开
始替换指定的长度。
SUBSTRING（char_expr,start,length） 返回从字符表达式的指定位置开始，截取指定长度得到的字
符集。
UPPER（char_expr） 将字符表达式全部转换为大写。
6、系统函数:用于返回元数据或配置设置。
COALESCE（expr1,expr2, xprN） 返回第一个非NULL表达式。
COL_LENGTH（’table_name’,’column_name’） 返回列的长度。
COLNAME（table_id,column_id） 返回指定的表中的列名。
DATALENGTH（’expr’） 返回任何数据类型的实际长度。
DB_ID（[‘database_name’]） 返回数据库的标识号。
DB_NAME（[database_id]） 返回数据库的名称。
GETANSINULL（[‘database_name’]） 返回数据库的默认空性（Nullability）。
HOST_ID（） 返回工作站的标识号。
HOST_NAME（） 返回工作站的名称。
IDENT_INCR（’table_or_view’） 有新的记录添加入到表中时计数加1。
IDENT_SEED（’table_or_view’） 返回标识列的起始编号。
INDEX_COL（’table_name’,index_id,key_id） 返回索引的列名。
ISNULL（expr,value） 使用指定的值替换的NULL表达式。
NULLIF（expr1,expr2） Expr1与Expr2相等时，返回Null。
OBJECT_ID（’obj_name’） 返回数据库对象标识号。
OBJECT_NAME（’object_id’） 返回数据库对象名。
STATS_DATE（table_id,index_id） 返回上次更新指定索引的统计的日期。
SUSER_SID（[‘login_name’]） 返回用户的登录标识号。
SUSER_ID（[‘login_name’]） 返回用户的登录标识号。这个函数类似于SUSER_SID（）函数，并且
保留了向后的兼容性。
SUSER_SNAME（[server_user_id]） 返回用户的登录标识号。
SUSER_NAME（[server_user_id]） 返回用户的登录标识号。这个函数类似于SUSER_SNAME（）函
数，并且保留了向后的兼容性。
USER_ID（’user_name’） 返回用户的数据库标识号。
USER_NAME（[’user_id’]） 返回用户的数据库名称。
7、文本和图像函数:通常返回有关文本和图像数据所需的信息。文本和图像数据是以二进制格式的形式进
行存储的。
TEXTPTR（col_name） 返回varbinary格式的文本指针值。对文本指针进行检查以确保它指向第一个
文本页。
TEXTVALID（’table__name’,text_ptr）检查给定的文本指针是否有效。返回1表示有效，
返回0表示指针无效。
第六节 增、删、改表数据
一、插入数据:可以使用不同的INSERT语句向表或视图添加全部列数据或只带一部分列数据的行。
1、插入单行:
语法:Insert into 表名 （字段清单）values （列值）
实例:insert into student values（4,’mike’,5,’男’）
2、 插入多行:
语法:Insert into 表名 select 语句
实例:Create table stu（sno varchar（10），sname varchar（10），class int,sex char（2））
Insert into stu select * from student
二、修改数据:可以使用UPDATE语句对以前添加到表中的数据进行修改。
UPDATE语句用来更改现有行中的数据，可以是添加新数据，也可以是修改现有的数据。
语法:Update 表名 set 字段1=列值1，字段2=列值2，……字段n=列值n
where 条件
实例: insert into student values(5,'jack',6,'男')
select * from student;
update student set class=5 where stuno=5
select * from student;
三、删除表中的数据:可以使用DELETE语句删除不在起作用的数据。
语法:Delete from 表名 where 条件
实例:Delete from student where stuno=5
如果字段内容为NULL
DELETE FROM STUDENT WHERE STUNO IS NULL
第七节
使用COMPUTE和COMPUTE BY对数据进行汇总
COMPUTE子句使用聚合函数生成数据的汇总值。COMPUTE和COMPUTE BY子句之间的区别在于
COMPUTE不仅显示汇总的信息，还显示详细信息。生成的汇总值显示为另一行。这样在同一结果集就可
以同时看到详细信息行与汇总行。COMPUTE支持使用聚合函数AVG、COUNT、MIN、MAX和SUM。
语法:Select 列名1，列名2……列名n from 表名
order by列名1
compute [by] 函数名（列名2）
实例:
a、 Use pubs
select type,price from titles
order by type
compute avg(price)
b、select type,advance from titles where type='business' or type='trad_cook'
order by type
compute sum(advance) by type
注意:
a)不允许DISTINCT关键字与行聚合函数一起使用。
b)COMPUTE子句中使用的列名必须出现在语句的选择列表中。
c)同一个语句中不能同时出现SELECT INTO和COMPUTE子句，这是因为包含COMPUTE的语句是以
另一
个结构生成行。
d)如果使用COMPUTE BY，就必须同时使用ORDER BY子句。列于COMPUTE BY后的列必须等同于
ORDER BY 后出现的列或是ORDER BY 后的子集。它们必须具有相同的从左到右的顺序，从同一
个表达式开始，并且不能跳过任何表达式。
第八节 操作符
操作符:使用Rollup操作符可以创建Group by 子句内元素的分类汇总和汇总。 它
支持累计聚合，如求和和求平均。它反映了Group by 子句中使用的列和表达式的位置。
实例:
Use pubs
go
Select stor_id,title_id,sum(qty) as 'total quantity' from sales
group by stor_id,title_id with rollup
操作符（交叉汇总）:使用CUBE操作符可以返回Group by 子句中每个元素的分类汇总。
CUBE产生超聚合行，即由Group by 子句生成的行的信息的汇总。通过创建Group by 子句中
列列表的所有可能的分组组合，生成超聚合行。
实例:
Use pubs
go
Select stor_id,title_id,sum(qty) as 'Total quantity' from sales
group by stor_id,title_id with cube
注意:
a)使用CUBE时不支持使用具有Distinct特性的聚合函数，如COUNT（DISTINCT column）。
b)使用CUBE时,Group by 子句中最多可使用10列或10个表达式。
c)不支持Group by All。
第九节 联接
进行查询时，可以联接多个表来执行相关的查询，通常，我们用一个公用列来联接表，经常是指定一列的
主键和外键关系。
可以有两种方式来联接表。首先，可以在WHERE子句中指定联接条件。这是以前联接表的方式，但现在
仍然支持。如果使用SQL很久了，可能会习惯
这种方法。也可以通过FROM子句指定联接条件。
下面列出了SQLSERVER支持的连接类型:
联接类型 描述
CROSS 返回联接类型左右两侧的表中的所有行的所用组合。即笛卡尔积。
INNER 返回联接类型左侧表和右侧表中有相同值的所有行。
LEFT OUTER 返回左侧表中的所有行，以及与左侧表相匹配的右侧表中的那些行。如果不存在匹配，就在
该字段以null值替代。
RIGHT OUTER 返回右侧表中的所有行，以及与右侧表相匹配的左侧表中的那些行。如果不存在匹配，就
在该字段以null值替代。
FULL OUTER 返回左右两侧表中的所有行。它们完全相同，就输出两遍，否则就根据需要填以null值。
Self 类似于INNEER JOIN ,只是左右两侧的表为同一个表。
1.内联接（Inner join）:两表组合常用方法，经常采用主键和外键匹配的形式。
实例: Select ,hwname,ddid,hwje from huowu join
dingdan on =
左外联接（LEFT OUTER JOIN）:左边的表不加限制。
右外联接（RIGHT OUTER JOIN）:右边的表不加限制。
2.外联接（Outer join）: 全外联接（FULL OUTER JOIN）:不受条件约束，显示两表中所有内容。
实例:Select ,hwname,ddid,hwje from huowu left outer join dingdan on
=
3.交叉联接（Cross join）:典型的笛卡尔积，没有on。
实例:Select ,hwname,ddid,hwje from huowu cross join dingdan。
4.自联接（Self join）:表自身的连接。
实例:Select ,, from huowu as c1 join huowu as c2 on
=
第十节 在查询的基础上创建表
格式:Select 列名清单 into 新表名 from 旧表名
实例:Select * into newstudent from student
第十一节 子查询
是在其他查询结果的基础上提供一种自然而有效的方式表示WHERE子句的条件。
子查询是一个SELECT语句，它定义在另一个SELECT、INSERT、UPDATE或DELETE语句中或定义
在另一个子查询中。
子查询会受到一定限制规则。这些规则为:
1)子查询的选择列表中不能包括文本或图像数据类型。
2)由未修改的比较运算符（不跟有ANY或ALL关键字的一种运算符）引入的子查询不能包括GROUP BY
和HAVING子句，因
为这些子查询必须返回单一值。
3)包含GROUP BY子句的子查询不能使用DISTINCT关键字。
4)由比较运算符引入的子查询的选择列表中只能包含一个表达式或一个列名。
5)子查询不能内部地处理它们自己的结果，因为它们不能包含COMPUTE子句、ORDER BY子句或INTO
关键字。因为系统
首先通过排序结果消除重复的记录，所以可选的DISTINCT关键字可对不包含GROUP BY 子句的子查
询的结果进行有
效排序。
6)使用EXISTS的子查询的选择列表规则等同于那些标准选择列表规则，这是因为使用EXISTS的子查询
构成了一个存在
性测试，并且它返回TURE（真）或FALSE（假）值而非数据值。根据惯例，使用EXISTS的子查询的
选择列表由有星
号（*）构成而不是单个列名。不要指定多个列。
实例:
a)子查询只返回一行和一列。
Use pubs
go
Select title from titles where pub_id=(
select pub_id from publishers where pub_name='Binnet & Hardley')
b)可以使用 IN运算符来操作返回一列或多行的子查询。
Select pub_name from publishers where pub_id in (select pub_id from titles where
type='business')
c)可以返回多行或多列的子查询（事实上是所有的列）使用EXISTS关键字。下面的例子返回与上例相
同的结果集:
Select pub_name from publishers p where exists
(select * from titles t where _id=_id and type='business')
第十二节
使用 TOP 和 PERCENT 限制结果集
TOP 子句限制返回到结果集中的行数。
TOP n [PERCENT]
n 指定返回的行数。如果未指定 PERCENT，n 就是返回的行数。如果指定了 PERCENT，n 就是返回
的结果集行的百分比，
如下所示：
1)TOP 120 /*Return the top 120 rows of the result set. */
2)TOP 15 PERCENT /* Return the top 15% of the result set. */.
说明：
a)如果一个 SELECT 语句既包含 TOP 又包含 ORDER BY 子句，那么返回的行将会从排序后的结果
集中选择。整个结果集按
照指定的顺序建立并且返回排好序的结果集的前 n 行。
b)限制结果集大小的另一种方法是在执行一个语句之前执行 SET ROWCOUNT n 语句。
第十三节 设置数字日期格式
SQL Server 2000允许用指定的数字月份指定日期数据，当使用数字日期格式时，在字符串中以斜
杠()>、连字符(-)或句号(.)作为分隔符来指定月、日、年，字符串必须以下面的形式出现：
数字 分隔符 数字 分隔符 数字 [时间] [时间]
下面的数字日期格式是有效的：
[0]4/15/2001---（mdy） [0]4-15-2001---(mdy) [0]4.15.2001---(mdy)
[04]/2001/15---(myd) 15/[0]4/2001---(dmy) 15/2001/[0]4---(dym)
2001/15/[0]4---(ydm) 2001/[04]/15---(ymd)
当语言被设置为us_english时，默认的日期顺序是mdy,可以使用SET DATEFORMAT语句改变日期的
顺序，根据所用的语言，它也会影响日期顺序。
例：SET DATEFORMAT ymd 将日期设置为年月日格式
第十四节 数据导入导出
1、将数据从SQL Server中导出到Excel文件
p_cmdshell 'bcp oa..verify out c: -c -q -S"audiserver" -U"sa" -P“1"‘
--如果从SQL数据库中,导出数据到Excel,如果Excel文件已经存在,而且已经按照要接收的数据创建好表
头,就可以简单的用: insert into OPENROWSET('.4.0','Excel
5.0;HDR=YES;DATABASE=c:',kc$) select * from 表
2、将数据从Excel文件导入到SQL Server中
--导入数据并生成表
select * into 表 from OPENROWSET('.4.0' ,'Excel
5.0;HDR=YES;DATABASE=c:',kc$)
--如果接受数据导入的表已经存在 insert into 表 select * from
OPENROWSET('.4.0' ,'Excel
5.0;HDR=YES;DATABASE=c:',kc$)
p_cmdshell 'bcp oa..verify in c: -c -q -S"server" -U"sa" -P"1"'
3、将数据从SQL Server中导出到文本文件
p_cmdshell 'bcp "oa..verify" out c: -c -S"server" -U"sa" -P"1"'
p_cmdshell 'bcp "oa..verify" out "d:" -c -t -S "server" -U "sa" -P "1"‘
p_cmdshell 'bcp "Select * from oa..verify" queryout c:
-c -S"server" -U"sa" -P"1"'
4、将数据从文本文件导入到SQL Server中
p_cmdshell 'bcp "oa..verify" in c: -c -S"server" -U"sa" -P"1"'
master 数据库
记录 SQL Server 系统的所有系统级别信息。它记录所有的登录帐户和系统配置设置。
xp_cmdshell
以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。
-c
使用字符数据类型执行大容量复制操作。此选项不提示输入每一字段；它使用 char 作为存储类型，不
带前缀，t（制表符）作为字段分隔符，n（换行符）作为行终止符。
in | out | queryout | format
指定大容量复制的方向。in 是从文件复制到数据库表或视图，out 是指从数据库表或视图复制到文件。只
有从查询中大容量复制数据时，才必须
指定 queryout。根据指定的选项（-n、-c、-w、-6 或 -N）以及表或视图分隔符，format 将创建一个
格式文件。如果使用 format，则还必须指
定 -f 选项。
第十五节
使用TRUNCATE TABLE快速删除表中的所有数据
TRUNCATE TABLE
删除表中的所有行，而不记录单个行删除操作。
语法
TRUNCATE TABLE name
参数
name
是要截断的表的名称或要删除其全部行的表的名称。
注释
TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。
但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释
放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。
TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用
的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数
据，请使用 DROP TABLE 语句。
对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的
DELETE 语
句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。
TRUNCATE TABLE 不能用于参与了索引视图的表。
示例
下例删除 authors 表中的所有数据。
TRUNCATE TABLE authors 权限
TRUNCATE TABLE 权限默认授予表所有者、sysadmin 固定服务器角色成员、db_owner 和
db_ddladmin 固定数
据库角色成员且不可转让。
第十六节
使用CHARINDEX函数代替Like进行数据查询
CHARINDEX函数(比like查找更快速 )
返回字符串中指定表达式的起始位置。
语法
CHARINDEX ( expression1 , expression2 [ , start_location ] )
参数
expression1
一个表达式，其中包含要寻找的字符的次序。expression1 是一个短字符数据类型分类的表达式。
expression2
一个表达式，通常是一个用于搜索指定序列的列。expression2 属于字符串数据类型分类。
start_location
在 expression2 中搜索 expression1 时的起始字符位置。如果没有给定 start_location，而是
一个负数或零，则将从 expression2 的起始位置开始搜索。
返回类型
int
例:
select study_matter,study_data from english_data where study_data like '%问%'
select study_matter,study_data from english_data
where CHARINDEX('问',study_data)>0
第十七节
使用CASE函数格式进行条件查询
USE pubs
SELECT
CASE type
WHEN 'popular_comp' THEN 'Popular Computing'
WHEN 'mod_cook' THEN 'Modern Cooking'
WHEN 'business' THEN 'Business'
WHEN 'psychology' THEN 'Psychology'
WHEN 'trad_cook' THEN 'Traditional Cooking'
ELSE 'Not yet categorized'
END AS Category,
CONVERT(varchar(30), title) AS "Shortened Title",
price AS Price
FROM titles
WHERE price IS NOT NULL
ORDER BY 1
第十八节
用BEGIN…END语句进行数据统计
BEGIN和END语句主要用于将多个Transact-SQL语句组合为一个逻辑块,可把这个逻辑块看作为一个
整体来进行处理,在书写程序时应注意
当控制流语句(条件语句和循环控制语句)执行一个包含两条或两条以上Transact-SQL语句的语句块时,
就应使用此语句。
IF(SELECT COUNT(*) FROM DEPT WHERE DEPT='信息部')>0
BEGIN
SELECT COUNT(NAME) AS 人数 FROM VERIFY WHERE DEPT_ID=(SELECT ID FROM DEPT
WHERE DEPT='信息部')
IF(SELECT COUNT(*) FROM VERIFY WHERE STATION='网络管理工程师')>0
BEGIN
SELECT COUNT(NAME) AS 人数 FROM VERIFY WHERE STATION='网络管理工程师'
END
ELSE
BEGIN
SELECT ‘无记录’AS 人数
END
END
第十九节 使用DECLARE 语句进行数据统计
在批处理或过程的正文中用 DECLARE 语句声明变量，并用 SET 或 SELECT 语句给其指派值。游标变
量可通
过该语句声明，并且可用在其它与游标相关的语句中。所有变量在声明后均初始化为 NULL。
A. 使用 DECLARE
下例使用名为 @find 的局部变量检索所有姓以 Ring 开头的作者信息。
USE pubs
DECLARE @find varchar(30)
SET @find = 'Ring%'
SELECT au_lname, au_fname, phone
FROM authors
WHERE au_lname LIKE @find
B. 在 DECLARE 中使用两个变量
下例从 Binnet & Hardley (pub_id = 0877) 的雇员中检索从 1993 年 1 月 1 日起所雇佣的雇员名
称。
USE pubs
GO
DECLARE @pub_id char(4), @hire_date datetime
SET @pub_id = '0877'
SET @hire_date = '1993-01/01'
SET NOCOUNT OFF
SELECT fname, lname
FROM employee
WHERE pub_id = @pub_id and hire_date >= @hire_date
第二十节
使用GOTO进行循环求和
GOTO语句可以改变控制流程的方向，一般用来从一个语句块、程序或者是嵌套层的控制流程语言结构中
跳出，
它的功能是将执行流变更到标签处，跳过GOTO之后的Transact-SQL语句，使流程在标签处继续处理，
GOTO语
句和标签可在过程、批处理或语句块中的任何位置使用。GOTO语句可嵌套使用，但用户必须注意，过度
使用
GOTO语句会使程序的执行逻辑变得很难理解，程序控制也不容易，所以除非特殊情况，尽量少用GOTO
语句。
--声明两个变量分别用来记录100的数各记录数据之和
DECLARE @Var_Number int,@Var_Sum int
--初始化两个变量
SET @Var_Number=0
SET @Var_Sum=0
--进行求和循环
LoopSum:
SET @Var_Sum=@Var_Sum+@Var_Number
SET @Var_Number=@Var_Number+1
IF @Var_Number<100
GOTO LoopSum
PRINT '1+2+...+100='+CAST(@Var_Sum as char(10))
输出结果：1+2+...+100=4950
第二十一节 使用CASE语句、SUM函数、AVG函数进行综合数据统计
DROP TABLE 成绩表
GO
CREATE TABLE 成绩表(班组 VARCHAR(10),姓名 VARCHAR(6),学科 VARCHAR(4),成绩 INT)
GO
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('一班','王小明','数学',100) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('一班','王小明','语文',98)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('一班','王小明','英语',76) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('一班','李小东','数学',97)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('一班','李小东','语文',99) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('一班','李小东','英语',85)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('二班','刘小刚','数学',100) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('二班','刘小刚','语文',100)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('二班','刘小刚','英语',96) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('二班','张小英','数学',96)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('二班','张小英','语文',88) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('二班','张小英','英语',66)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('二班','周小侠','数学',84) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('二班','周小侠','语文',98)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('二班','周小侠','英语',76) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('三班','张大利','数学',99)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('三班','张大利','语文',100) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('三班','张大利','英语',100)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('三班','张冬杰','数学',50) INSERT INTO 成
绩表(班组,姓名,学科,成绩) VALUES('三班','张冬杰','语文',88)
INSERT INTO 成绩表(班组,姓名,学科,成绩) VALUES('三班','张冬杰','英语',46)
--竖表变横表
SELECT 班组,
姓名,
SUM(CASE 学科 WHEN '数学' THEN 成绩 ELSE 000 END) AS 数学,
SUM(CASE 学科 WHEN '语文' THEN 成绩 ELSE 000 END) AS 语文,
SUM(CASE 学科 WHEN '英语' THEN 成绩 ELSE 000 END) AS 英语,
AVG(CASE 学科 WHEN '数学' THEN 成绩 ELSE 000 END+
CASE 学科 WHEN '语文' THEN 成绩 ELSE 000 END+
CASE 学科 WHEN '英语' THEN 成绩 ELSE 000 END) AS 平均成绩
INTO 成绩表横表
FROM 成绩表
GROUP BY 班组,姓名
ORDER BY 平均成绩 DESC
第二十二节 利用UNION对多条SQL查询语句合并生成表
--横表变竖表
SELECT 班组,姓名,'数学' AS 学科,数学 AS 成绩 INTO 成绩表竖表 FROM 成绩表横表
UNION
SELECT 班组,姓名,'语文' AS 学科,语文 AS 成绩 FROM 成绩表横表
UNION
SELECT 班组,姓名,'英语' AS 学科,英语 AS 成绩 FROM 成绩表横表 ORDER BY 班组
第二十三节
将具有相同字段的记录删除，只留下一条
在SQL Server中除了对拥有十几条记录的表进行人工删除外，实现删除重复记录一般都是写一段代码，
用游标的方法一行一行检查，删除重复
的记录。因为这种方法需要对整个表进行遍历，所以对于表中的记录数不是很大的时候还是可行的，如果
一张表的数据达到上百万条，用游标的方
法来删除简直是个噩梦，因为它会执行相当长的一段时间。在SQL Server中有一种更为简单的方法，它
不需要用游标，只要写一句简单插入语句
就能实现删除重复记录的功能。为了能清楚地表述，我们首先假设存在一个产品信息表Products，其表结
构如下：
CREATE TABLE Products ( ProductID int, ProductName nvarchar (40), Unit char(2), UnitPrice
money )
假设产品Chang和Tofu的记录在产品信息表中存在重复。现在要删除这些重复的记录，只保留其中的一
条。
步骤如下：
第一步——建立一张具有相同结构的临时表
CREATE TABLE Products_temp ( ProductID int, ProductName nvarchar (40), Unit
char(2), UnitPrice money )
第二步——为该表加上索引，并使其忽略重复的值 手动设置：方法是在企业管理器中找到上面建
立的临时表Products _temp，单击鼠标右键，选择所有任务，选择管理索引，选
择新建。然后设置索引选项。
代码：CREATE UNIQUE INDEX [索引名] ON [表名]([索引字段名1], [索引字段名2])
WITH IGNORE_DUP_KEY ON [PRIMARY]
IGNORE_DUP_KEY
控制当尝试向属于唯一聚集索引的列插入重复的键值时所发生的情况。如果为索引指定了
IGNORE_DUP_KEY，并且执行了创建重复
键的 INSERT 语句，SQL Server 将发出警告消息并忽略重复的行。如果没有为索引指定
IGNORE_DUP_KEY，SQL Server 会发出一
条警告消息，并回滚整个 INSERT 语句。
第三步——拷贝产品信息到临时表
insert into Products_temp Select * from Products
此时SQL Server会返回如下提示： 服务器: 消息 3604，级别 16，状态 1，行 1 已忽略重
复的键。 它表明在产品信息临时表Products_temp中不会有重复的行出现。 第四步——将新的
数据导入原表 将原产品信息表Products清空，并将临时表Products_temp中数据导入，最后删
除临时表Products_temp。
truncate table Products insert into Products select * from Products_temp drop table
Products_temp
这样就完成了对表中重复记录的删除。无论表有多大，它的执行速度都是相当快的，而且因为几乎
不用写语句，所以它也是很
安全的。 注意：上述方法中删除重复记录取决于创建唯一索引时选择的字段，在实际的操作过程中
读者务必首先确认创建的唯一索引字段是否正
确，以免将有用的数据删除。
第二十四节
使用table数据类型变量获得临时表
Table数据类型是一种特殊的数据类型,用于存储结果集以供后续处理。该数据类型主要用于临时存储一组
行，这些行将作为表值函数的结果集返回
Table数据类型最主要的用途是作为多条数据记录的临时存储之处，此外还可以作为一个用户定义函数的
返回值。
用户需要注意使用DECLARE @local_variable来声明table类型的变量
--声明TABLE变量@Var_tableGoods
DECLARE @Var_tableGoods TABLE
(
订单编号 [int] NOT NULL,
书籍编号 [int] NOT NULL,
单价 [money] NOT NULL,
数量 [smallint] NOT NULL,
折扣 [real] NOT NULL
)
--将”作者表”中统计的数据添加到TABLE变量@Var_tableAuthor
INSERT INTO @Var_tableGoods
SELECT * FROM 订货表
--将TABLE变量@Var_tableAuthor中的内容显示出来
SELECT * FROM @Var_tableGoods
Table变量有以下优点:
1)Table变量的行为类似于局部变量,定义有明确的作用域,该作用域为声明该变量的函数、存储过程或批
处理的作用范围。
2）在其作用域内，table变量可像常规表那样使用。该变量可应用于SELECT、INSERT、UPDATE和
DELETE语句中用到表或表的表达式的地方。
3）在定义table变量的函数、存储过程或批处理结束时，自动清除table变量
4）在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
5）涉及表变量的事务只在表变量更新期存在，这样就减少了表变量对锁定和记录资源的需求。
6）不支持在表变量之间进行赋值操作。另处，由于表变量作用域有限，并且不是持久数据库的一部分，因
而不受事务回滚的影响。
第二十五节
从SQLSERVER2000中随机提取N条记录
select top N 字段名 from 表名 order by newid()
N为本次要提取的记录数量
NEWID
创建 uniqueidentifier 类型的唯一值。
语法
NEWID ( )
返回类型
uniqueidentifier
下面的SQL语句从表中提取前10条记录,记录按NEWID()函数产生的唯一值进行排序
select top 10 study_matter,study_data from study_english_小学英语 order by newid()
第六章 索引与视图
第一节 索引
1．1 索引的概述
索引是数据库的一种对象，利用索引可以快速检索表中的数据。索引经常被建立在表的主键，外
键或哪些经常访问的字段。一般对于记录较少的表格可不创立索引；
1．2 索引的分类
A．聚集索引：实际物理数据的行次序与索引次序相同。创建聚集时会对表中的数据重新排序每个表只有
一个
聚集索引；使用主键或唯一性约束性该索引自动创立。
B．非聚集索引：是SQLServer中默认的索引类型。表的逻辑次序由索引指定。每个表可以有多个非聚集
索引。
C．唯一性索引和组合索引：聚集索引和非聚集索引中又可以创建唯一性索引，可以确保表中索引的列值
不重
复；还可将多个列组合在一起创建一个组合索引，组合索引效率更高一些。
1．2 索引的格式
格式：Create [Unique][Clustered|Nonclustered] Index IndexName On
Table_Name(Column1,Column2..)
例1：在authors表上创立一个关于title_id列的聚集索引。
Create clustered Index cdx_title_id on authors(title_id)
例2：在titles表上的title_id列创立非聚集的唯一性索引。
Create Unique Nonclustered Index ucdx_title_id on titles(title_id)
例3：在auhtors表频繁访问的city 列和state列上创立组合非聚集索引。
Create Index ucdx_cityAndState on authors(city,state)
1．4 索引的删除
格式：Drop Index _name
例：Drop Index _cityAndState
1．5 索引的统计和更新
A：索引的统计
DBCC Show_Statistics(_name,Index_Name)
如：DBCC Show_Statistics(authors,cdx_title_id)
B：索引的更新：
Update Statistics(ame,index_name)
如：Update Statistics(s,cdx_title_id)
第二节 视图
A．概述：虚拟的表或存储查询。
B．功能：通过视图可以实现
（1）将用户限定在特定的行上；
（2）将用户限定在特定的列上；
（3）将多个表的列联接起来构成一个“表”；
（4）聚合信息而非提供详细信息；
（5）可以使用视图更新表的数据，但关联表之间一般不可以；
2．1 创建视图
格式：Create view 视图名 [（列名）][with encryption] As select 语句 [with check option]
说明：A、with encryption :加密syscomments系统表项，该表项包含有Create view语句文本。
B、with check option:强制所有对视图进行的数据修改语句都要遵守select语句对视图设置的条件。
注意：(a) 定义视图的查询不可以包含Order by,Compute或Compute by 子句或者into关键字；
(b) 不能在临时表上创建视图；
例1
/*在单个表上创立视图*/
Create view vw_student
As
Select stuno,sname,entrancedate
From student
Where entrancedate>'2002-01-01'
Go
注：每个字段可以定义一个别名，表或其他对象也可创建别名
例2 创建一个视图用以显示学生的学号，姓名和所学的课程名称
Create view Vw_studentinfo
As
Select as '学生学号', as '学生姓名',Name as '课程名称'
From Student as a Join score as b
On (=)
Join source as c
On (No=No)
注：Join用来连接两个或多个表，on 参数指定连接条件
2．2 删除视图
Drop view vw_student
2．3 修改视图
例：
Alter view vw_studentinfo
As
Select as ‘学生名称’,Name as ‘课程名称’From Student as a
Join Score as b On (=)
Joio Source as c On(No=No)
2．4 通过视图修改数据
可以将视图看作表输入数据
例如
Create view vw_Source
As
Select SourceNo as ‘课程号’,SourceName as ‘课程名’,SourceDesc as ‘课程详细’
From Source
Insert into vw_Source values(‘102’,’VB’,’第二学期的重点课程’)
第七章Transact-SQL编程
第一节 变量
（1）局部变量
A．TSQL局部变量：是可以保存特定类型的单个数据值的对象。一般说来在一个批处理中被声明、定义，
在这
个批处理（也可能是存储过程或触发器）中用SQL语言对这个变量赋值，或使用这个变量已经被赋予
的值。
当批处理结束后，这个局部变量的生命周期就结束了。
作用：作为计数器在循环中使用；作为返回值；保存临时数据等；
B．声明局部变量：
格式： Declare @变量名1 数据类型，@变量名2 数据类型……
说明：Declare的使用：
（1）指派名称：第一个字符必为@+变量名；
（2）指派系统提供或用户定义的数据类型和长度；
（3）将值设置为NULL;
示例：Declare @myCount int
C．给变量赋值：
Set 语句：如Set @myCount=100
Select语句：如 Select @myCount=100
综合示例
User Pubs
Go
Declare @find varchar(30)
Set @find=’Ring%’
Select au_lname,au_fname,phone
From authors
Where au_lname like @find
(2)全局变量
A)全局变量：变量名前用@@表示；在SQLServer7.0及以后的版本中,全局变量作为函数据形式被使用；
B)一共30多个对于一般的用户来说，一般不用全局变量；
C)SQLServer提供的系统全局变量不用声明直接使用
使用：也可声明自定义的全局变量（类似局部变量）；
常用全局变量如下
@@CONNECTIONS : 返回自上次启动SQL Server以来连接或试图连接的次数，用其可让管理人员方
便地了解今天所有试图连接服务器的次数。
@@DATEFIRST : 返回使用SET DATEFIRST命令而被赋值的DATAFIRST参数值。SET DATEFIRST
命令用来指定每周的第一天是星期几。
@@SERVICENAME : 返回SQL Server正运行于哪种服务状态之下：如 MS SQLServer、MSDTC、
SQLServerAgent。
@@TEXTSIZE : 返回SET语句的TEXTSIZE选项值SET语句定义了SELECT语句中text或image。
数据类型的最大长度基本单位为字节。
@@ROWCOUNT : 返回受上一语句影响的行数，任何不返回行的语句将这一变量设置为0。
@@IDLE : 返回自SQL Server最近一次启动以来CPU处于空闭状态的时间长短，单位为毫秒。
@@IO_BUSY : 返回自SQL Server最后一次启动以来CPU执行输入输出操作所花费的时间(毫秒)。
@@CPU_BUSY : 返回自SQL Server最近一次启动以来CPU的工作时间其单位为毫秒。
@@DBTS : 返回当前数据库的时间戳值必须保证数据库中时间戳的值是惟一的。
@@CURSOR_ROWS : 返回最后连接上并打开的游标中当前存在的合格行的数量。
@@REMSERVER : 返回登录记录中记载的远程SQL Server服务器的名称。
@@VERSION : 返回SQL Server当前安装的日期、版本和处理器类型。
@@MAX_CONNECTIONS : 返回允许连接到SQL Server的最大连接数目。
@@PACK_RECEIVED : 返回SQL Server通过网络读取的输入包的数目。
@@NESTLEVEL : 返回当前执行的存储过程的嵌套级数，初始值为0。
@@LOCK_TIMEOUT: 返回当前会话等待锁的时间长短其单位为毫秒。
@@MAX_PRECISION : 返回decimal 和 numeric数据类型的精确度。
@@SERVERNAME: 返回运行SQL Server 2000本地服务器的名称。
@@PACK_SENT : 返回SQL Server写给网络的输出包的数目。
@@ERROR : 返回最后执行的Transact-SQL语句的错误代码。
@@TRANCOUNT : 返回当前连接中处于激活状态的事务数目。
@@FETCH_STATUS : 返回上一次FETCH语句的状态值。
@@SPID : 返回当前用户处理的服务器处理ID值。
@@IDENTITY : 返回最后插入行的标识列的列值。
@@PACKET_ERRORS : 返回网络包的错误数目。
@@TOTAL_ERRORS : 返回磁盘读写错误数目。
@@TOTAL_WRITE : 返回磁盘写操作的数目。
@@LANGID : 返回当前所使用的语言ID值。
@@LANGUAGE : 返回当前使用的语言名称。
@@TOTAL_READ : 返回磁盘读操作的数目。
@@TIMETICKS : 返回每一时钟的微秒数。
@@OPTIONS : 返回当前SET选项的信息。
@@PROCID : 返回当前存储过程的ID值。
第二节 流程控制命令
1) 块结构：Begin----End
2)If-else- 条件分支：
如：
Use Pubs
Go --这是批处理标识符,表示上面的代码交给服务器编译
If (select avg(price) from titles where type=’mod_cook’)<15 --判断表中有此记录
Begin
Print ‘下列书是优秀的烹调书籍’
Select substring(title,1,35) as title From titles Where type=’mod_book’
End
Else
Print ‘这是价格较为昂贵的烹调书籍’
3). waitfor
Waitfor delay ‘时间’ 延迟多长时间
Waitfor time ‘时间’ 具体某个时间执行
例1：
waitfor delay ’00:00:03’ --将在3秒钟之后执行select语句
select * from student
例2：
waitfor time ’22:30:02’ --将在22:30:02s时执行select语句
select * from student1
4) While（条件语句）条件循环
示例：
Create table test(id int,name char(3))
Go
Declare
@fcount int
set @fcount=0
while (@fcount<6)
Begin
--向字段id编号及name添加字符 并转换为ASCII码对应的字符
Insert into Test values(@Fcount,CHAR(@Fcount+ASCII(‘a’)))
Set @Fcount=@Fcount+1
End
5) Goto
说明：用来改变程序执行的流程，使程序跳到标有标识和程序继续执行；
语法：Goto 标识符
示例 ：分行打印字符1至5
Declare @Fcount int
Begin
Select @Fcount=1
Label:
Print Cast (@Fcount as varchar)
Select @Fcount=@Fcount+1
While @Fcount<6
Goto Label
End
6) Return语句
说明：无条件终止查询、存储过程或批处理处理。存储过程或批处理中Return语句后面的语句都不执行；
当在存储过程中使用Return语句时，此
语句可以指定返回给调用的应用程序、批处理或过程的整数值。如Return未指定值，则存储过程返回0。
大多数存储过程按常规使用返回代码表示
存储过程的成功或失败。没有发生错误时存储过程返回0。任何非零值表示有错误发生。
语法：Return [integer_expression]
（注：此处到讲解存储过程时再介绍）
7)Break 退出while循环
8)Continue 结束本次循环
9)Case表达式
格式：case 判断表达式（变量）/选择条件表达式
when 表达式/条件 then 语句
………………
when 表达式 then 语句
end
例：
select name,chengji=
case
when price<60 then ‘不及格’
when price>60 then ‘及格’
end
from student
10)批处理语句 Go
第三节 存储过程
1．存储过程的概述
存储过程是SQL语句的预编译集合，它存储在数据库内，可由应用程序通过一个调用执行，而且充许用户
声明
变量、有条件执行以及其它强大的功能。使用存储过程可以使程序模块化，可以在服务器端更快的执行，
可
以减少网络流量，还可以增强安全性。
2．存储过程的创建
create procedure 存储过程名 [参数列表] as SQL语句
3．存储过程的执行
exec procedure 参数1、参数2。。。。。。
4．修改存储过程
alter procedure 存储过程名
5．删除存储过程
drop procedure存储过程名
6．例题
学生表（学号、姓名、住址、年级） 成绩表（考号、学号、成绩）
例1、创建立不带参数的存储过程
--查询所有学生的考试成绩
create proc proc_student1
as
select 姓名，成绩 from 学生表，成绩表
where 学生表.学号=成绩表.学号
执行：EXEC proc_student1
例2、创建带输入参数的存储过程
--查询某位学生的成绩
create proc proc_student2
@stuName varchar(8)
as
select姓名，成绩 from 学生表
inner join成绩表 on 学生表.学号=成绩表.学号
where 姓名=@stuName
执行：EXEC proc_student2 ‘Mary’
例3、创建带输出参数的存储过程
--按照所要查找班级求出平均分
create proc proc_student3
@Year char(20),@SumCj int output
as
select @SumCj=sum(成绩) from 学生表
inner join 成绩表 on 学生表.学号=成绩表.学号
where 年级=@Year
执行：declare @sumCj1 int
EXEC proc_student3 ‘一年二班’,@sumCj1 output
Print @sumCj1
例4、创建带返回值的存储过程
Return 语句可以带回一个值，也可带回结果集。
程序如果遇到return，那么return语句以下的所有语句将不再执行，程序
返回存储过程执行的起点。
--将所以查找的班级返回平均值
create proc proc_student4
@Year char(2)
as
begin
declare @avgCj int
if exists(select * from student where 年级=@Year)
begin
select @avgCj=avg(成绩) from学生表
inner join成绩表 on 学生表.学号=成绩表.学号
where 年级=成绩表.学号
end
else
set @avgCj=-1
return @avgCj
end
第四节 游标
1． 游标概述
由于SQLServer是使用结果集来处理数据，因此当需要逐条处理表中的记录时就必须使用游标来处理。
游标一般被定义和使用在服务器端，当游标
开启后，服务器为每一个游标保持一个指针，用来前后检索数据。当游标大量的使用时，服务器端的负担
很重。
2． 游标的特性
A．种类：动态游标、静态游标和、键集游标和仅向前游标；
动态游标（Dynamic）：可以监测对结果集的所有操作（增、删、改），前后滚动。当滚动时，动态
游标反映结果集中所做的修改。
静态游标（Static）：不能监测其他用户的所有操作，前后滚动。以快照形式把当前表存到Tempdb
临时表中，执行指令后将结果集带给游标，
新的数据值不会显示在静态游标中。
键集游标（Keyset）：可以监测用户对数据的修改，前后滚动。在Tempdb中利用主键实现对数据检
索。
对于仅向前游标（Fast_only）：该游标只能向前滚动。
B．功能：可设置游标结果为只读的，或可更新的；游标的移动类型等；
3 游标的状态
--声明
Declare cursor_ name Cursor for Select 语句
--打开
Open cursor_name
--提取数据
Fetch next from cursor_name into 变量1，变量2
--关闭游标
Close cursor_name
--释放游标
Deallocate cursor
4．例题
事例1、查询某个书店的订单的数量
--存储过程名称 StoreOrderSum
--输入参数：@StoreName varchar(30) 书店名称
--返回参数：@SumQty int 订单数量
--定义静态游标名称：SalesQty
Create procedure StoreOrderSum
@StoreName varchar(30)
As
Declare SalesQty cursor static
For select qty from sales
where stor_id= (select stor_id from stores where stor_name like @storename+'%')
Open SalesQty
De4clare @Qty smallint
Declare @sumqty int
select @qty=0
select @sumqty=0
Fetch next from SalesQty into @Qty --游标首次打开时指针指为 Bof 必须移动到第一条记录上
While @@fetch_status=0 --游标状态函数 ，当值不为0时，游标移动指针
--可能到记录末或出现错误
Begin
Set @Sumqty=@Sumqty+@Qty --累计订单数量
Fetch next from SalesQty into @Qty --按查询结果集字段顺序依次将
--当前记录中的数据给变量赋值
End
Close Salesqty
Deallocate Salesqty
Return @SumQty
测试：
Begin
Declare @ff int
Exec @ff=OrderNum 'Bookbeat'
Print cast(@ff as varchar)
End
例题2、打印每名学生的成绩，并对其评定。
--声明游标
declare cursor_stu cursor
static
for select name,cj from student
declare
@name varchar(8),
@cj int
--打开游标
open cursor_stu
--提取游标
fetch next from cursor_stu into @name ,@cj
while (@@fetch_status=0)
begin
if @cj<60
print @name+cast(@cj as char(8))+’不及格’
else
if @cj>=60 and @cj<70
print @name+cast(@cj as char(8))+’ 及格’
fetch next from cursor_stu into @name,@cj
end
--关闭游标
close cursor_stu
--释放游标
deallocate cursor_stu
事例3：将多行数据按每二行合并一行
--声明游标
declare cursor_AA cursor static
for select ID,name from AA
declare @ID_A varchar(10), @NAME_A VARCHAR(10),
@ID_B varchar(10), @NAME_B VARCHAR(10),
@ROWS INT, @ROW INT
SET @ROW=0
--打开游标
open cursor_AA
--提取游标
SET @ROWS=@@CURSOR_ROWS
SELECT @ROWS
while (@ROW<@ROWS)
begin
fetch next from cursor_AA into @ID_A,@name_A
SET @ROW=@ROW+1
fetch next from cursor_AA into @ID_B,@name_B
SET @ROW=@ROW+1
INSERT INTO BB(ID_A,NAME_A,ID_B,NAME_B)
VALUES(@ID_A,@NAME_A,@ID_B,@NAME_B)
SET @ID_A=''
SET @NAME_A=''
SET @ID_B=''
SET @NAME_B=''
end
close cursor_AA --关闭游标
deallocate cursor_AA--释放游标
第五节 事务和锁
1． 事务概述
事务保证多个数据更改操作作为一个单位处理。
2． 事务的特性
（1）原子性：事务必须是完全成功或完全失败；
（2）一致性：事务必须强制招待所有数据一致性规则。
（3）隔离性：所有修改数据的事务都必须彼此隔离开来。
（4）永久性：事务所做的更改是永久而稳固的；
3． 事务的类型
A．显示事务：
事务开始和结束由用户显示的指定；
begin transaction 开始事务
commit tran 提交事务
rollback tran 回滚事务
B．隐式事务：
服务器自动启动并维护。
C．自动提交事务：
自动依据每个批次确立事务，如果某个批次回
滚不影响其他的批处理语句的执行；
例题：银行转帐
create proc proc_bank
@accounts1 char(4),@accounts2 char(4),@price int
AS
BEGIN
begin tran
update 帐户表 set 帐户余额=帐户余额-@price where 帐号=@accounts1
if @@error<>0
rollback tran
update 帐户表 set 帐户余额=帐户余额+@price where 帐号=@accounts2
if @@error<>0
rollback tran
else
commit tran
END
4． 事务的隔离级别
（1）提交读：是SQLServer的默认模式。如果事务已提交，则该级别允许读取数据。
（2）未提交读：是最低的限制的隔离级别。这事务结束前可更改数据以及删除和添加行。
（3）可重复读：进行锁定，这样将只能添加行而不能更新数据。
（4）可串行读：整个数据集将被锁定， 其他用户将不能添加或更新。
5． 设置事务的隔离级别
Set Transction Isolation Level {Read Committed|Read Uncommitted|Repeatable
Read|Serializable}
READ COMMITTED
指定在读取数据时控制共享锁以避免脏读，但数据可在事务结束前更改，从而产生不可重复读取或幻像数
据。该选项是 SQL Server 的默认值。
READ UNCOMMITTED
执行脏读或 0 级隔离锁定，这表示不发出共享锁，也不接受排它锁。当设置该选项时，可以对数据执行未
提交读或脏读；在事务结束前可以更改数据内的数值，行也可以出现
在数据集中或从数据集消失。该选项的作用与在事务内所有语句中的所有表上设置 NOLOCK 相同。这是
四个隔离级别中限制最小的级别。
REPEATABLE READ
锁定查询中使用的所有数据以防止其他用户更新数据，但是其他用户可以将新的幻像行插入数据集，且幻
像行包括在当前事务的后续读取中。因为并发低于默认隔离级别，所
以应只在必要时才使用该选项。
SERIALIZABLE
在数据集上放置一个范围锁，以防止其他用户在事务完成之前更新数据集或将行插入数据集内。这是四个
隔离级别中限制最大的级别。因为并发级别较低，所以应只在必要时
才使用该选项。该选项的作用与在事务内所有 SELECT 语句中的所有表上设置 HOLDLOCK 相同。
脏读:包含未提交数据的读。 例如，事务1 更改了某行。事务2 在事务1 提交更改之前读取已更改的
行。如果事务1 回滚更改，则事务2 便读取了逻辑上从未存在过的行。
不可重复读取: 当某个事务不止一次读取同一行，并且一个单独的事务在两次（或多次）读取之间修改该
行时。因为在同一个事务内的多次读取之间修改了该行，所以每次读
取都生成不同值，从而引发不一致问题
幻像:通过一个任务，在以前由另一个尚未提交其事务的任务读取的行的范围中插入新行或删除现有行。带
有未提交事务的任务由于该范围中行数的更改而无法重复其原始读
取。如果某个连接设置其事务隔离级别为可串行，则 SQL Server 使用键范围锁定以防止幻像。
6． 死锁处理
死锁概念：
在多用户环境下，当不同用户分别锁定一个资源之后，双方都等待对方释放它所锁定的资源时，就产生
一个锁定请求环，
从而出现死锁现象。
事务会对访问或更改的数据加锁。
可以通过强制任务释放锁：
Set deadlock_priority{low|normal}
(如果您将一个任务的优先级设为Low，SQL Server将在死锁的情况下强制该任务释放锁)
第六节 触发器
1． 触发器的概述
触发器是一类特殊的存储过程。当用户对表或视图发出Update、Insert或Delete语句时触发器自动执
行。
在SQLServer中自动为触发器维护两个隐含表：
A．Inserted表 ：存放用户对表插入或更新的但尚未提交到数据库中的记录
B．Deleted 表 ：存放用户对表即将删除的记录
2． 创建触发器
create trigger 触发器名
on 表名
[after|instead of ]
[insert、update、delete]
as
sql 语句
3．修改触发器
格式：Alter trigger trigger_name
4．删除触发器
格式：Drop Trigger trigger_name
例：Drop Trigger tgOutStoctInsert
5．例题
例1、--业务规则：销售金额 = 销售数量 * 销售单价 业务规则。
CREATE TABLE 卷烟销售表
(
卷烟品牌 VARCHAR(40) NULL,
购货商 VARCHAR(40) NULL,
销售数量 INT NULL,
销售单价 MONEY NULL,
销售金额 MONEY NULL
)
GO
--业务规则：库存金额 = 库存数量 * 库存单价 业务规则。
CREATE TABLE 卷烟库存表
(
卷烟品牌 VARCHAR(40) PRIMARY KEY NOT NULL,
库存数量 INT NULL,
库存单价 MONEY NULL,
库存金额 MONEY NULL
)
GO
/*
创建触发器[T_INSERT_卷烟库存表]，这个触发器较简单。
说明： 每当[卷烟库存表]发生 INSERT 动作，则引发该触发器。
触发器功能： 强制执行业务规则，保证插入的数据中，库存金额 = 库存数量 * 库存单价。
注意： [INSERTED]、[DELETED]为系统表，不可创建、修改、删除，但可以调用。
重要： 这两个系统表的结构同插入数据的表的结构。
*/
DROP TRIGGER T_INSERT_卷烟库存表
GO
CREATE TRIGGER T_INSERT_卷烟库存表
ON 卷烟库存表
FOR INSERT
AS
--提交事务处理
BEGIN TRANSACTION
--强制执行下列语句，保证业务规则
UPDATE 卷烟库存表
SET 库存金额 = 库存数量 * 库存单价
WHERE 卷烟品牌 IN (SELECT 卷烟品牌 from INSERTED)
COMMIT TRANSACTION
GO
/*
针对[卷烟库存表]，插入测试数据：
注意，第一条数据（红塔山新势力）中的数据符合业务规则，
第二条数据（红塔山人为峰）中，[库存金额]空，不符合业务规则，
第三条数据（云南映像）中，[库存金额]不等于[库存数量]乘以[库存单价]，不符合业务规则。
第四条数据库存数量为0。
请注意在插入数据后，检查[卷烟库存表]中的数据是否 库存金额 = 库存数量 * 库存单价。
*/
INSERT INTO 卷烟库存表(卷烟品牌,库存数量,库存单价,库存金额)
SELECT '红塔山新势力',100,12,1200 UNION ALL
SELECT '红塔山人为峰',100,22,NULL UNION ALL
SELECT '云南映像',100,60,500 UNION ALL
SELECT '玉溪',0,30,0
结果集
RecordId 卷烟品牌 库存数量 库存单价 库存金额
-------- ------------ -------- ------- ---------
1 红塔山新势力 100 12.0000 1200.0000
2 红塔山人为峰 100 22.0000 2200.0000
3 云南映像 100 60.0000 6000.0000
4 玉溪 0 30.0000 .0000
例2、/* 创建触发器[T_INSERT_卷烟销售表]，该触发器较复杂。 说明: 每当[卷烟库存表]发
生 INSERT 动作，则引发该触发器。 触发器功能： 实现业务规则。 业务规则: 如果销售的卷烟品牌不
存在库存或者库存为零，则返回错误。 否则则自动减少[卷烟库存表]中对应品牌卷烟的库存数量和库存
金额。*/
CREATE TRIGGER T_INSERT_卷烟销售表
ON 卷烟销售表
FOR INSERT
AS
BEGIN TRANSACTION
--检查数据的合法性：销售的卷烟是否有库存，或者库存是否大于零
IF NOT EXISTS (SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌
FROM INSERTED))
BEGIN
RAISERROR('错误！该卷烟不存在库存，不能销售。',16,1)--返回错误提示
ROLLBACK--回滚事务
RETURN
END
IF EXISTS (SELECT 库存数量 FROM 卷烟库存表 WHERE 卷烟品牌 IN (SELECT 卷烟品牌 FROM
INSERTED) AND 库存数量 <= 0)
BEGIN
RAISERROR('错误！该卷烟库存小于等于0，不能销售。',16,1)--返回错误提示
ROLLBACK--回滚事务
RETURN
END
--对合法的数据进行处理--强制执行下列语句，保证业务规则
UPDATE 卷烟销售表 SET 销售金额 = 销售数量 * 销售单价 WHERE 卷烟品牌 IN (SELECT 卷烟
品牌 FROM INSERTED)
DECLARE @卷烟品牌 VARCHAR(40),@销售数量 MONEY
SELECT @卷烟品牌=卷烟品牌,@销售数量=销售数量 FROM INSERTED
UPDATE 卷烟库存表 SET 库存数量=库存数量-@销售数量,库存金额=(库存数量-@销售数量)*库存单
价 WHERE 卷烟品牌=@卷烟品牌
COMMIT TRANSACTION
--请大家自行跟踪[卷烟库存表]和[卷烟销售表]的数据变化。
--针对[卷烟销售表]，插入第一条测试数据，该数据是正常的。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT '红塔山新势力','某
购货商',10,12,1200
--针对[卷烟销售表]，插入第二条测试数据，该数据 销售金额 不等于 销售单价 * 销售数量。
--触发器将自动更正数据，使 销售金额 等于 销售单价 * 销售数量。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT '红塔山人为峰','某
购货商',10,22,2000
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中找不到对应。
--触发器将报错。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT '红河V8','某购货
商',10,60,600
/*
结果集
服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 15
错误！该卷烟不存在库存，不能销售。
*/
--针对[卷烟销售表]，插入第三条测试数据，该数据中的卷烟品牌在 卷烟库存表中库存为0。
--触发器将报错。
INSERT INTO 卷烟销售表(卷烟品牌,购货商,销售数量,销售单价,销售金额) SELECT '玉溪','某购货商
',10,30,300
/*
结果集
服务器: 消息 50000，级别 16，状态 1，过程 T_INSERT_卷烟销售表，行 29
错误！该卷烟库存小于等于0，不能销售。
*/
6、补充
1、本示例主要通过一个简单的业务规则实现来进行触发器使用的说明，具体的要根据需要灵活处理； 2、
关于触发器要理解并运用好 INSERTED ，DELETED 两个系统表； 3、本示例创建的触发器都
是 FOR INSERT ,具体的语法可参考：
Trigger语法
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ] --用于加密触发器
{
{ { FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
4、关于触发器，还应该注意 (1)、DELETE 触发器不能捕获 TRUNCATE TABLE 语句。 (2)、触发器中
不允许以下 Transact-SQL 语句：
ALTER DATABASE CREATE DATABASE DISK INIT DISK RESIZE DROP DATABASE LOAD DA
TABASE LOAD LOG RECONFIGURE RESTORE DATABASE RESTORE LOG (3)、触发器最多可
以嵌套 32 层。
第七节 用户自定义函数
CREATE FUNCTION
1、创建用户定义函数，它是返回值的已保存的 Transact-SQL 例程。用户定义函数不能用于执行一组
修改全局
数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通
过
EXECUTE 语句执行。用户定义函数用 ALTER FUNCTION 修改，用 DROP FUNCTION 除去。
例：CREATE FUNCTION 创建了一个函数CubicVolume 来计算立方体的体积，变量CubeLength
CubeWidth CubeHeight 为输入参数，返回值为数值型。
BEGIN 表明函数体的开始，END 表明函数体的结束
Create Function CubicVolume(@CubeLength decimal(4,1),@CubeWidth decimal(4,1),@Cub
eHeight decimal(4,1) )
Returns decimal(12,3)
As
Begin
Return (@CubeLength * @CubeWidth * @CubeHeight)
End
--SELECT olume (10,8,6)
2、内嵌表值函数
用户定义函数与table 型数据
Use pubs
Create Function SalesByStore(@storeid varchar(30))
Returns Table
As
Return (Select title, qty From sales s, titles t
Where _id = @storeid and _id = _id)
-- select * from sales
SELECT * FROM yStore(7131)
3、利用DATENAME函数创建自定义日期转换函数
DATENAME函数是返回代表指定日期部分的字符串
语法说明
DATENAME(DATEPART,DATE)
参数说明
DATEPART
是指定应返回的日期部分的参数,WEEKDAY(DW)日期部分返回星期几(星期天、星期一等）
返回类型
NVARCHAR
例：根据日期返回特定格式的日期 如：2007年10月1日16时58分42秒
DROP FUNCTION t_Date
GO
CREATE FUNCTION t_Date(@date datetime)
RETURNS VARCHAR(38)
as
begin
RETURN '今天是'+DATENAME(yy,@date)+'年'+
+DATENAME(mm,@date)+'月'+
+DATENAME(dd,@date)+'日'+
+DATENAME(hh,@date)+'时'+
+DATENAME(mi,@date)+'分'+
+DATENAME(ss,@date)+'秒'+
+DATENAME(dw,@date)
End
GO
SELECT t_Date(GETDATE()) AS 日期
第八节 提前存储过程的参数列表
1、利用系统表提取存储过程的参数列表
select as parameter, as type,,,aram as
parameter_output,
case when is null then 0 else end as scale
from syscolumns a left join systypes b on =
where =(select id from sysobjects where name='IUD_CHTKD')--IUD_CHTKD是存储过程
的名称
2、利用系统存储过程表提取存储过程的参数列表
SP_HELP 'IUD_CHTKD'--IUD_CHTKD是存储过程的名称
第章
第一节
第二节
第三节
第四节
第章
第一节
第二节
第三节
第四节
发布评论