《SQL Server数据库应用技术实例教程》课件任务二.ppt

1、任务二 为数据管理创建数据库环境 创建和管理SQL Server数据库任务任务:在SSMS中创建数据库JXGL，并对JXGL进行简单管理；使用CREATE DATABASE语句创建数据库JXGL，使用T-SQL语句对JXGL进行简单管理。2.1 认识SQL Server数据库2.2 创建SQL Server数据库2.3 管理SQL Server数据库2.4 知识进阶 项目实训 学习目标 了解SQL Server数据库的存储结构 建立事务日志的概念 使用SSMS创建数据库 使用CREATE DATABASE语句创建数据库 使用SSMS管理数据库 使用T-SQL语言管理数据库2.1 2.1 认识认

2、识SQL ServerSQL Server数据库数据库 任务：了解SQL Server数据库的存储结构，建立事务日志的概念。创建和管理创建和管理SQL Server数据库数据库2.1.1 SQL Server数据库的存储结构2.1.2 事务日志2.1.1 SQL Server2.1.1 SQL Server数据库的存储结构数据库的存储结构 SQL Server数据库的存储结构分为逻辑存储结构和物理存储结构。逻辑存储结构指的是数据库是由各种数据库对象组成的；物理存储结构是讨论数据库文件如何在磁盘上存储的。事务日志是 SQL Server数据库的一个亮点，它对数据库恢复起着至关重要的作用。从逻辑存

3、储结构角度看，SQL Server数据库是由表、视图、索引、存储过程等各种数据库对象组成的，它们被数据库管理系统管理。从物理存储结构角度看，数据库实际上是一个文件的集合，在磁盘上以文件为单位存储。数据库的物理存储结构是用来讨论数据库文件如何在磁盘上存储的。创建和管理创建和管理SQL Server数据库数据库数据的逻辑结构和物理结构数据的逻辑结构和物理结构 数据区学号学号姓名姓名性别性别出生日期出生日期民族民族J07051129王浩男1988-9-23汉族J08010212杜薇女1990-3-16回族槽2槽1槽0页头（96字节）行偏移数组1 1数据库的物理文件数据库的物理文件 数据库是存储数据的

4、容器，数据以文件形式存储在磁盘上，称为数据库的物理文件或操作系统文件。每个SQL Server 2005数据库包含两类物理文件：数据文件和日志文件。数据文件存储数据和数据库对象。第一个数据文件称为主数据文件，文件名后缀为.mdf（Primary Data File）；日志文件用来存储恢复数据库中所有事务所需的信息，文件名后缀为.ldf（Log Data File）。每个SQL Server数据库至少包含一个主数据文件和一个日志文件。当数据库存储的数据量巨大，或需要将重要数据与次要数据分开存储时，用户可自行创建次数据文件，辅助主数据文件存储数据，文件名后缀为.ndf（Secondary Data

5、 File）。一个SQL Server 2005数据库只包含一个主数据文件，必须至少包含一个日志文件，可以不包含或包含多个次数据文件。2 2数据库文件的名称数据库文件的名称 每个SQL Server 2005数据库文件有两个文件名：逻辑文件名和操作系统文件名。（1）逻辑文件名。在T-SQL语句中引用数据库文件时使用的名称。其命名必须符合SQL Server标识符规则，且在同一数据库中必须唯一。（2）操作系统文件名。包含存储目录路径的数据库文件名称。其命名必须符合操作系统文件命名规则，且在同一数据库中必须唯一。3 3数据库文件的存储数据库文件的存储 （1 1）数据页）数据页 数据文件的基本存储单

6、位为8KB（8192B）的连续空间，称为一个页（Page）。SQL Server 2005中包含数据页、索引页、文本/图像页等8种类型的页。页由以下三部分组成：1 1）页头）页头。页首的96KB用来存储页的系统管理信息，如页类型、页的可用空间、页码及拥有该页的数据库对象的分配单元ID值等信息。2 2）数据区。）数据区。以槽（Slot）为单位的空间，用来存储数据。数据记录（行）就存储在槽中，紧接着页头按顺序放置。3 3）行偏移数组。）行偏移数组。映射槽的相对位置，通过查询行偏移数组中某个槽的偏移量，就可以准确定位槽在数据区的位置。一个页只能存储一个数据库对象，如表、索引。一个页只能存储一个数据库

