第 3 章创建和使用数据库

第3章创建和使用数据库 学习导读本章主要介绍数据库的创建和管理方法。通过本章的学习，应掌握以下内容： • 查看数据库各种对象的方法 • 创建数据库的方法 • 删除数据库的方法 • 数据库更名、修改大小的方法

第3章创建和使用数据库 SQL Server的数据库不只是存储数据，还存储与数据处理操作相关的信息。SQL Server的数据库由关系图、表、视图、索引、存储过程、触发器、用户、角色、规则、默认、用户自定义数据类型和用户自定义函数等组成。本章主要介绍使用企业管理器查看数据库属性、创建和删除数据库等内容。

第3章创建和使用数据库 • SQL Server 2000中数据库的组成： • 数据表集合 • 视图 • 索引 • 存储过程 • 触发器 • …… • SQL Server可以支持多个数据库

第3章创建和使用数据库 3.1 查看数据库企业管理器展开Microsoft SQL Servers 展开选中的服务器展开“数据库”。默认的数据库： master、model、msdb、Northwind、pubs、tempdb

第3章创建和使用数据库 • master数据库： • 记录SQL Server系统的所有系统级别信息； • 记录所有的登录帐户和系统配置设置； • 记录所有其他的数据库，包括数据文件的位置； • 记录SQL Server的初始化信息，始终有一个可用的最新master数据库的备份。

第3章创建和使用数据库 • model数据库：用作在系统上创建的所有数据库的模板。新数据库的第一部分通过复制model数据库创建，剩余部分由空页填充。 • msdb数据库：供SQL Server代理程序在调度警报和作业以及记录操作员时使用。 • Northwind和pubs数据库：SQL Server自带的两个范例数据库。

第3章创建和使用数据库 • tempdb数据库： • 保存所有的临时表和临时存储过程； • 满足其他所有的临时存储要求； • 是全局资源，所有用户的临时数据都存储在tempdb中； • SQL Server启动时会重新创建tempdb； • tempdb的内容不会从一个会话保存到另一个会话。

第3章创建和使用数据库 • 在数据库名上右键选择查看任务板可以显示数据库的“常规”内容。

第3章创建和使用数据库 • 点击“表信息”选项卡可以显示出数据库中表的信息。

第3章创建和使用数据库 • 点击“向导”选项卡可以打开对于数据库进行各种操作的向导。

第3章创建和使用数据库 • 查看关系图选中数据库下面的关系图，双击右侧显示的关系图图标可以查看关系图的详细内容。关系图是用来记录表之间关联情况的图。

第3章创建和使用数据库 在关系上右键选择“属性”可以查看关系的详细信息。

第3章创建和使用数据库 • 查看表结构 • 在数据库下选择“表”，右侧会列出数据库中所有的表，在表上右键选择“设计表”可以查看表的结构。

第3章创建和使用数据库 • 查看表的内容在表上右键选择“打开表”的“返回所有行”可以查看表的内容。

第3章创建和使用数据库 • 查看视图结构 • 在数据库下选择“表”，右侧会列出数据库中所有的视图，在视图上右键选择“设计视图”可以查看视图的结构。

第3章创建和使用数据库 • 查看视图的内容在视图上右键选择“打开视图”的“返回所有行”可以查看视图的内容。

第3章创建和使用数据库 • 查看存储过程存储过程是用SQL语言编写的存储在数据库中的程序，执行效率较高，还可以重复调用。和表的查看方式一样。

第3章创建和使用数据库 • 查看用户和角色 • SQL Server中默认有dbo和guest两个用户，dbo的登录名为sa。 • 双击用户名可以打开用户的属性对话框。

第3章创建和使用数据库 • 查看用户和角色 • SQL Server中默认有10个角色。 • 双击角色可以打开角色的属性对话框。 • 点击权限可以用来设置角色的权限。

第3章创建和使用数据库 • 数据库的其他组成部分： • 规则（Rules）：用来限制限制表字段的数据范围 • 默认（Defaults）：用来设置默认值，字段可以与默认值绑定 • 用户定义的数据类型（User Defined Data Type）：用户根据自己的需要定义数据类型 • 用户定义的函数（User Defined Function）：用户自定义的函数 • 全文目录（Full-Text Catalogs）：存放数据库中表字段的全文索引目录，可方便搜索较长的字符串字段数据

