第二章数据库的设计

1. 第二章数据库的设计

2. 学习目标 • 了解数据库的存储结构； • 掌握数据库的创建，修改及删除； • 掌握数据库选项的设置。

3. 学习重点 • 数据库的创建和修改； • 数据库选项的设置。

4. 2.1数据库的存储结构 数据库 数据 (文件).mdf 或 .ndf 日志 (文件).Idf 表， 索引 扩展盘区 (8个连续的 8KB页) 数据 页 (8 KB) 每行大小最多 8060 字节

5. 2.1数据库的存储结构 • 每一个数据库都对应于两个或多个文件，在该数据库内创建的数据库对象（如表、视图等）都保存在这些文件中。 • 数据库系统文件分为三类：主数据文件、二级数据文件和日志文件。

6. 2.1数据库的存储结构 • （1）主数据文件：主数据文件是数据库的起点，指向数据库中的其他文件。主数据文件的默认扩展名为“*.mdf”。每个数据库有且只能包含一个主数据文件，用于存储数据库的启动信息、数据和数据库对象。 • （2）二级数据文件也称为辅助数据文件，其默认扩展名为“*.ndf”。每个数据库可以没有或有多个二级数据文件，用于存储数据和数据库对象。

7. 2.1数据库的存储结构 • （3）日志文件的默认扩展名为“*.ldf”。每个数据库必须包含一个以上的日志文件。用于存放对数据库进行操作的事务日志，在数据库遭到破坏时，可以利用日志文件恢复数据。

8. 2.1数据库的存储结构 • 在有多个数据文件时，为了管理和分配上的目的，可以将他们分成几个组，每一组可以有多个数据文件。 • 日志文件是独立的，它不属于任何文件组。 • 一个独立的文件必须存储在一个磁盘上，但一个文件组中的各个文件可以分别存储在不同的物理磁盘上。这样，可以减轻单个磁盘驱动器的存储负载并提高输入输出速度。

9. 2.1数据库的存储结构 • 在SQL Server中，日志文件是由一系列日志记录组成的，而数据文件则由页面和扩展盘区组成。 • 1页＝8千字节 • 1个扩展盘区=8页。 • 页是SQL Server 2000中数据的最小基本存储单位

10. 2.1数据库的存储结构 • 1个扩展盘区是8个连续的页，它是表和索引的基本存储单位。 • SQL Server 2000有两类扩展盘区，统一扩展盘区和混合扩展盘区 • 从物理上讲，日志文件是由一系列连续的日志记录组成的，日志记录序列必须存储在一个或多个物理文件内。

11. 2.2 SQL Server的系统数据库与示例数据库 • SQL Server的数据库可以分为两大类：系统数据库和用户数据库。 • 系统数据库是SQL Server内置的，它们主要用于管理，不能随便更改或删除。 • 1．master数据库 用以记录SQL Server系统的相关信息 • 2．tempdb数据库 用于保存所有临时表、临时存储过程以及其他临时数据。

12. 2.2 SQL Server的系统数据库与示例数据库 • 3．model数据库 是所有新建数据库的模板，用户每创建一个新数据库，其内容都与模板相同。 • 4．msdb数据库 由SQL Server Agent服务使用，用于自动执行数据库有关操作，如自动进行备份、警报和复制等。 SQL Server 2000在安装时建立了两个示例数据库——pubs和Northwind

13. 2.3 数据库的创建 • 使用企业管理器创建数据库 • 单击需要创建数据库的服务器左边的加号图标，展开树形目录，在对话框中选择“数据库”节点，然后单击鼠标右键，在快捷菜单中选择“新建数据库”命令 。

14. 2.3 数据库的创建 • 使用向导创建数据库 • 启动 SQL Server 2000 创建数据库向导有两种方法 • 一是选择“工具”→“向导”菜单命令或单击工具栏中的按钮，打开“选择向导”对话框。双击其中的“数据库”，显示数据库操作向导 • 第二种方法是先在目录结构树窗口中选中某个 SQL Server 2000 服务器或数据库，选择“查看”→“任务板”菜单命令，并在窗口中切换到“向导”选项卡 ，单击任务板“向导”选项卡中的“创建数据库”, 也可启动创建数据库向导

15. 2.3 数据库的创建 • 使用 T-SQL 语句创建数据库 • T-SQL 的CREATE DATABASE 语句用于创建一个新数据库及存储该数据库的文件。其完整格式如下： • CREATE DATABASE database_name • [ON • [ < filespec> [ ,...n ] ] • [ , < filegroup > [ ,...n ] ] • ] • [ LOG ON { < filespec> [ ,...n ] } ] • [ COLLATE collation_name] • [FOR LOAD | FOR ATTACH ]

16. 2.3 数据库的创建 • 其中 < filespec> 格式为 : • [PRIMARY] • ([NAME = logical_file_name ,] • FILENAME = ‘os_file_name’ • [ , SIZE = size] • [ , MAXSIZE = { max_size | UNLIMITED } ] • [ , FILEGROWTH = growth_increment] ) [ ,...n ] • < file group > 格式为： • FILEGROUP filegroup_name < filespec> [ ,...n ]

17. 2.3 数据库的创建 • database name新数据库的名称,最多可包含128个字符。如果没指定日志文件名称，database_name最多为123个字符 • ON显式定义存储数据库数据部分的数据文件 • LOGON显式定义存储数据库日志的事务日志文件

