T-SQL语法

T-SQL语法

一、数据库存储结构 SQL Server中的每个数据库有多个操作系统文件组成，数据库的所有资料、对象和数据库操作日志均存储在这些操作系统文件中。根据这些文件的作用不同，可以将它们划分为以下三类：

? 主数据文件：每个数据库有且只有一个主数据文件，它是数据库和其它数据文件

的起点。主数据文件的扩展名一般为.mdf；

? 辅数据文件：用于存储主数据文件中未存储的剩余资料和数据库对象，一个数据

库可以没有辅数据文件，但也可以同时拥有多个辅数据文件。辅数据文件的多少主要根据数据库的大小、磁盘存储情况和存储性能要求而设置。辅数据文件的扩展名一般为.ndf；

? 日志文件：存储数据库的事务日志信息，当数据库损坏时，管理员使用事务日志

恢复数据库。日志文件的扩展名一般为.ldf。

每个数据库中至少两个文件：主数据文件和日志文件。 SQL Server数据库文件除操作系统所赋予的物理文件名称外，还有一个逻辑名称。数据库的逻辑名称应用于Transact-SQL语句中。例如，对于master系统数据库，master为其逻辑名称，使用Transact-SQL语句操作数据库时，均使用该名称。而对应的物理文件名称为master.mdf、其日志文件名称为master.ldf。

为了管理方便，可将多个数据库文件组织为一组，称作数据库文件组。文件组能够控制各个文件的存放位置，其中的每个文件常建立在不同的硬盘驱动器上，这样可以减轻每个磁盘驱动器的存储压力，提高数据库的存储效率，从而达到提高系统性能的目的。SQL Server采用比例填充策略使用文件组中的每个文件提供的存储空间。

在SQL Server中建立文件和文件组时，应注意以下两点：

? 每个文件或文件组只能属于一个数据库，每个文件也只能成为一个文件组的成

员，文件和文件组不能跨数据库使用；

? 日志文件是独立的,它不能成为文件组的成员。也就是说，数据库的资料内容和

日志内容不能存入相同的文件或文件组。

SQL Server中的数据库文件组有以下三种类型：

? 主文件组：其中包数据库的主数据文件和不属于其它文件组的数据库文件，数据

库系统表的所有页面存储在主文件组中；

? 用户定义文件组：数据库创建语句（CREATE DATABASE）或修改语句（ALTER

DATABASE）中使用FILEGROUP关键词所指定的文件组；

? 默认文件组：在创建数据库对象时，如果没有为它们指定文件组，它们将被存储

在默认文件组中。可以使用ALTER DATABASE语句修改数据库的默认文件组设置，但每个数据库同时最多只能有一个默认文件组。当数据库没有指定默认文件组时，主文件组将被作为默认文件组使用。

由于默认文件组的特殊作用，所以在创建数据库对象时，即使不指定用户文件组，SQL Server也能照常执行。

1.使用Transact-SQL语句建立数据库 CREATE DATABASE 语句的语法格式为： CREATE DATABASE database_name [ ON [PRIMARY]

[ [,...n] ] [, [,...n] ] ]

[ LOG ON { [,...n]} ] [ FOR LOAD | FOR ATTACH ] ::=

( [ NAME = logical_file_name, ] FILENAME = 'os_file_name' [, SIZE = size]

[, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment] ) [,...n] ::=

FILEGROUP filegroup_name [,...n]

其中，database_name为新建数据库的逻辑名称，在一个SQL Server服务器上，必须保证各数据库名称是唯一的。

ON子句显示指定存储数据库资料部分所使用的数据文件和文件组列表，PRIMARY关键词说明其后的数据文件属于主文件组。如果PRIMARY关键词未被指定，则关键词CREATE DATABASE后的第一个文件列表将成为主数据文件。定义数据文件列表中各数据文件项，有多个数据文件项时，相互之间以逗号分隔。

其中，logical_file_name参数指出数据文件的逻辑名称，数据文件的逻辑名称应用在Transact-SQL语句中。在同一个数据库中，必须保持数据文件的逻辑名称是唯一的。

os_file_name参数说明数据文件对应的操作系统文件名称，即数据文件的物理文件名称及其路径。

size参数指定数据文件的初始长度，其单位为MB或KB，默认时为MB。对于主数据文件，其size参数的最小值应等于model数据库中主数据文件的长度。对于其它数据文件，其长度最小为512KB。size参数默认时，对于辅数据文件和日志文件，SQL Server将其长度设置为1MB，而对于主数据文件，SQL Server将其长度设为model数据库中主数据文件的长度。

SQL Server中，如果打开数据库的autoshrink选项，当数据库文件空间用尽时，系统将自动增加数据文件的大小。max_size参数定义数据文件可以增加到的最大尺寸，其单位为MB或KB。如果未定义max_size参数，数据库文件的长度可根据需要一直增加，直到磁盘空间用尽为止。这时它等同于MAXSIZE = UNLIMITED。

growth_increment参数说明数据文件空间的每次增加量，其单位为MB，KB或％，默认为MB。使用％时说明数据文件每次增加的长度等于增加时文件现有长度的百分比，growth_increment参数的默认值为10％。用MB或KB表示时，其增加值应为64KB或其倍

数。

ON子句中的参数用于指出数据库的数据文件组，其中，filegroup_name为文件组名称。文件组中各文件的定义格式与上面介绍的数据文件的定义格式相同。

CREATE DATABASE语句中的LOG ON子句用于定义数据库日志文件。各个日志文件的定义格式与数据文件相同。当未使用LOG ON子句指定日志文件时，SQL Server将自动为数据库建立一个日志文件，文件名称由系统产生，其长度等于数据库所有数据文件长度之和的25％。

SQL Server创建一个数据库时要经过以下两个步骤：

① 使用model数据库拷贝初始化新建立的数据库，用户在model数据库中所建立的数据库对象也一并被拷贝到新建数据库中。此外，新建数据库还继承了model中的各种数据库选项设置，如果model数据库选项设置被修改，它只影响修改后所建立的数据库，已经建立的数据库的各种选项不再发生变化了；

② 用空白页面填充数据库中的自由空间。 (1)在创建数据库时，如果省略了CREATE DATABASE语句中的所有可选参数，即使用下面的语句格式，它所创建的数据库大小完全等同于model数据库： CREATE DATABASE MYDB1 GO

（2）下面例子在创建数据库MYDB2时指定文件组，其数据文件有主文件组和MYDB2_GROUP文件组组成。MYDB2数据库所包含的数据文件和日志文件，以及它们的参数如图所示： CREATE DATABASE MYDB2 ON

PRIMARY(

NAME = MYDB2_P1_dat,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_P1.mdf’, SIZE = 5,

MAXSIZE = 10, FILEGROWTH = 20% ),

(NAME = MYDB2_P2_dat,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_P2.ndf’, SIZE = 5,

MAXSIZE = 10, FILEGROWTH = 1MB ),

FILEGROUP MYDB2_GROUP(

NAME = MYDB2_S1_dat,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_S1.ndf’,

SIZE = 10, MAXSIZE = 50, FILEGROWTH = 10 ),

(NAME = MYDB2_S2_dat,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_S2.ndf’, SIZE = 20, MAXSIZE = 100, FILEGROWTH = 20 )

Go

MYDB2数据库文件

逻辑名 dat 文件名 C:\\mssql7\\data\\ MYDB2_P1.mdf 初始长度 最大长度 增 量 5MB 10MB 20％ 主文件组 MYDB2_P1_MYDB2_P2_dat c:\\mssql7\\data\\ MYDB2_P2.ndf 5MB 10MB 1MB MYDB2_GROUP文件组 MYDB2_S1_dat C:\\mssql7\\data\\ MYDB2_S1.ndf 10MB 50MB 10MB

MYDB2_S2_dat c:\\mssql7\\data\\ MYDB2_S2.ndf 20MB 100MB 20MB 日志文件 MYDB2_log c:\\mssql7\\data\\ MYDB2_log.ldf 10MB 无限制 10％ 2.使用Transact-SQL语句修改数据库 ALTER DATABASE 语句的语法格式为：

ALTER DATABASE database

{ ADD FILE [,...n] [TO FILEGROUP filegroup_name] | ADD LOG FILE [,...n] | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE

| MODIFY FILEGROUP filegroup_name filegroup_property }

::=

(NAME = logical_file_name

[, FILENAME = 'os_file_name' ] [, SIZE = size]

[, MAXSIZE = { max_size | UNLIMITED } ] [, FILEGROWTH = growth_increment] )

其中，database为待修改的数据库名称。 ADD FILE子句指出向数据库中添加数据文件，TO FILEGROUP说明新添加数据文件所属的文件组名称。

ADD LOG FILE子句指出向数据库中新添加的日志文件项。

REMOVE FILE子句指出从数据库中删除文件，在删除后，SQL Server将该文件对应的物理文件一并从操作系统中删除。

注意：① 当数据文件内容不为空时，不能将它们从指定数据库中删除；

② 当数据文件为主数据文件或数据库日志文件时，不能将它们从指定数据库中删除； ③ 当数据文件所属的文件组为默认文件组并且此数据文件是该默认文件组的唯一成员时，不能将它从指定数据库中删除。

