SQL Server 2012数据库管理与开发（慕课版）

第3章 创建和管理数据库

第3章　创建和管理数据库

本章主要介绍使用SQL语句和使用界面方式（SQL Server Management Studio）创建数据库、修改数据库和删除数据库的过程。通过本章的学习，读者可以熟悉SQL Server 2012数据库的组成元素，并能够掌握创建和管理数据库的方法。

本章要点

  • SQL Server 2012的组成
  • SQL Server 2012命名规范
  • SQL Server 2012数据的创建、修改与删除

3.1　认识数据库

本节将对数据库的基本概念、数据库对象及其相关知识进行详细的介绍。

3.1.1　数据库的基本概念

Picture

数据库的基本概念

数据库（DataBase）是按照数据结构来组织、存储和管理数据的仓库，是长期存储在一起的相关数据的集合。其优点主要体现在以下几方面。

（1）减少了数据的冗余度，节省数据的存储空间。

（2）具有较高的数据独立性和易扩充性。

（3）实现数据资源的充分共享。

下面介绍一下与数据库相关的几个概念。

1．数据库管理系统

数据库管理系统（DataBase Management System，DBMS）是数据库系统的一个重要组成部分，是位于用户与操作系统之间的一个数据管理软件，负责数据库中的数据组织、数据操纵、数据维护和数据服务等。主要具有如下功能。

（1） 数据存取的物理构建：为数据库模式的物理存取与构建提供有效的存取方法与手段。

（2） 数据操纵功能：为用户使用数据库的数据提供方便，包括查询、插入、修改、删除以及简单的算术运算和统计功能。

（3） 数据定义功能：用户可以通过数据库管理系统提供的数据定义语言（Data Definition Language，DDL）方便地对数据库中的对象进行定义。

（4） 数据库的运行管理：数据库管理系统统一管理数据库的运行和维护，以保障数据的安全性、完整性、并发性和故障的系统恢复性。

（5） 数据库的建立和维护功能：数据库管理系统能够完成初始数据的输入和转换、数据库的转储和恢复、数据库的性能监视和分析等任务。

2．关系数据库

关系数据库是支持关系模型的数据库。关系模型由关系数据结构、关系操作集合和完整性约束3部分组成。

（1）关系数据结构：在关系模型中数据结构单一，现实世界的实体以及实体间的联系均用关系来表示，实际上关系模型中的数据结构就是一张二维表。

（2）关系操作集合：关系操作分为关系代数、关系演算、具有关系代数和关系演算双重特点的语言（SQL语言）。

（3）完整性约束：完整性约束包括实体完整性、参照完整性、用户自定义的完整性。

3.1.2　数据库常用对象

Picture

数据库常用对象

在SQL Server 2012的数据库中，表、视图、存储过程和索引等具体存储数据或对数据进行操作的实体都被称为数据库对象。下面介绍几种常用的数据库对象。

1．表

表是包含数据库中所有数据的数据库对象，由行和列组成，用于组织和存储数据。

2．字段

表中每列称为一个字段，字段具有自己的属性，如字段类型、字段大小等，其中字段类型是字段最重要的属性，它决定了字段能够存储哪种数据。

SQL规范支持5种基本字段类型：字符型、文本型、数值型、逻辑型和日期时间型。

3．索引

索引是一个单独的、物理的数据库结构。它是依赖于表建立的。在数据库中索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需的数据。

4．视图

视图是从一张或多张表中导出的表（也称虚拟表），是用户查看数据表中数据的一种方式。表中包括几个被定义的数据列与数据行，其结构和数据建立在对表的查询基础之上。

5．存储过程

存储过程（Stored Procedure）是一组用于完成特定功能的SQL语句集合（包含查询、插入、删除和更新等操作），经编译后以名称的形式存储在SQL Server服务器端的数据库中，由用户通过指定存储过程的名字来执行。当这个存储过程被调用执行时，这些操作也会同时执行。

3.1.3　数据库的组成

Picture

数据库的组成

SQL Server 2012数据库主要由文件和文件组组成。数据库中的所有数据和对象（如表、存储过程和触发器）都被存储在文件中。

1．文件

文件主要分为以下3种类型。

（1）主要数据文件：存放数据和数据库的初始化信息。每个数据库有且只有一个主要数据文件，默认扩展名是.mdf。

（2）次要数据文件：存放除主要数据文件以外的所有数据文件。有些数据库可能没有次要数据文件，也可能有多个次要数据文件，默认扩展名是.ndf。

