SQL创建管理数据库课件

1、1第2章 创建管理数据库2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4 分离和附加数据库22.1 SQL SERVER 数据库结构2.1.1 SQL Server的数据库的结构 2.1.2 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库 2.1.5 数据库元素32.1.1 SQL Server的数据库的结构1. SQL Server的数据库数据库的数据按不同形式组织在一起，构成了不同的数据库对象。以二维表的形式组织在一起的数据构成了数据库的表对象，数据库是数据库对象的容器。用

2、SSMS连接到数据库看到的都是逻辑对象，不是存放在物理磁盘上的文件，数据库对象没有对应的磁盘文件，整个数据库对应磁盘上的文件与文件组。4数据文件日志文件学生管理数据库数据、表、存储过程、触发器、视图、规则、默认值、用户定义数据类型、索引、权限数据库用户、数据库规则等对象图2.1 数据库、数据库对象及文件关系图2.1.1 SQL Server的数据库的结构5数据库数据文件日志文件主数据文件辅助数据文件1辅助数据文件2辅助数据文件n2.1.1 SQL Server的数据库的结构图2.2 数据库结构图8K页62.1 SQL SERVER 数据库结构2.1.1 SQL Server的数据库的结构 2.

3、1.2 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库 72.1.2 SQL Server 数据库文件及文件组SQL Server的数据库文件的三种类型 1.主数据文件（Primary file） 用来存储数据库的数据和数据库的启动信息。每个数据库必须并且只有一个主数据文件，其扩展名为.MDF。实际的文件都有两种名称：操作系统文件名和逻辑文件名（T-SQL语句中使用）。82.1.2 SQL Server 数据库文件及文件组2.辅助数据文件（Secondary file） 用来存储数据库的数据，使用辅助数据库文件可

4、以扩展存储空间。如果数据库用一个主数据文件和多个辅助数据文件来存放数据，并将它们放在不同的物理磁盘上，数据库的总容量就是这几个磁盘容量的和。辅助数据文件的扩展名为：.NDF。92.1.2 SQL Server 数据库文件及文件组3.事务日志文件（Transaction log） 用来存放数据库的事务日志。凡是对数据库进行的增、删、改等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用事务日志文件恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名为：.LDF。 102.1.2 SQL Server 数据库文件及文件组4、文件组文件组的概念 文件组是SQL Serve

5、r 中一个或多个文件的命名集合，它构成分配或用于数据库管理的单个单元。文件组的使用场合当有多个磁盘，希望把文件分布在这些磁盘上以提高性能。Student数据库Student_1.mdfStudent_2.ndfStudent_3.ndfStudent_log.ldfC盘d盘e盘f盘primaryStudgrpStudgrp112.1.2 SQL Server 数据库文件及文件组文件组的作用：当对数据库对象写操作时,数据库会根据组内数据文件的大小,按比例写入组内所有数据文件中.当查询数据时,系统会创建多个单独的线程来并行读取分配在不同物理磁盘上的每个文件,从一定程度上提高了查询速度.12文件组的

6、类型:主文件组和用户定义文件组主文件组：包含主文件的文件组为主文件组 如果主文件组空间耗尽，那么将不能向系统表添加新目录信息 只要在下列情况下，主文件组才会填满：关闭了自动增长 选项，或者含有主文件组中文件的所有磁盘空间耗尽。 如果发生主文件组满的情况: 要么重新打开自动增长，要 么将其它文件从磁盘上移走以释放更多的空间。 用户定义的文件组:指在首次创建或以后更改数据库时， 用户明确创建的任何文件组。如果用户定义的文件组填满， 那么只有明确地分配到该文件组的用户表会受到影响2.1.2 SQL Server 数据库文件及文件组13指定默认的文件组数据库建立时的默认文件组为主文件组未指定文件组的对

7、象都将放到默认文件组中在任何时候，有且仅有一个文件组被指定为 DEFAULT 文件组 指定默认文件组的方法: Alter database 数据库名 modify filegroup 文件组名 default查看文件组信息：系统存储过程描述sp_helpfile 文件名=名称返回与当前数据库关联的文件的物理名称和属性sp_helpfilegroup 文件组名返回与当前数据库关联的文件组的名称和属性2.1.2 SQL Server 数据库文件及文件组例：use pubsexec sp_helpfile142.1 SQL SERVER 数据库结构2.1.1 SQL Server的数据库的结构 2.

