使用T-SQL语句管理数据库.ppt

1、第五节 使用T-SQL语句管理数据库,掌握创建、删除、分离和附加数据库操作。 使用SQL语句创建数据库、修改数据库设置，结合上机实战熟练相关操 作。,创建数据库CREATE DATABASE语句 修改数据库设置ALTER DATABASE语句 删除数据库 分离和附加数据库,创建数据库CREATE DATABASE语句,CREATE DATABASE语句格式如下： CREATE DATABASE database_name ON ,.n , ,.n LOG ON ,.n COLLATE collation_name FOR LOAD | FOR ATTACH := PRIMARY ( NAME

2、= logical_file_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) ,.n := FILEGROUP filegroup_name ,.n ,在解释CREATE DATABASE语句之前，首先说明一下SQL语法中各种符号和书写规则的意义。 语句中的大写表示SQL关键字，小写表示语句参数。例如，创建数据库语句中CREATE DATABASE为关键字，database_name则为语句参数。在实际使用时，SQL语句

3、不区分大小写。 中为可选内容，如 COLLATE collation_name 。 中为必选内容，如 max_size | UNLIMITED 。 用|分隔的内容一次只能选择一项，如 FOR LOAD | FOR ATTACH 表示可选择FOR LOAD或FOR ATTACH，但不能同时使用。 中为需要另外说明的内容，:=后即为其语法内容。例如，CREATE DATABASE语句中的表示需要另外说明，其内容在 :=之后。 ,.n 用于说明某项内容可设置多个，使用逗号（,）分隔。例如， ,.n 表示可设置多个，如,。,CREATE DATABASE语句参数,database_name datab

4、ase_name用于指定数据库名称，最多128个字符。如果没有指定事务日志文件的逻辑名称，SQL Server在数据库名称后添加后缀来获得事务日志文件逻辑名称，此时的数据库名称最多为123个字符，这样可保证生成的事务日志文件逻辑名称在128个字符之内。 ON ON用于指定数据库的数据文件。 一个数据库的数据文件可分为主数据文件和辅助数据文件，主数据文件的扩展名建议使用.mdf，辅助数据文件的扩展名建议使用.ndf。一个数据库允许有多个数据文件，但主数据文件只能有一个，并且必须有一个。 LOG ON LOG ON用于指定数据库的事务日志文件，日志文件的扩展名建议使用.ldf，一个数据库必须至少有

5、一个事务日志文件。如果在创建数据库时没有指定日志文件，SQL Server则会自动为数据库创建一个日志文件，其大小为所有数据文件大小总和的25%。日志文件最小为512KB。,COLLATE COLLATE用于指定数据库的默认排序规则。排序规则名称既可以是Windows排序规则名称，也可以是SQL排序规则名称，如果没有指定排序规则，则将SQL Server服务器的默认排序规则作为数据库的排序规则。 FOR LOAD和FOR ATTACH FOR LOAD用于与早期版本的SQL Server兼容，SQL Server 7.0及以后版本不需要该子句，因为RESTORE语句可以作为还原操作的一部分重新

6、创建数据库。 FOR ATTACH用于从指定文件附加数据库。 PRIMARY 用于指出其后的文件属于主文件组。主文件组中的第一个文件为主数据文件，如果没有指定PRIMARY，则CREATE DATABASE语句中列出的第一个文件作为主数据文件。 NAME = logical_file_name logical_file_name为数据文件的逻辑名称，在附加数据库（FOR ATTACH）时，不需要指定NAME参数。逻辑名称在数据库中必须惟一。,FILENAME = os_file_name 用于指定物理文件名，即操作系统文件名。 SIZE = size 用于指定文件大小。如果主数据文件没有指定大

7、小，SQL Server将使用model数据库主数据文件的大小作为其大小。如果辅助数据文件或日志文件没有指定大小，则默认文件大小为1 MB。size的单位可以是KB、MB（默认单位）、GB或TB。size的最小值为512 KB。 MAXSIZE =max_size | UNLIMITED 用于指定文件的最大容量。UNLIMITED表示不限制大小。 FILEGROWTH = growth_increment 用于指定文件的增长数量。growth_increment可以是数值或百分比，如果是数值，则应为一个整数，并可用MB（默认单位）、KB、GB、TB作为单位。如果没有指定文件增长量，默认值为10

