【文档说明】SQL数据库技术课件.ppt，共(60)页，1005.500 KB，由小橙橙上传

转载请保留链接：https://www.ichengzhen.cn/view-92302.html

以下为本文档部分文字说明：

SQLserver数据库技术第3讲管理数据库学习要点：–SQLServer数据库的存储结构–事务日志的概念–数据库的创建、属性设置、收缩、删除–SQLserver的标识符规则从逻辑结构上讲，SQLServer2000数据库用于存放数据库对象，如表、视

图、存储过程、触发器、索引和约束等，在企业管理器中，可以直观地看到他们。从物理结构的角度讲这些数据库对象是要以一定的数据文件形式存储在硬盘中的，这就是所谓数据库的存储结构（如下介绍）。1基本概念及存储结构–主数据文件用来存储数据库的启动信息、部分或全部数据，扩展名为.mdf–每个数据库只能有

一个主数据文件–次数据文件用于保存所有主要数据文件中容纳不下的数据，扩展名为.ndf用于存储数据库中的所有对象保存用于恢复数据库的日志信息（1）文件的概念（2）文件组的概念出于分配和管理上的需要，可以

将数据库文件分成不同的文件组。实际应用中可以通过控制在特定磁盘上放置的数据和索引来提高数据库系统的性能。数据库的对象集合主数据文件.mdf次数据文件.ndf次数据文件.ndf日志文件.ldfPRIMARY组（主文件一定要属于该组）用户命名的组不属于任何组次数

据文件.ndf日志文件.ldf•文件组:将多个数据文件集合起来形成的一个整体–目的：便于进行管理和数据的分配–类型：•Primary组：包含了主数据文件和部分次数据文件•用户命名文件组：包含多个次数据文件•默认文件组：包含所有在创建时

没有指定文件组的表、索引等数据库对象注意：一个数据文件不能存在于两个或两个以上的文件组里，日志文件不属于任何文件组（3）数据库的物理存储结构数据页页是数据库存储的基本单元，是一块大小为8KB的连续磁盘空间，即1M

B空间可以存储128个页。每页的开始部分是96字节的页首，用于存储系统信息，如页的类型、页的可用空间、该页的所有者ID等。页根据功能划分为数据页、索引页、文本和图像页等8个类型。事务日志文件不包含页，而包含一系列日志记录。范围（扩展）（extent）范围是SQ

LServer数据库分配给表或索引的存储空间的最基本单位。每个范围包括八个连续页，即每兆字节最多可容纳16个范围。为了提高空间存储效率，为分两种统一范围（统一扩展）和混合范围（混合扩展）（范围）扩展盘区统一扩展盘区：该扩展盘区的所有八页只能由拥有该盘区的单个对象（表

或索引）独自使用。混合扩展盘区：混合类型的范围可供8个数据库对象共享。事务日志文件的存储结构事务日志文件，用以记录所有事务和每个事务对数据库所做的修改，当数据库破坏时可以用事务日志恢复数据库内容。从物理上讲，日志文件是由一系列连续的日志记录组成的，日志记录序列必须存储在一个或多个物理文

件内。查看：系统数据库和示例数据库的数据文件及日志文件安装SQLServer2000时，系统自动创建4个系统数据库和2个示例数据库。补充说明：1.表中数据是以行为单元进行存储，任何一行数据都会被存储在数据页中，但注意“行不能跨页（即行宽<8KByte），一页可存放多行

”。例：1000行的表，每行宽度5*1024Byte，那么它所占的空间为多少？1000*5*1024Byte，1000*8*1024Byte如果将行宽压缩到4*1024Byte，那么他所占的空间为多少字节？多少页？还有什么其他好处？1000*8*1024Byte，500页，I/O减少，性能提高2.

如果除了主数据文件（.mdf）外，还有多个次数据文件，那么，数据（如表中记录）不是只存储在一个数据文件中，而是分散存储在不同的数据文件上。“表数据跨文件存储”见下页示意图页01：0000页01：0001页01：0002页02：0

000页02：0001页02：0002页01：0255页02：0127主数据文件(*.mdf)：文件ID01次数据文件(*.ndf)：文件ID02文件大小：8*1024*256Byte=2MByte文件大小：8*1024*128Byte=1MByte行不跨页，一页存多行，一

个表中数据跨文件存储示意图表a的第1，2行记录表a的第3，4行记录表a的第n行记录2创建数据库图数据库及其对象的结构关系–创建数据库应具备的条件•创建数据库的登录账户必须具有systemadministrators或dbcreator的服务器角色。•在SQLServer中，可以使用•1.企业管理