（3）事务日志文件：存放用于恢复数据库的所有日志信息。每个数据库至少有一个事务日志文件，也可以有多个事务日志文件，默认扩展名是.ldf。

2．文件组

文件组是SQL Server 2012数据文件的一种逻辑管理单位，它将数据库文件分成不同的文件组，方便于对文件的分配和管理。

文件组主要分为以下两种类型。

（1） 主文件组：包含主要数据文件和任何没有明确指派给其他文件组的文件。系统表的所有页都分配在主文件组中。

（2） 用户定义文件组：主要是在CREATE DATABASE或ALTER DATABASE语句中，使用FILEGROUP关键字指定的文件组。

图像说明文字

每个数据库中都有一个文件组作为默认文件组运行，默认文件组包含在创建时没有指定文件组的所有表和索引的页。在没有指定的情况下，主文件组作为默认文件组。

对文件进行分组时，一定要遵循文件和文件组的设计规则，如下所示。

（1） 文件只能是一个文件组的成员。

（2）文件或文件组不能由一个以上的数据库使用。

（3） 数据和事务日志信息不能属于同一文件或文件组。

（4） 日志文件不能作为文件组的一部分。日志空间与数据空间分开管理。

图像说明文字

系统管理员在进行备份操作时，可以备份或恢复个别的文件或文件组，而不用备份或恢复整个数据库。

3.1.4　系统数据库

Picture

系统数据库

SQL Server 2012的安装程序在安装时默认将建立4个系统数据库（Master、Model、Msdb、Tempdb）。下面分别进行介绍。

1．Master数据库

SQL Server 2012中最重要的数据库。记录SQL Server实例的所有系统级信息，包括实例范围的元数据、端点、链接服务器和系统配置设置。

图像说明文字

SQL Server实例表示是后台进程和数据库文件的集合，一个SQL Server服务器就是一个实例。

2．Tempdb数据库

Tempdb是一个临时数据库，用于保存临时对象或中间结果集。

3．Model数据库

用作SQL Server实例上创建的所有数据库的模板。对Model数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。

4．Msdb数据库

用于SQL Server代理计划警报和作业。

3.2　SQL Server的命名规范

SQL Server2012为了完善数据库的管理机制，设计了严格的命名规则。用户在创建数据库及数据库对象时必须严格遵守SQL Server2012的命名规则。本节将对标识符、对象和实例的命名进行详细的介绍。

3.2.1　标识符

Picture

标识符

在SQL Server2012中，服务器、数据库和数据库对象（如表、视图、列、索引、触发器、过程、约束和规则等）都有标识符，数据库对象的名称被看成是该对象的标识符。大多数对象要求带有标识符，但有些对象（如约束）中标识符是可选项。

对象标识符是在定义对象时创建的，标识符随后用于引用该对象，下面分别对标识符的格式及分类进行介绍。

1．标识符格式

在定义标识符时必须遵守以下规定。

（1）标识符的首字符必须是下列字符之一。

①统一码（Unicode）2.0标准中所定义的字母，包括拉丁字母a~z和A~Z，以及来自其他语言的字符。

②下划线“_”、符号“@”或者数字符号“#”。

在SQL Server2012中，某些处于标识符开始位置的符号具有特殊意义。以“@”符号开始的标识符表示局部变量或参数；以一个数字符号“#”开始的标识符表示临时表或过程，如表“#gzb”就是一张临时表；以双数字符号“##”开始的标识符表示全局临时对象，如表“##gzb”就是全局临时表。

图像说明文字

某些SQL函数的名称以双at符号(@@)开始，为避免混淆这些函数，建议不要使用以@@开始的名称。

（2）标识符的后续字符可以是以下3种。

①统一码（Unicode）2.0标准中所定义的字母。

②来自拉丁字母或其他国家/地区脚本的十进制数字。

③“@”符号、美元符号“$”、数字符号“#”或下划线“_”。

（3）标识符不允许是SQL的保留字。

（4）不允许嵌入空格或其他特殊字符。

例如：为明日科技公司创建一个工资管理系统，可以将其数据库命名为“MR_NXT”。名字除了要遵守命名规则以外，最好还能准确表达数据库的内容，本例中的数据库名称是以每个字的大写首字母命名的，其中还使用了下划线“_”。

2．标识符分类

SQL Server将标识符分为以下两种类型。

（1）常规标识符：符合标识符的格式规则。

（2）分隔标识符：包含在双引号(“”)或者方括号([ ])内的标识符。该标识符可以不符合标识符的格式规则，如[MR GZGLXT]，虽然MR和GZGLXT之间含有空格，但因为使用了方括号，所以视为分隔标识符。