ADD FILEGROUP子句说明向数据库中添加文件组。 REMOVE FILEGROUP子句说明从数据库中删除文件组，在删除时一并删除文件组中所有数据文件对应的操作系统文件。

注意：只有当文件组中所有的数据文件为空时才能将它们从数据库中删除。

MODIFY FILE指出修改数据文件，文件名称由中的NAME参数指定。 注意：① 每次只能对数据文件的FILENAME、SIZE、FILEGROWTH和MAXSIZE中 的一项进行修改；

② 使用ALTER DATABASE语句改变数据文件大小时，只能增加不能减少。

MODIFY FILEGROUP子句指出待修改的文件组，其中filegroup_name为文件组名称，file_property说明修改后的文件组属性，其取值包含以下几种：

? READONLY：将文件组设置为只读文件组，之后将禁止对其中的数据库对象进行修改。数

据库中的主文件组不能设置为只读文件组； 说明：如果将主文件组被设置为只读文件组，所有用户不能在该数据库中再创建任何新

的数据库对象和登录标识，或重新编译存储过程，因为这些工作都需要修改数据库中的系统表。

? READWRITE：删除文件中的只读属性，之后可对该文件组进行读、写操作；

? DEFAULT：将指定文件组设置为数据库的默认文件组，每个数据库中只能有一个默认文

件组。

（1）向MYDB2中添加一个数据文件MYDB2_P3_dat，其大小为10MB，执行后此数据文

件被添加到主文件组［PRIMARY］中： ALTER DATABASE MYDB2 ADD FILE（

NAME = MYDB2_P3_dat,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_P3.ndf’, SIZE = 10MB, MAXSIZE = 100MB, FILEGROWTH = 2MB ) GO

(2)向MYDB2中添加一个日志文件MYDB2_LOG2： ALTER DATABASE MYDB2 ADD LOG FILE

（NAME = MYDB2_LOG2,

FILENAME = ‘c:\\mssql7\\data\\MYDB2_LOG2.ldf’, SIZE = 10MB,

MAXSIZE = 50MB, FILEGROWTH = 2MB )

(3)将数据库MYDB2中的文件组MYDB2_GROUP设置为默认文件组： ALTER DATABASE MODIFY FILEGROUP MYDB2_GROUP DEFAULT 3.使用Transact-SQL语句删除数据库 DROP DATABASE语句的语法格式为： DROP DATABASE database_name [,...n]

其中，database_name为待删除的数据库名。在SQL Server中只有系统管理员和数据库所有者才有删除数据库的权限。

（1）将以上建立的数据库MYDB2删除： DROP DATABASE MYDB2

4.使用系统存储过程检索数据库的定义信息

SQL Server提供了下列系统存储过程和语句，让用户检索服务器上的数据库定义信息以及每个数据库文件空间的使用情况：

? sp_helpdb：检索服务器上的所有数据库信息及单个数据库的定义信息；对应的语

法格式为：sp_helpdb [‘name’]

其中，name参数为选项，当提供该参数时，sp_helpdb检索name参数指定的数据库定义信息，否则它检索服务器上的所有数据库信息； ? sp_spaceused：检索数据库中资料空间的使用情况以及表所占用的空间；对应的语

法格式为：sp_spaceused [‘objname’] [,’updateusage’]

其中，objname是数据库中的表名，它要求sp_spaceusage显示系统分配给该表的空间及其使用状况。不指定objname参数时，系统存储过程sp_spaceused将统计当前数据库中的资料空间信息。

updateusage参数说明是否在统计空间使用情况前执行DBCC UPDATEUSAGE语句。默认时其值为false，即不执行DBCC UPDATEUSAGE语句。将其值设置为true时，系统将对数据库执行DBCC UPDATEUSAGE语句，这样所得到的空间使用信息将更为准确，但执行该语句要占用一定的时间，尤其是当数据库较大时，其执行时间会更长；

? DBCC SQLPERF(LOGSPACE)：检索数据库中的日志空间信息。

二、数据库表存储结构

在SQL Server中，每个数据库最多可创建20亿个表，一个表允许定义1024列，每行的最大长度为8092字节（不包括文本和图像类型的长度）。当表中定义有varchar、nvarchar或varbinary类型列时，如果向表中插入的数据行超过8092字节时将导致Transact-SQL语句失败，并产生错误信息。SQL Server对每个表中行的数量没有直接限制，但它受数据库存储空间的限制。每个数据库的最大空间1048516TB，所以一个表可用的最大空间为1048516TB减去数据库类系统表和其它数据库对象所占用的空间。 SQL Server中的资料表分为永久表和临时表两种，永久表在创建后一直存储在数据库文件中，直至用户删除为止。而临时表则在用户退出或系统修复时被自动删除。临时表又分为局部临时表和全局临时表两种，局部临时表只能由创建它的用户使用，在该用户连接断开时，它被自动删除。全局临时表对系统当前的所有连接用户来说都是可用的，在使用它的最后一个会话结束时它被自动删除。在创建表时，系统根据当前表名来确定是创建临时表还是永久表，临时表的表名以#开头，除此之外为永久表。局部临时表表名开头包含一个#号，而

全局临时表的表名开头包含两个#号。 1.使用Transact-SQL语句建立资料表 CREATE TABLE 语句的语法格式为： CREATE TABLE

[

database_name.[owner]. | owner. ] table_name (

{

| column_name AS computed_column_expression | } [,...n] )

[ON {filegroup | DEFAULT} ]

[TEXTIMAGE_ON {filegroup | DEFAULT} ]

其中，table_name为新建立的表名。对于临时表，表名字符串长度不能超过116个字符，而永久表的表名字符串长度则不能超过128个字符。此外，在同一个数据库中，每个表所有者创建的表名必须保证唯一。

computed_column_expression指出计算列的定义表达式，计算列是一个虚拟列，它并不是存储在表中，而是由表中的其它非计算列（常规列）导出。计算列定义表达式可以为常规列、常量、变量、函数组成的表达式，但它不能由一个子查询构成。除了下面情况，计算列可以与常规列一样使用在SELECT列表、WHERE子句和ORDER BY子句中： ? 计算列不能用在索引的关键词列；

? 计算列不能作为PRIMARY KEY、UNIQUE、FOREIGN KEY或DEFAULT约束定义的一部分； ? 计算列不能用INSERT和UPDATE语句插入资料。

注意：① 在建表语句中，不允许对计算列设置空值属性（NULL或NOT NULL）； ② 在建表语句中，列与列的定义用逗号分隔，当将PRIMARY KEY设置在最后时，

如果最后一列是常规列，则它后面的逗号可以省略；否则如果是计算列，则此逗号不可以省略。

ON子句指出存储新建表的数据库文件组。当省略该子句或用DEFAULT关键词时，表被建立在数据库的默认文件组中。

TEXTIMAGE_ON子句说明存储新建表中的text、ntext和image列资料的数据库文件组名称。当省略TEXTIMAGE_ON子句时，text、ntext和image列资料与表存储在同一个文件组中。如果表中不包含text、ntext和image列，则可以省略TEXTIMAGE_ON子句。 ::= { column_name data_type } [ NULL | NOT NULL ]

[ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ] [ ROWGUIDCOL ]

[ ] [ ...n]

column_definition和data_type参数分别说明列名及其数据类型，其中数据类型可以为系统数据类型或用户定义数据类型。对于timestamp数据类型列，列名可以省略，此时系统用timestamp字符串作为列名。在一个表中只能有一个timestamp类型列。

NULL和NOT NULL说明列值是否允许为NULL。在SQL Server中，NULL既不是0也不是空格，它意味着用户还没有为列输入资料或是明确地插入了NULL。如果不使用NULL或NOT NULL为列设置空值属性时，列空值属性遵循以下规则：

? 对于用户定义数据类型，SQL Server使用该数据类型的空值属性设置； ? 对于系统数据类型列，当列数据类型为timestamp时，列的空值属性为NOT NULL。

对于其它数据类型列，其空值属性则由连接选项ANSI_NULL_DFLT_ON和数据库选项‘ANSI null default’决定。

说明：①通过设置连接选项SET ANSI_NULL_DFLT_ON ON|OFF对列的默认空值属性进行

切换；

②通过设置数据库选项sp_dboption ‘database’,‘ANSI null default’,‘true’

或sp_dboption ‘database’,‘ANSI null default’,‘false’ 对列的默认空值属性进行切换。

IDENTITY关键词指定该列为IDENTITY列。当用户向表中插入新的资料时，系统自动为该行的IDENTITY列赋值，并保证其值在表中的唯一性。每个表中只能有一个IDENTITY列，其列值不能由用户更新，不允许空值，也不许关联默认值或建立DEFAULT约束。IDENTITY列常与PRIMARY KEY约束一起使用，从而保证表中各行具有唯一标识。

IDENTITY列的数据类型只能为int、smallint、tinyint、numeric、decimal等数据类型。当IDENTITY列数据类型为numeric和decimal时，不允许出现小数字。对于IDENTITY列,可用seed参数和increment参数指出IDENTITY列的基值和列值增量。在建立新表时，必须同时指定IDENTITY列的基值和增量，或同时省去这两个参数。默认时，seed和increment的值均为1。

