SqlServer：数据库的创建与修改

目录

• 数据库文件
• 创建数据库
  • CREATE DATABASE 命令
  • 使用样例
  • 样例一
  • 样例二
  • 样例三
  • 样例四
• 分离、附加数据库
  • 分离数据库
  • 附加数据库
  • 样例
• 修改数据库
  • ALTER DATABASE
  • 样例一
  • 样例二
• 删除数据库
  • DROP DATABASE
  • 样例
• 参考资料

数据库文件#

数据库包括逻辑结构和物理结构两个部分，对应的文件也有逻辑文件和物理文件之分。一个数据库对应的物理文件主要有以下 4 种类型：

1. 主数据库文件：也叫行数据文件，类型名是 .mdf，存储数据库的启动信息和全部数据内容，一个数据库至少要有一个 mdf 文件；
2. 辅助数据库文件：也称次要数据库文件，类型名是 .ndf，用于存储除了主数据库文件之外的其他文件信息，一个数据库可以有 0 个或者一个以上的 .ndf 文件；
3. 事务日志文件：类型名是 .ldf，记录对数据库的操作情况，系统会自动将用户名、登录日期、机器名、操作内容等信息按照时间的先后顺序记录在日志文件中；
4. 文件流文件：SQL Server 允许以文件流文件的形式存放大对象数据，而不必将所有数据都保存到数据文件中。

一个数据库至少应该包含一个主数据库文件和一个事务日志文件，当一个数据库数据内容非常多时，数据文件也会有多个。为方便管理可以将文件分成若干组，称为文件组，每一个数据文件必须属于且只能属于一个组，系统默认的文件组是 PRIMARY，即主文件组。

创建数据库#

创建数据库就是确定数据库名，并设置对应的参数，包括所有者、磁盘文件名及存储位置、初始大小、最大容量、增长速度。创建数据库有两种方法：一种是用 SSMS 管理器的菜单方式，另一种是用命令方式，其中命令方式的用途更广泛。

CREATE DATABASE 命令#

CREATE DATABASE 命令创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件中附加数据库。CREATE DATABASE 命令的主要语法为：

Copy Highlighter-hljs
CREATE DATABASE database_name 

其中 CREATE DATABASE 命令的参数较多，具体可以查看说明文档，本文将展示一些常用的用法。

使用样例#

样例一#

创建数据库 DBA，不指定任何文件。

Copy Highlighter-hljs
CREATE DATABASE DBA
GO

样例二#

创建数据库 DBB，仅指定一个数据文件 DBB_dat。

Copy Highlighter-hljs
CREATE DATABASE DBB  
ON   
( 
    NAME = DBB_dat,  
    FILENAME = '(文件路径省略)\DBBdat.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 
) 
GO

样例三#

创建数据库 DBC，指定一个大小 5MB，最大 10，增长 10% 的数据文件 DBC_dat，和一个大小 3，最大 10MB，增长 2 的日志文件 DBC_log。

Copy Highlighter-hljs
CREATE DATABASE DBC 
ON   
( 
    NAME = DBC_dat,  
    FILENAME = '(文件路径省略)\DBCdat.mdf',  
    SIZE = 5,  
    MAXSIZE = 10,
    FILEGROWTH = 10% 
)
LOG ON  
(
    NAME = DBC_log,  
    FILENAME = '(文件路径省略)\DBClog.ldf',
    SIZE = 3MB,  
    MAXSIZE = 10MB,  
    FILEGROWTH = 2MB 
)  
GO 

样例四#

创建数据库 DBD，主文组中 1 个数据文件。并增加一个文件组，并其中增加 1 个数据文件和 2 个日志文件。

Copy Highlighter-hljs
CREATE DATABASE DBD
ON
PRIMARY ( 
    NAME = DBD_dat1,
    FILENAME = '(文件路径省略)\DBDdat1.mdf',
    SIZE = 10MB,
    MAXSIZE = 20,
    FILEGROWTH = 2
),
FILEGROUP SalesGroup1
( 
    NAME = SC1DBD_dat2,
    FILENAME = '(文件路径省略)\DBDdat2.mdf',
    SIZE = 5,
    MAXSIZE = 10,
    FILEGROWTH = 5 
)
LOG ON 
( 
    NAME = DBD_log1,
    FILENAME = '(文件路径省略)\DBDlog1.ldf',
    SIZE = 10MB,
    MAXSIZE = 20,
    FILEGROWTH = 2
),
( 
    NAME = DBD_log2,
    FILENAME = '(文件路径省略)\DBDlog2.ldf',
    SIZE = 10MB,
    MAXSIZE = 20,
    FILEGROWTH = 2
)
GO 

分离、附加数据库#

分离数据库#

SQL Server 允许分离数据库的数据和事务日志文件，然后将其重新附加到另一台服务器。分离数据库将从 SQL Server 删除数据库，但是保持组成该数据库的数据和事务日志文件中的数据库完好无损。语法为如下，其中 dbname 参数为要分离的数据库名称。

