第03章 创建与管理数据库

  1. 数据库的基本概念
    数据库的逻辑结构: SQL Server的数据库逻辑结构可以理解为在运行SQL Server软件中观察到的数据库组成。例如，展开SQL Server 2016的本书示例数据库teaching，如图3-1所示。
    server.database.schema.object
    --即服务器.数据库.架构.数据库对象

  2. 数据库的基本概念

基本概念

SQL Server 2016将数据库映射为一组磁盘文件，并将数据与日志信息分别保存于不同的磁盘文件中，每个文件仅在与之相关的数据库中使用。
因此，
从物理角度看，
数据库包括数据文件和日志文件。

从逻辑角度看，数据库中的表、索引、触发器、视图、键、约束、默认值、规则、用户定义数据类型或存储过程及数据库本身，都可以理解为数据库对象。

数据库的结构层次

  1. 以内模式为框架所组成的数据库叫作物理数据库，
  2. 以概念模式为框架所组成的数据库叫作概念数据库
  3. 以外模式为框架所组成的数据库叫作用户数据库。

物理数据库
是数据库的最内层，是物理存储设备上实际存储的数据的集合。这些数据是原始数据，是用户加工的对象，由内部模式描述的指令操作处理的位串、字符和字组成。
在SQL Server中就是存储文件，即由操作系统管理的数据文件和日志文件。

概念数据库
是数据库的中间一层，是数据库的整体逻辑表示。概念数据库指出了每个数据的逻辑定义及数据间的逻辑联系，是存储记录的集合，涉及的是数据库所有对象的逻辑关系，而不是它们的物理情况，是数据库管理员概念下的数据库。
在SQL Server中表现为由数据行和列组成的基本表。

用户数据库
是用户所看到和使用的数据库，表示了一个或一些特定用户使用的数据集合。数据库不同层次之间的联系是通过映射进行转换的。
在SQL Server中表现为视图、报表和查询结果集等。

架构

：架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其内部的每个元素的名称都是唯一的。在 SQL Server 2016 中的默认架构是DBO。如果用户创建数据库时没有指定架构，系统将使用默认架构。可以展开一个数据库中的“安全性架构”文件夹，就可以观察到系统架构列表。

数据库所有者

：数据库所有者（DBO）就是有权限访问数据库的用户，即登录数据库的网络用户。数据库所有者是唯一的，拥有该数据库中的全部权限，并能够提供给其他用户访问权限和功能。

数据库的物理文件

每个 SQL Server 2016 数据库至少具有两个操作系统文件：

  1. 一个主数据文件
    数据文件包含数据和数据库对象，

  2. 一个日志文件
    日志文件包含恢复数据库中的所有事务所需的信息。

SQL Server 2016 数据库具有3种类型的文件：
1.主数据文件
包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件，建议文件扩展名是 .mdf。

2.次要数据文件（可选的）
由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上，建议文件扩展名是 .ndf。

3.事务日志文件
保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件，建议文件扩展名是 .ldf。

默认情况下，数据和事务日志被放在同一个驱动器的同一个路径下。这是为处理单磁盘系统而采用的方法。但是，在生产环境中，建议将数据和日志文件放在不同的磁盘上。

数据库的常用对象

表：
数据库中的表与日常生活中使用的表格类似，由行（Row）和列（Column）组成。
其中，列由同类的信息组成，每列又称为一个字段，每列的标题称为字段名。行包括若干列的信息项。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表中通常都有一个主关键字，用于唯一地确定一条记录。
索引：
索引是根据指定的数据库表列建立起来的顺序。它提供了快速查询大量数据的方法。有的索引还可以限制表，使其指定的列数据不重复。
视图
是一个虚拟的表，在数据库中并不实际存在。视图是由查询数据表产生的。
作用：

  • 可以用来控制用户对数据的访问，并能简化数据的显示，
  • 提高数据的安全性管理水平。

数据库关系图：
是本数据库中的表之间的关系示意图，利用图表可以编辑表与表之间的关系，以及表的行列属性。

默认值（逐渐淘汰）：
默认值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。

规则（逐渐淘汰）：
是对数据库表中数据信息的限制，其限定的是表的列。
存储过程：
是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。
（在SQL中，没有返回值的函数）

触发器:
是一个用户定义的SQL 事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。

用户
所谓用户，就是有权限访问数据库的使用者，同时需要自己登录账号和密码。
一般来说，数据库用户分为管理员用户和普通用户，前者可对数据库进行修改删除，后者只能进行阅读、查看等操作。

注意：
除了如上列出的数据库对象之外，不同的数据库管理系统也有部分自定义的对象，将在具体学习中分别介绍，此处不再赘述。

