中职 数据库应用基础（SQL Server 2016）项目2教学课件.pptx

《中职 数据库应用基础（SQL Server 2016）项目2教学课件.pptx》由会员分享，可在线阅读，更多相关《中职 数据库应用基础（SQL Server 2016）项目2教学课件.pptx（32页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、中职 数据库应用基础（SQL Server 2016）项目2教学课件工信版p理解SQL Serve数据库文件组成p掌握创建数据库的方法p掌握修改数据库的方法p掌握分离和附加数据库的方法p掌握备份和还原数据库的方法 项目目标项目目标 2.1.1 SQL Server数据库概述SQL Server数据库由一些表和其他对象组成，这些表用于存储一组特定的结构化数据。每个表中都包含一些行和列，在数据库术语中每一行称为一条记录，每一列称为一个字段。表中的每一列都用于存储某种类型的信息，例如日期、名称和金额等。为了保证数据的有效性，可以在表中添加各种类型的控制，例如约束、触发器、默认值和自定义用户数据类型等

2、。通过在表中添加声明性引用完整性约束，可以确保不同表中的相关数据保持一致。在表中可以创建索引，利用索引能够快速找到所需要的行。在一台计算机上可以安装一个或多个SQL Server实例，每个SQL Server实例都可以包含一个或多个数据库。一个SQL Server实例中最多可以包含32767个数据库。在数据库中有一个或多个对象所有权组，称为架构。在每个架构中，都存在数据库对象，例如表、视图和存储过程等。某些对象（例如证书和非对称密钥）包含在数据库中，但不包含在架构中。如果某人获得对SQL Server实例的访问权限，则将其标识为一个登录名。当某些人获取对数据库的访问权限时，他们将被标识为数据库

3、用户。数据库用户可以基于登录名来创建。如果启用包含的数据库，也可以创建不基于登录名的数据库用户。对数据库具有访问权限的用户可以授予他们访问数据库中对象的权限。尽管可以将权限授予各个用户，但建议创建数据库角色，并将数据库用户添加到角色中，然后对角色授予访问权限。任务任务2.1 2.1 认识认识SQL ServerSQL Server数据库数据库2.1.2 数据库文件与文件组1.SQL Server 数据库文件的类型p主要数据文件：包含数据库的启动信息，并指向数据库中的其他文件，文件扩展名是.mdf。p次要数据文件：这种文件是可选的，由用户定义并存储用户数据，文件扩展名是.ndf。p事务日志文件：

4、保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。文件扩展名是.ldf。2.SQL Server数据库文件的名称p逻辑文件名：这是在所有SQL语句中引用物理文件时所使用的名称，必须符合标识符命名规则，在数据库中必须唯一。p操作系统文件名：这是包括目录路径的物理文件的名称，必须符合操作系统中的文件命名规则。3.数据文件页SQL Server数据文件中的页面按顺序编号，文件首页以0开头。数据库中的每个文件都有一个唯一的文件ID号。如果要唯一地标识数据库中的页，则需要同时使用文件ID和页码。4.文件大小SQL Server文件可以从其最初指定的大小开始自动增长。在定义文件时可以指定一个

5、特定的增量。每次填充文件时，其大小均按此增量来增长。如果文件组中有多个文件，则它们在所有文件被填满之前不会自动增长。填满后这些文件会循环增长。每个文件还可以指定一个最大大小。如果没有指定最大大小，文件可以一直增长到用完磁盘上的所有可用空间。5.文件组每个SQL Server数据库都有一个主要文件组。这个文件组包含主要数据文件和未放入其他文件组的所有次要文件。所有系统表都被分配到主要文件组中。也可以创建用户定义文件组，这是首次创建数据库或以后修改数据库时明确创建的文件组，它用于将数据文件集合起来，以便于管理、分配和放置数据。任务任务2.1 2.1 认识认识SQL ServerSQL Server

6、数据库数据库2.1.3 SQL Server系统数据库1.master数据库master数据库用于记录SQL Server系统的所有系统级信息，包括实例范围的元数据（例如登录账户）、端点、链接服务器和系统配置设置。此外，master数据库还记录了所有其他数据库的存在、数据库文件的位置以及SQL Server的初始化信息。2.model数据库model数据库用作在SQL Server服务器实例上创建的所有数据库的模板。因为每次启动SQL Server时都会创建tempdb，所以model数据库必须始终存在于SQL Server系统中。3.msdb数据库msdb数据库由SQL Server代理用于

