第4章创建和管理SQLServer数据库.ppt

4.1创建第一个数据库,利用企业管理器创建第一个数据库,4.2数据库的存储结构,数据库存储结构,逻辑存储结构,物理存储结构,逻辑存储结构,一、数据库的逻辑存储结构指的是指数据库是由哪些性质的信息所组成，SQL Server的数据库不仅仅只是数据的存储，所有与数据处理操作相关的信息都存储在数据库中。实际上，SQL Server的数据库是由诸如表、视图、索引等各种不同的数据库对象所组成。,物理存储结构,二、数据库的物理存储结构则是讨论数据库文件是如何在磁盘上存储的，数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成，一个数据库至少应该包含一个数据库文件和一个事务日志文件。,物理文件名和逻辑文件名,注意：SQL Server 2000中的数据和事务日志文件不能存放在压缩文件系统或象共享网络目录等远程的网络驱动器上。SQL Server 2000的文件拥有两个名称，即逻辑文件名和物理文件名。当使用Transact-SQL命令语句访问某一个文件时，必须使用该文件的逻辑名。物理文件名是文件实际存储在磁盘上的文件名，而且可包含完整的磁盘目录路径。,数据库文件,主数据库文件（.mdf）,辅助数据库文件(.ndf),事务日志文件(.ldf),数据库文件(操作系统文件),1主数据库文件（Primary Database File）,一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。主数据库文件用来存储数据库的启动信息以及部分或者全部数据，是所有数据库文件的起点，包含指向其它数据库文件的指针。一个数据库只能有一个主数据库文件。,2辅助数据库文件（Secondary Database File）,用于存储主数据库文件中未存储的剩余数据和数据库对象，一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。辅助数据库文件的扩展名为ndf（简称为辅助文件）。,3事务日志文件,日志文件是用来记录用户对数据库所进行的所有操作的地方。但是，也存在不记录日志的数据库操作。SQL Server操作规则：,数据库修改,写日志,下达事务命令,了解事务,事务：一次性完成的一组针对数据库的操作。如：表1中插入一条数据，在表2中删除一条数据，并将两个操作定义为一个事务,3事务日志文件,日志文件是维护数据库完整性的重要工具。存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。当一个日志文件写满后，可以再继续在别的日志文件上填写将要执行的操作。日志文件的大小至少是512KB。,文件组（filegroup）,文件组：把数据库文件分别组成不同的文件组，按组的方式对文件进行管理。作用：可以有效提高数据库的读写性能。,文件组作用,2000 根据文件中的可用空间量将一定比例的数据写入文件组的每个文件，而不是将所有的数据先写满第一个文件，接着再写入下一个文件。例如，如果文件 f1 有 100 兆字节(MB)可用空间，文件 f2 有 200 MB 可用空间，则从文件 f1 中分配一个扩展盘区，从文件 f2 中分配两个扩展盘区，依此类推。两个文件几乎同时填满,文件组作用,写数据,写数据,不是,文件组类型,文件组类型,主文件组（primary）,用户自定义文件组（user defined）,默认文件组（default）,文件组类型,数据库可以看成由一个主文件组和任意用户定义的文件组组成。主文件组为包含主数据文件的文件组。用户定义的文件组是指在首次创建或以后更改数据库时，用户明确创建的任何文件组。,各种文件组概述,利用文件组可以使主文件组中包含了所有的系统表，当建立数据库时，主文件组包括主数据库文件和未指定组的其他文件。用户定义文件组中可以指定一个默认文件组，那么在创建数据库对象时如果没有指定将其放在哪一个文件组中，就会将它放在缺省文件组中。如果没有指定默认文件组，则主文件组为默认文件组。,各种文件组概述,一个文件只能存在于一个文件组中，一个文件组也只能被一个数据库使用；日志文件是独立的，它不能作为任何文件组的成员,4.3创建SQL Server数据库,采用企业管理器创建采用查询分析器创建,数据库命名,数据库命名必须符合SQL Server 2000的标识命名规则。标识符用于标识数据库对象。它们用字符串表示，最多可包含128个字符，包括字母、数字和以下字符：_、#、$。每个名字必须以一个字母或下列字符中的一个打头：_、#在 SQL Server 中，某些处于标识符开始位置的符号具有特殊意义。以 符号开始的标识符表示局部变量或参数。以一个#开始的标识符表示临时表或过程。以双数字符号(#)开始的标识符表示全局临时对象。标识符不能是 Transact-SQL 的保留字。,使用Transact-SQL语言创建数据库，语法如下：CREATE DATABASE database_name ON PRIMARY，n，n LOG ON，n FOR RESTORE:=(NAME=logical_file_name，FILENAME=os_file_name，SIZE=size，MAXSIZE=max_size|UNLIMITED，FILEGROWTH=growth_increment)，n:=FILEGROUP filegroup_name，n,语法约定,：多个代选项目。expression|user|null,:可选项，可要可不要,1,n:表示同样的选项可以重复1到n遍,可以替换的内容,变量1|变量2:选用变量1或变量2（只能从 竖杆隔开的项目中选择一个）,transact-SQL创建数据库,各参数说明如下：database_name：数据库的名称，最长为128个字符。PRIMARY：该选项是一个关键字，指定主文件组中的文件。LOG ON：指明事务日志文件的明确定义。NAME：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。,transact-SQL创建数据库,FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE：指定数据库的初始容量大小。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。,transact-SQL创建数据库,创建简单数据库创建指定文件名、日志名的数据库创建指定文件名、日志名、文件组的数据库,思考题1,例3-2-1：使用CREATE DATABASE创建一个student数据库，所有参数均取默认值。例3-2-2：创建一个Student1数据库，该数据库的主文件逻辑名称为Student1_data，物理文件名为Student1.mdf，初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为Student1_log，物理文件名为Student1.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为1MB。,思考题2,例3-2-3：创建一个指定多个数据文件和日志文件的数据库。该数据库名称为students，有1个10MB和1个20MB的数据文件和2个10MB的事务日志文件。数据文件逻辑名称为student1和student2，物理文件名为student1.mdf和student2.mdf。主文件是student1，由primary指定，两个数据文件的最大尺寸分别为无限大和100MB，增长速度分别为10%和1MB。事务日志文件的逻辑名为studentlog1和studentlog2，物理文件名为studentlog1.ldf和studentlog2.ldf，最大尺寸均为50MB，文件增长速度为1MB。,修改数据库,改变分配给数据库的数据或事务日志空间。添加或删除数据和事务日志文件创建文件组创建默认文件组更改数据库名称更改数据库的所有者 压缩数据库,修改数据库,用企业管理器用查询分析器,用企业管理器修改数据库,限制访问包含两个选项：“db_owner,dbcreator或sysadmin的成员”选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；“单用户”选项表示数据库在同一时间只能供一个用户使用。ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。递归触发器：指定是否允许触发器递归调用。自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。,用企业管理器修改数据库,残缺页检测：允许自动检测有损坏的页。页是数据库内容的基本存储单位，每个页的大小为8KB。由于SQL Server对页的读写单位是大小为512字节的扇区，当每个页的第一个扇区被成功写入后，SQL Server就认为此页已经被成功写入。因此，如果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。,用企业管理器修改数据库,自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件中未用空间超过其大小的25%时，系统将会自动缩减文件，使其未用空间等于25%。当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。自动创建统计信息：在优化查询时，根据需要自动创建统计信息。使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,Alter database databasename add file,n to filegroup filegroupname|add log file,n|remove file logical_file_name|remove filegroup filegroup_name|modify name=new_databasename|modify file|add filegroup filegroup_name|modify filegroup filegroup_name filegroup_property,修改数据库语法解释,add file,n to filegroup filegroupname：表示向指定的文件组中添加新的数据文件。add log file,n：增加新的日志文件。remove file logical_file_name：删除指定的操作系统文件。remove filegroup filegroup_name：删除指定的文件组。modify file：修改某个操作系统文件。,修改数据库语法解释,add filegroup filegroup_name：增加一个文件组。modify filegroup filegroup_name：修改某个指定文件组的属性。文件组属性有modify name=new_databasename：重命名数据库。,Readonly:只能读取文件组中的数据，而不能修改其中的数据,Readwrite:即可以读取文件又可以修改该文件组中的数据,Default:设置该文件组为默认文件组,改变分配给数据库的数据或事务日志空间,ALTER DATABASE Test1 MODIFY FILE(NAME=test1dat3,SIZE=20MB)GO,添加或删除数据文件,添加数据文件ALTER DATABASE Test1 ADD FILE(NAME=Test1dat2,FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat2.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)删除数据文件ALTER DATABASE Test1 REMOVE FILE test1dat4 GO,添加日志文件,ALTER DATABASE Test1 ADD LOG FILE(NAME=test1log2,FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatatest2log.ldf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB),ALTER DATABASE Test1（建立文件组）ADD FILEGROUP Test1FG1 GO ALTER DATABASE Test1（建立文件组文件）ADD FILE(NAME=test1dat3,FILENAME=c:Program FilesMicrosoft SQL ServerMSSQLDatat1dat3.ndf,SIZE=5MB,MAXSIZE=100MB,FILEGROWTH=5MB)TO FILEGROUP Test1FG1 ALTER DATABASE Test1（设置默认组）MODIFY FILEGROUP Test1FG1 DEFAULT GO,文件组修改,重命名数据库,在给数据库更名前，必须在企业管理器中设置该数据库为单用户状态。在单用户状态下，只能有一个用户使用该数据库，因此，这时不能同时打开企业管理器和查询分析器。,重命名数据库,采用系统存储过程sp_renamedb old_name,new_nameSp_renamedb mydb,mydb2采用修改数据库的SQL语句Alter database mydb modify name=mydb2,更改数据库的所有者,sp_changedbowner Albert sp_helplogins显示有效 login 值的列表,收缩数据库,收缩指定数据库中的数据文件大小 DBCC SHRINKDATABASE(database_name,target_percent,NOTRUNCATE|TRUNCATEONLY),收缩数据库语法解释,database_name是要收缩的数据库名称target_percent是数据库收缩后的数据库文件中所要的剩余可用空间百分比。NOTRUNCATE导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。TRUNCATEONLY导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行,收缩数据库示例,示例：DBCC SHRINKDATABASE(mydb,10)DBCC SHRINKDATABASE(mydb,10,notruncate),收缩文件,使用DBCC SHRINKFILE命令收缩数据库的指定数据文件或日志文件大小DBCC SHRINKFILE的语法格式如下：DBCC SHRINKFILE(file_name,target_size|,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY),收缩文件语法解释,file_name：要收缩文件的逻辑名称。target_size：将文件缩小到指定的长度，以MB为单位。如果不指定，将文件减少到最大程度。EMPTYFILE：将数据从指定文件中转移到同一文件组中的其它文件。NOTRUNCATE和TRUNCATEONLY：与DBCC SHRINKDATABASE命令中相同。,收缩文件示例,DBCC SHRINKFILE（mydb_dat_1,15,notruncate）,删除数据库,DROP DATABASE database_name,.n DROP DATABASE mydb(删除mydb数据库)DROP DATABASE pubs,newpubs（删除两个数据库）,4.4管理SQL Server数据库,Use：选择要执行操作的数据库Sp_helpdb:查看当前服务器上所有数据库的信息Sp_databases：查看当前服务器上所有可以使用的数据库Sp_helpfile查看当前数据库的所有文件信息Sp_helpfilegroup：查看当前数据库上所有文件组信息,思考题,复习修改数据库的SQL语句,