18. 2.3 数据库的创建 • PRIMARY显式指定关联的<filespec>属于PRIMARY文件组，主文件组的第一个<filespec>条目成为主文件 • logical_file_name指定数据库的逻辑文件名，该名称用于在T-SQL语句中引用数据库 • ‘os_file_name’指定数据库文件的名称 • size指定<filespec>中定义的文件的初始大小。可以KB、MB、GB或TB为单位。默认值为MB

19. 2.3 数据库的创建 • max size指定< filespec >中定义的文件可以增长到的最大范围 • UNLIMITED指定< filespec >中定义的文件将增长到磁盘变满为止 • growth_increment指定< filespec >中定义的文件每次添加的空间大小。可以以KB、MB、GB、TB或% (百分比) 为单位，默认单位为MB。默认的FILEGROWTH值为10%

20. 2.3 数据库的创建 • 例1：创建一个名为students的数据库，所有参数均使用默认值。 • CREATE DATABASE students

21. 2.3 数据库的创建 • 例2：创建了一个名为 mySQLTest1的数据库,数据库文件保存在 d:\Data文件夹中。 数据文件名称为 mySQLTest1_Data, 其磁盘文件名为 mySQLTest1_Data.mdf。事务日志文件 名为 mySQLTest1_Log, 其磁盘文件名为 mySQLTest1_Log.ldf。两个文件都属于 PRIMARY 文件组，初始大小为 10MB, 文件最大可达50MB, 文件每次可增长 2MB 。

22. 2.3 数据库的创建 • CREATE DATABASE mySQLTest1 • ON PRIMARY • (NAME = mySQLTest1_Data, • FILENAME =‘E:\Data\mySQLTest1_Data.mdf’, • SIZE = 10MB, • MAXSIZE = 20, • FILEGROWTH = 2) • LOG ON • ( NAME = mySQLTest1_Log, • FILENAME = ‘E:\Data\mySQLTest1_Log.1df’, • SIZE = 10MB, • MAXSIZE = 50, • FILEGROWTH = 2)

23. 2.4 设置数据库选项 • 数据库选项决定数据库的属性，它可以控制数据库的某些行为和特性 • 在 SQL Server 2000中，设置数据选项有两种方法: • 一种方法是在查询分析器中执行系统的存储过程 sp_dboption。 • 另一种方法是在企业管理器中使用数据库属性对话框设置。

24. 2.4 设置数据库选项 • 使用sp_dboption 设置数据库选项的语法格式如下: • sp_dboption [,’database’] [,’option_name’] [,’value’] • 其中 database 为所设置的数据库名称，option_name 是需要设置的数据库选项名称，value说明打开或关闭指定的数据库选项，其值为 true、false 或 on、off。

25. 2.4 设置数据库选项 • 例3:下面语句可以将 “students”数据库设置为只读数据库。 • sp_dboption 'students','read only','true' • GO

26. 2.4 设置数据库选项 • 例4:下面语句可以将“students”数据库设定为脱机状态。 • sp_dboption 'students','offline','true' • GO

27. 2.5修改数据库 • 数据库在使用过程中,经常需要对其属性进行修改,比如增加、删除文件，创建文件组，修改文件组属性，修改日志文件大小等。 • 修改数据库有两种方法: • 一种方法是通过企业管理器中的数据库属性对话框进行修改。 • 另一种方法是通过 ALTER DATABASE 命令进行修改。

28. 2.5修改数据库 • ALTER DATABASE语句的语法格式为： • ALTER DATABASE database_name • {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 FILEGROUP • filegroup_name filegroup_property • }

29. 2.5修改数据库 • 例5 创建一个mySQLTest3的数据库，然后用 ALTER DATABASE 语句添加一个新的Test4数据库文件，其大小为10MB。 • CREATE DATABASE mySQLTest3 • ON • (NAME=Test3, • FILENAME="e:\Data\Test3.mdf", • SIZE=5, • MAXSIZE=10, • FILEGROWTH=1 • ) • GO

30. 2.5修改数据库 • ALTER DATABASE mySQLTest3 • ADD FILE ( • NAME=Test4, • FILENAME="e:\Data\Test4.mdf", • SIZE=10MB, • MAXSIZE= 100MB, • FILEGROWTH=2MB • ) • GO

31. 2.5修改数据库 • 例6 将mySQLTest3数据库中的Test4数据文件大小修改为 20MB。 • ALTER DATABASE mySQLTest3 • MODIFY FILE ( • NAME=Test4, • SIZE=20MB • ) • GO

32. 2.5修改数据库 • 例7 向mySQLTest3数据库中再添加一个日志文件Test3_log2。 • ALTER DATABASE mySQLTest3 • ADD LOG FILE • ( • NAME=Test3_log2, • FILENAME="E:\Data\Test3_log2.ldf", • SIZE=10MB, • MAXSIZE=50MB, • FILEGROWTH=2MB • ) • GO

33. 2.6删除数据库 • 删除数据库有两种方法： • 方法一是使用企业管理器 • 方法二是使用 DROP DATABASE 命令。

34. 2.6删除数据库 • 在 SQL Server 2000中，通过执行 DROP DATABASE 语句来删除没用的数据库，其语法格式为： • DROP DATABASE database_name [, n] • 其中database_name为需要删除的数据库名。

35. 2.6删除数据库 • 例8：用DROP DATABASE语句删除数据库mySQLTest1。 • 在查询分析器中键入并执行下面语句： • DROP DATABASE mySQLTest1

36. 本章小结 • 数据库由主数据文件，辅助数据文件和事务日志文件组成。 • 使用CREATE DATABASE语句创建数据库 • 使用ALTER DATABASE语句修改数据库：添加文件、删除文件、修改数据库文件属性。 • 使用DROP DATABASE语句删除数据库 。