【T-SQL】一、数据库的创建与管理

一、数据库的创建与管理

（一）创建数据库

完整语法格式

CREATE DATABASE <数据库名称> -- 其它选项使用默认值
[ON
[PRIMARY] 
{<数据文件> }[,……,n]
[FILEGROUP]
{<文件组名>} [,……,n]
LOG ON
{<日志文件>}[,……,n]
]

选项说明与设置

说明	名称
数据库名称	在SQL Server中创建数据库的名称
ON	指明主要数据文件、次要数据文件和文件组的明确定义
PRIMARY	指定创建在主文件组中的主要数据文件和次要数据文件
FILEGROUP	指定要创建的次文件组，并指定要创建在其中的次要数据文件
LON ON	指明食物日志文件的明确定义。若未定义，会自动 创建为所有数据文件总和25%大小或512KB大小的日志文件
<数据文件> <日志文件>	为下表属性的组合

名称	含义	说明
name	逻辑文件名	主数据文件名与日志文件名
filename	物理文件名	数据文件名：*.mdf 或 *.ndf，日志文件名：*.ldf
size	文件初始大小	单位MB
maxsize	文件最大值	单位MB
filegrowth	文件增长量	单位可以是绝对值，也可以是百分比

例题：图书馆管理数据库

创建图书管理数据库Library。该数据库的主要数据文件逻辑名称为Library，物理文件名为Library.mdf，物理文件路径为：D:\图书管理数据（事先在操作系统下建立相应的文件夹），初始大小为5MB，最大容量100MB，增长速度为10%；数据库的日志文件逻辑名称为Library_log，物理文件名为Library_log.ldf，物理文件路径为E:\图书管理日志（事先在操作系统下建立相应的文件夹），初始大小为1MB，最大容量为2MB，增长速度为10%。

CREATE DATABASE Library
ON PRIMARY(
NAME='Library',
FILENAME='D:\图书管理数据\Library.mdf',
SIZE=5MB,
MAXSIZE=10MB,
FILEGROWTH=10%
)

LOG ON(
NAME='Library_log',
FILENAME='E:\图书管理日志\Library_log.ldf',
SIZE=1,
MAXSIZE=2,
FILEGROWTH=10%
)

例题：学生管理数据库

创建一个指定多个数据文件和日志文件的数据库。该数据库名称为STUDENTS，有1个5MB和1个10MB的数据文件和2个5MB的事务日志文件。

数据文件逻辑名称为STUDENTS1和STUDENTS2，物理文件名为STUDENTS1.mdf和STUDENTS2.ndf。主文件是STUDENTS1，由PRIMARY指定，辅助数据文件STUDENTS2.ndf属于新建文件组FG1，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。

事务日志文件的逻辑名为STUDENTSLOG1和STUDENTSLOG2，物理文件名为STUDENTSLOG1.ldf和STUDENTSLOG2.ldf，最大尺寸均为50MB，文件增长速度为1MB。

要求数据文件和日志文件分别存放在d盘和e盘的DATA文件夹下。

create database STUDENTS
on primary(
name='STUDENTS1',
filename='D:\DATA\STUDENTS1.mdf',
size=5MB,
maxsize=unlimited,
filegrowth=10%
),filegroup FG1(
name='STUDENTS2',
filename='D:\DATA\STUDENTS2.ndf',
size=10MB,
maxsize=100MB,
filegrowth=1MB
)

log on(
name='STUDENTSLOG1',
filename='E:\DATA\STUDENTSLOG1.ldf',
size=5MB,
maxsize=50MB,
filegrowth=1MB
),(
name='STUDENTSLOG2',
filename='E:\DATA\STUDENTSLOG2.ldf',
size=5MB,
maxsize=50MB,
filegrowth=1MB
)

（二）查看数据库

数据库基本信息

• 数据库上次备份的日期：最后一次执行数据库日志备份操作的日期。
• 数据库日志上次备份日期：最后一次执行数据库日志备份操作的日期。
• 名称：数据库名称。
• 状态：数据库当前状态。
• 所有者：数据库的所有者名称。
• 创建日期：数据库创建的日期。
• 大小：数据库的所有数据文件和日志文件大小的总和。
• 可用空间：数据库的所有数据文件和日志文件的可用空间总和。
• 用户数：当前连接到数据库的用户数量。
• 排序规则：数据库当前使用的排序规则。

查看数据库信息

使用系统存储过程sp_helpdb查看数据库信息
语法格式：

[EXECUTE] sp_helpdb [数据库名]

PS：