说明：① 使用关键词IDENTITYCOL可以引用表中的IDENTITY列，而不使用实际列名。

因为每个表中只有一个IDENTITY列，所以这样操作不会引起二义性；

② 通过使用SQL Server中所提供的的系统函数IDENT_SEED(‘table_name’)和

IDENT_INCR(‘table_name’)可以返回指定表中IDENTITY列的基值及其增量。

ROWGUIDCOL关键词说明该列为全局唯一标识列，每个表中只能有一个ROWGUIDCOL列，ROWGUIDCOL列的数据类型必须为uniqueidentifier。ROWGUIDCOL属性不能自动为列赋值，也不要求列值的唯一性。在INSERT语句中，可以使用NEWID函数为ROWGUIDCOL列赋值。 2.使用Transact-SQL语句修改资料表 ALTER TABLE 语句的语法格式为：

ALTER TABLE table

{ [ALTER COLUMN column_name

{ new_data_type [ (precision[, scale] ) ] [ NULL | NOT NULL ] | {ADD | DROP} ROWGUIDCOL } ] | ADD

{ [ ]

| column_name AS computed_column_expression }[,...n]

| [WITH CHECK | WITH NOCHECK] ADD { }[,...n] | DROP

{ [CONSTRAINT] constraint_name | COLUMN column }[,...n]

| {CHECK | NOCHECK} CONSTRAINT {ALL | constraint_name[,...n]} | {ENABLE | DISABLE} TRIGGER {ALL | trigger_name[,...n]} }

在建立一个表后，在使用过程中经常会发现原来创建的表可能存在结构、约束等方面的问题。在这种情况下，如果用一个新表替换原来的表，将造成表中资料的丢失。使用ALTER TABLE语句可以在保留表中原有资料的基础上修改表结构，打开、关闭或删除已有的约束，或增加新的约束。

其中，table_name参数说明所修改的表名。

WITH CHECK 和WITH NOCHECK选项说明向表中添加新的或打开表中原有的CHECK约束和FOREIGN KEY约束时，是否对表中已有资料进行约束检查。使用WITH NOCHECK选项可以禁止对表中已有数据进行约束检查，但该选项对新添加的数据无效，新插入的资料必须接受约束检查。

ALTER COLUMN子句说明修改表中column_name参数所指定列定义，它可以改变列数据类型和空值设置，添加或删除ROWGUIDCOL属性。但下列类型不能被修改：

? text、ntext、image、timestamp列； ? 计算列或用于计算的常规列； ? 复制列；

? 用于索引列，但如果这些列为varchar或varbinary数据类型，可以增加它们的列长

度；

? 用在CHECK、FOREIGN KEY、UNIQUE或PRIMARY KEY约束中的列，但如果这些列为变长

列，可以使用ALTER TABLE语句改变这些列的定义长度； ? 关联有默认值的列。

使用ALTER COLUMN子句修改列数据类型时，new_data_type参数必须符合以下条件： ? 原数据类型必须能够转换为新的数据类型； ? 修改后的数据类型不能为timestamp；

? ANSI null default选项是打开的或被修改列允许空值；

注意：如果表中的对应列原定义为NULL并列中不存在资料时，将此列更改为NOT NULL时是允许的。

? ANSI_PADDING选项是打开的；

? 对于被修改的IDENTITY列，必须具有有效的IDENTITY数据类型。

ADD子句说明向表中添加新列，新列的定义方法与CREATE TABLE语句中的相同，包括列名、数据类型、约束条件等。

注意：在ALTER TABLE语句中，对于新增加的列，必须允许空值，或关联一个默认值。 无论此列原定义是否为NULL或此列中是否存在资料。

DROP { [CONSTRAINT] constraint | COLUMN column }子句说明从表中删除指定约束或

列。一个表中的下列类型不能被删除：

? 复制列； ? 用于索引列； ? 用于CHECK、FOREIGN KEY、UNIQUE或 PRIMARY KEY约束中的列； ? 定义有默认值或关联有默认对象的列； ? 关联有规则的列。

{ CHECK | NOCHECK } CONSTRAINT { ALL | constraint }子句说明打开或关闭表中所有或constraint参数指定的FOREIGN KEY和CHECK约束。当使用NOCHECK CONSTRAINT关闭约束时，之后所插入到表中的资料不再接受该约束检查。在打开或关闭表中约束时，可以使用WITH CHECK或WITH NOCHECK子句对表中的资料进行（或不进行）检查。 3.使用Transact-SQL语句删除资料表 DROP TABLE 语句的语法格式为： DROP TABLE table_name

其中，table_name为待删除表的名称。

当删除一个表时，表之定义和表中的所有数据、以及该表的索引、许可设置、约束、触发器等均被自动删除，与该表相关联的规则和默认对象失去与它的关联关系。但是，使用DROP TABLE语句不能删除SQL Server系统表和被FOREIGN KEY约束所参照的用户表。 三、约 束

在数据库管理系统中，保证数据库中的资料完整性是非常重要的。所谓资料完整性，就是指存储在数据库中资料的一致性和正确性。在SQL Server中，可以通过各种约束和默认、规则、触发器等资料对象来保证资料的完整性。其中约束包括以下几种：

? PRIMARY KEY：主键约束； ? FOREIGN KEY：外键约束； ? UNIQUE： 唯一约束； ? CHECK： 检查约束； ? DEFAULT： 默认值约束。

在SQL Server中，根据完整性措施所作用的数据库对象和范围不同，可将它们分类为以下几种：

? 实体完整性； ? 域完整性； ? 参照完整性；

1. 实体完整性

实体完整性把表中的每行看作一个实体，它要求所有行都具有唯一标识。在SQL Server中，可以通过建立PARMARY KEY约束、UNIQUE约束，以及列的IDENTITY属性等措施来实施实体完整性。

2. 域完整性

域完整性要求表中指定列的资料具有正确的数据类型、格式和有效的资料范围。域完整性通过默认值、FOREIGN KEY、CHECK等约束，以及默认、规则等数据库对象来实现。

3. 参照完整性

参照完整性维持被参照表和参照表之间的资料一致性，它通过主键（PRIMARY KEY）约束和外键（FOREIGN KEY）约束来实现。在被参照表中，当其主键被其它表所参照时，该行不能被删除，也不允许改变。在参照表中，不允许参照不存在的主键值。

（1） DEFAULT约束

使用默认值（DEFAULT）约束后，如果资料在插入新行时没有显示为列提供资料，系统将默认值赋给该列。默认值约束所提供的默认值可以为常量、函数、系统零进函数、空值（NULL）等。

默认值约束的定义格式为：

[ CONSTRAINT constraint_name ] DEFAULT constraint_expression

其中，constraint_name参数指出所建立的默认值约束名称。constraint_expression表达式为列提供默认值。

在使用DEFAULT约束时，还应注意以下两点： ? 每列只能有一个DEFAULT约束；

? 约束表达式不能参照表中的其它列和其它表、视图或存储过程。

例如，先创建TB_constraint表，并使用默认值约束为country列设置默认值。之后，再执行ALTER TABLE语句为TB_constraint表的name列添加一个默认值约束：

CREATE TABLE TB_constraint (

name char(20) not null, country varchar(30)

CONSTRAINT DF_country DEFAULT ‘China’ ) GO

ALTER TABLE TB_countraint ADD CONSTRANT DF_name DEFAULT ‘UNKNOWN’ FOR name

（2） CHECK约束

CHECK约束限制输入到一列或多列的可能值，从而保证SQL Server数据库中资料的域完整性。在CHECK约束中可以包含搜索条件，但不能包含子查询。一个表可以定义多个CHECK约束，对于列也可以定义多个CHECK约束。

注意：① 对于ALTER TABLE语句可以为同一列添加多个CHECK约束，但对于CREATE

TABLE语句只能为每列定义一个CHECK约束；

② 如果CHECK约束被应用于一列，则它被定义为列级CHECK约束；如果CHECK

约束被应用于多列，则它被定义为表级CHECK约束。

在ALTER TABLE语句和CREATE TABLE语句中，列级CHECK约束和表级CHECK约束的

定义格式分别为：

[ CONSTRAINT constraint_name ] CHECK [ NOT FOR REPLICATION ](逻辑表达式) [ CONSTRAINT constraint_name ]

CHECK [ NOT FOR REPLICATION ](搜索条件) 其中，constraint_name选项指出所建立的CHECK约束的名称。

逻辑表达式可以是AND和OR连接的多个简单逻辑表达式而构成的复合型逻辑表达式，

搜索条件为布尔表达式。

列级CHECK约束只能参照被约束列，而表级CHECK约束则只能参照表中列，它不能参

照其它表中资料。

例如，为前面已经创建的表TB_constraint增加phone字段及其对应的CHECK约束：

ALTER TABLE TB_constraint ADD

Phone char(8) null CONSTRAINT CH_phone CHECK (phone LIKE ‘[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]’) 在ALTER TABLE语句中，使用CHECK或NOCHECK选项可以打开或关闭某个约束，并可

以使用WITH CHECK或WITH NOCHECK子句对表中的资料进行（或不进行）检查。

