第2章SQL_Server数据库的管理与使用

1、数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组第第2章章 数据库的管理与使用数据库的管理与使用SQL Server数据库的存储结构、数据库数据库的存储结构、数据库的创建、数据库的修改的创建、数据库的修改 、数据库的删除、数据库的删除 、数据库的分离和附加数据库的分离和附加 、数据备份和还原、数据备份和还原 数据的嵌套查询和连接查询数据的嵌套查询和连接查询数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组2学习目标学习目标u了解：了解：nSQL Serve数据库的存储结构；数据库的存储结构；SQL Serve数据数据库文件的基本类型；数据库文件和文件组的基本概库文件的基本

2、类型；数据库文件和文件组的基本概念。念。u理解：理解：n数据库文件的组织结构；数据库对象的基本概念；数据库文件的组织结构；数据库对象的基本概念；查看、修改、删除数据库的基本概念；分离数据库查看、修改、删除数据库的基本概念；分离数据库和附加数据库的基本概念。和附加数据库的基本概念。u掌握：掌握：n数据库文件的创建、分离、附加的方法；备份和恢数据库文件的创建、分离、附加的方法；备份和恢复数据库的方法。复数据库的方法。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组32.1 SQL Server数据库的存储结构数据库的存储结构u2.1.1逻辑存储结构逻辑存储结构u2.1.2物理存储结构物

3、理存储结构数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组42.1.1逻辑存储结构逻辑存储结构u数据库的逻辑存储结构数据库的逻辑存储结构n指的是数据库是由哪些性质的信息所组成。指的是数据库是由哪些性质的信息所组成。n它主要应用于面向用户的数据组织和管理，如数据它主要应用于面向用户的数据组织和管理，如数据库的表、视图、存储过程、用户权限等。库的表、视图、存储过程、用户权限等。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组52.1.1逻辑存储结构逻辑存储结构uSQL Server数据库对象数据库对象 对象名称对象名称 功功 能能表表 由数据的行和列组成，格式与工作表相类似

4、。行代表一个唯一的记录，列代表记录中的一个由数据的行和列组成，格式与工作表相类似。行代表一个唯一的记录，列代表记录中的一个字段。类型定义规定了某个列中可以存放的数据类型。字段。类型定义规定了某个列中可以存放的数据类型。 视图视图 可以限制某个表格可见的行和列，或者将多个表格数据结合起来，作为一个表格显示。一个可以限制某个表格可见的行和列，或者将多个表格数据结合起来，作为一个表格显示。一个视图还可以集中列。视图还可以集中列。 存储过程存储过程 是一种是一种Transact-SQL语句，它们被编译成一个执行计划。该过程用于性能优化和控制访问。语句，它们被编译成一个执行计划。该过程用于性能优化和控制

5、访问。 用户用户在数据库内，对象的全部权限和所有权由用户帐户控制。用户帐户与数据库相关。在数据库内，对象的全部权限和所有权由用户帐户控制。用户帐户与数据库相关。角色角色指对数据具有相同的访问权限，是数据库一个对象。指对数据具有相同的访问权限，是数据库一个对象。规则规则实现实现check约束从而保证数据的完整性。约束从而保证数据的完整性。默认默认 默认是针对一个表上的一个列的设置，它决定没有被指定为任何值时使用该值。默认是针对一个表上的一个列的设置，它决定没有被指定为任何值时使用该值。用户定义的数据类用户定义的数据类型型 是一种自定义的数据类型，它基于某个预先定义的数据类型。它可以用来建立对程序

6、员更有是一种自定义的数据类型，它基于某个预先定义的数据类型。它可以用来建立对程序员更有意义的表结构，有助于确保存放数据的相似类的列都具有相同的数据类型。意义的表结构，有助于确保存放数据的相似类的列都具有相同的数据类型。 用户定义的函数用户定义的函数 是一个由一条或多条是一个由一条或多条Transact-SQL语句构成的子例行程序，用于封装代码以便于重用。一语句构成的子例行程序，用于封装代码以便于重用。一个函数可以有最多个函数可以有最多1024个输入参数。用户定义的函数可以取代视图和存储过程。个输入参数。用户定义的函数可以取代视图和存储过程。 数据库技术与应用基础教材编写组数据库技术与应用基础教