8、%，最小值为64 KB，增长量为0则表示不增长。 FILEGROUP filegroup_name 用于指定文件组。SQL Server在创建数据库时，自动创建主文件组，其名称为PRIMARY。一个数据文件如果没有指定文件组，则默认属于PRIMARY。,CREATE DATABASE语句应用举例,采用默认设置创建数据库 下面的语句将使用默认设置创建一个名称为test1的数据库。 CREATE DATABASE test1 该语句创建的数据库的文件默认保存在SQL Server 2000安装目录下的MSSQLData文件夹中，数据文件名称为test1.mdf，事务日志文件名称为test1_log

9、.ldf。 在创建数据库时指定主数据文件 下面的语句将创建一个名称为test2的数据库，并指定数据文件的相关设置。 CREATE DATABASE test2 ON (NAME=test2_data, FILENAME=d:sqldatatest2_data.mdf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=15%),创建包含多个文件的数据库 下面的语句创建一个名称为test3的数据库，该数据库包含3个文件组和6个文件。 CREATE DATABASE test3 ON PRIMARY (NAME=test3_data11,FILENAME=c:sqldatates

10、t3_data11.mdf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=15%), (NAME=test3_data12,FILENAME=c:sqldatatest3_data12.ndf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=1MB), FILEGROUP test3_files2 (NAME=test3_data21,FILENAME=d:sqldatatest3_data21.mdf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=5%), (NAME=test3_data22,FILENAME=d:sql

11、datatest3_data22.mdf, SIZE=2MB, MAXSIZE=UNLIMITED,FILEGROWTH=512KB), FILEGROUP test3_files3 (NAME=test3_data31,FILENAME=f:sqldatatest3_data31.mdf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=5%), (NAME=test3_data32, FILENAME=f:sqldatatest3_data32.mdf, SIZE=2MB, MAXSIZE=UNLIMITED,FILEGROWTH=512KB),在创建数据库时指定事务日

12、志文件 下面的语句将创建一个名称为test4的数据库，并指定了数据文件和日志文件的相关设置。 CREATE DATABASE test4 ON PRIMARY (NAME=test4_data,FILENAME=c:sqldatatest4 _data.mdf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=15%) LOG ON (NAME=test4_LOG,FILENAME=c:sqldatatest4 _LOG.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=5%),在创建数据库时指定排序规则 在创建数据库时可使用COLLATE指定排

13、序规则，如下所示。 CREATE DATABASE test5 ON PRIMARY (NAME=test5_data,FILENAME=c:sqldatatest 5_data.mdf, SIZE=3MB, MAXSIZE=5MB, FILEGROWTH=15%) LOG ON (NAME=test5_LOG,FILENAME=c:sqldata test5 _LOG.ldf, SIZE=2MB, MAXSIZE=5MB, FILEGROWTH=5%) COLLATE Chinese_PRC_CI_AI,修改数据库设置ALTER DATABASE语句,ALTER DATABASE语句可用于修

14、改数据库选项设置、添加或删除文件和文件组、修改文件或文件组属性。ALTER DATABASE语句语法格式如下。 ALTER DATABASE database ADD FILE ,.n TO FILEGROU P filegroup_name | ADD LOG FILE ,.n | REMOVE FILE logical_file_name | ADD FILEGROUP filegroup_name | REMOVE FILEGROUP filegroup_name | MODIFY FILE | MODIFY NAME = new_dbname | MODIFY FILEGROUP fi

15、legroup_name filegroup _property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,ALTER DATABASE语句的选项比较多，但一次只能选择其中一项，所以使 用时比较简单。下面分别讲解ALTER DATABASE语句中各个参数的意义。 ALTER DATABASE database ADD FILE ,.n TO FILEGROUP filegroup_name ADD LOG FILE REMOVE FILE logical_file_name ADD FILEGROUP filegroup_nam