例如，将上例中所建立的CHECK约束关闭：

ALTER TABLE TB_constraint NOCHECK CONSTRAINT CH_phone （3） PRIMARY KEY约束

PRIMARY KEY约束通过建立唯一索引保证指定列的实体完整性，使用PRIMARY KEY约束时，列的空值属性必须定义为NOT NULL。PRIMARY KEY约束可以应用于表中一列或多列，应用于多列时，它被定义为表级PRIMARY KEY约束，否则被定义为列级PRIMARY KEY约束。

列级PRIMARY KEY约束的定义格式为： [ CONSTRAINT constraint_name ]

PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] [ WITH [ FILLFACTOR = fillfactor ] ] [ ON { filegroup | DEFAULT } ] 表级PRIMARY KEY约束的定义格式为： [ CONSTRAINT constraint_name ]

PRIMARY KEY [ CLUSTERED | NONCLUSTERED ] { （ column [,...n] ）}

[ WITH [ FILLFACTOR = fillfactor ] ] [ ON { filegroup | DEFAULT } ]

SQL Server自动为实施PRIMARY KEY约束的列建立唯一索引。如果在PRIMARY KEY约束中未指定索引类型时，默认情况下所建立的索引为簇索引（CLUSTERED）。该索引只能够通过删除PRIMARY KEY约束或其相关表的方法来删除，而不能使用DROP INDEX语句删除。无论是建立列级PRIMARY KEY约束还是表级PRIMARY KEY约束，每个表只能创建一个PRIMARY KEY约束。

在PRIMARY KEY约束定义中，WITH子句设置为PRIMARY KEY约束所建立索引的页面填充度，ON子句指出存储索引的数据库文件组名称。

例如，为表TB_constraint的name列添加PRIMARY KEY约束：

因为PRIMARY KEY约束禁止被约束列出现重复的列值，所以，在建立PRIMARY KEY约束前应删除前面为name列所建立的DEFAULT约束。

ALTER TABLE TB_constraint DROP CONSTRAINT DF_name GO

ALTER TABLE TB_constraint ADD CONSTRAINT PK_name PRIMARY KEY (name)

（4） UNIQUE约束

UNIQUE约束也能保证一列或多列的实体完整性，每个UNIQUE约束要建立一个唯一索引。对于实施UNIQUE约束的列，不允许有任意两行具有相同的索引值。与PRIMARY KEY约束不同的是，SQL Server允许为一个表建立多个UNIQUE约束。

列级UNIQUE约束的定义格式为： [ CONSTRAINT constraint_name ]

UNIQUE [ CLUSTERED | NONCLUSTERED ] [ WITH [ FILLFACTOR = fillfactor ] ] [ ON { filegroup | DEFAULT } ]

UNIQUE约束应用于多列时称作表级UNIQUE约束，其定义格式为： [ CONSTRAINT constraint_name ] UNIQUE [ CLUSTERED | NONCLUSTERED ] { （ column [,...n] ）} [ WITH [ FILLFACTOR = fillfactor ] ] [ ON { filegroup | DEFAULT } ]

CLUSTERED和NONCLUSTERED关键词分别要求SQL Server对UNIQUE约束自动创建唯一簇索引和非簇索引。为UNIQUE约束所建立的索引也只能通过删除UNIQUE约束或删除表的方法来删除，而不能够使用DROP INDEX语句删除。

注意：与PRIMARY KEY约束不同的是：UNIQUE约束允许被约束列的空值属性设置为NULL，

但UNIQUE约束不允许表中受约束列有一行以上同时为NULL。

例如，为表TB_constraint添加UNIQUE约束：

ALTER TABLE TB_constraint ADD CONSTRAINT UN_phone UNIQUE (name, phone) FOREIGN KEY约束

FOREIGN KEY约束为表中一列或多列资料提供参照完整性。实施FOREIGN KEY约束时，要求在被参照表中定义了PRIMARY KEY约束或UNIQUE约束。FOREIGN KEY约束限制插入到表中被约束列的值必须在被参照表中已经存在。 表级FOREIGN KEY约束的定义格式为： [ CONSTRAINT constraint_name ]

FOREIGN KEY [ ( column [,...n] ) ]

REFERENCES ref_table [ ( ref_column [,...n] ) ] [ NOT FOR REPLICATION ]

列级FOREIGN KEY约束的定义格式为： [ CONSTRAINT constraint_name ] [ FOREIGN KEY ]

REFERENCES ref_table [ ( ref_column ) ] [ NOT FOR REPLICATION ]

其中，ref_table为被参照表，ref_column指出被参照表中的被参照列。被参照表中必须具有 PRIMARY KEY约束和UNIQUE约束。

在FOREIGN KEY约束中，FOREIGN KEY子句中指定的列数和每列的数据类型必须与REFERENCES子句中的相同。对于列级FOREIGN KEY约束，由于参照列唯一确定，所以可以省略，而只需指出被参照列即可。

使用FOREIGN KEY约束时，必须注意以下几点： ? 一个表最多只能参照253个不同的资料表，每个表也最多只能有253个FOREIGN KEY

约束；

? FOREIGN KEY约束不能应用于临时表；

? 在实施FOREIGN KEY约束时，用户必须至少拥有被参照中参照列的SELECT或

REFERENCES权限；

? FOREIGN KEY约束可以参照自身表中的其它列，这种参照称为自参照； ? FOREIGN KEY约束中，只能参照同一个数据库中的某个表，它不能参照其它数据库

中的表。跨数据库参照约束只能通过触发器来实现。 列如，下面是表titleauthor和表titles的建表语句，为author_id列增加FOREIGN KEY约束：

CREATE TABLE titleauthor (

author_id numeric(8) not null, author_name varchar(12) not null, author_phone varchar(12) null PRIMARY KEY(author_id) )

CREATE TABLE titles (

title_id numeric(8) not null, author_id numeric(8) not null, title varchar(12) null PRIMARY KEY(title_id) )

ALTER TABLE titles

ADD CONSTRAINT FK_author_id FOREIGN KEY (author_id) REFERENCES titleauthor (author_id) 对于一个表，可以使用通过系统存储过程sp_help检索其定义信息，它所返回的内容包括表的结构定义、所有者、创建时间、各种属性、约束和索引等信息。 sp_help的语法格式为：

sp_help [ @objname = ] name

其中，name参数说明所检索表的名称。 四、规 则

1、 功能：规则的的作用与CHECK约束相同，它检查用户为其所关联的列或所关联的用

户的数据类型列所输入数据的有效性。每列或每个用户定义数据类型只能同时关联一个规则，除此之外，每列还可以具有多个CHECK约束。 2、 创建格式：

CREATE RULE rule_name

AS condition_expression

3、 说明：rule_name:所创建的规则名称。

Condition_expression指出规则所定义的条件，它用一个局部变量代表

INSERT语句或UPDATE语句所输入的数值。Condition_expression表达式可以与有效的WHERE子句所指的条件相同，其中可以包含算术运算符、关系运算符和IN、LIKE、BETWEEN等谓词，也可以包含不参照数据库对象的内置函数。但是，在规则定义中，不能参任何列或其它数据对象。

例1、 创建一个规则，限制所输入的数据范围为1到999之间的实数

CREATE RULE money_range

AS @range>1 and @range<999

例2、 创建一个规则，限制用户输入的字符串只能在指定的列表条目中：

CREATE RULE DB_name

AS @db_name IN (‘SQL SERVER’,’ORACLE’,’INFORMIX’)

4、 规则的应用

在建立规则后，应将它们关联到列或用户定义数据类型才能使它们发挥作用，执行系统存储过程sp_bindrule建立规则关联。在用输入或修改数据时激活规则，系统将自动检查列值是否在规则指定的范围内、或是否与规则指定的数据格式相匹配。 系统存储过程sp_bindrule语法格式为：

sp_bindrule [@rulename] ‘rule_name’,[@objname=] ‘object_name’ 说明:rule_name为规则名称

object_name为“表名.列名”格式时，说明它为一列名，否则，sp_bindrule

将 object_name参数视作用户定义数据类型。

例如：在SHIKONG数据库中建立一个shl_rule规则，然后关联到SPKFK表中的shl

列

CREATE RULE shl_rule AS @shl>0

Sp_bindrule shl_rule,’spkfk.shl’ 5、 解除规则

在删除规则前必须执行系统存储过程sp_unbindrule解除规则与列或用户定义数据库类型之间的关联.sp_unbindrule语句的格式为：

sp_unbindrule [@rulename] ‘rule_name’,[@objname=] ‘object_name’

解除关联后，规则仍存储在当前数据库中，这时可执行DROP RULE语句将它删除。在DROP RULE语句的语法格式为： 语法：DROP RULE {rule_name}[,?n]

在一个DROP RULE语句中，可以一次同时删除多个规则，但必须保证这些规则没有相关联的列或用户定义数据类型，否则，系统将取消DROP RULE语句的执行，并返回一条错误信息。

五、默认

默认对象所执行的功能与默认值约束完全一样。但默认值约束是在CREATE TABLE