8、1.2 SQL Server的数据文件及文件组2.1.3 SQL Server的事务日志 2.1.4 SQL Server 的系统数据库 152.1.3 SQL Server的事务日志 1.事务是一组T-SQL语句的集合，这组语句作为单个的工作与恢复的单元。事务作为一个整体来执行，对于其数据的修改，要么全都执行，要么全都不执行。 2.事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。 3.事务日志记录更改数据的流程。162.1 SQL SERVER 数据库结构2.1.1 SQL Server的数据库的结构 2.1.2 SQL Server的数据文件及文件组2.1.3 SQL

9、Server的事务日志 2.1.4 SQL Server 的系统数据库 172.1.4 SQL Server 的系统数据库 SQL server 2008的系统构成 数据库分为两类有系统数据库和用户数据库。当安装完成后系统会自动创建四个系统数据库,并且在其中定义了若干个系统表和系统的存储过程。 下面分别介绍系统中的四个系统数据库，这四个数据库分别是master、model、tempb、msdb182.1.4 SQL Server 的系统数据库1、master数据库 此数据库记录了SQL server 2005的所有的服务器机系统信息，所有的注册账户和密码以及所有的系统设置信息等大量对对系统至关

10、重要的信息，是系统的关键性所在，所以他一旦受到破坏，可能会导致这个系统的瘫痪。2、model数据库 为用户提供了模板和原型包含了每一用户数据库所需要的系统表。它的定制结构可以被更改，因为每当用户创建新的数据库时，都是复制model数据库的模板，所以所作的所有更改都将反映到用户数据库当中。192.1.4 SQL Server 的系统数据库3、tempdb数据库 此数据库保存所有的临时性表和临时存储过程，并满足任何其他的临时存储要求。tempdb数据库是全局资源，在每次启动时都重新创建，在用户离开或系统关机时,临时数据库中的临时表将被删除。4、msdb数据库 此数据库用来存储计划信息、备份和恢复相

11、关信息、SQL server代理程序调度报警和作业调度等操作。202.1.5 数据库元素1、表 表是数据库中的基本元素，用于存储实际的数据。表由行和列构成2、视图 视图是一个虚表，只存在视图的定义，不存在视图相对应的数据。视图可以进行查询、修改、删除等操作。3、索引 索引可以使用户快速找到表或索引视图的特定信息。4、存储过程和触发器 存储过程完善应用程序，促使应用程序高效执行，触发器实现各种复杂的业务规则。21第2章 创建管理数据库2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4 分离和附加数据库222.2 创建数据库 2.2.1 用SSMS图形界面创建数据

12、库 2.2.2 用T-SQL命令创建数据库 232.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，文件属性为文件自

13、动增长，文件增长限制为20MB。24练习用SSMS创建职工信息管理数据库，数据库名为employee，将数据文件和日志文件存放在e:sql中，并设置数据文件大小为3MB，文件增长率为15%；日志文件大小为1MB，文件增长率为10%。252.2 创建数据库 2.2.1 用SSMS图形界面创建数据库 2.2.2 用T-SQL命令创建数据库 262.2.2 用T-SQL命令创建数据库使用Transact-SQL语言创建数据库，语法如下：CREATE DATABASE database_nameON PRIMARY FILEGROUP groupname (NAME=logical_file_name

14、,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),n272.2.2 用T-SQL命令创建数据库各参数说明如下：database_name ：要建立的数据库名称。PRIMARY：该参数在主文件组中指定文件。若没有指定 PRIMARY关键字，

15、该语句中列的第一个文件成为主文件LOG ON：指定建立数据库的事务日志文件。NAME：指定数据或事务日志文件的名称。FLENAEM：指定文件的操作系统文件名称和路径。os_file_name中的路径必须为安装SQL服务器的计算机上的文件夹。282.2.2 用T-SQL命令创建数据库SIZE：指定数据或日志文件的大小，单位可以用KB、MB等。MAXSIZE：指定文件能够增长到的最大长度，单位可以用KB、MB等。如果没有指定最大长度，文件将一直增长到磁盘满为止。 FILEGROWTH：指定文件的增长量，该参数不能超过MAXSIZE的值。默认单位在KB，也可以指定用MB单位。也可以使用百分比。如果没