• EXECUTE可以缩写为EXEC，如果它是一个批处理中的第一条语句可全部省略。
• 省略数据库名会默认查看所有数据库信息

查看所有可用数据库信息

使用系统存储过程sp_databases查看所有可用数据库信息
语法格式：

[EXECUTE] sp_database

查看当前数据库中某个文件信息

使用系统存储过程sp_helpfile查看当前数据库中某个文件信息
语法格式：

[EXECUTE] sp_helpfile [文件名]

查看当前数据库中某个文件组的信息

使用系统存储过程sp_helpfilegroup查看当前数据库中某个文件组的信息
语法格式：

[EXECUTE] sp_helpfilegroup [文件组名]

（三）修改数据库

完整语法格式

ALTER  DATABASE <数据库名称>
-- 增加
 ADD FILE <数据文件>[,…,n] [to FILEGROUP 文件组]	--增加数据文件到文件组
|ADD LOG FILE <日志文件>[,…,n]     				  --增加日志文件
|ADD FILEGROUP 文件组名                 		  --增加文件组
-- 移除
|REMOVE FILE 逻辑文件名              			 --移去文件
|REMOVE FILEGROUP 文件组名       				 --删除文件组 
-- 修改
|MODIFY FILE<数据文件>             		     	 --修改数据库文件属性
|MODIFY NAME=新数据文件名         			    --修改数据库文件名
|MODIFY FILEGROUP 文件组名         				 --修改文件组属性

例题：修改test数据库

在test数据库中添加一个次要数据文件和一个事务日志文件。次要数据文件逻辑名为test2，物理文件名为test2.ndf，初始大小3MB，最大容量10MB，增长速度为1MB；日志文件逻辑名称为testlog1，物理文件名为testlog1.ldf，初始大小为5MB，最大容量为100MB，增长速度为5MB。

alter database test
add file(
name='test2',
filename='D:/testdata/test2.ndf',
size=3MB,
maxsize=10MB,
filegrowth=1MB
)
go
alter database test
add log file(
name='testlog1',
filename='E:/testdata/testlog1.ldf',
size=5MB,
maxsize=100MB,
filegrowth=5MB
)

（四）删除数据库

使用DROP DATABASE命令删除数据库，其语法格式如下：

DROP DATABASE <数据库名>

使用DROP语句可以从SQL Server中一次删除一个或多个数据库。

（五）分离与附加

分离数据库

具体步骤如下：

（1）确保没有任何用户登录到数据库中。

（2）启动SSMS并连接到数据库实例。

（3）在【对象资源管理器】窗口中，展开“数据库”节点，用右键单击需要分离的数据库，在弹出的菜单中选额“任务”“分离”命令。

（4）打开的【分离数据库】窗口中的“数据库名称”栏中显示了所选择的数据库名称。

要分离数据库选项说明：

• 删除连接：删除所有用户连接。
• 更新统计信息：默认情况下，分离操作将在分离数据库时保留过期的优化统计信息；若要更新现有的优化统计信息，可选中【更新统计信息】复选框。
• 保留全文目录：若选中，可能确保数据库被分离时，不会丢失一起的的全文索引信息。

（5）设置完成后，单击“确定”按钮。

附加数据库

具体步骤如下：

（1）启动SSMS并连接到数据库实例。

（2）在【对象资源管理器】窗口中，展开“数据库”节点，从弹出的菜单中选择“附加”命令，如图4-18所示。

（3）在打开的【附加数据库】窗口中，单击“添加”按钮，会弹出【定位数据库文件】窗口，选择数据库所在的磁盘驱动器并展开目录树定位到数据库的.mdf文件。

（4）单节【定位数据库文件】窗口中的“确定”按钮，回到【附件数据库】窗口，可以为附加的数据库指定不同的名称和物理位置，如图4-20所示。

（5）设置完毕后，单击“确定”按钮。数据库引擎将执行附加数据库任务。如果附加成功，在【对象资源管理器】窗口中将会出现被附加的数据库。

（六）导入与导出

通过导入和导出操作可以在SQL Server 2012和其它数据源（例如Excel表、Access和Oracle数据库）之间轻松移动数据。“导出”是指将数据从SQL Server表复制到数据文件；“导入”是指将数据文件加载到SQL Server 2012表。例如，可以将数据从Excel文件导入到数据库表中，也可以将数据库表中的数据库导出到Excel文件。

（七）收缩数据库

​ 企业数据量很大，在设置数据库的时候就需要将数据文件和事务日志文件设置较大，但是往往在实际应用时不需要很大的空间，因此就需要把数据库尺寸收缩，使得计算机磁盘空间释放，提高资源的利用率。