第一部分-SQL-SERVER-2005数据库管理与SQL编程

应用系统设计与实践资料下载：1课程内容、性质与任务课程内容SQLServer2005基本操作与管理Transact-SQL编程数据库设计课程性质计算机类专业必修课——“数据库原理与设计”课程的配套实践课程。任务培养使用商用数据库管理系统和编写SQL程序的实践经验，掌握数据库应用系统的数据模型设计方法和系统开发方法。2课程要求实验报告：提交第一部分（共4个实验）的实验报告。报告的内容包括文字描述和上机结果。提交打印结果与实验数据。实验报告的文字描述是对上机过程的总结，包括每一部分完成的步骤和结果。使用WORD文档。实验结果中第一部分实验是所用的数据库的备份，所创建的表、插入的记录应记录和创建的存储过程等应包括在备份的数据库中。考试：笔试(40分)和上机考试(40分)（时间未定）,平时成绩20分。3教材信息系统开发教程--使用C#和SQLSERVER2005清华大学出版社马威,吕文哲,曹瑞2007年4月第1版ISBN97873021461864参考书SQLServer2000开发指南清华大学出版社2002年1月第1版ISBN7-302-05106-2/TP.2988VISUALC#.NET技术内幕清华大学出版社（美）MICKEYWILLIAMS著,冉晓旻罗邓郭炎译2004年6月Erwin数据建模电子工业出版社杨国强、路萍、张志军编著2004年10月5主流商用DBMSOracleDB2SybaseMSSQLServerInformix6数据库管理软件的市场据IDC报告数据显示，2006年全球数据库市场规模达到了165亿美元。虽然现在开源数据库的势头不可阻挡，但在全球数据库市场分布中，前三甲的位置始终被Oracle、DB2、SQLServer所占据。占据榜首位置的依然是Oracle。Oracle在2006年中的销售额为73亿美元，销售额同比增长额为14.7%。Oracle占据了44.4%的市场份额，。IBM凭借DB2，在2006年中的销售额同比增长了11.9%，2006年全年销售额为35亿美元，占据市场份额为21.2%。排在第三位置的依然是微软公司的SQLServer。虽然SQLServer在2006全年销售额为31亿美元，市场份额为18.6%。但是，SQLServer的销售额同比涨幅达到了最高，为25%，远远超过了Oracle和DB2的同比涨幅额度。7SQLServer的版本8SQLServer2005简介SQLServer是一个全面的、集成的、端到端的数据解决方案，它为组织中的用户提供了一个更安全可靠和更高效的平台用于企业数据和BI应用。SQLServer2005为IT专家和信息工作者带来了强大的、熟悉的工具，同时降低了在从移动设备到企业数据系统的多平台上创建、部署、管理和使用企业数据和分析应用程序的复杂性。9数据库引擎MicrosoftSQLServer2005DatabaseEngine是用于存储、处理和保护数据的核心服务。利用数据库引擎可控制访问权限并快速处理事务。10数据库引擎的任务设计并创建数据库以保存系统所需的关系表或XML文档。实现系统以访问和更改数据库中存储的数据。提供日常管理支持以优化数据库的性能。11分析服务MicrosoftSQLServer2005AnalysisServices(SSAS)为商业智能应用程序提供了联机分析处理(OLAP)和数据挖掘功能。AnalysisServices允许设计、创建和管理包含多维结构，使其包含从其他数据源（例如关系数据库）聚合的数据，并通过这种方式来支持OLAP。对于数据挖掘应用程序，AnalysisServices允许使用多种行业标准的数据挖掘算法来设计、创建和可视化从其他数据源构造的数据挖掘模型。12集成服务MicrosoftSQLServer2005IntegrationServices(SSIS)是生成高性能数据集成解决方案（包括数据仓库的提取、转换和加载(ETL)包）的平台。IntegrationServices包含用于生成和调试包的图形工具及向导；用于执行工作流功能的任务，例如FTP操作、SQL语句执行和电子邮件消息处理；用于提取和加载数据的数据源和目标；用于清理、聚合、合并和复制数据的转换；用于管理IntegrationServices的管理服务IntegrationServices；以及对IntegrationServices对象模型进行编程的应用程序编程接口(API)。13复制复制是在数据库之间对数据和数据库对象进行复制和分发，然后在数据库之间进行同步以保持一致性的一组技术。使用复制可以将数据通过局域网、广域网、拨号连接、无线连接和Internet分发到不同位置以及分发给远程用户或移动用户。14报表服务MicrosoftSQLServer2005ReportingServices是一种基于服务器的解决方案，用于生成从多种关系数据源和多维数据源提取内容的企业报表，发布能以各种格式查看的报表，以及集中管理安全性和订阅。ReportingServices包含用于创建和发布报表及报表模型的图形工具和向导；用于管理ReportingServices的报表服务器管理工具；以及用于对ReportingServices对象模型进行编程和扩展的应用程序编程接口(API)。15通知服务MicrosoftSQLServer2005NotificationServices平台用于开发和部署可生成并发送通知的应用程序。可以使用NotificationServices生成并向大量订阅方及时发送个性化的消息，还可以向各种各样的设备传递消息。16服务代理ServiceBroker是数据库引擎的一部分。ServiceBroker提供：用来在SQLServer数据库中存储消息队列的设备。应用程序可以用来发送和接收队列消息的新Transact-SQL语句。17全文搜索MicrosoftSQLServer2005包含对SQLServer表中基于纯字符的数据进行全文查询所需的功能。全文查询可以包括单词和短语，或者一个单词或短语的多种形式。18管理数据库引擎使用SQLServer服务SQLServer服务可以在本地或远程作为服务来启动和停止。SQLServer服务若是默认实例，则被称为SQLServer(MSSQLSERVER)；若是命名实例，则被称为MSSQL$<instancename>。使用SQLServer配置管理器SQLServer配置管理器组合了以下SQLServer2000工具的功能：服务器网络实用工具、客户端网络实用工具和服务管理器。SQLServer配置管理器允许停止、启动、或暂停各种SQLServer2005服务。19管理服务器注册服务器并发管理操作管理大型数据库的内存使用专用管理连接链接服务器20连接服务器21连接服务器-服务器类型22连接服务器-连接选项23连接选项连接到数据库从列表中选择要连接到的数据库。如果选择“<默认值>”，则将连接到服务器的默认数据库。如果选择“<浏览服务器>”，则可以浏览服务器以查找要连接到的数据库。网络协议从该列表中选择某个协议。可用的客户端协议是您使用“计算机管理”中的“客户端网络配置”所配置的那些协议。网络数据包大小输入要发送的网络数据包的大小。默认为4096字节。连接超时值输入在超时之前等待建立连接的秒数。默认值为15秒。执行超时值输入在服务器上完成任务执行之前等待的时间（秒）。默认值为零秒，指示无超时。加密连接强制对连接进行加密。全部重置将所有手动输入的连接属性值替换为默认值。连接使用列出的值尝试连接。选项单击此项可更改对话框并隐藏其他服务器连接选项，如注册服务器和记住密码。24身份验证模式Windows身份验证模式混合模式（Windows身份验证或SQLServer身份验证）25Windows身份验证模式用户通过MicrosoftWindows用户帐户连接时，SQLServer使用Windows操作系统中的信息验证帐户名和密码。这是默认的身份验证模式，比混合模式安全得多。如果选择Windows身份验证，安装程序会创建一个在默认情况下禁用的sa帐户。26WindowsWindows用户administrators组guoadministratordavidericWindows身份验证BULTIN\administratorsSQLServer登录名sa27混合模式（Windows身份验证或SQLServer身份验证）允许用户使用Windows身份验证或SQLServer身份验证进行连接。通过Windows用户帐户连接的用户可以使用Windows验证的受信任连接。如果必须选择“混合模式身份验证”并要求使用SQL登录以适应旧式应用程序，则必须为所有SQL帐户设置强密码。(演示)28更改服务器身份验证模式在SQLServerManagementStudio对象资源管理器中，右键单击服务器，再单击“属性”。在“安全性”页上的“服务器身份验证”下，选择新的服务器身份验证模式，再单击“确定”。在SQLServerManagementStudio对话框中，单击“确定”确认需要重新启动SQLServer。重新启动SQLServer服务(演示)29安全架构30安全架构31数据库安全-主体“主体”是可以请求SQLServer资源的个体、组和过程。与SQLServer授权模型的其他组件一样，主体也可以按层次结构排列。Windows级别的主体SQLServer级别的主体数据库级别的主体主体的影响范围取决于主体定义的范围（Windows、服务器或数据库）以及主体是否不可分或是一个集合。例如，Windows登录名就是一个不可分主体，而Windows组则是一个集合主体。每个主体都有一个唯一的安全标识符(SID)。32各级别的主体Windows级别的主体Windows域登录名Windows本地登录名SQLServer级别的主体SQLServer登录名数据库级别的主体数据库用户数据库角色应用程序角色33数据库用户数据库用户是数据库级别上的主体。每个数据库用户都是public角色的成员。在数据库中，用户与登录账号是两个不同的概念。一个合法的登录账号只表明该账号通过了NT认证或SQLServer认证，但不能表明其可以对数据库数据和数据对象进行某种或某些操作，所以一个登录账号总是与一个或多个数据库用户（这些用户必须分别存在相异的数据库中）相对应，这样才可以访问数据库。例如，登录账号sa自动与每一个数据库用户dbo相关联。用户、数据库角色应用程序角色Master数据库用户、数据库角色应用程序角色Finance数据库用户、数据库角色应用程序角色Course数据库34登录与用户的映射服务器安全性-登录sastudentteacherguestdbostudentteacherguestdboteacherCourse数据库Finance数据库35guest用户默认情况下，数据库创建时就包含一个guest用户。授予guest用户的权限由在数据库中没有用户映射的登录继承。guest用户不能删除，但可以通过在master和temp以外的任何数据库中执行REVOKECONNECTFROMGUEST来撤消该用户的CONNECT权限，从而禁用该用户。36固定服务器角色固定服务器角色描述sysadmin可以在SQLServer中执行任何活动。serveradmin可以设置服务器范围的配置选项，关闭服务器。setupadmin可以管理链接服务器和启动过程。securityadmin可以管理登录和CREATEDATABASE权限，还可以读取错误日志和更改密码。processadmin可以管理在SQLServer中运行的进程。dbcreator可以创建、更改和除去数据库。diskadmin可以管理磁盘文件。bulkadmin可以执行BULKINSERT语句。固定服务器角色的权限不能修改，角色也不能删除。可以从sp_helpsrvrole获得固定服务器角色的列表，可以从sp_srvrolepermission获得每个角色的特定权限。37固定数据库角色可以从sp_helpdbfixedrole获得固定数据库角色的列表，可以从sp_dbfixedrolepermission获得每个角色的特定权限。固定数据库角色描述db_owner在数据库中有全部权限。db_accessadmin可以添加或删除用户ID。db_securityadmin可以管理全部权限、对象所有权、角色和角色成员资格。db_ddladmin可以发出ALLDDL，但不能发出GRANT、REVOKE或DENY语句。db_backupoperator可以发出DBCC、CHECKPOINT和BACKUP语句。db_datareader可以选择数据库内任何用户表中的所有数据。db_datawriter可以更改数据库内任何用户表中的所有数据。db_denydatareader不能选择数据库内任何用户表中的任何数据。db_denydatawriter不能更改数据库内任何用户表中的任何数据。38public角色public角色是一个特殊的数据库角色，每个数据库用户都属于它。public角色：捕获数据库中用户的所有默认权限。无法将用户、组或角色指派给它，因为默认情况下它们即属于该角色。含在每个数据库中，包括master、msdb、tempdb、model和所有用户数据库。无法除去。39系统管理员(sa)登录系统管理员(sa)是为向后兼容而提供的特殊登录。默认情况下，它指派给固定服务器角色sysadmin，并不能进行更改。虽然sa是内置的管理员登录，但不应例行公事地使用它。相反，应使系统管理员成为sysadmin固定服务器角色的成员，并让他们使用自己的登录来登录。只有当没有其它方法登录到Microsoft®SQLServer™实例（例如，当其它系统管理员不可用或忘记了密码）时才使用sa。40使用SQLServer登录sa修改数据库服务器为混合验证模式修改sa登录的密码(强密码)修改sa登录的状态(演示)41数据库的所有者dbo是具有在数据库中执行所有活动的暗示性权限的用户。将固定服务器角色sysadmin的任何成员都映射到每个数据库内称为dbo的一个特殊用户上。另外，由固定服务器角色sysadmin的任何成员创建的任何对象都自动属于dbo。42数据库的所有者无法删除dbo用户，且此用户始终出现在每个数据库中。只有由sysadmin固定服务器角色成员（或dbo用户）创建的对象才属于dbo。由任何其他不是syadmin固定服务器角色成员的用户（包括db_owner固定数据库角色成员）创建的对象：属于创建该对象的用户，而不是dbo。用创建该对象的用户名限定。43数据库的guest用户guest用户帐户允许没有用户帐户的登录访问数据库。当满足下列所有条件时，登录采用guest用户的标识：登录有访问Microsoft®SQLServer™实例的权限，但没有通过自己的用户帐户访问数据库的权限。数据库中含有guest用户帐户。44添加guest用户可以将权限应用到guest用户，就如同它是任何其它用户一样。可以在除master和tempdb外（在这两个数据库中它必须始终存在）的所有数据库中添加或删除guest用户。例如，若要将guest用户添加到名为Accounts的数据库中，请在SQL查询分析器中运行下列代码：USEAccountsGOEXECUTEsp_grantdbaccessguest45添加Windows验证创建Windows用户或组在SQLServer中使用所创建的Windows用户或组创建登录为新的登录指定服务器角色使用新的Windows用户或组登录Windows登录SQLServer(演示)46架构(SCHEMA)架构是形成单个命名空间的数据库实体的集合。命名空间是一个集合，其中每个元素的名称都是唯一的。例如:accounting.ap.george.reconciliation架构包含数据库对象，如表、视图和存储过程。架构所有者可以是数据库用户、数据库角色，也可以是应用程序角色。在SQLServer2000中，数据库用户和架构是隐式连接在一起的。每个数据库用户都是与该用户同名的架构的所有者。对象的所有者在功能上与包含它的架构所有者相同。因而，SQLServer2000中的完全限定名称的“架构”也是数据库中的用户。在SQLServer2005中，架构独立于创建它们的数据库用户而存在。可以在不更改架构名称的情况下转让架构的所有权。47默认架构SQLServer2005还引入了“默认架构”的概念，用于解析未使用其完全限定名称引用的对象的名称。在SQLServer2000中，首先检查的是调用数据库用户所拥有的架构，然后是DBO拥有的架构。在SQLServer2005中，每个用户都有一个默认架构，用于指定服务器在解析对象的名称时将要搜索的第一个架构。可以使用CREATEUSER和ALTERUSER的DEFAULT_SCHEMA选项设置和更改默认架构。如果未定义DEFAULT_SCHEMA，则数据库用户将把DBO作为其默认架构。48架构在数据库中的位置49数据库中预定义的架构50架构所有者架构的所有者可以修改，它可以是数据库用户、数据库角色或应用程序角色。51用户与架构分离的优点多个用户可以通过角色成员身份或Windows组成员身份拥有一个架构。简化了删除数据库用户的操作。删除数据库用户不需要重命名该用户架构所包含的对象。多个用户可以共享一个默认架构以进行统一名称解析。开发人员通过共享默认架构可以将共享对象存储在为特定应用程序专门创建的架构中，而不是DBO架构中。可以用比早期版本中的粒度更大的粒度管理架构和架构包含的对象的权限。完全限定的对象名称现在包含四部分：server.database.schema.object。52多个SQLServer实例Microsoft®SQLServer™2005支持在同一台计算机上同时运行多个SQLServer数据库引擎实例。每个SQLServer数据库引擎实例各有一套不为其它实例共享的系统及用户数据库。默认实例默认实例仅由运行该实例的计算机的名称唯一标识，它没有单独的实例名。如果应用程序在请求连接SQLServer时只指定了计算机名，则SQLServer客户端组件将尝试连接这台计算机上的数据库引擎默认实例。一台计算机上只能有一个默认实例，而默认实例可以是SQLServer的任何版本。命名实例除默认实例外，所有数据库引擎实例都由安装该实例的过程中指定的实例名标识。应用程序必须提供准备连接的计算机的名称和命名实例的实例名。计算机名和实例名以格式computer_name\instance_name指定。一台计算机上可以运行多个命名实例，但只有SQLServer2005数据库引擎才可作为命名实例运行。单个服务器或处理器上的所有实例必须是SQLServer2005的同一本地化版本。53实例组成系统和用户数据库。SQLServer和SQLServer代理服务。与数据库引擎、SQLServer和SQLServer代理服务相关联的注册表键。使应用程序能连接特定实例的网络连接地址。54数据库构架每个SQLServer实例有四个系统数据库（master、model、tempdb和msdb）以及一个或多个用户数据库。有些单位只使用一个用户数据库来存储其所有数据。55master数据库master数据库记录SQLServer系统的所有系统级信息。这包括实例范围的元数据（例如登录帐户）、端点、链接服务器和系统配置设置。此外，master数据库还记录了所有其他数据库的存在、数据库文件的位置以及SQLServer的初始化信息。因此，如果master数据库不可用，则SQLServer无法启动。在SQLServer2005中，系统对象不再存储在master数据库中，而是存储在Resource数据库中。56model数据库model数据库用作在SQLServer实例上创建的所有数据库的模板。因为每次启动SQLServer时都会创建tempdb，所以model数据库必须始终存在于SQLServer系统中。57msdb数据库msdb数据库由SQLServer代理用于计划警报和作业，也可以由其他功能（如ServiceBroker和数据库邮件）使用。58Resource数据库Resource数据库是只读数据库，它包含了SQLServer2005中的所有系统对象。SQLServer系统对象（例如sys.objects）在物理上持续存在于Resource数据库中，但在逻辑上，它们出现在每个数据库的sys架构中。Resource数据库不包含用户数据或用户元数据。Resource数据库的物理文件名是mssqlsystemresource.mdf和mssqlsystemresource.ldf。默认情况下，这些文件位于<驱动器>:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\Data\Resource数据库依赖于master数据库的位置。如果移动了master数据库，则必须也将Resource数据库移动到相同的位置。59tempdb数据库tempdb系统数据库是一个全局资源，可供连接到SQLServer实例的所有用户使用，并可用于保存下列各项：显式创建的临时用户对象，例如全局或局部临时表、临时存储过程、表变量或游标。SQLServer2005数据库引擎创建的内部对象，例如，用于存储排序的中间结果的工作表。由使用已提交读的数据库中数据修改事务生成的行版本。由数据修改事务为实现联机索引操作、多个活动的结果集(MARS)以及AFTER触发器等功能而生成的行版本。每次启动SQLServer时都会重新创建tempdb，从而在系统启动时总是保持一个干净的数据库副本。在断开联接时会自动删除临时表和存储过程，并且在系统关闭后没有活动连接。不允许对tempdb进行备份和还原操作。60文件与文件组每个SQLServer2005数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。为了便于分配和管理，可以将数据文件集合起来，放到文件组中。61文件的类型(1)主要数据文件包含数据库的启动信息，并指向数据库中的其他文件。用户数据和对象可存储在此文件中，也可以存储在次要数据文件中。每个数据库有一个主要数据文件。主要数据文件的建议文件扩展名是.mdf。(2)次要数据文件是可选的，由用户定义并存储用户数据。通过将每个文件放在不同的磁盘驱动器上，次要文件可用于将数据分散到多个磁盘上。另外，如果数据库超过了单个Windows文件的最大大小，可以使用次要数据文件，这样数据库就能继续增长。次要数据文件的建议文件扩展名是.ndf。(3)事务日志文件保存用于恢复数据库的日志信息。每个数据库必须至少有一个日志文件。事务日志的建议文件扩展名是.ldf。62文件组每个数据库有一个主要文件组。此文件组包含主要数据文件和未放入其他文件组的所有次要文件。可以创建用户定义的文件组，用于将数据文件集合起来，以便于管理、数据分配和放置。例如，可以分别在三个磁盘驱动器上创建三个文件Data1.ndf、Data2.ndf和Data3.ndf，然后将它们分配给文件组fgroup1。然后，可以明确地在文件组fgroup1上创建一个表。对表中数据的查询将分散到三个磁盘上，从而提高了性能。通过使用在RAID（独立磁盘冗余阵列）条带集上创建的单个文件也能获得同样的性能提高。但是，文件和文件组能够轻松地在新磁盘上添加新文件。63默认文件组如果在数据库中创建对象时没有指定对象所属的文件组，对象将被分配给默认文件组。不管何时，只能将一个文件组指定为默认文件组。默认文件组中的文件必须足够大，能够容纳未分配给其他文件组的所有新对象。PRIMARY文件组是默认文件组，除非使用ALTERDATABASE语句进行了更改。但系统对象和表仍然分配给PRIMARY文件组，而不是新的默认文件组。64逻辑与物理文件名逻辑文件名是在所有Transact-SQL语句中引用物理文件时所使用的名称。逻辑文件名必须符合SQLServer标识符规则，而且在数据库中的逻辑文件名中必须是唯一的。物理文件名是包括目录路径的物理文件名。它必须符合操作系统文件命名规则。master数据库:(1)数据文件的逻辑名为master,物理文件名为C:\ProgramFiles\MicrosoftSQLServer\MSSQL.2\MSSQL\DATA\master.mdf(2)日志文件的逻辑文件名为mastlog,物理文件名为C:\ProgramFiles\MicrosoftSQLServer\MSSQL.2\MSSQL\DATA\mastlog.ldf65事务日志每个SQLServer2005数据库都具有事务日志，用于记录所有事务以及每个事务对数据库所做的修改。事务日志是数据库的重要组件，如果系统出现故障，则可能需要使用事务日志将数据库恢复到一致状态。事务日志支持以下操作：撤消个别的事务。在SQLServer启动时撤消所有未完成的事务。将还原的数据库、文件、文件组或页前滚至故障点。支持事务复制支持备份服务器解决方案。66事务日志的特点事务日志是作为数据库中的单独的文件或一组文件实现的。日志记录和页的格式不必遵守数据页的格式。事务日志可以在几个文件上实现。可以将这些文件定义为自动扩展。这样可减少事务日志内空间不足的可能性。重用日志文件中空间的机制速度快且对事务吞吐量影响最小。67数据库的状态ONLINE—可以对数据库进行访问。OFFLINE—数据库无法使用。RECOVERING—正在恢复数据库。RECOVERYPENDING—SQLServer在恢复过程中遇到了与资源相关的错误。数据库未损坏，但是可能缺少文件，或系统资源限制可能导致无法启动数据库。数据库不可用。SUSPECT—至少主文件组可疑或可能已损坏。在SQLServer启动过程中无法恢复数据库。数据库不可用。EMERGENCY—用户更改了数据库，并将其状态设置为EMERGENCY。数据库处于单用户模式，可以修复或还原。68文件状态ONLINE—文件可用于所有操作。OFFLINE—文件不可访问，并且可能不显示在磁盘中。RESTORING—正在还原文件。RECOVERYPENDING—文件恢复被推迟。SUSPECT—在线还原过程中，恢复文件失败。DEFUNCT—当文件不处于在线状态时被删除。69数据库存储结构数据库数据文件.MDF或者.NDF日志文件.LDF盘区（8个连续页面）数据页面（8KB）最大行长度8060字节表、索引70页面页面是数据库中使用的最小数据单元。每一个页面存储8KB，所有的页面都包含一个96字节的页面头。页面的类型：分配页面—用于控制数据库中给表和索引分配的页面数据和日志页面—存储数据库中数据和事务日志数据索引页面—存储索引数据分发页面—存储有关索引的信息文本/图像页面—存储文本或二进制大对象（BLOB）71盘区盘区（extent）是由8个连续的页面组成的数据结构，大小为8X8KB=64KB。盘区是表和索引分配存储空间的单位。72数据库逻辑组件约束表默认值触发器索引用户定义的数据类型键用户定义的函数存储过程视图73检查点检查点从当前数据库的高速缓冲存储器中刷新脏数据和日志页，以尽量减少在恢复时必须前滚的修改量。SQLServer2000检查点在当前数据库内执行下列进程：将标记检查点起点的记录写入日志文件。将为检查点记录的信息存储在检查点日志记录链内。将这条链起点的LSN写入数据库根页。记录在检查点记录中的一条信息是第一个日志映像的LSN，该映像必须存在以保证成功的数据库范围的回滚。这个LSN称为最小恢复LSN(MinLSN)，它是下面这些LSN中的最小LSN：74检查点检查点起点的LSN。最旧的活动事务起点的LSN。最早的复制事务起点的LSN，该事务尚未复制到所有订阅服务器。记录在检查点记录中的另一条信息是所有未完成的活动事务的列表。如果数据库使用的是简单恢复模式，则删除新的MinLSN之前的所有日志记录。将所有脏日志和数据页写入磁盘。将标记检查点末端的记录写入日志文件。75检查点的产生当执行CHECKPOINT语句时。对连接所使用的当前数据库执行检查点操作。当使用ALTERDATABASE更改数据库选项时。当数据库选项被更改时，ALTERDATABASE对数据库执行检查点操作。当SQLServer实例由于以下原因停止运行时：执行SHUTDOWN语句。使用SQLServer服务控制管理器阻止运行数据库引擎实例的服务。上述方法之一对SQLServer实例中的每个数据库执行检查点操作。当SQLServer实例为减少恢复数据库所需的时间而在每个数据库内定期生成自动检查点时。76SQL查询分析器SQL查询分析器是一个图形用户界面，用以交互地设计和测试Transact-SQL语句、批处理和脚本。可以从SQLServer企业管理器调用SQL查询分析器。SQL查询分析器提供：用于键入Transact-SQL语句的自由格式文本编辑器。在Transact-SQL语法中使用不同的颜色，以提高复杂语句的易读性。对象浏览器和对象搜索工具，可以轻松查找数据库中的对象和对象结构。模板，可用于加快创建SQLServer对象的Transact-SQL语句的开发速度。模板是包含创建数据库对象所需的Transact-SQL语句基本结构的文件。用于分析存储过程的交互式调试工具。以网格或自由格式文本窗口的形式显示结果。显示计划信息的图形关系图，用以说明内置在Transact-SQL语句执行计划中的逻辑步骤。77sqlcmd实用工具使用sqlcmd实用工具可以在命令提示符处输入Transact-SQL语句、系统过程和脚本文件。78sqlcmd命令语法sqlcmd[{{-Ulogin_id[-Ppassword]}|–E}][-Sserver_name[\instance_name]][-Hwksta_name][-ddb_name][-ltime_out][-ttime_out][-hheaders][-scol_separator][-wcolumn_width][-apacket_size][-e][-I][-ccmd_end][-L[c]][-q"query"][-Q"query"][-merror_level][-V][-W][-u][-r[0|1]][-iinput_file][-ooutput_file][-f<codepage>|i:<codepage>[<,o:<codepage>][-k[1|2]][-ydisplay_width][-Ydisplay_width][-p[1]][-R][-b][-v][-A][-X[1]][-x][-?]]79ODBC管理器ODBC管理器实用工具用于添加、删除和编辑计算机上所有ODBC驱动程序的ODBC数据源，包括SQLServerODBC驱动程序的数据源。80数据库的备份与恢复北京理工大学计算机科学技术学院郭贵锁资料下载:81数据库的故障类型介质错误用户错误（例如，不小心删除表）硬件故障（例如，磁盘故障或服务器故障）自然灾害82数据库备份的类型完整备份“完整备份”包括特定数据库（或者一组特定的文件组或文件）中的所有数据，以及可以恢复这些数据的足够的日志。差异备份“差异备份”基于数据的最新完整备份。这称为差异的“基准”或者差异基准。差异备份仅包括自建立差异基准后发生更改的数据。还原时，首先还原完整备份，然后再还原最新的差异备份。83简单恢复模式下的备份下图显示简单恢复模式下最简单的备份和还原策略。其中有五个数据库备份，只只有在时间t5进行的最新备份才需要还原，还原这个备份将数据库恢复到t5时间点。所有后面的更新（以t6方块表示）都会丢失。84降低损失风险85简单数据库恢复模式下备份示例--BackuptheAdventureWorksdatabasetonewmediaset.BACKUPDATABASEAdventureWorksTODISK='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak';WITHFORMAT;GO86使用SSMS备份数据库连接数据库(注意权限)选择要备份的数据库右键菜单中选择”任务/备份”填写相应的内容(如果要进行完整恢复模式下的备份则需要事先修改数据库的属性)87用差异备份降低风险88在完整恢复模式下的备份完整恢复模式使用日志备份在最大范围内防止出现故障时丢失数据，这种模式需要备份和还原事务日志（“日志备份”）。使用日志备份的优点是允许您将数据库还原到日志备份内包含的任何时点（“时间点恢复”）。假定可以在发生严重故障后备份活动日志，则可将数据库一直还原到没有发生数据丢失的故障点处。89备份策略示例一下图显示了在完整恢复模式下的最简单的备份策略。在此图中，已完成了数据库备份Db_1以及两个例行日志备份Log_1和Log_2。在Log_2日志备份后的某个时间，数据库出现数据丢失。在还原这三个备份前，数据库管理员必须备份活动日志（日志尾部）。然后还原Db_1、Log_1和Log_2，而不恢复数据库。接着数据库管理员还原并恢复尾日志备份(Tail)。这将把数据库恢复到故障点，从而恢复所有数据。90备份策略示例二91完整数据库恢复模式下的备份示例USEmaster;ALTERDATABASEAdventureWorksSETRECOVERYFULL;GO--BackuptheAdventureWorksdatabasetonewmediaset(backupset1).BACKUPDATABASEAdventureWorksTODISK='C:\ProgramFiles\MicrosoftSQLServer\MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak';WITHFORMAT;GO--Createaroutinelogbackup(backupset2).BACKUPLOGAdventureWorksTODISK='C:\ProgramFiles\MicrosoftSQLServer\ MSSQL.1\MSSQL\BACKUP\AdventureWorksFullRM.bak';GO92大容量事务日志恢复模式下的备份大容量日志恢复模式是一种特殊用途的恢复模式，只应偶尔用于提高某些大规模大容量操作（如大量数据的大容量导入）的性能。93简单恢复模式下还原完整数据库如果只使用完整数据库备份，则只需还原最近的备份。如果还使用差异数据库备份，则应还原最近的完整数据库备份而不恢复数据库，然后还原最近的差异数据库备份并恢复数据库。94USEmaster;--1.Makesurethedatabaseisusingthesimplerecoverymodel.ALTERDATABASEAdventureWorksSETRECOVERYSIMPLE;GO--2.BackupthefullAdventureWorksdatabase.BACKUPDATABASEAdventureWorksTODISK=‘C:\SQLServerBackups\AdventureWorks.bak'WITHFORMAT;GO--3.Createadifferentialdatabasebackup.BACKUPDATABASEAdventureWorksTODISK=‘C:\SQLServerBackups\AdventureWorks.bak'WITHDIFFERENTIAL;GO--4.Restorethefulldatabasebackup(frombackupset1).RESTOREDATABASEAdventureWorksFROMDISK=‘C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=1,NORECOVERY;--5.Restorethedifferentialbackup(frombackupset2).RESTOREDATABASEAdventureWorksFROMDISK=‘C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=2;GO95完整恢复模式下执行数据库完整还原备份活动事务日志（称为日志尾部）。此操作将创建尾日志备份。如果活动事务日志不可用，则该日志部分的所有事务都将丢失。还原最新完整数据库备份而不恢复数据库(RESTOREDATABASEdatabase_nameFROMbackup_deviceWITHNORECOVERY)。如果存在差异备份，则还原最新的差异备份而不恢复数据库(RESTOREDATABASEdatabase_nameWITHNORECOVERY)。从还原备份后创建的第一个事务日志备份开始，使用NORECOVERY依次还原日志。恢复数据库(RESTOREDATABASEdatabase_nameWITHRECOVERY)。此步骤也可以与还原上一次日志备份结合使用。数据库完整还原通常可以恢复到日志备份中的某一时间点或标记的事务。96完整恢复模式下还原完整数据库示例USEmaster;--1.将数据库修改为完整恢复模式.ALTERDATABASEAdventureWorksSETRECOVERYFULL;GO--2.执行数据库的完整备份.BACKUPDATABASEAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFORMAT;GO--3.创建日志文件备份.BACKUPLOGAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak';GO--4.创建尾日志备份.BACKUPLOGAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak';GO97完整恢复模式下还原完整数据库示例(续)--5.还原数据库完整备份(自备份集1).RESTOREDATABASEAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=1,NORECOVERY;--6.还原常规日志备份(自备份集2).RESTORELOGAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=2,NORECOVERY;--7.还原尾日志备份(自备份集3).RESTORELOGAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=3,NORECOVERY;GO--8.恢复数据库:RESTOREDATABASEAdventureWorksWITHRECOVERY;GO98恢复数据库至某个时间点示例USEmaster;--1.确认数据库工作在完整恢复模式.ALTERDATABASEAdventureWorksSETRECOVERYFULL;GO--2.创建一个表，如果已经有该表则先删除.droptableAdventureWorks.dbo.Table_1;createtableAdventureWorks.dbo.Table_1(iddatetime);--3.备份数据库BACKUPDATABASEAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFORMAT;GO99恢复数据库至某个时间点示例（续）--4.往表Table_1中插入第1条记录并查询当前时间insertintoAdventureWorks.dbo.table_1values(current_timestamp);selectcurrent_timestamp;--5.在插入第一条记录后备份日志文件.BACKUPLOGAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak';GO--6.插入第2条记录并显示当前时间.insertintoAdventureWorks.dbo.table_1values(current_timestamp);selectcurrent_timestamp;--7.在插入第2条记录后备份日志文件BACKUPLOGAdventureWorksTODISK='C:\SQLServerBackups\AdventureWorks.bak';GO100恢复数据库至某个时间点示例（续）--8.还原完整数据库备份（在恢复之前先删除数据库）RESTOREDATABASEAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITHFILE=1,NORECOVERY;--9.恢复日志文件（时间点为插入第1条记录后且最好是插入第2条记录前的时间RESTORELOGAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITH,STOPAT='2007-10-717:35:18';--10.恢复日志文件（注意时间与上面的时间相同）RESTORELOGAdventureWorksFROMDISK='C:\SQLServerBackups\AdventureWorks.bak'WITH,STOPAT='2007-10-717:35:18';GO--11.恢复数据库（这一步可能不再需要）RESTOREDATABASEAdventureWorksWITHRECOVERY;GO101Transact-SQL北京理工大学计算机科学技术学院郭贵锁资料下载:102Transact-SQLTransact-SQL语言用于管理SQLServer数据库引擎实例，创建和管理数据库对象，以及插入、检索、修改和删除数据。Transact-SQL是对按照国际标准化组织(ISO)和美国国家标准协会(ANSI)发布的SQL标准定义的语言的扩展。103Transact-SQL的元素元素说明标识符表、视图、列、数据库和服务器等对象的名称。数据类型定义数据对象（如列、变量和参数）所包含的数据的类型。函数语法元素，可以接受零个、一个或多个输入值，并返回一个标量值或表格形式的一组值。示例包括将多个值相加的SUM函数、获取实例名称的@@SERVERNAME函数。表达式SQLServer可以解析为单个值的语法单位。表达式中的运算符与一个或多个简单表达式一起使用，构造一个更为复杂的表达式。注释插入到Transact-SQL语句或脚本中、用于解释语句作用的文本段。保留关键字保留下来供SQLServer使用的词，不应用作数据库中的对象名。104标识符数据库对象的名称即为其标识符。MicrosoftSQLServer2005中的所有内容都可以有标识符。服务器、数据库和数据库对象（例如表、视图、列、索引、触发器、过程、约束及规则等）都可以有标识符。大多数对象要求有标识符，但对有些对象（例如约束），标识符是可选的。对象标识符是在定义对象时创建的。标识符随后用于引用该对象。例如，下列语句创建一个标识符为Course的表，该表中有两列的标识符分别是ID和Name：CREATETABLECourse (IDINTPRIMARYKEY,Namenvarchar(80))105标识符的种类常规标识符—符合标识符的格式规则。在Transact-SQL语句中使用常规标识符时不用将其分隔开。分隔标识符—包含在双引号(“)或者方括号([])内。符合标识符格式规则的标识符可以分隔，也可以不分隔。SELECT*FROM[TableX]--分隔符是可选的.WHERE[KeyCol]=124--分隔符是可选的.SELECT*FROM[MyTable]--标识符含有空格并使用保留字.WHERE[order]=10--标识符是一个保留字.106常规标识符规则第一个字符必须是下列字符之一：Unicode标准3.2所定义的字母。Unicode中定义的字母包括拉丁字符a-z和A-Z，以及来自其他语言的字母字符。下划线(_)、“at”符号(@)或者数字符号(#)。在SQLServer中，某些位于标识符开头位置的符号具有特殊意义。以“at”符号开头的标识符表示局部变量或参数。以一个数字符号开头的标识符表示临时表或过程。以两个数字符号(##)开头的标识符表示全局临时对象。某些Transact-SQL函数的名称以两个at符号(@@)开头。为了避免与这些函数混淆，不应使用以@@开头的名称。107常规标识符规则（续）后续字符可以包括：如Unicode标准3.2中所定义的字母。基本拉丁字符或其他国家/地区字符中的十进制数字。“at”符号、美元符号($)、数字符号或下划线。标识符不能是Transact-SQL保留字。SQLServer保留其保留字的大写和小写形式。不允许嵌入空格或其他特殊字符。108对象名完整的对象名称由四个标识符组成：服务器名称、数据库名称、架构名称和对象名称。其格式如下：[[[server.][database].][schema_name].]object_name例如：Bit.Course.dbo.StudentCourse.dbo.Studentdbo.StudentStudent109对象名称限定符服务器、数据库和所有者的名称即所谓的对象名称限定符。引用对象时，不必指定服务器、数据库和所有者。可以用句点标记它们的位置来省略限定符。对象名称的有效格式包括以下几种：server.database.schema_name.object_nameserver.database..object_nameserver..schema_name.object_nameserver...object_namedatabase.schema_name.object_namedatabase..object_nameschema_name.object_nameobject_name110数据类型111用户定义的数据类型--Createabirthdaydatatypethatallowsnulls.EXECsp_addtypebirthday,datetime,'NULL'GO--Createatableusingthenewdatatype.CREATETABLEemployee(emp_idchar(5),emp_first_namechar(30),emp_last_namechar(40),emp_birthdaybirthday)112访问与更改数据库北京理工大学计算机科学技术学院郭贵锁资料下载:113表的概念表是一个关于特定实体（人、地点或事务）的数据集合，表数据的属性（如学号、姓名、年龄等）在表中称为列或字段，表中的每个数据实例称为行或记录。表名行列列名114表的规划表要包含的数据的类型。表中的列数，每一列中数据的类型和长度（如果必要）。哪些列允许空值。是否要使用以及何处使用约束、默认设置和规则。所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。115使用标识符引用列同一个表或视图内的列名称必须唯一。最多可以使用三个前缀来指定查询中的列，查询中引用的多个表可以具有同名称的列。可以使用下列格式之一：database_name.schema_name.object_name.column_namedatabase_name..object_name.column_nameschema_name.object_name.column_nameobject_name.column_name116为列定义数据类型数据类型定义了各列允许使用的数据值。通过下列方法之一可以为列指定数据类型：使用SQLServer2005系统数据类型。创建基于系统数据类型的别名数据类型。从在Microsoft.NETFramework公共语言运行时中创建的类型中创建用户定义类型。117数据完整性实体完整性域完整性引用完整性用户定义完整性118实体完整性实体完整性将行定义为特定表的唯一实体。实体完整性通过索引、UNIQUE约束、PRIMARYKEY约束或IDENTITY属性强制表的标识符列或主键的完整性。示例CREATETABLEstudent(idINTPRIMARYKEY,Namenvarchar(10)NOTNULL,AgeINTCHECK(ageBETWEEN18AND50),SexNCHAR(2)CHECK(sexIN(‘男‘，’女‘））119PRIMARYKEY约束CREATETABLEPRODUCT（PRODUCTIDINT，VENDORIDINT，AVERAGELEADTIMEINT，STANDARDPRICEMONEY，LASTRECEIPTCOSTMONEY，PRIMARYKEY（PRODUCTID，VENDORID））120PRIMARYKEY约束表通常具有包含唯一标识表中每一行的值的一列或一组列。这样的一列或多列称为表的主键(PK)，用于强制表的实体完整性。121域完整性域完整性指特定列的项的有效性。可以强制域完整性限制类型（通过使用数据类型）、限制格式（通过使用CHECK约束和规则）或限制可能值的范围（通过使用FOREIGNKEY约束、CHECK约束、DEFAULT定义、NOTNULL定义和规则）。CREATETABLEstudent(idINTPRIMARYKEY,Namenvarchar(10)NOTNULL,AgeINTCHECK(ageBETWEEN18AND50),SexNCHAR(2)CHECK(sexIN(‘男‘，’女‘））122引用完整性示例CREATETABLESALES（SalesOrderIDINTPRIMARYKEY,OrderQtyINT,ProductIDINTREFERENCESProduct(ProductID));父表/被引用表/主表子表/引用表/相关表123用户定义完整性用户定义完整性可以定义不属于其他任何完整性类别的特定业务规则。所有完整性类别都支持用户定义完整性。这包括CREATETABLE中所有列级约束和表级约束、存储过程以及触发器。124修改表ALTERTABLEStudentADDBirthdayDate;ALTERTABLECourseADDCreditINT;ALTERTABLECourseALTERCOLUMNCreditDECIMAL(3,1);125删除表DROPTABLESTUDENT126插入记录INSERTINTOSTUDENTVALUES(5,’Eric’,23);127引用完整性在输入或删除记录时，引用完整性保持表之间已定义的关系。在SQLServer2005中，引用完整性通过FOREIGNKEY和CHECK约束，以外键与主键之间或外键与唯一键之间的关系为基础。引用完整性确保键值在所有表中一致。这类一致性要求不引用不存在的值，如果一个键值发生更改，则整个数据库中，对该键值的所有引用要进行一致的更改。强制引用完整性时，SQLServer将防止用户执行下列操作：在主表中没有关联的记录时，将记录添加或更改到相关表中。更改主表中的值，这会导致相关表中生成孤立记录。从主表中删除记录，但仍存在与该记录匹配的相关记录。128SELECT语句SELECT[ALL|DISTINCT][TOPn]<选择列表>