16、指定参数，默认百分之十，最小64K。292.2.2 用T-SQL命令创建数据库例2：用T-SQL语句创建学生信息管理数据库，数据库名为student, 将数据文件和日志文件存放在d:stud中,数据文件和日志文件的初始大小为3MB，增长幅度为1MB，文件大小限制为20MB。302.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,

17、 FILENAME=d:studstudent_log.ldf, SIZE=3MB,FILEGROWTH=1MB,MAXSIZE=20MB)GO312.2.2 用T-SQL命令创建数据库例3：某公司生产许多产品，数据量大，需要建立产品信息数据库。数据库命名为product.其数据文件和事务文件分别需要3个，文件信息为:逻辑名物理名文件组初始大小增长率最大值p1_datad:productproduct_data1.mdfprimary315%p2_datad:productproduct_data2.ndfusergroup315%10p3_datad:productproduct_data3

18、.ndfusergroup315%20p1_logd:productproduct_log1.ldf210%p2_logd:productproduct_log2.ldf210%322.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.

19、ndf , SIZE = 3MB, MAXSIZE=10MB,FILEGROWTH = 15%),(NAME =p3_DATA, FILENAME = d:product product_DATA3.ndf , SIZE = 3MB, MAXSIZE=20MB,FILEGROWTH = 15%)332.2.2 用T-SQL命令创建数据库 LOG ON (NAME =p1_log, FILENAME = d:productproduct_log1.ldf , SIZE = 2MB, FILEGROWTH = 10%),(NAME =p2_log, FILENAME = d:productprod

20、uct_log2.ldf , SIZE = 2MB, FILEGROWTH = 10%)GO34第2章 创建管理数据库2.1 SQL SERVER数据库的结构2.2 创建数据库 2.3 管理数据库2.4分离和附加数据库352.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除 362.3.1 打开数据库1.在SSMS中打开数据库，单击要打开的数据库即可。2.在“SQL语句编辑器”中，使用USE语句打开并切换数据库 ，语法为：USE database_name 3.在“S

21、QL语句编辑器”中，直接通过数据库下拉列表框 打开并切换数据库。 372.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除 382.3.2 查看数据库信息1. 用T-SQL命令查看数据库信息 语法格式为： EXECUTE sp_helpdb database_name 例6：查看数据库northwind信息。EXEC sp_helpdb northwind 392.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库

22、2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除 402.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 412.3.3 数据库更名 2、存储过程语法格式： EXEC sp_renamedb oldname,newname 例8：更改数据库“stud”的名称为“student”。命令

23、语句如下： EXEC sp_renamedb stud, student GO 422.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除 432.3.4 修改数据库 增加数据库容量 增加数据库文件或文件组44 增加数据库容量 1）使用SSMS增加数据库容量 在SSMS右击要增加容量的数据库（如“student”数据库），弹出快捷菜单，选择属性命令，打开student数据库的属性对话框，在属性对话框中对数据库文件的分配空间进行重新设定。注意 重新指定的数据库分配空间必须

24、大于现有空间,否则不会对该文件的大小进行修改并提示出错信息. 45 增加数据库容量2）增加数据库容量的T-SQL语句为： ALTER DATABASE database_name MODIFY FILE (NAME=file_name, SIZE=newsize )46 增加数据库容量例9：为STUDENT数据库增加容量，原数据库文件STUDENT_DATA的初始分配空间为3M，现在将STUDENT_DATA的分配空间增加至5M,增长率为15%。操作命令如下：USE masterGOALTER DATABASE studentMODIFY FILE(NAME= student _data,SI

25、ZE=5MBFILEGROWTH=15% ) 47 增加数据库文件或文件组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 groupn

26、ame48 增加数据库文件或文件组例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%)Go492.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.

