次课-管理篇-数据库管理基础.ppt

1、1,SQL SERVER2005的配置和基本操作,1、SQL SERVER2005配置管理器 2、SQL SERVER2005外围应用配置器 3、 SQL SERVER2005企业管理器的使用 4、模板资源管理器的使用 5、数据导入和导出 6、BCP工具 7、SQLCMD,复习上次课,2,第二篇 数据管理篇,3,本篇内容,4,第6章 数据库管理基础,6.1 了解 SQLSERVER中的数据库,6.2 数据库的组成数据文件和日志文件,6.3 创建数据库的方法,6.4 数据库的管理操作,5,6.1 了解SQLSERVER中的数据库,数据库是DBMS的核心，它包含了系统运行所需的全部数据。一个SQL

2、SERVER实例可以支持多个数据库，最多32767个。 SQLSERVER数据库分为系统数据库和用户数据库。,6,系统数据库,系统数据库是随SQLSERVER2005安装程序一起安装，用于协助SQLSERVER2005共同完成管理操作的数据库，它们是SQLSERVER2005运行的基础。 在SQLSERVER2005中默认有5个系统数据库：Master、Model、Msdb、Tempdb、Resource。,7,1、Master数据库: 存储系统级信息。 实例上的元数据（登录账户）、连接的服务器信息和系统配置设置； 所有其他数据库是否存在以及这些数据库文件的位置； SQLSERVER2005的

3、初始化信息。,Master数据库是最重要的系统数据库， 如果Master数据库不可用，则SQLSERVER2005无法启动。,注意：及时备份 Master数据库。,8,2、Model数据库 用作SQLSERVER2005实例上创建的所有数据库的模板。,3、msdb数据库 用于存储SQL SERVER AGENT代理所执行的计划、警报和作业。用于调度任务或故障排除。,9,4、Tempdb数据库,一个工作空间，用于保存临时对象或中间结果集。 每次启动SQL Server 时，都要重新创建temPdb ，以便系统启动时，保证该数据库总空的。 在断开连接时会自动删除临时表和存储过程。,注意：不能对该数

4、据库进行备份或还原操作。,10,5、Resource 数据库 只读数据库，包含SQL Server 2005 包括的系统对象。,Resource数据库的物理文件名为：Mssqlsystemresource.mdf，,默认的文件路径为： microsoft sqlservermssql.1mssqldata Mssqlsystemresource.mdf,数据库中默认的存储路径是可以更改的。,方法：右击数据库实例，在弹出的快捷菜单中选择“属性”命令， 打开“服务器属性”对话框。 在该对话框中，选择“数据库设置” ， 在“数据库默认位置”下修改数据库默认位置。,11,系统数据库小结,由于其特殊性，

5、建议一般情况下不要去修改系统数据库。 必要的时候可以隐藏系统数据库，降低DBA误操作带来的风险。 方法是：通过“工具|选项|常规”，选择“在对象资源管理器中隐藏系统对象”,12,6.2 数据库的组成数据文件和日志文件（重点）,子程序过程：Sub,SQL server 2005使用文件和文件组来管理物理数据库。 每个数据库至少对应2个系统文件：1个数据文件和1个日志文件。 数据文件包含数据和对象，例如表、索引、存储过程和视图等。 日志文件包含恢复数据库中的所有事务所需的信息。,13,1.数据库文件,主数据文件：记录数据库的初始信息，是数据库的起点，指向数据库中的其他文件。用户数据和对象可以存储在

6、此文件中，也可以存储在次要数据文件中。 1个数据库必须且只能有1个主数据文件。扩展名为.mdf,SQL server 2005数据库具有主要数据文件、 次要数据库文件和日志文件3种类型的文件。,14,次要数据库文件：是可选的。由用户定义并存储用户数据。 通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。 另外如果数据库超过单个Windows文件的最大大小，可以使用次要数据文件，这样能够保证数据库的继续增长。 扩展名为.ndf。,15,日志文件：保存用于恢复数据库的日志信息，每个数据库至少拥有一个日志文件。默认扩展名为.ldf。最少是1MB。,例如，可以创建一个简单的数

7、据库sales ，其中只包括1 个包含所有数据和对象的主要数据文件和1个包含事务日志信息的日志文件。,也可以创一个更复杂的数据库Orders ，其中包括1个主要数据文件和5 个次要数据文件。数据库中的数据和对象分散在所有6 个数据文件中，另外使用4 个日志文件包含事务日志信息。,16,技巧：可以通过在SQL server Management studiO”的“对象资源管理器”组件窗口中，右击某个数据库，选择“属性”命令，打开“数据库属性”对话框。在打开的对话框中，选择“文件”页，来查看数据库文件的具体情况。,17,默认情况下，数据和事务日志被放在同一个驱动器上的同一个路径下。这是为处理单磁盘