7、计划警报和作业，SSMS、Service Broker以及数据库邮件等其他功能也使用该数据库。4.tempdb数据库tempdb系统数据库是一个全局资源，可供连接到SQL Server实例的所有用户使用，并可用于保存临时用户对象等。5.Resource数据库Resource数据库为只读数据库，它包含了SQL Server中的所有系统对象。SQL Server系统对象（如sys.objects）在物理上保留在Resource数据库中，但在逻辑上却显示在每个数据库的sys架构中。任务任务2.1 2.1 认识认识SQL ServerSQL Server数据库数据库2.1.4 数据库状态pONLINE

8、：在线状态或联机状态，可以对数据库进行访问。即使可能尚未完成恢复的撤消阶段，主文件组仍处于在线状态。pOFFLINE：离线状态或脱机状态，数据库无法使用。pRESTORING：恢复状态，正在还原主文件组的一个或多个文件，或正在脱机还原一个或多个辅助文件。数据库不可用。pRECOVERING：还原状态，正在恢复数据库。恢复进程是一个暂时性状态，恢复成功后数据库将自动处于在线状态。若恢复失败，则数据库将处于可疑状态。数据库不可用。pRECOVERY PENDING：恢复未完成状态，SQL Server在恢复过程中遇到了与资源相关的错误。pSUSPECT：可疑状态，至少主文件组可疑或可能已损坏。在S

9、QL Server启动过程中无法恢复数据库。pEMERGENCY：紧急状态，用户更改了数据库，并将其状态设置为EMERGENCY。任务任务2.1 2.1 认识认识SQL ServerSQL Server数据库数据库2.1.5 数据库文件状态pONLINE：在线状态，文件可用于所有操作。pOFFLINE：离线状态，文件不可访问，并且可能不显示在磁盘中。pRECOVERY PENDING：恢复未完成状态，文件恢复被推迟。pSUSPECT：可疑状态，联机还原过程中恢复文件失败。pDEFUNCT：失效状态，当文件不处于在线状态时被删除。任务任务2.1 2.1 认识认识SQL ServerSQL Ser

10、ver数据库数据库任务目标 p掌握使用SSMS创建数据库的方法p掌握使用SQL语句创建数据库的方法任务任务2.2 2.2 创建数据库创建数据库2.2.1 使用SSMS创建数据库（1）在对象资源管理器中连接到数据库引擎实例，然后展开该实例。（2）右键单击“数据库”，然后单击“新建数据库”。（3）在“新建数据库”对话框中，输入数据库名称。（4）若要通过接受所有默认值来创建数据库，请单击“确定”按钮；否则，请执行继续后续的可选步骤。（5）若要更改所有者名称，可以单击 按钮选择其他所有者。（6）若要更改主数据文件和事务日志文件的默认值，可以在“数据库文件”网格中单击“逻辑名称”、“初始大小”、“自动增

11、长/最大大小”或“路径”单元格，然后输入或设置新值。（7）若要更改数据库的排序规则，可以选择“选项”页，然后从列表中选择一个排序规则。（8）若要更改恢复模式，可以选择“选项”页，然后从列表中选择一个恢复模式。（9）若要更改数据库选项，可以选择“选项”页，然后修改数据库选项。（10）若要添加新文件组，可以单击“文件组”页，单击“添加文件组”按钮，然后输入文件组的值。（11）所有选项设置完成后，单击“确定”按钮，以创建数据库。任务任务2.2 2.2 创建数据库创建数据库2.2.2 使用SQL语句创建数据库1.CREATE DATABASE语句语法格式CREATE DATABASE 数据库名称ON