器•2.数据库向导•3.执行CREATEDATABASE语句3种方法来创建数据库。创建数据库是创建表及其它数据库对象的第一步。数据库的创建包括为确定数据库名称、决定数据库大小，及在数据库中用来存储数据的文件。(1)使用企业管理器创建数据库（

1）进入SQLServer企业管理器，在左边的目录树结构中选择“数据库”文件夹。（2）单击“操作”菜单中的“新建数据库”命令或右键单击“数据库”文件夹，在弹出的快捷菜单上选择“新建数据库”命令。系统打开“数据库属性”对话

框。（3）在“常规”选项卡中输入数据库的名称，假设为Sales。输入数据库名称（4）选择“数据文件”选项卡，在该窗口中设置数据文件的属性。(可修改数据文件的逻辑名和物理名，完成后不能再修改)（5）选择“事务日志”选项卡，在该窗口中可设置日志文件的属性。（可修改日志文件的逻辑名和物理名，创建

完成后不能再修改）(2)使用向导创建数据库首先选中相应的服务器实例，点击工具栏上的“运行向导”，选中创建数据库即可。(3)使用T-SQL语句创建数据库创建数据库的T-SQL语句的语法形式：CREATEDATABASEdatabase_name[ON[<filespec>[,...n]][,<fi

legroup>[,...n]]][LOGON{<filespec>[,...n]}]<filespec>::=[PRIMARY]([NAME=logical_file_name,]FILENAME='os_file_name‘[

,SIZE=size][,MAXSIZE={max_size|UNLIMITED}][,FILEGROWTH=growth_increment])[,...n]<filegroup>::=FILEGROUPfilegr

oup_name<filespec>[,...n]•database_name：新创建的数据库的名称。•ON：指出用来存储数据库中数据部分的磁盘文件（数据文件）。•PRIMARY：指定主文件组中的主文件。•LOGON：指定

用来存储数据库日志的磁盘文件。•NAME=logical_file_name：指定数据文件或日志文件的逻辑名。•FILENAME='os_file_name'：指定数据文件或日志文件的操作系统文件名，包括文件名和路径。•SIZE=size：指定数据文件或

日志文件的初始大小，默认单位为MB。•MAXSIZE={max_size|UNLIMITED}：指定数据文件或日志文件可以增长到的最大容量，默认单位为MB。•FILEGROWTH=growth_increment：指定数据文件或日志

