SQL创建管理数据库.ppt

1,第2章 创建管理数据库,2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4 分离和附加数据库,2,2.1 SQL SERVER 数据库结构,2.1.1 SQL Server的数据库的结构 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库 2.1.5 数据库元素,3,2.1.1 SQL Server的数据库的结构,1.SQL Server的数据库数据库的数据按不同形式组织在一起，构成了不同的数据库对象。以二维表的形式组织在一起的数据构成了数据库的表对象，数据库是数据库对象的容器。用SSMS连接到数据库看到的都是逻辑对象，不是存放在物理磁盘上的文件，数据库对象没有对应的磁盘文件，整个数据库对应磁盘上的文件与文件组。,4,2.1.1 SQL Server的数据库的结构,5,数据库,2.1.1 SQL Server的数据库的结构,图2.2 数据库结构图,6,2.1 SQL SERVER 数据库结构,2.1.1 SQL Server的数据库的结构 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库,7,2.1.2 SQL Server 数据库文件及文件组,SQL Server的数据库文件的三种类型 1.主数据文件（Primary file）用来存储数据库的数据和数据库的启动信息。每个数据库必须并且只有一个主数据文件，其扩展名为.MDF。实际的文件都有两种名称：操作系统文件名和逻辑文件名（T-SQL语句中使用）。,8,2.1.2 SQL Server 数据库文件及文件组,2.辅助数据文件（Secondary file）用来存储数据库的数据，使用辅助数据库文件可以扩展存储空间。如果数据库用一个主数据文件和多个辅助数据文件来存放数据，并将它们放在不同的物理磁盘上，数据库的总容量就是这几个磁盘容量的和。辅助数据文件的扩展名为：.NDF。,9,2.1.2 SQL Server 数据库文件及文件组,3.事务日志文件（Transaction log）用来存放数据库的事务日志。凡是对数据库进行的增、删、改等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用事务日志文件恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名为：.LDF。,10,2.1.2 SQL Server 数据库文件及文件组,4、文件组文件组的概念 文件组是SQL Server 中一个或多个文件的命名集合，它构成分配或用于数据库管理的单个单元。文件组的使用场合当有多个磁盘，希望把文件分布在这些磁盘上以提高性能。,Student数据库,Student_1.mdf,Student_2.ndf,Student_3.ndf,Student_log.ldf,C盘,d盘,e盘,f盘,primary,Studgrp,Studgrp,11,2.1.2 SQL Server 数据库文件及文件组,文件组的作用：当对数据库对象写操作时,数据库会根据组内数据文件的大小,按比例写入组内所有数据文件中.当查询数据时,系统会创建多个单独的线程来并行读取分配在不同物理磁盘上的每个文件,从一定程度上提高了查询速度.,12,文件组的类型:主文件组和用户定义文件组主文件组：包含主文件的文件组为主文件组 如果主文件组空间耗尽，那么将不能向系统表添加新目录信息 只要在下列情况下，主文件组才会填满：关闭了自动增长 选项，或者含有主文件组中文件的所有磁盘空间耗尽。如果发生主文件组满的情况:要么重新打开自动增长，要 么将其它文件从磁盘上移走以释放更多的空间。用户定义的文件组:指在首次创建或以后更改数据库时，用户明确创建的任何文件组。如果用户定义的文件组填满，那么只有明确地分配到该文件组的用户表会受到影响,2.1.2 SQL Server 数据库文件及文件组,13,指定默认的文件组数据库建立时的默认文件组为主文件组未指定文件组的对象都将放到默认文件组中在任何时候，有且仅有一个文件组被指定为 DEFAULT 文件组 指定默认文件组的方法:Alter database 数据库名 modify filegroup 文件组名 default查看文件组信息：,2.1.2 SQL Server 数据库文件及文件组,例：use pubsexec sp_helpfile,14,2.1 SQL SERVER 数据库结构,2.1.1 SQL Server的数据库的结构 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库,15,2.1.3 SQL Server的事务日志,1.事务是一组T-SQL语句的集合，这组语句作为单个的工作与恢复的单元。事务作为一个整体来执行，对于其数据的修改，要么全都执行，要么全都不执行。2.事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。3.事务日志记录更改数据的流程。,16,2.1 SQL SERVER 数据库结构,2.1.1 SQL Server的数据库的结构 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库,17,2.1.4 SQL Server 的系统数据库,SQL server 2008的系统构成 数据库分为两类有系统数据库和用户数据库。当安装完成后系统会自动创建四个系统数据库,并且在其中定义了若干个系统表和系统的存储过程。下面分别介绍系统中的四个系统数据库，这四个数据库分别是master、model、tempb、msdb,18,2.1.4 SQL Server 的系统数据库,1、master数据库 此数据库记录了SQL server 2005的所有的服务器机系统信息，所有的注册账户和密码以及所有的系统设置信息等大量对对系统至关重要的信息，是系统的关键性所在，所以他一旦受到破坏，可能会导致这个系统的瘫痪。2、model数据库 为用户提供了模板和原型包含了每一用户数据库所需要的系统表。它的定制结构可以被更改，因为每当用户创建新的数据库时，都是复制model数据库的模板，所以所作的所有更改都将反映到用户数据库当中。,19,2.1.4 SQL Server 的系统数据库,3、tempdb数据库 此数据库保存所有的临时性表和临时存储过程，并满足任何其他的临时存储要求。tempdb数据库是全局资源，在每次启动时都重新创建，在用户离开或系统关机时,临时数据库中的临时表将被删除。4、msdb数据库 此数据库用来存储计划信息、备份和恢复相关信息、SQL server代理程序调度报警和作业调度等操作。,20,2.1.5 数据库元素,1、表 表是数据库中的基本元素，用于存储实际的数据。表由行和列构成2、视图 视图是一个虚表，只存在视图的定义，不存在视图相对应的数据。视图可以进行查询、修改、删除等操作。3、索引 索引可以使用户快速找到表或索引视图的特定信息。4、存储过程和触发器 存储过程完善应用程序，促使应用程序高效执行，触发器实现各种复杂的业务规则。,21,第2章 创建管理数据库,2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4 分离和附加数据库,22,2.2 创建数据库,2.2.1 用SSMS图形界面创建数据库 2.2.2 用T-SQL命令创建数据库,23,2.2.1 用SSMS图形界面创建数据库,例1、运用SSMS建立STUDENT数据库.1)数据库的文件名为STUDENT_DATA，物理文件名为E:SQLSTUDENT_DATA.MDF，初始大小为3MB。文件组为PRIMARY，文件属性为文件自动增长，文件增长限制为20MB。2）日志文件为STUDENT_LOG,物理文件名为E:SQLSTUDENT_LOG.LDF，初始大小为1MB。3）辅助数据文件为STUDENT_DATA1物理文件名为E:SQLSTUDENT_DATA1.NDF。初始大小为1MB，文件组为PRIMARY，文件属性为文件自动增长，文件增长限制为20MB。,24,练习,用SSMS创建职工信息管理数据库，数据库名为employee，将数据文件和日志文件存放在e:sql中，并设置数据文件大小为3MB，文件增长率为15%；日志文件大小为1MB，文件增长率为10%。,25,2.2 创建数据库,2.2.1 用SSMS图形界面创建数据库 2.2.2 用T-SQL命令创建数据库,26,2.2.2 用T-SQL命令创建数据库,使用Transact-SQL语言创建数据库，语法如下：CREATE DATABASE database_nameON PRIMARY FILEGROUP groupname(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=grow_increment),nLOG ON(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),n,27,2.2.2 用T-SQL命令创建数据库,各参数说明如下：database_name：要建立的数据库名称。PRIMARY：该参数在主文件组中指定文件。若没有指定 PRIMARY关键字，该语句中列的第一个文件成为主文件LOG ON：指定建立数据库的事务日志文件。NAME：指定数据或事务日志文件的名称。FLENAEM：指定文件的操作系统文件名称和路径。os_file_name中的路径必须为安装SQL服务器的计算机上的文件夹。,28,2.2.2 用T-SQL命令创建数据库,SIZE：指定数据或日志文件的大小，单位可以用KB、MB等。MAXSIZE：指定文件能够增长到的最大长度，单位可以用KB、MB等。如果没有指定最大长度，文件将一直增长到磁盘满为止。FILEGROWTH：指定文件的增长量，该参数不能超过MAXSIZE的值。默认单位在KB，也可以指定用MB单位。也可以使用百分比。如果没指定参数，默认百分之十，最小64K。,29,2.2.2 用T-SQL命令创建数据库,例2：用T-SQL语句创建学生信息管理数据库，数据库名为student,将数据文件和日志文件存放在d:stud中,数据文件和日志文件的初始大小为3MB，增长幅度为1MB，文件大小限制为20MB。,30,2.2.2 用T-SQL命令创建数据库,CREATE DATABASE studentON(NAME=student_data,FILENAME=d:studstudent_data.mdf,SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)LOG ON(NAME=student_log,FILENAME=d:studstudent_log.ldf,SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)GO,31,2.2.2 用T-SQL命令创建数据库,例3：某公司生产许多产品，数据量大，需要建立产品信息数据库。数据库命名为product.其数据文件和事务文件分别需要3个，文件信息为:,32,2.2.2 用T-SQL命令创建数据库,CREATE DATABASE product ON PRIMARY(NAME=p1_DATA,FILENAME=d:product product_DATA1.mdf,SIZE=3MB,FILEGROWTH=15%),FILEGROUP USERGROUP(NAME=p2_DATA,FILENAME=d:product product_DATA2.ndf,SIZE=3MB,MAXSIZE=10MB,FILEGROWTH=15%),(NAME=p3_DATA,FILENAME=d:product product_DATA3.ndf,SIZE=3MB,MAXSIZE=20MB,FILEGROWTH=15%),33,2.2.2 用T-SQL命令创建数据库,LOG ON(NAME=p1_log,FILENAME=d:productproduct_log1.ldf,SIZE=2MB,FILEGROWTH=10%),(NAME=p2_log,FILENAME=d:productproduct_log2.ldf,SIZE=2MB,FILEGROWTH=10%)GO,34,第2章 创建管理数据库,2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4分离和附加数据库,35,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,36,2.3.1 打开数据库,1.在SSMS中打开数据库，单击要打开的数据库即可。2.在“SQL语句编辑器”中，使用USE语句打开并切换数据库，语法为：USE database_name 3.在“SQL语句编辑器”中，直接通过数据库下拉列表框 打开并切换数据库。,37,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,38,2.3.2 查看数据库信息,1.用T-SQL命令查看数据库信息 语法格式为：EXECUTE sp_helpdb database_name 例6：查看数据库northwind信息。EXEC sp_helpdb northwind,39,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,40,2.3.3 数据库更名,1、语法格式：alter database database_name modify name=new_database_name 例7：更改数据库“student”的名称为“stud”。命令语句如下：USE master GO ALTER DATABASE student MODIFY NAME=stud GO,41,2.3.3 数据库更名,2、存储过程语法格式：EXEC sp_renamedb oldname,newname 例8：更改数据库“stud”的名称为“student”。命令语句如下：EXEC sp_renamedb stud,student GO,42,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,43,2.3.4 修改数据库,2.3.4.1 增加数据库容量2.3.4.2 增加数据库文件或文件组,44,2.3.4.1 增加数据库容量,1）使用SSMS增加数据库容量 在SSMS右击要增加容量的数据库（如“student”数据库），弹出快捷菜单，选择属性命令，打开student数据库的属性对话框，在属性对话框中对数据库文件的分配空间进行重新设定。注意 重新指定的数据库分配空间必须大于现有空间,否则不会对该文件的大小进行修改并提示出错信息.,45,2.3.4.1 增加数据库容量,2）增加数据库容量的T-SQL语句为：ALTER DATABASE database_name MODIFY FILE(NAME=file_name,SIZE=newsize),46,2.3.4.1 增加数据库容量,例9：为STUDENT数据库增加容量，原数据库文件STUDENT_DATA的初始分配空间为3M，现在将STUDENT_DATA的分配空间增加至5M,增长率为15%。操作命令如下：USE masterGOALTER DATABASE studentMODIFY FILE(NAME=student _data,SIZE=5MBFILEGROWTH=15%),47,2.3.4.2 增加数据库文件或文件组,1、使用T-SQL语句增加数据库文件组：ALTER DATABASE database_name ADD FILEGROUP groupname,2、增加数据库文件(到某个组中)，T-SQL语句为：ALTER DATABASE database_name ADD LOG FILE(NAME=file_name,FILENAME=os_file_name,SIZE=newsize,MAXSIZE=maxsize,FILEGROWTH=file_increatment)TO FILEGROUP groupname,48,2.3.4.2 增加数据库文件或文件组,例10：使用T-SQL语句修改学生信息管理数据库,在该数据库下增加一个数据文件student_data2.ndf,存放在d:student下，文件初始大小为2mb，增长率为15%。T-SQL语句为：Alter database studentAdd file(name=student_data2，Filename=d:studentstudent_data2.ndf，Size=2mb,Filegrowth=15%)Go,49,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,50,2.3.5 收缩数据库,收缩数据库容量1)使用SSMS收缩数据库容量 在SSMS右击要增加容量的数据库（如“student”数据库），弹出快捷菜单，选择属性命令，打开student数据库的属性对话框，在属性对话框中选择“选项”页，选择“其它选项”中的“自动”，将其自动收缩属性设置为“true”。,51,2.3.5 收缩数据库,2)使用T-SQL语句收缩数据库DBCC SHRINKDATABASE 语句DBCC SHRINKDATABASE(数据库名,目标百分比,NOTRUNCATE|TRUNCATEONLY)目标百分比:指收缩后文件中的 可用空间大小所占的百分比.如果不给出目标百分比,则数据库的容量收缩至最小.NOTRUNCATE 选项:执行收缩时将实际数据移动,而文件末尾的可用空间不释放给操作系统，文件的物理大小不会更改,数据文件好像没有收缩。TRUNCATEONLY 选项:将文件末尾的所有可用空间收回给操作系统。但在文件内不执行任何页移动操作。指定的文件只能收缩到上一次分配的大小 收缩后的数据库不能比 model 数据库更小,且收缩后的目标大小始终不小于文件的最小大小,52,2.3.5 收缩数据库,例11:使用T-SQL语句收缩学生信息管理数据库student容量至最小./*在收缩数据库前将此数据库设定为单用户模式*/EXEC SP_dboption student,single user,truegoDBCC SHRINKDATABASE（STUDENT）GOEXEC SP_dboption student,single user,falsego,53,3)收缩数据库中的一个数据文件使用SSMS收缩数据文件DBCC SHRINKFILE 语句DBCC SHRINKFILE(文件名|文件id,目标大小,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY)EMPTYFILE注意：将所有数据从指定文件中迁移到同一文件组中的其 它文件。该选项允许使用 ALTER DATABASE 语句 除去文件。如果想删除以被清空的数据库文件，可以使用如下T-SQL命令语句：ALTER DATABASE database_name REMOVE FILE file_name,2.3.5 收缩数据库,54,2.3.5 收缩数据库,例12：使用T-SQL语句修改产品信息管理数据库，清空（移动）数据文件p_data3.ndf中的数据到同文件组中的其他文件中，并删除该数据文件。查看product数据库的文件信息。实现的T-SQL语句:DBCC SHRINKFILE(p_data3,emptyfile)ALTER DATABASE productREMOVE FILE p_data3sp_helpdb product,55,2.3.5 收缩数据库,4)自动收缩数据库 ALTER DATABASE 数据库名 SET AUTO_SHRINK ON/OFF,56,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,57,2.3.6 修改数据库选项,1.使用SSMS设定和修改数据库选项 2.使用T-SQL语句查看和设定数据库的选项 1)查看数据库选项可以使用系统存储过程，语句如下：EXEC sp_dboption database_name2)修改数据库选项可以使用存储过程，命令格式如下：EXEC sp_dboption database_name,option_name,TRUE|FALSE,58,2.3.6 设定修改数据库选项,例13：查看数据库“student”的选项。命令语句如下：EXEC sp_dboption student GO例14：更改数据库student为只读状态，命令语句如下：EXEC sp_dboption student,read only,TRUE GO,59,2.3 管理数据库,2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除,60,2.3.7 删除数据库,1.使用SSMS对象资源管理器删除数据库 在SSMS对象资源管理器中展开“数据库”文件夹，单击要删除的数据库（如”student”)，单击鼠标右键，在弹出的菜单中单击“删除”命令。,61,2.3.7 删除数据库,2.使用T-SQL语句删除数据库语法格式为：DROP DATABASE database_name,database_name3.使用系统存储过程删除数据库Exec sp_dbremove database_name注意:任何时候删除数据库前都应备份数据库master。无法删除系统数据库msdb、model、master、tempdb,62,2.3.7 删除数据库,例15：使用T-SQL语句与或系统存储过程删除student 数据库。实现的T-SQL语句:Drop database studentGO或EXEC sp_dbremove studentGO,63,第2章 创建管理数据库,2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4分离和附加数据库,64,2.4 分离和附加数据库,SQL Server 2005 允许分离数据库的数据和事务日志文件，然后将其重新附加到另一台服务器，甚至同一台服务器上1、分离数据库分离数据库将从 SQL Server 删除数据库，但是保持组成该数据库的数据和事务日志文件中的数据库完好无损分离数据库要求没有用户正在使用数据库，数据库中没有未完成的事务，并且在内存中没有这个数据库的脏页一旦数据库被分离，其条目就从 master 数据库的 sysdatabases 表中被移除。从 SQL Server 的角度看，数据库似乎被删除了但是其物理文件仍然保存在磁盘上,65,2.4 分离和附加数据库,1）使用SSMS对象资源管理器分离数据库 在SSMS右击要分离的数据库（如“student”数据库），弹出快捷菜单，选择“任务”下的“分离”命令。,66,2.4 分离和附加数据库,2）使用系统存储过程分离数据库EXEC sp_detach_db database_name例4：使用系统存储过程分离数据库student。EXEC sp_detach_db studentGO注意：在分离数据库的时候要确保没有人在使用此数据库，如果数据库正被使用，在分离前应先断开连接。,67,2、附加数据库当附加在数据库上时，必须指定主数据文件的名称和物理位置可以指定新的数据库名字。当日志文件不存在时,附加数据库后将生成一个新的日志,也可以用此种方法快速收缩日志文件.但当日志文件多于一个此收缩日志的方法将不起作用.,2.4 分离和附加数据库,68,2.4 分离和附加数据库,1）使用SSMS对象资源管理器附加数据库 在SSMS右击要附加的数据库（如“student”数据库），弹出快捷菜单，选择“任务”下的“附加”命令。,69,2.4 分离和附加数据库,2）使用系统存储过程附加数据库sp_attach_db dbname=dbname,filename1=filename_n,.16 例5：使用系统存储过程附加学生信息管理数据库student。实现语句:EXEC sp_attach_db student,d:studstudent_data.mdf或EXEC sp_attach_db dbname=student,filename1=d:studstudent_data.mdf,filename2=d:studstudent_log.ldf GO,70,小结,掌握1、使用SSMS创建、管理、分离、附加数据库2、使用T-SQL语句创建、管理、分离、附加数据库了解1、了解系统数据库的作用 2、了解文件组的作用,