8、系统而采用的方法。 建议将数据和日志文件放在不同的磁盘上。,18,如果使用的是默认的安装选项，并且系统盘是C 盘，那么文件的存储路径一般是C ：Program FileMicrosoft SQL Server MSSQL .1 Data ”。,2、数据库文件的存储路径,如果多个SQL server 实例安装在一台计算机上运行，则每个实例都会使用不同的默认目录来保存在该实例中创建的一些数据库文件。,19,3、数据库文件的名称,在SQL server2005中，数据库文件包含有两个名称：逻辑名称和物理文件名。,逻辑名称是在所有Transact 一SQL 语句中引用物理文件所使用的名称。 逻辑文件名

9、须符合SQL server 标识符规则，而且在数据库中的逻辑文件名中必须是唯一的。,物理文件名是包括目录路径的物理文件名。 它必须符合操作系统文件的命名规则。,20,在“数据库属性”对话框中，可以看到逻辑名称和物理文件名。,区分这两种名称，对数据库的维护和开发都是非常有帮助的。 在以后的数库维护和开发过程中，如果涉及到数据库文件的名称，通常都要指明是逻辑名称还是物理文件名。,21,4 数据库文件的大小,SQL server 2005 数据库文件可以从它们最初指定的大小开始自动增长。 在定义文件时，可以指定文件的初始大小以及一个特定的增量。每次填充文件时，其大小均按此增量来增长。 每个文件还可以

10、指定一个最大值。如果没有指定，文件可以一直增长到用完磁盘上的所有空间。,22,6.2.3 文件组,文件组是数据文件的集合。,分为2类：主件组和用户定义文件组,主文件组 (primary)包含主数据文件和任何没有明确分配给 其他文件组的其他文件。系统表均分配在主文件组中。,用户定义文件组是用户在创建数据库的过程自定义的文件组。,23,如果没有指定默认文件组，则将主文件组作为默认文件组。,如果创建数据库时未指定文件组，则将视为属于默认文件组。,每个数据库中均有一个文件组被指定为默认文件组。,注意：日志文件不包括在文件组内。默认文件组可以更改，但系统对象和表仍然分配给PRIMARY文件组，而不是默认

11、的文件组。,24,创建和使用文件组必须遵守的规则： 一个文件或文件组不能由多个数据库使用 一个数据文件只能属于一个文件组。 主数据文件存储在主文件组中。 大多数数据库在只有1个数据文件和1个事务日志文件的情况下性能良好。 如果使用多个文件，请为附加文件创建第二个文件组，并将其设置为默认文件组。 在尽可能多的不同的可用本地物理磁盘上创建文件或文件组，以提高读写效率。 将在同一联接查询中使用的不同表置于不同的文件组中。 将经常访问的表和属于这些表的非聚集索引置于不同的文件组中。 日志文件不能存放在任何文件组中。,25,6.3 创建数据库的方法,两种方法： 直观利用“Managerment stud

