SQLserver数据库创建管理

1、数据库系统原理实践SQL Server 20052 创建和管理数据库1创建和管理数据库o1 SQL Server1 SQL Server数据库概述数据库概述o2 2 创建数据库创建数据库o3 3 修改数据库修改数据库o4 4 删除数据库删除数据库o5 5 分离和附加数据库分离和附加数据库2021年11月21日0时56分21 SQL Server数据库概述o在一个实例中在一个实例中可以可以创建多个数据库，创建多个数据库，o每个数据库在物理上和逻辑上都是独每个数据库在物理上和逻辑上都是独立的。立的。o每个数据库存储相关的数据每个数据库存储相关的数据。2021年11月21日0时56分3数据库分类o

2、SQL ServerSQL Server将数据库分为两大类：将数据库分为两大类：n 系统数据库系统数据库n 用户数据库。用户数据库。o 系统数据库系统数据库是是SQL ServerSQL Server数据库管理系数据库管理系统自动创建和维护的，这些数据库用于统自动创建和维护的，这些数据库用于保存维护系统正常运行的信息保存维护系统正常运行的信息。o 用户数据库用户数据库保存与用户的业务有关的数保存与用户的业务有关的数据，用户数据库中的数据是用户来维护据，用户数据库中的数据是用户来维护的。的。2021年11月21日0时56分4数据库分类示意图2021年11月21日0时56分51.1 系统数据库o

3、mastermaster：是最重要的数据库，用于记录：是最重要的数据库，用于记录SQL SQL ServerServer系统中所有系统级信息系统中所有系统级信息。o msdbmsdb：保存关于调度报警、作业、操作员等：保存关于调度报警、作业、操作员等信息信息。o modelmodel：是用户数据库的模板，其中包含所：是用户数据库的模板，其中包含所有用户数据库的共享信息。当创建有用户数据库的共享信息。当创建用户用户数据数据库时，系统自动将库时，系统自动将modelmodel数据库中的全部内数据库中的全部内容复制到新建数据库中容复制到新建数据库中。o tempdbtempdb：临时数据库，用于存储

4、临时：临时数据库，用于存储临时信息。信息。2021年11月21日0时56分61.2 SQL Server数据库的组成o由若干个文件组成，由若干个文件组成，o文件被分为两类：文件被分为两类：n 数据文件数据文件：用于存放数据库中的数据用于存放数据库中的数据信息信息。n 日志文件日志文件：用于存放对数据库的操作用于存放对数据库的操作记录记录。2021年11月21日0时56分7数据库文件o每个每个数据库都包含数据库都包含：n 一个一个主数据文件主数据文件n 一个或多个一个或多个日志文件日志文件n 零个或多个零个或多个辅助数据文件辅助数据文件o每个每个文件都有文件都有：n 物理文件名物理文件名n 逻辑

5、文件名逻辑文件名2021年11月21日0时56分8数据页o 在在SQL Server 2005SQL Server 2005中，数据的存储单位中，数据的存储单位是是页页（PagePage，也称为数据页）。，也称为数据页）。o 一页是一块一页是一块8KB8KB（8 810241024字节，其中用字节，其中用80608060个字节存放数据，另外的个字节存放数据，另外的132132个字节个字节存放系统信息）的连续磁盘空间，存放系统信息）的连续磁盘空间，o 页是存储数据的最小单位。页是存储数据的最小单位。o 页的大小决定了数据库表中一行数据的页的大小决定了数据库表中一行数据的最大大小最大大小。2021

6、年11月21日0时56分9o不允许表中的一行数据存储在不同页不允许表中的一行数据存储在不同页上，即行不能跨页存储。上，即行不能跨页存储。o因此表中一行数据的大小不能超过因此表中一行数据的大小不能超过80608060字节字节。o例：例：一个数据表有一个数据表有1000010000行数据，每行数据，每行行30003000字节。则每个数据页可存放字节。则每个数据页可存放两行数据，此表需要的空间为：两行数据，此表需要的空间为：(10000/2) (10000/2) * * 8KB = 40MB 8KB = 40MB2021年11月21日0时56分10数据存储示意图2021年11月21日0时56分111