Copy Highlighter-hljs
sp_detach_db [ @dbname = ] 'dbname' [ , [ @skipchecks = ] 'skipchecks' ]

附加数据库#

将需要将被分离的数据库重新附加时，必须指定主数据文件的名称和物理位置。语法为如下，其中 dbname 参数为要附加的数据库名称，filename_n 参数为数据库文件的路径和名称。

Copy Highlighter-hljs
sp_attach_db [ @dbname = ] 'dbname' , [ @filename1 = ] 'filename_n' [ ,...16 ]

样例#

分离数据库 DBA，把数据库所有文件直接拷贝到其他文件夹后附加该数据库。首先使用 sp_detach_db 分离数据库：

Copy Highlighter-hljs
EXEC sp_detach_db DBA

此时数据库 DBA 已经分离，在 MSSMS 已经看不到了，但是在文件系统中还是存在。直接将这个文件拷贝到其他的文件夹(例如 d:\DB_text)，使用 sp_attach_db 附加数据库。

Copy Highlighter-hljs
EXEC sp_attach_db @dbname = DBA, 
@filename1 = N'd:\DB_text\DBA.mdf',
@filename2 = N'd:\DB_text\DBA_log.ldf'

修改数据库#

ALTER DATABASE#

在数据库中添加或删除文件和文件组,也可用于更改文件和文件组的属性，例如更改文件的名称和大小。ALTER DATABASE 提供了更改数据库名称、文件组名称以及数据文件和日志文件的逻辑名称的能力。

Copy Highlighter-hljs
ALTER DATABASE database 
{ ADD FILE < filespec > [ ,...n ] [ TO FILEGROUP filegroup_name ] 
| ADD LOG FILE < filespec > [ ,...n ] 
| REMOVE FILE logical_file_name 
| ADD FILEGROUP filegroup_name 
| REMOVE FILEGROUP filegroup_name 
| MODIFY FILE < filespec > 
| MODIFY NAME = new_dbname 
| MODIFY FILEGROUP filegroup_name {filegroup_property | NAME = new_filegroup_name }
| SET < optionspec > [ ,...n ] [ WITH < termination > ] 
| COLLATE < collation_name > 
}

ALTER DATABASE 命令的参数较多，具体可以查看说明文档，本文将展示一些常用的用法。

样例一#

向数据库 DBA 增加一个数据文件和一个日志文件。

Copy Highlighter-hljs
ALTER DATABASE DBA
ADD FILE 
(
    NAME = DBA_dat2,
    FILENAME = '(文件路径省略)\DBA_dat2.ndf',
    SIZE = 5MB,
    MAXSIZE = 100MB,
    FILEGROWTH = 5MB
)
GO

ALTER DATABASE DB010A 
ADD LOG FILE 
( 
    NAME = DBA_log2,
    FILENAME = '(文件路径省略)\DBA_log2.ldf',
    SIZE = 10MB,
    MAXSIZE = 20,
    FILEGROWTH = 2
)
GO

接着删除数据文件 DBA_dat2 和第二个日志文件 DBA_log2。注意数据文件 DBA.mdf 是主数据文件不能被删除，否则数据库中一个数据文件都没有，就没有了存在的意义了。同理数据文件 DBA_log.ldf 是主日志文件也不能被删除，否则数据库无法记录任何日志信息。

Copy Highlighter-hljs
ALTER DATABASE DBA
REMOVE FILE DBA_dat2
GO

ALTER DATABASE DBA
REMOVE FILE DBA_log2
GO

样例二#

向数据库 DBB 增加一个文件组 FG，并在其中增加 1 个大小 10Mb 的文件。

Copy Highlighter-hljs
ALTER DATABASE DBB
ADD FILEGROUP FG
GO

ALTER DATABASE DBB
ADD FILE 
( 
    NAME = dbb_dat2,
    FILENAME = '(文件路径省略)\dbb_dat2.mdf',
    SIZE = 10MB,
    MAXSIZE = 20MB,
    FILEGROWTH = 5MB
)
TO FILEGROUP FG
GO

ALTER DATABASE DBB
MODIFY FILEGROUP FG DEFAULT
GO

接着把数据库 DB dbb_dat 文件改为改为 15MB，注意命令 ALTER DATABASE 修改数据文件时，修改后的文件大小必须大于原来的大小，否则报错。

Copy Highlighter-hljs
ALTER DATABASE DBB
MODIFY FILE
(
    NAME = dbb_dat2,
    SIZE = 15MB
)
GO

删除数据库#

DROP DATABASE#

DROP DATABASE 命令删除一个或多个数据库，同时将删除数据库所使用的数据库文件和磁盘文件。命令的语法如下，其中 database_name 参数为要删除的数据库名称。

Copy Highlighter-hljs
DROP DATABASE database_name [ ,...n ]

样例#

删除数据库 DBC。

Copy Highlighter-hljs
DROP DATABASE DBC

参考资料#

《SqlServer 2014 数据库技术实用教程》，胡伏湘、肖玉朝 主编，清华大学出版社