图像说明文字

常规标识符和分隔标识符包含的字符数必须在1～128之间，对于本地临时表，标识符最多可以有116个字符。

3.2.2　对象命名规则

Picture

对象命名规则

SQL Server 2012的数据库对象的名字由1～128个字符组成，不区分大小写。使用标识符也可以作为对象的名称。

在一个数据库中创建了一个数据库对象后，数据库对象的完整名称应该由服务器名、数据库名、拥有者名和对象名4部分组成，其格式如下：

[[[server.][database].][owner_name].]object_name

服务器、数据库和所有者的名称即所谓的对象名称限定符。当引用一个对象时，不需要指定服务器、数据库和所有者，可以利用句号标出它们的位置，从而省略限定符。

对象名的有效格式如下：

server.database.owner_name.object_name 
server.database..object_name 
server..owner_name.object_name 
server...object_name 
database.owner_name.object_name 
database..object_name 
owner_name.object_name 
object_name

指定了4个部分的对象名称被称为完全合法名称。

图像说明文字

不允许存在4部分名称完全相同的数据库对象。在同一个数据库里可以存在两个名为EXAMPLE的表格，但前提是这两个表的拥有者必须不同。

3.2.3　实例命名规则

Picture

实例命名规则

使用SQL Server 2012，可以选择在一台计算机上安装SQL Server的多个实例。SQL Server 2012提供了两种类型的实例——默认实例和命名实例。

1．默认实例

此实例由运行它的计算机的网络名称标识。使用以前版本SQL Server客户端软件的应用程序可以连接到默认实例。SQL Server 6.5版或SQL Server 7.0版服务器可作为默认实例操作。但是，一台计算机上每次只能有一个版本作为默认实例运行。

2．命名实例

计算机可以同时运行任意个SQL Server命名实例。实例通过计算机的网络名称加上实例名称以<计算机名称>\<实例名称>的格式进行标识，即computer_name\instance_name，但该实例名不能超过16个字符。

3.3　数据库操作

3.3.1　创建数据库

Picture

创建数据库

在使用SQL Server创建用户数据库之前，用户必须设计好数据库的名称和它的所有者、空间大小，以及存储信息的文件和文件组。

1．以界面方式创建数据库

下面在SQL Server Management Studio中创建数据库“db_database”，具体操作步骤如下。

（1）启动SQL Server Management Studio，并连接到SQL Server 2012中的数据库。

（2）鼠标右键单击“数据库”选项，在弹出的快捷菜单中选择【新建数据库】命令，如图3-1所示。

Picture182

图3-1　新建数据库

（3）进入“新建数据库”对话框，如图3-2所示。在列表框中填写数据库名“db_database”，单击【确定】按钮，即添加数据库成功。

Picture512

图3-2　创建数据库名称

2．使用CREATE DATABASE语句创建数据库

语法如下：

CREATE DATABASE database_name
[ON
[PRIMARY] [<filespec> [,...n] 
[,<filegroup> [,...n]] 
[LOG ON { <filespec> [,...n] }] 
] 
[COLLATEcollation_name]
[WITH<external_access_option>]
]
[;]
To attach a database
CREATE DATABASE database_name
ON <filespec>[,...n] 
FOR { ATTACH [WITH <service_broker_option>]
| ATTACH_REBUILD_LOG }
[;]
<filespec> ::= 
{
(
NAME = logical_file_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 | %]]
) [,...n]
}
<filegroup> ::= 
{
FILEGROUP filegroup_name[CONTAINS FILESTREAM] [DEFAULT]
<filespec> [,...n]
}
<external_access_option> ::=
{
[DB_CHAINING { ON | OFF }]
[, TRUSTWORTHY { ON | OFF }]
}
<service_broker_option> ::=
{
ENABLE_BROKER
|NEW_BROKER
|ERROR_BROKER_CONVERSATIONS
}
Create a database snapshot
CREATE DATABASE database_snapshot_name
ON 
 (
NAME = logical_file_name,
FILENAME = 'os_file_name' 
) [,...n] 
AS SNAPSHOT OF source_database_name
[;]

