SQLServer数据库.ppt

1,第四章 系统数据库与用户数据库,教学目标： SQL Server 2005系统数据库的介绍 SQL Server 2005系统表的介绍 SQL Server 2005系统视图的介绍 SQL Server 2005系统函数的介绍 如何规划数据库,2,4.1 SQL Server 2005系统数据库介绍,SQL Server 2005中的系统数据库包括： master、model、msdb、temdb、distribution和Resource 6个系统数据库中包含的信息有如下4个方面： 配置信息 维护历史 数据排序用的临时空间 一个用于创建所有用户数据库的基本结构。,3,数据库对象,SQL Server 数据库中的数据分别存储在以下几个不同的对象中： 1.表（Tables）2.索引（Index）3.视图（Views） 4.用户定义的数据类型 5.默认（Default）6.约束（Constraints）7.触发器（Triggers）8.存储过程（Store procedures）,4,页面和范围-1,1. 页面是SQL Server 中数据存储的最小单位。数据中所有数据信息都存储在页面上，一个页面的大小是8KB(8192字节) SQL Server中有6类页面:,5,页面和范围-2,2. 范围 范围是SQL Server 为表的索引分配空间的最小单位。一个范围是由8个相连的页面所组成，即64KB。SQL Server 会自动地以范围为单位分配空间。 注： SQL Server 不允许一条记录跨页面存储。 数据文件和日志信息是不会放在同一个文件上的。 所有的SQL Server 数据库都包含这些数据结构。简单讲，一个数据库是由文件组成的，文件是由范围组成的，范围是由页面组成的。,6,物理数据库的文件和文件组-1,1.文件 SQL Server数据库中的所有数据和对象，如表、存储过程、触发器和视图只存储在下列文件内： 主数据文件 主数据文件是数据库的起点并指向数据库的其余文件。主数据库文件扩展名为“.mdf”。 次数据文件 次数据文件由除主文件之外的所有数据文件组成，这些文件包括主数据文件中不包含的所有文件。扩展名：“.ndf”。 事务日志文件 日志文件含有恢复数据库的所有日志信息。扩展名：“.ldf”。,7,物理数据库的文件和文件组-2,2.文件组 文件组是为了管理和数据分配的目的将文件组织在一起。 文件组共有以下3种： 主文件组、用户定义的文件组、默认文件组。,8,物理数据库的文件和文件组-3,3. 文件和文件组规则 一个文件或文件组不能用于多个数据库，只能用于一个数据库。 一个文件只能是一个文件组成员。 一个数据库的数据信息和事务日志信息不能放在同一个文件或文件组中，数据文件和日志文件总是分开的。 事务日志文件不能成为任何文件组的成员。,9,事务日志,SQL Server从7.0版本开始，日志和数据必须分开存放。 事务日志用于记录对数据库操作的所有事务以及每个事务对数据库所做的修改,一旦数据库系统出现故障,事务日志是恢复数据库的唯一源泉。,10,SQL Server系统数据库-1,SQL Server数据库分为两类：系统数据库和用户数据库。两种数据库都存储数据，但SQL Server使用系统数据库来操作和管理系统。 安装SQL Server时，系统自动建立master， model，tempdb，msdb，Resource，AdventureWorks，ReportServer，ReportServerTempDB和AdventureWorksDW。其中master，model，tempdb，msdb和Resource是系统数据库。,11,SQL Server系统数据库-2,1. master 数据库 它包含了 SQL Server 系统中所有系统级信息。包括：磁盘空间及其设备的分配和方法、系统配置参数、登录账号、是否存在其他数据库以及是否存在其他的SQL Server。 注：禁止用户直接访问，要确保随时保留它的一个当前备份。,12,SQL Server系统数据库-3,2. tempdb数据库 它是个临时数据库，在每次重启SQL Server时重建，不需要备份。用于：用户明确创建的临时表、保存中间结果的工作表等。 3. model 数据库 它是一个数据库模板和原形，可以偶尔备份。 4. msdb 数据库 存储备份和还原操作、SQL代理作业和复制的信息和历史记录。SQL Server 使用它来调度报警和任务并记录操作。,13,SQL Server系统数据库-3,5. distribution数据库 只有在因为复制将服务器配置成分发服务器时，才存在该数据库。应当定期备份。 6. Resource数据库 包含SQL Server 2005中包含的所有系统对象，但不包含任何用户数据或用户元数据。该数据库只读，不需备份，用于加快和简化升级到新版本SQL Server 的过程，以及回滚早期的服务包。,14,系统表、系统视图与系统函数,系统表：是SQL Server 系统自己创建并使用和维护的数据表。 系统视图： SQL Server 2005将以前版本中许多系统表作为视图实现，用于向后兼容，这些视图称为兼容性视图，分布在每个数据库的sys架构里。 系统函数：可以直接访问系统表中的信息，而不必直接访问系统表。系统函数也分布在每个数据库的sys架构里。,15,4.3 创建数据库-1,创建数据库，首先要确定数据库名、所有者（创建数据库的用户）、数据库大小以及用来存储数据库的文件和文件组。 创建数据库的方法： SQL Server Management Studio。 Transact-SQL 语句。,16,4.3 创建数据库-2,4.3.1 SQL Server命名规则 字符的长度可以从1到30。 名称的第一个字符必须是一个字母或者是下列字符中的某一个：“”、“”或者是“#”。 在中文版SQL Server 2005中，可以直接使用中文名字。 在首字母后的字符可以是字母、数字或者前面规则中提到的符号。 名称当中不能有任何空格、除非将名字用引号引起来。,17,4.3 创建数据库-3,4.3.2 在SQL Server Management Studio中创建数据库 【例4-1】 建立一个逻辑文件名称为“学生管理数据库”，实际名称为“学生管理数据库.mdf”，初始容量为1MB，最大容为50MB。再建立逻辑文件名为“学生管理日志”，实际文件名“学生管理日志.ldf，初始容量为1MB，最大容量为5MB。 创建数据库 1. 单击【开始】/【所有程序】/【Microsoft SQL Server 2005 】/【SQL Server Management Studio】命令，打开【SQL Server Management Studio】。 2. 在【SQL Server Management Studio】中，展开服务器，右击【数据库】，在弹出的菜单中选择【新建数据库】命令。,18,4.3 创建数据库-4,此时弹出的【新建数据库】对话框，在【常规】选项卡中，输入数据库的名称【学生管理数据库】，然后，点击确定按钮。 此时，返回到【SQL Server Management Studio】窗口。右键单击【学生管理数据库】，在弹出的菜单中选择【属性】命令。 这时出现【学生管理数据库 属性】窗口，点击【文件】选项卡，对新建的用户数据库文件进行设置。 设置完成后，单击确定按钮，就完成了对数据库的创建工作。,19,4.3 创建数据库-5,4.3.3 用SQL语句创建数据库 4.3.3.1 【CREATE DATABASE】 语句 1.命令 CREATE DATABASE 2. 功能 创建一个新数据库及存储该数据库的文件，或从先前创建的数据库的文件中附加数据库。 3. 语法 CREATE DATABASE database_name ON ,n , ,n LOG ON ,n COLLATE collation_name FOR LOAD | FOR ATTACH ,20,4.3 创建数据库-6,其中： := PRIMARY ( NAME = logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,n : = FILEGROUP filegroup_name ,n 4.参数说明 database_name 新数据库的名称。数据库名称在服务器中必须唯一，并且符合标识符的规则。 ON 指定显示定义用来存储数据库部分的磁盘文件（数据文件）。该关键字后跟以逗号分隔的 filespec 项列表。 filespec 项用以定义用户文件和文件组。,21,4.3 创建数据库-7,n 占位符，表示可以为新数据库指定文件。 LOG ON 指定显示定义用来存储数据库日志的磁盘文件（日志文件）。 FOR LOAD 支持该子句是为了与早期版本的Microsoft SQL Server 兼容。 Collation_name 指定数据库的默认排序规则。 PRIMARY 指定关联的 列表定义主文件。 NAME 为由定义的文件指定逻辑名称。 Logical _file_name 用来在创建数据库后执行的Transact-SQL语句中引用文件的名称。 FILENAME 为 定义的文件指定操作系统文件名。,22,4.3 创建数据库-8,os_file_name 操作系统创建定义的物理文件时使用的路径名和文件名。 SIZE 指定中定义的文件的大小。 size 中定义的文件的初始大小。 MAXSIZE 指定中定义的文件可以增长到的最大大小。 max_size 中定义的文件可以增长到的最大大小。 UNLIMITED 指定中定义的文件将增长到磁盘变满为止。 FILEGROWTH 指定中定义的文件的增长增量。 Growth_increment 每次需要新的空间时为文件添加的空间大小。,23,4.3 创建数据库-9,4.3.3.2 使用【CREATE DATABASE】创建数据库 1. 启动【SQL server】服务器。 2. 单击【开始】 / 【所有程序】 / 【Microsoft SQL Server 2005】 / 【SQL Server Management Studio】。 3. 点击【新建查询】，进入【 查询编辑器】界面，录入框中录入SQL语句。,24,4.3 创建数据库-10,4. 在查询窗口录入如下SQL语句并执行： CREATE DATABASE 学生管理数据库 ON（NAME = 学生管理数据库， FILENAME = D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData学生管理数据库.MDF， SIZE = 1MB， MAXSIZE = 50MB， FILEGROWTH = 5MB） LOG ON（NAME = 学生管理日志， FILENAME = D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData 学生管理日志.LDF， SIZE = 1MB， MAXSIZE = 5MB， FILEGROWTH =10%）,25,4.4 查看和修改数据库-1,修改数据库方法： SQL Server SQL Server Management Studio Transact-SQL语句,26,4.4 查看和修改数据库-2,4.4.1在 SQL Server Management Studio中查看和修改数据库 步骤： 1. 单击【开始】 / 【所有程序】 / 【Microsoft SQL Server 2005】 / 【 SQL Server Management Studio 】，打开【SQL Server Management Studio】。 2. 在 【 SQL Server Management Studio 】中展开服务器，展开数据库项，右键单击【学生管理数据库】，在弹出的快捷菜单中选择【属性】命令。,27,4.4 查看和修改数据库-3,此时出现【学生管理数据库 属性】对话框，可以查看到数据库的各项设置参数。 点击数据文件选项卡，修改数据库的最大容量为无限大。 5. 点击事务日志选项卡，修改日志文件每次以1MB空间增长。 6. 最后点击确定按钮，即可完成本次操作。,28,4.4 查看和修改数据库-4,4.4.2 用SQL 语句修改数据库 4.4.2.1【ALTER DATABASE】语句 1.【命令】 ALTER DATABASE 2.【语法】 ALTER DATABASE database ADD FILE ,. TO FILEGROUP filegroup-name | ADD LOG FILE ,. | REMOVE FILE logical-file-name | ADD FILEGROUP filegroup-name | REMOVE FILEGROUP filegroup-name | MODIFY FILE | MODIFY NAME= new-dbname | MODIFY FILEGROUP filegruoup-name filegroup-property | NAME=new-filegroup-name | SET,nWITH | COLLATE,29,4.4 查看和修改数据库-5,其中: :=(NAME=logical-file-name,NEWNAME=New-logical-name ,FILENAME=os-file-name ,SIZE=size ,MAXSIZE=max-size |UNLIMITED ,FILEGROWTH=growth-increment),30,4.4 查看和修改数据库-6,4.4.2.2 使用SQL语句修改数据库 1.启动【SQL Server】服务器. 2.单击【开始】/【所有程序】/【Microsoft SQL Server2005】/【 SQL Server Management Studio 】命令,点击【新建查询】。 3. 在SQL语句录入框中输入如下SQL语句: ALTER DATABASE 学生管理数据库 MODIFY FILE(NAME=