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,