第二单元 创建和管理数据库

第二单元 创建和管理数据库

2.1使用SSMS创建数据库

SQL server中数据库的分类

数据库分为系统数据库和用户数据库，系统数据库又分为主数据库Master、模板数据库Model、数据库Msdb、临时数据库Tempdb。

主数据库Master

保存SQL server所有系统信息、所有数据库文件的位置，同时还记录了SQL server的初始化信息。

模板数据库Model

作为新创建数据库的一种模板或原型

Msdb数据库

用于安排SQL server的周期活动，包括任务调度、异常处理和报警管理等。

临时数据库Tempdb

用做系统的临时存储空间，其主要作用是存储用户建立的临时表和临时存储过程。

 

数据库的文件组成

1.文件

SQL Server采用文件来存放数据库，数据库文件可分为主数据文件、辅助数据文件和事务日志文件三类。

主数据库文件：

扩展名为.MDF，作用：主数据文件用于存放数据，是所有数据库文件的起点，有且只有一个。

辅助数据文件：

扩展名为.NDF，作用：辅助数据文件也用来存放数据，存储主数据文件未存储的所有其他数据和对象。可有可无，如有可有多个。

事务日志文件：

扩展名为.LDF，作用：事务日志文件用来存放事务日志，用来记录SQL Server中所有的事务和由这些事务引起的数据库的变化。至少要有一个。

2.文件组

文件组允许将多个数据文件归纳为同一组，并赋予此组一个名称，这就是文件组。但是需要注意的是事务日志文件不属于文件组。

文件组的作用：

(1)为了便于分配和管理数据库文件

(2)可以有效提高数据库的读写速度

 

 

数据库的文件组成

1.文件组类型：

(1)主文件组

主文件组是SQL Server自动创建的，名字叫PRIMARY，主文件组上包括主数据文件和所有没有被包括在其他文件组中的文件。

(2)用户自定义文件组

用户自定义文件组是为了提高数据库的性能，用户自己创建的，它包括创建或修改数据库时使用FileGroup关键字指定的文件组。

 

使用数据库文件和文件组的几个问题：

(1)一个文件或者文件组只能用于一个数据库，不能用于多个数据库。

(2)同一个辅数据文件只能存放在一个文件组中。

(3)文件组不适用于事务日志文件。

 

 

2.2使用SQL语句创建数据库

SQL语言

SQL是结构化查询语言(Structured Query Language)的简称，它是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL的功能主要包括三个方面：

数据定义

定义数据库、表、视图、索引等，包括这些对象的创建、修改和删除。

数据操纵

分为数据查询和数据更新。

数据控制

包括表和视图等数据库对象的授权。

 

 

创建数据库语法格式：

CREATE DATABASE 数据库名
ON[PRIMARY]
(NAME='数据文件的逻辑名称',
FILENAME='文件的路径和文件名',
SIZE=文件的初始大小,
MAXSIZE=文件的最大容量|UNLIMITED,
FILEGROWTH=文件的每次增长量)[,...n]
LOG ON
(NAME='事务日志文件的逻辑名称',
FILENAME='文件的路径和文件名',
SIZE=文件的初始大小,
MAXSIZE=文件的最大容量|UNLIMITED,
FILEGROWTH=文件的每次增长量)[,...n]
#这5条语句NAME和FILENAME是不可以省略的，其他的三条可以省略，则默认按照系统属性值进行设置。

 

使用SQL语句创建数据库

实例1：

创建产品销售管理数据库Shop，采用系统默认配置方式(只包含一个主数据文件和一个日志文件，它们均采用系统默认文件名，其大小、最大长度和文件增长幅度均采用系统默认值)。

create database shop

实例2：创建学生选课管理数据库SCC

要求：有两个数据文件，其中主数据文件初始化大小为10MB，最大容量为60MB，按2MB增长，文件名为SCC_data1.mdf;一个辅助数据文件为10MB，最大大小不限，按10%增长，文件名为SCC_data2.ndf。该数据库有2个日志文件，SCC_log1.ldf和SCC_log2.ldf，大小、最大长度和文件增长幅度均采用系统默认值。所有文件存放在e:\DB文件夹中。

Create Database SCC
on
(name=SCC_data1,
filename='e:\database\SCC_data1.mdf',
size=10mb,
maxsize=60,
filegrowth=2
),
(name=SCC_data2,
filename='e:\database\SCC_data2.mdf',
size=10mb,
maxsize=unlimited,
filegrowth=10%
)
Log On
(name=SCC_log1,
filename='e:\database\SCC_log1.ldf'
),
(name=SCC_log2,
filename='e:\database\SCC_log2.ldf'
)