7、材编写组62.1.1逻辑存储结构逻辑存储结构uSQL Server 数据库类型数据库类型n系统数据库系统数据库nMasternModelnMsdbnTempdbn用户数据库用户数据库n系统提供的示例数据库系统提供的示例数据库nPubsnNorthwind n用户创建的数据库用户创建的数据库数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组72.1.2物理存储结构物理存储结构u数据库的物理存储结构数据库的物理存储结构n指的是数据库文件在磁盘中是如何存储的。指的是数据库文件在磁盘中是如何存储的。n它主要应用于面向计算机的数据组织和管理，如数它主要应用于面向计算机的数据组织和管理，如数据文

8、件、表和视图的数据组织方式、磁盘空间的利据文件、表和视图的数据组织方式、磁盘空间的利用和回收、文本和图形数据的有效存储等。用和回收、文本和图形数据的有效存储等。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组82.1.2物理存储结构物理存储结构u数据库文件数据库文件 n存放数据库数据和数据库对象的文件。存放数据库数据和数据库对象的文件。3种类型：种类型：n主数据文件（主数据文件（.mdf）n次数据文件次数据文件 (.ndf)n事务日志文件事务日志文件(.ldf)数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组92.1.2物理存储结构物理存储结构u数据库文件的存储形式数据

9、库文件的存储形式 数据库页的结构数据库页的结构 数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组102.1.2物理存储结构物理存储结构u数据库的文件组成数据库的文件组成主数据文件主数据文件*.mdf仅有仅有1个个事务日志文件事务日志文件*.ldf1到多个到多个次数据文件次数据文件*.ndf零到多个零到多个次数据文件次数据文件*.ndf零到多个零到多个(仅有一个仅有一个)主文件组主文件组(零到多个零到多个)次文件组次文件组事务日志事务日志数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组112.1.2物理存储结构物理存储结构u数据库文件组数据库文件组(Database Fi

10、le Group)n主文件组主文件组n用户定义的文件用户定义的文件n文件进行分组的目地：以便于管理和数据的分配文件进行分组的目地：以便于管理和数据的分配放置。放置。n注意：事务日志文件不属于任何文件组。注意：事务日志文件不属于任何文件组。例：分别在三个硬盘驱动器上创建三个文件（例：分别在三个硬盘驱动器上创建三个文件（Data1.ndf、Data2.ndf 和和 Data3.ndf）将这将这3个文件指派到文件组个文件指派到文件组 fgroup1 中，然后，在文件中，然后，在文件组组 fgroup1 上创建一个表。上创建一个表。对表中数据的查询将分散到对表中数据的查询将分散到3个磁盘上，因而性能得

11、以提个磁盘上，因而性能得以提高。高。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组122.2 数据库的创建数据库的创建u2.2.1 使用企业管理器创建数据库使用企业管理器创建数据库u2.2.2 使用向导创建数据库使用向导创建数据库u2.2.3 使用使用Transact-SQL语言创建数据库语言创建数据库数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组132.2.1 使用企业管理器创建数据库使用企业管理器创建数据库u操作方法操作方法数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组142.2.2 使用向导创建数据库使用向导创建数据库u操作方法操作方法n在企业管

12、理器中使用工具菜单中的在企业管理器中使用工具菜单中的“向导向导”命令命令数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组152.2.3 使用使用T-SQL语言创建数据库语言创建数据库Transact-SQLTransact-SQL语言的语法约定及用于说明语言的语法约定及用于说明约定约定 用于用于 大写大写 Transact-SQL Transact-SQL 关键字。关键字。斜体斜体用户提供的用户提供的 Transact-SQL Transact-SQL 语法的参数。语法的参数。粗体粗体数据库名、表名、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样数据库名、表名、

13、列名、索引名、存储过程、实用工具、数据类型名以及必须按所显示的原样键入的文本。键入的文本。下划线下划线指示当语句中省略了包含带下划线的值的子句时应用的默认值。指示当语句中省略了包含带下划线的值的子句时应用的默认值。| |（竖线）（竖线）分隔括号或大括号中的语法项。只能使用其中一项。分隔括号或大括号中的语法项。只能使用其中一项。 （方括号）（方括号）可选语法项。不要键入方括号。可选语法项。不要键入方括号。 （大括号）（大括号）必选语法项。不要键入大括号。必选语法项。不要键入大括号。,.,.n n 指示前面的项可以重复指示前面的项可以重复 n n 次。各项之间以逗号分隔。次。各项之间以逗号分隔。.

