1、第第4章章 数据库的创建与管理数据库的创建与管理4.1 数据库与事务日志数据库与事务日志 SQL Server 2000数据库是一种关系型数据库，逻辑上，数据是存放在一个个相互关联的二维表中。数据库系统1.数据库2.数据管理系统3.数据库应用程序DBA 数据库管理系统的作用是描述数据库的逻辑结构与存储结构，管理数据库系统的运行1.控制用户的控制用户的访问权限与并发访问数量访问权限与并发访问数量2.控制数据库中数据编辑（添加、修改与删除）控制数据库中数据编辑（添加、修改与删除）3.记录数据库的记录数据库的工作日志、监视数据库的性能工作日志、监视数据库的性能4.对数据库进行对数据库进行备份及恢复备
2、份及恢复等等功功能能数据库应用程序是利用某种编程语言编写的应用程序。如用VB、VC、C+BUILDER、JAVA、.NET、POWERBUILER等等1.所谓事务是SQL Server 的一个工作单元，一个事务内的所有语句作为一个整体执行ABCX事务12.事务日志：目的是记录数据库中事务所有操作并进行相关备份，以便发生错误时予以恢复。事务日志的作用有以下几个方面 数据库操作发生错误发生错误时，执行“回滚回滚”事务事务的操作 执行事务的过程出现死机或断电死机或断电，事务未执行完。重新启动数据库时，事务日志可以回滚尚未完成的事务回滚尚未完成的事务，以保证数据库中的内容达到一致 数据库发生崩溃发生崩
3、溃时，可以利用以前的数据库备份以及事务数据库备份以及事务日志将数据库恢复日志将数据库恢复到崩溃前的状态破坏数据库的完整性返返 回回4.2 数据库的存储结构数据库的存储结构 SQL Server 逻辑视图:数据库对象，如:表、视图、存储过程、触发器、索引和约束等 物理结构:一定的数据文件形式存储在硬盘中企业管理器1文件：指数据库的系统文件，一个数据库都对应于两个或多个文件，在该数据库内创建的数据库对象（如表、视图等）都保存在这些文件中。分为三类：文文件件主数据文件主数据文件二级数据文件二级数据文件日志文件日志文件是数据库的起点，指向数据库中文件的其他部分。默认扩展名为“.mdf”，必须且只能包含
4、一个主数据文件，存储启动信息、数据和数据库对象启动信息、数据和数据库对象是辅数据文件，默认扩展名为“.ndf”。可以有0个以上的二级数据文件，用于存储主数据文件中没有存储主数据文件中没有存储的数据和数据库对象存储的数据和数据库对象。存放对数据库进行操作的事务日志，在数据库遭到破存放对数据库进行操作的事务日志，在数据库遭到破坏时，可以坏时，可以利用日志文件恢复数据利用日志文件恢复数据，默认扩展名，默认扩展名“.ldf”。必须包含一个以上的日志文件。必须包含一个以上的日志文件2 数据库的物理存储结构 数据库的数据文件和日志文件是存储在磁盘上的。日志文件是由一系列日志记录组成数据文件则由页面和扩展盘
5、区组成最小基本存储单位，有多种页1页8千字节1个扩展盘区=8页是表和索引的基本存储单位现在知道这么多就够了统一扩展盘区：为单个对象所独占统一扩展盘区：为单个对象所独占混合扩展盘区混合扩展盘区 逻辑环方式存储逻辑环方式存储,日志的不活动部分在备份后被自动删除返返 回回4.3 SQL Server的系统数据库与示例数据库1.SQL Server的数据库分为两大类：系统数据库和用户数据库2.系统数据库是SQL Server内置的，它们用于管理目的，不能随便更改或删除3.用户数据库一般由用户创建，其使用与管理由其创建者决定4.安装时建立：4个系统数据库：master，tempdb，model，msdb
6、 2个用户示例数据库：pubs和Northwind概述：概述：1系统数据库系统数据库1.Master：记录SQL Server系统的相关信息，如：登录账户登录账户及其密码、系统配置信息、用户数据库密码、系统配置信息、用户数据库的位置等，包括许多系统存储系统存储过程过程2.Tempdb：保存所有临时表、临时存储过程以及其他临时数据临时表、临时存储过程以及其他临时数据 3.Model：是新建数据库的模板模板，每创建一个新数据库，其内容都新数据库，其内容都与模板相同与模板相同4.Msdb：由SQL Server Agent使用，用于自动执行有关操作,如自自动进行备份、警报和复制动进行备份、警报和复制
7、等2 2 示例数据库示例数据库pubs和Northwind：联机丛书中的例题是以这两个数据库的数据为样本的3 3 系统表系统表 有两类系统表，表名以“sys”开头。分别位于master数据库和用户数据库中 另一类系统表位于每个数据库中，继承自继承自model数数据库据库，其结构与数量都与model数据库一致存放服务器级的系统信息存放数据库级信息返返 回回4.4 SQL Server的标识符1.标识符是指数据库、数据库对象数据库、数据库对象等（如表、视图、存储过程、触发器、规则等、列、变量）的名称名称2.SQL Server标识符分两类：概述：概述：规则标识符 界定标识符1 规则标识符（相关的逻
8、辑文件名命名）定义：是可以直接使用的标识符，其书写遵守如下规则：第一个字符是Unicode 标准所定义的字符：26个英文字母、下划线_、#，及其他语言中的字母符号 说明：其中以一个“”开头的表示局部变量局部变量；以“”开头的表示全局变量全局变量；以一个“#”开头的表示局部临时数据库临时数据库对象对象，以“#”开头的表示全局临时数据库对象全局临时数据库对象第一个字符后的字符可以是Unicode标准所定义的字符、_、#、$或阿拉伯数字 规则标识符中不允许有空格不允许有空格、或其他特殊字符特殊字符（如：、：-）不能不能与SQL Server保留关键保留关键字相同，无论是大写或小写无论是大写或小写de
9、clare error1 int,error2 int,error3 int,error4 int,error5 int,error6 int,error7 int,error8 begin tranCREATE TABLE#ck_kc_phb(hp_bhchar(16),hp_jldwchar(8),)CREATE TABLE#hp_temp(hp_bhchar(16),hp_slfloat)insert into#ck_kc_phbinsert into#ck_kc_phbselect hp_bh,jldw,dj,ck_kc.sl,0,0,0,0,0,0,0,0,0,0,0,0,0 fro
10、m ck_kcwhere ck_bh=ck_bh_gsset error1=error2 界定标识符 定义：建立规则标识符以外的形式定义标识符，通过这类标识符外加上界定符号，标示明该界定符号内的字符为标识符，这类标识称为界定标识符。界定符号：、英文单引号 、英文双引号“”三种 说明：方括号与英文双引号英文双引号均可以用于界定标识符，单引号只能是字符串的界定符号 举例：举例：Select *from ck_kcSelect*from ck_kcSelect*from ck_kc等价Select*from ck_kc错误注意是英文双引号！3 命名规则 对象命名规则 对象的全名为：服务器名服务器名.
11、数据库名数据库名.拥有者名拥有者名.对象名对象名，使用可以省略省略服务器名、数据库名、拥有者名中的一项或多项。举例：举例：Select*FROM AHUT-TOM.sms.dbo.Blanks in Table Name服务器名数据库名所有者表名指代明确就可以了指代明确就可以了SQL Server 2000 文件名：文件命名规则 略，可跳过（1）逻辑文件名：在T-SQL中引用的文件名，符合SQL Server中的标识符规则标识符规则（2）物理文件名：遵从NT或者或者windows98的文件命名规范 返返 回回4.5 创建用户数据库1.用企业管理器2.编写Transact-SQL（T-SQL）语
12、句概述：概述：SQL Server 中创建数据库有两种方法中创建数据库有两种方法1 使用企业管理器创建数据库在企业管理器中，应该在某个数据库服务器（或称数据库实例）下创建用户数据库。2 使用T-SQL创建数据库CREATE DATABASE database_name ON PRIMARY (NAME=logical_file_name,FILENAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment ),.n LOG ON (NAME=logical_file_name,FILE
13、NAME=os_file_name ,SIZE=size ,MAXSIZE=max_size|UNLIMITED ,FILEGROWTH=growth_increment ),.n COLLATE collation_name 问：如何使用这么复杂的语法？命令说明：（1）database_name：创建的：创建的数据库名称数据库名称，该名称在数据库，该名称在数据库服务器中服务器中必须惟一必须惟一，且符合标识符的规则，且符合标识符的规则（2）关键字）关键字ON：用于：用于指定存储数据库数据的磁盘文件（数据存储数据库数据的磁盘文件（数据文件）文件）（3）PRIMARY:指定其后定义的第一个文件是主
14、数据文件。指定其后定义的第一个文件是主数据文件。（4）LOG ON：指定数据库的日志文件。：指定数据库的日志文件。先了解4点内容！例：创建一个名为例：创建一个名为Report的数据库，定义一个主文件、一个的数据库，定义一个主文件、一个二级文件和一个日志文件。主文件的逻辑文件名为二级文件和一个日志文件。主文件的逻辑文件名为Rep1,磁磁盘文件名为盘文件名为rep1_dat.mdf。二级文件的逻辑文件名为。二级文件的逻辑文件名为Rep2,磁盘文件名为磁盘文件名为rep2_dat.ndf。事务日志文件的逻辑文件名为。事务日志文件的逻辑文件名为Rep1_log，磁盘文件名为，磁盘文件名为rep1_lo
15、g.ldf。所有文件的初始容量。所有文件的初始容量都是都是5MB，最大容量限制都是，最大容量限制都是10MB，在最大容量限制内，在最大容量限制内，当文件空间不够时每次的增长量都是当文件空间不够时每次的增长量都是1MB.CREATE DATABASE ReportONPRIMARY(NAME=Rep1,FILENAME=E:Microsoft SQL ServerMSSQLDatarep1_dat.mdfSIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB),(NAME=Rep2,FILENAME=E:Microsoft SQL ServerMSSQLDatarep2_dat
16、.ndfSIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)LOG ON(NAME=Rep1_log,FILENAME=E:Microsoft SQL ServerMSSQLDatarep1_log.ldfSIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)GO返返 回回4.6 管理数据库1 查看数据库的信息 使用系统存储过程查看数据库信息使用系统存储过程查看数据库信息 使用企业管理器查看数据库信息使用企业管理器查看数据库信息系统存储过程，如：系统存储过程，如：sp_helpdb、sp_database、sp_helpfile等等利用企业管理器查看数
17、据库属性2 修改数据库（1）修改数据库用户数：多用户状态和单用户状态，用系统存储过程或企业管理器存储过程命令格式：sp_dboption dbname=database_name ,optname=option_name ,optvalue=value 命令说明：（1）database_name：表示数据库的名称（2）option_name：选项名称：dbo use only=true 只有dbo可以使用数据库，=false 任何用户可以使用single user=true 单用户状态 =false时多个用户同时访问格式需要记吗？修改数据库为只读！删除错误！删除错误！去除数据库去除数据库MMS
18、的只读属的只读属性性格式：sp_renamedb old_dbname，new_ dbname命令说明：old_dbname与new_ dbname分别表示数据库的旧名与新名（2）修改数据库名称）修改数据库名称系统存储过程sp_renamedb用于对数据库进行更名例：例：sp_renamedb mms mms_new注：企业管理器不能改名数据库，为什么？，为什么？改名前改名前执行改名执行改名系统数据库不能删除，只有用户数据库才能够被删除。用系统存储过程用企业管理器用企业管理器 删除数据库删除数据库3 3 删除数据库删除数据库命令格式：DROP DATABASE database_name,.n
19、说明：database_name,.n：表示被删除的数据库名称，可以一次删除多个数据库请同学自请同学自己联系一己联系一下！下！返返 回回T-sql创建数据库示例 A.创建创建指定数据文件和事务日志文件指定数据文件和事务日志文件的数据的数据库库 下面的示例创建名为 SalesSales 的数据库。1、没有使用关键字 PRIMARY，第一个文件(Sales_datSales_dat)成为主文件。2、Sales_datSales_dat 文件的 SIZE 参数没有指定 MB 或 KB，因此默认为 MB，以兆字节为单位进行分配。Sales_logSales_log 文件以兆字节为单位进行分配，因为 S
20、IZE 参数中显式声明了 MB 后缀。CREATE DATABASE SalesON(NAME=Sales_dat,FILENAME=e:microsoft sql servermssqldatasaledat.mdf,SIZE=1,MAXSIZE=5,FILEGROWTH=1)LOG ON(NAME=Sales_log,FILENAME=e:microsoft sql servermssqldatasalelog.ldf,SIZE=1MB,MAXSIZE=3MB,FILEGROWTH=1MB)GO删除数据库：DROP DATABASE Sales B.指定指定多个数据文件和事务日志文件多个数
21、据文件和事务日志文件创建数创建数据库据库 注意注意 FILENAME FILENAME 选项中所用的文件扩展名：选项中所用的文件扩展名：主要数据文件使用主要数据文件使用.mdf.mdf，次要数据文件使，次要数据文件使用用.ndf.ndf，事务日志文件使用，事务日志文件使用.ldf.ldf USE masterGOCREATE DATABASE ComputerONPRIMARY(NAME=Computer1,FILENAME=e:microsoft sql servermssqldataComputerDat1.mdf,SIZE=1MB,MAXSIZE=3,FILEGROWTH=1),(NAM
22、E=Computer2,FILENAME=e:microsoft sql servermssqldataComputerDat2.ndf,SIZE=1MB,MAXSIZE=3,FILEGROWTH=1)LOG ON(NAME=ComputerLog,FILENAME=e:microsoft sql servermssqldataComputerLog.ldf,SIZE=1MB,MAXSIZE=2,FILEGROWTH=1)GO C.创建创建简单的简单的数据库数据库 本例创建名为 ProductsProducts 的数据库，并指定单个文件。指定的文件成为主文件，并会自动创建一个 1 MB 的事务
23、日志文件。因为主文件的 SIZESIZE 参数中没有指定 MB 或 KB，所以主文件将以兆字节为单位进行分配。因为没有为事务日志文件指定，所以事务日志文件没有 MAXSIZE，可以增长到填满所有可用的磁盘空间为止。USE master GO CREATE DATABASE Products ON (NAME=prods_dat,FILENAME=c:program filesmicrosoft sql servermssqldataprods.mdf,SIZE=4,MAXSIZE=10,FILEGROWTH=1)GO D.不指定文件创建数据库不指定文件创建数据库 下面的示例创建名为 mytes
24、t mytest 的数据库，并创建相应的主文件和事务日志文件。因为该语句没有 项，所以主数据库文件的大小为 modelmodel 数据库主文件的大小。事务日志文件的大小为 model 数据库事务日志文件的大小。因为没有指定 MAXSIZE，文件可以增长到填满所有可用的磁盘空间为止。CREATE DATABASE mytest 上机作业(一)分别使用SQL Server 2000企业管理器企业管理器和Transact-SQL语句语句，按下列要求创建和修改用户数据库。要求如下：数据库名“TestDB”.数据库中包含一个数据文件，逻辑文件名为TestDB_data,磁盘文件名为TestDB_data
25、.mdf,文件初始容量为5MB,最大容量为15MB,文件容量递增值为1MB。事务日志文件，逻辑文件名为TestDB_log,磁盘文件名为TestDB_log.ldf,文件初始容量为5MB,最大容量为10MB,文件容量递增值为1MB。对该数据库作如下修改：添加一个数据库文件，逻辑文件名为TestDB2_data,实际文件名为TestDB2_data.ndf,文件初始容量为1MB,最大容量为6MB,文件容量递增值为1MB。将日志文件的最大容量增加为15MB,递增值改为2MB.（二）微软教材P56-3 分别使用SQL Server 2000企业管理器企业管理器和Transact-SQL语句语句，按下
26、列要求创建和修改用户数据库。要求如下：数据库名称为Library.主文件组有如下两个数据文件：第一个数据文件：逻辑文件名为LibraryData1,实际文件名为”C:cyjLibraryData_1.mdf”,文件初始容量为3MB,最大容量不受限制,文件容量递增率为20%。第二个数据文件：逻辑文件名为LibraryData2,实际文件名为”C:cyjLibraryData_2.ndf”,文件初始容量为3MB,最大容量不受限制,文件容量递增值为1MB。（3）日志文件只有一个数据文件，逻辑名为LibraryLog，文件名为”C:cyjLibraryLog.ldf”,文件初始容量为2MB,最大容量为5 MB,文件容量递增值为1MB。