第3章创建和使用数据库 3.2 SQL Server数据库和文件 • 数据库文件组 SQL Server 2000用操作系统文件来存放数据库，分为3类： • 主数据文件（Primary） • 用于存放数据，是其他所有数据库文件的基础； • 每个数据库有且只有一个主数据文件； • 默认扩展名为.MDF。

第3章创建和使用数据库 • 二级数据文件（Secondary） • 用于存放数据，作为Primary的补充和扩展； • 每个数据库可以有多个二级数据文件； • 默认扩展名为.NDF。 • Primary和Secondary统称为数据文件，都是数据库用于存放数据库对象的地方。其容量设置需要仔细斟酌考虑。

第3章创建和使用数据库 • 事务日志文件（Transaction Log） • 用于存放事务日志，它记录了所有事务信息及其对数据库的修改； • SQL Server先写日志后进行数据库修改； • 默认扩展名为.LDF。 • 日志文件是维护数据完整性的重要工具。若数据库崩溃，可通过日志文件和数据备份重建和恢复数据库。

第3章创建和使用数据库 • 数据库文件组 SQL Server 7.0开始引入文件组（FileGroup）将各个数据库文件组成一组。文件组可以有效地提高数据库的读写速度。提供三种文件组类型： • 主文件组（Primary）：包含主数据文件和所有没有包含在其他文件组内的文件。数据库的系统表都被包含在主文件组里。

第3章创建和使用数据库 • 自定义文件组（Primary）：包含所有在使用CREATE DATABASE或ALTER DATABASE时用FileGroup关键字来进行约束的文件。 • 默认文件组（Default）：容纳所有在创建时没有指定文件组的表、索引，以及text、ntext和image数据类型的数据。 • 创建数据库文件组要遵循的规则： • 一个文件或文件组只能被一个数据库使用； • 一个文件只能属于一个文件组； • 数据和事务日志不能共存于同一个文件或文件组上； • 日志文件不能属于文件组。

第3章创建和使用数据库 • 数据库存储结构数据库创建数据库对象（表、索引等）时，SQL Server会使用一些特定的数据结构给数据对象分配空间，即盘区和页面。数据文件.MDF或者.NDF 日志文件.MDF 盘区（8个连续页面）表、索引数据页（8KB）最大行长度为8060字节

第3章创建和使用数据库 • 页面 • SQL Server的所有信息都存储在页面上，页面是数据库中使用的最小存储单元。 • 每个页面大小为8192字节，其中前132字节是页面头（页面标识），剩余8060字节用于存储数据。

第3章创建和使用数据库 • 页面类型： • 分配页面：用于控制数据库中给表和索引分配的页面 • 数据和日志页面：用于存储数据库数据和事务日志数据。数据存储在每个页面的数据行中，SQL Server不允许跨页面存储 • 索引页面：用于存储数据库中的索引数据 • 分发页面：用于存储数据库中有关索引的信息 • 文本/图像页面：用于存储大量的文本或者二进制大对象（BLOB），例如图像。

第3章创建和使用数据库 • 盘区 • 盘区（extent）是由8个连续的页面组成的数据结构，大小为8×8KB=64KB。 • 当创建一个数据库对象时，SQL Server会以盘区为单位为其分配空间。 • 每一个盘区只能包含一个数据库对象。页面盘区文件数据库

第3章创建和使用数据库 • 事务日志事务日志随着数据库被创建。事务日志存储在一个单独的文件上。 • 事务日志的创建：创建数据库时必须指定事务日志的存储文件。日志和数据分开存放的优点： • 事务日志可以单独备份； • 可以把服务器恢复到最近的状态； • 事务日志不会抢占数据库的空间； • 易于检测事务日志的空间； • 写入时减少冲突，有利于提高性能。

第3章创建和使用数据库 • 事务日志提供容错机制事务是指一次完成的操作的集合，如果一个事务只是部分执行，数据库将受到破坏。事务日志可以防止这种情况发生： • 用户执行修改数据库对象的任务。 • 当这个事务开始时，在事务日志中记录其开始标志，并将数据读入缓冲区。 • 先在日志中记录操作，然后进行操作；如果操作都完成则先记录事务提交的标志，然后提交事务。对日志的写入会立即写到磁盘上。 • 当检查点进程发生时，将缓冲区的数据写回到硬盘。然后在日志中写入一个检查点标志。

第3章创建和使用数据库 • 在服务器崩溃后，恢复过程会通过日志记录中最后一个检查点信息及当时的事务信息对数据库进行REDO或者UNDO操作，将数据库恢复到崩溃前最近的一个完整的状态。