12、io”创建（课本例题） 通过T-SQL语句创建（重点）,26,27,利用T-SQL语句,1、创建未指定文件的数据库，只需指定数据库的全局名称，而其他参数均采用系统默认值。 【实例6一1 】 ：创建一个Student 数据库，所有参数均取默认值。 ( l ）打开SQL server Management studio ，并连接到数据库引擎服务器。( 2 ）在SQL Server Management中，单击工具栏上的“新建查询”按钮，打开“新建查询”窗口。( 3 ）在该“新建查询”窗口中，输入以下Transact 一SQL 代码，并单击工具栏上的“执行”按钮，即可完成Student 数据库的创建

13、工作。 Transact 一SQL 代码如下：,28,use master-打开master系统数据库，因为她记录着数据库是否存在 Go-在查询分析器中可省略，但在命令提示符下不可省，表示执行命令 if exists(select name from sys.databases系统视图，保存着已存在数据库的信息 where name=student ) drop database student go create database student go,练习：创建数据库data1 Create database data1,29,30,2、创建指定数据文件和日志文件的数据库,【例】创建一个X

14、JGL数据库，该数据库的主文件逻辑名称为XJGL_data，物理文件名为XJGL.mdf，初始大小为3MB，最大尺寸为5MB，增长速度为10%；数据库的日志文件逻辑名称为XJGL_log，物理文件名为XJGL.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。文件存放在C:下。 在查询分析器中通过执行以下语句可以创建此数据库。,31,CREATE DATABASE XJGL ON -创建数据文件 ( NAME =XJGL_Data, -逻辑文件名 FILENAME = C:XJGL_Data.MDF, -文件的物理文件名 SIZE = 3, -初始大小 MAXSIZE=5, -最大

15、值 FILEGROWTH = 10% ) 增长速度 LOG ON创建日志文件 ( NAME =XJGL_Log, FILENAME = C:XJGL_Log.LDF, SIZE = 1, MAXSIZE=5, FILEGROWTH = 10%) GO,【例】创建数据库文件data2，该数据库的主文件的逻辑名称data2，物理名称为data2.mdf，文件初始大小3MB，最大大小不限，文件自动增长为2MB；数据库的日志文件逻辑名称为data2_log，物理文件名为data2log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%，数据文件和日志文件全部保存在e盘根目录下。,32,33

16、,3、创建指定多个数据文件和日志文件的数据库,【例】创建一个students数据库，该数据库有1个10MB、1个20MB的数据文件和2个10MB的日志文件。数据文件的逻辑名称为：student1和student2；物理名称为：student1.mdf和student2.ndf；主文件是student1，由PRIMARY指定；两个数据文件的最大尺寸分别为无限大和100MB,增长速度分别为10%和1MB。事务日志文件逻辑名称为studentlog1和 studentlog2, ，物理文件名为studentlog1.ldf和 studentlog2.ldf,最大尺寸分别为50MB,增长速度分别为1M

17、B。 数据文件存放在D:data下,日志文件存放在E:data 。 在查询分析器中通过执行以下语句可以创建此数据库。,create database students ON PRIMARY (NAME=STUDENT1, FILENAME=D:dataSTUDENT1.MDF, SIZE=10MB, MAXSIZE=UNLIMITED, FILEGROWTH=10%), (NAME=STUDENT2, FILENAME=D:dataSTUDENT2.NDF, SIZE=20MB, MAXSIZE=100MB, FILEGROWTH=1MB) LOG ON (NAME=STUDENTLOG1,

18、FILENAME=e:dataSTUDENTLOG1.LDF, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=1MB), (NAME=STUDENTLOG2, FILENAME=e:dataSTUDENTLOG2.LDF, SIZE=10MB, MAXSIZE=50MB, FILEGROWTH=1MB) GO,35,【例】创建一个data2数据库，该数据库有1个5MB、1个10MB的数据文件和2个5MB的日志文件。数据文件的逻辑名称为：data1和data2；物理名称为：data 1.mdf和data2.ndf；主文件是data1，由PRIMARY指定；两个数据文件的

19、最大尺寸分别为无限大和20MB,增长速度分别为15%和2MB。事务日志文件逻辑名称为datalog1和 datalog2, ，物理文件名为datalog1.ldf和 datalog2.ldf,最大尺寸分别为30MB,增长速度分别为1MB和10%。 数据文件存放在D:data下,日志文件存放在E:data 。 在查询分析器中通过执行以下语句可以创建此数据库。,36,4、带有默认文件组的数据库。 例：使用T-SQL语句创建课本Customers数据库,CREATE DATABASE Customers ON PRIMARY -表示创建的是主数据文件 ( NAME = Customers, FILE

20、NAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATACustomers.mdf, SIZE = 10, MAXSIZE = UNLIMITED, FILEGROWTH = 2 ),37,FILEGROUP DefaultGroup ( NAME = Customers_1, FILENAME = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATACustomers_1.ndf, SIZE = 3MB, MAXSIZE = UNLIMITED, FILEGROWTH = 1MB )

21、,LOG ON ( NAME = Customers_log, FILENAME = D:Customers_log.ldf, SIZE = 1MB, MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),【例】创建数据库文件s。数据库有两个文件组，primary和newgroup1。主文件组包括数据文件s1，其逻辑文件名s1，物理文件名s1_data.mdf，初始大小3MB，最大大小10MB，自动增长为10%；用户定义文件组newgroup1，包括主数据文件s2，其逻辑文件名s2，物理文件名s2_data.mdf，初始大小3MB，最大大小10MB，自动增长为10%；

22、次要数据文件s3，其逻辑文件名s3，物理文件名s3_data.ndf，初始大小3MB，最大大小不限，自动增长为1MB；日志文件s1，其逻辑文件名为s1log，物理文件名为s1log.ldf，初始大小3MB，最大大小10MB，自动增长为10%，所有文件保存在e盘sql文件夹下。,38,39,注意：数据文件和日志文件所在的目录必须事先都已经建立好。,例：有一个数据库，数据文件和日志文件的存储路径如下：,d:downloadsdatacustomer.mdf, e:downloadsdatacustomer.ldf,则创建数据库之前，d:downloadsdata文件夹和 e:downloadsda

23、tacustomer.ldf必须事先存在。否则会产生错误，创建数据库失败。,40,数据库创建技巧：,估计数据库的最大数据量，尽量大一些。 创建、修改或删除用户数据库后，应及时备份master数据库。,41,补充： 系统存储过程sp_helpdb,功能：报告有关指定数据库或所有数据库的信息。,语法： sp_helpdb dbname= name ,42,【例】显示XSCJ数据库信息。在查询分析器中运行如下命令： sp_helpdb XSCJ,【例】显示系统中的所有数据库信息。 在查询分析器中运行如下命令： sp_helpdb,43,补充： 系统存储过程sp_renamedb,功能：报告有关指定数

24、据库或所有数据库的信息。,语法： sp_renamedb dbname= old_name, newname= new_name,【例】将XJGL数据库重命名为XJGL1。在查询分析器中运行如下命令： sp_renamedb XJGL，XJGL1,44,数据库创建技巧：,估计数据库的最大数据量，尽量大一些。 创建、修改或删除用户数据库后，应及时备份master数据库。,45,ALTER DATABASE,在数据库中添加或删除文件和文件组。也可用于更改文件和文件组的属性，例如更改文件的名称和大小。,语法 ALTER DATABASE database ADD FILE ,.n TO FILEGR

25、OUP 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 filegroup_name filegroup_property | NAME = new_filegroup_name | SET ,.n WITH | COLLATE ,46,A. 向数据库中添加文件,下

26、例创建数据库，并更改该数据库以添加一个 5 MB 大小的新数据文件。,USE master GO CREATE DATABASE Test1 ON ( NAME = Test1dat1, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat1.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO,ALTER DATABASE Test1 ADD FILE ( NAME = Test1dat2, FILENAME = c:Program FilesMicrosoft SQ

27、L ServerMSSQLDatat1dat2.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB ) GO,47,阅读下列代码，说出其功能。,USE master GO ALTER DATABASE Test1 ADD FILEGROUP Test1FG1 GO ALTER DATABASE Test1 ADD FILE ( NAME = test1dat3, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat3.ndf, SIZE = 5MB, MAXSIZE = 100

28、MB, FILEGROWTH = 5MB), ( NAME = test1dat4, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat4.ndf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) TO FILEGROUP Test1FG1 ALTER DATABASE Test1 MODIFY FILEGROUP Test1FG1 DEFAULT GO,向数据库中添加由两个文件组成的文件组,B、增加日志文件,向数据库文件增加日志文件，命令的格式 Alter database 数

29、据库文件名 add log file,48,49,USE master GO ALTER DATABASE Test1 ADD LOG FILE ( NAME = test1log2, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatatest2log.ldf, SIZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB), ( NAME = test1log3, FILENAME = c:Program FilesMicrosoft SQL ServerMSSQLDatatest3log.ldf, S

30、IZE = 5MB, MAXSIZE = 100MB, FILEGROWTH = 5MB) GO,下例向数据库中添加两个 5 MB 大小的日志文件。,50,C、删除文件,USE master GO ALTER DATABASE Test1 REMOVE FILE test1dat4 GO,将数据库 Test1 中的一个文件删除。 Alter database 文件组名 remove file 文件名,51,D、修改数据库 文件组中的一个文件的大小,USE master GO ALTER DATABASE Test1 MODIFY FILE (NAME = test1dat3, SIZE = 2

31、0MB) GO,ALTER DATABASE Test1 MODIFY FILE (NAME =文件名, SIZE =指定大小),52,E、将主文件组设置为默认文件组。,USE master GO ALTER DATABASE MyDatabase MODIFY FILEGROUP PRIMARY DEFAULT GO,53,F、将主文件组设置为默认文件组,USE master GO ALTER DATABASE 数据库文件名 MODIFY FILEGROUP 文件组名 DEFAULT GO,【例】将customers的默认文件组设置为primary alter database custom

32、ers modify filegroup defaultgroup default,54,第4节 数据库的管理操作,了解数据库的状态 SELECT * FROM sys.databases,SELECT name,state_desc FROM sys.databases,55,了解文件的状态 SELECT * FROM sys.master_files,56,修改数据库的属性,57,1、删除数据库,可视操作 使用T-SQL语句 语法：drop database database_name,例：drop database xuhai,但是，当出现以下几种情况时，将无法删除数据库： （1）当数据库正在使用时。 （2）当有用户正在对数据库进行操作时。 （3）当数据库正在执行数据复制时。,58,2、扩大数据库 如果没有“启用自动增长”，需要手动修改。,59,3、其他操作,分离数据库 附加数据库 收缩数据库 收缩文件 脱机 联机,60,本章小结 本章主要介绍了数据库的创建和管理知识。 在本章中大家学习了数据库