SQLserver数据库创建管理

数据库系统原理实践

——SQLServer20052创建和管理数据库1创建和管理数据库1SQLServer数据库概述2创建数据库3修改数据库4删除数据库5分离和附加数据库*21SQLServer数据库概述在一个实例中可以创建多个数据库，每个数据库在物理上和逻辑上都是独立的。每个数据库存储相关的数据。*3数据库分类SQLServer将数据库分为两大类：系统数据库用户数据库。系统数据库是SQLServer数据库管理系统自动创建和维护的，这些数据库用于保存维护系统正常运行的信息。用户数据库保存与用户的业务有关的数据，用户数据库中的数据是用户来维护的。*4数据库分类示意图*51.1系统数据库master：是最重要的数据库，用于记录SQLServer系统中所有系统级信息。msdb：保存关于调度报警、作业、操作员等信息。model：是用户数据库的模板，其中包含所有用户数据库的共享信息。当创建用户数据库时，系统自动将model数据库中的全部内容复制到新建数据库中。tempdb：临时数据库，用于存储临时信息。*61.2SQLServer数据库的组成由若干个文件组成，文件被分为两类：数据文件：用于存放数据库中的数据信息。日志文件：用于存放对数据库的操作记录。*7数据库文件每个数据库都包含：一个主数据文件一个或多个日志文件零个或多个辅助数据文件每个文件都有：物理文件名逻辑文件名*8数据页在SQLServer2005中，数据的存储单位是页（Page，也称为数据页）。一页是一块8KB（8×1024字节，其中用8060个字节存放数据，另外的132个字节存放系统信息）的连续磁盘空间，页是存储数据的最小单位。页的大小决定了数据库表中一行数据的最大大小。*9不允许表中的一行数据存储在不同页上，即行不能跨页存储。因此表中一行数据的大小不能超过8060字节。例：一个数据表有10000行数据，每行3000字节。则每个数据页可存放两行数据，此表需要的空间为：(10000/2)*8KB=40MB*10数据存储示意图*111.3数据文件和日志文件数据文件用于存放数据库数据。分为：主数据文件和辅助数据文件事务日志文件推荐扩展名为.ldf。用于存放恢复数据库的所有日志信息。每个数据库必须至少有一个日志文件。最小为512KB，但最好不要小于1MB。*12数据文件主数据文件：推荐扩展名是.mdf包含数据库的系统信息，并可存放用户数据每个数据库都有且仅有一个主数据文件。辅助数据文件：推荐扩展名是.ndf。一个数据库可以包含0~n个辅助数据文件。辅助数据文件可以分别建立不同的磁盘上。*131.4数据库文件的属性文件名及其位置初始大小增长方式最大大小*14文件名及其位置每个数据文件和日志文件都具有一个逻辑文件名和物理存储位置（包括物理文件名）。一般情况下，如果有多个数据文件，则为了获得更好的性能，建议将文件分散存储在多个磁盘上。*15初始大小主数据文件的初始大小时，其大小不能小于model数据库主文件的大小。因为系统是将model数据库中主数据文件的内容拷贝到用户数据库的主数据文件上。*16增长方式如果需要的话，可以指定文件是否自动增长。默认配置为自动增长，即当数据库的空间用完后，系统自动扩大数据库的空间，以防止由于数据库空间用完而造成的不能进行数据操作的错误。*17最大大小指文件增长的最大空间限制。默认情况是无限制。建议设定允许文件增长的最大空间大小，以防止因文件无限制增长而将磁盘空间用完。*182创建数据库可以在SQLServerManagementStudio（SSMS）工具中用图形化的方式实现，也可以通过T-SQL语句实现。*19T-SQL语句创建数据库CREATEDATABASEdatabase_name[ON--数据文件[PRIMARY][<filespec>[,...n][,<filegroup>[,...n]][LOGON–日志文件{<filespec>[,...n]}]]]*20T-SQL语句创建数据库（续）<filespec>::={(NAME=logical_file_name,--逻辑名FILENAME={‘os_file_name’|‘filestream_path'}--物理名[,SIZE=size[KB|MB|GB|TB]]--初始大小[,MAXSIZE={max_size[KB|MB|GB|TB]|UNLIMITED}]--最大大小[,FILEGROWTH=growth_increment[KB|MB|GB|TB|%]]--增长方式，0为不增长)[,...n]}*21T-SQL语句创建数据库（续）<filegroup>::={FILEGROUPfilegroup_name[DEFAULT]<filespec>[,...n]}*22示例1例1．创建一个名字为“学生管理数据库”的数据库，其他选项均采用默认设置。

CREATEDATABASE学生管理数据库*23示例2CREATEDATABASERShDBON(NAME=RShDB_Data,FILENAME='D:\RShDB_Data\RShDB_Data.mdf',SIZE=10,MAXSIZE=30,FILEGROWTH=5)LOGON(NAME=RShDB_log,FILENAME='D:\RShDB_Data\RShDB_log.ldf',SIZE=3,MAXSIZE=12,FILEGROWTH=2)*24示例3CREATEDATABASEstudentsONPRIMARY(NAME=students,FILENAME='F:\Data\students.mdf',SIZE=3MB,MAXSIZE=UNLIMITED),(NAME=students_data1,FILENAME='D:\Data\students_data1.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)*25示例3（续）LOGON(NAME=students_log,

FILENAME='F:\Data\students_log.ldf',SIZE=2MB,MAXSIZE=6MB,FILEGROWTH=10%)*263查看和设置数据库选项数据库选项是指在数据库范围内有效的一些参数，用于控制这个数据库的某些特性和行为。所有的数据库选项只能取True或False。*27设置数据库选项窗口*284修改数据库扩大数据库空间；缩小数据库空间；添加和删除文件。*29扩大数据库空间扩大数据库空间有两种方法，扩大数据库中已有文件的大小为数据库添加新的文件这两种方法均可在SQLServerManagementStudio中用图形化的方法实现，也可以用T-SQL语句实现。*30修改数据库的语句ALTERDATABASEdatabase_name{

<add_or_modify_files>}<add_or_modify_files>::={

ADDFILE<filespec>[,...n]--添加数据文件

[TOFILEGROUP{filegroup_name|DEFAULT}]

|ADDLOGFILE<filespec>[,...n]—添加日志文件

|MODIFYFILE<filespec>--修改文件*31示例1例1．为“RShDB”数据库添加一个新的数据文件，逻辑文件名为：RShDB_Data2，物理存储位置为：E:\Data文件夹下，物理文件名为：RShDB_Data2.ndf,初始大小为6MB，不自动增长。ALTERDATABASERShDBADDFILE(NAME=RShDB_Data2,FILENAME='E:\Data\RShDB_Data2.ndf',SIZE=6MB,FILEGROWTH=0)*32示例2例2．增加students数据库中students文件的初始大小，将其初始大小改为8MB。ALTERDATABASEstudentsMODIFYFILE(NAME=students,SIZE=8MB)*33示例3例3.为students数据库加添加一个新的日志文件。ALTERDATABASEstudentsADDLOGFILE(NAME=students_log1,FILENAME='D:\Data\students_log1.ldf',SIZE=2MB,FILEGROWTH=1MB,MAXSIZE=10MB)*34收缩数据库空间就是释放数据库中未使用的空间。可以收缩数据文件和事务日志文件。可以成组或单独地手动收缩数据库文件。可以通过设置数据库选项，使其按照指定的间隔自动收缩。*35收缩数据库（续）文件的收缩都是从末尾开始的。如果文件中被释放的空间部分包含使用过的页，则数据库引擎先将这些页重新放置到保留的空间部分，然后再进行收缩。只能将数据库收缩到没有剩余的可用空间为止。*36自动收缩只需将该数据库的“自动收缩”（AUTO_SHRINK）选项设置为“ON”即可（在数据库属性窗口的“选项”对应的窗口中，在“自动”部分的“自动收缩”选项），数据库引擎会定期检查数据库空间的使用情况，并减少数据库中文件的大小。*37手动收缩两种收缩方式：收缩数据库中某个文件的大小，收缩整个数据库的大小。当收缩整个数据库空间的大小时，收缩后各文件的大小不能小于创建这些文件时指定的初始大小。*38收缩整个数据库大小的SQL语句DBCCSHRINKDATABASE('database_name'|database_id|0--当前正在使用的数据库[,target_percent]--剩余百分比[,{NOTRUNCATE|TRUNCATEONLY}])NOTRUNCATE|TRUNCATEONLY：释放空间是否给操作系统*39收缩指定文件大小的SQL语句DBCCSHRINKFILE({'file_name'|file_id}{[,EMPTYFILE]--清空该文件|[[,target_size]--目标大小[,{NOTRUNCATE|TRUNCATEONLY}]]})*40数据库文件使用情况数据文件按比例填充数据，各文件存储的数据量与文件的可用空间成正比，这种方式使得所有数据文件几乎是同时被填满的。日志文件是填充到满的策略,即先填充第一个日志文件，第一个日志文件填满后，再填充第二个日志文件，依此类推。*41添加文件向数据库添加文件时，可以指定文件的初始大小存放位置增长方式等属性同创建数据库时指定文件属性方法相同。*42删除文件删除数据或日志文件是将该文件从数据库中删除。只有当文件中没有数据或事务日志信息，文件完全为空时，才可以从数据库中删除该文件。*43删除文件的T-SQL语句ALTERDATABASEdatabase_nameREMOVEFILElogical_file_name例1.删除students数据库中的students_data1文件。ALTERDATABASEstudentsREMOVEFILEstudents_data1*445删除数据库删除一个数据库，也就删除了该数据库的全部对象，包括数据文件和日志文件也被从磁盘上删除。一旦删除数据库，它即被永久删除。*45删除数据库的T-SQL语句DROPDATABASEdatabase_name[,…n]例1．删除studnets数据库。DROPDATABASEstudents例2．删除“学生管理数据库”和“RShDB”数据库。DROPDATABASE学生管理数据库,RShDB*466分离和附加数据库可以实现将数据库从一台计算机移动到另一台计算机，而不需要重新创建数据库。可以分离数据库的数据文件和事务日志文件，然后将它们重新附加到其他SQLServer实例中。*47分离数据库分离数据库是指将数据库从SQLServer实例中删除，但不删除数据库中的数据文件和事务日志文件。分离数据库会保持数据库的数据文件和日志文件的完整和一致。用户不能使用被分离的数据库。*48分离数据库的语句sp_detach_db[@dbname=]'dbname'

[,[@skipchecks=]'skipchecks'][,[@KeepFulltextIndexFile=]'KeepFulltextIndexFile'][@skipchecks=]'skipchecks'：默认为NULL。True：跳过“更新统计信息”；false：运行“更新统计信息”，则指定。[@KeepFulltextIndexFile=]‘KeepFulltextIndexFile’:是否保留“全文目录”。*49示例例．分离students数据库，并跳过“更新统计信息”。EXECsp_detach_db'students','true'*50附加数据库将分离的数据库重