27、6 修改数据库选项2.3.7 数据库的删除 502.3.5 收缩数据库收缩数据库容量1)使用SSMS收缩数据库容量 在SSMS右击要增加容量的数据库（如“student”数据库），弹出快捷菜单，选择属性命令，打开student数据库的属性对话框，在属性对话框中选择“选项”页，选择“其它选项”中的“自动”，将其自动收缩属性设置为“true”。512.3.5 收缩数据库2)使用T-SQL语句收缩数据库DBCC SHRINKDATABASE 语句DBCC SHRINKDATABASE (数据库名 , 目标百分比 , NOTRUNCATE | TRUNCATEONLY) 目标百分比:指收缩后文件中的

28、可用空间大小所占的百分比.如果不给出目标百分比,则数据库的容量收缩至最小.NOTRUNCATE 选项:执行收缩时将实际数据移动,而文件末尾的可用空间不释放给操作系统，文件的物理大小不会更改,数据文件好像没有收缩。 TRUNCATEONLY 选项:将文件末尾的所有可用空间收回给操作系统。但在文件内不执行任何页移动操作。指定的文件只能收缩到上一次分配的大小 收缩后的数据库不能比 model 数据库更小 ,且收缩后的目标大小始终不小于文件的最小大小 522.3.5 收缩数据库例11:使用T-SQL语句收缩学生信息管理数据库student容量至最小./* 在收缩数据库前将此数据库设定为单用户模式*/E

29、XEC SP_dboption student,single user,truegoDBCC SHRINKDATABASE （STUDENT）GOEXEC SP_dboption student,single user,falsego533)收缩数据库中的一个数据文件使用SSMS收缩数据文件DBCC SHRINKFILE 语句DBCC SHRINKFILE (文件名 | 文件id , 目标大小, EMPTYFILE | NOTRUNCATE | TRUNCATEONLY) EMPTYFILE注意：将所有数据从指定文件中迁移到同一文件组中的其 它文件。该选项允许使用 ALTER DATABASE

30、 语句 除去文件。如果想删除以被清空的数据库文件，可以使用如下T-SQL命令语句：ALTER DATABASE database_name REMOVE FILE file_name2.3.5 收缩数据库542.3.5 收缩数据库例12：使用T-SQL语句修改产品信息管理数据库，清空（移动）数据文件p_data3.ndf中的数据到同文件组中的其他文件中，并删除该数据文件。查看product数据库的文件信息。实现的T-SQL语句:DBCC SHRINKFILE(p_data3,emptyfile)ALTER DATABASE productREMOVE FILE p_data3sp_helpdb

31、 product552.3.5 收缩数据库4)自动收缩数据库 ALTER DATABASE 数据库名 SET AUTO_SHRINK ON/OFF 562.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除 572.3.6 修改数据库选项 1.使用SSMS设定和修改数据库选项 2.使用T-SQL语句查看和设定数据库的选项 1)查看数据库选项可以使用系统存储过程，语句如下： EXEC sp_dboption database_name2)修改数据库选项可以使用存储过程，命

32、令格式如下： EXEC sp_dboption database_name,option_name,TRUE|FALSE582.3.6 设定修改数据库选项例13：查看数据库“student”的选项。命令语句如下： EXEC sp_dboption student GO例14：更改数据库student为只读状态，命令语句如下： EXEC sp_dboption student ,read only,TRUE GO 592.3 管理数据库 2.3.1 打开数据库2.3.2 查看数据库2.3.3 数据库更名2.3.4 修改数据库2.3.5 收缩数据库2.3.6 修改数据库选项2.3.7 数据库的删除

33、 602.3.7 删除数据库1.使用SSMS对象资源管理器删除数据库 在SSMS对象资源管理器中展开“数据库”文件夹，单击要删除的数据库（如”student”)，单击鼠标右键，在弹出的菜单中单击“删除”命令。 612.3.7 删除数据库2.使用T-SQL语句删除数据库语法格式为：DROP DATABASE database_name ,database_name3.使用系统存储过程删除数据库Exec sp_dbremove database_name注意:任何时候删除数据库前都应备份数据库master。无法删除系统数据库msdb、model、master 、tempdb622.3.7 删除数据库例15：使用T-SQL语句与或系统存储过程删除student 数据库。实现的T-SQL语句:Drop database studentGO或EXEC sp_dbremove studentGO63第2章 创建管理数据库2.1 SQL SERVER数据库的结构2.2 创