[FROM]{<表资源>}[,…n]

[WHERE]<搜索条件>[GROUPBY]{<分组表达式>}[,…n][HAVING]<搜索条件>[ORDERBY]{<字段名[ASC|DESC]>}[,…n]示例:SELECTID,NAME,AGEFROMSTUDENTWHERENAME=‘张杰’;129通配符通配符描述%0或多个字符串_任何单个的字符[]在指定区域或集合内的任何单个字符[^]不在指定区域或集合内的任何单个字符SELECTID,NAME,AGEFROMSTUDENTWHERENAMELIKE‘张%’;SELECTID,NAME,AGEFROMSTUDENTWHERENAMELIKE‘张__杰’;130使用逻辑运算符SELECTproductid,productname,supplierid,unitprice

FROMproducts

WHERE(productnameLIKE'T%'ORproductid=46)

AND

(unitprice>16.00)

131搜索一定范围的行SELECTID,NAME,AGEFROMSTUDENTWHEREAGEBETWEEN20AND30;132聚合函数聚合函数描述AVG计算表达式中平均值COUNT表达式中值的数目COUNT(*)所选择的行的数目MAX表达式中的最大值MIN表达式中最小值SUM计算表达式中所有值的和STDEV样本标准偏差STDEVP填充标准偏差VAR样本方差VARP总体方差133分组USEnorthwindSELECTproductid,orderid

