···························································· 第3 章 chapter3 SQLServer2019基础 SQLServer是Microsoft(微软)公司发布的适用于大型网络环境的企业级数据库产 品,是一个典型的关系数据库管理系统,一经推出便得到了广大用户的积极响应,成为数 据库市场上的一个重要产品。经过对SQLServer的不断更新换代,最新的SQLServer 2019版本使用统一的数据平台实现业务转型,附带ApacheSpark和HadoopDistributed FileSystem(HDFS),可实现所有数据的智能化。通过本章 学习SQLServer2019相关基础知识,为SQLServer2019后 续业务数据处理的实际操作和应用奠定基础。 教学目标 (1)了解SQLServer的概念和主要发展过程。 (2)了解SQLServer2019的新增功能及版本。 (3)理解SQLServer2019的体系结构及文件类型。 (4)掌握SQLServer2019的系统数据库及作用。 (5)掌握数据库命令语法规则、特点及用法。 (6)掌握SQLServer2019的安装与配置。 3.1　SQL Server 的概念和发展 .................................................................................. ........................ .................................................................................. ........................ 【案例3-1】 SQLServer的强大功能获得国际研究调查机构Gartner的肯定,曾 荣登Gartner魔力象限领导者冠军地位,将SQLServer评选在领导者象限内,领先 Oracle等,并将在市场远见、强大执行力与效能及支持服务等方面具有极大优势。 SQLServer的强大功能将为企业带来绝佳的领先优势并提供让企业无后顾之忧的支 持服务。 SQLServer2019的推出,代表了数据库的最新技术和发展。在2020年6月DB -Engines数据库排名中,SQLServer依然稳居榜单的第三名。 ...................... ........ ...................... .......... .. .. .. 教学课件 第3 章SQL Server2019基础 8 0 ◆数据库原理及应用与实践(第4 版)——基于SQL Server 2019 3.1.1 SQL Server 的概念及发展概况 1.SQL及T-SQL的基本概念 1974年IBM 圣约瑟实验室的Boyce和Chamberlin,为关系数据库管理系统设计的 一种查询语言,当时称为SEQUEL 语言,后简称为结构化查询语言(StructuredQuery Language,SQL)。其率先在IBM 公司的关系数据库系统SystemR上实现,主要用于访 问和处理数据的标准计算机语言。 1986年美国国家标准局(AmericanNaturalStandardInstitute,ANSI)正式批准为 关系数据库语言的国家标准。1987年获得国际标准化组织(InternationalOrganization forStandardization,ISO)的通过,成为国际通用标准。在信息化业务数据处理应用中,所 有企事业机构的关系数据库管理系统DBMS产品基本都支持SQL,已经被广泛应用于 各行各业。 T-SQL是Transact-SQL的简称,是微软公司在SQLServer系统中使用的事务-结 构化查询语言。它是SQLServer的核心组件,也是对SQL的一种扩展形式。 2.SQLServer的发展概况 SQLServer最初由Microsoft、Sybase和Ashton-Tate三家公司共同研发,是一种广 泛应用于网络业务数据处理的关系数据库管理系统。从SQLServer6.0开始首次由 Microsoft公司独立研发,1996年推出SQLServer6.5版本,1998年又推出了7.0版,并 于2000年9月发布了SQLServer2000,正式进入企业数据库的行列。SQLServer2005 则真正走向了成熟,与Oracle、IBM DB2形成了三足鼎立之势;之后SQLServer经历了 2008、2008R2、2012、2014、2016、2017、2019版本的持续投入和不断进化。 3.1.2 SQL Server 版本及优点 微软公司从1995年到2019年20多年来,开发的数据库管理系统SQLServer,各种 业务数据处理新技术得到了广泛应用且不断快速发展和完善,其版本发布时间和开发代 号见表3-1。 表3-1 SQLServer版本发布时间和开发代号 发布时间产品名称开发代号内核版本 1995年SQLServer6.0 SQL95 6.x 1996年SQLServer6.5 Hydra 6.5 1998年SQLServer7.0 Sphinx 7.x 2000年SQLServer2000 Shiloh 8.x 2003年SQLServer2000Enterprise64位版Liberty 8.x 2005年SQLServer2005 Yukon 9.x ◆ 第 3 章　SQL Server 2019 基础81 续表 发布时间产品名称开发代号内核版本 2008 年SQLServer2008 Katmai 10.x 2005 年SQLServer2008R2 Kilimanjaro 10.5 2012 年SQLServer2012 Denali 11.x 2014 年SQLServer2014 Hekaton 12.x 2016 年SQLServer2016 DataExplorer 13.x 2017 年SQLServer2017 — 14.x 2019 年SQLServer2019 — 15.x SQLSevr2019(x) 旨 re15.是微软公司最新研发的新一代旗舰级数据库和分析平台, 在将SQLServer发展成一个平台,以提供开发语言、数据类型、本地或云以及操作系统 选项。 SQLServer2019 为所有数据工作负载带来了创新的安全性和合规性功能、业界领 先的性能、任务关键型可用性和高级分析,现在还支持内置的大数据。同时带来了十大 全新亮点,将行业领先的性能和SQLServer安全性引入所选的语言、平台、结构化和非 结构化数据。 (1)利用大数据的力量。具备由SQLServer、Spark和HDFS 组成的可扩展计算和 存储功能的大数据群集。数据可在扩展数据集市中缓存。 (2)将AI 引入工作负载。完整的AI 平台,可使用AzureDataStudioNotebooks在 SQLServerML 服务或SparkML 中培训和实施模型。 (3)消除数据迁移的需求。借助数据虚拟化,用户可以查询关系和非关系数据,而无 须对数据进行迁移或复制。 (4)了解可视数据并与之进行交互。使用SQLServerBI 工具和PowerBI 报表服 务器进行可视化数据浏览和交互式分析。 (5)对操作数据运行实施分析。使用HTAP 对操作数据进行分析的内存技术。通 过持久内存提高并发性和规模。 (6)自动调整SQLServer。智能查询处理改善了查询的扩展,自动计划更正解决了 性能问题。 (7)减少数据库维护并延长业务正常运行时间。在线索引操作的增加延长了正常运 行时间。现在可使用Kubernetes在容器上运行AlwaysOn 可用性组。 (8)提高安全性并保护使用中的数据。SQLServer支持多个安全层,包括Always EncryptedSecureEnclave中的计算保护。 (9)跟踪复杂资源的合规性。通过数据发现和分类(可通过标记确保遵守GDPR)及 漏洞评估工具跟踪合规性。 (10)利用丰富选择和灵活性进行优化。支持选择Windows、Linux和容器。在 SQLServer上运行Java代码,并存储和分析图形数据。 8 2 ◆数据库原理及应用与实践(第4 版)——基于SQL Server 2019 讨论思考 (1)什么是SQL和T-SQL? 它们与SQLServer之间有什么关系? (2)SQLServer2019与同类数据库系统相比具有哪些亮点? 3.2　SQL Server 2019 的新增功能及版本 .................................................................................. .................... .................................................................................. .................... 【案例3-2】 全球数据量急剧增加需要快速处理。据全球权威IT研究与咨询机 构Gartner统计,未来十年的数据量将增长40多倍。互联网数据中心(InternetData Center,IDC)的研究报告称中国数据增长最显著。面对庞杂的数据处理,SQLServer 是世界上应用最广泛的关系网络数据库管理系统(DBMS),微软最新的SQLServer 2019可帮助企事业更好地适应快速增长的业务需求。 3.2.1 SQL Server 2019 的新增功能 SQLServer2019为SQLServer引入了大数据群集,而且还为SQLServer数据库 引擎、SQLServerAnalysisServices、SQLServer机器学习服务、Linux上的SQLServer 和SQLServerMasterDataServices提供了附加功能和改进。 1.可缩放的大数据解决方案 部署SQLServer、Spark和在Kubernetes上运行的HDFS容器的可缩放群集;在 Transact-SQL或Spark中读取、写入和处理大数据;通过大容量大数据轻松合并和分析 高价值关系数据;查询外部数据源;在由SQLServer管理的HDFS中存储大数据;通过 群集查询多个外部数据源的数据;将数据用于AI、机器学习和其他分析任务;在大数据群 集中部署和运行应用程序;SQLServer主实例数据库使用AlwaysOn可用性组等。 2.数据库引擎安全 具有安全Enclave的AlwaysEncrypted;暂停和恢复透明数据加密(TDE)的初始扫 描;SQLServer配置管理器中的证书管理。 3.图形 在图形数据库中,在边缘约束上定义级联删除操作;可以使用MATCH 内的 SHORTEST_PATH 来查找图中任意2个结点之间的最短路径,或执行任意长度遍历; 已分区表和已分区索引的数据被划分为多个单元,这些单元可以跨图形数据库中的多个 文件组分散;在图形匹配查询中使用派生表或视图别名。 4.索引 在SQLServer数据库引擎内启用优化,有助于提高索引中高并发插入的吞吐量。 第◆3 章　SQL Server 2019 基础8 3 此选项旨在用于易发生最后一页插入争用的索引,常见于有顺序键(如标识列、序列或日 期/时间列)的索引;联机聚集列存储索引生成和重新生成;可恢复联机行存储索引生成。 5.内存中数据库 SQLServer数据库引擎的新功能,可以在需要时直接访问位于永久性内存(PMEM) 设备上数据库文件中的数据库页;SQLServer2019(15.x)引入了属于内存数据库功能系 列的新功能,即内存优化tempdb元数据,它可有效消除此瓶颈,并为tempdb繁重的工作 负荷解锁新的可伸缩性级别。在SQLServer2019(15.x)中,管理临时表元数据时所涉及 的系统表可以移动到无闩锁的非持久内存优化表中。 6.Unicode支持 支持使用UTF-8字符进行导入和导出编码,并用作字符串数据的数据库级别或列 级别排序规则。这支持将应用程序扩展到全球范围,其中提供全球多语言数据库应用程 序和服务的要求对于满足客户需求和特定市场规范至关重要。 7.PolyBase 外部表列名现可用于查询SQLServer、Oracle、Teradata、MongoDB 和ODBC 数据 源。外部表支持UTF-8字符。 此外,SQLServer2019在性能监视、语言扩展、空间、性 能、可用性组、设置、错误消息、Linux 上的SQLServer、SQL Server 机器学习服务、Master Data Services、Analysis Services等方面均有新增功能或更新。 3.2.2 SQL Server 2019 版本及对应功能 SQLServer2019共有5个版本,主要包括Enterprise(企业版)、Standard(标准版)、 Web(网站版)、Developer(开发人员版)和Express(精简版),后两个可免费下载使用。其 版本及对应功能见表3-2。 表3-2 SQLServer2019的主要版本及对应功能 版 本主要功能 Enterprise 提供了全面的高端数据中心功能,具有快速的性能和无限的虚拟化功能,以及端到 端的商业智能———为关键任务的工作负载和终端用户访问数据提供高水平服务 Standard 为部门和小型组织提供基本的数据管理和商业智能数据库,以运行其应用程序,并 支持用于现场和云的通用开发工具,使数据库能够以最少的IT资源进行有效管理 Web 是一项总拥有成本较低的选择,可针对从小规模到大规模的Web资产等内容提供 可伸缩性、经济性和可管理性的能力 Developer 允许开发人员在SQLServer之上构建任何类型的应用程序。它包含企业版的所有 功能,但被授权作为开发和测试系统使用,而不是作为生产服务器使用。SQL ServerDeveloper是构建和测试应用程序的人员的理想选择 ...................... ........ ...................... .......... .. .. .. 知识拓展 更多新增功能 8 4 ◆数据库原理及应用与实践(第4 版)——基于SQL Server 2019 续表 版 本主要功能 Express 精简版是入门级的免费数据库,是学习和构建桌面和小型服务器数据驱动应用程序 的理想选择。对于构建客户端应用程序的独立软件供应商、开发人员和爱好者来 说,这是最好的选择。如果用户需要更高级的数据库功能,则可以无缝地将SQL ServerExpress 升级到其他更高级的SQL Server 版本。SQL ServerExpress LocalDB是一个轻量级的Express版本,它具有所有可编程特性,以用户模式运行, 具有快速、零配置安装和简短的先决条件列表 讨论思考 (1)SQLServer2019的新增功能主要包含哪些方面? (2)SQLServer2019的版本有哪几种? 说明各版本的功能差异? 3.3　SQL Server 2019 体系结构和文件 3.3.1 SQL Server 2019 的体系结构概述 1.客户机/服务器体系结构 SQLServer2019的客户机/服务器(C/S)体系结构主要体现在:由客户机负责与用 户进行交互和数据显示,服务器负责数据的存取、调用和管理,客户机向服务器发出各种 操作请求(语句命令或界面菜单操作指令),服务器验证权限后根据用户请求处理数据, 并将结果返回客户机,如图3-1所示。 图3-1 SQL客户机/服务器体系结构 2.数据库的三级模式结构 SQLServer2019支持数据库的三级模式结构,其中外模式对应视图,模式对应基本 表,内模式对应存储文件,如图3-2所示。 1)基本表 基本表(BaseTable)也称基表,是实际存储在数据库中的数据表,是独立存在的,并 非由其他表导出。一个基本表对应一个实际存在的关系。关系模型中数据(记录)为基 本表的行,属性为列。 2)视图 视图(View)是查看数据的一种方式,是从表或其他视图导出的数据并按需要做成的 ◆ 第 3 章　SQL Server 2019 基础85 图3- 2 数据库的三级模式结构 虚表(如网页中的图书,只是其局部信息)。视图只在刚刚打开的一瞬间,通过定义从对 应的基表中搜集并调用数据,展现给用户的是数据库外模式(可见的部分数据形式)。用 户可以通过视图(如网页)调用数据库中对应基本表的数据。视图以一种逻辑定义形式 保存在数据字典中。当基本表中的数据发生变化时,从视图中查询的数据也将相应地改 变。在第5章进行具体介绍。 视图和基本表的主要区别如下。 (1)视图是已经编译好的SQL 语句,而表不是。 (2)视图没有实际的物理记录,而表有具体数据(记录)。 (3)表是具体的数据结构及内容,视图是可见的窗口。 (4)表只用物理(存储)空间而视图不占用,视图只以逻辑概念(定义)存在,表可以及 时地进行修改,视图只能用创建的语句修改。 (5)表是内模式(存储在计算机中), 视图是外模式。 (6)视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一 些SQL 语句的集合。从安全的角度来说,视图可以避免用户接触数据表,从而不知道表 的结构。 (7)表属于全局模式中的表(结构及数据), 是实表;视图属于局部模式的表(部分数 据), ( 是虚表。 8)视图的建立和删除只影响视图本身,不影响对应的基本表。 3)存储文件 存储文件是数据库内模式(内部存储方式及逻辑结构)的基本单位,其逻辑结构构成 关系数据库的内模式。物理结构(如存取路径及索引)可由需要而定。存储文件的存储 结构对用户很明确,各存储文件与外存设备上的物理文件对应。基本表和存储文件的关 系如下。 (1)每个基本表可以对应一个或几个存储文件(如索引文件)。 (2)每个存储文件可以存放一个或几个基本表。 (3)每个基本表可以有多个索引,索引存放在存储文件中。 4)SQL 用户 SQL 用户,是指利用终端对数据库系统及应用程序的操作者,包括终端用户、数据 库管理员和数据库应用程序员。通常,各种用户可以利用SQL 依其具体使用权限,通 ◆ 86 数据库原理及应用与实践(第 4 版)——基于SQL Server 2019 过网络应用系统的界面对视图和基本表进行业务数据的操作,如网上购物和网银操 作等。 3.SQLServer2019 的体系结构 SQLServer具有大规模处理联机事务、数据仓库和商业智能等许多强大功能,这与 其内部完善的体系结构是密切相关的。SQL Server2019 主要包括数据库引擎(Database Engine)、分析服务(AnalysisServices)、集成服务 IntegrationServices)、报表服务(Reporting ( s)以及主数据服务(Ma ServicesterData Services)等组件,各组件的组成结构如图3- 3 所示 。 用于操作、管理和控制的数据库引擎是整 个系统的主要核心,其他所有组件都与其有着 密不可分的联系。SQLServer数据库引擎有协 议(Protocol)、查询编码与执行引擎(Query 图3- 3 各组件的组成结构 CompilationandExecutionEngine)、存储引擎 (StorageEngine)和SQLOS(UserModeOperatingSystem)4大组件。各客户端提交的 操作指令都与这4个组件交互。SQLServer2019 主要组件之间的关系如图3-4所示。 图3- 4 SQLServer2019 主要组件之间的关系 1)服务器组件 使用SQLServer2019 安装向导的“功能选择”页面,可以选择安装SQLServer服务 器组件。表3-3列出了SQLServer2019 的主要服务器组件。 表3- 3 SQLServer2019 的主要服务器组件 服务器组件 SQLServer数据库引擎 分析服务 主要功能 SQLServer数据库引擎包括引擎、部分工具和“数据库引擎服务” (DQS)服务器,其中,引擎用于存储、处理和保护数据,复制及全文搜 索的核心服务,工具用于管理数据库分析集成中、可访问Hadoop及其 他异类数据源的Polybase集成中的关系数据和XML 数据 分析服务包括一些工具,可用于创建和管理联机分析处理(OLAP)以 及数据挖掘应用程序 ◆ 第 3 章　SQL Server 2019 基础87 续表 服务器组件 报表服务 集成服务 主数据服务 机器学习服务(数据库内) 机器学习服务器(独立) 2)管理工具 主要功能 报表服务包括用于创建、管理和部署表格报表、矩阵报表、图形报表, 以及自由格式报表的服务器和客户端组件。报表服务还是一个可用于 开发报表应用程序的可扩展平台 集成服务是一组图形工具和可编程对象,用于移动、复制和转换数据。 它还包括数据库引擎服务(DQS)的集成服务组件 主数据服务是针对主数据管理的SQLServer解决方案。可以配置 MDS来管理任何领域(产品、客户、账户);主数据服务中可包括层次 结构、各种级别的安全性、事务、数据版本控制和业务规则,以及可用 于管理数据的用于Excel的外接程序 机器学习服务(数据库内)支持使用企业数据源的分布式、可缩放的机 器学习解决方案。支持R语言和Python 机器学习服务器(独立)支持在多个平台上部署分布式、可缩放机器学 习解决方案,并可使用多个企业数据源,包括Linux和Hadoop。支持 R语言和Python 在实际应用中,经常使用SQLServer2019的主要管理工具,见表3-4。 表3- 4 SQLServer2019的主要管理工具 管理工具 SQLServerManagement Studio SQLServer配置管理器 SQLServerProfiler 数据库引擎优化顾问 数据质量客户端 SQLServerDataTools 主要功能 SQLServerManagementStudio是用于访问、配置、管理和开发SQL Server组件的集成环境; SQLServerManagementStudio使各种技术水平的开发人员和管理员 都能使用SQLServer; SQLServerManagementStudio需要单独下载并安装 SQLServer配置管理器为SQLServer服务、服务器协议、客户端协议 和客户端别名提供基本配置管理 SQLServerProfiler提供了一个图形用户界面,用于监视数据库引擎 实例或分析服务实例 数据库引擎优化顾问可以协助创建索引、索引视图和分区的最佳组合 提供了一个非常简单和直观的图形用户界面,用于连接到DQS数据 库并执行数据清理操作。它还允许用户集中监视在数据清理操作过 程中执行的各项活动 SQLServerDataTools提供IDE以便为以下商业智能组件生成解决 方案:分析服务、报表服务和集成服务(以前称作BusinesInteligence DlopmentStudio);SQLS(e) (v) (e) erverDataTools还包含数据库项目,为数据库开发人员提供集 成环境,以便在VisualStudio内为任何SQLServer平台(包括本地和 外部)执行其所有数据库设计工作。数据库开发人员可以使用Visual Studio中功能增强的服务器资源管理器,轻松创建或编辑数据库对象 和数据或执行查询 ◆ 88 数据库原理及应用与实践(第 4 版)——基于SQL Server 2019 续表 管理工具 主要功能 安装用于客户端和服务器之间通信的组件,以及用于DB-Library、 连接组件 ODBC 和OLEDB 的网络库 4. 数据库存储结构及文件种类 1)数据库的存储结构 数据库的存储结构包括数据库的逻辑结构和物理结构两种。 (1)数据库的逻辑结构。表示数据库中各数据之间的逻辑关系,数据库由多个用户 界面可视对象构成,主要包括的数据库对象有数据表、视图、约束、规则、默认、索引等。 (2)数据库的物理结构。数据库中数据的存储方式和方法(存储路径和索引方式), 主要描述数据存储的实际位置,对应一系列的物理文件,一个数据库由一个或多个文件 组成 2 。 )数据库文件 常用的数据库文件主要有主要数据文件、次要数据文件、事务日志文件三种。 (1)主要数据文件。数据库的起点,指向数据库中文件的其他部分,记录数据库所拥 有的文件指针。每个数据库有且只有一个主要数据文件,默认扩展名为mdf。 (2)次要数据文件。次要数据文件也称为辅助数据文件,包含除主要数据文件外的 所有数据文件。有些数据库可能无次要数据文件,而有些数据库可能有多个,不是数据 库必需的文件,默认扩展名是ndf。 (3)事务日志文件。事务日志文件简称日志文件,是包含用于恢复数据库所需的所 有操作日志信息的文件。每个数据库必须至少有一个日志文件,默认扩展名是ldf。 使用这些扩展名有助于标识文件的用途,但SQLServer不强制使用mdf、ndf和ldf 作为文件扩展名。数据库文件组织案例如图3-5所示。 3)数据库文件组 为了便于管理和分配数据,SQLServer将多个数据库文件组成一个组。数据库文件 组是数据文件的逻辑组合。主要包括以下3类。 (1)主文件组。主文件组包含主要数据文件和未指明组的其他文件。如在创建数据 库时,未指定其他数据文件所属的文件组。数据库的所有系统表都被分配到(包含在)主 文件组中,当主文件组的存储空间用完之后,将无法向系统表中添加新的目录信息,一个 数据库有一个主文件组。 (2)次文件组。次文件组也称用户自定义文件组,是用户首次创建或修改数据库时 自定义的,其目的在于数据分配,以提高数据表的读写效率。 (3)默认文件组。各数据库都有一个被指定的默认文件组。若在数据库中创建对象 时没指定其所属的文件组,则将被分配给默认文件组。 数据库文件和文件组遵循的规则:一个文件或文件组只能被一个数据库使用;一个 文件只能属于一个文件组;日志文件不能属于任何文件组。 第◆3 章　SQL Server 2019 基础8 9 图3-5 数据库文件组织案例 注意:为了提高使用效率,使用数据文件和文件组应注意以下4点。 (1)在创建数据库时,需要考虑数据文件可能会出现自动增长的情况,应当设置上 限,以免占满磁盘空间。 (2)主文件组可以容纳各系统表。当容量不足时,后更新的数据可能无法添加到系 统表中,数据库也可能无法进行追加或修改等操作。 (3)建议将频繁查询或频繁修改的文件分放在不同的文件组。 (4)将索引、大型的文本文件、图像文件放到专门的文件组里。 3.3.2 SQL Server 数据库的种类及文件 1.SQLServer数据库的种类和特点 数据库对象,是指数据库中的数据在逻辑上组成一系列对象(数据库的组成部分)。 当一个用户连接数据库后,所看到的是逻辑对象,而不是物理的数据库文件。例如在“对 象资源管理器”中可以查看的(数据)表、索引、视图等。 SQLServer2019数据库对象的类型如图3-6所示。 数据库是存放各种对象(表、视图、约束、规则、索引等)的逻辑实体。逻辑上表现(界 面中看到的)为数据库对象,物理上表现为数据库文件(主要数据文件、次要数据文件或 事务日志文件)。 在SQLServer实例中,数据库被分为系统数据库、用户数据库和示例数据库3 大类。 ◆ 90 数据库原理及应用与实践(第 4 版)——基于SQL Server 2019 图3- 6 SQLServer2019 数据库对象的类型 1)系统数据库 系统数据库,是指随着安装程序一起安装,用于协助SQLServer2019 系统共同完成 管理操作的数据库,它们是SQLServer2019 运行的基础。系统数据库存储有关SQL Server的系统信息,它们是SQLServer2019 管理数据库的依据。如果系统数据库遭到 破坏,SQLServer2019 将不能正常启动。 在安装SQLServer2019 时,将创建master数据库、model数据库、msdb 数据库、 resource数据库和tempdb 数据库共5个系统数据库。这些数据库各司其职,其功能见 表3-5。 表3- 5 SQLServer2019 的系统数据库 系统数据库 master数据库 model数据库 msdb 数据库 resource数据库 tempdb 数据库 主要功能 记录SQLServer实例的所有系统级信息 用作SQLServer实例上创建的所有数据库的模板。对model数据库进行的修改 (如数据库大小、排序规则、恢复模式和其他数据库选项)将应用于以后创建的所 有数据库 用于SQLServer代理计划警报和作业 一个只读数据库,包含SQLServer包括的系统对象。系统对象在物理上保留在 resource数据库中,但在逻辑上显示在每个数据库的sys架构中 一个工作空间,用于保存临时对象或中间结果集 (1)master数据库。它记录SQLServer系统的所有系统级信息。包括实例范围的 元数据(例如登录账户)、端点、连接服务器和系统配置设置。在SQLServer中,系统对 象不再存储在master数据库中,而是存储在resource数据库中。此外,master数据库还 ◆ 第 3 章　SQL Server 2019 基础91 记录了所有其他数据库的存在、数据库文件的位置,以及SQLServer的初始化信息。因 此,如果SQLServermaster数据库不可用,则无法启动。 (2)model数据库。用于在SQLServer实例上创建的所有数据库的模板。因为 SQLServer每次启动时都会创建tempdb数据库;model数据库的全部内容(包括数据库 选项)都会被复制到新的数据库中;启动期间,也可使用model数据库的某些设置创建新 的tempdb 。因此,model数据库必须始终存在于SQLServer系统中。 (3)msdb数据库。代理使用msdb数据库来计划警报和作业,SQLServerManagement Studio、ServiceBroker和数据库邮件等其他功能也要使用该数据库。 例如,SQLServer在msdb的表中自动保留一份完整的联机备份和还原历史记录。 这些信息包括执行备份一方的名称、备份时间和用来存储备份的设备或文件。SQL ServerManagementStudio使用这些信息来提出计划,还原数据库和应用任何事务日志 备份。将会记录有关所有数据库的备份事件,即使它们是由自定义应用程序或第三方工 具创建的。例如,如果使用调用SQLServer管理对象(SMO)的MicrosoftVisualBasic 应用程序执行备份操作,则事件将记录在msdb系统表、MicrosoftWindows应用程序日 志和SQLServer错误日志中。为了保护存储在msdb中的信息,建议将msdb事务日志 放在容错存储区中。 (4)resource数据库。resource数据库为只读数据库,包含SQLServer中的所有系 统对象。SQLServer系统对象(如sysobjects)在物理上保留在resource数据库中,但在 逻辑上却显示在每个数据库的sys架构中(.) 。resource数据库不包含用户数据或用户元 数据。 suclytrsuc. sqssersuc.d reore数据库的物理文件名为msqssemeoremdf和mlytmeorelf。 这些文件位于:\ProgramFiles\MicrosoftSQLServer\MSSQL. \MSSQL\Binn\,不应移动。每个SQLServer实例都具有一个(也是 唯一的一个)(_) 关联的msqlsystemresource.mdf文件,并且实例间不共享此文件。 (5)tempdb数据库。tempdb系统数据库是一个全局资源,可供连接到SQLServer 实例或SQL数据库的所有用户使用。tempdb用于保留。 ①显式创建的临时用户对象。例如,全局或局部临时表及索引、临时存储过程、表变 量、表值函数返回的表或游标。 ②由数据库引擎创建的内部对象。其中包括:用于存储假脱机、游标、排序和临时 大型对象(LOB)存储的中间结果的工作表;用于哈希连接或哈希聚合操作的工作文件; 用于创建或重新生成索引等操作(如果指定了SORT_IN_TEMPDB)的中间排序结果,或 者某些GROUPBY 、ORDERBY或UNION查询的中间排序结果。 ③版本存储区,是数据页的集合,它包含支持使用行版本控制的功能所需的数据行。 tempdb中的操作是最小日志记录操作,以便回滚事务。每次启动时都会重新创建 tempdb数据库,从而在系统启动时总是具有一个干净的数据库副本。在断开连接时会 自动删除临时表和存储过程,并且在系统关闭后没有活动连接。因此,empdb中不会有 t 任何内容从一个SQLServer会话保存到另一个会话。不允许对tempdb数据库进行备 份和还原操作。 ◆ 92 数据库原理及应用与实践(第 4 版)——基于SQL Server 2019 2)用户数据库 用户数据库,是指由用户建立并使用的数据库,用于存储用户使用的数据信息。 用户数据库由用户建立,且由永久存储表和索引等数据库对象的磁盘空间构成,空 间被分配在操作系统文件上。系统数据库与用户数据库的结构如图3-7所示。用户数据 库和系统数据库一样,也被划分成许多逻辑页,通过指定数据库ID 、文件ID 和页号,可引 用任何一页。当扩大文件时,新空间被追加到文件末尾。 图3- 7 系统数据库与用户数据库的结构 3)示例数据库 示例数据库是一种实用的学习数据库的范例,安装SQLServer2019 时,默认情况下 不会自动安装示例数据库,需要单独下载安装和设置。 2. 数据库逻辑组件 数据库(空间)的存储(安排), 实际上是按物理方式在磁盘上以多个文件方式实现 的。用户使用数据库时调用的主要是逻辑组件,如图3-8所示。 图3- 8 用户使用数据库时调用主要的逻辑组件 讨论思考 (1)解释SQLServer2019 的体系结构。 (2)SQLServer数据库文件的种类具体有哪些? 第◆3 章　SQL Server 2019 基础9 3 (3)SQLServer系统数据库有哪些? 其作用分别是什么? 3.4　数据库语句使用规则和特点 .................................................................................. .................... .................................................................................. .................... 【案例3-3】 数据库语句使用规则特别重要。用于业务数据处理与管理等,有两 种常用的操作方式:一是通过SSMS(SQLServerManagementStudio)的界面菜单方 式进行操作;二是利用SQL 语句及扩展的事务-结构化查询语言T-SQL(Transact StructuredQueryLanguage,Transact-SQL)方式。T-SQL 是SQLServer的核心组 件,在数据处理与管理等常用操作语句中,其使用规则极为重要,特别是在动态数据处 理及系统运行中更为常用。 3.4.1 数据库语句的种类及特点 1.数据库语句的种类及功能 根据数据库的基本功能,将常用操作命令语句主要概括为数据定义语言(Data DefinitionLanguage,DDL)、数据操作语言(DataManipulationLanguage,DML)、数据控 制语言(DataControlLanguage,DCL)、事务管理语言(TransactManagementLanguage, TML)和其他附加的语言等5类,具体功能特点及操作和应用将以第4章为重点在后续 内容中进行介绍。 (1)数据定义语言(DDL)。SQLServer的功能非常强大且其性能优异高效,其中的 数据定义语言的功能包括对数据库、表(数据表)、视图、索引等操作对象的定义(建立)和 删除(撤销)等,见表3-6。具体操作将陆续进行介绍。 表3-6 SQLServer常用的数据定义语言 操作对象 操作方式 创 建修 改删 除 数据库GREATEDATABASE ALTERDATABASE DROPDATABASE (数据)表CREATETABLE ALTERTABLE DROPTABLE 视图CREATEVIEW ALTERVIEW DROPVIEW 索引CREATEINDEX DROPINDEX (2)数据操作语言(DML)。主要功能包括插入数据INSERT、更新修改数据 UPDATE、删除数据DELETE和数据查询SELECT等,具体操作及用法将在4.3节中进 行介绍。 (3)数据控制语言(DCL)。为了确保数据库的安全,需要对用户使用表中的数据的 权限进行管理和控制。数据控制语言主要用于实现对数据库进行安全管理和权限管理 等控制,如GRAMT(赋予权限)、DENY(禁止赋予的权限)、REVOKE(收回权限)等 9 4 ◆数据库原理及应用与实践(第4 版)——基于SQL Server 2019 语句。 (4)事务管理语言(TML)。主要用于事务管理操作。如将资金从一个账户转账到 另一个账户。可用COMMIT语句提交事务,也可用ROLLBACK语句撤销。 (5)其他附加的语言。主要用于辅助命令语句的操作、标识、理解和使用,包括标识 符、变量、常量、运算符、表达式、数据类型、函数、流程控制、错误处理、注释等。 2.数据库T-SQL的主要特点 事务-结构化查询语言T-SQL,实际是SQL 在MicrosoftSQLServer上的扩展,是 用于数据处理应用程序与同SQLServer交互的主要语言。 T-SQL的主要特点,可以概括如下。 (1)多功能综合统一。交互式查询语言功能强大,简单易学,而且集数据定义、数据 操作、数据控制、事务管理语言和附加语言元素为一体。 (2)高度非过程化。用SQL进行数据操作时,只要提出“做什么”,而无须指明具体 “怎么做”。选择存取路径和SQL语句的操作过程由系统自动完成,极大地减轻了用户 负担,提高了数据独立性。 (3)面向集合的操作方式。SQL操作的对象和结果都是集合(关系),用关系(二维 数据表结构方式)表示数据处理操作更快捷、方便。 (4)一种语法,两种使用方式。SQL 既是自含式语言———在终端键盘上直接输入 SQL命令对数据库进行操作,又是嵌入式语言———将SQL语句嵌入到高级语言中进行 数据处理,如在C、C++、C#、Java、PHP等编写的程序中使用SQL语句,且语法结构基 本一致。 (5)语言简洁,易学易用。SQL极为接近人的思维习惯,而且常用操作的核心功能 (建立、插入、修改、更新、删除、查询和权限管理等)语句比较少,易于理解和掌握。 3.4.2 数据库语法规则及注释语句 1.命令语句使用的语法规则 结构化查询语言SQL是用于管理、控制和操作数据库的标准计算机语言,主要功能 包括:创建(定义)、修改、删除数据库及数据表,存取、插入、更新、删除数据,在数据库中 创建索引及视图、设置表、建立存储过程,以及授予视图和存储过程的权限等。 SQL及T-SQL主要用于SQLServer提供的数据定义和数据操作的具体应用、管理 控制、调用数据库对象和数据处理等。 为了便于实际操作应用语句,在书写使用中的语法规则如下。 (1)“< >”(尖括号)中的内容为“必选项”,不可缺省。 (2)“[]”(方括号)中的内容为“可选项”,省略时系统取默认值。 (3)“|”(同或/)表示相邻前后两项“二者选其一”,只能取一项。 (4)“…”表示其中的内容可以重复书写,且各项之间须用逗号隔开。 (5)一条较长语句可以分成多行书写且以“;”(称为换行符或改行符,也可以使用回 第◆3 章　SQL Server 2019 基础9 5 车操作)结尾,但是,在同一行不允许写多条语句。 (6)在一个关键字的中间不能加入空格或换行符。 (7)在T-SQL中,保留关键字是SQL中系统预留或事先定义好的关键字,命令和语 句的写书不区分大小写。关键字不能缩写也不能分行。 (8)在书写各种SQL 命令时,所涉及的标点符号,如括号、逗号、分号、圆点(英文句 号)等都应是英文半角,若写成中文符号或全角符号,将会在执行命令时出错。 说明 (1)上述语法规则(1)至(4)中的有关符号,只是用于与读者交流的书写“印刷符”,在 实际SQLServer系统操作中这些符号不可输入。 (2)SQL语句不区分大小写,也可以用前4个字母缩写,但是为了便于阅读和维护 不提倡缩写,通常在编写SQL时,还是尽量统一保留关键字的大小写。例如,以大写字 母的形式写保留关键字,以小写字母的形式写表或列名。另外,根据使用数据库的不同, 在部分数据库中区分表或列名的大小写。 2.注释语句 在T-SQL程序中,注释语句主要用于对程序语句的解释说明并增加阅读性,有助于 对源程序语句的理解和修改维护,系统对注释语句不予以执行。当在查询分析器中使用 注释语句时,相应被注释的部分变为蓝绿色。注释语句包括多行注释语句和单行注释语 句两种。 (1)多行注释语句。多行注释语句也称为块注释语句,通常放在程序(块)的前面,用 于对程序功能、特性和注意事项等方面的说明,以/*开头并以*/结束。例如: /* 以下为数据修改程序 请注意修改的具体条件及确认*/ (2)单行注释语句。单行注释语句也称为行注释语句,通常放在一行语句的后面,用 于对本行语句的具体说明,是以两个减号(--)开始的若干字符。例如: -- 定义(声明)局部变量 -- 为局部变量赋初始值 讨论思考 (1)T-SQL常用的语法规则是什么? (2)T-SQL常用操作语言的种类具体有哪些? (3)T-SQL的特点及注释语句是什么? 3.5　实验　SQL Server 2019 安装及操作界面 本节主要介绍SQLServer2019的安装、配置、登录、操作界面和功能操作。 9 6 ◆数据库原理及应用与实践(第4 版)——基于SQL Server 2019 3.5.1 实验目的 (1)掌握SQLServer2019的安装或升级方法及过程。 (2)理解SQLServer2019服务器配置和登录的方法。 (3)掌握SQLServer2019的常用操作界面及功能。 3.5.2 实验要求及安排 1.运行环境 运行环境:SQLServer2019RTM 官方版。 操作系统:Windows10。 2.学时安排 学时安排:建议2~3学时(可以安排课后补充练习)。 3.5.3 实验内容及步骤 1.SQLServer2019的安装与升级 1)常用的全新安装过程 根据常用SQLServer2019安装向导进行安装。SQLServer安装向导提供了一个 用于安装所有SQLServer组件的功能树,便于根据需要分别安装这些组件。 还可采用安装SQLServer2019的其他方法。 (1)从命令提示符安装SQLServer2019。 (2)使用配置文件安装SQLServer2019。 (3)使用SysPrep安装SQLServer2019。 (4)创建新的SQLServer故障转移群集(安装程序)。 (5)使用安装向导(安装程序)升级到SQLServer2019。 SQLServer2019常用的全新安装步骤如下。 (1)下载或插入SQLServer2019安装软件,然后双击根目录中的Setup.exe,出现安 装界面。 (2)这里要创建新的SQLServer安装,单击左侧导航栏中的“安装”,然后单击“全新 SQLServer独立安装或向现有安装添加功能”,如图3-9所示,进入安装过程。 (3)进入“Microsoft更新”界面。检查完成后,单击“下一步”按钮。 (4)进入“安装规则”界面。进行安装程序文件和安装规则检查的结果如图3-10所 示,单击“下一步”按钮。 (5)进入“安装类型”选择界面。选择默认的“执行SQLServer2019的全新安装” 后,单击“下一步”按钮。 ◆ 第 3 章　SQL Server 2019 基础97 图3- 9 SQLServer安装中心界面 图3-10 “安装规则”界面 (6)进入“产品密钥”界面。选择某个选项以安装免费的SQLServer版本,或者具有 PID 密钥的生产版本。选择好后,单击“下一步”按钮。 (7)进入“许可条款”界面。勾选“我接受许可条款”复选框,然后单击“下一步”按钮。 为了帮助改进SQLServer,还可启用功能使用情况选项并将报告发送给Microsoft。 (8)进入“功能选择”界面。勾选“数据库引擎服务”复选框,如图3-11 所示,单击“下 一步”按钮。 98 ◆数据库原理及应用与实践(第 4 版)——基于SQL Server 2019 图3-11 “ 功能选择” 界面 ...................... .. (9) 进入“ 实例配置” 界面。在此选择“ 默认实例”( 一台 .. ...... .. 知识拓展 默认实例和命 名实例 ........ .. 服务器只能有一个默认实例) 的命名实例名称) 后, 单击“ 下一步” 按钮。 (10 ) 进入“ 服务器配置” 或“ 命名实例”( 需要输入有效 界面。在此保持默认配置, 单击 .. .. .. .. .. .. .. .. .. .. .. .. “ 下一步” 按钮。 (11 ) 进入“ 数据库引擎配置” 界面。选择使用混合模式, 输入密码后单击下部的“ 添 加当前用户” 按钮, 如图3-12 所示, 单击“ 下一步” 按钮。还可利用其他选项卡进行更多的 设置。 图3-12 “ 数据库引擎配置” 界面