数据库的基本操作1.ppt

SQL Server 2005 实用教程,第3章,数据库的基本操作,第3章 数据库的基本操作,3.1 SQL Server 数据库的一些基本术语和概念 3.2 创建数据库 3.3 管理数据库 3.4 案例中的应用举例,3.1 SQL Server 数据库的一些基本术语和概念,3.1.1 SQL Server的数据库 3.1.2 SQL Server的事务日志 3.1.3 SQL Server 数据库文件及文件组 3.1.4 SQL Server 的系统数据库,3.1.1 SQL Server的数据库,1. SQL Server的数据库 是有组织的数据的集合，这种数据集合具有逻辑结构并得到数据库系统的管理和维护。 SQL Server数据库是数据库对象的容器，它以操作系统文件的形式存储在磁盘上。一般情况下它用一组文件映射DB。DB中所有的数据和对象都存储在OS文件中。,图3.1 数据库、数据库对象及文件 关系图,3.1.1 SQL Server的数据库,3.1.2 SQL Server的事务日志,1.事务是一组T-SQL语句的集合，这组语句作为单个的工作与恢复的单元。事务作为一个整体来执行，对于其数据的修改，要么全都执行，要么全都不执行。 2.事务日志是数据库中已发生的所有修改和执行每次修改的事务的一连串记录。 3.事务日志记录更改数据的流程 。,3.1.2 SQL Server的事务日志,事务日志工作流程,数据库修改操作,写入日志,页调入内存,写入磁盘,3.1.3 SQL Server 数据库文件及文件组,1.SQL Server的数据库文件的三种类型 1）主数据文件（Primary file） 用来存储数据库的数据和数据库的启动信息。每个数据库必须并且只有一个主数据文件，其扩展名为.MDF。实际的文件都有两种名称：操作系统文件名和逻辑文件名（T-SQL语句中使用）。 2）辅助数据文件（Secondary file） 用来存储数据库的数据，使用辅助数据库文件可以扩展存储空间。如果数据库用一个主数据文件和多个辅助数据文件来存放数据，并将它们放在不同的物理磁盘上，数据库的总容量就是这几个磁盘容量的和。辅助数据文件的扩展名为：.NDF 3）事务日志文件（Transaction log） 用来存放数据库的事务日志。凡是对数据库进行的增、删、改等操作，都会记录在事务日志文件中。当数据库被破坏时可以利用事务日志文件恢复数据库的数据。每个数据库至少要有一个事务日志文件，事务日志文件的扩展名为：LDF,3.1.3 SQL Server 数据库文件及文件组,2SQL Server的数据库文件组 文件组是SQL另一种形式的容器，文件位置可以很灵活，你可以将数据文件存储在不同的地方，然后用文件组把他们作为一个单元来管理。例如，你可以将主数据文件放在一个地方然后将次要数据文件(aa1.ndf,aa2.ndf,aa3.ndf),如果需要，可将他们放在三个不同的磁盘（如D盘、E盘、F盘）上。然后创建一个文件组，将所有的文件指定到此文件组。如图3.2所示。,图 3.2 数据库与操作系统文件之间的映射,3.1.4 SQL Server 的系统数据库,SQL server 2005的系统构成 数据库分为两类有系统数据库和用户数据库。当安装完成后系统会自动创建5个系统数据库,其中4个在SQL Server Management Studio环境中可见（ master、model、tempb、msdb ）和1个逻辑上不单独存在，隐藏的系统数据库Resource。,3.1.4 SQL Server 的系统数据库,SQL server 20005系统构成 master数据库：该数据库记录SQL server 2005的所有的服务器系统信息、注册账户和密码以及所有的系统设置信息等大量对对系统至关重要的信息，是系统的关键性所在，所以他一旦受到破坏，可能会导致这个系统的瘫痪。 model数据库：为用户提供了模板和原型，包含了每一用户数据库所需要的系统表。它的定制结构可以被更改，因为每当用户创建新的数据库时，都是复制model数据库的模板，所以所作的所有更改都将反映到用户数据库当中。 tempdb数据库： 此数据库保存所有的临时性表和临时存储过程，并满足任何其他的临时存储要求。tempdb数据库是全局资源，在每次启动时都重新创建，因此该数据库在系统启动时总是空白的。 msdb数据库：此数据库供SQL server代理程序调度报警和作业调度等活动。 Resource数据库：是一个只读的数据库，它包含了 SQL Server 2005 中的所有系统对象。系统对象在物理上保存在Resource 数据库文件中，在逻辑上显示在每个数据库的sys架构中。,3.2创建数据库,数据库是数据库系统最基本的对象，是存储过程、触发器、视图和规则等数据库对象的容器。因此，创建数据库是创建其它数据库对象的基础。若要创建数据库，需要确定数据库的名称、所有者、大小以及存储该数据库的文件和文件组。在SQL Server 2005中创建数据库主要有两种方法：使用SQL Server Management Studio或T-SQL语言创建数据库。 1、使用SQL Server Management Studio创建数据库,3.2创建数据库,2、使用T-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,各参数的含义（如下）： database_name：新数据库的名称。数据库名称在服务器中必须唯一，最长为128个字符，并且要符合标识符的命名规则。每个服务器管理的数据库最多为32767个。 ON：指定存放数据库的数据文件信息。列表用于定义主文件组的数据文件，列表用于定义用户文件组及其中的文件。 PRIMARY：用于指定主文件组中的文件。主文件组的第一个由指定的文件是主文件。如果不指定PRIMARY关键字，则在命令中列出的第一个文件将被默认为主文件。 LOG ON：指明事务日志文件的明确定义。如果没有本选项，则系统会自动产生一个文件名前缀与数据库名相同，容量为所有数据库文件大小1/4的事务日志文件。 FOR LOAD：表示计划将备份直接装入新建的数据库，主要是为了和过去的SQL Server版本兼容。 FOR ATTACH：表示在一组已经存在的操作系统文件中建立一个新的数据库。 NAME：指定数据库的逻辑名称。 FILENAME：指定数据库所在文件的操作系统文件名称和路径，该操作系统文件名和NAME的逻辑名称一一对应。,SIZE：指定数据库的初始容量大小。如果没有指定主文件的大小，则SQL Server默认其与模板数据库中的主文件大小一致，其它数据库文件和事务日志文件则默认为1MB。指定大小的数字size可以使用KB、MB、GB和TB后缀，默认的后缀为MB。Size中不能使用小数，其最小值为512KB，默认值为1MB。主文件的size不能小于模板数据库中的主文件。 MAXSIZE：指定操作系统文件可以增长到的最大尺寸。如果没有指定，则文件可以不断增长直到充满磁盘。 FILEGROWTH：指定文件每次增加容量的大小，当指定数据为0时，表示文件不增长。增加量可以确定为以KB、MB作后缀的字节数或以%作后缀的被增加容量文件的百分比来表示。默认后缀为MB。如果没有指定FILEGROWTH，则默认值为10%，每次扩容的最小值为64KB。,3.2创建数据库,3、案例 创建数据库名为“BVTC_DB”的数据库，其包含一个主数据文件和一个事务日志文件。主数据文件的逻辑名为“BVTC_DB_DATA”，操作系统文件名为“BVTC_DB_DATA.MDF”，初始容量大小为5MB，最大容量为20MB，文件的增长量为20%；事务日志文件的逻辑文件名为“BVTC_DB_LOG”，操作系统文件名为“BVTC_DB_LOG.LDF”，初始容量大小为5MB，文件增长量为2MB，最大不受限制。数据文件与事务日志文件都保存在D盘根目录。,3.2创建数据库,CREATE DATABASE BVTC_DB ON PRIMARY (NAME = BVTC_DB_DATA, FILENAME = D:BVTC_DB_DATA.MDF , SIZE = 5MB, MAXSIZE = 20MB, FILEGROWTH = 20%) LOG ON (NAME =BVTC_DB_LOG, FILENAME = D:BVTC_DB_LOG.LDF , SIZE = 5MB, FILEGROWTH = 2MB) COLLATE Chinese_PRC_CI_AS GO,创建一个包含2个文件组的数据库。该数据库名为 jwgl3，主文件组包含文件 jwgl30_data和 jwgl31_data。文件组jwgl3_group包含文件jwgl32_data和 jwgl33_data。两个文件组数据文件的FILEGROWTH 增量为 15%，数据文件的初始大小为10 MB。事务日志文件的文件名为jwgl3_log，FILEGROWTH 增量为 15%，日志文件的初始大小为5 MB。,CREATE DATABASE jwgl3 ON PRIMARY ( NAME = jwgl30_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl30.mdf, SIZE = 10MB, FILEGROWTH = 15% ), ( NAME = jwgl31_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl31.ndf, SIZE = 10MB, FILEGROWTH = 15% ),FILEGROUP jwgl3_Group ( NAME = jwgl32_data, FILENAME = d:Program FilesMicrosoft SQLServerMSSQL DATAjwgl32.ndf, SIZE = 10MB, FILEGROWTH = 15% ), ( NAME = jwgl33_data, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl33.ndf, SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 15% ) LOG ON ( NAME = jwgl3_log, FILENAME = c:Program FilesMicrosoft SQLServerMSSQL DATAjwgl3.ldf , SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 15% ) GO,几点说明: 1）创建用户数据库后，要备份master数据库。 2）所有数据库都至少包含一个主文件组。所有系统表都分配在主文件组中。数据库还可以包含用户定义的文件组。 3）每个数据库都有一个所有者，可在数据库中执行某些特殊的活动。数据库所有者是创建数据库的用户，也可以使用sp_changedbowner更改数据库所有者。 4）创建数据库的权限默认地授予sysadmin和dbcreator固定服务器角色的成员。,3.3管理数据库,3.3.1使用SQL Server Management Studio管理数据库 3.3.2使用T-SQL管理数据库,3.3.1使用SQL Server Management Studio管理数据库,1、打开数据库 在SQL Server Management Studio中打开数据库的步骤是：在“对象资源管理器”窗口中，展开“数据库”节点，单击要打开的数据库“BVTC_DB”，此时右边“摘要”窗口中列出当前打开数据库的数据库对象。,3.3.1使用SQL Server Management Studio管理数据库,2、查看数据库信息 （1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，选择要查看信息的数据库“BVTC_DB”，单击右键，在弹出的快捷菜单中，单击“属性”命令，打开“数据库属性”对话框。 （2）在“数据库属性”对话框中，包含常规、文件、文件组、选项、权限、扩展属性、镜像和事务日志传送8个选择页。单击其中任意的选择页，可以查看到与之相关的数据库信息。,3.3.1使用SQL Server Management Studio管理数据库,3、修改数据库容量 （1）增加数据库容量：在“对象资源管理器”窗口中，展开“数据库”节点，选择要增加容量数据库“BVTC_DB”，单击右键，在弹出的快捷菜单中单击“属性”命令，打开“数据库属性”对话框。从“选择页”中选择“文件”页，在这里可以修改数据库文件的初始大小和增长方式，其修改方法与创建数据库时相同。 （2）收缩数据库容量：在“对象资源管理器”窗口中，展开“数据库”节点，选择要收缩容量的数据库“BVTC_DB”，单击右键，在弹出的快捷菜单中执行“任务”“收缩”“数据库”命令，打开“收缩数据库”对话框 。根据需要，可以选择“在释放未使用的空间前重新组织文件”复选框，如果选择该项，必须为“收缩后文件中的最大可用空间”输入值，允许输入的值介于0和99之间。设置完成后，单击“确定”命令按钮，完成数据库收缩。 自动收缩：“选项”页-选“自动收缩” 的TRUE,3.3.1使用SQL Server Management Studio管理数据库,4、设定修改数据库选项 (1)在“对象资源管理器”窗口中，展开“数据库”节点，选择要设置选项的数据库“BVTC_DB”，单击右键，在弹出的快捷菜单中，单击“属性”命令，打开“数据库属性”对话框。 （2）在“数据库属性”对话框中，选择“选项”页，出现数据库的各个选项，在此，可以根据管理需要对数据库选项进行重新设定。 P54 访问限制：指定只有属于db_owner、dbcreator或sysadmin的成员才能访问数据库。 单用户：只能有一个用户使用数据库。这个选项通常用于在执行一些诸如“DBCC SHRINKDB”的命令时阻止用户访问这个数据库。 只读：使这个数据库处于只读状态。如数据库设为只读状态，用户就不能修改数据库中的任何记录。 恢复模式：指定数据库的恢复模型 ANSI NULL默认设置：允许在数据库表的列中输入空（NULL）值。,限制访问包含两个选项：“db_owner, dbcreator或sysadmin的成员”选项表示只有数据库的所有者、数据库创建者和系统管理员才有权使用数据库；“单用户” 选项表示数据库在同一时间只能供一个用户使用。 递归触发器：指定是否允许触发器递归调用。 自动更新统计信息：允许使用SELECT INTO或BCP、WRITETEXT、UPDATETEXT命令向表中大量插入数据。在开发数据库时常常将此选项设置为真。 残缺页检测：允许自动检测有损坏的页。页是数据库内容的基本存储单位，每个页的大小为8KB。由于SQL Server对页的读写单位是大小为512字节的扇区，当每个页的第一个扇区被成功写入后，SQL Server就认为此页已经被成功写入。因此，如果写完第一个扇区后发生突发事件，导致写入中断，就会产生有损坏的页，需要通知备份来恢复数据库。,自动关闭：当数据库中无用户时，自动关闭该数据库，并将所占用的资源交还给操作系统。对那些不间断使用的数据库不要使用此选项。 自动收缩：允许定期对数据库进行检查，当数据库文件或日志文件中未用空间超过其大小的25%时，系统将会自动缩减文件，使其未用空间等于25%。当文件大小没有超过其建立时的初始大小时，不会缩减文件。缩减后的文件也必须大于或等于其初始大小。 自动创建统计信息：在优化查询时，根据需要自动创建统计信息。 使用被引用的标识符：标识符必须用双引号括起来，且可以不遵循Transact-SQL命名标准。,3.3.1使用SQL Server Management Studio管理数据库,5、更改数据库名称 （1）启动SQL Server Management Studio，在“对象资源管理器”窗口中，展开“数据库”节点，选择要更名的数据库“BVTC_DB”，单击右键，在弹出的快捷菜单中，单击“属性”命令，打开“数据库属性”对话框，选择“选项”页，将数据库选项中的“限制访问”设为“Single”用户模式，设置成功后，在对象资源管理器中该数据库名称旁边有单个用户标志。 （2）选择“BVTC_DB”，单击右键，在弹出的快捷菜单中选择“重命名”，数据库名称变为可编辑状态，输入新的数据库名称即可。 （3）更名后，将数据库选项中的“限制访问”设为“Multipe”用户模式。,3.3.1使用SQL Server Management Studio管理数据库,6、分离和附加数据库 （1）分离数据库：在“对象资源管理器”窗口中，展开“数据库”节点，选择要分离的数据库，如“BVTC_DB”，单击右键，在弹出的快捷菜单中执行“任务”“分离”命令，打开“分离数据库”对话框。在“分离数据库”对话框中右侧是“要分离的数据库”窗格，在“状态”为就绪时，单击“确定”按钮，将数据库与SQL Server服务器分离。 （2）附加数据库：在“对象资源管理器”窗口中，选择“数据库”节点，单击右键，在弹出的快捷菜单中，单击“附加”命令，打开“附加数据库”对话框。单击“添加”命令按钮，打开“定位数据库文件”对话框，在该对话框中选择数据文件所在的路径，选择扩展名为. MDF数据文件，如BVTC_DB_DATA.MDF，单击“确定”命令按钮返回“附加数据库”对话框。最后，单击“确定”按钮，完成数据库附加。,3.3.1使用SQL Server Management Studio管理数据库,6、删除数据库 在“对象资源管理器”窗口中，展开“数据库”节点，选择要删除的数据库，单击右键，在弹出的快捷菜单中单击“删除”命令，打开“删除对象”对话框，单击“确定”命令按钮即可完成数据库删除操作。,3.3.2使用T-SQL管理数据库,1、打开数据库 语法格式为： USE database_name 2、查看数据库信息 语法格式为：查看某个数据库或所有数据库的信息： EXECUTE sp_helpdb database_name,查看有关数据库中所占用空间的报表； EXEC sp_spaceused 显示有关数据库中文件组的报表： EXEC sp_helpfilegroup 显示数据库中文件的报表： EXEC sp_helpfile,3.3.2使用T-SQL管理数据库,3、增加或减少数据库容量 修改数据库容量，可以修改数据库文件的大小，也可以增加或删除数据库文件，其语法格式为： ALTER DATABASE database_name ADD FILE (NAME= logical_file_name, *添加数据文件 FILENAME=os_file_name ,SIZE= size ,MAXSIZE= max_size |UNLIMITED ,FILEGROWTH= grow_increment) | ADD LOG FILE(NAME= logical_file_name, *添加日志文件 FILENAME=os_file_name ,SIZE= size ,MAXSIZE= max_size |UNLIMITED ,FILEGROWTH= grow_increment) | MODIFY FILE (NAME=file_name, * 修改数据库文件容量 SIZE= newsize)| REMOVE FILE logical_file_name *删除数据库文件,3.3.2使用T-SQL管理数据库,案例：为BVTC_DB数据库增加容量，原来数据库文件BVTC_DB_DATA的初始分配空间为5MB，指派给BVTC_DB数据库使用，现在将BVTC_DB_DATA的分配空间增加至20M。 代码如下： USE BVTC_DB GO ALTER DATABASE BVTC_DB MODIFY FILE (NAME=BVTC_DB_DATA, SIZE=20MB),3.3.2使用T-SQL管理数据库,4、收缩数据库容量 语法格式如下： DBCC SHRINKDATABASE 使用DBCC SHRINKDATABASE命令收缩指定数据库中的数据文件。 DBCC SHRINKDATABASE (database_name,target_percent,NOTRUNCATE |TRUNCATEONLY) database_name：要收缩的数据库名称。 Target_percent：当数据库收缩后，数据库文件中剩余可用空间的百分比。 NOTRUNCATE：被释放的文件空间依然保持在数据库文件中。如果未指定，将所释放的文件空间被操作系统回收。 TRUNCATEONLY：将数据文件中未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小。使用 TRUNCATEONLY 时，将忽略 target_percent的限制。,案例：收缩BVTC_DB数据库的容量至最小。 其代码如下： DBCC SHRINKDATABASE (BVTC_DB) GO 将数据库设为自动收缩 ALTER DATABASE database_name SET AUTO_SHRINK on/off,3.3.2使用T-SQL管理数据库,5、设定修改数据库选项 （1）查看数据库选项：语法格式如下： EXEC sp_dboption database_name （2）修改数据库选项：语法格式如下： EXEC sp_dboption database_name,option_name,TRUE|FALSE option_name：要设置的选项的名称。该选项常用的4个参数及含