16、e REMOVE FILEGROUP filegroup_name MODIFY FILE MODIFY NAME=new_dbname MODIFY FILEGROUP filegroup_name SET ,.n WITH COLLATE ,ALTER DATABASE database 参数database为要修改的数据库的名称。 ADD FILE ,.n TO FILEGROUP filegroup_name ADD FILE用于指定要为数据库添加的数据文件，文件格式如下， 其中的参数与CREATEDABASE语句相同，这里不再重复介绍。 ( NAME = logical_file_n

17、ame , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) 默认情况下，添加的数据文件属于PRIMARY文件组。如果要将文件添加到其他文 件组，则需使用TO FILEGROUP指定文件组，filegroup_name为文件组名称。 COLLATE 用于修改数据库排序规则。,ADD LOG FILE 为数据库指定要添加的事务日志文件，为文件格式。 REMOVE FILE logical_file_name 指定要删除的文件，logica

18、l_file_name为要删除文件的逻辑名称。删除文 件时，会从数据库中删除该文件的描述信息，并删除其物理文件。但应注意 只有在文件为空时才能删除。 ADD FILEGROUP filegroup_name 为数据库添加新的文件组，filegroup_name为添加的文件组名称。 REMOVE FILEGROUP filegroup_name 删除指定的文件组，只有在文件组中不包含文件时才能删除。,MODIFY FILE ,用于修改数据库文件属性，包括FILENAME、SIZE、FILEGROWTH和MAXSIZE等 属性，但一次只能更改一种属性。文件格式内容如下。 ( NAME = logi

19、cal_file_name , NEWNAME = new_logical_name , FILENAME = os_file_name , SIZE = size , MAXSIZE = max_size | UNLIMITED , FILEGROWTH = growth_increment ) 在修改文件属性时，必须指明NAME参数，以便确定要修改属性的数据库文件。 如果要修改文件大小，则新的size值必须大于文件当前大小。 在修改数据库文件属性之前，可以使用系统存储过程sp_helpfile来查看数据 库有哪些文件以及文件的属性。 例如，下面的语句用于查询数据库test5的文件及其属性。

20、 USE test5 EXEC sp_helpfile 下图所示显示了上述语句在查询分析器中的执行结果。,MODIFY NAME=new_dbname 用于修改数据库名称，new_dbname为新的名称。在修改数据库名称时，必须保证该数据库无人使用，否则不能修改数据库名称。 MODIFY FILEGROUP filegroup_name 用于修改数据库文件组属性或名称，filegroup_name为要修改的文件组的名称。文件组属性分别如下。 READONLY：将文件组设置为只读。 READWRITE：将文件组设置为可读写。 DEFAULT：将文件组设置为数据库的默认文件组。 一次只允许修改一个

21、属性。 例如，下面的SQL语句将数据库test3的文件组test3_files3设置为只读。 ALTER DATABASE test3 MODIFY FILEGROUP test3_files3 READONLY 下面的语句将数据库test3的文件组test3_files3的名称修改为test3_data_files3。 ALTER DATABASE test3 MODIFY FILEGROUP test3_files3 NAME = test3_data_files3,SET ,.n WITH ,用于修改数据库选项设置，选项参数个数如下。 | | | | := SINGLE_USER | R

22、ESTRICTED_USER | MULTI_USER | OFFLINE | ONLINE | READ_ONLY | READ_WRITE := ROLLBACK AFTER integer SECONDS | ROLLBACK IMMEDIATE | NO_WAIT, := CURSOR_CLOSE_ON_COMMIT ON | OFF | CURSOR_DEFAULT LOCAL | GLOBAL := AUTO_CLOSE ON | OFF | AUTO_CREATE_STATISTICS ON | OFF | AUTO_SHRINK ON | OFF | AUTO_UPDATE_S

23、TATISTICS ON | OFF := ANSI_NULL_DEFAULT ON | OFF | ANSI_NULLS ON | OFF | ANSI_PADDING ON | OFF | ANSI_WARNINGS ON | OFF | ARITHABORT ON | OFF | CONCAT_NULL_YIELDS_NULL ON | OFF | NUMERIC_ROUNDABORT ON | OFF | QUOTED_IDENTIFIER ON | OFF | RECURSIVE_TRIGGERS ON | OFF := RECOVERY FULL | BULK_LOGGED | SIMPLE | TORN_PAGE_DETECTION ON | OFF ,其中，WITH 用于指定在选项设置修改无法完成时，何时 执行回滚操作。ROLLBACK AFTER integer表示在指定的秒数后回滚，ROLLBACK IMMEDIATE表示立即回滚，NO_WAIT表示选项更改如果不能立即执行则回滚，即更 改请求失败。 其他选项设置与数据库属性对话框的“选项”选项卡内容