实例1(数据库的创建和管理).sql

经典的sql资料

-- 上课内容：第二单元 数据库的创建和管理

-- 2.1 数据库的创建

 -- 2.1.1 基本结构

CREATE DATABASE 数据库名
ON [PRIMARY]	 -- 定义 数据文件，包括主数据文件.MDF、辅助数据文件.NDF，多个数据文件用，隔开
(
NAME=逻辑文件名,
FILENAME='物理文件名',  -- 要求写名文件路径
SIZE=初始大小,
MAXSIZE=文件最大值,
FILEGROWTH=文件增长增量
)
TO FILEGROUP 文件组名


LOG ON 		 -- 定义 日志文件，至少有一个，可以有多个日志文件，之间用，隔开
(
NAME=逻辑文件名,
FILENAME='物理文件名',  -- 要求写名文件路径，以.ldf为扩展名
SIZE=初始大小,
MAXSIZE=文件最大值,
FILEGROWTH=文件增长增量
)


-- 例1：创建一个数据库test1,只有一个数据文件d:\data\t1_data1.mdf,
     -- 初始大小为10M,最大尺寸为30M,增长速度为20%
     -- 一个日志文件d:\data\t1_log1.ldf
     -- 初始大小为5M,最大尺寸为50M,增长速度为2M

create database test1
on primary
(
name=t1_data1,
filename='d:\data\t1_data_1.mdf',  	-- 注意：
size=10M,				-- 注意：
maxsize=30,
filegrowth=20%
)
log on
(
name=t1_log1,
filename='d:\data\t1_log_1.ldf',
size=5,
maxsize=50,
filegrowth=2
)

-- 2.2 查看数据库信息

 -- 2.2.1 查看当前服务器上的所有数据库信息
    sp_helpdb [数据库名]
    -- 例：
    sp_helpdb test1

 -- 2.2.2 查看当前服务器上所有可以使用的数据库信息
    sp_databases

 -- 2.2.3 查看当前服务器上的所有文件信息
    sp_helpfile [任意一个逻辑文件名]
    -- 例：
    sp_helpfile t1_data1

 -- 2.2.4 查看当前服务器上的所有文件组信息
    sp_helpfilegroup [任意一个逻辑文件组名]
    -- 例：
    sp_helpfilegroup t1_group1


-- 2.3 修改数据库

 -- 2.3.1 基本结构

ALTER DATABASE 数据库名

{
 ADD FILE 		--添加数据文件

|ADD LOG FILE 		--添加日志文件

|REMOVE FILE  		--移除文件

|ADD FILEGROUP 		--添加文件组

|MODIFY FILE		--修改文件

|MODIFY NAME 		--重命名数据库

|MODIFY FILEGROUP 	--修改文件组

}

  -- 2.3.2 修改数据库
   -- 2.3.2.1 增加文件组
   alter database test1
   add filegroup t_group1
   sp_helpfilegroup t_group1

   -- 2.3.2.2 增加数据文件
   alter database test1
   add file
   (name=t1_data2,
    filename='d:\data\t1_data_2.ndf',
    size=10,
    maxsize=50,
    filegrowth=20%
    )
    to filegroup t_group1

    sp_helpdb test1


   -- 2.3.2.3 增加日志文件
   alter database test1
   add log file
   (name=t1_log2,
    filename='d:\data\t1_log_2.ldf',
    size=5,
    maxsize=20,
    filegrowth=1
    )
 
   -- 2.3.2.4 修改文件
   alter database test1
   modify file
   (name='t1_data2',
    --filename='d:\data\t1_data_2.ndf',	--注意：不需要指定物理文件名
    size=60,
    maxsize=100,
    filegrowth=2
    )

   -- 2.3.2.5 删除文件
   alter database test1 
   remove file t1_data2

   alter database test1 
   remove file t1_log2

 -- 2.4 收缩数据库

  -- 2.4.1 收缩数据库
     DBCC SHRINKDATABASE(数据库名，剩余可用空间百分比) 
     -- 例：
     dbcc shrinkdatabase(test1,50)
     sp_helpdb test1

  -- 2.4.2 收缩数据库文件
     DBCC SHRINKFILE(逻辑文件名，收缩文件所需要的文件大小)
     -- 例：
     dbcc shrinkfile(t1_data2,20)
     sp_helpdb test1

 -- 2.5 重命名数据库
    sp_renamedb 旧数据库名，新数据库名
    -- 例：
    sp_renamedb test2,test1

 -- 2.6 删除数据库
    DROP DATABASE 数据库名
    -- 例：
    drop database test1