或ALTER TABLE时定义，它与表定义存储在一起，所以，删除表时，默认值约束被自动删除。而默认对象则需要使用CREATE DEFAULT语句定义，它作为一种数据对象单独存储，所以它可以被多次应用于不同列。在删除表时不能删除默认对象，而需要使用DROP DEFAULT语句删除。 1、 创建默认对象

CREATE DEFAULT [owner.]default_name AS constant_expression

2、 说明：default_name为所建立的默认对象名称。

Constant_expression参数为默认对象所提供数据。它可以是由常量、SQL Server

内置函数、算术表达式和全局变量等所组成的常量表达式，但是不能包含任何列名或其它数据库对象。在Constant_expression表达式中，对于字符和日期类型数据应引在单引号内，二进制数据必须以0X作前导符，货币类型以美元符号$开头，对于其它数据类型（如整数和浮点数等）则不需要定界符。

CREATE DEFAULT语句只能在当前数据库中创建默认对象。对每个用户来说，他在

同一个数据库中所创建的默认对象名称必须保持唯一。

例如：下面语句分别建立一个字符、日期、二进制、货币和整数等数据类型的默认

对象

CREATE DEFAULT DF_char AS ‘UNKNOWN’

GO

CREATE DEFAULT DF_date AS ‘Dec 12 2002 6:00 AM’ GO

CREATE DEFAULT DF_binary AS 0Xffffffffff GO

CREATE DEFAULT DF_money AS $1000 GO

CREATE DEFAULT DF_int AS 0 GO

3、默认的应用

在创建默认后，必须将它与列或用户定义数据类型关联起来才能使之发挥作用。

执行系统存储过程sp_bindefault在默认对象和列或默认对象和用户定义数据类型间建立关联。Sp_bindefault 的语法格式：

sp_bindefault [@defname] ‘default_name’ [@objname=] ‘object_name’

[,[@futureonly=] ‘futureonly_flag’] 说明:default_name为所关联的默认对象名称

object_name为默认对象所关联到的列名或用户定义数据类型名称。

Sp_bindefault只能将默认与当前数据库表中的列或用户定义的数据类型相关

联，在关联时，默认对象的数据类型必须与其所关联的列的数据类型相同或兼容。在未解除已有默认关联之前，可以将一个新的默认对象关联到该列，这时，旧的关联将自动解除，只有最近一次关联的默认对象起作用。

4、删除默认对象

在删除默认对象时，首先要执行系统存储过程sp_unbindefault解除默认对象与列和用户定义数据类型之间的关联，然后才能执行DROP DEFAULT语句删除默认对象，sp_unbindefault的语法格式：

sp_unbindefault [@objname=] ‘object_name’ [,[@futureonly]=] ‘futureonly_flag’

解除默认对象的关联后，该对象仍存在于当前数据库中，这时可执行DROP DEFAULT语句将其删除。DROP DEFAULT语句的语法格式为： DROP DEFAULT ｛default_name｝ [,?n] 六、索 引

如果对一个未建立索引的表执行查询操作，SQL Server将逐行扫描表数据页面中的资料行，并从中挑出符合条件的资料行。当一个表中有很多行时，执行一次查询将耗费大量的时间。然而，在建立索引后，SQL Server则根据索引指示，直接定位到所要查找的资料行，从而加快SQL Server的资料检索操作。此外，索引还能够加快ORDER BY和GROUP BY子句的执行、强制实施行的唯一性。

SQL Server中的索引类型包括以下几种： ? 唯一索引； ? 簇索引； ? 非簇索引。 1. 唯一索引

唯一索引要求所有资料行中的任意两行中的被索引列不能存在重复值（包括空值

NULL）。有以下两种方法建立唯一索引：

? 在CREATE TABLE或ALTER TABLE语句中设置列级或表级PRIMARY KEY约束或UNIQUE

约束时，SQL Server自动为这些约束建立唯一索引； ? 在CREATE INDEX语句中使用UNIQUE选项创建唯一索引。

使用CREATE INDEX语句对一个已存在的表创建唯一索引时，系统首先检查表中已有数据，如果被索引列存在重复键值，系统将停止建立索引。在这种情况下，只有删除已存在的重复行后，才能对这些列建立唯一索引。

表创建唯一索引后，SQL Server将禁止INSERT语句或UPDATE语句向表中添加重复的键值行。

2. 簇索引

在簇索引中，行的物理存储顺序与索引顺序完全相同，每个表只允许建立一个簇索引。由于在建立簇索引时要改变表中资料行的物理顺序，所以应在其它非簇索引建立之前建立簇索引，以免引起SQL Server重新构造非簇索引。

默认情况下，SQL Server为PRIMARY KEY约束所建立的索引为簇索引，但这一默认设置可以使用NONCLUSTERED关键词改变。在CREATE INDEX语句中，使用CLUSTERED选项建立簇索引。

3. 非簇索引

非簇索引不改变行的物理存储顺序。在非簇索引的叶级页面包含一个索引值和一个指针，指针指向资料页中的资料行，该行具有与索引键值相同的列值。在CREATE INDEX语句中，使用NONCLUSTERED选项建立非簇索引。

注意：一个表最多可以建立249个非簇索引，其中包括使用CREATE INDEX语句显式建

立的非簇索引，以及各种约束所建立的非簇索引。 4. 复合索引

复合索引是对一个表中的两列或多列的组合进行索引，复合索引的最大列数为16，且这些列必须位于同一个表中。复合索引值的最大长度为900字节，即复合索引列的定义长度之和不能超过900字节。在使用复合索引时，把被索引列（两列或多列）作为一个单位。

注意：复合索引中的列顺序可以与表中的列顺序不同，在复合索引中应首先定义最可能

具有唯一性的列。 在SQL Server中，有两种方法建立索引：第一，调用CREATE TABLE语句创建表或执行ALTER TABLE语句修改表时，建立PRIMARY KEY约束和UNIQUE约束，使SQL Server自动为这些约束建立索引；第二，使用CREATE INDEX语句对一个已存在的表建立索引。

CREATE INDEX语句的语法格式为：

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON table ( column [,...n] ) [ WITH

[ PAD_INDEX ]

[ [,] FILLFACTOR = fillfactor ] [ [,] IGNORE_DUP_KEY ] [ [,] DROP_EXISTING ]

[ [,] STATISTICS_NORECOMPUTE ] ]

[ ON filegroup ]

其中，UNIQUE、CLUSTERED和NONCLUSTERED指出所建立的索引类型，它们分别为唯一

索引，簇索引和非簇索引。省略CLUSTERED和NONCLUSTERED选项时，SQL Server所建立的为非簇索引。

index_name参数所建立的索引名称。在同一表中要保证索引名称的唯一性。

table和column说明被索引表及其列名，一个索引可以包含一列或多列（构成符合索引）。在建立索引时，不能对bit、text、ntext、image数据类型列和计算列建立索引。

FILLFACTOR 参数指出在创建索引时，每个索引页面的叶级填充度，它说明每次叶级索引页面填充多少时开始分页，从而在索引页面中保留一定的空间。索引页面中保留一定的空间是非常有用的，它可以存储以后所插入新行的索引值，从而避免在每次插入新行时使系统都必须将原索引页面重新分页，从而提高系统的运行效率。

用户定义的FILLFACTOR参数值为１到100（％），默认值为0。只有在不需要插入或修改资料的情况下，才将FILLFACTOR设为100。将FILLFACTOR设为100时，在插入和修改操作时都回导致索引页的分页，从而大量占用系统时间。所以对于非只读表和数据库，应根据索引建立后需要插入的资料量来估算FILLFACTOR 参数。

PAD_INDEX指出SQL Server在创建索引时，其内部节点页面是否也遵守FILLFACTOR 参数指定的填充度进行填充。所以，在使用PAD_INDEX选项时，必须同时设置FILLFACTOR 参数。默认时，SQL Server在索引节点上至少保留能够存储两个索引项的空间。

例如，对表spkfk建立索引，指定该索引的内部节点页面和叶级页面的填充度均为20%：

CREATE INDEX spkfk_index ON spkfk(spid) WITH PAD_INDEX, FILLFACTOR = 20 建立唯一索引后，每次使用INSERT语句或UPDATE语句向表中添加或修改资料时，系统将自动检查这些资料在索引列中是否存在重复键值。如果没有重复键值，资料将被成功地添加或修改；否则，系统将根据是否设置了IGNORE_DUP_KEY索引选项，对所执行的语句做以下两种不同的处理：

(1) 如果未设置IGNORE_DUP_KEY选项，系统将取消INSERT语句或UPDATE语句的执行。

对于影响多行的INSERT语句或UPDATE语句，在遇到重复的索引值时，所有已修改的行将被回滚，数据库中的其它修改（如索引页的改变等）也将被撤消，表中资料被恢复到语句执行前的状态。

(2) 如果设置了IGNORE_DUP_KEY选项，当INSERT语句或UPDATE语句影响多行时，所

插入的包含重复键值的资料行将被忽略，而修改时，所修改的行将被删除，而其它行则被正常插入或修改。

UPDATE语句的修改操作实际是先删除旧行，然后再插入新行。所以，如果所插入的行存在重复的键值，并且设置了IGNORE_DUP_KEY选项，那么SQL Server将取消UPDATE语句对该行的插入操作，并导致被修改行的删除。