12、PRIMARY ,.,.LOG ON,.COLLATE 排序名称;任务任务2.2 2.2 创建数据库创建数据库:=(NAME=逻辑文件名,FILENAME=操作系统文件名 ,SIZE=文件初始容量 KB|MB|GB|TB ,MAXSIZE=文件最大容量 KB|MB|GB|TB|UNLIMITED ,FILEGROWTH=文件增量 KB|MB|GB|TB|%):=FILEGROUP 文件组名 DEFAULT ,.2.2.2 使用SQL语句创建数据库2.CREATE DATABASE语句应用实例使用CREATE DATABASE语句创建数据库分为以下三种情况。p数据库包含一个数据文件和一个事务日志

13、文件。p数据库包含两个数据文件和一个事务日志文件。p数据库包含六个数据文件，分成三个文件组。任务任务2.2 2.2 创建数据库创建数据库任务目标 p掌握使用SSMS修改数据库的方法p掌握扩展数据库的各种方法p掌握收缩数据库的的各种方法p掌握移动数据库文件的方法p掌握设置数据库选项的方法p掌握重命名和删除数据库的方法任务任务2.3 2.3 修改数据库修改数据库2.3.1 使用SSMS修改数据库（1）在对象资源管理器中连接到SQL Server数据库引擎实例，然后展开该实例。（2）展开“数据库”，右键单击要修改的数据库，选择“属性”命令。（3）在“数据库属性”对话框中，选择一个页以修改相关属性。（

14、4）完成数据库属性修改后，单击“确定”按钮，使修改立即生效。任务任务2.3 2.3 修改数据库修改数据库2.3.2 使用SQL语句修改数据库ALTER DATABASE 数据库名|CURRENT ADD FILE,.TO FILEGROUP 文件组名|ADD LOG FILE,.|REMOVE FILE 逻辑文件名|MODIFY FILE|MODIFY NAME=新数据库名|ADD FILEGROUP 文件组名,.|REMOVE FILEGROUP 文件组名|MODIFY FILEGROUP 文件组名|DEFAULT|NAME=新文件组名|SET,.WITH|COLLATE 排序名;任务任务2

15、.3 2.3 修改数据库修改数据库2.3.3 扩展数据库默认情况下，SQL Server根据创建数据库时指定的增长参数自动扩展数据库。不过，也可以通过手动方式来扩展数据库，为此可以为现有数据库文件分配更多的磁盘空间，或者向数据库中添加新文件。如果现有的文件已满，则可能需要扩展数据或事务日志的空间。如果数据库已经用完分配给它的空间且不能自动增长，则会出现错误。在Transact-SQL中，可以使用ALTER DATABASE语句来增加数据库的大小，这个操作过程可以通过以下三种方式来实现：p使用MODIFY FILE子句修改现有数据文件的大小；p使用ADD FILE子句向数据库中添加新的数据文件；

16、p使用ADD FILEGROUP子句向数据库中添加新的文件组。任务任务2.3 2.3 修改数据库修改数据库2.3.4 收缩数据库1.自动收缩数据库如果某个数据库的AUTO_SHRINK选项设置为ON，则数据库引擎将自动收缩该数据库的可用空间：ALTER DATABASE 数据库名SET AUTO_SHRINK ON;2.使用DBCC SHRINKDATABASE收缩数据库DBCC SHRINKDATABASE(数据库名|数据库ID|0,目标百分比,NOTRUNCATE|TRUNCATEONLY);3.使用DBCC SHRINKFILE收缩数据库文件DBCC SHRINKFILE(文件名|文件I

17、D,EMPTYFILE|,目标大小,NOTRUNCATE|TRUNCATEONLY)4.从数据库中删除文件和文件组在ALTER DATABASE语句中使用REMOVE FILE子句可以从指定的数据库中删除文件，使用REMOVE FILEGROUP子句可以从数据库中删除文件组。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项1.自动选项pAUTO_CLOSE：当设置为ON时，数据库将在最后一个用户退出后完全关闭，它占用的资源也将释放。pAUTO_CREATE_STATISTICS：当设置为ON时，将自动创建查询谓词所用的列的统计信息。pAUTO_SHRINK：当设置为ON时