14、n n 指示前面的项可以重复指示前面的项可以重复 n n 次。每一项由空格分隔。次。每一项由空格分隔。;可选的可选的 Transact-SQL Transact-SQL 语句终止符。不要键入方括号。语句终止符。不要键入方括号。 := :=语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和语法块的名称。此约定用于对可在语句中的多个位置使用的过长语法段或语法单元进行分组和标记。可使用的语法块的每个位置由括在尖括号内的标签指示：标记。可使用的语法块的每个位置由括在尖括号内的标签指示：。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组162.2.3 使用使用

15、T-SQL语言创建数据库语言创建数据库u创建数据库的命令创建数据库的命令nCREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH 指定数据库的指定数据库的默认排序规则默认排序规则 附加数据库附加数据库支持该子句是为了与早期版支持该子句是为了与早期版本的本的 Microsoft SQL Server Microsoft SQL Server 兼兼容。容。 数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组172.2.3 使用使用T-SQL语言创建

16、数据库语言创建数据库u说明说明n := PRIMARY ( NAME = logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n 数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组182.2.3 使用使用T-SQL语言创建数据库语言创建数据库举例举例【例【例2.2】创建一个指定主数据文件和事务日志文件的简】创建一个指定主数据文件和事务日志文件的简单数据库，数据库名称为单数据库，数据库名

17、称为Exercise_db2。CREATE DATABASE Exercise_db2ONPRIMARY( NAME=Exercise_Data,FILENAME= F:mydbExercise.MDF,SIZE=1,MAXSIZE=Unlimited,FILEGROWTH=10% )LOG ON( NAME=Exercise_LOG,FILENAME= F:mydbExercise.LDF,SIZE=1,MAXSIZE=10,FILEGROWTH=2 )数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组192.3 数据库的修改数据库的修改u2.3.1 使用企业管理器修改数据库使用企

18、业管理器修改数据库u2.3.2 使用使用Transact-SQL语言修改数据库语言修改数据库数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组202.3.1 使用企业管理器修改数据库使用企业管理器修改数据库u注意：不能修改数据文件的逻辑文件名、物理注意：不能修改数据文件的逻辑文件名、物理文件名。文件名。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组212.3.2 使用使用T-SQL语言修改数据库语言修改数据库nALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name | ADD LOG FILE ,

19、.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE 数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组222.3.2 使用使用T-SQL语言修改数据

20、库语言修改数据库u例例2-5：向数据库中添加文件。要求如下：向数据库中添加文件。要求如下：n（1）在）在Exercise_db1数据库中添加一个新数据数据库中添加一个新数据文件（次数据文件），文件（次数据文件），逻辑文件名：逻辑文件名：Exe1dat1物理文件名：物理文件名：D:mydbExe1_dat1.NDF。n（2）数据文件的）数据文件的初始大小为初始大小为2MB，最大大小为最大大小为30MB，自动增长量为自动增长量为2MB。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组232.3.2 使用使用T-SQL语言修改数据库语言修改数据库u操作语句如下（使用查询分析器操作）：操作

21、语句如下（使用查询分析器操作）：nALTER DATABASE Exercise_db1 ADD FILE ( NAME=Exe1dat1, FILENAME=D:mydbExe1_dat1.NDF, SIZE=2MB, MAXSIZE=30MB, FILEGROWTH=2MB)数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组242.3.2 使用使用T-SQL语言修改数据库语言修改数据库u在查询分析器中的状态在查询分析器中的状态打开左边的对象浏览窗口检验命令的有效性执行命令数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组252.4 数据库的删除数据库的删除u2.4.1

22、使用快捷菜单删除数据库使用快捷菜单删除数据库u2.4.2 使用使用Transact-SQL语言删除数据库语言删除数据库数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组262.4.1 使用快捷菜单删除数据库使用快捷菜单删除数据库（1）在企业管理器中删除数据库）在企业管理器中删除数据库（2）在查询分析器中删除数据库）在查询分析器中删除数据库数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组272.4.2 使用使用T-SQL语言删除数据库语言删除数据库u语句格式语句格式nDROP DATABASE database_name ,.n n例例2-10: 删除删除Test_db1

23、DROP DATABASE Test_db1n例例2-11: 删除删除Test_db2和和Test_db3.DROP DATABASE Test_db2,Test_db3数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组282.5 数据库的分离和附加数据库的分离和附加u2.5.1 数据库的分离数据库的分离u2.5.2 数据库的附加数据库的附加数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组292.5.1 数据库的分离数据库的分离u即将数据库从即将数据库从SQL Server中删除，但是保持中删除，但是保持组成该数据的数据文件和事务日志文件中的数组成该数据的数据文件和事务日

24、志文件中的数据完好无损。在实际工作中，分离数据库作为据完好无损。在实际工作中，分离数据库作为对数据库的一种备份方法来使用。对数据库的一种备份方法来使用。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组302.5.2 数据库的附加数据库的附加u即分离数据库的逆操作，通过附加数据库，可即分离数据库的逆操作，通过附加数据库，可以将没有加入以将没有加入SQL Server服务器的数据库文服务器的数据库文件添加到服务器中。还可以很方便地在件添加到服务器中。还可以很方便地在SQL Server 服务器之间利用分离后的数据文件和服务器之间利用分离后的数据文件和事务日志文件组成新的数据库。事务日志

25、文件组成新的数据库。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组312.6 数据备份和还原数据备份和还原u2.6.1数据备份数据备份u2.6.2数据还原数据还原u2.6.3数据备份和还原操作数据备份和还原操作数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组322.6.1 数据备份数据备份u备份概述备份概述n备份是指定期或不定期地将备份是指定期或不定期地将SQL Server数据库中的全部或数据库中的全部或部分数据复制到安全的存储介质部分数据复制到安全的存储介质(磁盘磁盘,磁带等磁带等)上保存起来的上保存起来的过程。过程。n备份类型（备份类型（4种）种）n数据库备份（

26、数据库备份（Database Backups）n差异备份（差异备份（Differential Database Backups）n事务日志备份（事务日志备份（Transaction Log Backup）n文件或文件组备份（文件或文件组备份（File and File Group Backup）n备份的策略备份的策略n是指确定需备份的内容、备份的时间及备份的方式。是指确定需备份的内容、备份的时间及备份的方式。n完全完全 (全库全库)备份备份n完全备份加日志备份完全备份加日志备份n完全备份加差异备份再加日志备份。完全备份加差异备份再加日志备份。数据库技术与应用基础教材编写组数据库技术与应用基础教

27、材编写组332.6.1 数据备份数据备份制定一个完全备份制定一个完全备份的策略是在每天上的策略是在每天上午午12:00点点全库备份全库备份全库备份全库备份Sunday（星期日）（星期日）Monday（星期一）（星期一）Tuesday（星期二）（星期二） DataLogDataLogDataLogu例例1:使用全库备份使用全库备份数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组342.6.1数据备份数据备份u例例2:使用全库使用全库+日志备份例子日志备份例子SundayMonday 全库备份全库备份全库备份全库备份LogLogLogLogDataLogDataLog几个点几个点: M

28、onday 11:30, 12:30,数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组352.6.1数据备份数据备份u例例3:使用全库使用全库+差异差异+日志备份例子日志备份例子MondayTuesday全库备份全库备份差异备份差异备份差异备份差异备份LogDataLogLogLogLogLogLog LogData 日志备份日志备份数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组362.6.2数据还原数据还原u还原概述还原概述n即数据备份的逆向操作。即数据备份的逆向操作。n还原数据库是一个装载数据库的备份，然后应用事务日志还原数据库是一个装载数据库的备份，然后应用事务

29、日志重建的过程。重建的过程。n当数据库或数据遭受破坏或丢失，或者因维护任务或数据当数据库或数据遭受破坏或丢失，或者因维护任务或数据的远程处理从一个服务器向另一个服务器拷贝数据库时，的远程处理从一个服务器向另一个服务器拷贝数据库时，需要执行还原数据库的操作。需要执行还原数据库的操作。n还原模型（还原模型（3种）种）n简单（简单（Simple）还原模型）还原模型n完全（完全（Full）还原模型）还原模型n大容量日志（大容量日志（Bulk_logged）还原模型。）还原模型。n还原顺序还原顺序n还原方案使用一个或多个还原步骤（操作）来实现，这称还原方案使用一个或多个还原步骤（操作）来实现，这称为为“还原顺序还原顺序”。还原的顺序与选择的备份类型和方式有。还原的顺序与选择的备份类型和方式有关。关。数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组372.6.3数据备份和还原操作数据备份和还原操作u数据备份的基本操作数据备份的基本操作n选择备份类型选择备份类型n创建备份设备创建备份设备n实现备份实现备份数据库技术与应用基础教材编写组数据库技术与应用基础教材编写组382.6.3数据备份和还原操作数据备份和还原操作u例例2-12：数据备份和还原操作综合示