注意：只有当INSERT语句和UPDATE语句一次操作多行时，IGNORE_DUP_KEY选项才有意义。

例如，对表spkfjc中的spid和spbh列建立复合式非簇索引index_spid，并使用PAD_INDEX和FILLFACTOR参数要求索引文件的节点页面和叶级页面的填充度均为30%：

CREATE NONCLUSTERED INDEX index_spid ON spkfjc(name, phone)

WITH PAD_INDEX, FILLFACTOR = 30

CREATE INDEX语句所创建的索引可以调用DROP INDEX语句删除，但是DROP INDEX语句不能删除SQL Server为PRIMARY KEY约束和UNIQUE约束所建立的索引，这些索引只能通过删除约束或删除表的方法删除。

DROP INDEX语句的语法格式为： DROP INDEX ‘table.index’ [,...n]

其中，INDEX为待删除的索引名称，table为索引所属表名。

在同一个数据库中可能存在多个同名约束，但是同一个表中的索引是唯一的，所以，在DROP INDEX语句中需要使用table参数限制索引所属表名。

例如，删除前面所建立的index_spid索引： DROP INDEX spkfjc.index_spid

通过执行系统存储过程sp_helpindex能够检索资料表目前所建立的索引（或约束）的索引类型、存储位置和被索引列。sp_helpindex的语法格式为：

sp_helpindex [ @objname = ] ‘name 其中，name为当前数据库中的表名称’。 七、视图:

定义：视图是用户查看数据库表中资料的一种方式，它相当于一个虚拟表，用户通过

它来浏览表中部分或全部资料。而数据的物理存放位置仍然在表中。视图可以基于一个或多个表。 优点：

1、 用户注意力聚焦在特定的资料上，并达到资料安全的目。因为表中通常存放着

整个对象的全部资料，而不同的用户只需要他们应该得到的资料。检索表时，用户则可能看到表中所有资料。而使用视图则能够限制用户从表中所检索的内容。

2、 简化资料查询和处理操作。能够使用户在处理资料时如同处理单表一样。 3、 有利于资料交换操作。在实际工作中，需要与其它数据库或电子表格软件之间

交换资料。如果资料存在于多个表中，使用视图简化资料交换操作。

语法：create view view_name [(column[,…n])] [WITH ENCRYPTION] as

select_statement [WITH CHECK OPTION] 说明：view_name:创建的视图名称

[WITH ENCRYPTION]：要求在存储CREATE VIEW文本时加密，这使任何人无法检

索视图的定义文本

select_statement:视图定义语句 限制条件：（1）用户必须具有查询语句所参照对象的SELECT权限。

（2）语句中不能含有ORDER BY、COMPUTE或COMPUTE BY关键词。 （3）不能包含INTO关键词。 （4）不允许参照到一个临表

（5）不能建立规则、默认和触发器，或构造索引。

一个视图最多只能参照1024列。

不能创建临时视图，也不能以临时表作基表建立视图

[WITH CHECK OPTION]：视图所执行的所有的资料修改操作必须遵守视图定义中 所设置的条件。从而保证修改后的资料通过视图仍能看到。 存储：视图名称存储在SYSOBJECTS系统表，列定义信息存储在SYSCOLUMNS系统表中， 视图的基表信息存储在SYSDEPEND表中，语句文本存储在SYSCOMENTS中。 例如：检索从供货商进货情况 create view count_je

as

select b.danwbh,b.dwmch,sum(a.hsje) as hsje from cwk a join mchk b on a.dwbh=b.dwbh where a.djbh like 'jha%'

group by a.dwbh,b.danwbh,b.dwmch 1、 通过视图修改资料：

（1） 在一个语句中，一次不能修改一个以上的视图基表

（2） 修改操作必须遵守视图基表中所定义的的各种资料完整性约束条件。 （3） 不允许对视图中的计算列（通过算术运算或内置函数生的列）进行修改，

也不允许对视图定义中包含有统计函数或GROUP BY子句的视图进行修改或插入操作。

2、 修改和删除视图：

（1） 修改视图:ALTER VIEW view_name其结构与CREATE VIEW语句完全相同。 （2） 删除视图：DROP VIEW view_name 八、触发器

定义：触发器是一种特殊的存储过程，它不允许带参数，也不能被直接调用，只能由系

统自动激活。

语法：

CREATE TRIGGER trigger_name On table_name [WITH ENCRYPTION] {

{FOR{[INSERT][[,]DELETE][[,]UPDATE]}} [NOT FOR REPLICATION] AS

Sql_statement[…n]

}| {

{FOR{[INSERT][[,]DELETE]}} [NOT FOR REPLICATION] AS {

IF UPDATE (column)

[{AND|OR} UPDATE (column)][...n]

}

sql_statement[]

}

说明：trigger_name为所建立的触发器的名称。

WITH ENCRYPTION：触发器定义文本加密后存储。

{FOR{[INSERT][[,]DELETE][[,]UPDATE]}}：定义触发器事件，一个触发器由表

中多个事件触发时，使用INSERT、DELETE、UPDATE的组合表示，它们之间用逗号分隔。

Sql_statements参数为单个Transact_SQL语句或语句块，它定义触发事件发生

时，触发器所执行的动作。

from mchk a cross join spkfk b c.自联结。 自联结：不是一种特殊的联结，它实际上是在同一表中进行的内联结和外联结。 例如：选择具有相同含税售价列表。 select distinct a.hshsj,a.spmch from spkfk a

join spkfk b on a.hshsj=b.hshsj and a.spid<>b.spid

(1)spid不等表示记录自巳不与自己联结。 (2)distinct存在重复的资料。 (7)合并:

UNION是将两个或多个查询合并到一个结果集中的方法，合并后的结果包含合 并组中的的所有查询。

例如：在SPKFK中选择针剂和片剂。

select * from spkfk where spbh like '[z]%' union

select * from spkfk where spbh like '[z]%'

注意事项：

1、在所有查询中列的数目必须相同，如果不相同。

2、所有的数据类型必须兼容。是指经过隐含的转换能够兼容。 2、delete 语句:

例1、从ysmxk表中删除行。 delete ywmxk

where djbh='xsa00000001'

根据值为' xsa00000001'的djbh将相应资料行删除。 例2、通过ID删除某个出版商的sales 利用联结查找到符合条件记录

begin tran

delete cwk from cwk c

join mchk m on c.dwbh=m.dwbh where m.danwbh like '1389' rollback tran

利用子查询查找到符合条件记录 begin tran delete cwk from cwk where dwbh in

(select dwbh from mchk where danwbh like '1389' ) rollback tran 优点：可读性好。

缺点：效率较低。

在何处必须使用子查询进行删除。 例如：

delete ywmxk

where shl<(select avg(shl) from ywmxk) and djbh like ‘xsa%’

因为在WHERE子句中有聚合函数，无法使用联结进行处理。 特例：truncate table 语句 truncate table sales

区别：delete语句删除记录时对一行修改都记录日志，

truncate table不记录日志，只记录整个资料面的释放操作。 3、update 子句

update语句可以像select和delete语句一样，可以使用联结和子查询对需要 更新的资料加以限制。并且也能使用子查询作为更新值的表达式。 基本格式：

update tablename

set fieldname=fieldvalue

例如：把总销量大于300的商品的含税售价加10%。 begin tran update spkfk

set hshsj=hshsj*1.1 where spid in

(select spid from ywmxk group by spid

having sum(shl)>=300 )

rollbace tran 4、insert语句： 基本语法：

insert into

table_or_view [(colun_list)] {data_values|select_statement} 例1：添加资料到一行中的所有列 insert into spkfk

values('sph00000001',’ztj001’,…,…)

不用给出列名，但在values中赋值顺序要与表中列的顺序相同，数据类型匹 配，不能对identity列赋值.不能违反完整性约束。 例2：添加资料以一行中的部分列 insert into

spkfk(spid,spbh,spmch)

values(' sph00000002',’ztj001’,’青霉素’)

要确认没有提供资料列应为identity,允许为null或具有默认值 例3：使用insert...select 语句揷入多行

insert into destdb..jxdjhz

select * from sourdb..jxdjhz b where b.rq=’2002-05-22’ 5：扩展：

CASE表达式在select和update中的应用。 例1、select d.spbh,

case when a.djbh like 'jha%' then sum(a.shl) else 0 end as jhshl, case when a.djbh like 'jha%' then sum(a.hsje) else 0 end as jhje, case when a.djbh like 'jhc%' then sum(a.shl) else 0 end as tcshl, case when a.djbh like 'jhc%' then sum(a.hsje) else 0 end as tcje, case when a.djbh like 'jhb%' then sum(a.hsje) else 0 end as tbje from ywmxk a join cwk b on a.djbh=b.djbh join mchk c on c.dwbh=b.dwbh

join spkfk d on a.spid=d.spid

where b.rq between @startrq and @endrq and c.danwbh= @danwbh group by d.spbh,a.djbh 十、函数数据整理

ABS：ABS函数返回数值表达的绝对值

语法：ABS（numeric_expression）可以是整型、短整型、微短整型、小数、浮点数、货

币、实型或小货币型数。