文件的增长幅度，默认单位为MB。例2-1创建一个数据库sample。主文件为sample_dat。CREATEDATABASEsampleONPRIMARY(NAME=sample_dat,FILENAME='d:\programfiles\microsoftsqlserver

\mssql\data\sample_data.mdf',SIZE=5,MAXSIZE=50,FILEGROWTH=10)例2-2创建一个只包含一个数据文件和一个日志文件的数据库。该数据库名为Sales，数据文件的逻辑名为Sales_data，数据

文件的操作系统名为sales_data.mdf，初始大小为10MB，最大可增加至500MB，增幅为10％；日志文件的逻辑名为Sales_log，操作系统名为sales_log.ldf，初始大小为5MB，最大值为100MB，日志文件大小以2MB增幅增加。

CREATEDATABASESalesON(NAME=Sales_data,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sales_data.mdf',SIZE=10MB,MAXSIZE=500MB,FILEGROWTH=1

0%)LOGON(NAME=Sales_log,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sales_log.ldf',SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=2MB)例2-3创建一个包含

多个数据文件和日志文件的数据库。该数据库名为student，含有三个初始大小为10MB的数据文件和两个8MB的日志文件。CREATEDATABASEstudentONPRIMARY(NAME=std_dat1,FILENAME='

d:\programfiles\microsoftsqlserver\mssql\data\student1.mdf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat2,FILENAME='d:\

programfiles\microsoftsqlserver\mssql\data\student2.ndf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20),(NAME=std_dat3,FILENAME='d:\programfiles

\microsoftsqlserver\mssql\data\student3.ndf',SIZE=10MB,MAXSIZE=200MB,FILEGROWTH=20)LOGON(NAME=std_log1,FILENAME='d:\programfiles\mic

rosoftsqlserver\mssql\data\stdlog1.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10MB),(NAME=std_log2,FILENAME='d:\progr

amfiles\microsoftsqlserver\mssql\data\stdlog2.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10MB)例2-4创建一个包含两个文件组的数据库

。该数据库名为business，主文件组包含business_dat1和business_dat2两个数据文件。文件组business_group包含文件数据文件business_dat3。该数据库还包含一个日志文件business_log。CREATEDATABASEbusin

essONPRIMARY(NAME=business_dat1,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\businessdat1.mdf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10),(NA

ME=business_dat2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\businessdat2.ndf',SIZE=10MB,MAXSIZE=500MB,FILEGR

OWTH=10),FILEGROUPbusiness_group(NAME=business_dat3,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\b

usinessdat3.ndf',SIZE=10MB,MAXSIZE=50MB,FILEGROWTH=10%)LOGON(NAME=business_log,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\busin

esslog.ldf',SIZE=8MB,MAXSIZE=100MB,FILEGROWTH=10MB)3数据库的管理•3.1查看数据库•3.2修改数据库•3.3收缩数据库•3.4删除数据库3.1查看数据库1查看数据库两种方法：（企业管理器和存储过程）（1）用企业管

理器查看数据库的属性进入SQLServer企业管理器，在左边的目录树结构中选中要查看的数据库，单击右键，在弹出的快捷菜单上单击“属性”命令，即可进入数据库的属性窗口。该窗口共有6个选项卡，包括“常规”、“数据文件”、“事务日志”、“文件

组”、“选项”及“权限”，我们可以根据需要进入选择不同的选项卡查看数据库相应的信息。演示创建用户命名文件组，添加次数据文件，修改日志文件或次数据文件逻辑名物理名。（2）用T-SQL语句查看数据库的属性使用系统存储过程sp_helpdb可以查

看某个数据库或所有数据库的属性。格式：sp_helpdbdatabase_name该存储过程显示了数据库的名称、大小、所有者、创建日期以及数据文件和日志文件等属性。例2-5查看数据库Sales的属性。sp

_helpdbSales例2-6查看所有数据库的属性sp_helpdb3.2修改数据库1.修改数据库名称•一般情况下，不建议用户修改创建好的数据库名称。因为许多应用程序可能已经使用了该数据库的名称，引起不必要的麻烦。•（1）ALTERDATABASE语句

•Alterdatabasedatabasenamemodifyname=newname（举例）•（2）Sp_renamedb存储过程•例：Execsp_renamedb‘schoolst’,‘std’（将数据库schoolst改名为std）•SQLserve

r2000不提供重命名属性。补充：重命名数据库语法形式为：sp_renamedb‘old_name’,’new_name’old_name：数据库的当前名称；new_name是数据库的新名称。练习题：将数据库business

更名为company。sp_renamedb'business','company’2修改数据库大小实质上就是修改数据文件和日志文件的长度，或者增加和删除文件。如果数据库中的数据量不断膨胀，需要扩大数据库的尺寸。3种方式：(1)设置数据

库为自动增长方式，这个在创建数据库时设计。(2)直接修改数据库中的数据文件和日志文件的大小。(3)在数据库中增加新的次要数据文件或日志文件。（演示）使用t_sql语句后面系统讲解ALTERDATABASEdatabase{ADDFILE<f

ilespec>[,...n][TOFILEGROUPfilegroup_name]|ADDLOGFILE<filespec>[,...n]|REMOVEFILElogical_file_name|ADDFILEGROUPfilegroup_name|REMOVEFILEGROUP

filegroup_name|MODIFYFILE<filespec>|MODIFYNAME=new_dbname|MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}用T-SQL语句修改数据库属性其

语法形式为：ADDFILE<filespec>[,...n][TOFILEGROUPfilegroup_name]：表示向指定的文件组添加新的数据文件。ADDLOGFILE<filespec>[,...n]：添加新的事务日志文件。REMOVEFIL

Elogical_file_name：删除某一文件。ADDFILEGROUPfilegroup_name：添加一个文件组。REMOVEFILEGROUPfilegroup_name：删除某一文件组。MODIFYFILE<filespec>：修改某个文件的属性。MODIFYNAME=new_d

bname：修改数据库的名字•MODIFYFILEGROUPfilegroup_name{filegroup_property|NAME=new_filegroup_name}：修改某一文件组的属性。例2-7为数据库sample添

加一个数据文件sample_dat2和一个日志文件sample_log2。ALTERDATABASEsampleADDFILE(NAME=sample_dat2,FILENAME='d:\programfiles\microsoftsqlse

rver\mssql\data\sample_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)ALTERDATABASEsampleADDLOGFILE(NAME=sa

mple_log2,FILENAME='d:\programfiles\microsoftsqlserver\mssql\data\sample_log2.ldf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)例2-8为数据库sampl

e添加一个文件组USER1，并向该文件组添加一个二个数据文件sampuser_dat1和sampuser_dat2。ALTERDATABASEsampleADDFILEGROUPUSER1ALTERDATABASEsampleADDFILE(NAME=sampuser_dat1,FILENAME

='d:\programfiles\microsoftsqlserver\mssql\data\sampuser_dat1.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1),(NAME=sampuser_dat2,FILENAME='d:\programfiles

\microsoftsqlserver\mssql\data\sampuser_dat2.ndf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)TOFILEGROUPUSER1例2-9从数据库sample中删除文件sampuser_dat2。ALTERDATABASEsampl

eREMOVEFILEsampuser_dat2例2-10删除数据库sample中的文件组USER1。ALTERDATABASEsampleREMOVEFILEsampuser_dat1ALTERDATABASEsampleREMOVEFILEGROUPUSER1注意：在删

除文件组时必须先删除文件组中所包含的文件。例2-11修改数据库sample中数据文件sample_dat2的属性，将其初始大小改为10MB，最大容量改为80MB,增长幅度改为5MB。ALTERDATABASEsampleMODIFFILE(NAME=sample_dat2,SIZE=10,MAXS

IZE=80,FILEGROWTH=5)例2-12修改数据库business中文件组business_group的属性，将其改名为group1，并设置为DEFAULT属性（即该文件组为默认文件组）。ALTERDATABASEbusinessMODIFYFILEGROUPbu

siness_groupNAME=group1ALTERDATABASEbusinessMODIFYFILEGROUPgroup1DEFAULT例2-13将数据库student改名为stud_teacher。ALTERDATABASEstudentMODIFYNAME=stud_teacher3

.3收缩数据库SQLServer2000允许收缩数据库中的每个文件以删除未使用的页。数据文件和事务日志文件都可以收缩。1．自动收缩（1）使用企业管理器设置自动收缩数据库在数据库的“属性对话框”中，点击“选项”选项卡，选中“自动收缩

”复选框，即可完成数据库自动收缩的设置。设置自动收缩（2）T-SQL语句自动收缩数据库使用ALTERDATABASE语句可以将数据库设为自动收缩。当数据库中有足够的可用空间时，就会发生收缩。其语法形式为：ALTE

RDATABASEdatabase_nameSETAUTO_SHRINKon/off还可使用存储过程：SP_dboption‘databasename’,’autoshrink’,’true’注意：1.自动收缩功能将在数据库的自由空

间超过25%时开始。2.不能将整个数据库收缩到比其初始化的空间还要小，即初始空间占满之后才有可能自动收缩。3.自动收缩功能将在后台执行，对数据库运行有一定影响。2．手工收缩（收缩数据库；收缩数据和日志文件）（1）使用企业管理器收缩数据库及数据、日志文件（2）使用Transact-sql命令实现（

了解）---收缩数据库命令DBCCSHRINKDATABASE(database_name[,target_percent][,{NOTRUNCATE|TRUNCATEONLY}])•database_na

me：是要收缩的数据库名称。•target_percent：是数据库收缩后的数据库文件中所要的剩余可用空间百分比。•NOTRUNCATE：被释放的文件空间依然保持在数据库文件的范围内。•TRUNCATEONLY：尽量收缩数据库到最小（指定的自

由空间百分比将失效），同时将数据文件中的任何未使用的空间释放给操作系统。•如果未指定（NOTRUNCATE和TRUNCATEONLY），则按百分比被释放的文件空间将被操作系统回收利用。例2-16缩小sales数据库的大小

，使得该数据库中的文件有20％的可用空间。DBCCSHRINKDATABASE(sales,20)---收缩数据文件或日志文件命令DBCCSHRINKFILE(file_name{[,target_s

ize]|[,{EMPTYFILE|NOTRUNCATE|TRUNCATEONLY}]})file_name：是要收缩的数据或日志文件的逻辑名。target_size：将文件缩小到指定的长度，以MB为单位。

如果缺省该项，文件将尽最大可能进行缩小。EMPTYFILE：将指定文件上的数据全部迁移到本文件组的其它文件上，以后的操作将不会再再该文件上增加数据，此时即可删除该数据库文件。注意：1.手工收缩数据库不能小于初始数据文件大小，即使有多余的空间，但可采用继续收缩数据或日志文

件的方式继续缩小数据库总大小（注意:数据文件收缩不能小于其实际数据所占空间，日志文件借助backuplog可收缩到比初始值要小）。2.执行完清空某个数据文件的数据时，其所占空间显示大小并不是0MB，但此时数据已被转移，可以删除此数据文件，否则不允许删除。3.4

删除数据库1．用企业管理器删除数据库（1）打开SQLServer企业管理器窗口，在左边的目录树结构中选择要删除的数据库文件夹，如company。（2）选择“操作”菜单中的“删除”命令或右键单击要删除的数据库，在弹出的快捷菜单中选择“删除”命令

，系统会弹出“删除数据库”的确认对话框。（3）单击“是”按钮就会删除该数据库。注：删除之前要停止服务；此操作会将数据库彻底删除。2．T-SQL语句删除数据库语法形式：DROPDATABASEdatabase_name例2-18删除数据库stude

ntDROPDATABASEstudent例2-19删除sample和student数据库。DROPDATABASEsample,student注意：–4个系统数据库master、tempdb、model、msd

b不能删除。–正在使用的数据库不能删除。–数据库被删除之后，文件及其数据都从服务器上的磁盘中删除。一旦删除数据库，它即被永久删除，不能恢复，所以删除数据库时一定要谨慎。4SQLServer命名规则为了提供完善的数据库管理机制，SQLServer设计了严格的命名规则。在创

建或引用数据库实体，如表、索引、约束等时，必须遵守SQLServer的命名规则，否则有可能发生一些难以预料和检查的错误。本章将介绍SQLServer有关对象命名的内容。*SQLServer有关标识符的分类和格式规定SQLSe

rverd的标识符*SQLServer的所有对象，包括服务器、数据库以及数据库对象，如表、视图、列、索引、触发器、存储过程、规则、默认值和约束等都可以有一个标识符。例如下面的SQL语句：CREATETABLEEXAMPLE(

KEYCOLINTPRIMARYKEY,DESCRIPTIONCHAR(80))这个例子创建了一个表格，表格是一个标识符：EXAMPLE；表格中定义了两列，列的名字分别是KEYCOL和DESCRIPTION，它们都是合法的标识符。5.1标识符的分类*SQLSe

rver一共定义了两种标识符：规则（常规）标识符：严格遵守标识符有关格式的规定。所以在Transaction—sql语句中凡是规则标识符都不必使用定界符，如[]和‚‛，来进行限定。界定标识符：是指那些使用了如[]和‘’等界定符

号来进行位置限定的标识符。使用了界定标识符，既可以遵守标识符命名规则，也可以不遵守标识符命名规则。注意：没有使用定界的标识符一律按照规则标识符对待，必须严格遵守标识符命名规则。标识符的分类*例如：select*from[example]等效于——select*from

example但如这样——Select*from[mytable]就要用界定符号。Where[order]=10标识符中不能有空格，order为系统保留字5.2标识符格式规定*一。标识符的首字母必须是以下两种情况之一：1.所有在统一（Unicode）2.0标准中规定的字

符，包括26个英文字母A~Z和a~z，以及其他一些语言字符，如汉字。例如可以给一个表格命名为‚学生基本情况‛。2.下划线‚_‛,‚@‛或‚#‛。*二。标识符首字母后的字符可以是：1.所有在统一码（Unicode）2.0标准中规定的字符，包括26个英文字母A~Z和

a~z，以及其他一些语言字符，如汉字。2.下划线‚_‛,‚@‛或‚#‛或者‚$‛，数字0~9.*三。标识符不允许是Transact-sql的保留字。由于Transact-sql不区分大小写，所以无论是保留字的大写还是小写都不

允许使用。*四。标识符内部不允许有空格或特殊字符。补充：1.无论是界定标识符还是规则标识符都最多只能容纳128个字符。本地临时表标识符最多可以包含116个字符。2.某些以特殊符号开头的标识符具有特殊意义：①当标识符以单个

@字符开头时，表示它是局部变量或参数，以两个@@字符开头时表示它为一个全局变量。②标识符首字符为#时，表示一临时数据库对象。对于表或存储过程，名称以单个#号开头时表示为局部临时对象；③以两个#字符开头时表示为全局

临时对象。临时对象的名称长度不能超过29个字符。小结：–介绍了SQLServer数据库创建、管理和删除的方法，学习了数据库的基本存储结构。–通过对本章的学习，应该重点掌握根据需要创建数据库，并对其进行有效的管理，上机实验所学的基本操作。