7、.3 数据文件和日志文件o数据文件数据文件n 用于存放数据库数据。用于存放数据库数据。n 分为：主数据文件和辅助数据文件分为：主数据文件和辅助数据文件o事务日志文件事务日志文件n 推荐扩展名为推荐扩展名为.ldf.ldf。n 用于存放恢复数据库的所有日志信息。用于存放恢复数据库的所有日志信息。n 每个数据库必须至少有一个日志文件。每个数据库必须至少有一个日志文件。n 最小为最小为512KB512KB，但最好不要小于，但最好不要小于1MB1MB。2021年11月21日0时56分12数据文件o 主数据文件：主数据文件：n 推荐扩展名是推荐扩展名是. .mdfmdfn 包含数据库的系统信息，并可存放

8、用户数据包含数据库的系统信息，并可存放用户数据n 每个数据库都有且仅有一个主数据文件。每个数据库都有且仅有一个主数据文件。o 辅助数据文件：辅助数据文件：n 推荐扩展名是推荐扩展名是. .ndfndf。n 一个数据库可以包含一个数据库可以包含0 0 n n个个辅助数据文件辅助数据文件。n 辅助数据文件可以辅助数据文件可以分别分别建立建立不同的不同的磁盘上。磁盘上。2021年11月21日0时56分131.4数据库文件的属性o文件名及其位置文件名及其位置o初始大小初始大小o增长方式增长方式o最大大小最大大小2021年11月21日0时56分14文件名及其位置o每个数据文件和日志文件都具有一个每个数据

9、文件和日志文件都具有一个逻辑文件名和物理存储位置（包括物逻辑文件名和物理存储位置（包括物理文件名）。理文件名）。o一般情况下，如果有多个数据文件，一般情况下，如果有多个数据文件，则则为了获得更好的性能，建议将文件为了获得更好的性能，建议将文件分散存储在多个磁盘上分散存储在多个磁盘上。2021年11月21日0时56分15初始大小o主数据文件的初始大小时，其大小不主数据文件的初始大小时，其大小不能小于能小于modelmodel数据库主文件的大小数据库主文件的大小。o因为系统是将因为系统是将modelmodel数据库中主数据数据库中主数据文件的内容拷贝到用户数据库的主数文件的内容拷贝到用户数据库的主

10、数据文件上。据文件上。2021年11月21日0时56分16增长方式o如果需要的话，可以指定文件是否自如果需要的话，可以指定文件是否自动增长。动增长。o默认配置为自动增长，即当数据库的默认配置为自动增长，即当数据库的空间用完后，系统自动扩大数据库的空间用完后，系统自动扩大数据库的空间，以防止由于数据库空间用完而空间，以防止由于数据库空间用完而造成的不能进行数据操作的错误。造成的不能进行数据操作的错误。2021年11月21日0时56分17最大大小o指文件增长的最大空间限制。指文件增长的最大空间限制。o默认情况是无限制。默认情况是无限制。o建议设定允许文件增长的最大空间大建议设定允许文件增长的最大空

11、间大小，小，以防止因以防止因文件无限制增长文件无限制增长而将而将磁磁盘空间用完。盘空间用完。2021年11月21日0时56分182 创建数据库o可以在可以在SQL Server Management SQL Server Management StudioStudio（SSMSSSMS）工具中用图形化的）工具中用图形化的方式实现，方式实现，o也可以通过也可以通过T-SQLT-SQL语句实现语句实现。2021年11月21日0时56分19T-SQL语句创建数据库CREATE DATABASE database_name CREATE DATABASE database_name ON ON -数据

12、文件数据文件 PRIMARY ,.n PRIMARY ,.n , ,.n , ,.n LOG ON LOG ON 日志文件日志文件 ,.n ,.n 2021年11月21日0时56分20T-SQL语句创建数据库（续） := := ( NAME = logical_file_name , ( NAME = logical_file_name , -逻辑名逻辑名 FILENAME = os_file_name | FILENAME = os_file_name | filestream_path filestream_path -物理名物理名 , SIZE = size KB | MB | GB |

13、 TB , SIZE = size KB | MB | GB | TB -初始大小初始大小 , MAXSIZE = max_size KB | MB | GB | TB | , MAXSIZE = max_size KB | MB | GB | TB | UNLIMITED UNLIMITED -最大大小最大大小 , FILEGROWTH = growth_increment KB | MB | GB | , FILEGROWTH = growth_increment KB | MB | GB | TB | % TB | % -增长方式，增长方式，0 0为不增长为不增长) ,.n ) ,.n