18、，数据库文件可作为定期收缩的对象。pAUTO_UPDATE_STATISTICS：当设置为ON时，优化查询所需的任何缺少的统计信息将在查询优化过程中自动生成。2.游标选项pCURSOR_CLOSE_ON_COMMIT：设置为ON时，所有打开的游标都将在提交或回滚事务时关闭。pCURSOR_DEFAULT：如果指定为LOCAL，而创建游标时未将其定义为GLOBAL，则游标的作用域将局限于创建游标时所在的批处理、存储过程或触发器。游标名仅在该作用域内有效。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项3.数据库可用性选项pOFFLINE|ONLINE|EMERGENCY：如

19、果指定为OFFLINE，则数据库将完全关闭和退出，并标记为脱机；如果指定为ONLINE（默认值），则数据库处于打开状态并且可供使用；当指定为EMERGENCY时，数据库将标记为READ_ONLY，日志记录将被禁用，并且只有sysadmin固定服务器角色的成员才能进行访问。pREAD_ONLY|READ_WRITE：如果设置为READ_ONLY，则允许用户从数据库中读取数据，但此时不能修改数据库；如果设置为READ_WRITE（默认值），则允许用户对数据库执行读写操作。pSINGLE_USER|RESTRICTED_USER|MULTI_USER：若指定为SINGLE_USER，则一次只允许一个

20、用户连接到数据库，所有其他用户连接均中断；若指定为RESTRICTED_USER，则只允许db_owner固定数据库角色的成员以及dbcreator和sysadmin固定服务器角色的成员连接到数据库，不过对连接数没有限制；若指定为MULTI_USER（默认值），则允许所有具有相应权限的用户连接到数据库。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项4.日期相关性优化选项日期相关性优化选项DATE_CORRELATION_OPTIMIZATION可用于控制date_correlation _optimization选项。如果指定为ON，则SQL Server将维护数据库中

21、所有由FOREIGN KEY约束链接的包含datetime列的两个表中的相关统计信息；如果指定为OFF（默认值），则不会维护相关统计信息。5.外部访问选项外部访问选项用于控制是否允许外部资源（例如另一个数据库中的对象）访问数据库。pDB_CHAINING：如果指定为ON，则数据库可以是跨数据库所有权链的源或目标；如果设置为OFF（默认值），则数据库不能参与跨数据库的所有权链接。pTRUSTWORTHY：如果设置为ON，则使用了模拟上下文的数据库模块（例如用户定义函数或存储过程）可以访问数据库以外的资源；如果指定为OFF（默认值），则在模拟上下文中无法访问数据库以外的资源。只要附加数据库，TRU

22、STWORTHY就会设置为OFF。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项4.日期相关性优化选项日期相关性优化选项DATE_CORRELATION_OPTIMIZATION可用于控制date_correlation _optimization选项。如果指定为ON，则SQL Server将维护数据库中所有由FOREIGN KEY约束链接的包含datetime列的两个表中的相关统计信息；如果指定为OFF（默认值），则不会维护相关统计信息。5.外部访问选项外部访问选项用于控制是否允许外部资源（例如另一个数据库中的对象）访问数据库。pDB_CHAINING：如果指定为ON

23、，则数据库可以是跨数据库所有权链的源或目标；如果设置为OFF（默认值），则数据库不能参与跨数据库的所有权链接。pTRUSTWORTHY：如果设置为ON，则使用了模拟上下文的数据库模块（例如用户定义函数或存储过程）可以访问数据库以外的资源；如果指定为OFF（默认值），则在模拟上下文中无法访问数据库以外的资源。只要附加数据库，TRUSTWORTHY就会设置为OFF。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项6.参数化选项当参数化选项PARAMETERIZATION设置为SIMPLE（默认值）时，将根据数据库的默认行为对查询进行参数化；如果指定为FORCED，则SQL S

24、erver将对数据库中的所有查询进行参数化。7.恢复选项pRECOVERY：当指定为FULL（默认值）时，将使用事务日志备份在发生媒体故障后进行完全恢复，如果数据文件损坏，则媒体恢复可以还原所有已提交的事务；当指定为BULK_LOGGED时，将综合某些大规模或大容量操作的最佳性能和日志空间的最少占用量，在发生媒体故障后进行恢复；当指定为SIMPLE时，将会提供占用最小日志空间的简单备份策略。pPAGE_VERIFY：当指定为CHECKSUM（默认值）时，数据库引擎将在页写入磁盘时，计算整个页的内容的校验和并存储页头中的值，从磁盘中读取页时，将重新计算校验和，并与存储在页头中的校验和值进行比较；