参数说明如下。

  • database_name：新数据库的名称。数据库名称在 SQL Server 的实例中必须唯一，并且必须符合标识符规则。

  • ON：指定显式定义用来存储数据库数据部分的磁盘文件（数据文件）。当后面是以逗号分隔的、用以定义主文件组的数据文件的 <filespec> 项列表时，需要使用 ON。主文件组的文件列表可后跟以逗号分隔的、用以定义用户文件组及其文件的 <filegroup> 项列表（可选）。

  • PRIMARY：指定关联的 <filespec> 列表定义主文件。在主文件组的 <filespec> 项中指定的第一个文件将成为主文件。一个数据库只能有一个主文件。有关详细信息，请参阅文件和文件组体系结构。

  • LOG ON：指定显式定义用来存储数据库日志的磁盘文件（日志文件）。LOG ON 后跟以逗号分隔的用以定义日志文件的 <filespec> 项列表。如果没有指定 LOG ON，将自动创建一个日志文件，其大小为该数据库的所有数据文件大小总和的 25% 或 512 KB（取两者之中的较大者）。不能对数据库快照指定 LOG ON。

  • COLLATE：指明数据库使用的校验方式。collation_name可以是Windows的校验方式名称，也可以是SQL校验方式名称。如果省略此子句，则数据库使用当前的SQL Server校验方式。

  • NAME：指定文件在SQL Server中的逻辑名称。当使用FOR ATTACH选项时，就不需要使用NAME选项了。

  • FILENAME：指定文件在操作系统中存储的路径和文件名称。

  • SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQL Server默认其与模板数据库中的主文件大小一致，其他数据库文件和事务日志文件则默认为1MB。指定大小的数字SIZE可以使用KB、MB、GB和TB作为后缀，默认的后缀是MB。SIZE中不能使用小数，其最小值为512KB，默认值是1MB。主文件的SIZE不能小于模板数据库中的主文件。

  • MAXSIZE：指定文件的最大容量。如果没有指定MAXSIZE，则文件可以不断增长直到充满磁盘。

  • UNLIMITED：指明文件无容量限制。

  • FILEGROWTH：指定文件每次增容时增加的容量大小。增加量可以用以KB、MB作后缀的字节数或以%作后缀的被增容文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。

例如：使用命令创建超市管理系统数据库db_supermarket。

运行的结果如图3-3所示。

Picture188

图3-3　创建一个名称为“db_supermarket”的数据库

使用create database 命令创建一个名称是“db_supermarket”的数据库

create database db_supermarket

图像说明文字

在创建数据库时，所要创建的数据库名称必须是系统中不存在的，如果存在相同名称的数据库，在创建数据库时系统将会报错。另外，数据库的名称也可以是中文名称。

3.3.2　修改数据库

Picture

修改数据库

数据库创建完成后，常常需要根据用户环境进行调整，如对数据库的某些参数进行更改，这就需要使用修改数据库的命令。

1．以界面方式修改数据库

下面介绍如何更改数据库“MR_KFGL”的所有者。具体操作步骤如下。

（1）启动SQL Server Management Studio，并连接到SQL Server 2012中的数据库，在“对象资源管理器”中展开“数据库”节点。

（2）鼠标右键单击需要更改的数据库“db_2012”选项，在弹出的快捷菜单中选择【属性】命令，如图3-4所示。

Picture192

图3-4　选择【属性】命令

（3）进入“数据库属性”对话框，如图3-5所示。通过该对话框可以修改数据库的相关选项。

Picture416

图3-5　“数据库属性”对话框

（4）单击“数据库属性”对话框中的“文件”选项卡，然后单击“所有者”后的浏览按钮图像说明文字，弹出“选择数据库所有者”对话框，如图3-6所示。

Picture254

图3-6　“选择数据库所有者”对话框

（5）单击【浏览】按钮，弹出“查找对象”对话框，如图3-7所示。通过该对话框选择匹配对象。

Picture400

图3-7　查找对象对话框

（6）在“匹配的对象”列表框中选择数据库的所有者“sa”选项，单击【确定】按钮，完成数据库所有者的更改操作。

2．使用ALTER DATABASE 语句修改数据库

SQL中修改数据库的命令为ALTER DATABASE。

语法格式如下：

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>
}