14、2021年11月21日0时56分21T-SQL语句创建数据库（续） := := FILEGROUP filegroup_nameFILEGROUP filegroup_name DEFAULT DEFAULT ,.n ,.n 2021年11月21日0时56分22示例1o例例1 1创建一个名字为创建一个名字为“学生管理数学生管理数据库据库”的数据库，其他选项均采用默的数据库，其他选项均采用默认设置。认设置。CREATE DATABASE CREATE DATABASE 学生管理数据库学生管理数据库2021年11月21日0时56分23示例2CREATE DATABASE RShDBCREATE D

15、ATABASE RShDBON ON ( NAME = RShDB_Data, ( NAME = RShDB_Data, FILENAME = D:RShDB_DataRShDB_Data.mdf, FILENAME = D:RShDB_DataRShDB_Data.mdf, SIZE = 10, SIZE = 10, MAXSIZE = 30, MAXSIZE = 30,FILEGROWTH = 5 )FILEGROWTH = 5 )LOG ONLOG ON( NAME = RShDB_log,( NAME = RShDB_log, FILENAME = D:RShDB_DataRShDB_

16、log.ldf, FILENAME = D:RShDB_DataRShDB_log.ldf, SIZE = 3, SIZE = 3, MAXSIZE = 12, MAXSIZE = 12, FILEGROWTH = 2 ) FILEGROWTH = 2 )2021年11月21日0时56分24示例3CREATE DATABASE studentsCREATE DATABASE studentsON PRIMARYON PRIMARY ( NAME = students, ( NAME = students, FILENAME = F:Datastudents.mdf, FILENAME = F:

17、Datastudents.mdf, SIZE = 3MB, SIZE = 3MB, MAXSIZE = UNLIMITED), MAXSIZE = UNLIMITED), ( NAME = students_data1, ( NAME = students_data1, FILENAME = D:Datastudents_data1.ndf, FILENAME = D:Datastudents_data1.ndf, SIZE = 5MB, SIZE = 5MB, MAXSIZE = 10MB, MAXSIZE = 10MB, FILEGROWTH = 1MB FILEGROWTH = 1MB

18、) )2021年11月21日0时56分25示例3（续）LOG ONLOG ON ( NAME = students_log, ( NAME = students_log, FILENAME = F:Datastudents_log.ldf,FILENAME = F:Datastudents_log.ldf, SIZE = 2MB, SIZE = 2MB, MAXSIZE = 6MB, MAXSIZE = 6MB, FILEGROWTH = 10% FILEGROWTH = 10% ) )2021年11月21日0时56分263查看和设置数据库选项o数据库选项是指在数据库范围内有效数据库选项是指在

19、数据库范围内有效的一些参数，的一些参数，o用于控制这个数据库的某些特性和行用于控制这个数据库的某些特性和行为。为。o所有的数据库选项只能取所有的数据库选项只能取TrueTrue或或FalseFalse。2021年11月21日0时56分27设置数据库选项窗口2021年11月21日0时56分284 修改数据库o扩大数据库空间；扩大数据库空间；o缩小数据库空间；缩小数据库空间；o添加和删除文件添加和删除文件。2021年11月21日0时56分29扩大数据库空间o扩大数据库空间有两种方法，扩大数据库空间有两种方法，n 扩大数据库中已有文件的大小扩大数据库中已有文件的大小n 为数据库添加新的文件为数据库添

20、加新的文件o这两种方法均可在这两种方法均可在SQL Server SQL Server Management StudioManagement Studio中用图形化的方中用图形化的方法实现，也可以用法实现，也可以用T-SQLT-SQL语句实现。语句实现。2021年11月21日0时56分30修改数据库的语句ALTER DATABASE ALTER DATABASE database_name database_name := := ADD FILE ,.n ADD FILE ,.n -添加数据文件添加数据文件 TO FILEGROUP filegroup_name | DEFAULT TO F

21、ILEGROUP filegroup_name | DEFAULT | ADD LOG FILE ,.n | ADD LOG FILE ,.n 添加日志文件添加日志文件 | MODIFY FILE | MODIFY FILE -修改文件修改文件2021年11月21日0时56分31示例1例例1 1为为“RShDBRShDB”数据库添加一个新的数据文件，逻辑数据库添加一个新的数据文件，逻辑文件名为：文件名为：RShDB_Data2RShDB_Data2，物理存储位置为：，物理存储位置为：E:DataE:Data文件夹下，物理文件名为：文件夹下，物理文件名为：RShDB_Data2.ndf,RShD