25、当指定为TORN_PAGE_DETECTION时，在将8KB的数据库页写入磁盘时，该页的每个512字节的扇区都有一个特定的位保存并存储在数据库的页头中，当从磁盘中读取页时，页头中存储的残缺位将与实际的页扇区信息进行比较；当指定为NONE时，数据库页写入将不生成CHECKSUM或TORN_PAGE_DETECTION值，即使CHECKSUM或TORN_PAGE_DETECTION值在页头中出现，SQL Server也不会在读取期间验证校验和或页撕裂。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项8.Service Broker选项ENABLE_BROKER|DISABLE

26、_BROKER|NEW_BROKER|ERROR_BROKER_CONVERSATIONS选项用于控制Service Broker。当指定为ENABLE_ BROKER（默认值）时，针对指定的数据库启用Service Broker；当指定为DISABLE_BROKER时，针对指定的数据库禁用Service Broker；当指定为NEW_BROKER时，数据库将收到新的代理标识符；当指定为ERROR_BROKER_CONVERSATIONS时，数据库中的会话将会在附加数据库时收到一个错误消息。9.快照隔离选项pALLOW_SNAPSHOT_ISOLATION：如果指定为ON，则事务可以指定SNA

27、PSHOT事务隔离级别，当事务在SNAPSHOT隔离级别运行时，所有的语句都将数据快照视为位于事务的开头；如果指定为OFF（默认值），则事务无法指定SNAPSHOT事务隔离级别。pREAD_COMMITTED_SNAPSHOT：如果设置为ON，则指定READ COMMITTED隔离级别的事务将使用行版本控制而不是锁定，当事务在READ COMMITTED隔离级别运行时，所有的语句都将数据快照视为位于语句的开头；如果设置为OFF（默认值）时，则指定READ COMMITTED隔离级别的事务将使用锁定；设置READ_COMMITTED_ SNAPSHOT选项时，数据库中只允许存在执行ALTER D

28、ATABASE命令的连接，在ALTER DATABASE完成之前，数据库中决不能有其他打开的连接。数据库不必一定要处于单用户模式中。任务任务2.3 2.3 修改数据库修改数据库2.3.5 设置数据库选项10.SQL选项pANSI_NULL_DEFAULT：确定在CREATE TABLE或ALTER TABLE语句中未显式定义为空性的alias数据类型或CLR user-defined type列的默认值（NULL或NOT NULL）。如果设置为ON，则列默认值为NULL。pANSI_NULLS：如果设置为ON，则所有与空值的比较运算计算结果为UNKNOWN。pANSI_PADDING：如果设置

29、为ON，则不剪裁插入varchar或nvarchar列中的字符值的尾随空格，也不剪裁插入varbinary列中的二进制值的尾随零，不将值填充到列的长度；如果设置为OFF（默认值），则剪裁varchar或nvarchar的尾随空格以及varbinary的尾随零。pANSI_WARNINGS：如果设置为ON，则在出现如除以零或聚合函数中出现空值这类情形时将发出错误或警告。pARITHABORT：如果设置为ON，则在执行查询期间发生溢出或除以零的错误时，该查询将结束。pCONCAT_NULL_YIELDS_NULL：当指定为ON时，如果串联操作的两个操作数中任意一个为NULL，则结果也为NULL。p

30、QUOTED_IDENTIFIER：如果设置为ON，则双引号可用来将分隔标识符括起来。pNUMERIC_ROUNDABORT：如果设置为ON，则表达式中出现失去精度时将产生错误。pRECURSIVE_TRIGGERS：如果设置为ON时，则允许递归激发AFTER触发器。任务任务2.3 2.3 修改数据库修改数据库2.3.6 移动数据库文件在SQL Server中，可以通过在ALTER DATABASE语句的FILENAME子句中指定新的文件位置来移动用户数据库。数据、日志也可以通过这种方法进行移动。这在故障恢复（例如由于硬件故障数据库处于可疑模式或被关闭）、预先安排的重定位以及为预定的磁盘维护操