--练习：

-- 1. 创建mytest数据库，数据文件逻辑名为't_data_1',物理文件名为't_data.mdf',初始大小为10M,最大尺寸为30M，增长速度为10%
    --日志文件逻辑名为't_log_1',物理文件名为't_log.ldf',初始大小为5M,最大尺寸为20M，增长速度为1M
    --并查看数据库信息


-- 2. 为mytest数据库添加一个包含两个数据文件的文件组和一个日志文件
    --文件组名t_group1,数据文件名分别为t_add1和t_add2,物理文件名分别为t_add1.ndf和t_add2.ndf
    --初始大小均为10M，最大尺寸均为30M，文件增长速度分别为10%和2M
    --日志文件逻辑文件名为t_addlog1,数据文件名为t_addlog1.ldf,
    --初始大小均为1M，最大尺寸为无限大，文件增长速度为1M
    --并查看数据库信息


-- 3. 将mytest数据库中的t_data_1容量增加到60M，上限为100M,自动增长3M,并查看数据库信息

-- 4. 使用DBCC SHRINKFILE 把数据文件t_add1缩小到5M,并查看数据库信息

-- 5. 重命名数据库为test1










--练习参考答案：

-- 1. 创建mytest数据库，数据文件逻辑名为't_data_1',物理文件名为't_data.mdf',初始大小为10M,最大尺寸为30M，增长速度为10%
    --日志文件逻辑名为't_log_1',物理文件名为't_log.ldf',初始大小为5M,最大尺寸为20M，增长速度为1M
    --并查看数据库信息
create database mytest
on primary
(
name=t_data_1,
filename='d:\data\t_data.mdf',
size=10,
maxsize=30,
filegrowth=10%
)
log on
(
name=t_log_1,
filename='d:\data\t_log.ldf',
size=5,
maxsize=20,
filegrowth=1
)

-- 2. 为mytest数据库添加一个包含两个数据文件的文件组和一个日志文件
    --文件组名t_group1,数据文件名分别为t_add1和t_add2,物理文件名分别为t_add1.ndf和t_add2.ndf
    --初始大小均为10M，最大尺寸均为30M，文件增长速度分别为10%和2M
    --日志文件逻辑文件名为t_addlog1,数据文件名为t_addlog1.ldf,
    --初始大小均为1M，最大尺寸为无限大，文件增长速度为1M
    --并查看数据库信息
alter database mytest
add filegroup t_group1

alter database mytest
add file
(
name=t_add1,
filename='d:\data\t_add1.ndf',
size=10,
maxsize=30,
filegrowth=10%
),
(
name=t_add2,
filename='d:\data\t_add2.ndf',
size=10,
maxsize=30,
filegrowth=2
)
to filegroup t_group1

alter database mytest
add log file
(
name=t_addlog1,
filename='d:\data\t_addlog1.ldf',
size=1,
maxsize=unlimited,
filegrowth=1
)

-- 3. 将mytest数据库中的t_data_1容量增加到60M，上限为100M,自动增长3M,并查看数据库信息
alter database mytest
modify file
(
name=t_data_1,
size=60,
maxsize=100,
filegrowth=3
)

sp_helpdb mytest


-- 4. 使用DBCC SHRINKFILE 把数据文件t_add1缩小到5M,并查看数据库信息
dbcc shrinkfile(t_add1,5)
sp_helpdb mytest

-- 5. 重命名数据库为test1

sp_renamedb mytest,test1