22、B_Data2.ndf,初始大小为初始大小为6MB6MB，不自动增长。，不自动增长。ALTER DATABASE RShDBALTER DATABASE RShDBADD FILE (ADD FILE ( NAME = RShDB_Data2, NAME = RShDB_Data2, FILENAME = E:DataRShDB_Data2.ndf, FILENAME = E:DataRShDB_Data2.ndf, SIZE = 6MB, SIZE = 6MB, FILEGROWTH = 0 ) FILEGROWTH = 0 )2021年11月21日0时56分32示例2例例2 2增加增加st

23、udentsstudents数据库中数据库中studentsstudents文件文件的初始大小，将其初始大小改为的初始大小，将其初始大小改为8MB8MB。ALTER DATABASE students ALTER DATABASE students MODIFY FILE (MODIFY FILE ( NAME = students, NAME = students, SIZE = 8MB ) SIZE = 8MB )2021年11月21日0时56分33示例3例例3.3.为为studentsstudents数据库加添加一个新的日志文件数据库加添加一个新的日志文件。ALTER DATABASE

24、students ALTER DATABASE students ADD LOG FILE (ADD LOG FILE ( NAME = students_log1, NAME = students_log1, FILENAME = D:Datastudents_log1.ldf, FILENAME = D:Datastudents_log1.ldf, SIZE = 2MB, SIZE = 2MB, FILEGROWTH = 1MB, FILEGROWTH = 1MB, MAXSIZE = 10MB ) MAXSIZE = 10MB )2021年11月21日0时56分34收缩数据库空间o就是释

25、放数据库中未使用的空间。就是释放数据库中未使用的空间。o可以收缩数据文件和事务日志文件可以收缩数据文件和事务日志文件。o可以成组或单独地手动收缩数据库文可以成组或单独地手动收缩数据库文件件。o可以通过设置数据库选项，使其按照可以通过设置数据库选项，使其按照指定的间隔自动收缩。指定的间隔自动收缩。2021年11月21日0时56分35收缩数据库（续）o文件的收缩都是从末尾开始的文件的收缩都是从末尾开始的。o如果文件中被释放的空间部分包含使如果文件中被释放的空间部分包含使用过的页，则数据库引擎先将这些页用过的页，则数据库引擎先将这些页重新放置到保留的空间部分，然后再重新放置到保留的空间部分，然后再进

26、行收缩。进行收缩。o只能将数据库收缩到没有剩余的可用只能将数据库收缩到没有剩余的可用空间为止。空间为止。2021年11月21日0时56分36自动收缩o 只需将该数据库的只需将该数据库的“自动收缩自动收缩”（AUTO_SHRINKAUTO_SHRINK）选项设置为）选项设置为“ONON”即可（即可（在数据库属性窗口的在数据库属性窗口的“选项选项”对应的窗对应的窗口中，在口中，在“自动自动”部分的部分的“自动收缩自动收缩”选项），选项），o 数据库引擎会定期检查数据库空间的使数据库引擎会定期检查数据库空间的使用情况，并减少数据库中文件的大小。用情况，并减少数据库中文件的大小。2021年11月21日

27、0时56分37手动收缩o两种收缩方式：两种收缩方式：n 收缩数据库中某个文件的大小，收缩数据库中某个文件的大小，n 收缩整个数据库收缩整个数据库的的大小。大小。o当收缩整个数据库空间的大小时，收当收缩整个数据库空间的大小时，收缩后各文件的大小不能小于创建这些缩后各文件的大小不能小于创建这些文件时指定的初始大小。文件时指定的初始大小。2021年11月21日0时56分38收缩整个数据库大小的SQL语句DBCC SHRINKDATABASE DBCC SHRINKDATABASE ( database_name | database_id ( database_name | database_id

28、| 0 | 0 -当前正在使用的数据库当前正在使用的数据库 ,target_percent ,target_percent -剩余百分比剩余百分比 , NOTRUNCATE | TRUNCATEONLY , NOTRUNCATE | TRUNCATEONLY ) )NOTRUNCATE | TRUNCATEONLYNOTRUNCATE | TRUNCATEONLY：释放空间是：释放空间是否给操作系统否给操作系统2021年11月21日0时56分39收缩指定文件大小的SQL语句DBCC SHRINKFILE DBCC SHRINKFILE ( ( file_name | file_id file_

29、name | file_id , EMPTYFILE , EMPTYFILE -清空该文件清空该文件 | ,target_size | ,target_size -目标大小目标大小 , NOTRUNCATE | TRUNCATEONLY , NOTRUNCATE | TRUNCATEONLY ) )2021年11月21日0时56分40数据库文件使用情况o数据文件数据文件按比例填充数据，各文件存储按比例填充数据，各文件存储的数据量与文件的可用空间成正比，这的数据量与文件的可用空间成正比，这种方式使得所有数据文件几乎是同时被种方式使得所有数据文件几乎是同时被填满的填满的。o日志文件日志文件是填充到