ACOS：ACOS函数是一个数学函数，它返回以弧度表示的角度，即：ARCCOS。

语法：ACOS（float_expression）变量是浮点数数据类型，表示角度的余弦。 ASIN：反正弦函数。

SIN：它返回表达式中的正弦值。

语法：SIN（ float_expression）float_expression是浮点型的资料 COS：它返回表达式中的余弦值。

语法：COS（ float_expression）float_expression是浮点型的资料。 TAN：它返加表达式的正切值。 COT：它返回表达式中的余切值。 ATAN：反正切函数。

SIGN：如果表达式为正，SIGN函数返回1，如果表达为负，SIGN返回-1，如果表达式为0，

则函数返回0。

CEILING：返回不小于给定的数值表达式的最小整数。

语法：CEILING（numeric_expression）小数、浮点、整数、实数、货币等类型。 例如：SELECT CEILING（1.45）返回值为2 SELECT CEILING （-1.45）返回值为-1 FLOOR：返回不大于给定的数值表达式的最大整数。

语法：FLOOR（numeric_expression）小数、浮点、整数、实数、货币等类型。 例如：SELECT FLOOR（1.45）返回值为1 SELECT FLOOR （-1.45）返回值为-2

ASCII：ASCII函数返回整数，表示字符表达式最左边的字符的ASCII代码值。

语法：ASCII（character_expression） 例：ASCII（‘AB’）返回65。

CHAR：字符串函数，用于将ASCII码整数转换为字符。

语法：CHAR（integer_expression）变量是0~255间的正整数。 例如：CHAR（65）返回‘A’

CHARINDEX：字符串函数，返回想要的查找字符串，在目标字符中的起始位置。

语法：CHARINDEX（‘PATTERN’，EXPRESSION[，START_LOCATION]）； 例如：CHARINDE（‘A’，‘BCDEA’，2）返回值为5，其2为从第几个字符进行搜索

不写默认为从第一个位置。

AVG：AVG是个集合函数，它计算数值列的平均值，忽略空值，如果使用了DISTINCT则

对重复的值只取一次进行平均。

语法；AVG（[ALL|DISTINCT] expression） ALL是缺省值。

例如：SELECT DISTINCT（AGE）FROM TABLE1 如果任意行有NULL值，则此行被

忽略。

COUNT：是一个集合函数，它返回的整数代表表达式中值的个数通为一个列，DISTINCT

和COUNT一起使用时，返回单一值的个数。

语法：COUNT（{[ALL|DISTINCT] EXPRESSION |*}） 例如：SELECT COUNT（COLUMN1）FROM TABLE1，

返回COLUMN1中没有空值的行数，如果是使用*将返回表中所有行的数目。 使用DISTINCT则返回唯一值的个数。

SUM：SUM是一个集合函数，返回表格中选定的数值型列中的值的总和。 语法：SUM（[ALL|DISTINCT]expression） MAX：MAX返回表达式中的最大值， 语法：MAX（expression）

MIN：MIN返回表达式中的最小值， 语法：MIN（expression）

GETDATE：GETDATE是一个函数，它返回系统日期和时间。 语法：GETDATE（）

DATEADD：DATEADD返回根据日期时间添加的日期， 语法：DATEADD（datepart,number,date）

变量：DATEPART是用于计算的日期单位。有一定的格式。 NUMBER是指添加至日期变量中的日期单位数。

DATE：格式化为日期的字符串，或日期时间型的列名、变量或参数。 例如：SELECT DATEADD（DAY，7，GETDATE（）） DATEDIFF：返回两个指定日期的日期单位差别。 语法：DATEDIFF（datepart,date,date）

例如：DATEDIFF（day,datevar,getdate()）

返回值为DATEVAR和当前日期相差的天数。 DATEPART：返回的整数表示日期中的日期单位。 语法：DATEPART（datepart,date）

例如：DATEPART（month,getdate()） 返回值为当前日期的月份数。 YEAR：显示所提供日期的年， 语法：YEAR（DATE） MONTH：显示所提供日期的月， 语法：MONTH（DATE）

DAY：显示所提供日期的天， 语法：DAY（DATE）

CAST：CAST语句用来进行数据类型的转换。

语法：CAST（expression AS datatype） 例如：CAST（MYDATE AS VARCHAR） 把MYDATE 转换成可变字符类型。 CONVERT：用来进行资料类的转换。

语法：CONVERT（data_type,expression）

例如：SELECT ‘my birthday is today’+convert(varchar(12),getdate( )) 把日期转换成字符串类型，用加号与前边相连。 EXP：返回以E为底的N次幂。

语法：EXP（float_expression） 例如：EXP（0）返回值为1。

LOG：LOG函数返回浮点表达式的自然对数。 语法：LOG（float_expression）

LOG10：LOG函数返回浮点表达式的以10为底的对数值。 语法：LOG10（float_expression）

ROUND：ROUND返回给出的数学表达式的，并四舍五入至指定的精度。 语法：ROUND（numeric_expression,length[,funcion]） 变量：numeric_expression为数值型表达式

length是四舍五入的精度，小数点后边的数，当为负值时，数字表达式在小数点左边进行四舍五入。

Function值为0时表达式四舍五入，如果不为0，将数字表达式舍位。

SQUARE：求平方

语法：SQUARE（float_expression） SQRT：求平方根

语法：SQRT（float_expression）

STR：SQRT可以将数值型转换为字符型资料，允许控制小数的格式。 语法：STR（float_expression[,length[,decimal]]） 变量：length：想要的字符串的总长度。缺省为10。

decimal：是小数点右边的位数。四舍五入。如果不写长度则为取整，小数后四 舍五入。

ISNULL：ISNULL可有非空资料代替NULL。

语法：ISNULL（expression,value） 例如：SELECT ISNULL(NULL,'ABC')

LEFT：LEFT函数返回字符表达式从左边开始的给定的字节数。如果该整数是一个负数，

就返回NULL。

语法：LEFT（character_expression,integer_expression）

变量：character_expression可以是变量、常量、或表中的列。 Integer_expression为要返回的字符数。

RIGHT：RIGHT函数返回字符表达式从右边开始的给定的字节数。如果该整数是一个负 数，就返回NULL。

语法：LEFT（character_expression,integer_expression）

变量：character_expression可以是变量、常量、或表中的列。

Integer_expression为要返回的字符数。

LEN：LEN函数提供了字符串表达式中的字符的长度。 语法：LEN（string-expression） 例如：LEN（‘ABCE’）值为4

LOWER：LOWER函数将大写字符转换为小写字符。 语法：LOWER（character_expression） 变量是字符表达式。

UPPER：UPPER函数将小写字符转换为大写字符。 语法：UPPER（character_expression） 变量是字符表达式。

REVERSE:REVERSE返回字符串表达式的倒序。 语法：REVERSE（character_expression） 例如：REVERSE（‘PAM’）结果为‘MAP’ LTRIM：LTRIM删除字符表的前导空格。 语法：LTRIM（character_expression） 变量是字符表达式。

RTRIM：RTRIM删除字符表的后继空格。 语法：RTRIM（character_expression） 变量是字符表达式。

NULLIF：如果一对表达式相等，NULLIF函数就返回NULL，如果它们不相等，NULLIF

函数就返回第一个表达式的值。

语法：NULLIF（expression1,expression2）

变量：expression可以数值型，也可以是字符串。 例如：SELECT NULLIF（134，135）返回值为134。 PI：PI函数返回pi的值3。1415926

REPLACE：REPLACE用串表达式3取代串表达式1中的表达式2。 语法：REPLACE（‘string1’，‘string2’，‘string3’） 例如：SELECT REPLACE（‘mydog’，‘dog’，‘car’）

本例中，用CAR取代了MYDOG中的DOG，结果为MYCAT。

STUFF：函数可以将表达式的一部分用所提供的另一个字符串替代，替代部分用起始位置

和长度来定义。

语法：STUFF（character_expression,start,length,character_expression）

LENGTH：是决定要替换的字符数。

例如：STUFF（‘MY DOG FIDO’，8，4，‘SALLY’） SUBSTRING：返回字符型的一部分。

语法：SUBSTRING（expression,start,length） 例如：SUBSTRING（‘MICROSOFT’，6，4） 返回SOFT。

REPLICATE：REPLICATE将字符串表达式复制用户定义的次数。 语法：REPLICATE（character_expression,integer-expression） 例如：REPLCATE（‘2’，5） 十一、游标

1、应用程序对游标操作过程

A：用DECLARE语句声明光标，并定义光标类型和属性。

B：调用OPEN语句打开和填充光标。

C：执行FETCH语句读取光标中的单行数据，Transact-SQL光标不支持多行读取

操作。

D：如果需要，使用帝UPDATE…WHERE CURRENT OF…或

UPDATE…WHERE CURRENT OF…语句修改光标表中的当前行数据。

E：完成光标操作之后，执行CLOSE语句关闭光标，如果需要还可以OPEN语句

打开光标。

F：最后执行DEALLOCATED语句删除光标，并释放它所占用的所有资源。

2、 游标定义语法：

A：SQL－92游标定义语法

语法：DECLARE cursor_name [INSENSITIVE] [SCROLL] CURSOR FOR select_statement

