sqlserver创建修改数据.ppt

SQL SERVER 2000中文版入门与提高主讲：王玮,第七章SQL SERVER 2000数据库管理,本节主要内容,1、文件和文件组的分类与使用2、如何创建数据库3、查看数据库信息4、对数据库更名5、修改数据库选项和大小6、缩小数据库7、如何删除数据库8、数据库的物理存储结构,数据库的存储结构分为逻辑存储结构和物理存储结构两种。数据库的逻辑存储结构指的是数据库是由哪些性质的信息所组成。诸如表、视图、索引等各种不同的数据库对象所组成。数据库的物理存储结构指数据库文件是如何在磁盘上存储的。数据库在磁盘上是以文件为单位存储的，由数据库文件和事务日志文件组成。注意：一个数据库至少应该包含一个数据库文件和一个事务日志文件。,数据库文件,主数据文件(.mdf)：存储数据信息和数据库 的启动信息。次数据文件(.ndf)：存储主数据文件存储不下的数据信息。日志文件(.ldf)：存储数据库的所有事务日志信息，包含用于恢复数据库的日志信息。,数据库文件,1主数据库文件（Primary Database File）一个数据库可以有一个或多个数据库文件，一个数据库文件只能属于一个数据库。当有多个数据库文件时，有一个文件被定义为主数据库文件（简称为主文件），其扩展名为mdf。注意：一个数据库只能有一个主数据库文件。,数据库文件,2辅助数据库文件（Secondary Database File）用于存储主数据库文件中未存储的剩余数据和数据库对象，辅助数据库文件的扩展名为ndf（简称为辅助文件）。注意：一个数据库可以没有辅助数据库文件，但也可以同时拥有多个辅助数据库文件。,数据库文件,3事务日志文件 存储数据库的更新情况等事务日志信息，当数据库损坏时，管理员使用事务日志恢复数据库。注意：每一个数据库至少必须拥有一个事务日志文件，而且允许拥有多个日志文件。事务日志文件的扩展名为ldf，日志文件的大小至少是512KB。SQL Server事务日志采用提前写入的方式。,为了方便管理、提高系统性能，将多个数据库文件组织成一组，即称为数据库文件组。,文件组,数据库文件组,与数据库文件一样，文件组分为：主文件组（Primary File Group）自定义文件组（User_defined File Group）默认文件组（Default File Group）,注意：（1）每个文件或文件组只能用于一个数据库。（2）每个文件只能属于一个文件组。（3）日志文件是独立的。,数据库文件组,技巧：数据库文件组控制各个文件的存放位置，常常将每个文件建立在不同的硬盘驱动器上。这样可以减轻单个硬盘驱动器的存储负载，提高数据库的存储效率，从而实现提高系统性能的目的。,注意事项,1、新建文件组时不能将其设为默认文件组，因为其中不存在任何数据文件。需先建立数据文件，并将该文件置于新建文件 组中，再将该文件组设置为默认文件组。2、主数据文件不能删除，日志文件必须保留一个。3、若文件组中包含数据文件，该文件组不能被删除。,创建数据库的方法,使用向导创建数据库使用企业管理器（Enterprise Manager）创建数据库使用Transact-SQL语言创建数据库。,创建数据库的T-SQL语句,CREATE DATABASE 数据库名称ON PRIMARY(数据文件),(数据文件)FILEGROUP 文件组名称(数据文件),(数据文件)LOG ON(日志文件),(日志文件),创建数据库的T-SQL语句,CREATE DATABASE 数据库名称 ON PRIMARY（NAME=文件名，FILENAME=存放位置，SIZE=文件大小，MAXSIZE=文件最大容量|UNLIMITED，FILEGROWTH=增长方式），n FILEGROUP 文件组名称（NAME=文件名，FILENAME=存放位置，SIZE=文件大小，MAXSIZE=文件最大容量|UNLIMITED，FILEGROWTH=增长方式），n LOG ON（NAME=文件名，FILENAME=存放位置，SIZE=初始文件大小，MAXSIZE=文件最大容量|UNLIMITED，FILEGROWTH=增长方式），n,Database_name：数据库的名称，最长为128个字符。PRIMARY：该选项是一个关键字，指定主文件组中的文件。若不指定此关键字，则在命令中列出的第一个文件将被默认为主文件。ON：指定存放数据库的数据文件信息。LOG ON：指明事务日志文件的明确定义。NAME：指定数据库的逻辑名称，这是在SQL Server系统中使用的名称，是数据库在SQL Server中的标识符。,各参数说明如下：,FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。SIZE：指定数据库的初始容量大小。MAXSIZE：指定操作系统文件可以增长到的最大尺寸。FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以是KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。,说明：在Transact-SQL语言的命令格式中：表示是可选也可省略；，n：表示重复前面的内容；：表示应该用相应的内容替代；：表示是必选的；A|B：表示A和B只能选择一个，不能同时都选。,【例1】使用CREATE DATABASE创建一个“山东新华电脑学院”数据库，所有参数均取默认值。CREATE DATABASE 山东新华电脑学院,create database 软件1班on(name=软件开发_dat,filename=e:SQLdata软件1班_data.MDF,size=10,maxsize=UNLIMITED,filegrowth=10%)log on(name=软件开发_log,filename=e:SQLlog软件1班_log.LDF,size=1,maxsize=5,filegrowth=1)go,【例2】,create database 软件1班on PRIMARY(name=student1,filename=e:SQLstudent1.MDF,size=10,maxsize=UNLIMITED,filegrowth=10%)(name=student2,filename=e:SQLstudent2.MDF,size=20,maxsize=100,filegrowth=10%)log on(name=studentlog1,filename=e:SQLstudentlog1.LDF,size=10,maxsize=50,filegrowth=1)(name=studentlog2,filename=e:SQLstudentlog1.LDF,size=10,maxsize=50,filegrowth=1)go,【例3】,Create database 软件2班on PRIMARY(name=软件2,filename=e:SQL软件2.MDF,size=10,maxsize=UNLIMITED,filegrowth=10%),Filegroup fg1(name=软件22,filename=e:SQL软件22.MDF,size=20,maxsize=100,filegrowth=10%)log on(name=软件2_log,filename=e:SQL软件1_log.LDF,size=10,maxsize=50,filegrowth=1)go,【例4】,CREATE DATABASE 例5数据库ON(NAME=软件1,FILENAME=D:软件1.mdf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),(NAME=软件2,FILENAME=D:软件2.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),FILEGROUP fg1(NAME=软件3,FILENAME=D:软件3.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),FILEGROUP fg2(NAME=软件4,FILENAME=D:软件4.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%)LOG ON(NAME=软件_log,FILENAME=D:软件_log.ldf),【例5】,CREATE DATABASE 例6数据库ON(NAME=软件11,FILENAME=D:软件11.mdf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),(NAME=软件22,FILENAME=D:软件22.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),FILEGROUP fg1(NAME=软件33,FILENAME=D:软件33.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),FILEGROUP fg2(NAME=软件44,FILENAME=D:软件44.ndf,SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=5%),【例6】,注意：创建数据库时若未建立日志文件，只建立多个数据文件，此时DBMS会自动创建一个日志文件。其初始大小取多个数据文件初始容量之和的25和512K两者中最小的。,CREATE DATABASE 例7数据库ON(NAME=例7数据库,FILENAME=D:例7数据库.mdf,SIZE=8MB,MAXSIZE=10MB,FILEGROWTH=5%),【例7】,CREATE DATABASE 例8数据库ON(NAME=例8数据库,FILENAME=D:例8数据库.mdf,SIZE=1MB,MAXSIZE=10MB,FILEGROWTH=5%),【例8】,查看信息,/察看所有对象信息sp_help/察看数据库信息sp_helpdb 软件开发/查看数据库文件sp_helpfile 文件名/查看数据库文件组信息sp_helpfilegroup 文件组名,注意：创建数据库的过程实际上就是为数据库设计名称、设计所占用的存储空间和存放文件位置的过程等。,删除数据库,1利用企业管理器删除数据库 2利用Drop语句删除数据库 Drop database 数据库名称,n 说明：当数据库处于以下状态时不能被删除：数据库正在使用；数据库正在恢复；数据库包含用于复制的已经出版的对象。Drop语句一次可以删除一个或多个数据库。,【例】删除数据库山东新华电脑学院 DROP DATABASE 山东新华电脑学院 DROP DATABASE 例7数据库，例8数据库,删除数据库,数据库更名,sp_renamedb dbname=旧数据库名,newname=新数据库名例：sp_renamedb 例3数据库,3数据库 ALTER DATABASE 例3数据库 modify name=3数据库 注意：在重命名数据库之前，应该确保没有用户使用该数据库，而且数据库应该设置为“单用户”模式。,修改数据库,1.利用企业管理器修改数据库 2.使用ALTER DATABASE语句修改数据库,Alter database databasename add NAME=文件名，FILENAME=存放位置，SIZE=文件大小，MAXSIZE=文件最大容量|UNLIMITED，FILEGROWTH=增长方式），nto filegroup 文件组名称|add log file NAME=文件名，FILENAME=存放位置，SIZE=文件大小，MAXSIZE=文件最大容量|UNLIMITED，FILEGROWTH=增长方式），n|remove file 文件名|remove filegroup 文件组名|modify file|modify name=新数据库名称|add filegroup 文件组名称|modify filegroup filegroup_property|name=新文件组名称,ALTER DATABASE 旧数据库名MODIFY NAME=新数据库名或 sp_renamedb 旧数据库名,新数据库名,【例1】修改数据库名称,ALTER DATABASE 数据库名称ADD FILE(NAME=增加的数据文件名称,FILENAME=D:增加的数据文件.ndf),【例2】为数据库增加数据文件,ALTER DATABASE 数据库名称ADD LOG FILE(NAME=增加的日志文件,FILENAME=D:DBtest增加的日志文件.ldf,SIZE=3MB,MAXSIZE=50MB,FILEGROWTH=10%),【例3】为数据库日志文件,ALTER DATABASE 数据库名称ADD FILEGROUP 文件组名称,【例4】增加文件组,ALTER DATABASE 数据库名称MODIFY FILE(NAME=原数据文件名称,NEWNAME=新数据文件名称,FILENAME=D:DBtest例十数据文件.ndf),【例5】修改数据文件的名称,ALTER DATABASE 数据库名称MODIFY FILEGROUP 文件组原名称 NAME=文件组新名称,【例6】修改日志文件名称,ALTER DATABASE 数据库名称 REMOVE FILE 数据文件名称,【例7】删除数据文件,ALTER DATABASE 数据库名称 REMOVE FILEGROUP 文件组名称,【例8】删除文件组,ALTER DATABASE 数据库名称ADD FILE(NAME=数据文件名称,FILENAME=D:DBtest数据文件.ndf)TO FILEGROUP 文件组GOALTER DATABASE 数据库名称MODIFY FILEGROUP 文件组 DEFAULTGO,【例9】在数据库中增加一个数据文件后 将该文件组设为默认文件组,ALTER DATABASE 数据库名称MODIFY FILEGROUP PRIMARY DEFAULTGOALTER DATABASE 数据库名称REMOVE FILE 数据文件名称GOALTER DATABASE 数据库名称REMOVE FILEGROUP 文件组名称GO,【例10】删除用户自定义的默认文件组,【例11】修改数据文件rj的初始长度为2MB ALTER DATABASE rjkf MODIFY FILE(NAME=rj，SIZE=2),缩小数据库,1使用企业管理器自动缩小数据库。2使用Transact-SQL语言缩小数据库。,DBCC SHRINKDATABASE(数据库名,目标百分比大小,NOTRUNCATE|TRUNCATEONLY),缩小数据库,数据库名：要收缩的数据库名称。目标百分比大小：当数据库收缩后，数据文件中剩余可用空间的百分比。NOTRUNCATE：数据库保留所释放的文件空间。不允许操作系统回收。TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统。使用 TRUNCATEONLY 时，将忽略 target_percent的限制。,【例】数据库“山东新华电脑学院”初始大小50M，每次递增50M，现容量为60M。DBCC SHRINKDATABASE(山东新华电脑学院,80,NOTRUNCATE)DBCC SHRINKDATABASE(山东新华电脑学院,80)DBCC SHRINKDATABASE(山东新华电脑学院,80,TRUNCATEONLY),DBCC SHRINKFILE(文件名,将文件缩小到指定的长度|,EMPTYFILE|NOTRUNCATE|TRUNCATEONLY)EMPTYFILE：将指定文件中所有数据迁移到同一文件组 的其它文件中，然后删除该空文件。,缩小文件,【例】数据库“山东新华”初始大小10M，每次递增5M，现容量为12M。DBCC SHRINKFILE(山东新华)DBCC SHRINKFILE(山东新华,13)DBCC SHRINKFILE(山东新华,8),页,页是SQL SERVER 最基本的存储单元。每页8K（8192字节），每兆字节可容纳128页。只有数据文件是由页组成，日志文件由若干记录组成。,每页最开头96个字节是系统信息。下面存储若干个数据行，每个数据行最多存储8060个字节,范围,范围是SQL SERVER分配存储空间的最基本单位。每个范围包括8个连续的页，每兆字节可容纳16个范围。,统一范围：将某一范围内的8个数据页全部分配给同一个表混合范围：将某一范围内的8个数据页分配给不同的表,范围的分类,修改数据库选项,1、限制访问包含两个选项：“db_owner,dbcreator或sysadmin的成员”选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；“单用户”选项表示数据库在同一时间只能供一个用户使用。2、ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。3、递归触发器：指定是否允许触发器递归调用。,4、残缺页检测：允许自动检测有损坏的页。页是数据库内容的基本存储单位，每个页的大小为8KB。由于SQL Server对页的读写单位是大小为512字节的扇区，当每个页的第一个扇区被成功写入后，SQL Server就认为此页已经被成功写入。因此如果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页。,5、自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。6、自动收缩：允许定期对数据库进行检查，当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。,