数据库的存储

SQL Server 2016数据库是以文件的方式存储到磁盘中，其中数据文件和日志文件的文件结构不同，存储方式也不一样，如图：
image

数据文件的存储结构： 从SQL Server 2016数据库的物理架构上来看，

  • SQL Server用于存储数据的基本单位是页，每页容量为8KB。SQL Server 2016在执行底层的磁盘I/O时也是以页级为单位的。
    SQL Server将8个物理上连续的页组成一个区，以此可以更加有效地管理数据页。

数据页 SQL Server将8KB的数据划分为一页。即在SQL Server 数据库中的1MB数据中包含128页。包括数据页、索引页、文本/图像页等8种。

每个页的开头为96字节的系统信息。数据区占有8060个字节，页尾的行偏移数组占有36个字节。

扩展盘区是SQL Server数据库读写数据的基本单位，扩展盘区就是管理存储空间的基本单位。
一个扩展盘区由8个物理上连续的页（64 KB）组成。即SQL Server数据库中每1MB包含16个区。

为了提高空间利用率，SQL Server2016在为数据库中的某个数据表分配存储区时采取两种不同的策略。

  1. 将扩展盘区中所有8个存储页全部分配给一个数据库对象（例如数据表）。
    采用这种方法分配的区也被称为“统一区”。统一区中的所有8个存储页只能供所属对象使用。

  2. 允许扩展盘区中的存储页由1~8个数据对象共同使用。这种分区方式也被称为“混合区”。
    采用这种方式的分区，区中的每1页（共8页）都可由不同的对象拥有。

系统数据库

系统数据库是存储SQL Server系统的信息数据库，能够实现系统配置、数据库属性、账户登录、数据库文件、数据库备份、警报、作业的设置和管理。

  1. master数据库 是SQL Server系统最重要的数据库，它记录了SQL Server系统的所有系统信息。
    master 数据库还记录了所有其他数据库的存在、数据库文件的位置以及 SQL Server 的初始化信息。

  2. model数据库 用于在 SQL Server 实例上创建所有数据库的存储新数据库结构特性的模板。
    model数据库包含了建立新数据库时所需的基本对象。在系统执行建立新数据库操作时，它会复制这个模板数据库的内容到新的数据库上。

  3. msdb数据库 该数据库是代理服务数据库，为其报警、任务调度和记录操作员的操作提供存储空间。
    如果不使用这些SQL Server代理服务，就不会使用到该系统数据库。

  4. resource（资源）数据库 是隐形只读数据库，它包含了SQL Server 2016中的所有系统对象，
    在逻辑上，系统对象出现在每个数据库的sys架构中，资源系统数据库不包含用户数据或用户元数据。
    资源系统数据库的物理文件名是mssqlsystemresource.mdf。

  5. tempdb数据库 该数据库是一个为所有的临时表、临时存储过程及其他临时操作提供存储空间的临时数据库。

用户数据库创建与修改

一个 SQL Server 实例，可以创建32767个用户数据库。在创建数据库之前，首先用户应该清楚是否有相关的权限。

要创建数据库，必须至少拥有 CREATE DATABASE、CREATE ANY DATABASE 或 ALTER ANY DATABASE 等语句的权限。其次，创建数据库的用户将是该数据库的所有者。

1. 用户数据库的创建

在SQL Server 2016中，用户要创建数据库，必须确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。
数据库名称必须遵循为标识符指定的规则。这些规则主要包括如下几点：

  1. 数据库名称长度为1~128个字符。
  2. 名称首字符必须是一个英文字母或“_ ”、“#”和“@”中的任意字符。
  3. 在中文版SQL Server 2016中，可以直接使用汉字为数据库命名。
  4. 名称中不能出现空格，不允许使用SQL Server 2016的保留字。

文件组
每个SQL Server数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。

使用SSMS

在SQL Server Management Studio中创建数据库
（1）启动SSMS，在对象资源管理器中，右击“数据库”选项，在弹出的快捷菜单中选择“新建数据库”命令，打开“新建数据库”窗口。在“新建数据库”窗口中的“常规”选项卡，如图
image

① 在“数据库名称”文本框中，输入数据库名称test01。
② 若要通过接受所有的默认值来创建数据库，则单击“确定”按钮；否则，继续后面的可选项目的选择。
③ 若要更改所有者名称，单击“所有者”后的...按钮选择其他所有者。

④ 若要启用数据库的全文搜索，选中“使用全文索引”复选框。
⑤ 若要更改主数据文件和事务日志文件的默认值，在“数据库文件”列表中单击相应的单元格，并输入新值。各项的具体含义如下：