7、对象，如表、索引。2.1.1 SQL Server2.1.1 SQL Server数据库的存储结构数据库的存储结构创建和管理创建和管理SQL Server数据库数据库（2 2）盘区）盘区 SQL Server 2005将8个物理上连续的页作为一个单元为数据库对象分配存储空间，这个单元称为盘区（Extent），容量为64KB。以盘区为分配单位，就不会因分配存储空间的操作十分繁忙而影响数据库性能，提高了空间分配效率。分配盘区的基本策略是：容量不满8个页的数据库对象尽量从已经存在的混合盘区中挑选一个进行后续分配；为容量满8个页的数据库对象分配统一盘区。这样大大提高了空间利用率。数据库（表，索引）（数

8、据）数据（文件）.mdf 或.ndf日志（文件）.ldf.页（8KB，每行最大容量8060B）盘区（64KB，8个物理上连续的页）4数据文件组 为便于分配数据和管理文件，可以将数据文件组织到不同的文件组中。SQL Server 2005包含两种类型的文件组：主文件组（Primary）和用户定义文件组。主文件组包含主数据文件和未放入其他文件组的所有次数据文件。每个数据库只有一个主文件组，也是数据库的默认文件组，系统表的所有页均分配在主文件组中。数据库包含多个数据文件时，用户也可以自定义文件组，将次文件放到自定义文件组中。提示：提示：文件组是命名的文件集合，只包含数据文件，日志文件不包括在任何文件

9、组内。2.1.1 SQL Server2.1.1 SQL Server数据库的存储结构数据库的存储结构创建和管理创建和管理SQL Server数据库数据库2.1.1 SQL Server2.1.1 SQL Server数据库的存储结构数据库的存储结构 事务（Transaction）是需要一次完成的操作集合。事务作为SQL Server的单个逻辑工作单元，必须彻底执行完或根本不执行。事务日志用来记录所有事务和每个事务对数据库所做的更新操作，以日志文件形式存储在磁盘中。1 1事务日志的作用事务日志的作用（1）单独恢复个别事务。（2）保证数据库的完整。（3）将恢复的数据库、文件、文件组或页回滚到某一

10、故障点。2 2事务日志的特点事务日志的特点（1）作为单独的文件实现，可以根据需要定义文件为自动增长。（2）日志文件与数据文件分开存放，减少了冲突。（3）事务日志可以单独备份、恢复，不必遵守数据页的存储格式。（4）日志的截断机制速度快，对事务的容量影响小。（5）记录了数据页的分配和释放信息。2.2.1 使用SSMS创建数据库2.2.2 使用T-SQL创建数据库【例2-1】创建未指定文件的数据库shili01。代码如下：IF DB_ID(shili01)IS NOT NULL-判断当前服务中是否已有名称为shili01的数据库DROP DATABASE shili01GO-若已有名称为shili0

11、1的数据库，则删除它CREATE DATABASE shili01GO 分析：本示例没有 语句项。主数据库文件的大小为 model 数据库主文件的大小1MB，事务日志文件为下列值中较大者：512 KB 或主数据文件大小的 25%。因为没有指定MAXSIZE，文件可以增大到填满全部可用的磁盘空间为止。创建用户数据库的过程就是为数据库确定名称、大小和数据库文件名称及存放位置的过程。新建数据库的信息存放在系统数据库master中，属于系统级信息。创建SQL Server数据库主要有两种方法：使用视图化管理工具SQL Server Management Studio（SSMS）和在代码编辑器中使用T-

12、SQL语言中的CREATE DATABASE语句。创建示例数据库JXGL的操作步骤：1CREATE DATABASE的基本语法使用CREATE DATABASE语句创建数据库。基本语法如下：CREATE DATABASE database_name ON PRIMARY ,n ,n LOG ON ,n 其中：:=(NAME=logical_file_name ,FILENAME=os_file_name ,SIZE=size MB ,MAXSIZE=max_size MB ,FILEGROWTH=growth_increment MB|%),n 其中：:=FILEGROUP filegroup