30、满的策略是填充到满的策略, ,即先填充第即先填充第一个日志文件，第一个日志文件填满后一个日志文件，第一个日志文件填满后，再填充第二个日志文件，依此类推，再填充第二个日志文件，依此类推。2021年11月21日0时56分41添加文件o向数据库添加文件时，可以指定文件向数据库添加文件时，可以指定文件的的n 初始大小初始大小n 存放位置存放位置n 增长方式等属性增长方式等属性o同创建数据库时指定文件属性方法相同创建数据库时指定文件属性方法相同。同。2021年11月21日0时56分42删除文件o删除数据或日志文件是将该文件从数删除数据或日志文件是将该文件从数据库中删除。据库中删除。o只有当文件中没有数据

31、或事务日志信只有当文件中没有数据或事务日志信息，文件完全为空时，才可以从数据息，文件完全为空时，才可以从数据库中删除该文件。库中删除该文件。2021年11月21日0时56分43删除文件的T-SQL语句ALTER DATABASE database_nameALTER DATABASE database_name REMOVE FILE logical_file_name REMOVE FILE logical_file_nameo例例1.1.删除删除studentsstudents数据库中的数据库中的students_data1students_data1文件。文件。ALTER DATABAS

32、E studentsALTER DATABASE students REMOVE FILE students_data1 REMOVE FILE students_data12021年11月21日0时56分445 删除数据库o删除一个数据库，也就删除了该数据删除一个数据库，也就删除了该数据库的全部对象，包括数据文件和日志库的全部对象，包括数据文件和日志文件也被从磁盘上删除。文件也被从磁盘上删除。o一旦删除数据库，它即被永久删除一旦删除数据库，它即被永久删除。2021年11月21日0时56分45删除数据库的T-SQL语句DROP DATABASE database_name,nDROP DATA

33、BASE database_name,no例例1 1删除删除studnetsstudnets数据库。数据库。DROP DATABASE studentsDROP DATABASE studentso例例2 2删除删除“学生管理数据库学生管理数据库”和和“RShDBRShDB”数据库。数据库。DROP DATABASE DROP DATABASE 学生管理数据库学生管理数据库, RShDB, RShDB2021年11月21日0时56分466 分离和附加数据库o可以实现将数据库从一台计算机移动可以实现将数据库从一台计算机移动到另一台计算机，而不需要重新创建到另一台计算机，而不需要重新创建数据库。数

34、据库。o可以分离数据库的数据文件和事务日可以分离数据库的数据文件和事务日志文件，然后将它们重新附加到其他志文件，然后将它们重新附加到其他SQL ServerSQL Server实例中实例中。2021年11月21日0时56分47分离数据库o分离数据库是指将数据库从分离数据库是指将数据库从SQL SQL ServerServer实例中删除，但不删除数据实例中删除，但不删除数据库中的数据文件和事务日志文件。库中的数据文件和事务日志文件。o分离数据库会保持数据库的数据文件分离数据库会保持数据库的数据文件和日志文件的完整和一致和日志文件的完整和一致。o用户不能使用用户不能使用被被分离分离的的数据库数据库

35、。2021年11月21日0时56分48分离数据库的语句sp_detach_dbsp_detach_db dbnamedbname= = dbnamedbname , , skipchecksskipchecks= = skipchecksskipchecks , , KeepFulltextIndexFileKeepFulltextIndexFile= = KeepFulltextIndexFileKeepFulltextIndexFile o skipchecksskipchecks = = skipchecksskipchecks ：默认为：默认为 NULL NULL。TrueTrue：跳

36、过跳过“更新统计信息更新统计信息”；falsefalse：运行运行“更新统计信息更新统计信息”，则指定。，则指定。o KeepFulltextIndexFileKeepFulltextIndexFile = = KeepFulltextIndexFileKeepFulltextIndexFile:是否保留是否保留“全文目录全文目录”。2021年11月21日0时56分49示例o例分离例分离studentsstudents数据库，并跳过数据库，并跳过“更新统计信息更新统计信息”。 EXEC sp_detach_db students, true EXEC sp_detach_db students, true2021年11月21日0时56分50附加数据库o将分离的数据库重新附加到数据库管将分离的数据库重新附加到数据库管理