逻辑名称   文件类型    文件组    初始大小
自动增长    位置     路径    文件名

image
切换到“文件组”选项卡进行设置，如图3-6所示。

（3）如果要添加文件组，可以单击“添加”按钮，然后输入文件组的名称。
（4）如果单击“脚本”按钮，系统还会在查询窗口自动生成创建数据库test01命令代码，如果执行此代码，系统也会创建数据库test01。

作用：方便复制数据库。

（5）所有参数设置完毕后，单击“确定”按钮，新的数据库就创建成功。

展开对象资源管理器中的数据库文件夹，就可以观察到test01数据库已经创建成功。

SQL语句CREATE DATABASE

利用Transact-SQL语句创建数据库
（1）CREATE DATABASE语句的基本格式

CREATE  DATABASE  database_name                --设置数据库名称
[ ON   [ PRIMARY ] 
  [ <filespec> [ ,...n ]                  --设置数据文件
  [ , <filegroup> [ ,...n ] ]                               --设置文件组
  [ LOG ON { <filespec> [ ,...n ] } ]         --设置日志文件
  [ COLLATE collation_name ]                   --设置排序规则名称
  [ WITH  <external_access_option> ]          --设置外部访问
] [;]

例：
【例3.1】 创建数据库student，并指定数据库的数据文件所在位置、初始容量、最大容量和文件增长量。

CREATE DATABASE   student   ON
(   NAME='student',
  FILENAME = >'D:\sqlprogram\student.mdf',
  SIZE = 5MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 5%)
GO

【例3.2】 创建数据库teaching，并指定数据库的数据文件和日志文件的所在位置、初始容量、最大容量和文件增长量。

CREATE DATABASE teaching
ON  PRIMARY 
( NAME = 'teaching', 
FILENAME = 'D:\sqlprogram\teaching.mdf',
SIZE = 6MB, 
MAXSIZE = 30MB, 
FILEGROWTH = 1MB ) 
LOG ON 
( NAME = 'teaching_log',
 FILENAME = 'D:\sqlprogram\teaching_log.ldf',
 SIZE = 2MB ,
 MAXSIZE = 10 MB, 
 FILEGROWTH = 10%)
 COLLATE Chinese_PRC_CI_AS
GO

2. 修改数据库

使用SSMS

使用SQL Server Management Studio修改数据库，其主要步骤如下：
启动SQL Server Management Studio，在对象资源管理器中，用户可以右击所选择的数据库 test01，在弹出的快捷菜单中选择“属性”命令打开“数据库属性”窗口。

（1）在“数据库属性”窗口的“常规”选项卡中，显示的是数据库的基本信息，这些信息不能修改。
image

（2）单击“文件”选项卡，如图
image
可以修改数据库的逻辑名称、初始大小、自动增长等属性，也可以根据需要添加数据文件和日志文件，还可以更改数据库的所有者。

（3）在“文件组”选项卡中，可以修改现有的文件组，也可以指定数据库的默认文件组、添加新文件组。
image

（4）在“选项”选项卡中，修改数据库的排序规则。
“数据库属性”窗口包含的各种属性，只要需要，就可以选择相应的选项卡来修改
image

SQL语句ALTER DATABASE

（1）ALTER DATABASE语句的语法

ALTER DATABASE database_name    --需修改的数据库名
{  <add_or_modify_files>                 --增加或修改数据库文件
  | <add_or_modify_filegroups>     --增加或修改数据库文件组
  | <set_database_options>                --设置数据库选项
  | MODIFY NAME = new_database_name       --数据库重命名 
  | COLLATE collation_name              --更改排序规则
} [;]

【例3.3】 为student数据库增加一个日志文件。

ALTER DATABASE student
ADD LOG FILE
(  NAME= stud_log,
  >FILENAME='D:\sqlprogram\stud_log.LDF',
  SIZE=2 MB,
 MAXSIZE=6 MB,
  FILEGROWTH=1MB  )
GO

【例3.4】修改student数据库的排序规则。

ALTER DATABASE student
COLLATE Chinese_PRC_CI_AS_KS

数据库的属性设置

修改数据库的排序规则
前面的例题3.4是利用命令方式更改数据库的排序规则，下面介绍如何利用可视化方式修改排序规则。
同样是在图3-16所示的“选项”选项卡内，利用“排序规则”下拉框，可以设置数据库采用的排序规则，如图3-17所示。

image

排序规则的含义
SQL Server 的排序规则指定了

  1. 字符的物理存储模式
  2. 存储和比较字符的规则

以Chinese_PRC_CS_AI_WS为例，该规则可以分成两部分来理解。前半部分指排序规则所支持的字符集。
如Chinese_PRC_表示对简体字UNICODE的排序规则

文件组

概述

文件组是指将数据库相关的一组磁盘文件组成的集合。
为便于分配和管理，可以将数据库对象和文件一起分成文件组。

注意：
SQL Server 2016在创建数据库时会自动创建一个的主文件组，用户也可根据自己的需要自定义一个文件组。

分类：

  • 主文件组： 主文件组包含主数据文件和任何没有明确分配给其他文件组的其他文件。系统表的所有页均分配在主文件组中。

  • 用户定义文件组：用户定义文件组是通过在 CREATE DATABASE 或 ALTER DATABASE 语句中使用 FILEGROUP 关键字指定的任何文件组。

注意：
一个文件不可以是多个文件组的成员。 表、索引和大型对象数据可以与指定的文件组相关联，它们的所有页将被分配到该文件组。

创建文件组的必要性

  1. 对于大型数据库，如果硬件设置上需要多个磁盘驱动器，就可以把特定的对象或文件分配到不同的磁盘上，将数据库文件组织成用户文件组。
  2. 文件组可以帮助数据库管理人员执行相应的数据布局，以及某些管理任务。

例如，在数据库的备份和恢复过程中，系统管理员可以通过备份和恢复独立的文件组或文件代替整个数据库的备份和恢复，这也是需要具有有效备份和恢复策略的大型数据库的必备选择。

  1. 利用文件组，可以在特定的文件中定位特定的对象，从而将频繁查询和频繁修改的文件分离出来，以提高磁盘驱动器的效率，减少磁盘驱动器的争用。

  2. 通过创建用户文件组，可以将数据文件集合起来，以便于管理、数据分配和放置。

创建用户文件组

每个数据库有一个主要文件组，此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。所有系统表都被分配到主要文件组中。用户定义文件组是用户首次创建数据库或以后修改数据库时明确创建的任何文件组。
如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。一个数据库只能将一个文件组指定为默认文件组。

用户自定义文件组的两种方法。

  1. 在SSMS中创建用户文件组。
    使用相应的Transact-SQL命令。

SQL语句

使用Transact-SQL同样可以实现创建文件组的功能。在查询编辑器窗口中输入下面的Transact-SQL脚本，同样可以创建一个名为userdefined02的文件组。

USE test01
GO
ALTER DATABASE  test01
ADD FILEGROUP userdefined02
GO

查看数据库状态信息

在实际生产过程中的数据库总是处于一个特定的状态中.
若要确认数据库的当前状态，

  1. 通过“数据库属性”窗口的“常规”选项卡查看数据库属性.

  2. 选择 sys.databases 目录视图中的 state_desc 列。在查询设计器窗口中输入如下代码并执行，如图3-14所示。

        Select     name,state,state_desc    From    sys.databases

在 SQL Server 2016 中，数据库文件的状态独立于数据库的状态。文件始终处于一个特定状态，若要查看文件的当前状态，请使用 sys.master_files 或 sys.database_files 目录视图。

如果数据库处于离线状态，则可以从 sys.master_files 目录视图中查看文件的状态。如图3-15所示。
可以在查询设计器窗口中输入如下代码并执行，即可查看到相关数据文件的状态信息。

Select name,physical_name,type,type_desc,state, state_desc
From sys.master_files

数据库状态含义

  • ONLINE表示可以对数据库进行访问。即使可能尚未完成恢复的撤销阶段，主文件组仍处于在线状态。

  • OFFLINE表示数据库无法使用。数据库由于显式的用户操作而处于离线状态，并保持离线状态直至执行了其他的用户操作。

  • RESTORING表示正在还原主文件组的一个或多个文件，或正在离线还原一个或多个辅助文件，此时数据库不可用

  • RECOVERING表示正在恢复数据库。恢复进程是一个暂时性状态，恢复成功后数据库将自动处于在线状态。

其他的还有

RECOVERY PENDING表示SQL Server 在恢复过程中遇到了与资源相关的错误。此时数据库不可用。
SUSPECT表示至少主文件组可疑或可能已损坏。在 SQL Server 启动过程中无法恢复数据库。
EMERGENCY表示用户更改了数据库，并将其状态设置为 EMERGENCY。数据库处于单用户模式，可以修复或还原。数据库标记为 READ_ONLY，禁用日志行，并且仅限 sysadmin 固定服务器角色的成员访问。EMERGENCY 主要用于故障排除。

posted @ 2022-10-26 08:38  kingwzun  阅读(177)  评论(0)  编辑  收藏  举报