项目二 数据库的创建与管理.ppt

项目二 数据库的创建与管理,掌握利用图形界面创建除数据库,掌握数据库的基本结构,掌握利用T-SQL语句创建数据库,掌握利用图形界面和T-SQL语句修改数据库,掌握利用图形界面和T-SQL语句查看数据库,掌握利用图形界面和T-SQL语句删除数据库,SQL Server 2005 数据库概念,1,系统数据库概述,2,创建数据库,3,管理和维护数据库,4,学习内容,学习重点,数据库及其对象文件和文件组及事务日志创建数据库系统的几个阶段和原则创建和管理数据库的方法,学习难点,文件和文件组及事务日志创建和管理数据库的方法,1 SQL Server 2005 数据库概念,数据库文件,逻辑结构,存储结构,1 SQL Server 2005 数据库概念,从存储结构上来看，一个数据库文件至少由一个后缀名为.MDF的主数据文件和一个后缀名为.LDF的事务日志文件组成，可根据实际需要，给数据库增加后缀名为.NDF的次文件和其他日志文件。,1 SQL Server 2005 数据库概念,主文件：用于存储数据，每一个数据库都包含 一个主文件，其中包含数据库的启动信 息，并用来存储数据库事务文件：事务文件包含有用于恢复数据库的 日志信息。每一个数据库必须至少包 含一个事务文件次文件：次文件包含有不能置于主文件中的数 据。如果主文件足够大，能够容纳数据 库中的所有数据，则不需要次文件,SQL Server 2005遵循着先写日记、后进行数据修改的原则对数据库进行操作。,1 SQL Server 2005 数据库概念,数据库文件组 文件组是SQL另一种形式的容器，文件位置可以很灵活，你可以将数据文件存储在不同的地方，然后用文件组把他们作为一个单元来管理。例如，你可以将主数据文件放在一个地方然后将次要数据文件(aa1.ndf,aa2.ndf,aa3.ndf),如果需要，可将他们放在三个不同的磁盘（如D盘、E盘、F盘）上。然后创建一个文件组，将所有的文件指定到此文件组。,1 SQL Server 2005 数据库概念,当对数据库对象写操作时，数据库会根据组内数据文件的大小，按比例写入组内所有数据文件中。当进行操作时，可以并行提高工作效率。,2 系统数据库概述,数据库对象 表：由行和列组成，用于存储数据视图：是一个虚表，用于查看一个或者多个表约束：用于强制数据库完整性索引：加快检索数据的方式用户自定义函数：实现用户定义的某种功能存储过程：一组预编译的SQL语句，可以完成指定的操作触发器：一种特殊类型的存储过程，当某个操作影响到它 保护的数据时，它就会自动触发执行规则：限制表中列的取值范围默认值：自动插入的常量值用户自定义数据类型：由用户基于已有的数据类型而定义 的新的数据类型,2 系统数据库概述,系统数据库在安装SQL Server时，安装程序创建四个系统数据库和示例用户数据库,2 系统数据库概述,1master数据库 master数据库记录SQL Server 2005实、例的所有系统级信息。如果master数据库不可用，则SQL Server无法启动。注意：不能在master数据库中创建任何用户对象(例如表、视图、存储过程或触发器)。master数据库包含SQL Server实例使用的系统级信息(例如登录信息和配置选项设置)。,2 系统数据库概述,2model数据库 model数据库用作SQL Server 2005实例上创建的所有数据库的模板。对model数据库进行的修改（如数据库大小、排序规则、恢复模式和其他数据库选项）将应用于以后创建的所有数据库。如果修改model数据库，之后创建的所有数据库都将继承这些修改。例如，可以设置权限或数据库选项或者添加对象，例如表、函数或存储过程等。,2 系统数据库概述,3msdb数据库 msdb数据库由SQL Server代理用来计划警报和作业以及与备份和恢复相关的信息，尤其是SQL Sercer Agent需要使用它来执行安排工作和警报，记录操作者等操作。4tempdb数据库 tempdb数据库是连接到SQL Server 2005实例的所有用户都可用的全局资源，它保存所有临时表和临时存储过程。另外，它还用来满足所有其他临时存储要求。每次启动SQL Server时，系统都要重新创建tempdb数据库。在断开连接时tempdb数据库自动删除临时表和临时存储过程。所以不要在此建立需要永久保存的表。,任务1 创建数据库,创建数据库注意事项 创建数据库需要一定许可，在默认情况下，只有系统管理员和数据库拥有者可以创建数据库。数据库被创建后，创建数据库的用户自动成为该数据库的所有者。创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等，数据库名字必须遵循SQL Server命名规范。,任务1 创建数据库,这里以兼容级别90为例来说明数据库的命名规则：step1 名称长度不能超过128个字符，本地临时表的名称不能超过116个字符。step2 名称字符由英文字母组成，采用Pascal命名法。比如NorthWind。step3 名称中间不允许有空格或其它特殊字符。step4 名称不能是保留字，也不能是变量或局部对象的表示符号（具体参考变量命名部分）。,任务1 创建数据库,建议数据库命名：用产品或项目的名字命名；Pascal Case，如AdventureWork;避免使用特殊字符，如数字，下划线，空格之类；避免使用缩写,任务1 创建数据库,所有的新数据库都是系统样本数据库model的副本。单个数据库可以存储在单个文件上，也可以跨越多个文件存储。数据库的大小可以被增大或者收缩。当新的数据库创建时，SQL Server自动更新“sysdatabases”系统表。一台服务器上最多可能创建32767个数据库。,任务1 创建数据库,在SQL Server2005中，可以使用二种方法来创建数据库：（1）在SQL Server Management Studio中使用“对象资源管理器”创建数据库，由于对象资源管理器提供了图形化的操作界面，创建数据库操作简单，容易掌握。（2）通过在查询窗口中执行T-SQL语句创建数据库。这种方法要求用户掌握基本的SQL语句,任务1 创建数据库,（1）在SQL Server Management Studio中使用“对象资源管理器”创建数据库,右击“数据库”，选择“新建数据库”,任务1 创建数据库,执行上述的命令或操作后，就会打开数据库属性对话框，这个对话框里有3个选项卡，填完这3个选项卡的内容就建好了数据库。,任务1 创建数据库,在常规选项卡中，设置数据库名称,任务1 创建数据库,设置文件和路径,SQL Server自动设置的数据文件，若不满意其中的设置值，如文件名，存放路径等可自行更改，但这里的文件组无法修改,任务1 创建数据库,设置文件初始大小,设置文件的初始大小,任务1 创建数据库,设置文件增长方式,选择此项，则数据文件的文件大小可依需要自动增长；若取消，则文件大小是固定的。（设置一次增长多少MB，或多少百分比）,限制文件成长的上限在多少MB以内，或不限制,任务1 创建数据库,课堂练习：创建一个名为XS的学生数据库，并为它创建一个主数据文件和一个日志文件。默认存放在C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData目录下，初始大小是5MB，扩展文件时按10%的幅度增长，文件增长不受限制。,任务1 创建数据库,（2）通过在查询窗口中执行T-SQL语句创建数据库。这种方法要求用户掌握基本的SQL语句,CREATE DATABASE database_name ON PRIMARY(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=grow_increment),n LOG ON(NAME=logical_file_name,FILENAME=os_file_name,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=growth_increment),n COLLATE collation_name,设置数据库名称,该参数在主文件组中指定文件。若没有指定PRIMARY关键字，该语句中所列的第一个文件成为主文件,设置数据库的数据文件,指定建立数据库的事务日志文件,指定数据或事务日志文件的名称,指定文件的操作系统文件名称和路径。os_file_name中的路径必须为安装SQL服务器的计算机上的文件夹,指定数据或日志文件的大小，默认单位在KB，也可以指定用MB单位。如果没有指定大小，则默认是1MB,指定文件能够增长到的最大长度，默认单位在KB，也可以指定用MB单位。如果没有指定长度，文件将一直增长到磁盘满为止,指定文件的增长量，该参数不能超过MAXSIZE的值。默认单位在KB，也可以指定用MB单位。也可以使用百分比。如果没指定参数，默认百分之十，最小64K,该参数指定数据库的默认排序规则,3.3 创建数据库,SQL 语法中符号的意义在SQL语法中有的用中括号，也有的用尖拓号，它们分别代表了不同的意义，下面来说明各种符号代表的意义：在SQL语法中，SQL关键词一律用大写字母表示，如CREATE DATABASE；小写字母名称则是要用户替换的参数，例如 database_name。凡是可以省略的项目或参数，就用，例如PRIMARY。如果有多个选项可以选择其一使用，则用|隔开，例如max_size|UNLIMITED，表示可以用其中一个，但两都不可同时使用。,任务1 创建数据库,例3.1 创建一个名为“xsgl”数据库。其中主数据文件为10MB，最大大小不受限制，每次增长1MB；事务日志文件大小为1MB，最大大小不受限制，文件每次增长10%。,CREATE DATABASE xsgl/*数据库名*/ONPRIMARY/*主文件组*/(NAME=xsgl,/*主数据文件逻辑名*/FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1 MSSQLDataxsgl.mdf,SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=1MB)LOG ON(NAME=xsgl_log,FILENAME=C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataxsgl_log.ldf,SIZE=1MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)GO,任务1 创建数据库,例3.2 创建test数据库，包含一个主文件组和两个次文件组。,CREATE DATABASE testON PRIMARY/*定义在主文件组上的文件*/(NAME=pri_file1,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapri_file1.mdf,SIZE=10,MAXSIZE=50,FILEGROWTH=15%),(NAME=pri_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatapri_file2.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=15%),任务1 创建数据库,FILEGROUP Grp1/*定义在次文件组Grp1上的文件*/(NAME=Grp1_file1,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp1_file1.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=Grp1_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp1_file2.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),FILEGROUP Grp2/*定义在次文件组Grp2上的文件*/(NAME=Grp2_file1,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp2_file1.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),(NAME=Grp2_file2,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataGrp2_file2.ndf,SIZE=10,MAXSIZE=50,FILEGROWTH=5),任务1 创建数据库,LOG ON/*定义事务日志文件*/(NAME=test_log,FILENAME=D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatatest_log.ldf,SIZE=5,MAXSIZE=25,FILEGROWTH=5)GO,任务1 创建数据库,事务日志SQL创建数据库的时候，会同时创建事务日志文件。事务日志是存放恢复数据所需的所有信息，是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。当数据库损坏时，管理员可以使用事务日志还原数据库。每一个数据库必须至少拥有一个事务日志文件，允许拥有多个日志文件。事务日志文件的扩展名为.ldf，日志文件的大小至少是512KB。,任务1 创建数据库,事务日志文件和数据文件必须分开存放，优点如下。（1）事务日志文件可以单独备份。（2）有可能从服务器失效的事件中将服务器恢复到最近的状态。（3）事务日志不会抢占数据库的空间。（4）可以很容易地监测到事务日志的空间。（5）在向数据文件和事务日志文件写入数据时会产生较少的冲突，这有利于提高SQL的性能。,任务1 创建数据库,课堂练习1、创建数据库名为XS的数据库，数据文件名为XS1 _Data.Mdf，存储在E:下，初始大小为3MB，最大为10MB，允许数据库自动增长，增长方式按10比例增长。事务文件为XS1_Log.Ldf，存储在E盘下，初始大小为5MB，最大为10MB，文件增量以1MB增长,任务1 创建数据库,课堂练习,CREATE DATABASE XS ON(NAME=XS1_Data,FILENAME=E:XS1_Data.Mdf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=10%)LOG ON(NAME=XS1_Log,FILENAME=E:XS1_Log.Ldf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)GO,任务1 创建数据库,课堂练习2、创建数据库名为Sales的数据库，数据文件名为Sales.Mdf，存储在E:下，初始大小为4MB，最大为10MB，文件增量以1MB增长。事务文件为Sales_Log.Ldf，存储在E盘下，初始大小为2MB，最大为5MB，文件增量以1MB增长。,任务1 创建数据库,课堂练习,CREATE DATABASE Sales ON PRIMARY(NAME=Sales,FILENAME=E:Sales.Mdf,SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOG ON(NAME=Sales_Log,FILENAME=E:Sales_Log.Ldf,SIZE=2MB,MAXSIZE=5MB,FILEGROWTH=1MB)GO,任务2 管理和维护数据库,打开数据库,USE database_name,USE xsgl,任务2 管理和维护数据库,查看数据库信息（1）使用管理工具查看,任务2 管理和维护数据库,显示了xsgl的数据库的信息,任务2 管理和维护数据库,（2）使用T-SQL语句查看,sp_helpdb 数据库名,任务2 管理和维护数据库,修改数据库配置（1）使用管理工具查看,任务2 管理和维护数据库,其余操作与创建数据库的过程相似,任务2 管理和维护数据库,（2）使用T-SQL语句修改数据库配置,ALTER DATABASE database_name ADD FILE(NAME=logical_file_name,FILENAME=physical_file_name.mdf,SIZE=size,MAXSIZE=max_size|UNLIMITED,FILEGROWTH=grow_increment),ADD LOG FILE：增加日志文件REMOVE FILE：删除数据文件ADD FILEGROUP：增加文件组REMOVE FILEGROUP：删除文件组MODIFY FILE：更改文件属性MODIFY NAME：重命名数据库MODIFY FILEGROUP：更改文件组属性,任务2 管理和维护数据库,例3.3 用T-SQL命令将xsgl重命名为“学生管理数据库”,ALTER DATABASE xsgl MODIFY NAME=学生管理数据库,SP_RENAMEDB xsgl,学生管理数据库,任务2 管理和维护数据库,例3.4 为“学生管理数据库”增加一个数据库文件,ALTER DATABASE 学生管理数据库 ADD FILE(NAME=xsgl2,FILENAME=D:xsgl2.ndf),任务2 管理和维护数据库,课堂练习：1、为数据库XS增加数据文件XSBAK，文件初始大小为5MB，最大大小为50MB，增长幅度为5,ALTER DATABASE XSADD FILE(NAME=XSBAK,FILENAME=E:XSBAK_data.ndf,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=5%)GO,任务2 管理和维护数据库,课堂练习：2、修改学生数据库XS中的第二个数据文件XSBAK最大大小为50MB,ALTER DATABASE XSMODIFY FILE(NAME=XSBAK,MAXSIZE=50MB)GO,任务2 管理和维护数据库,课堂练习：3、删除学生数据库XS中的第二个数据文件,ALTER DATABASE XSREMOVE FILE XSBAK,任务2 管理和维护数据库,删除数据库 当不再需要数据库中的数据时，为了节省空间，可以删除数据库。删除数据库时，SQL Server将从服务器的磁盘中永久删除文件和数据。因此，在删除数据库前，请确认数据库中已经没有任何可需要的数据了。,任务2 管理和维护数据库,（1）使用管理工具删除数据库,任务2 管理和维护数据库,在确认删除对话框中有一个删除备份历史的复选框，选中该复选框，删除数据库的同时，也删除数据库的备份历史,任务2 管理和维护数据库,（2）使用T-SQL语句删除数据库,DROP DATABASE 数据库名称,DROP DATABASE 学生管理数据库,任务2 管理和维护数据库,什么情况不能删除数据库1、当数据库正在执行数据复制2、数据库正在恢复3、当有用户正在对数据库进行操作4、不能删除系统数据库,任务2 管理和维护数据库,分离和附加数据库为了避免SQL Server同时管理太多的数据库，耗用不必要的系统资源，我们可以将暂时用不着的数据库从SQL Server中分离，即取消SQL Server与数据库各个实体文件的关系，这样一来，SQL Server可以稍微轻松一点儿。如果后来要再使用已分离的数据库，没有关系，只要再将数据库附加到SQL Server中就可以了。,任务2 管理和维护数据库,分离与附加数据库适用于以下两种情况：(1)将数据库从一台计算机移到另一台计算机。(2)将数据库从一台计算机的一个磁盘移到另一个磁盘。分离与附加数据库有两种方法：一种是使用管理工具，另一种是使用查询分析器。,任务2 管理和维护数据库,（1）使用管理工具分离数据库,任务2 管理和维护数据库,如果存在使用本数据库的连接，勾选“删除连接”断开这些连接。只有无人在使用数据库，才能分离。,任务2 管理和维护数据库,使用管理工具附加数据库,任务2 管理和维护数据库,任务2 管理和维护数据库,任务2 管理和维护数据库,任务2 管理和维护数据库,任务2 管理和维护数据库,（2）使用T-SQL语句分离数据库,SP_DETACH_DB 数据库名,skipchecks=skipchecks,表示分离数据库后是否要进行更新统计，如果为true，则跳过。如果为false，则运行更新统计。,任务2 管理和维护数据库,例：使用查询分析器分离数据库xs，分离后不进行更新统计。,SP_DETACH_DB xs,true,任务2 管理和维护数据库,使用T-SQL语句附加数据库,CREATE DATABASE xsON PRIMARY(NAME=xs_Data,FILENAME=E:xs_Data.Mdf)LOG ON(NAME=xs_Log,FILENAME=E:xs_Log.Ldf)FOR ATTACHGO,SP_ATTACH_DB xs,E:xs_Data.Mdf,任务2 管理和维护数据库,使用命令行方式显示数据库XSGL可以重新设置的选项。,SP_DBOPTION XSGLGO,任务2 管理和维护数据库,使用命令行方式将XSGL数据库设置为只读。去掉只读属性,SP_DBOPTION XSGL,read only,trueGO,SP_DBOPTION XSGL,read only,falseGO,任务2 管理和维护数据库,使用命令行方式将XSGL数据库设置为单用户方式。去掉属性,SP_DBOPTION XSGL,single user,trueGO,SP_DBOPTION XSGL,single user,falseGO,本章小结,本章主要讨论了数据库的各种操作。数据库是SQL Server 2005中最基本的操作对象。对数据库的创建、查看、修改、和删除。这些基本操作是进行数据库管理与开发的基础。通过学习，要求熟练掌握使用管理工具和T-SOL语言进行数据库的创建、修改、查看、删除的操作技能。,习题,SOL Server 2005的数据库对象有哪些？简述SOL Server 2005物理数据库的结构。,