,quantityFROMorderhist

GOUSEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductid

GOproductidtotal_quantity115235345productidorderidquantity11511102110222531153230productidtotal_quantity235只对满足WHERE子句的行分组USEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistWHEREproductid=2GROUPBYproductid

GO示例1示例2134联合使用GROUPBY子句和HAVING子句（续）USEnorthwindSELECTproductid,orderid,quantityFROMorderhist

GOUSEnorthwindSELECTproductid,SUM(quantity)AStotal_quantityFROMorderhistGROUPBYproductidHAVINGSUM(quantity)>=30

GOproductidtotal_quantity235345productidorderidquantity11511102110222531153230示例135使用内连接USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersINNERJOINsalesONbuyers.buyer_id=sales.buyer_id

GOsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234示例136使用外连接USEjoindbSELECTbuyer_name,sales.buyer_id,qtyFROMbuyersLEFTOUTERJOINsalesONbuyers.buyer_id=sales.buyer_id

GOsalesbuyer_idprod_idqty114323151553711421003buyersbuyer_nameAdamBarrSeanChaiEvaCoretsErinO’Meliabuyer_id1234结果buyer_nameAdamBarrAdamBarrErinO’MeliaEvaCoretsbuyer_idqty11431553711ErinO’Melia41003SeanChaiNULLNULL示例137表的类型已分区表临时表系统表138已分区表已分区表是将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中。在维护整个集合的完整性时，使用分区可以快速而有效地访问或管理数据子集，从而使大型表或索引更易于管理。139系统表SQLServer将定义服务器配置的数据以及它的所有表存储在称为系统表的特殊表集内。用户不应直接查询或更新系统表，除非没有其它方法获得应用程序所需的数据。只有SQLServer才应引用系统表来响应用户发出的管理命令。系统表可能因版本的不同而异。对于直接引用系统表的应用程序，可能必须先进行重写，然后才能升级到使用不同版本系统表的新SQLServer版本。SQLServer2005数据库引擎

系统表已作为只读视图实现，目的是为了保证SQLServer2005中的向后兼容性。无法直接使用这些系统表中的数据。建议通过使用目录视图访问SQLServer元数据。140