补充：数据库的管理

1、 第3章 SQL Server 数据库的管理本章内容 3.1 SQL Server 2014数据库概述 3.2 SQL Server数据库的管理3.1 SQL Server 2014 数据库概述一、数据库的存储结构 数据库的逻辑组件数据库的逻辑组件(数据库对象数据库对象) Sales数据库数据库SQL Server表表视图视图数据库的物理实现数据库的物理实现(数据库文件数据库文件)SalesDat1.mdfSalesDat2.ndfSalesLog1.ldf主数据文件主数据文件次数据文件次数据文件日志文件日志文件索引索引存储过程存储过程用户视图用户视图物理视图物理视图图图3-1 数据库的用户视

2、图和物理视图数据库的用户视图和物理视图1. SQL Server中的数据库对象 SQL Server提供了很多逻辑组件，这些逻辑组件通常被称为数据库对象。 对象作用表数据库中数据的实际存放处所。视图定制复杂或常用的查询，以便用户使用；限定用户只能查看表中的特定行或列；为用户提供统计数据而不展示细节。索引加快从表或视图中检索数据的效率。存储过程提高性能；封装数据库的部分或全部细节；帮助在不同的数据库应用程序之间实现一致的逻辑。约束、规则、默认值和触发器确保数据库的数据完整性；强制执行业务规则。登录、用户、角色和组保障数据安全的基础。SQL Server中常用的数据库对象及作用2、数据库对象的标识

