使用T-SQL创建、修改数据库

原文出处：http://blog.jobbole.com/86718/

一、使用Transact_SQL创建数据库
Transact_SQL语法如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create database database_name   [ on     [ primary ] [<filespec> [,...n] ]   ]   [ log on   [<filespec>[,...n]]   ];     <filespec>::=    (       name =logical_file_name      [  ,  newname = new_login_name ]      [  ,  fileName = { 'os_file_name' | 'fileStream_path' } ]      [  ,   size  =  size [ KB | MB | GB | TB] ]      [  ,  MaxSize  = {max_size [ KB | MB |GB |TB] | UNLIMITED} ]      [  ,  FILEGROWTH  = growth_increment [ KB | MB |GB | TB | %] ] );

上述语句分析如下：

1.database_name:数据库名称，不能与SQL SERVER中现有的数据库实例名称相冲突,最多可包含128个字符;

2.ON:指定显示定义用来存储数据库中的数据的磁盘文件。

3.PRIMARY：指定关联的<filespec>列表定义的主文件，在主文件组<filespec>项中指定第一个文件将生成主文件，一个数据库只能有一个主文件。如果没有指定primary，那么create datebase 语句中列出的第一个文件将成为主文件。

4.LOG ON:指定用来存储数据库日志的日志文件。LOG ON后跟以逗号分隔的用以定义日志文件的<filespec>列表。如果没有指定log on，将自动创建一个日志文件，其大小为该数据库的所有文件大小总和的25%或521KB，取两者之中最大者。

5.name:指定文件的逻辑名称。指定filename时，需要使用name，除非指定 FOR ATTCH 子句之一。无法将filename文件组命名为primary。

6.filename:指定创建文件时又操作系统使用的路径和文件名。执行create datebase 语句前，指定路径必须存在.

7.size:指定数据库文件的初始大小，如果没有为主文件提供size，数据库引擎使用model数据库中主文件的大小。

8.max_size:指定文件可增大的最大大小。可使用KB、MB、GB和TB做后缀，默认值为MB。max_size是整数值.如果不指定max_size,则文件将不断增长直至磁盘被占满。UNLIMITED表示文件一直增长到磁盘装满.

9.filegrowth:指定文件的自动增量。文件的filegrowth设置不能超过MAXSIZE设置。该值可以 MB、KB、GB、TB或百分比（%）为单位指定，默认值为MB，如果指定%，则增量大小为发生增长时文件大小的的指定百分比。值为0表明自动增长被设为关闭，不允许增加空间.

eg:创建一个数据库sample_db,该数据库的主数据文件逻辑名为sample_db，物理文件名称为sample_db.mdf,初始大小为5MB，最大尺寸为30MB，增长速度为5%；数据库日志文件的逻辑名称为sample_log,保存日志文件的物理名称为sample_log.ldf,初始大小为1MB，最大尺寸为8MB，增长速度为128KB。
代码如下:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

create database [sample_db] on primary (       name = 'sample_db' ,      filename= 'C:\SQL_SERVER_temp\sampl_db.mdf' ,     size =5120KB,     maxsize=30MB,     filegrowth=5% ) log on (       name = 'sample_log' ,     filename= 'C:\SQL_SERVER_temp\sample_log.ldf' ,       size =1024KB,     maxsize=8192KB,      filegrowth=10% ) go

二：使用Transact_SQL修改数据库

使用alter database语句进行修改:
alter database 语句可以进行一下的修改：增加或删除数据文件、改变数据文件或日志文件的大小和增长方式，增加或者删除日志文件和文件组。
基本语法如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

alter database database_name {     modify name =new_database_name    | Add file<filespec> [ ,...n ] [ TO filegroup { filegroup_name } ]    | Add log file <filespec> [ ,...n ]    | remove file logical_file_name    | modify file <filespec> }   <filespec>::=    (       name =logical_file_name      [  ,  newname = new_login_name ]      [  ,  fileName = { 'os_file_name' | 'filestream_path' } ]      [  ,   size  =  size [ KB | MB | GB | TB] ]      [  ,  MaxSize  = {max_size [ KB | MB |GB |TB] | UNLIMITED} ]      [  ,  FILEGROWTH  = growth_increment [ KB | MB |GB | TB | %] ]      [ , offline ] );