实例3：

创建数据库Test，要求：有两个文件组，主文件组primary包括文件Test_data1，Test_data2；第2个文件组名为Testgroup，包括文件Test_data3,Test_data4.该数据库只有一个日志文件。所有文件的大小、最大长度和文件增长幅度均采用系统默认值，所有文件都存放在e:\sql文件夹中。

Create Database Test
On Primary
(name=Test_data1,
filename='e:\sql\Test_data1.mdf'
),
(name=Test_data2,
filename='e:\sql\Test_data2.mdf'
),
Filegroup Testgroup
(name=Test_data3,
filename='e:\sql\Test_data3.ndf'
),
(name=Test_data4,
filename='e:\sql\Test_data4.ndf'
)
Log On
(name=Test_log,
filename='e:\sql\Test_log.ldf')

使用"模板资源管理器"创建数据库

SSMS----视图----"模板资源管理器"----Database----Create Database on Multiple Filegroups

 

 

2.3数据库的维护

包括修改数据库和删除数据库

修改数据库：

不可以修改：主数据文件名；事务数据文件名；存放路径

可以修改：新增辅助数据文件或事务日志文件；修改配置选项

修改数据库包括三个方面：

1.在数据库中新增文件组和文件，删除文件组和文件。

2.修改数据库文件或事务日志的容量

3.修改数据库名称。

新增文件组和文件的语法格式是：

#新增文件组：
Alter Database 数据库名
Add Filegroup newgroup
#新增辅数据文件：
Alter Database 数据库名
Add File
(NAME='数据文件的逻辑名称',
FILENAME='文件的路径和文件名',
SIZE=文件的初始大小,
MAXSIZE=问价的最大容量|UNLIMITED,
FILEGROWTH=文件的每次增长量)
#新增事务日志文件：
Alter Database 数据库名
Add log File
(NAME='数据文件的逻辑名称',
FILENAME='文件的路径和文件名',
SIZE=文件的初始大小,
MAXSIZE=文件的最大容量|UNLIMITED,
FILEGROWTH=文件的每次增长量)

注意：

1.一条Alter Database语句只能新增一个文件，不管是数据文件还是日志文件。

2.如果是数据文件，只能是辅数据文件，因为一个数据库只能有一个主数据文件。

 

修改数据库

实例1：

在SCC中新增名为newgroup的文件组，并为该文件添加一个辅数据文件SCC_data3.ndf,同时为数据库添加一个事务日志文件SCC_log3.ldf,文件属性都使用系统默认设置。

Alter Database SCC
Add Filegroup newgroup
​
Alter Database SCC
Add File
(name=SCC_DATA3,
filename='e:\database\SCC_data3.ndf'
)
Alter Database SCC
Add log File
(name=SCC_log3,
filename='e:\database\SCC_data.ldf')

 

删除文件组和文件需要掌握的语法格式是：

#删除文件组：
Alter Database 数据库名
Remove filegroup 文件组名
​
#删除文件：
Alter Database 数据库名
Remove file 文件名

注意：

在删除文件组时必须保证文件组为空，也就是说，文件组的数据文件要在删除文件组之前全部删除。

实例2：删除SCC数据库中的newgroup文件组.

Alter Database SCC
Remove File SCC_data3
Alter Database SCC
Remove Filegroup newgroup

修改数据库文件或事务日志的容量，SQL语法：

Alter Database 数据库名
Modify file
(NAME='数据文件的逻辑名称',
SIZE=文件的初始大小,
MAXSIZE=文件的最大容量|UNLIMITED,
FILEGROWTH=文件的每次增长量)
#文件的物理名称是不能修改的，所以没有filename

实例3：将数据库SCC的数据文件SCC_data1的容量从原来的10MB扩充20MB，事务日志文件SCC_log1的容量从原来的8MB扩充至16MB。

Alter Database SCC
Modify file
(NAME=SCC_data1,
SIZE=20MB)
Alter Database SCC
Modify file
(NAME=SCC_log1,
SIZE=16MB)

修改数据库名称：

两种方法：

1.使用Alter Database语句

2.调用系统内置存储过程sp_renamedb.

 

实例4:将数据库Test重命名为NewTest

Alter Database Test
Modify name=NewTest
​
Sp_renamedb Test,NewTest

删除数据库

删除数据库作用：

可以释放数据库所占用的磁盘空间

 

能被删除数据库的三种情况：

(1)当用户正使用数据库时；

(2)当数据库正在恢复时；

(3)当数据库正被复制时。

 

删除数据库语法格式为：

Drop Database 数据库名

实例5：删除名为NewTest的数据库

Drop Database NewTest