1、第3章 SQL Server2005数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,本章重点,数据库及其对象 文件和文件组及事务日志 创建数据库系统的几个阶段和原则 创建和管理数据库的方法,本章难点,文件和文件组及事务日志 创建和管理数据库的方法,第3章 SQL Server2005数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,3.1 SQL Server 2005 数据库概念,数据库文件,逻辑结构,存储结构,3.1 SQL Server 2005 数据库概念,
2、从存储结构上来看，一个数据库文件至少由一个后缀名为.MDF的主数据文件和一个后缀名为.LDF的事务日志文件组成，可根据实际需要，给数据库增加后缀名为.NDF的次文件和其他日志文件。,3.1 SQL Server 2005 数据库概念,主文件：用于存储数据，每一个数据库都包含一个主文件，其中包含数据库的启动信息，并用来存储数据库 事务文件：事务文件包含有用于恢复数据库的日志信息。每一个数据库必须至少包 含一个事务文件 次文件：次文件包含有不能置于主文件中的数据。如果主文件足够大，能够容纳数据库中的所有数据，则不需要次文件,SQL Server 2005遵循着先写日记、后进行数据修改的原则对数据库
3、进行操作。,3.1 SQL Server 2005 数据库概念,数据库文件组 文件组是SQL另一种形式的容器，文件位置可以很灵活，你可以将数据文件存储在不同的地方，然后用文件组把他们作为一个单元来管理。 例如，你可以将主数据文件放在一个地方然后 将次要数据文件(aa1.mdf, aa2.ndf, aa3.ndf ) ,如 果需要，可将他们放在三个不同的磁盘（如D盘、E 盘、F盘）上。然后创建一个文件组，将所有的文件 指定到此文件组。,3.1 SQL Server 2005 数据库概念,当对数据库对象写操作时，数据库会根据组内数据文件的大小，按比例写入组内所有数据文件中。当进行操作时，可以并行提
4、高工作效率。,第3章 SQL Server2005数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,3.2 系统数据库概述,数据库对象 表：由行和列组成，用于存储数据 视图：是一个虚表，用于查看一个或者多个表 约束：用于强制数据库完整性 索引：加快检索数据的方式 用户自定义函数：实现用户定义的某种功能 存储过程：一组预编译的SQL语句，可以完成指定的操作 触发器：一种特殊类型的存储过程，当某个操作影响到它保护的数据时，它就会自动触发执行 规则：限制表中列的取值范围 默认值：自动插入的常量值 用户自定义数据类型：由用户基于已有的数据
5、类型而定义的新的数据类型,3.2 系统数据库概述,系统数据库 在安装SQL Server时，安装程序创建四个系统数据库和示例用户数据库,3.2 系统数据库概述,1master数据库master数据库记录SQL Server 2005实、 例的所有系统级信息。如果master数据库不可 用，则SQL Server无法启动。 注意：不能在master数据库中创建任何用 户对象(例如表、视图、存储过程或触发器)。 master数据库包含SQL Server实例使用的系 统级信息(例如登录信息和配置选项设置)。,3.2 系统数据库概述,2model数据库 model数据库用作SQL Server 20
6、05实例 上创建的所有数据库的模板。对model数据库进行 的修改（如数据库大小、排序规则、恢复模式和其他 数据库选项）将应用于以后创建的所有数据库。如果修改model数据库，之后创建的所有数据库 都将继承这些修改。例如，可以设置权限或数据库选 项或者添加对象，例如表、函数或存储过程等。,3.2 系统数据库概述,3msdb数据库msdb数据库由SQL Server代理用来计划警报和作 业以及与备份和恢复相关的信息，尤其是SQL Sercer Agent需要使用它来执行安排工作和警报，记录操作者 等操作。 4tempdb数据库tempdb数据库是连接到SQL Server 2005实例 的所有用
7、户都可用的全局资源，它保存所有临时表和临时 存储过程。另外，它还用来满足所有其他临时存储要求。每次启动SQL Server时，系统都要重新创建tempdb数 据库。在断开连接时tempdb数据库自动删除临时表和临时存 储过程。所以不要在此建立需要永久保存的表。,第3章 SQL Server2005数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,3.3 创建数据库,创建数据库注意事项 创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。 创建数
8、据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQL Server命名规范。,3.3 创建数据库,这里以兼容级别90为例来说明数据库的命名规则： step1 名称长度不能超过128个字符，本地临时表的名称不能超过116个字符。 step2 名称字符由英文字母组成，采用Pascal命名法。比如NorthWind。 step3 名称中间不允许有空格或其它特殊字符。 step4 名称不能是保留字，也不能是变量或局部对象的表示符号（具体参考变量命名部分）。,3.3 创建数据库,建议数据库命名： 用产品或项目的名字命名；Pascal Case，如Ad
9、ventureWork; 避免使用特殊字符，如数字，下划线，空格之类；避免使用缩写,3.3 创建数据库,所有的新数据库都是系统样本数据库model的副本。 单个数据库可以存储在单个文件上，也可以跨越多个文件存储。 数据库的大小可以被增大或者收缩。 当新的数据库创建时，SQL Server自动更新“sysdatabases”系统表。 一台服务器上最多可能创建32767个数据库。,3.3 创建数据库,在SQL Server2005中，可以使用二种方法来创建数据库： （1） 在SQL Server Management Studio中使用“对象资源管理器”创建数据库，由于对象资源管理器提供了图形化的
10、操作界面，创建数据库操作简单，容易掌握。 （2）通过在查询窗口中执行T-SQL语句创建数据库。这种方法要求用户掌握基本的SQL语句,3.3 创建数据库,（1） 在SQL Server Management Studio中使用“对象资源管理器”创建数据库,右击“数据库”，选择“新建数据库”,3.3 创建数据库,执行上述的命令或操作后，就会打开数据库属性对话框，这个对话框里有3个选项卡，填完这3个选项卡的内容就建好了数据库。,3.3 创建数据库,在常规选项卡中，设置数据库名称,3.3 创建数据库,设置文件和路径,SQL Server自动设置的数据文件，若不满意其中的设置值，如文件名，存放路径等可自
11、行更改，但这里的文件组无法修改,3.3 创建数据库,设置文件初始大小,设置文件的初始大小,3.3 创建数据库,设置文件增长方式,选择此项，则数据文件的文件大小可依需要自动增长；若取消，则文件大小是固定的。（设置一次增长多少MB，或多少百分比）,限制文件成长的上限在多少MB以内，或不限制,3.3 创建数据库,课堂练习：创建一个名为XS的学生数据库，并为它创建一个主数据文件和一个日志文件。默认存放在C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData目录下，初始大小是5MB，扩展文件时按10%的幅度增长，文件增长不受限制。,3.3 创建数据库,（2
12、）通过在查询窗口中执行T-SQL语句创建数据库。这种方法要求用户掌握基本的SQL语句,CREATE DATABASE database_nameON PRIMARY (NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=grow_increment),nLOG ON(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth
13、_increment),nCOLLATE collation_name,设置数据库名称,该参数在主文件组中指定文件。若没有指定PRIMARY关键字，该语句中所列的第一个文件成为主文件,设置数据库 的数据文件,指定建立数据库的事务日志文件,指定数据或事务日志文件的名称,指定文件的操作系统文件名称和路径。os_file_name中的路径必须为安装SQL服务器的计算机上的文件夹,指定数据或日志文件的大小，默认单位在KB，也可以指定用MB单位。如果没有指定大小，则默认是1MB,指定文件能够增长到的最大长度，默认单位在KB，也可以指定用MB单位。如果没有指定长度，文件将一直增长到磁盘满为止,指定文件的增
14、长量，该参数不能超过MAXSIZE的值。默认单位在KB，也可以指定用MB单位。也可以使用百分比。如果没指定参数，默认百分之十，最小64K,该参数指定数据库的默认排序规则,3.3 创建数据库,SQL 语法中符号的意义 在SQL语法中有的用中括号 ，也有的用尖拓号，它们分别代表了不同的意义，下面来说明各种符号代表的意义： 在SQL语法中，SQL关键词一律用大写字母表示，如CREATE DATABASE；小写字母名称则是要用户替换的参数，例如 database_name。 凡是可以省略的项目或参数，就用 ，例如PRIMARY。 如果有多个选项可以选择其一使用，则用|隔开，例如max_size|UNL
15、IMITED，表示可以用其中一个，但两都不可同时使用。,3.3 创建数据库,例3.1 创建一个名为“xsgl”数据库。其中主数据文件为10MB，最大大小不受限制，每次增长1MB；事务日志文件大小为1MB，最大大小不受限制，文件每次增长10%。,CREATE DATABASE xsgl /* 数据库名 */ ON PRIMARY /* 主文件组 */ ( NAME = xsgl, /* 主数据文件逻辑名 */ FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1 MSSQLDataxsgl.mdf , SIZE = 10MB,MAXSIZE =
16、UNLIMITED, FILEGROWTH = 1MB) LOG ON ( NAME = xsgl_log, FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataxsgl_log.ldf, SIZE = 1MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10%) GO,3.3 创建数据库,例3.2 创建test数据库，包含一个主文件组和两个次文件组。,CREATE DATABASE test ON PRIMARY /*定义在主文件组上的文件*/ ( NAME=pri_file1,FILENAME=D
17、:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapri_file1.mdf ,SIZE=10,MAXSIZE=50,FILEGROWTH=15%), ( NAME=pri_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapri_file2.ndf ,SIZE=10,MAXSIZE=50,FILEGROWTH=15%),3.3 创建数据库,FILEGROUP Grp1 /*定义在次文件组Grp1上的文件*/ ( NAME=Grp1_file1,FILENAME=
18、D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp1_file1.ndf ,SIZE=10,MAXSIZE = 50,FILEGROWTH=5), ( NAME=Grp1_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp1_file2.ndf ,SIZE=10,MAXSIZE=50,FILEGROWTH=5), FILEGROUP Grp2 /*定义在次文件组Grp2上的文件*/ ( NAME = Grp2_file1,FILENAME=D:Pro
19、gram FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp2_file1.ndf ,SIZE=10,MAXSIZE=50,FILEGROWTH=5), ( NAME=Grp2_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp2_file2.ndf ,SIZE=10,MAXSIZE = 50,FILEGROWTH=5 ),3.3 创建数据库,LOG ON /*定义事务日志文件*/ ( NAME=test_log,FILENAME=D:Program FilesMicr
20、osoft SQL ServerMSSQL.1MSSQLDatatest_log.ldf ,SIZE=5,MAXSIZE=25,FILEGROWTH=5 ) GO,3.3 创建数据库,事务日志 SQL创建数据库的时候，会同时创建事务日志文件。 事务日志是存放恢复数据所需的所有信息，是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。当数据库损坏时，管理员可以使用事务日志还原数据库。每一个数据库必须至少拥有一个事务日志文件，允许拥有多个日志文件。事务日志文件的扩展名为.ldf，日志文件的大小至少是512KB。,3.3 创建数据库,事务日志文件和数据文件必须分开存放，优点如下。 （1）事务
21、日志文件可以单独备份。 （2）有可能从服务器失效的事件中将服务器恢复到最近的状态。 （3）事务日志不会抢占数据库的空间。 （4）可以很容易地监测到事务日志的空间。 （5）在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。,3.3 创建数据库,课堂练习 1、创建数据库名为XS的数据库，数据文件名为XS1 _Data.Mdf，存储在E:下，初始大小为3MB，最大为10MB，允许数据库自动增长，增长方式按10比例增长。事务文件为XS1_Log.Ldf，存储在E盘下，初始大小为5MB，最大为10MB，文件增量以1MB增长,3.3 创建数据库,课堂练习,CREATE DAT
22、ABASE XSON ( NAME=XS1_Data,FILENAME=E:XS1_Data.Mdf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=10%)LOG ON( NAME=XS1_Log,FILENAME=E:XS1_Log.Ldf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)GO,3.3 创建数据库,课堂练习 2、创建数据库名为Sales的数据库，数据文件名为Sales.Mdf，存储在E:下，初始大小为4MB，最大为10MB，文件增量以1MB增长。事务文件为Sales_Log.Ldf，存储在E盘下，初始大小为2MB，最大为5MB，文
23、件增量以1MB增长。,3.3 创建数据库,课堂练习,CREATE DATABASE SalesON PRIMARY( NAME=Sales,FILENAME=E:Sales.Mdf,SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOG ON( NAME=Sales_Log,FILENAME=E:Sales_Log.Ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO,第3章 SQL Server2005数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,3.4 管理和维护数
24、据库,打开数据库,USE database_name,USE xsgl,3.4 管理和维护数据库,查看数据库信息 （1）使用管理工具查看,3.4 管理和维护数据库,显示了xsgl的数据库的信息,3.4 管理和维护数据库,（2）使用T-SQL语句查看,sp_helpdb 数据库名,3.4 管理和维护数据库,修改数据库配置 （1）使用管理工具查看,3.4 管理和维护数据库,其余操作与创建数据库的过程相似,3.4 管理和维护数据库,（2）使用T-SQL语句修改数据库配置,ALTER DATABASE database_nameADD FILE(NAME=logical_file_name,FILEN
25、AME=physical_file_name.mdf,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=grow_increment),ADD LOG FILE：增加日志文件 REMOVE FILE：删除数据文件 ADD FILEGROUP：增加文件组 REMOVE FILEGROUP：删除文件组 MODIFY FILE：更改文件属性 MODIFY NAME：重命名数据库 MODIFY FILEGROUP：更改文件组属性,3.4 管理和维护数据库,例3.3 用T-SQL命令将xsgl重命名为“学生管理数据库”,ALTER DATABASE xsgl
26、MODIFY NAME=学生管理数据库,3.4 管理和维护数据库,例3.4 为“学生管理数据库”增加一个数据库文件,ALTER DATABASE 学生管理数据库ADD FILE (NAME=xsgl2,FILENAME=D:xsgl2.ndf),3.4 管理和维护数据库,课堂练习： 1、为数据库XS增加数据文件XSBAK，文件初始大小为5MB，最大大小为50MB，增长幅度为5,ALTER DATABASE XS ADD FILE ( NAME=XSBAK,FILENAME=E:XSBAK_data.ndf,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=5% ) GO,3.4
27、 管理和维护数据库,课堂练习： 2、修改学生数据库XS中的第二个数据文件XSBAK最大大小为50MB,ALTER DATABASE XS MODIFY FILE ( NAME=XSBAK,MAXSIZE=50MB ) GO,3.4 管理和维护数据库,课堂练习： 3、删除学生数据库XS中的第二个数据文件,ALTER DATABASE XS REMOVE FILE XSBAK,3.4 管理和维护数据库,删除数据库当不再需要数据库中的数据时，为了节省空间，可以删除数据库。删除数据库时，SQL Server将从服务器的磁盘中永久删除文件和数据。因此，在删除数据库前，请确认数据库中已经没有任何可需要的数
28、据了。,3.4 管理和维护数据库,（1）使用管理工具删除数据库,3.4 管理和维护数据库,在确认删除对话框中有一个删除备份历史的复选框，选中该复选框，删除数据库的同时，也删除数据库的备份历史,3.4 管理和维护数据库,（2）使用T-SQL语句删除数据库,DROP DATABASE 数据库名称,DROP DATABASE 学生管理数据库,3.4 管理和维护数据库,什么情况不能删除数据库 1、当数据库正在执行数据复制 2、数据库正在恢复 3、当有用户正在对数据库进行操作 4、不能删除系统数据库,3.4 管理和维护数据库,分离和附加数据库 为了避免SQL Server同时管理太多的数据库，耗用不必要
29、的系统资源，我们可以将暂时用不着的数据库从SQL Server中分离，即取消SQL Server与数据库各个实体文件的关系，这样一来，SQL Server可以稍微轻松一点儿。 如果后来要再使用已分离的数据库，没有关系，只要再将数据库附加到SQL Server中就可以了。,3.4 管理和维护数据库,分离与附加数据库适用于以下两种情况：(1)将数据库从一台计算机移到另一台计算机。(2)将数据库从一台计算机的一个磁盘移到另一个磁盘。分离与附加数据库有两种方法：一种是使用管理工具，另一种是使用查询分析器。,3.4 管理和维护数据库,（1）使用管理工具分离数据库,3.4 管理和维护数据库,如果存在使用本
30、数据库的连接，勾选“删除连接”断开这些连接。只有无人在使用数据库，才能分离。,3.4 管理和维护数据库,使用管理工具附加数据库,3.4 管理和维护数据库,3.4 管理和维护数据库,3.4 管理和维护数据库,3.4 管理和维护数据库,3.4 管理和维护数据库,（2）使用T-SQL语句分离数据库,SP_DETACH_DB 数据库名 ,skipchecks=skipchecks,表示分离数据库后是否要进行更新统计，如果为true，则跳过。如果为false，则运行更新统计。,3.4 管理和维护数据库,例：使用查询分析器分离数据库xs，分离后不进行更新统计。,SP_DETACH_DB xs,true,3
31、.4 管理和维护数据库,使用T-SQL语句附加数据库,CREATE DATABASE xs ON PRIMARY(NAME=xs_Data,FILENAME=E:xs_Data.Mdf) LOG ON(NAME=xs_Log,FILENAME=E:xs_Log.Ldf) FOR ATTACH GO,3.4 管理和维护数据库,分离与附加数据库适用于以下两种情况：(1)将数据库从一台计算机移到另一台计算机。(2)将数据库从一台计算机的一个磁盘移到另一个磁盘。分离与附加数据库有两种方法：一种是使用管理工具，另一种是使用查询分析器。,本章小结,本章主要讨论了数据库的各种操作。数据库是SQL Server 2005中最基本的操作对象。对数据库的创建、查看、修改、和删除。这些基本操作是进行数据库管理与开发的基础。通过学习，要求熟练掌握使用管理工具和T-SOL语言进行数据库的创建、修改、查看、删除的操作技能。,习题,SOL Server 2005的数据库对象有哪些？ 简述SOL Server 2005物理数据库的结构。,