上述语法解释如下：

database_name:要修改的数据库的名称

modify name:指定新的数据库名称；

Add file:向数据库中添加文件。

to filegroup{filegroup_name}:将指定文件添加到文件组。filegroup_name为文件组名称.

Add log file:将要添加的日志文件添加到指定的数据库

remove file logical_file_name:从SQL Server的实例中删除逻辑文件并删除物理文件。除非文件为空，否则无法删除文件。

logical_file_name是在Sql Server 中引用文件时所用的逻辑名称。

modify file:指定应修改的文件，一次只能更改一个<filespec>属性。必须在<filespec>中指定name,以标识要修改的文件。如果指定了size，那么新大小必须比文件当前大小要大。

–使用T_SQL语句修改数据库文件的初始大小：
–将sample_db数据库中的主数据文件的初始大小修改为15MB:

1 2 3 4 5 6

alter database sample_db   modify file   (     name = 'sample_db' ,     size =15MB  );

注意：修改数据库文件的初始大小时，指定的size的大小必须大于或等于当前大小，如果小于，代码不能执行.

–使用T_SQL语句增加数据库容量：
–增加sample_db数据库的容量，如下：

1 2 3 4 5 6 7

alter database sample_db    modify file    (      name = 'sample_db' ,     maxsize=50MB     );  go

–使用T_SQL缩减数据库容量:
–缩减 sample_db数据库的容量，如下：

1 2 3 4 5 6 7

alter database sample_db   modify file   (       name = 'sample_db' ,     MAXSIZE=15MB   );  go

三、查看数据库信息

Sql Server中可以使用多种方式查看数据库信息，例如使用目录视图，函数，存储过程等。
1.使用目录视图：
可以使用如下的目录视图查看数据库的基本信息。

a.使用sys.database_files查看有关数据库文件的信息
b.使用sys.filegroups查看有关数据库组的信息。
c.使用sys.master_files 查看数据库文件的基本信息和状态信息。
d.使用sys.databases 数据库和文件目录视图查看有关数据库的基本信息。
eg:

1 2 3 4

select * from sys.database_files;    select * from sys.filegroups;    select * from sys.master_files;    select * from sys.databases;

2.使用函数：
如果要查看指定数据库中的指定选项信息时，可以使用DataBaseProperTyex()函数，该函数每次只返回一个选项的信息.
eg:要查看test数据库的状态信息，输入如下语句：

1 2 3

use test     go    select databasepropertyex( 'test' , 'Status' ) as 'test数据库状态'

注：上述代码中DataBaseProperTyex语句中第一个参数表示要返回信息的数据库，第二个参数表示要返回数据库的属性表达式，其他的查看属性参数如下：

3.使用系统存储过程：
除了上述的目录视图和函数外，还可以使用存储过程 sp_spaceused显示数据库使用和保留的空间。
sp_helpdb存储过程查看所有数据库的基本信息

4.使用图形化工具查看，大家都会，就不罗嗦了。
四：使用T_SQL修改数据库名：
–使用alter database 语句修改数据名称，语法如下：

1 2	alter database old_database_name modify name =new_database_name

使用T_SQL删除数据库：
当数据库不再需要时，为了节省空间,可以将他们从系统中删除。

1 2	drop database database_name[ , ...n ] eg: drop database test;

注意:

1.删除数据库时一定要慎重，因为系统无法轻易恢复被删除的数据，除非做过数据库的备份.另外，特别注意，千万不能删除系统数据库( master–>数据库服务器的核心;

model–>创建数据库的模版;
msdb–>提供运行SQL Server Agent工作的信息;
tempdb–>Sql Server中的一个临时数据库，用于存放临时对象和中间结果，Sql server关闭后，该数据库中的内容被清空，每次重新启动服务器后tempdb数据库将被重建.
)，否则会导致sql server服务器无法启动.

2.并不是所有的数据库在任何时候都可以被删除，只有处于正常状态下的数据库，才能使用drop语句删除。当数据库处于一下状态时不能删除：a.数据库正在使用；b.数据库正在恢复; c.数据库包含用于复制的对象。