3、符 例如数据库名、表名、视图名、列名等。SQL Server标识符的命名遵循以下规则：(1)标识符包含的字符数必须在1-128之间。(2)标识符的第一个字符必须是字母、下划线(_)、at符号()或者数字符号(#)。(3)标识符的后续字符可以为字母、数字或“”符号、“$”符号、数字符号或下划线。 (4)标识符不能是Transact-SQL的保留字，也不能包含空格。 二、数据库所有者和架构 数据库所有者（DBO，Data Base Owner）：就是有权限访问数据库的用户，即登录数据库的网络用户。 是唯一的，拥有该数据库中的全部权限，并能够提供给其他用户访问权限和功能。二、数据库所有者和架构 架构

4、：将数据库中的所有对象分成不同的集合，每一个集合就称为一个架构。 数据库中的每一个用户都会有自己的默认架构，可以在建库时设定，系统默认架构为dbo。 数据库用户只能对属于自己架构中的数据库对象执行相应的数据操作。操作的权限则由数据库角色决定。三、 数据库文件行数据文件行数据文件*.mdf仅有一个仅有一个事务日志文件事务日志文件*.ldf一到多个一到多个 次数据文件次数据文件*.ndf零到多个零到多个次数据文件次数据文件*.ndf零到多个零到多个(仅有一个仅有一个)主文件组主文件组(零到多个零到多个)次文件组次文件组一到多个一到多个事务日志事务日志一个数据库的文件集一个数据库的文件集图图3-2

5、数据库的文件组成数据库的文件组成(1)数据库文件 行文件：.mdf,包含数据库的启动信息、数据信息唯一的 事务日志：.ldf,包含恢复数据库的所有日志信息至少有一个，最小为 512 KB 次要文件：.ndf,主文件中不包括的所有数据信息0个、一个或多个(2)文件组 SQL Server允许对文件进行分组，以便于管理和数据的分配放置。 文件组是将多个数据库文件集合起来形成的一个整体。每个文件组有一个组名。 数据库文件组分为主文件组（primary）、自定义文件组（user_defined）和默认文件组（default）三类。u主文件组（primary） ： 所有系统表、主要数据文件.mdf和未放

6、入其他文件组的所有次要文件都被分配到主文件组中。一个数据库有1个主文件组。u用户定义文件组（user_defined） ：是用户首次创建数据库时，或修改数据库时自定义的，其目的在于数据分配，以提高表的读写效率。u默认文件组（default）:每个数据库中均有一个文件组被指定为默认文件组.如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。在使用文件组时，应当注意以下几个准则： 文件或文件组不能由一个以上的数据库使用。 文件只能是一个文件组的成员。 数据和事务日志信息不能属于同一文件或文件组。 事务日志文件不能属于任何文件组。文件组的使用场合当有多个磁盘，希望把文件分布在

7、这些磁盘上以提高性能。13Student数据库Student_1.mdfStudent_2.ndfStudent_3.ndfStudent_log.ldfC盘d盘e盘f盘primaryStudgrpStudgrp 文件组的作用： 当对数据库对象写操作时,数据库会根据组内数据文件的大小,按比例写入组内所有数据文件中. 当查询数据时,系统会创建多个单独的线程来并行读取分配在不同物理磁盘上的每个文件, 提高了查询速度.14四、数据库分类151、系统数据库master数据库：记录系统的所有系统级的信息model数据库：模板数据库msdb数据库：用来存储计划信息、备份和恢复相关信息、SQL server

8、代理程序调度报警和作业调度等操作。tempdb数据库：临时数据库，它为所有的临时表、临时存储过程及其他临时操作提供存储空间。resource数据库：隐藏的只读数据库，包括SQL server 2014中的所有系统对象，但不含用户数据或用户元数据。此文件位于2.示例数据库 AdventureWorks/AdventureWorks DW是SQL Server 中的示例数据库（如果在安装过程中选择安装了的话）。此数据库包含 Adventure Works 虚购的公司及其分公司的财务数据和自行车销售数据，以简单、易于理解的方式来展示SQL Server 的功能。3.用户数据库 用户根据数据库设计创建

9、的数据库。如教学管理数据库（jxgl），图书管理数据库（Library）。一、创建数据库先规划数据库，规划数据库需要考虑的问题： 数据库名称、数据库所有者； 数据文件和事务日志文件的逻辑名、物理名、初始大小、增长方式和最大容量； 数据库用户数量和用户权限； 数据库大小与硬件配置的平衡、是否使用文件组； 数据库的备份与恢复。3.2 SQL Server 数据库的管理【例3-1】在D:sqllx目录下创建一个jxgl数据库，该数据库的主数据文件逻辑名称为jxgl，物理文件名为teching.mdf，初始大小为5MB，最大为20 MB，增长速度为20%；数据库的日志文件逻辑名称为jxgl_log，物

10、理文件名为teching_log.ldf，初始大小为2MB，最大为10MB，增长速度为1MB。1、使用对象资源管理器 打开“SQL Server Management Studio”窗口，右击“对象资源管理器”窗格中的“数据库”结点，在弹出菜单中选择“新建数据库”命令，如图3-3所示。3-3 “新建数据库新建数据库”命令命令 打开如图3-4所示的“新建数据库”对话框，它由“常规”、“选项”和“文件组”三个选项组成。 在各个选项中，可以指定它们的参数值，例如，在“常规”选项中，可以指定数据库名称、数据库的逻辑名、文件组、初始容量、增长方式和文件存储路径等。图图3-4 “新建数据库新建数据库”对话

11、框对话框 数据库名称：在“数据库名称”文本框中，输入数据库名称，【例3-1】中要求为jxgl，在应用程序中则可以通过jxgl来访问和操作数据库。 逻辑名称：数据库创建以后，T-SQL语句是通过引用逻辑名称来实现对数据库操作的。其默认值与数据库名相同，但可以更改，每个逻辑名称是唯一的，与物理文件名称相对应。【例3-1】中要求为jxgl、jxgl_log。 物理文件名称：用于存储数据库中数据的物理文件的名称。【例3-1】中要求为teching.mdf、teching_log.ldf。 文件类型：该项用于标识数据库文件的类型，表明该文件是数据文件还是日志文件。 文件组：表示数据文件隶属于哪个文件组，

12、创建后不能更改。文件组仅适用于数据文件，而不适用于日志文件。所有数据库都必须有一个主文件组。 初始大小：该项表示对应数据库文件所占的磁盘空间的大小，单位为MB，默认为5（数据库的初始大小至少是MODEL数据库的大小），日志文件为1。 自动增长：数据文件的大小能够自动增长。设置方式如图3-5所示。 路径：数据库文件存放的物理位置，默认的路径是C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATA 。 单击右边带有省略号（）的命令按钮，打开一个资源管理器风格的对话框，可以在该对话框中更改数据库文件的位置。图图3-5 自动增长自

13、动增长图图3-6 新创建的新创建的jxgl数据库数据库单击“确定”按钮，在“数据库”的树形结构中，就可看到刚创建的jxgl数据库，如图3-6所示。其中：database_name是新数据库的名称。数据库名称在 SQL Server 的实例中必须惟一，并且必须符合标识符规则。最长为128个字符。每个服务器管理的数据库最多为32767个。ON：指定显式定义用来存储数据库数据部分的磁盘文件(数据文件)。PRIMARY：指定关联的 列表定义主文件。LOG ON ：指定显式定义用来存储数据库日志的磁盘文件(日志文件)。2、使用T-SQL语句 命令格式：CREATE DATABASE database_n

14、ame ON PRIMARY ,n , ,n LOG ON ,n COLLATE collation_nameFOR ATTACH ：控制文件属性。 NAME=logical_file_name：指定文件的逻辑名称。 FILENAME= os_file_name ：指定操作系统使用的路径和文件名.该操作系统文件名和NAME的逻辑名称一一对应。 SIZE size：指定文件的初始大小。至少为模板Model数据库大小，Model数据库的主数据文件和日志文件的大小分别为3M和1M。 MAXSIZE max_size：指定文件可增大到的最大大小。 UNLIMITED：指定文件将增长到磁盘已满。 FIL

15、EGROWTH growth_increment：指定需要新空间时为文件添加的空间量。该参数不能超过MAXSIZE的值。当指定数据为0时，表示文件不增长。增加量可为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。 默认单位为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。28 ：控制文件组属性。不能对数据库快照指定文件组。 FILEGROUP filegroup_name：文件组的逻辑名称. filegroup_name 必须在数据库中惟一，不能是系统提供的名称 PRIMARY 和 PRIMARY_LOG。 DEFAULT：指定命名文

16、件组为数据库中的默认文件组。关于T-SQL语句格式约定： SQL语句不区分大小写，为了清晰，一般都用大写表示系统保留字，用小写表示用户自定义的名称。 “”表示该项可省略，省略时各参数取默认值； a|b表示a或b任选其一。 “，n”表示大括号括起来的内容可以重复写多次。 【例3-2】用代码实现，在D:sqllx目录下创建一个jxgl数据库，该数据库的主数据文件逻辑名称为jxgl，物理文件名为teching.mdf，初始大小为5MB，最大为20 MB，增长速度为20%；数据库的日志文件逻辑名称为jxgl _log，物理文件名为teching _log.ldf，初始大小为2MB，最大尺寸为10MB，

17、增长速度为1MB。CREATE DATABASE jxgl ON PRIMARY ( NAME=jxgl, FILENAME=D:sqllxteching.mdf, SIZE=5MB, MAXSIZE=20MB, FILEGROWTH=20%) LOG ON ( NAME=jxgl _log, FILENAME=D:sqllxteching_log.ldf, SIZE=2MB, MAXSIZE=10MB, FILEGROWTH=1MB )【补例2】 最简形式的创建数据库(不指定文件)语句。CREATE DATABASE sales补例3 不指定SIZE创建数据库。CREATE DATABASE

18、 sales2ON ( NAME=Sales2_dat, FILENAME=E:DataBasesales2.mdf)二、查看数据库信息 1.使用对象资源管理器 在“对象资源管理器”窗格中展开“数据库”结点，右击所要查看信息的数据库，在弹出的快捷菜单中选择“属性”命令，出现如图3-7所示的数据库属性设置对话框。图图3-7 数据库属性对话框数据库属性对话框2. 使用T-SQL语句语法格式为：sp_helpdb database_name 【例3-4】查看jxgl数据库的基本信息。代码如下：EXEC sp_helpdb jxgl如图3-8所示,显示了jxgl数据库的所有者、状态、创建时间、文件尺寸

19、、文件增长属性等数据库信息。 ：图图3-8数据库数据库jxgl文件的基本信息文件的基本信息1、 使用 “对象资源管理器” 展开“数据库”结点，选中要打开的数据库，右键，在出现的菜单中可以针对当前的数据库进行相关操作，如图3-9所示。图图3-9 在在“对象资源管理器对象资源管理器”窗格中打开数据库窗格中打开数据库三、打开或切换数据库2. 使用T-SQL语句语法格式为： USE database_name例：use jxgl也可以直接通过数据库下拉列表打开并切换，在查询分析器中打开或切换数据库，如图3-10所示。图3-10 在“查询分析器”窗格中切换数据库 四、修改数据库1、 使用 “对象资源管理

20、器” 选中要修改的数据库，右键，在弹出菜单中选择“属性”，选择属性中的“文件”选项，可以修改“初始大小”、“自动增长”中的“文件增长”和“最大文件大小”选项。如图3-11所示。重新设定的数据库分配空间必须大于现有空间。3738图3-11 修改数据库2、使用T-SQL语句修改数据库命令格式：ALTER DATABASE database_nameADD FILE . TO FILEGROUP 文件组名 /*在文件组中增加数据文件*/ | ADD LOG FILE . /*增加日志文件*/ | REMOVE FILE 逻辑文件名 /*删除数据文件*/ | ADD FILEGROUP 文件组名 /*

21、增加文件组*/ | REMOVE FILEGROUP 文件组名 /*删除文件组*/ | MODIFY FILE /*更改文件属性*/ | MODIFY NAME = 新数据库名 /*数据库更名*/ | MODIFY FILEGROUP 文件组名 | DEFAULT| NAME = 新文件组名 /*更改文件组属性*/ | SET . WITH /*设置数据库属性*/ | COLLATE 排序名 /*指定数据库排序规则*/39【例3-5】将两个数据文件和一个事务日志文件添加到test数据库中。代码如下：ALTER DATABASE test ADD FILE -添加两个次数据文件(NAME=test1,FILENAME=d:sqllxtest1.ndf,SIZE=5MB, MAXSIZE=100MB, FILEGROWTH=5MB),(NAME=test2, FILENAME=d:sqllxtest2.ndf, SIZE=5MB, MAXSIZE=10MB, FILEGROWTH=1MB)40GOALTER DATABASE testADD LOGFILE (NAME=testlog1, -添加一个次日志文件FILENAME=d:sqllxtestlog1.ldf, SIZE=5MB, MAXSIZE=100MB,