31、作而进行的重定位等情况下是很有用的。如果要将用户数据库中的数据、日志文件移动到新位置，可以在ALTER DATABASE语句的FILENAME子句中指定新的文件位置。这种方法适用于在同一个SQL Server实例中移动数据库文件。如果要将数据库移动到另一个SQL Server实例或另一台服务器上，请使用分离和附加操作或备份和还原。2.3.7 重命名数据库1.使用SSMS重命名数据库使用SSMS重命名数据库的操作方法如下。（1）启动SSMS，连接到SQL Server数据库引擎实例。（2）在对象资源管理器中，展开“数据库”。（3）右键单击要重命名的数据库，然后从弹出菜单中选择“重命名”命令。（4

32、）输入新的数据库名称，按Enter键进行确认。2.使用SQL语句重命名数据库通过在ALTER DATABASE语句中使用MODIFY NAME子句来更改数据库的名称。任务任务2.3 2.3 修改数据库修改数据库2.3.8 删除数据库1.使用SSMS删除数据库使用SSMS删除数据库的方法如下：（1）在对象资源管理器中展开“数据库”；（2）右键单击要删除的数据库；（3）从弹出菜单中选择“删除”命令；（4）在“删除对象”对话框中单击“确定”按钮。2.使用SQL语句删除数据库使用DROP DATABASE从SQL Server实例中删除一个或多个数据库：DROP DATABASE 数据库名,.其中数据

33、库名指定要删除的数据库。例如，下面的语句删除所列出的两个数据库。DROP DATABASE Sales,NewSales;任务任务2.3 2.3 修改数据库修改数据库任务目标 p掌握分离数据库的方法p掌握附加数据库的方法任务任务2.4 2.4 分离和附加数据库分离和附加数据库2.4.1 分离数据库1.使用SSMS分离数据库在对象资源管理器中连接到数据库引擎实例，展开该实例；展开“数据库”，选择要分离的用户数据库的名称；右键单击数据库名称，指向“任务”，然后单击“分离”；在“分离数据库”对话框中设置连接和统计选项，然后单击“确定”按钮。2.使用Transact-SQL语句分离数据库sp_deta

34、ch_db 数据库名称 ,skipchecks ,KeepFulltextIndexFile任务任务2.4 2.4 分离和附加数据库分离和附加数据库2.4.2 附加数据库1.使用SSMS附加数据库在对象资源管理器中连接到数据库引擎实例，展开该实例。在对象资源管理器中右键单击“数据库”，选择“附加”命令。在“附加数据库”对话框中指定要附加的数据库，单击“添加”，在“定位数据库文件”对话框中选择数据库文件。设置其他选项，然后单击“确定”按钮。2.使用Transact-SQL附加数据库CREATE DATABASE 数据库名称ON,.FOR ATTACH|ATTACH_REBUILD_LOG;任务任

35、务2.4 2.4 分离和附加数据库分离和附加数据库任务目标 p掌握备份数据库的方法p掌握还原数据库的方法任务任务2.5 2.5 备份和还原数据库备份和还原数据库2.5.1 备份数据库1.使用SSMS备份数据库在对象资源管理器中连接到数据库引擎实例，并展开该实例。展开“数据库”，右键单击要备份的数据库，指向“任务”，单击“备份”。在“数据库”列表中确认数据库名称，或者从列表中选择其他数据库。从“备份类型”下拉列表中，选择“完整”。在“目标”部分中，从使用“备份到”下拉列表选择备份目标。设置所有备份选项后，单击“确定”。2.使用SQL语句备份数据库BACKUP DATABASE 数据库名称 TO 备份设备,.WITH,.;任务任务2.5 2.5 备份和还原数据库备份和还原数据库2.5.2 还原数据库1.使用SSMS还原数据库连接到SQL Server数据库引擎实例，然后展开该实例。在对象资源管理器窗格中右键单击“数据库”，选择“还原数据库”。选择要还原的备份集的源和位置。对相关选项进行设置。完成还原选项设置后，单击“确定”按钮。2.使用SQL语句还原数据库RESTORE DATABASE|LOG 数据库名称FROM DISK=物理备份设备名称任务任务2.5 2.5 备份和还原数据库备份和还原数据库