第3章创建和使用数据库 3.3 创建数据库可以用企业管理器或者Transact-SQL创建数据库 • 用企业管理器直接创建数据库 • 企业管理器Microsoft SQL Servers SQL Server组某个服务器在“数据库”上右键并选择“新建数据库”菜单项。

第3章创建和使用数据库 • 在“数据库属性”中，输入符合要求的新数据库名称。选择排序规则。

第3章创建和使用数据库 • 在“数据文件”选项卡中，可以设置数据文件的名称、位置及大小。“文件自动增长”选项使得数据文件超过了初始容量时将会按照固定步长来自动扩容。

第3章创建和使用数据库 • 在“事务日志”选项卡中，也可以设置事务日志文件的名称、位置及大小。选项与“数据文件”选项卡中的一致。 • 单击“确定”按钮即可创建，系统不会返回任何信息。

第3章创建和使用数据库 • 使用企业管理器中的向导创建数据库 • 企业管理器“工具”菜单“向导” “数据库” “创建数据库向导”

第3章创建和使用数据库 • 创建数据库向导

第3章创建和使用数据库 • 数据库的属性设置

第3章创建和使用数据库 • “选项”中各选项的含义： • ANSI NULL默认设置：使数据库和ANSI标准兼容。该选项会影响到数据库中新建的表，表中的每一字段默认值可以设置为空值。 • 递归触发器：允许在触发器中启动其他触发器。 • 自动更新统计信息：任何最优化查询所需的统计数据过期后，会在最优化时自动更新。 • 残缺页检测：测试分页是否正常。

第3章创建和使用数据库 • 自动关闭：当数据库的资源全部释放，所有用户都离开后，数据库自动停止。 • 自动收缩：数据库自动整理数据以缩小所占用空间。 • 自动创建统计信息：任何最优化查询所需的统计数据丢失后，会在最优化时自动重新建立。 • 使用被引用的标识符：双引号中的文字表示标识字，单引号中的文字表示字符串。

第3章创建和使用数据库 • 数据库大小估算和收缩数据库设置数据库大小时应精确估计数据库的大小。如果设置得过小并设置了数据库自动增长选项后会造成数据存放得不连续，导致数据库性能下降。如果设置得过大，则会造成磁盘空间的浪费。

第3章创建和使用数据库 • 估算表所需页面数的公式：页面数=表的行数/(8060/行的长度) 其中：行的长度指一个记录所占的字节数。

第3章创建和使用数据库 • 为了避免数据丢失，更改数据库属性时只允许增大数据文件和日志文件的大小。 • SQL Server 2000可以通过收缩数据库中的每个文件以删除未使用的页。数据和事务日志文件都可以收缩。 • 可以手动收缩也可以设置为定时自动收缩。

第3章创建和使用数据库 • 收缩数据库在调度选项组中可以创建或更改自动收缩数据库的频率和时间。

第3章创建和使用数据库 • 在“收缩操作”选项组中的选项可以指定数据库的收缩量： • 在“收缩后文件中的最大可用空间”文本框中输入收缩后数据库中剩余的可用空间量。以“数据库大小”栏中的“可用空间”值作为依据。 • 选择“在收缩前将页移到文件起始位置”复选框，使释放的文件空间保留在数据库文件中，并使包含数据的页移到数据库文件的开始位置。

第3章创建和使用数据库 • 使用“收缩文件”栏中的“文件”按钮可以收缩个别的数据库文件。 • “压缩页，然后截断文件中的可用空间”：先进行压缩，然后截断可用空间 • “从文件结尾截断可用空间”：直接截断可用空间 • “清空文件”：将数据迁移到文件组中的其他文件，然后清空当前文件 • “收缩文件至”：指定收缩后文件的大小

第3章创建和使用数据库 • 注意：不能将整个数据库收缩到比其原始大小还要小，也不能将数据库的大小收缩到小于model数据库的大小。

第3章创建和使用数据库 3.4 数据库更名数据库更名需要先将并发用户数设为1，更名后再将并发用户数改回来。 • 选中数据库选“工具”菜单中“SQL Server配置属性” “连接”选项卡

第3章创建和使用数据库 • 用系统存储过程sp_renamedb重命名： sp_renamedb @原名称, @新名称例：sp_renamedb ‘school2’, ‘school1’ • 可用sp_helpdb查看指定数据库的信息 sp_helpdb [ [@dbname=] ‘name’] 其中[@dbname=] ‘name’用于指定数据库，如果没有指定name则报告所有的数据库。

第 3 章 创建和使用数据库