13、_name DEFAULT【例2-2】创建指定数据文件和日志文件的数据库shili02。代码如下：CREATE DATABASE shili02ON(NAME=shili02_data,FILENAME=D:shilishili02_data.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOG ON(NAME=shili02_log,FILENAME=D:shilishili02_log.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO【例2-3】创建指定多个数据和事务日志文件的数据库shili03。代码：CREATE DATA

14、BASE shili03 ONPRIMARY (NAME=shili03_data,FILENAME=D:shilishili03_data.mdf,SIZE=2,MAXSIZE=200,FILEGROWTH=2),(NAME=shili03_data302,FILENAME=D:shilishili03_data302.ndf,SIZE=5,MAXSIZE=500,FILEGROWTH=5%)LOG ON (NAME=shili03_log301,FILENAME=D:shilishili03_log301.ldf,SIZE=10,MAXSIZE=200,FILEGROWTH=10%),(N

15、AME=shili03_log302,FILENAME=D:shilishili03_log302.ldf,SIZE=5,MAXSIZE=1024,FILEGROWTH=2)GOFILEGROUP shiligroup1(NAME=shili04_data403,FILENAME=d:shilishili04_data403.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=shili04_data404,FILENAME=d:shilishili04_data404.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5)LOG ON(NA

16、ME=shili04_log,FILENAME=d:shilishili04_log.ldf,SIZE=5MB,MAXSIZE=25MB,FILEGROWTH=5MB)GO4创建“高职院校教学管理系统”数据库JXGL【例2-5】创建本教材实例数据库JXGL。该数据库具有两个文件组：包含主数据文件JXGL_data 的主文件组，文件初始容量为4MB，递增率为5MB，最大容量为500MB；filegroup1文件组，包含数据文件 JXGL_data02，文件初始容量为2MB，递增率为10%，最大容量为500MB。包含两个日志文件JXGL_log和JXGL_log02，文件初始容量为5MB，递增率为

17、5%和5MB，最大容量为1GB和500MB。代码如下：CREATE DATABASE JXGLON PRIMARY(NAME=JXGL_data,FILENAME=D:JXGLJXGL_data.mdf,SIZE=4,MAXSIZE=500,FILEGROWTH=5),FILEGROUP filegroup1(NAME=JXGL_data02,FILENAME=D:JXGLJXGL_data02.ndf,SIZE=2,MAXSIZE=500,FILEGROWTH=10%)LOG ON(NAME=JXGL_log,FILENAME=D:JXGLJXGL_log.ldf,SIZE=5,MAXSIZ

18、E=1024,FILEGROWTH=5%),(NAME=JXGL_log02,FILENAME=D:JXGLJXGL_log02.ldf,SIZE=5,MAXSIZE=500,FILEGROWTH=5)GO创建用户数据库时要注意以下几点：（1）默认情况下，只有系统管理员可以创建数据库。（2）当新的用户数据库创建后，master数据库的系统表sysdatabases立即被更新，因此创建数据库后应及时备份master数据库。（3）所有用户数据库都是model数据库的副本，数据库的系统表包含在主文件中。model数据库的数据文件为1MB，日志文件也是1MB。这意味着用户数据库的最小容量为2MB。（4

19、）在一个数据库实例上，最多可创建32767个数据库。一个数据库最多可包含32767个数据文件和32767个日志文件。（5）创建完成新数据库后，想在“对象资源管理器”的数据库列表中看到刚创建的数据库，需要刷新“数据库”节点。2.3 管理管理SQL Server数据库数据库任务：任务：使用SSMS及T-SQL语言两种方法对数据库JXGL进行简单管理。2.3.1 使用SSMS管理数据库2.3.2 使用T-SQL管理数据库1查看数据库属性2修改数据库3删除数据库提示：提示：对数据库执行删除命令，不但数据库将被删除，数据库文件也将从磁盘中被删除。删除数据库将更新master数据库中的系统表。若maste

20、r需要还原，则从上次备份master之后删除的所有数据库仍然在系统表中被引用，因而可能导致出错。4数据库更名数据库被创建后一般不要更改名称，否则所有与该数据库的连接都将出错。5生成创建数据库脚本1查看数据库属性使用系统存储过程sp_helpdb查看指定数据库或当前所有数据库信息。基本语法如下：sp_helpdb database_name database_name缺省时，查看当前所有数据库信息。【例2-6】查看数据库实例下所有数据库信息，查看JXGL数据库信息。代码如下：（1）sp_helpdb -查看数据库实例下所有数据库信息（2）sp_helpdb JXGL -查看JXGL数据库信息 运

21、行结果见图2-9，2-10 2修改数据库修改数据库通过 ALTER DATABASE语句可以添加、删除数据库文件、文件组；可以更改文件或文件组的属性，如名称、容量等；还可以更改数据库名称；但不能移动数据库的存储位置。基本语法如下：ALTER DATABASE ADD FILE|ADD FILE|REMOVE FILE|ADD FILEGROUP|REMOVE FILE|MODIFY FILE|MODIFY NAME|MODIFY FILEGROUP|SET【例2-7】使用ALTER DATABASE语句对数据库JXGL进行以下修改。添加一个次数据文件JXGL_data03，初始大小为5MB，最

22、大容量为200MB，增长率为2%。代码如下：USE JXGLGOALTER DATABASE JXGLADD FILE(NAME=JXGL_data03,FILENAME=D:JXGLJXGL_data03.ndf,SIZE=5,MAXSIZE=200,FILEGROWTH=2%)GO【例2-8】使用ALTER DATABASE语句对数据库JXGL进行以下修改：将日志文件JXGL_log02的初始容量由5MB改为10MB，删除数据文件JXGL_data03。代码如下：USE JXGLGOALTER DATABASE JXGLMODIFY FILE(NAME=JXGL_log02,SIZE=10

23、MB)GOALTER DATABASE JXGLREMOVE FILE JXGL_data03GO3移动数据库位置移动数据库位置通过ALTER DATABASE语句的FILENAME子句指定数据库文件的新位置，可以移动系统数据库文件和用户数据库文件。【例2-9】使用ALTER DATABASE语句将数据库shili02移动到D:shiliyidong。（1）移动数据文件位置，代码如下：ALTER DATABASE shili02MODIFY FILE(NAME=shili02_data,FILENAME=D:shiliyidongshili_data02.mdf)GOALTER DATABAS

24、E shili02MODIFY FILE(NAME=shili02_log,FILENAME=D:shiliyidongshili_log02.ldf)GO 提示：提示：移动数据库文件，需要知道数据库文件的逻辑名。可以通过查询 sys.master_files 目录视图中的name 列获取。不能移动或重命名资源数据库文件，若该文件已重命名或移动，SQL Server 将不启动。（2）验证数据文件位置已更改，代码如下：SELECT name,physical_nameFROM sys.master_filesWHERE database_id=DB_id(shili02)GO4删除数据库删除数据

25、库使用DROP DATABASE语句删除数据库。基本语法如下：DROP DATABASE 【例2-10】删除不再需要的数据库shili01。代码如下：DROP DATABASE shili01执行代码后，刷新“数据库”节点，观察发现，数据库shili01 已不在数据库列表中。5数据库更名数据库更名使用系统存储过程sp_renamedb可以对数据库进行重命名。基本语法如下：sp_renamedb ,如将数据库shili04改名为shili。代码如下：sp_renamedb shili04,shili2.4 2.4 知识进阶知识进阶任务：任务：对数据库容量进行理论估算，对数据库实施收缩，节省存储空

26、间。2.4.1 数据库空间的估算2.4.2 增加和收缩数据库容量许多因素影响数据库的最终容量许多因素影响数据库的最终容量:记录的大小。列的大小决定了记录的大小。记录的数量。一般情况下，应该按数据表所代表的实体或关系的实际数据量估算该表要存储的记录数，而不是凭空想象。表的数量。按关系模型特点和数据库设计规范化原则确定。索引的大小。索引的大小取决于索引关键列的大小、索引中包含的数据量（记录数）和索引填充因子。填充因子的值越小，索引页上的空闲空间越大。索引的数量。每个表中都至少包含一个索引。事务日志的大小。修改数据频率越高，存放日志需要更多空间 其他数据库对象的大小和数量。除数据表外，数据库还包含如

27、索引、视图、存储过程等数据库对象。数据库的计划增长率。预先对数据库存储需求估算得高些。2.4.1 2.4.1 数据库空间的估算数据库空间的估算 SQL Server采用预先指定、分配存储空间的方法来创建数据库。当数据库的数据增长到要超过定义的使用空间时，必须加大数据库容量，增加数据库容量即为数据库提供额外的存储空间。反之，当分配的空间没有被充分利用时，会造成空间浪费。收缩数据库功能允许对数据库中的每个文件进行收缩，删除已经分配但没有使用的页。不能将整个数据库收缩到比其初始容量还小。收缩数据库的动作在后台执行，不影响数据库内的用户活动，但不能在备份数据库时收缩数据库，也不在收缩数据库时创建或备份

28、数据库。2.4.2 2.4.2 1使用T-SQL语言手工收缩数据库语法如下：DBCC SHRINKDATABASE|SHRINKFILE (database_name|file_name ,target_percent ,TRUNCATEONLY|NOTRUNCATE )其中：target_percent：数据库收缩后，数据库文件中剩余的可用空间的百分比值。TRUNCATEONLY：将数据库文件中所有分配的未使用空间释放给操作系统。文件空间默认被释放给操作系统。NOTRUNCATE：将DBCC压缩释放的空间保留给数据库文件，文件的物理空间不更改。【例2-11】假设数据库JXGL的当前可用空间为

29、数据库大小的75%，则收缩JXGL，使其可用空间为40%。代码如下：DBCC SHRINKDATABASE(JXGL,40)2.4.2 2.4.2 2使用使用SSMS手工收缩数据库手工收缩数据库3自动收缩数据库自动收缩数据库有以下两种方法：（1）应用ALTER DATABASE语句可以将数据库设计为自动收缩。基本语法如下：ALTER DATABASE SET AUTO_SHRINK on/off其中，on表示将数据库设置为自动收缩。（2）使用SSMS设置数据库为自动收缩。选择要收缩的数据库，打开“数据库属性”对话框。在“选择页”列表中单击“选项”选项卡，展开“其他选项”区的“自动”下拉列表，将

30、“自动收缩”选项的值设置为true。2.4.2 2.4.2 提示：提示：文件收缩操作始终从文件末端开始反向进行。例如，要将一个5GB的文件收缩至4GB，系统将从文件的最后一个1GB开始释放尽可能多的空间。文件中被释放的部分若含有使用过的页，则将这些页重新定位到保留的区域（即文件的开始位置，也即重新组织文件）。只能将数据库收缩到没有剩余的可用空间为止。如5GB的数据库已有4GB数据（可用空间只有20%），若指定数据库收缩到2GB（数据库的40%），则实际上只会释放1GB空间。2.4.2 2.4.2 1实训题目实训题目创建“销售合同管理系统”数据库CONTRACTDB。2实训目的实训目的（1）掌握SQL Server数据库的存储结构。（2）掌握通过SSMS和T-SQL创建数据库的方法。（3）掌握通过SSMS和T-SQL管理数据库的方法。3实训内容实训内容（1）分析“销售合同管理系统”数据库空间需求，估算数据库初始容量，分析每张表所占的记录。（2）使用SSMS创建“销售合同管理系统”数据库CONTRACTDB，并删除数据库。（3）使用CREATE DATABASE语句创建“销售合同管理系统”数据库CONTRACTDB。（4）在SSMS中查看“销售合同管理系统”数据库CONTRACTDB的当前可用空间，若为数据库大小的80%，则收缩数据库，使其可用空间为40%。