[FOR {READ ONLY | UPDATE [OF column_list]}] 说明： 1）cursor_name为所定义的光标名称。

2）insensitive说明定义的光标使用SELECT语句结果集合的临时拷贝，禁止应用程序通过光标对其基表进行修改。

3)SCROLL选项指出所定义光标的数据操作可以使用以下所有选项： ·FIRST：读取光标中的第一行数据；

·NEXT: 读取光标当前位置中的下一行数据； ·PRIOR: 读取光标当前位置中的上一行数据； ·LAST: 读取光标中的最后一条数据；

·RELATIVE n ：读取光标当前位置之前（n为负数）或之后（n为正数）的第n行资料； ·ABSOLUTE n: 读取光标中的第n行数据。

没有说明SCROLL选项时，DECLARE所定义的光标只能使用NEXT选项，即每次只能读取

下一行资料。

4）select_statement为SELECT查询语句，它定义光标的结果集合，但其中不能使用

COUMPUTE、COMPUTE BY、FOR BORWSE和INTO等关键词。

5）READ ONLY选项说明所定义的光标为只读光标，它禁止UPDATE语句和DELETE语句通过

光标修改基表中的数据。

6）UPDATE [OF column_list]:可以通过光标修改其基表数据，其中可修改的列用column_list参数列出。如果只指定UPDATE关键词，而省略OF column_list参数时，说明非只读光标中的所有列均可以修改。

注意：光标声明语句中，如果有下列条件之一时，无论是否指定INSENSITIVE选项，

系统将自动把所建立的光标定义为INSENSITIVE光标：

1） SELECT语句中使用了DISTINCT、UNION、GROUP BY或HAVING等关 键词。 2） SELECT语句的选择列表中包含有集合表达式。

3） 所有光标基表均没有建立唯一索引，并且光标具有SCROLL属性时。 4） SELECT语句中包含有ORDER BY子句，而ORDER BY子句指定的列又不具备和唯一标识功

能。

B：Transact-SQL游标定义语法 语法：DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL]

[STATIC | KEYSET | DYNAMIC]

[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] FOR select_statement

[FOR UPDATE [OF column_list]]

说明：1）其中，cursor_name、SCROLL、UPDATE [OF column_list]参数与SQL-92语法格式定义中同名参数的作用相同。READ＿ONLY与SQL-92定义中的READ＿ONLY选项的作用相同。

2）LOCAL和GLOBAL选项分别说明所定义的游标为局部游标或全局游标。 局部游标的作用域为定义游标的批、存储过程或触发器

全局游标的作用域为当前连接，在作用域外游标是不可见的。

3）FORWARD_ONLY选项指所定义的游标的数据提取操作只能前滚，即FETCH语句只能使用NEXT选项。

4）STATIC与SQL-92定义中的INSENSITIVE关键字的功能相同，它将游标定义为

静态游标。

5）KEYSET关键字定义一个键集驱动游标，键集游标中的数据行及其顺序是固定

的。

6）DYNAMIC将游标定义为动态游标，可以随时看到游标结果集中被修改的数据，

不能使用ABSOLUTE提取选项定位游标。

7) SCROLL_LOCKS选项要求SQL Server在将数据读入游标时，锁定基表中的数据

行，以确保以后能通过游标成功对基表进行定位删除和修改。

8）OPTIMISTIC说明不锁定基表的数据行，当应用程序通过游标对基表进行修改

时，首行检测游标填充之后表中的数据是否被修改，如果被修改则停止当前操作。

9）select_statement为定义游标集合的SELECT语句，其中不能包含COMPUTE、

COMPUTE BY、FOR BROWSE和不INTO关键字。

注意：游标声明语句中，如果有下列条件之一时，无论是否指定STATIC选项，系统

将自动把所建立的光标定义为静态光标： 1） SELECT语句中使用了DISTINCT、UNION、GROUP BY或HAVING等 关键词。

2)SELECT语句的选择列表中包含有集合表达式。

3)所有光标基表均没有建立唯一索引，而又要求建立键值游标。

4)SELECT语句中包含有ORDER BY子句，而ORDER BY子句指定的列又不具备唯一标识功能，所定义的动态游标将被转换为键集游标，不能转换为键集游标时，将转换为静态游标。

3、 填充游标：

定义游标后，使用OPEN语句可打开并填充游标。OPEN语句的语法格式： OPEN ｛｛[GLOBAL] cursor_name｝|cursor_variable_name｝

其中cursor_name参数或cursor_variable_name变量指所打开和填充的游标

名称。

@@CURSOR_ROWS全局变量

1）-m说明游标以异方式填充，m为当前键集中已填充的行数； 2）-1游标为动态游标，游标中的行数是动态变化的。

3）0指定的游标未打开，或是所打开的游标已经关闭或释放。 4）n游标被全部填充，返回值为游标中的和数。

4、提取数据：

Transact-SQL程序调用FETCH语句提取游标中数据，FETCH每次只提取一行数

据，FETCH语句的语法: 格式：

FETCH

[[NEXT | PRIOR | LAST | ABSOLUTE {n | @nvar}

| RELATIVE {n | @nvar}]FROM]

{{[GOLBAL] cursorname} | cursor_variable_name} [INTO @variable[,...n]]

说明：1)cursor_name参数或cursor_variable_name变量指出所提取数据的

游标名称。

2）GLOBAL表明所操作游标为一全局游标。

3）NEXT表明读取游标下一行，第一次对游标实行读取操作时，返回第一

行。

4）PRIOR、FIRST、 LAST、 ABSOLUTE n和RELATIVE n选项只适用于

SCROLL游标

5）INTO子句说明将读取的游标数据存放到指定的局部变量中。 @@FETCH_STATUS

1）0: 说明FETCH语句成功执行

2）-1：说明的指的位置超出了游标结果集合的范围，从而导致读取不到

数据

3）-2: 说明要读取的行已从游标结果集合中删除，它不再是游标结果

集中的成员

5、游标定位修改和删除操作

SQL Server中的UPDATE语句和DELETE语句也支持游标操作，它们可以通过游标修改或删除游标基表中的当前数据行。用于游标操作时，UPDATE语句和DELTEE语句的格式为：

1） UPDATE table_name SET 子句

WHERE CURRENT OF cursor_name 2） DELETE FORM table_name

WHERE CURRENT OF cursor_name

当游标基于多个数据表时，UPDATE语句和DELETE语句一次只能修改或删除一个

基表中的数据，而其它基表中数据不受影响。

6、关闭和释放游标

当提取完游标结果集合中的数据时，应及时调用CLOSE语句关闭游标。在关闭游

标时，SQL Server删除游标当前的结果集合，并释放游标对数据库的所有锁定。

CLOSE语句的语法格式：

CLOSE {{[GLOBAL] cursor_name}| cursor_variable_name}

DELLOCATE释放游标的数据结构之后，要使用游标必须重新执DECLARE语句。 DELLOCATE语句的语法格式:

DEALLOCATE {{[GLOBAL] cursor_name}| cursor_variable_name} 十二、存储过程

1、局部变量：局部变量是用户定义的变量。它用DECLARE语句声明，用户可以用

SELECT或SET语句为其赋值。使用范围是定义的批处理、存储过程、储发器。

命名局部变量的语法：

declare @variable_name datatype

[,@variable_name datatype]

其中，

A：@variable_name是局部变量的名字，必须以“@”符号开头。

B：datatype 是为该局部变量指定的数据类型，如果需要，指定资料长度。 例：声明一个整型变量@order_count ，使用如下语句： declare @order_count int

我们可以 在一条declare语句中声明多个变量，变量之间用逗号分开。 Declare @lastname varchar(30),@firstname varchar(20)

C：变量声明以后，系统自动给它们初始为NULL。为局部变量赋值可以使用SET

语句。语法：

Set @variable_name=expression[,@variable_name=expression]… 其中：

（1）@variable_name是局部变量名。

（2）expression是与局部变量的数据类型相匹配的表达式。该表达式的值

赋给指定的局部变量。

例：在批处理中声明两个变量，并为它们赋值，然后将它们用到SELECT语句 的WHERE子句中。 Use shikong Go

Declare @kcshl decimal(15,2),@kcje decimal(15,2) Set @kcshl=1000 Set @kcje=25000

Select * From spkfjc

Where kcshl<=@kcshl and kcje<=@kcje Go

把spbh为00000001的商品的kcje赋值给@kcje Select @kcje=kcje from spkfjc Where spbh=’00000001’

如果SELECT 得到的不是单一的结果而是一个结果集，那么最后一个结果赋给

变量。

2、全局变量：全局变是SQL SERVER系统提供并赋值的变量。用户不能建立全局变量，

也不能用SET语句来修改全局的值。通常将全局变量的值赋给局部变量，以便保存和

处理。全局变量的名字以@@开头。

例如：@@rowcount 表示最近一个语句影响的行数。@@error保存最近执行操作

的错误状态。

3、注释：是程序中不被执行的正文。

作用：第一、说明代码的含义，增强代码的可读性。

第二、可以把程序中暂时不用的语句注释掉，等需要时，再将它们恢复。 单行注释：-- 两个减号

本文来源：https://www.bwwdw.com/article/g4cg.html