参数说明如下。

  • ADD FILE：指定要添加的数据库文件。

  • TO FILEGROUP：指定要添加文件到哪个文件组。

  • ADD LOG FILE：指定要添加的事务日志文件。

  • REMOVE FILE：从数据库中删除文件组并删除该文件组中的所有文件。只有在文件组为空时才能删除。

  • ADD FILEGROUP：指定要添加的文件组。

  • REMOVE FILEGROUP：从数据库中删除指定文件组的定义，并且删除其包含的所有数据库文件。文件组只有为空时才能被删除。

  • MODIFY FILE：修改指定文件的文件名、容量大小、最大容量、文件增容方式等属性，但一次只能修改一个文件的一个属性。使用此选项时应注意，在文件格式filespec中必须用NAME明确指定文件名称，如果文件大小是已经确定的，那么新定义的SIZE必须比当前的文件容量大；FILENAME只能指定在tempdbdatabase中存在的文件，并且新的文件名只有在SQL Server重新启动后才发生作用。

  • MODIFY FILEGROUP<filegroup_name><filegroup_property>：修改文件组属性，其中属性“filegroup_property”的取值可以为READONLY，表示指定文件组为只读，要注意的是主文件组不能指定为只读，只有对数据库有独占访问权限的用户才可以将一个文件组标志为只读；取值为READWRITE，表示使文件组为可读写，只有对数据库有独占访问权限的用户才可以将一个文件组标志为可读写；取值为DEFAULT，表示指定文件组为默认文件组，一个数据库中只能有一个默认文件组。

  • SET：设置数据库属性。

【例3-1】将一个大小为10MB的数据文件mrkj添加到MingRi数据库中，该数据文件的大小为10MB，最大的文件大小为100MB，增长速度为2MB，MingRi数据库的物理地址为D盘文件夹下。 SQL语句如下。

ALTER DATABASE Mingri
ADD FILE
(
NAME=mrkj,
Filename='D:\mrkj.ndf',
size=10MB,
Maxsize=100MB,
Filegrowth=2MB
)

3.3.3　删除数据库

Picture

删除数据库

使用DROP DATABASE命令可以删除一个或多个数据库。当某一个数据库被删除后，这个数据库的所有对象和数据都将被删除，所有日志文件和数据文件也都将删除，所占用的空间将会释放给操作系统。

1．以界面方式删除数据库

下面介绍如何删除数据库“MingRi”。具体操作步骤如下。

（1）启动SQL Server Management Studio，并连接到SQL Server 2012中的数据库。在“对象资源管理器”中展开“数据库”节点。

（2）鼠标右键单击要删除的数据库“MingRi”选项，在弹出的快捷菜单中选择【删除】命令。如图3-8所示。

Picture234

图3-8　删除数据库

Picture374

图3-9　删除对象

（3）在弹出的“删除对象”对话框中单击【确定】按钮，即可删除数据库，如图3-9所示。

图像说明文字

系统数据库（msdb、model、master、tempdb）无法删除。删除数据库后应立即备份master数据库，因为删除数据库将更新master数据库中的信息。

2．使用DROP DATABASE语句删除数据库

语法格式如下：

DROP DATABASE database_name [,...n]

其中database_name是要删除的数据库名称。

图像说明文字

使用DROP DATABASE命令删除数据库时，系统中必须存在所要删除的数据库，否则系统将会出现错误。

另外，如果删除正在使用的数据库，系统将会出现错误。

例如：不能在“学生档案管理”数据库中删除“学生档案管理”数据库，SQL代码如下：

Use 学生档案管理  --使用学生档案管理数据库
Drop database 学生档案管理 --删除正在使用的数据库

图像说明文字

上面的SQL代码中使用了Use指令，该指令用来指定要使用的数据库，例如“Use 学生档案管理”表示使用“学生档案管理”数据库。Use指令的基本语法如下：

Use {数据库}

删除学生档案管理数据库的操作没有成功，系统会报错，运行结果如图3-10所示。

在“学生档案管理”数据库中，使用DROP DATABASE命令删除名为“学生档案管理”的数据库。

Picture265

图3-10　删除正在使用的数据库，系统会报错的效果图

在查询分析器中的运行的结果如图3-11所示。

Picture385

图3-11　删除“学生档案管理”数据库

小　结

本章介绍了SQL Server 2012数据库的组成、创建和管理的方法，以及如何查看数据库信息。读者不仅可以使用SQL Server 2012界面方式完成创建和管理数据库的工作，还可以调用SQL语句完成对应操作。

习　题

3-1　数据库的常用对象有哪些？

3-2　下面哪些是系统数据库？

（1）Master数据库　　（2）Tempdb数据库

（3）Model数据库　　（4）msdb数据库

（5）Mssqlsystemresource数据库　　（6）my_db数据库

3-3　通过Transact-SQL，使用什么命令创建数据库？使用什么命令修改数据库？使用什么命令删除数据库？使用什么命令查看数据库参数？

目录

相关技术

人邮微信
本地服务
人邮微信
教师服务
二维码
读者服务
读者服务
返回顶部
返回顶部