SQL Server2000培训简明教程

SQL Server2000培训简明教程 第4章 SQL Server 2000概述 4.1 SQL Server 2000的体系结构 4.1.1 SQL Server 2000的客户/服务器结构 SQL Server 2000采用客户/服务器计算模型，即中央服务器用来存储数据库，该服务器可以被多台客户机访问，数据库应用的处理过程分布在客户机和服务器上。客户/服务器计算模型分为两层的客户/服务器结构和多层的客户/服务器结构。在两层的客户/服务器系统中，客户机通过网络与运行SQL Server 2000实例的服务器相连，客户机用来完成数据表示和大部分业务逻辑的实现，服务器完成数据的存储，这种客户机被称为“胖客户机”(thick client)。在多层的客户/服务器系统中，至少要经过三个处理层，第一层是客户机，它只负责数据的表示;第二层是业务逻辑服务器，负责业务逻辑的实现，所有的客户机都可以对它进行访问;第三层是数据库。这种结构中的客户机被称为“瘦客户机”(thin client)。Internet 应用就是三层结构的一个典型例子。 数据库系统采用客户/服务器结构的好处在于: (1)数据集中存储。数据集中存储在服务器上，而不是分开存储在各客户机上，使所有用户都可以访问到相同的数据。 (2)业务逻辑和安全规则可以在服务器上定义一次，而后被所有的客户使用。 (3)关系数据库服务器仅返回应用程序所需要的数据，这样可以减少网络流量。 (4)节省硬件开销，因为数据都存储到服务器上，不需在客户机上存储数据，所以客户机硬件不需要具备存储和处理大量数据的能力，同样，服务器不需要具备数据表示的功能。 (5)因为数据集中存储在服务器上，所以备份和恢复起来很容易。 4.1.2 SQL Server 2000的查询语言——交互式SQL 查询语言是数据库的重要组成部分。许多关系数据库系统拥有作为高级查询语言的结构化查询语言(Structure Query Language，简称SQL)。交互式 SQL(Transact SQL，简称T-SQL)是SQL Server 的查询语言，它与 ANSI92 SQL 标准 excel标准偏差excel标准偏差函数exl标准差函数国标检验抽样标准表免费下载红头文件格式标准下载 兼容，并对其进行了扩张。 如果希望开发的程序有更好的可移植性，那么应尽量使用标准的ANSI92 SQL，否则，就应考虑使用T-SQL，因为T-SQL可以带来更好的性能。 T-SQL 提供如下的命令: (1)创建和管理数据库对象。 (2)访问和修改数据。 (3)数据聚合。 (4)管理安全性和权限。 4.2 SQL Server 2000的安装 4.2.1 SQL Server 2000的系统需求 安装SQL Server 2000对系统硬件和软件有一定的要求，软件和硬件的不兼容性可能导致失败。所以在安装之前必须弄清SQL Server 2000对软件和硬件的要求。 4.2.1.1 硬件要求 计算机 Intel 或兼容机，Pentium 166 MHz 或更高。 内存 企业版:至少64 MB，建议128 MB 或更多。 标准版:至少64 MB。 个人版:Windows 2000上至少64 MB，其他操作系统上至少 32 MB。 开发版:至少64 MB。 硬盘 SQL Server 数据库组件:95,270 MB，一般为250 MB。 Analysis Services:至少50 MB，一般为130 MB。 English Query:80 MB;仅Desktop Engine:44 MB。 监视器 VGA或更高分辨率，SQL Server 图形工具要求 800×600或更高分辨率。 CD-ROM 需要。 4.2.1.2 软件要求 1(操作系统的要求 企业版:Microsoft Windows NT Server 4.0、Microsoft Windows NT Server 4.0 企业版、Windows 2000 Server、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。 标准版:Microsoft Windows NT Server 4.0、Windows 2000 Server、Microsoft Windows NT Server 企业版、Windows 2000 Advanced Server 和 Windows 2000 Data Center Server。 个人版:Microsoft Windows Me、Windows 98、Windows NT Workstation 4.0、Windows 2000 Professional、Microsoft Windows NT Server 4.0、Windows 2000 Server 和所有更高级的 Windows 操作系统。 开发版:Microsoft Windows NT Workstation 4.0、Windows 2000 Professional 和所有其他 Windows NT和Windows 2000 操作系统。 仅客户端工具:Microsoft Windows NT 4.0、Windows 2000(所有版本)、Windows Me 和 Windows 98。 仅连接:Microsoft Windows NT 4.0、Windows 2000(所有版本)、Windows Me、Windows 98 和 Windows 95。 2(Internet要求 Microsoft SQL Server 2000所有安装都需要 Microsoft Internet Explorer 5.0。Microsoft 管理控制台(MMC)和HTML帮助也需要 Microsoft Internet Explorer 5.0。最小安装已足够，而且 Internet Explorer 不必是默认浏览器。 Internet Explorer 5.0 要求的例外情况:如果使用“仅连接”选项而且不连接到要求加密的服务器，则带 Service Pack 2 的 Microsoft Internet Explorer 4.0就足够了。 如果编写 XML 应用程序，则还需要安装微软Internet 信息服务器(IIS)。 3(网络软件要求 Microsoft Windows NT、Windows 2000、Windows Me、Windows 98 和 Windows 95 都具有内置网络软件。只有在使用 Banyan VINES 或 AppleTalk ADSP 时，才需要其他网络软件。Novel NetWare IPX/SPX 客户端支持由 Windows Networking 的 NWLink 协议提供。 4(支持的客户端 Microsoft SQL Server 2000 支持以下客户端:Windows NT Workstation、Windows 2000 Professional、Windows 98、Windows 95、Apple Macintosh、OS/2 和 UNIX。Macintosh、OS/2 和UNIX不支持 SQL Server 图形工具，而要求来自第三方供应商的 ODBC 客户端软件。 5(其他考虑事项 下列Microsoft产品要求有Service Release或Service Pack才能正确操作SQL Server 2000。 Microsoft Access 2000 要求安装 Microsoft Office 2000 Service Release 1或Access 2000 SR1 才能正确操作 SQL Server 2000。如果运行Access 2000 的早期版本，则无法在 SQL Server 2000 上测试自动数据处理(ADP)应用程序，无法访问数据库关系图、存储过程、表设计或视图设计。 在运行 Microsoft Visual Studio 6.0 时，无法访问SQL Server 2000 中的数据库关系图、存储过程、表设计或视图设计。Visual Studio 6.0 Service Pack 4 使您可以更改数据库关系图、存储过程、表设计或视图设计，但是无法保存它们。将来的 Visual Studio Service Pack允许有限的保存更改能力。 SQL Server 2000 工具无法访问使用 Visual Studio 6.0 中的设计工具保存的数据库关系图，直到修改了数据库中的 dtproperties 表为止。 4.2.2 SQL Server 2000的安装版本 SQL Server 2000的版本包括企业版、标准版、个人版、开发版、Windows CE版和评估版。 (1)SQL Server 2000企业版:作为生产数据库服务器使用。支持 SQL Server 2000中的所有可用功能，并可根据支持最大的 Web 站点和企业联机事务处理(OLTP)及数据仓库系统所需的性能水平进行伸缩。 (2)SQL Server 2000标准版:作为小工作组或部门的数据库服务器使用。 (3)SQL Server 2000个人版:供移动的用户使用，这些用户有时从网络上断开，但所运行的应用程序需要 SQL Server 数据存储。在客户端计算机上运行需要本地 SQL Server 数据存储的独立应用程序时也使用个人版。 (4)SQL Server 2000开发版:供程序员用来开发将 SQL Server 2000 用作数据存储的应用程序。虽然开发版支持企业版的所有功能，使开发人员能够编写和测试可使用这些功能的应用程序，但是只能将开发版作为开发和测试系统使用，不能作为生产服务器使用。 (5)SQL Server 2000 Windows CE版:使用Microsoft SQL Server 2000 Windows CE 版(SQL Server CE)在Windows CE 设备上进行数据存储。能用任何版本的 SQL Server 2000 复制数据，以使 Windows CE 数据与主数据库保持同步。 (6)SQL Server 2000 企业评估版:可从Web上免费下载的功能完整的版本。 除了这些版本的 SQL Server 2000 之外，SQL Server 2000 Desktop Engine 组件允许应用程 序开发人员用他们的应用程序分发 SQL Server 2000 关系数据库引擎的复本。因为 SQL Server 2000 Desktop Engine 中的数据库引擎的功能与 SQL Server 各版本中的数据库引擎相似，所以 Desktop Engine 数据库的大小不能超过 2 GB。 SQL Server 2000 桌面版和 SQL Server 2000 个人版都有一个并发工作负荷调控器，可在并发执行5个以上的批处理时限制数据库引擎的性能。 4.2.3 SQL Server 2000的安装步骤 安装SQL Server 2000比较简单，其过程与其他Windows产品类似。安装过程及说明如下: (1)将SQL Server 2000的光盘放入光驱 ，如果有自动运行功能，则会出现启动画面，否则直接运行Autorun程序，出现启动画面，如图4-1所示。 图4-1 启动画面 (2)我们选择SQL Server 2000 简体中文个人版，打开如图4-2所示的安装SQL Server 2000个人版窗口，从中选择安装内容。 图4-2 SQL Server 2000个人版 (3)选择 SQL Server 2000 组件，打开如图4-3所示的安装SQL Server 2000 组件窗口。 图4-3 安装组件 在打开的屏幕画面上将出现下面三个选项。 安装数据库服务器:启动 SQL Server 安装程序，其中包含选择安装选项的屏幕。 安装 Analysis Services:在处理OLAP多维数据集的计算机上安装 Analysis Services。 安装 English Query:在运行English Query 应用程序的计算机上安装 English Query。 (4)选择数据库服务器，出现欢迎对话框，按下一步进入如图4-4所示的选择计算机名窗口。 在图4-4中，有以下几种选择: 本地计算机(Local Computer):默认情况下，编辑框中的名称为本地计算机(即正在运行安装程序的计算机)名称。对于本地安装，接受默认值并单击“下一步”按钮。 远程计算机(Remote Computer):输入计算机名称以进行远程安装，或单击“浏览”按 钮以定位远程计算机。 虚拟服务器(Virtual Computer):输入要管理的新的或现有的虚拟 SQL Server 名称。该选项仅当从Windows NT 或 Windows 2000 企业操作系统上检测到 Microsoft 群集服务(MSCS)时才可用。 浏览(Browse):单击“浏览”按钮以定位远程计算机。仅当选定“远程计算机”选项时该按钮才可用。 (5)选择“本地计算机”选项，出现如图4-5所示的安装选择对话框。 图4-4 输入计算机名称 图4-5 安装选择 可以从屏幕上的三个选项中选择一个运行安装程序: 创建新的 SQL Server 实例或安装客户端工具:创建新的SQL Server 2000实例，该实例是默认实例或命名实例。另外，该选项使您可以在除Microsoft Windows 95之外的任何操作系统上使用SQL Server 2000任何版本的光盘来仅安装客户端工具。 对现有的 SQL Server 实例进行升级、删除或添加组件:该选项使您可以对现有的 SQL Server 实例进行升级、删除或添加组件。现有实例包括早期版本(SQL Server 6.5 版和 SQL Server 7.0 版)以及 SQL Server 2000 实例的安装。 高级选项:选择用于群集维护、无值守安装和注册表重建的高级选项。 (6)如果是第一次安装SQL Server ，应该选择创建实例和客户端，这里我们选择这一选项继续。进入用户信息界面，如图4-6所示。 “用户信息”安装框提示您输入姓名和公司名称。这些字段是必须输入的。 在网络上进行安装时，应确保提供的是负责使用或管理该服务器的用户的姓名。 (7)输入名字和公司名称，单击“下一步”按钮。阅读许可协议， 用户在继续安装前应该认真地阅读它们，接受条款单击“是”按钮继续。 (8)选择安装类型，共有3种选择，如图4-7所示。 图4-6 用户信息 图4-7 安装定义 仅客户端工具:仅安装客户端关系数据库管理工具。此选项包含管理 SQL Server 的客户端工具和客户端连接组件。此外，该选项可以选择其他要安装的组件。 服务器和客户端工具:该选项执行安装服务器和客户端工具以创建具有管理能力的关系数据库服务器。选择“服务器和客户端工具”选项将显示所有附加的安装选项。 仅连接:仅安装关系数据库客户端连接组件，包括连接 SQL Server 2000 命名实例所需的MDAC 2.6(Microsoft 数据访问组件)。该选项只提供连接工具，不提供客户端工具或其他组件。 (9)选择服务器和客户端工具，出现如图4-8所示的选择实例名称对话框。选择“默认”选项或自命名一个实例名称。 (10)选择安装类型和路径。 当安装SQL Server 2000企业版、SQL Server 2000标准版或 SQL Server 2000 个人版时，SQL Server 安装程序在“安装类型”对话框中提供三种安装类型。另外，可修改该对话框中的程序和数据文件的安装位置。共有3种类型可供选择，如图4-9所示。 典型:使用默认安装选项安装整个 SQL Server。建议多数用户采用此安装。 最小:安装运行 SQL Server 所需的最小配置。建议使用最小可用磁盘空间的计算机的用户采用此安装。 自定义:安装 SQL Server 且可更改任意或所有默认选项。使用自定义安装选择组件和子组件，或者更改排序规则、服务账户、身份验证或网络库的设置。 图4-8 选择实例名称 图4-9 安装类型和路径 (11)选择典型安装，进入如图4-10所示的服务账户设置窗口。 图4-10 服务账户设置 使用安装程序中的“服务账户”屏幕，将登录账户指派给两个SQL Server 服务:SQL Server 和SQL Server 代理程序。可以使用本地系统账户或域用户账户，并且两个服务可使用同一账户。 默认设置是使用同一服务，且自动启动每个服务。若要使用默认设置，请输入域名密码，然后单击“下一步”。 还可以自定义每个服务的设置。可以为两个服务输入同一个登录账户，也可以为每个服务分别指定账户。以后若要更改“服务账户”屏幕上的选项集，则请运行Windows控制面板上的“服务”应用程序。 选项设置如下: 对每个服务使用同一账户。自动启动 SQL Server 服务。为默认选项。一个账户同时用于 SQL Server 和 SQL Server 代理程序。这些服务在操作系统启动时自动启动。 自定义每个服务的设置。允许对两个服务使用不同的设置。 服务:选择要自定义设置的服务。 SQL Server:选择此选项自定义 Microsoft SQL Server 服务的设置。 SQL Server 代理:选择此选项自定义 Microsoft SQL Server 代理服务的设置。 服务设置:选择需要的服务设置。 使用本地系统账户:本地系统账户不需要设置密码，也没有 Windows NT 4.0 的网络访问权限，同时，它可能限制 SQL Server 安装与其他服务器交互。(说明:在Windows 2000 中，本地系统账户不允许网络访问。) 使用域用户账户:域用户账户使用 Windows 身份验证设置并连接 SQL Server。默认情 况下，将显示当前登录到计算机的域用户账户的账户信息。 用户名:接受或更改域用户名。 密码:输入域密码。 域:接受或更改域名。 自动启动服务:选择此选项后将在操作系统启动时自动启动服务。只有当自定义每个服务的设置时才能使用此选项。 SQL Server 代理服务依赖于 SQL Server 服务，因为只有自动启动 SQL Server 服务才能自动启动 SQL Server 代理服务。 (12)单击“下一步”按钮，出现如图4-11所示的确认开始复制文件对话框，如果不再修改安装设置，则可以开始复制文件，进行安装工作。 (13)最后出现如图4-12所示的结束安装界面。 图4-11 确认开始复制文件 图4-12 安装完成 到此为止，我们已经完成了安装工作，重启系统即可完成安装。 4.3 SQL Server 2000的两个主要工具 4.3.1 SQL Server 2000企业管理器 SQL Server 2000企业管理器(Enterprise Manager)是用于管理企业级SQL Server 2000或者SQL Server 2000对象的方便而实用的图形化工具。 用户可以通过开始菜单或快捷方式启动企业管理器。启动后，界面如图4-13所示。 图4-13 企业管理器启动画面 Microsoft管理控制台(MMC)用以管理 Microsoft Windows 网络中的不同服务器应用程序。SQL Server 企业管理器是 Microsoft SQL Server MMC 的管理单元。 SQL Server 企业管理器是 SQL Server 的主要管理工具，它提供了一个遵从 MMC 标准的用户界面，使用户得以: 定义 SQL Server 实例组。 将个别服务器注册到组中。 为每个已注册的服务器配置所有 SQL Server 选项。 在每个已注册的服务器中创建并管理所有 SQL Server 数据库、对象、登录、用户和权限。 在每个已注册的服务器上定义并执行所有 SQL Server 管理任务。 通过唤醒调用 SQL 查询分析器，交互地设计并测试 SQL 语句、批处理和脚本。 唤醒调用为 SQL Server 定义的各种向导。 在企业管理器的管理中我们将做详细讲解。 4.3.2 SQL 查询分析器 SQL查询分析器(SQL Query Analyzer)是一个图形用户界面，用以交互地设计和测试Transact-SQL语句、批处理和脚本。可以从SQL Server企业管理器中调用SQL查询分析器。 SQL 查询分析器提供: 用于键入 Transact-SQL 语句的自由格式文本编辑器。 在 Transact-SQL 语法中使用不同的颜色，以提高复杂语句的易读性。 对象浏览器和对象搜索工具，可以轻松查找数据库中的对象和对象结构。 模板，可用于加快创建 SQL Server 对象的 Transact-SQL 语句的开发速度。模板是包含创建数据库对象所需的 Transact-SQL 语句基本结构的文件。 用于分析存储过程的交互式调试工具。 以网格或自由格式文本窗口的形式显示结果。 显示计划信息的图形关系图，用以说明内置在 Transact-SQL 语句执行计划中的逻辑步骤。 这使程序员可以确定在性能差的查询中具体是哪一部分使用了大量资源。之后，程序员可以试着采用不同的 方法 快递客服问题件处理详细方法山木方法pdf计算方法pdf华与华方法下载八字理论方法下载 更改查询，使查询使用的资源减到最小同时仍返回正确的数据。 使用索引优化向导分析 Transact-SQL 语句以及它所引用的表，以了解通过添加其他索引是否可以提高查询的性能。 从开始菜单的Microsoft SQL Server 程序组中选择 Query Analyzer 即可启动，如图4-14所示。 图4-14 查询分析器登录界面 在图4-14中的 SQL Server 下拉列表框中选择要登录的SQL Server 服务器，如果列表中没有，可以单击按钮，进行查找服务器。连接上服务器就可以出现如图4-15所示的查询分析器窗口。 图4-15 查询分析器窗口 在图4-15中的右边为查询窗口，在查询窗口中用户可以输入SQL语句，并按F5键运行，或单击工具栏上的按钮将其送到服务器执行，结果将显示在输出窗口中。用户也可以打 开一个含有SQL语句的文件来执行，执行的结果同样显示在输出窗口中，如图4-16所示显示了一个简单的查询执行情况。 图4-16 在查询分析器中执行查询 在查询分析器中，也可以控制显示方式，还可以保存到文件。切换结果，可以单击工具栏上的按钮，并在下拉菜单中选择一种显示结果的方式，如图4-17所示为选择了网格显示方式。 图4-17 以网格方式显示语句执行结果 如果选择窗口下的“消息”选项卡，可以查看当前执行的T-SQL语句所返回的消息，如图4-18所示。 图4-18 当前执行语句返回的消息 查询分析器是一个真正的分析工具，它不仅能执行T-SQL语句，还能对一个查询语句的执行进行分析，给出查询执行计划，为查询优化提供直观的帮助。 如果选择下拉菜单中的显示执行计划，则可以在结果窗口中显示查询计划，如图4-19所示。 图4-19 查看执行计划 在图4-19中用鼠标指向执行计划中的某一步，还可以查看该步的具体细节，如图4-20所示。 图4-20 查看计划某一步的具体细节 第5章 数据库和表的创建 5.1 数据库的创建 5.1.1 数据库的结构 1(数据库文件和文件组 SQL Server 2000用文件来存放数据库，数据库文件有三类。 主数据库文件(Primary):存放数据，每个数据库都必须有一个主数据文件。 其他数据文件(Secondary):存放数据，一个数据库可以没有也可以有多个Secondary文件。 事务日志文件(Transaction Log):存放事务日志，每个数据库必须有一个或多个日志文件。 一般情况下，一个简单的数据库可以只有一个主数据文件和一个日志文件。如果数据库很大，则可以设置多个Secondary文件和日志文件，并将它们放在不同的磁盘上。 默认状态下，数据库文件存放在\MSSQL\data\ 目录 工贸企业有限空间作业目录特种设备作业人员作业种类与目录特种设备作业人员目录1类医疗器械目录高值医用耗材参考目录 下，数据文件名为“数据库名_Data.MDF”，日志文件名为“数据库名_Log.LDF”。数据库的创建者可以在创建时指定其他的路径和文件名，也可以添加Secondary文件和更多的日志文件。 文件组允许多个数据库文件组成一个组，并对它们整体进行管理。比如，可以将三个数据文件(data1.mdf、data2.mdf和data3.mdf)分别创建在三个盘上，这三个文件组成文件组fgroup1，在创建表的时候，就可以指定一个表创建在文件组fgroup1上。这样该表的数据就可以分布在三个盘上，在对该表执行查询时，可以并行操作，大大提高了查询效率。 SQL Server的数据库文件和文件组必须遵循以下规则: (1)一个文件和文件组只能被一个数据库使用。 (2)一个文件只能属于一个文件组。 (3)数据和事务日志不能共存于同一文件或文件组上。 (4)日志文件不能属于文件组。 2(数据库对象 SQL Server 2000数据库中的数据在逻辑上被组织成一系列对象，当一个用户连接到数据库后，他所看到的是这些逻辑对象，而不是物理的数据库文件。 SQL Server 2000中有以下数据库对象:表(Table)、视图(View)、存储过程 (Stored procedures)、触发器(Triggers)、用户自定义数据类型(User-defined data types)、用户自定义函数(User-defined functions)、索引(Indexes)、规则(Constraints)、默认值(Defaults)等。 5.1.2 系统数据库 在创建任何数据库之前，打开企业管理器的“服务器/数据库”目录，可以看到系统中已经有了6个数据库。如图5-1所示。 图5-1 SQL Server 2000中的系统数据库和示例数据库 其中，有4个是SQL Server 2000的系统数据库，它们分别是:master数据库、tempdb数据库、model数据库和msdb数据库。 1(master数据库 master数据库 记录 混凝土 养护记录下载土方回填监理旁站记录免费下载集备记录下载集备记录下载集备记录下载 了SQL Server系统级的信息，包括系统中所有的登录账号、系统配置信息、所有数据库的信息、所有用户数据库的主文件地址等。每个数据库都有属于自己的一组系统表，记录了每个数据库各自的系统信息，这些表在创建数据库时自动产生。为了与用户创建的表相区别，这些表被称为系统表，表名都以“sys”开头。 master数据库中还有很多系统存储过程和扩展存储过程。系统存储过程是预先编译好的程序，所有的系统存储过程的名字都以sp_开头。 2(tempdb数据库 tempdb 数据库用于存放所有连接到系统的用户临时表和临时存储过程以及SQL Server产生的其他临时性的对象。tempdb是SQL Server中负担最重的数据库，因为几乎所有的查询都可能需要使用它。 在SQL Server 关闭时，tempdb数据库中的所有对象都被删除，每次启动SQL Server时，tempdb数据库里面总是空的。 3(model数据库 model数据库是系统所有数据库的模板，这个数据库相当于一个模子，所有在系统中创建的新数据库的内容，在刚创建时都和model数据库完全一样。 如果SQL Server专门用作一类应用，而这类应用都需要某个表，甚至在这个表中都要包括同样的数据，那么就可以在model数据库中创建这样的表，并向表中添加那些公共的数据，以后每一个新创建的数据库中都会自动包含这个表和这些数据。当然，也可以向model数据库中增加其他数据库对象，这些对象都能被以后创建的数据库所继承。 4(msdb数据库 msdb 数据库被SQL Server代理(SQL Server Agent)来安排报警、作业，并记录操作员。 SQL Server在安装时自动创建了另外两个数据库:pubs和northwind，它们是SQL Server的 示例数据库，示例数据库是让读者作为学习工具使用的。 5.1.3 创建数据库 1(使用企业管理器创建数据库 在企业管理器中创建数据库可以按以下步骤操作: 1)选中将要使用的Server，用鼠标右键单击数据库，在弹出的快捷菜单中选择“新( 建数据库”，如图5-2所示。 (2)打开新建数据库对话框的“常规”选项卡，在名称栏中输入数据库的名称，如图 5-3所示。 (3)在图5-3的对话框中选择“数据文件”选项卡，如图5-4所示。在这个对话框中 可以增减数据文件，对每一个数据文件设置以下内容: 文件名:也叫逻辑文件名，以后就以这个名字来代表这个数据文件。 文件存放的路径:也叫物理文件名。 文件初始大小。 文件大小是否有限制，最大是多少。 文件增长方式是以兆字节增长还是以百分比增长，每次增长的幅度。 图5-2 创建新数据库 图5-3 输入数据库名“LWZZ” 图5-4 设置数据库文件、文件大小和增长方式 (4)选择“事务日志”选项卡，指定存放日志文件的位置、初始大小、增长方式。对 -5所示。 每一个日志文件，做和数据文件一样的设置。如图5 (5)设置完成之后，单击“确定”按钮。 以上创建了一个名为LWZZ的数据库，并为它创建了一个主数据库文件和一个日志文件。存放在C盘的\MSSQL\data目录下，初始大小是1M，扩展文件时按10%的幅度增长，没有限制大小。 图5-5 设置日志文件、文件大小和增长方式 2(使用T-SQL语句创建数据库 通过执行以下的语句可以创建Market数据库。 CREATE DATABASE LWZZ ON ( NAME = LWZZ_Data, FILENAME = 'C:\Program Files\Microsoft\MSSQL\data\ LWZZ_Data.MDF', SIZE = 1, FILEGROWTH = 10% ) LOG ON ( NAME = LWZZ_Log, FILENAME = 'C: \Program Files\Microsoft \MSSQL\data\ LWZZ_Log.LDF', SIZE = 5, FILEGROWTH = 10% ) 我们也可以简单地来创建数据库: CREATE DATABASE mytest 由于没有指定主文件名，在默认的情况下，命名主文件为mytest.mdf，日志文件名为mytest_log.log。同时由于按复制Model数据库的方式来创建新的数据库，主文件和日志文件的大小都同Model数据库的主文件和日志文件大小一致，并且可以自由增长。 5.1.4 查看数据库信息 5.1.4.1 使用企业管理器显示数据库信息 (1)在企业管理器的树形目录中选中想要查看的数据库，右边的窗口中就会显示数据库的相关信息，如图5-6所示。 图5-6 企业管理器窗口中显示LWZZ数据库的信息 在图5-6所示的窗口中，显示了LWZZ数据库三方面的信息，包括以下几个方面。 基本信息(数据库):比如数据库拥有者、创建时间、大小、用户个数等。 维护信息(维护):一些备份和维护的信息。 空间使用情况(Spaceallocated):数据和日志文件的空间使用情况。 (2)用鼠标右键单击数据库名，在弹出的快捷菜单中选择Properities命令，打开如图5-7所示的属性窗口来查看数据库信息，在属性窗口中可以更改数据库的设置。 5.1.4.2 用T-SQL语句查看数据库信息 1(使用系统存储过程sp_helpdb 格式如下: sp_helpdb [数据库名] 1)不给出参数，将显示服务器中所有数据库的信息，如图5-8所示。 图5-7 LWZZ数据库的属性对话框 图5-8 查看服务器中所有数据库的信息 (2)显示LWZZ数据库的信息，如图5-9所示。 2(使用系统存储过程sp_databases 格式如下: sp_databases 显示服务器中所有可以使用的数据库的信息，如图5-10所示。 图5-9 查看LWZZ数据库的信息 图5-10 查看服务器中所有可用的数据库的信息 3(使用系统存储过程sp_helpfile 格式如下: sp_helpfile [文件名] 查看数据库中文件的信息。 (1)不加文件名，显示数据库中所有文件的信息，如图5-11所示。 图5-11 查看LWZZ数据库中所有文件的信息 (2)显示数据库中某一文件的信息，如图5-12所示。 图5-12 查看LWZZ数据库中LWZZ_Data文件的信息 4(使用系统存储过程sp_helpfilegroup 格式如下: sp_helpfilegroup [文件组名] 不加文件组名，显示数据库中所有文件组的信息。用法同sp_helpfile。 5.1.5 修改数据库 修改数据库包括增减数据文件和日志文件、修改文件属性(包括更改文件名和文件大小)、修改数据库选项等。 5.1.5.1 用企业管理器修改数据库 使用企业管理器中的数据库属性对话框可以很方便地修改数据库。一个已存在的数据库的属性对话框包括6个选项卡，如图5-13所示。 1(增减数据文件和日志文件 用户可以使用“数据文件”选项卡增减数据文件和修改数据文件属性。在如图5-13所示的对话框中，为LWZZ数据库增加一个文件C:\…\MSSQL\data\LWZZ_data2.ndf，初始大小为5MB，最大为50MB，每次自动增长1MB。 用户可以使用“事务日志”选项卡增减日志文件和修改日志文件属性，具体的做法与对数据文件的操作相同。 2(修改数据库选项 使用数据库属性对话框的“选项”选项卡可以修改一些数据库选项，只需用鼠标单击这些复选框，就可以非常容易地更改当前数据库的选项。勾选的就是True，否则就是False。如图5-14所示。 图5-13 修改LWZZ数据库的数据文件 图5-14 使用“选项”选项卡修改数据库选项 比较常用的数据库选项如下: (1)限制访问:只允许特殊用户访问数据库。如图5-14所示，限制访问有两种类型: 只允许db_owner、dbcreator和sysadmin的成员访问。 设置数据库为单用户模式。如果设置为单用户模式之前已有用户在使用该数据库，那么这些用户可以继续使用，但新的用户必须等到所有用户都退出之后才能登录。 (2)只读:数据库中的数据只能读取，而不能修改。 (3)自动关闭:用于指定数据库在没有用户访问并且所有进程结束时自动关闭，释放所有资源，当又有新的用户要求连接时，数据库自动打开。数据库自动关闭后，数据文件可以像普通文件一样处理(例如拷贝或作为电子邮件的附件发送)，所以这个选项很适合移动用户，而对于网络应用数据库，则最好不要设置这个选项，因为频繁地关闭和重新打开会对数据库性能造成极大的影响。 (4)自动缩减:当数据或日志量较少时自动缩小数据库文件的大小，当设置了只读时，这个选项无效。 5.1.5.2 使用SQL语句修改数据库 (1)用ALTER DATABASE语句增减数据文件和日志文件。 例如: ALTER DATABASE LWZZ ADD FILE ( NAME =LWZZ_data2, FILENAME='C:\ Program Files\Microsoft \MSSQL\data\LWZZ_data2.ndf' SIZE=5MB, MAXSIZE=50MB, FILEGROWTH=1MB ) 其中ADD FILE 是指增加一个数据文件，还可以是ADD LOG FILE、REMOVE FILE、MODIFY FILE 等，分别代表增加日志文件、删除数据库文件和修改文件信息。比如，以下的语句将LWZZ 数据库的第二个数据文件LWZZ_data2的初始大小修改为20MB。 ALTER DATABASE LWZZ MODIEY FILE ( NAME = LWZZ_data2, SIZE=20MB) 关于ALTER DATABASE 语句的更详细用法可以参考SQL Server 2000的在线手册。 (2)用ALTER DATABASE语句修改数据库选项 ALTER DATABASE LWZZ SET AUTO_SHRINK ON (3)使用系统存储过程sp_dboption修改数据库选项 Sp_dboption 'LWZZ', 'autoshrink', 'true' 除了设置选项外，Sp_dboption还有很多其他的作用。如运行不带参数的Sp_dboption，可以得到所有数据库选项列表。如图5-15所示。 如果希望看到某个数据库当前的选项设置情况，可以给Sp_dboption加上一个数据库名作为参数，SQL Server将显示所有在该数据库中设置为True的选项，如图5-16所示。 图5-15 使用Sp_dboption查看所有数据库选项列表 图5-16 查看LWZZ所有为True的选项 如果想查看某个特定选项的当前设置值，可以输入数据库名和选项名。图5-17显示了LWZZ数据库AUTOCLOSE 选项的设置情况。 图5-17 查看LWZZ数据库AUTOCLOSE选项的设置情况 5.1.6 删除数据库 当一个数据库不再使用时，可以将其删除。删除一个数据库会删除所有数据和该数据库 所使用的所有磁盘文件，数据库在操作系统上占用的空间将被释放。但删除一个数据库后，如果想再复原是很麻烦的，必须从备份中恢复数据库和它的事务日志，所以删除数据库之前应格外小心。 1(使用企业管理器删除数据库 在企业管理器中删除数据库只需找到容纳数据库的服务器，展开数据库上的数据库节点，用鼠标右键单击要删除的数据库，在弹出的快捷菜单中选择“删除”命令即可。如图5-18所示。 2(使用SQL语句删除数据库 使用SQL语句删除数据库需要使用DROP DATABASE语句。使用DROP DATABASE语句可以一次删除多个数据库。例如: DROP DATABASE northwind , pubs 当数据库处于以下三种情况之一时，不能被删除。 有用户使用此数据库时。 数据库正在被恢复(restore)时。 当数据库正在参与复制时。 图5-18 删除数据库 系统数据库中的master、model和tempdb都不能被删除，msdb虽然可以被删除，但删除msdb后很多服务(比如SQL Server 代理服务)将无法使用，因为这些服务在运行时会用到msdb。 5.2 数据表的创建 5.2.1 数据类型 SQL Server为了实现T-SQL的良好性能，提供了丰富的数据类型。 5.2.1.1 数值型数据 1(Bigint 6363Bigint型数据可以存放从-2到2-1范围内的整型数据。以bigint数据类型存储的每个值占用8个字节，共64位，其中63位用于存储数字，1位用于表示正负。 2(Int 3131Int也可以写作integer，可以存储从-2~2-1(-2,147,483,648~2,147,483,647)范围内的全部整数。以int数据类型存储的每个值占用4个字节，共32位，其中31位用于存储数字，1位用于表示正负的区别。 3(smallint 1515Smallint型数据可以存储从-2~2- 1(-32,768~32,767)范围内的所有整数。以smallint数据类型存储的每个值占用2个字节，共16位，其中15位用于存储数字，1位用于表示正负的区别。 4(Tinyint Tinyint型数据可以存储0~255范围内的所有整数。以tinyint数据类型存储的每个值占用1个字节。 整数型数据可以在较少的字节里存储较大的精确数字，而且存储结构的效率很高，所以平时在选用数据类型时，尽量选用整数数据类型。 5(Decimal和Numeric 事实上，numeric数据类型是decimal数据类型的同义词。但是二者也有区别，在表格中，只有numeric型数据的列可以带有identity关键字，decimal可以简写为dec。 使用decimal和numeric型数据可以精确指定小数点两边的总位数(精度，precision简写为p)和小数点右面的位数(刻度，scale简写为s)。 在SQL Server中，decimal和numeric型数据的最高精度的可以达到38位，即1?p?38，0?s?p。decimal和numeric型数据的刻度的取值范围必须小于精度的最大范围，也就 3838是说必须在-10-1~10-1之间。 SQL Server分配给decimal和numeric型数据的存储空间随精度的不同而不同，一般说来对应的比例关系如下所示: 精度范围 分配字节数 1~9 5 10~19 9 20~28 13 29~38 17 6(float和real Real型数据范围从-3.40E+38~1.79E+38，存储时使用4个字节。精度可以达到7位。 float型数据范围从-1.79E+38~1.79E+38。利用float来表明变量和表列时可以指定用来存储按科学计数法记录的数据尾数的bit数。如float(n)，n的范围是1~53。当n的取值为1~24 时，float型数据可以达到的精度是7位，用4个字节来存储。当n的取值范围是25~53时，float型数据可以达到的精度是15位，用8个字节来存储。 5.2.1.2 字符数据类型 SQL Server提供了3类字符数据类型，分别是Char、Varchar和Text。在这3类数据类型中，最常用的Char和Varchar两类。 1(Char 利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。利用Char数据类型来定义表列或者定义变量时，应该给定数据的最大长度。如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。在使用字符型常量为字符数据类型赋值时，必须使用单引号(‘’)将字符型常量括起来。 2(Varchar Varchar数据类型的使用方式与Char数据类型类似。SQL Server 利用Varchar数据类型来存储最长可以达到8000字符的变长字符。与Char 数据类型不同，Varchar数据类型的存储空间随存储在表列中的每一个数据的字符数的不同而变化。 例如，定义表列为Varchar(20)，那么存储在该列的数据最多可以长达20个字节。但是在数据没有达到20个字节时并不会在多余的字节上填充空格。 当存储在列中的数据的值大小经常变化时，使用Varchar数据类型可以有效地节省空间。 3(Text 当要存储的字符型数据非常庞大以至于8000字节完全不够用时，Char和Varchar数据类型都失去了作用。这时应该选择Text数据类型。 31Text数据类型专门用于存储数量庞大的变长字符数据。最大长度可以达到2-1个字符，约2GB。 下面的例子建立了一个以字符类型定义表列的表格，然后向其中插入了一行数。 创建一个表格: create table chars_example (char_1 char(5), varchar_1 varchar(5), text_1 text) go 插入一行数据: insert into chars_example values("abcd","abc","dddddddddddddddddddddddddd") go 5.2.1.3 日期/时间数据类型 SQL Server提供的日期/时间数据类型可以存储日期和时间的组合数据。以日期和时间数据类型存储日期或时间的数据比使用字符型数据更简单，因为SQL Server提供了一系列专门处理日期和时间的函数来处理这些数据。如果使用字符型数据来存储日期和时间，只有用户本人可以识别，计算机并不能识别，因而也不能自动将这些数据按照日期和时间进行处理。 日期/时间数据类型共有Datetime和Smalldatetime两类。 1(Datetime Datetime数据类型范围从1753年1月1日到9999年12月31日，可以精确到千分之一秒。Datetime数据类型的数据占用8个字节的存储空间。 2(Smalldatetime Smalldatetime数据范围从1900年1月1日到2079年6月6日，可以精确到分。Smalldatetime数据类型占4个字节的存储空间。 SQL Server在用户没有指定小时以上精度的数据时，会自动设置Datetime和Smalldatetime数据的时间为00:00:00。 5.2.1.4 货币数据类型 货币数据类型专门用于货币数据处理。SQL Server提供了Money和Smallmoney两种货币数据类型。 1(Money Money数据类型存储的货币值由2个4字节整数构成。前面的一个4字节表示货币值的整数部分，后面的一个4字节表示货币值的小数部分。以Money存储的货币值的范围从6363-2~2-1，可以精确到万分之一货币单位。 2(Smallmoney 由Smallmoney数据类型存储的货币值由2个2字节整数构成。前面的一个2字节表示货币值的整数部分，后面的一个2字节表示货币值的小数部分。以Smallmoney存储的货币值的范围从-214,748.3648~+214,748.3647，也可以精确到万分之一货币单位。 在把值加入定义为Money或Smallmoney数据类型的表列时，应该在最高位之前放一个货币符号$或其他货币单位的符号，但是也没有严格要求。 例如: create table number_example2 (money_num money, smallmoney_num smallmoney, ) go 插入1行数据: INSERT INTO number_example2 VALUES ($222.222,$333.333) 5.2.1.5 二进制数据类型 所谓二进制数据是一些用十六进制来表示的数据。例如，十进制数据245表示成十六进制数据就应该是F5。在SQL Server中，共使用了3种数据类型来存储二进制数据，分别是binary，varbinary 和Image。 二进制数据类型同字符型数据类型非常相似。使用binary数据类型定义的列或变量，具有固定的长度，最大长度可以达到8K字节;使用varbinary数据类型定义的列或变量具有不固定的长度，其最大长度也不得超过8K字节;Image数据类型可用于存储字节数超过 8K字节的数据，比如Microsoft Word文档、Microsoft Excel图表以及图像数据(包括.GIF、.BMP、.JPEG文件)等。 一般说来，最好使用binary或 varbinary数据类型来存储二进制数据。只有在数据的字节数超过了8KB的情况下，才使用Image数据类型。 在对二进制数据进行插入操作时，无需在数据上加上“”，但必须在数据常量前面增加一个前缀0x。 例如: create table binary_example (bin_1 binary (5), bin_2 varbinary(5)) go 输入数据: insert into binary_example values (0xaabbccdd,0xaabbccddee) insert into binary_example values (0xaabbccdde,0x) go 5.2.1.6 双字节数据类型 SQL Server提供的双字节数据类型共有3类，分别是Nchar，Nvarchar和Ntext。 1(Nchar(n) Nchar(n)是固定长度的双字节数据类型，括号里的n用来定义数据的最大长度。n的取值范围是1~4000，所以使用Nchar数据类型所能存储的最大字符数是4000字符。由于存储的都是双字节字符，所以双字节数据的存储空间为:字符数*2(字节)。 Nchar数据类型的其他属性及使用方法与Char数据类型一样。例如，也在有多余字节的情况下会自动加上空格进行填充。 2(Nvarchar(n) Nvarchar(n)数据类型存储可变长度的双字节数据类型，括号里的n用来定义数据的最大长度。n的取值为0~4000，所以使用Nvarchar数据类型所能存储的最大字符数也是4000。Nvarchar数据类型的其他属性及使用方法与Varchar数据类型一样。 3(Ntext(n) Ntext数据类型存储的是可变长度的双字节字符，Ntext数据类型突破了前2种双字节数 30据类型不能超过4000字符的规定，最多可以存储多达2-1个双字节字符。Ntext数据类型的其他属性及使用方法与Text数据类型一致。 5.2.1.7 图像、文本数据的使用 为了方便用户存储和使用文本、图像等大型数据，SQL Server提供了Text、Ntext和Image三种数据类型。 文本和图像数据在SQL Server中是用Text、Ntext和Image 数据类型来表示的，这三种数据类型很特殊，因为它们的数据量往往较大，所以它们不像表中其他类型的数据那样一行一行地依次存放在数据页中，而是经常被存储在专门的页中，在数据行的相应位置处只记录 指向这些数据实际存储位置的指针。在SQL Server 7.0以前的版本中，文本和图像数据都是这样与表中的其他数据分开存储的。SQL Server 2000提供了将小型的文本和图像数据在行中存储的功能。 当将文本和图像数据存储在数据行中时，SQL Server不需要为访问这些数据而去访问另外的页，这使得读写文本和图像数据可以与读写varchar、nvarchar和varbinary 字符串一样快。 为了指定某个表的文本和图像数据在行中存储，需要使用系统存储过程sp_tableoption设置该表的“text in row”选项。当指定“text in row”选项时，还可以指定一个文本和图像数据大小的上限值，这个上限值应在24~7000字节之间。当同时满足以下两个条件时，文本和图像数据直接存储在行中: (1)文本和图像数据的大小不超过指定的上限值。 (2)数据行有足够的空间存放这些数据。 当以上两个条件有一个不满足时，行中只存放指向这些数据实际存储位置的指针。 例如: create table text_example (bin_1 text, bin_2 ntext) go 以下的语句指定text_example表在行中存储文本和图像数据 sp_tableoption 'txet-example', 'text in row', 'TRUE' 规定text_example表中不大于1000字节的文本和图像数据直接在行中存储，可以执行以下语句: sp_tableoption 'text_example ', 'text in row', '1000' 如果不显式地指定上限，那么默认的上限为256字节，即在前一个例子中，行中存储文本和图像数据最大为256字节。 以下语句指定Mytable表不在行中存储文本和图像数据: sp_tableoption 'Mytable', 'text in row', 'FALSE' 5.2.1.8 用户自定义数据类型及使用 用户自定义数据类型并不是真正的数据类型，它只是提供了一种加强数据库内部和基本数据类型之间一致性的机制。通过使用用户自定义数据类型能够简化对常用规则和默认值的管理。 可以使用系统存储过程sp_addtype来创建用户自定义数据类型。语法是: sp_addtype type_name system_type; {" NULL"|"NOT NULL"|"NONULL"}-默认为"NULL" 凡是包含了诸如“()”或“，”等分隔符的系统数据类型，如Char(9)必须使用引号括起来，即“Char(9)”。用户自定义数据类型在数据库中的命名必须惟一。只要命名惟一，甚至相同的类型定义也可以存储在同一个数据库中。 下面的例子创建了2个用户自定义数据类型: use model exec sp_addtype telelephone,'varchar(24)','not null' exec sp_addtype fax, 'varchar(24)','null' 可以直接使用用户自定义数据类型来定义表格。但是用户自定义数据类型经常与默认值或规则等配合使用。 使用系统存储过程sp_droptype可以删除用户自定义数据类型，语法是: sp_droptype type_name 在实际使用中，如果用户自定义数据类型正被某表中的某列使用，则不能立即删除它，必须首先删除使用该数据类型的表。 可以使用Enterprise Manager来创建用户自定义数据类型，步骤是: (1)选中要创建数据类型的数据库，展开该节点。 (2)选中树型结构上的用户定义的数据类型。 (3)右击鼠标，从弹出的快捷菜单中选择“新建用户定义的数据类型(U)”命令。在弹出的对话框输入名称、系统数据类型、长度等参数，如图5-19所示，单击“确定”按钮，完成用户自定义数据类型的创建。 图5-19 创建用户自定义数据类型 5.2.2 创建表结构 1(利用企业管理器提供的图形界面创建表 步骤如下: (1)在树形目录中找到要建表的数据库，展开该数据库。 (2)选择表，单击鼠标右键，在弹出的快捷菜单中选择“新建表”命令，表设计器如图5-20所示。 (3)表设计器的上半部分有一个表格，在这个表格中输入列的属性，表格的每一行对应一列。对每一列都需要进行以下设置，其中前三项是必须在建表时给出的，它们是: 列名。 图5-20 表设计器 数据类型:数据类型是一个下拉列表框，其中包括了所有的系统数据类型和数据库中的用户自定义数据类型。 长度:如果选择的数据类型需要长度，则指定长度。 允许空:单击鼠标，可以切换是否允许为空值的状态，勾选说明允许为空值，空白说明不允许为空值，默认状态下是允许为空值的。 表设计器的下半部分是特定列的详细属性，包括是否是标识列、是否使用默认值等。 逐个定义好表中的列，如图5-21所示。 图5-21 创建表结构 (4)定义好所有列后，单击工具栏上的保存按扭，表就创建完成了。 2(用,-SQL语句创建表 用“CREATE TABLE表名”来创建表，表中列的定义必须有括号括起来。一个表最多有1024列。 下面将使用CREATE TABLE语句来创建LWZZ数据库中的两个表:LWQK 和ZZQK。 (1)创建LWQK表。 use LWZZ go create table LWQK (lwbh char (14), lwmc char(40), xkfl char(20), fbkw char(30), fbsj datetime, kwdw char(30), fxfw char(10), lwzs int, lwzz char(10), hxqk char(2), SCI char(2), EI char(2), zzhj char(100), bz text ) 上面的代码创建了一个包含13列的LWQK表，该表记录了论文的基本信息，包括:论 文编号、论文名称、学科分类、发表刊物及时间、刊物主办单位及发行范围、论文字数、论 文作者、是否核心期刊、是否被SCI收录、是否被EI收录、论文转载及获奖情况、备注。 在查询分析器中执行上述建表语句，如图5-22所示，结果窗口中显示语句成功完成信 息，表示建表语句成功执行。 使用sp_help系统存储过程可以查看表的定义，在查询分析器中输入 sp_help LWQK命令， 可以查看刚刚创建的LWQK表的结构。 (2)创建ZZQK表。ZZQK表用来记录著作的信息，创建该表的代码如下: use LWZZ go create table ZZQK (zzbh char(14), zzmc char(40), cglb char(10), xkfl char(20), cbsmc char(30), cbsj datetime, bzry char(10), zzzs text, zzsy text, bz text ) go 图5-22 创建LWQK表 上面的代码创建了一个包含10列的ZZQK表，该表记录了著作编号、著作名称、成果类别、学科分类、出版社名称及出版时间、参加编著人员顺序及单位、该著作总章节字数等信息。 5.2.3 查看表结构 1(用企业管理器查看表结构 找到要查看的表所在的数据库，选中表，右边的窗口中就会显示这一数据库中所有的表，对于每个表，都会显示它的所有者、类型和创建时间，如图5-23所示。 在列表中选择一个表，单击鼠标右键打开快捷菜单，选择属性命令，可以查看表中每一列的定义，如图5-24所示。 2(使用,-SQL语句显示表结构 在sp_help后面加上要查看的表名作为参数就可查看表结构。包括表的所有者、类型(系统表还是用户表)、创建时间、表上每一列的名称、数据类型、表上定义的索引以及约束等。 图5-23 查看LWZZ库中的所有表 图5-24 使用企业管理器查看LWQK表的结构 例如: use lwzz go exec sp_help lwqk 使用查询分析器执行这一语句的结果如图5-25所示。 实际上，使用sp_help可以查看所有数据库对象的定义，除了表外还包括视图、存储过程以 及用户自定义数据类型等。 图5-25 使用查询分析器查看LWQK表的结构 例如: use lwzz go sp_help 执行结果如图5-26所示。 图5-26 LWZZ库的所有对象 5.2.4 修改表结构 创建完一个表以后，难免要对表进行修改。可以使用企业管理器或ALTER TABLE语句 对表进行修改。 5.2.4.1 使用企业管理器修改表 使用企业管理器来修改表，可以用鼠标右键单击要修改的表，在弹出的快捷菜单中选择“设计表”命令，如图5-27所示。 图5-27 修改LWZZ的表结构 单击“设计表”命令，将弹出如图5-20所示的表设计器，此时可以像新建表时一样，向表中加入或从表中删除列或修改列的属性，修改完毕后单击“保存”按扭即可。图5-28显示了如何向LWZZ表中加入一个新列EMAIL。 图5-28 向LWQK表中加入一列 选中某一列，按右键选择“删除列”命令，则可删除某一列。用和建表时相似的方法可以对列值进行修改。 5.2.4.2 使用SQL语句修改表 1(添加列 向表中增加一列时，应使新增加的列有默认值或允许为空值，SQL Server将向表中已存在的行填充新增列的默认值或空值，如果既没有提供默认值也不允许为空值，那么新增列的操作将出错，因为SQL Server不知道该怎么处理那些已经存在的行。 向表中添加列的语句格式如下: ALTER TABLE表名 ADD列名 列的描述 例如:向LWQK表中添加电子邮件(Email)列。 use lwzz go alter table lwqk add email varchar(20) null 2(删除列 删除一列的语句格式为: ALTER TABLE表名 DROP COLUMN列名 例如:删除 LWQK表的Email列。 use lwzz go alter table lwqk drop column email 3(修改列定义 表中的每一列都有其定义，包括列名、数据类型、数据长度以及是否允许为空值等，这些值都可以在表创建好以后修改。 修改列定义的语句格式为: ALTER TABLE 表名 ALTER COLUMN 列名 列的描述 例如:将LWQK表的fxfw 列改为最大长度为20的varchar型数据，且不允许空值。 use lwzz go alter table lwqk alter column fxfw varchar(20) not null 默认状态下，列是被设置为允许空值的，将一个原来允许空值的列改为不允许空值，必须在以下两个条件满足时才能成功: (1)列中没有存放是空值的记录 (2)在列上没有创建索引。 5.2.5 删除表结构 当不再需要某个表时，可以将其删除。一旦一个表被删除，那么它的数据、结构定义、约束、索引都将被永久地删除，以前用来存储数据和索引的空间可以用来存储其他的数据库对象了。 1(用企业管理器删除表 (1)使用企业管理器删除一个表非常简单，只需找到要删除的表，按右键，在弹出的快捷菜单中选择“删除”命令，如图5-29所示。 图5-29 删除LWZZ库的TABLE1表 (2)在打开的如图5-30所示的“除去对象”对话框中，单击“全部除去”按钮。 图5-30 除去对象 2(使用SQL语句删除表 DROP TABLE语句可以用来删除表。其语法为: DROP TABLE 表名 需要注意的是DROP TABLE语句不能用来删除系统表。 例如:删除LWZZ库中的TABLE1表。 USE LWZZ GO DROP TABLE TABLE1 5.2.6 向表中插入数据 5.2.6.1 使用INSERT语句 INSERT语句的基本语法为: INSERT [INTO] 表名 [(列名)] Values(表达式) 1(添加数据到一行中的所有列 当将数据添加到一行的所有列时，使用VALUES关键字来给出要添加的数据。INSERT语句中无需给出表中的列名，只要VALUES中给出的数据与用CREATE TABLE定义表时给定的列名顺序相同即可。 例如:向LWQK表中添加一条记录。 use lwzz go insert into lwqk values('37132001081301','汽车前照灯电子控制装置的维护与故障排除','应用','汽车与配件','1999/9/23',' 中汽工业协会市场贸委会','全国'1800,'李正銮','否','否','否','无','无') 在查询分析器中执行，返回的结果为: (所影响的行数为 1 行) 需要注意的是: (1)输入的顺序和数据类型必须与表中列的顺序和数据类型一致。 (2)可以不给全部列赋值，但没有赋值的列必须是可以为空的列。 (3)字符型和日期型值插入时要用单引号扩起来。 2(添加数据到一行中的部分列 要将数据添加到一行中的部分列时，则需要同时给出要使用的列名以及要赋给这些列的数据。 例如:向ZZQK表中添加一条记录。 use lwzz go insert into zzqk(zzbh,zzmc) values('37132001050101','数据库原理与应用-sql server 2000') 在查询分析器中执行，返回的结果为: (所影响的行数为 1 行) 对于这种添加部分列的操作，在添加数据前应确认未在VALUES列表中出现的列允许不允许为NULL;只有允许为NULL的列，才可以不出现在VALUES列表中。 5.2.6.2 使用WRITETEXT语句 使用WRITETEXT语句可以更新已存在的文本或图像数据(即text、 ntext和 image类型的数据)，被更新的文本或图像数据被完全覆盖，默认状态下，WRITETEXT语句的操作不被记入事物日志。 WRITETEXT的语法如下: WRITETEXT 表名.列名 指针 数据 其中: 表名.列名为要更新的text、ntext、或image列名。 指针为指向要更新的文本或图像数据的指针，指针的数据类型必须为binary(16)。 数据为要写入的文本或图像数据 。 在SQL Server 2000中，有一个新特性就是可以在行内存储text、ntext和image类型的数据，在行内和不在行内的文本或图像数据，对指针的处理是不同的。对于不在行内存储的文本或图像数据，为了获得该数据的指针，应保证该数据不是空值。如果该数据为NULL，则应先用UPDATE语句将其改变为非空值，下面举一个例子。 例如:修改LWQK表中bz的值，LWQK表的“text in row”选项关闭，即不在行内存储 text数据，代码如下: EXEC sp_dboption 'LWZZ','select into/bulkcopy','false' GO DECLARE @ptrvary binary(16) SELECT @ptrvary =TEXTPTR(bz) FROM LWQK WHERE lwbh='37132001081301' WRITETEXT LWQK.bz @ptrvary '独立完成' 上述代码中，将用TEXTPTR函数获得的文本或图像数据指针保存到局部变量@ptrvary中，然后被WRITETEXT语句使用。 如果表的“text in row”选项打开，即在行内存储文本或图像数据，那么无论文本数据是否为空值，TEXTPTR函数都可以取到该数据的指针，但该指针只能在一个事务中使用。 5.2.7 修改表中数据 当数据添加到表中后，会经常需要修改，如客户的地址发生了变化，货品库存量的增减等。 1(使用UPDATE语句修改数据 语法格式为: UPDATE 表名 SET 列名=表达式 [WHERE 条件 ] 【例1】将所有论文的发表时间都改为'2002/12/31'。 use lwzz go update lwqk set fbsj='2002/12/31' 因为没有使用WHERE子句，所以对发表时间的修改影响到表中的每一行。 【例2】将编号为37132001081301的发表时间改为'2001/9/23'。 use lwzz go update lwqk set fbsj='2002/12/31' where lwbh=' 37132001081301' 只有满足WHERE子句中条件的行被修改。 2(使用UPDATETEXT语句 在前面曾经介绍过WRITETEXT语句，WRITETEXT语句的作用是修改完整的文本或图像数据，而UPDATETEXT语句则可以用于修改文本或图像数据的一部分，与WRITETEXT相比，UPDATETEXT更加灵活。比如，在某一个论文的备注文本中只需要修改一个拼写错误，这时就可以使用UPDATETEXT语句仅对文本中有错误的地方进行修改。 UPDATETEXT的语法如下: UPDATETEXT 表名.列名 指针 |位置 NULL|长度 数据 其中: 表名.列名为要修改的text、ntext或image列名。 指针为指向要修改的文本或图像数据的指针，其数据类型必须为binary(16)。 位置为原数据中要修改部分的起始位置，该位置从0开始，可以为NULL，表示将数据添加到原来的数据末尾。 长度为原始数据中要修改的部分的长度，如果为NULL，则表示从起始位置开始的所有数据都要被修改。 数据为修改成的数据。可以是直接给出的文本或图像数据，也可以是另外一个文本或图像数据指针。 例如:修改LWZZ表37132001081301的备注。 DECLARE @ptrvary binary(16) SELECT @ptrvary = TEXTPTR(bz) FROM LWQK WHERE lwbh = '37132001081301' UPDATETEXT LWQK.bz @ptrvary 2 2 '自' 在使用UPDATETEXT时，对于本文数据在行内或不在行内存储的处理与WRITETEXT相同。 5.2.8 删除表中数据 当数据的添加工作完成以后，随着使用和对数据的修改，表中可能存在着一些无用的数据，这些无用数据不仅会占用空间，还会影响修改和查询数据的速度，所以应及时将它们删除。 1(使用DELETE语句 使用T-SQL中的DELETE语句可以删除数据表中的一个或多个记录。 DELETE语句最简单的形式如下: DELETE 表名 [WHERE 条件] 其中，表名是要删除数据的表的名字。如果DELETE语句中没有WHERE子句限制，表中的所有记录都将被删除。 例如:删除ZZQK表的37132001050101号记录。 use lwzz go delete zzqk where zzbh='37132001050101' 执行结果为: (所影响的行数为 1 行) 2(使用TRUNCATE TABLE语句 TRUNCATE TABLE语句提供了一种删除表中所有记录的快速方法，因为TRUNCATE TABLE语句不记录日志，只记录整个数据页的释放操作，而DELETE语句对每一行修改都记录日志，所以TRUNCATE TABLE语句总比没有指定条件的DELETE语句快。 例如:删除所有ZZQK记录。 TRUNCATE TABLE ZZQK 因为TRUNCATE TABLE操作是不进行日志记录的，所以建议在TRUNCATE TABLE语句之前用先对数据库作备份，数据库备份的内容将在第10章介绍。 5.2.9 使用企业管理器查看表格 使用企业管理器可以对一个表进行查看、修改，方法如下: (1)在企业管理器树型目录中找到存放表的数据库。 (2)展开数据库，选中表。 (3)在右边窗口的列表中用鼠标右键单击要查询的表，在其弹出的快捷菜单中选择打开表，在该菜单下有三个子菜单，如图5-31所示。 图5-31 在企业管理器中查看表 (4)选择“返回所有行”，打开的窗口如图5-32所示;选择“返回首行”，则打开如图5-33所示的对话框，让用户输入n的值，然后显示前n行，如图5-34所示。 图5-32 显示表中所有行 图5-33 指定返回数据行的数目 图5-34 显示LWQK表的前5行 (5)在图5-32或图5-34所示的表格窗口中对数据进行操作。 第6章 数据查询 6.1 Transact-SQL简单查询 6.1.1 最简单的SELECT语句 SELECT在任何一种SQL语言中，都是使用频率最高的语句。可以说SELECT是SQL语言的灵魂。SELECT语句的作用是让数据库服务器根据客户端的要求搜寻出用户所需要的信息资料，并按用户规定的格式进行整理后返回给客户端。用户使用SELECT语句除可以查看普通数据库中的表格和视图的信息外，还可以查看SQL Server的系统信息。 SELECT语句具有强大的查询功能，有的用户甚至只需要熟练掌握SELECT语句的一部分，就可以轻松地利用数据库来完成自己的工作。 1(SELECT语句的常规使用方式 SELECT 列名1[,列名2,…列名n] FROM 表名 下面的例子从pubs数据库的publishers(出版商)表中检索所有的出版商代号、出版商的名字以及国籍。 use pubs go select pub_id,pub_name,country from publishers go 服务器返回的结果是: pub_id pub_name country ------ ---------------------------------------- ------------------------------ 0736 New Moon Books USA 0877 Binnet & Hardley USA 1389 Algodata Infosystems USA 1622 Five Lakes Publishing USA 1756 Ramona Publishers USA 9901 GGG&G Germany 9952 Scootney Books USA 9999 Lucerne Publishing France (所影响的行数为 8 行) 这个查询结果一共返回了8行数据，通过这一查询可以看出使用SQL语句所操作的是数据集合，而不是单独的行。在上述查询里，返回的是所有行中相同目标列上的数据。 2(用“*”表示表中所有的列 语法格式如下: SELECT * FROM 表名 服务器会按用户创建表格时声明列的顺序来显示所有的列，例如 use pubs go select * from titleauthor go 服务器返回的结果为: au_id title_id au_ord royaltyper ----------- -------- ------ ----------- 172-32-1176 PS3333 1 100 213-46-8915 BU1032 2 40 213-46-8915 BU2075 1 100 238-95-7766 PC1035 1 100 267-41-2394 BU1111 2 40 267-41-2394 TC7777 2 30 „„ (所影响的行数为 25 行) 3(使用SELECT语句进行无数据源检索 所谓无数源检索就是使用SELECT语句来检索不在表中的数据。例如，可以使用 SELECT语句检索常量、全局变量或已经赋值的变量。 无数据源检索实质上就是在客户机屏幕上显示出变量或常量的值。 (1)使用SELECT语句查看常量。例如: select "sql server 6.5" select "sql server 7.0" go 服务器返回的结果是: ------------- SQL Server 6.5 (所影响的行数为 1 行) -------------- SQL Server 7.0 (所影响的行数为 1 行) …………. (2)使用SELECT语句查看全局变量。 【例1】查询本地SQL Server服务器的版本信息。 select @@version go 服务器返回结果是: -------------------------------------------------------------------------------------------------------------- Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on Windows 4.10 (Build 2222: A ) (所影响的行数为 1 行) 【例2】查询本地SQL Server服务器使用的语言。 select @@language go 服务器返回结果是: ------------------------------------------------------------------------------------------------------------- 简体中文 (所影响的行数为 1 行) 4(使用TOP关键字 0提供了TOP关键字，让用户指定返回前面一定数量的数据。当查询到SQL Server 7. 的数据量非常庞大时(例如有100万行)，但没有必要对所有数据进行浏览时，使用TOP关 键字查询可以大大减少查询花费的时间。 语法格式如下: SELECT [TOP n | TOP n PERCENT] 列名1[,列名2,…列名n] FROM 表名 其中; TOP n表示返回最前面的n行，n表示返回的行数。 TOP n PERCENT表示返回的前面的n%行。 例3】从northwind数据库的customers表中返回前面10行数据。 【 use northwind go select top 10 * from customers go 【例4】从northwind数据库中的customers表中返回前10%的数据。 use northwind go select top 10 percent * from customers go 5(使用DISTINCT关键字 前面介绍的最基本的查询方式会返回从表格中搜索到的所有行的数据，而不管这些数据 是否重复，这常常不是用户所希望看到的。使用DISTINCT关键字就能够从返回的结果数 据集合中删除重复的行，使返回的结果更简洁。 在使用DISTINCT关键字后，如果表中有多个为NULL的数据，服务器会把这些数据 视为相等。 【例5】从pubs数据库的publishers表中搜索出所有国家的名字。 use pubs go select country from publishers go 服务器返回结果是: country ------------------------------ USA USA USA USA USA Germany USA France (所影响的行数为 8 行) 由于存在多个出版商位于同一个国家的情况，所以会有重复的国家名字出现。若使用 DISINCT关键字以后，可以过滤掉重复的国家名。 【例6】从pubs数据库的publishers表中搜索出所有国家的名字(要求不能重复)。 use pubs go select distinct country from publishers go 服务器返回的结果是: country ------------------------------ France Germany USA (所影响的行数为 3 行) 只返回了3个国家的名字，有5个重复的数据被过滤掉了。 【例7】当同时对2列数据进行查询时，如果使用了DISTINCT关键字，将返回这2列 数据的惟一组合。 use pubs go select distinct city,state from authors go 服务器返回的结果是: -------------------- ----- Ann Arbor MI Berkeley CA Corvallis OR Covelo CA Gary IN „„ (所影响的行数为 16 行) 6(使用计算列 在进行数据查询时，经常需要对查询到的数据进行再次计算处理。T-SQL允许直接在 SELECT语句中使用计算列。计算列并不存在于表格所存储的数据中，它是通过对某些列的 数据进行演算得来的结果。 【例8】将每本书的销售价格降低30%。 use pubs go select title_id , type , price , price- price* 0.3 from titles go 服务器返回的结果是: title_id type price -------- ------------ --------------------- ------------------------ BU1032 business 19.9900 13.99300 BU1111 business 11.9500 8.36500 BU2075 business 2.9900 2.09300 BU7832 business 19.9900 13.99300 MC2222 mod_cook 19.9900 13.99300 „„ (所影响的行数为 18 行) 由于没有为计算列指定列名，所以返回的结果上看不到它的名字。 在Transact-SQL的计算列上，允许使用+、-、*、/ 、%以及按照位来进行计算的逻辑运算 &)、OR(|)、XOR(^)、NOT(~)以及字符串连接符(+)。 符号AND( 【例9】查询作者的姓名(用+来连接作者的姓和名)以及作者所居住的州和城市。 use pubs go select au_lname + '.'+ au_fname ,city+ ','+state from authors go 执行后的结果如下: ------------------------------------------------------------ ----------------------- White.Johnson Menlo Park,CA Green.Marjorie Oakland,CA Carson.Cheryl Berkeley,CA O'Leary.Michael San Jose,CA „„ (所影响的行数为 23 行) 7(操作查询的列名 T-SQL提供了在SELECT语句中操作列名的方法。用户可以根据实际需要对查询数据 的列标 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 进行修改，或者为没有标题的列加上临时的标题。 对列名进行操作有3种方式: (1)采用符合ANSI规则的标准方法，在列表达式后面给出列名。例如: use pubs go select title_id '图书代号',price'原价',price-price*0.3'现价' from titles go 服务器查询结果如下: 图书代号 原价 现价 ------ --------------------- ------------------------ BU1032 19.9900 13.99300 BU1111 11.9500 8.36500 BU2075 2.9900 2.09300 BU7832 19.9900 13.99300 TC7777 14.9900 10.49300 (((((( (所影响的行数为 18 行) (2)用“=”来连接列表达式。例如: use pubs go select '图书代号'=title_id, '原价'=price,'现价'=price-price*0.3 from titles go (3)用AS关键字来连接列表达式和指定的列名。例如: use pubs go select title_id as'图书代号',price as'原价',price-price*0.3 as'现价' from titles go 执行(2)、(3)与(1)返回结果相同。 6.1.2 带条件的查询 使用WHERE子句的目的是为了从表格的数据集中过滤出符合条件的行。 语法格式如下: SELECT 列名1[,列名2,…列名n] FROM 表名 WHERE 条件 使用WHERE子句可以限制查询的范围，提高查询效率。在使用时，WHERE子句必须紧跟在FROM子句后面。WHERE子句中的条件表达式包括算术表达式和逻辑表达式两种;SQL Server 对WHERE子句中的查询条件的数目没有限制。 1(使用算术表达式 使用算术表达式作为搜索条件的一般表达形式是: 表达式 算术操作符 表达式 表达式为:常量、变量和列表达式的任意有效组合。 WHERE子句中允许使用的算术操作符包括:=(等于)、<(小于)、>(大于)、<>(不等于)、!>(不大于)、!<(不小于)、>=(大于等于)、<=(小于等于)、!=(不等于)。 例如:查询pubs库的titles表中，价格打了8折后仍大于12美元的书号、种类以及原价。 use pubs go select title_id as 书号,type as 种类,price as 原价 from titles where price -price * 0.2>12 go 查询结果如下: 书号 种类 原价 ------ ------------ --------------------- BU1032 business 19.9900 BU7832 business 19.9900 MC2222 mod_cook 19.9900 PC1035 popular_comp 22.9500 PC8888 popular_comp 20.0000 PS1372 psychology 21.5900 PS3333 psychology 19.9900 TC3218 trad_cook 20.9500 行) (所影响的行数为 8 2(使用逻辑表达式 在T-SQL里的逻辑表达式共有3个。分别是: NOT:非，对表达式的否定。 AND:与，连接多个条件，所有的条件都成立时为真。 OR:或，连接多个条件，只要有一个条件成立就为真。 在T-SQL中逻辑表达式共有3种可能的结果值，分别是TRUE，FALSE和UNKOWN。UNKOWN是由值为NULL的数据参与逻辑运算得出的结果。表6-1、表6-2和表6-3分别列出了进行逻辑运算时各种情况下的结果。 表6-1 AND运算各种情况 AND运算 TRUE UNKNOWN FALSE TRUE TRUE UNKNOWN FALSE UNKNOWN UNKNOWN UNKNOWN FALSE FALSE FALSE FALSE FALSE 表6-2 OR运算各种情况 OR运算 TRUE UNKNOWN FALSE TRUE TRUE TRUE TRUE UNKNOWN TRUE UNKNOWN UNKNOWN FALSE TRUE UNKNOWN FALSE 表6-3 NOT运算各种情况 NOT运算 运算结果 TRUE FALSE UNKNOWN UNKNOWN FALSE TRUE 【例10】查询所有在美国加利福尼亚州的出版社。 use pubs go select pub_id as 出版社代号, pub_name as 出版社名称,city as 城市, state as 州, country as 国家 from publishers where country='usa'and state ='ca' go 查询结果如下: 出版社代号 出版社名称 城市 州 国家 ----- ---------------------------------------- -------------------- ---- ------------------------------ 1389 Algodata Infosystems Berkeley CA USA (所影响的行数为 1 行) 3(使用BETWEEN关键字 使用BETWEEN关键字可以更方便地限制查询数据的范围。 语法格式为: 表达式[NOT] BETWEEN 表达式1 AND 表达式2 例如:查询价格在15和20美元之间的书的书号、种类和价格。 use pubs go select title_id as 书号,type as 种类 ,price as 原价 from titles where price between $15 and $20 go 查询结果如下: 书号 种类 原价 ------ ------------ --------------------- BU1032 business 19.9900 BU7832 business 19.9900 MC2222 mod_cook 19.9900 PC8888 popular_comp 20.0000 PS3333 psychology 19.9900 (所影响的行数为 5 行) 使用BETWEEN表达式进行查询的效果完全可以用含有>=和<=的逻辑表达式来代替，使用 NOT BETWEEN 进行查询的效果完全可以用含有>和<的逻辑表达式来代替。 例如:上面的查询语句可以用下面的语句代替。 use pubs go select title_id as 书号,type as 种类,price as 原价 from titles where price >= $15 and price <=$20 go 【例11】查询书价大于20和书价小于15的书的代号、种类和价格。 use pubs go select title_id as 书号,type as 种类, price as 原价 from titles where price < $15 or price > $20 go 查询结果如下: 书号 种类 原价 ------ ------------ --------------------- BU1111 business 11.9500 BU2075 business 2.9900 MC3021 mod_cook 2.9900 PC1035 popular_comp 22.9500 „„ (所影响的行数为 11 行) 若使用BETWEEN，查询语句如下: use pubs go select title_id 书号,type 种类,price 价格 from titles where price not between $15 and $20 go 使用BETWEEN限制查询数据范围时同时包括了边界值，而使用NOT BETWEEN进行查询 时没有包括边界值。 4(使用IN关键字 同BETWEEN关键字一样，IN的引入也是为了更方便地限制检索数据的范围，灵活使 用IN关键字，可以用简洁的语句实现结构复杂的查询。 语法格式为: 表达式 [NOT] IN (表达式1 , 表达式2 [,…表达式n]) 【例12】查询所有居住在KS、CA、MI或IN州的作家。 use pubs go select au_id,au_lname,au_fname from authors where state IN ('CA','KS','MI','IN') go 如果不使用IN关键字，这些语句可以使用下面的语句代替: use pubs go select au_id,au_lname,au_fname from authors where state='CA 'or state='KS'or state='MI'or state='IN' go 【例13】查询所有不在上述4个州居住的作家。 use pubs go select au_id,au_lname,au_fname from authors where state not in ('CA','KS','MI','IN') go 与下面的语句等价: use pubs go select au_id,au_lname,au_fname from authors where state<>'CA ' and state<>'KS' and state<>'MI' and state<>'IN' go 6.1.3 模糊查询 在实际的应用中，用户不会总是能够给出精确的查询条件。因此，经常需要根据一些并 不确切的线索来搜索信息。T-SQL提供了LIKE子句来进行这类模糊搜索。 语法格式为: 表达式 [NOT] LIKE 条件 条件通常与通配符配合使用。 1(通配符的使用 LIKE子句在大多数情况下会与通配符配合使用。SQL Server提供了以下4种通配符供 用户灵活实现复杂的查询条件。 %(百分号):表示从0~n个任意字符。 _(下划线):表示单个的任意字符。 [ ](封闭方括号):表示方括号里列出的任意一个字符。 [^]:任意一个没有在方括号里列出的字符。 下面我们看一下模糊查询的使用方法。 【例14】查询所有名字以D打头的作家。 use pubs go select au_lname + '.'+au_fname from authors where au_fname like'D%' go 查询结果如下: ------------------------------------------------------------- Straight.Dean Stringer.Dirk (所影响的行数为 2 行) 【例15】查询所有au_id满足前2个字符为“72”，第4个字符为“-”的作家的姓名和 电话号码。 use pubs go select au_lname,au_fname, phone, au_id from authors where au_id like '72_-%' go 查询结果如下: au_lname au_fname phone au_id ---------------------------------------- -------------------- ------------ ----------- DeFrance Michel 219 547-9982 722-51-5454 Stringer Dirk 415 843-2991 724-08-9931 MacFeather Stearns 415 354-7128 724-80-9391 (所影响的行数为 3 行) 使用方括号可以将字符搜索的范围进一步缩小。 【例16】查询出所有au_id满足前2个字符为“72”，第3个字符为3、4、5中的一个，第4个字符为“-”的作家的姓名和电话号码。 use pubs go select au_lname,au_lname, phone, au_id from authors where au_id like '72[345]-%' go 查询结果如下: au_lname au_lname phone au_id ---------------------------------------- ---------------------------------------- Stringer Stringer 415 843-2991 724-08-9931 MacFeather MacFeather 415 354-7128 724-80-9391 (所影响的行数为 2 行) 还可以使用方括号和连字符来指定一个值的范围。 【例17】查询所有au_id的第一个字符为5-9、第二个字符为1-4的作家的姓名和电话号码。 use pubs go select au_lname,au_fname, phone, au_id from authors where au_id like '[5-9][1-4]%' go 搜索结果如下: au_lname au_fname phone au_id ---------------------------------------- -------------------- ------------ ----------- Greene Morningstar 615 297-2723 527-72-3246 Blotchet-Halls Reginald 503 745-6402 648-92-1872 del Castillo Innes 615 996-8275 712-45-1867 DeFrance Michel 219 547-9982 722-51-5454 Stringer Dirk 415 843-2991 724-08-9931 MacFeather Stearns 415 354-7128 724-80-9391 Hunter Sheryl 415 836-7128 846-92-7186 (所影响的行数为 7 行) 用户必须注意的是，所有通配符都只有在LIKE子句中才有意义，否则通配符会被当作普通字符处理。在前面举过的查找所有名字以D开头的作家的例子中，若使用下面的查询语句， 将一无所获。 use pubs go select au_lname +'.'+au_fname from authors where au_fname ='D%' go 返回结果是: ------------------------------------------------------------- (所影响的行数为 0 行) 因为并不存在一个作家名字叫D,。 2(转义字符的使用 假设有一个表X，列col的值如下: ' [xyz]' '%xyz' 'x_yz' 'xyzw' 【例18】在X中查找以%开头的字符串 select col from X where col like 't%%' escape 't' 查询结果是: col ----- %xyz (所影响的行数为 1 行) 在这个查询中't'是转义字符,表示紧跟在它后面的字符已被转义。 6.1.4 函数的使用 为了有效处理用户通过使用SQL查询得到的数据集合，SQL Server提供了一系列统计 函数。这些函数把存储在数据库中的数据描述为一个整体而不是一行行孤立的记录，通过使 用这些函数可以实现数据集合的汇总或是求平均值等各种运算。 1(常用统计函数 最常见的统计函数如表6-4所示。 表6-4 SQL Server 的统计函数 函数名 功能 返回一个数字列或计算列的总和 sum() 对一个数字列或计算平均值 avg() 返回一个数字列或数字表达式的最小值 min() 返回一个数字列或数字表达式的最大值 max() 返回满足SELECT语句中指定的条件的记录值 count() 返回找到的行数 count(*) 例如:查询titles中所有书的平均价格。 use pubs go select avg(price) from titles go 返回的结果是: --------------------- 14.7662 (所影响的行数为 1 行) 使用统计函数所返回的结果同使用计算列一样，没有列标题。不过用户可以像使用计算 列一样，为统计函数返回的结果指定一个假的列名。 例如:可以为上例的avg(price)指定列名'avg_price'。 use pubs go select avg(price) 'avg_price' from titles go 返回的结果是: avg_price --------------------- 14.7662 (所影响的行数为 1 行) 2(与统计函数一起使用WHERE子句 通过与子句一起使用集合函数，可以指定集合操作中应该包括哪些行。 例如:查询各种商业图书的平均价格。 use pubs go select avg(price)'avg_price' from titles where type = 'business' 查询的结果是: avg_price --------------------- 13.7300 (所影响的行数为 1 行) 3(与统计函数一起使用DISTINCT关键字 在T-SQL中，允许与统计函数如count()，sum()和avg()一起使用DISTINCT关键字来处理列或表达式中不同的值。 例如:查询仓储的货物种类。 use pubs go select count(distinct stor_id) from sales go 返回的结果是: ----------- 6 (所影响的行数为 1 行) 若不用DISTINCT关键字，则返回结果为21。 6.1.5 查询结果排序 SELECT语句获得的数据一般是没有排序的。为了方便阅读和使用，最好对查询的结果进行一次排序。SQL语言中，用于排序的是ORDER BY子句。 语法格式为: ORDER BY 表达式1 [ ASC | DESC] [,表达式2[ ASC | DESC][,…n]] 其中，表达式是用于排序的列。可以用于多列进行排序，各列在ORDER BY子句中的顺序决定了排序过程中的优先级。 Ntext、Text或Image类型的列不允许出现在ORDER BY子句中。 在默认的情况下，ORDER BY按升序进行排列即默认使用的是ASC关键字。如果用户特别要求按降序进行排列，必须使用DESC关键字。 【例19】查询pubs数据库中的表stores中商店的名字。 use pubs go select stor_name from stores go 返回的结果如下: stor_name ---------------------------------------- Eric the Read Books Barnum's News & Brews Doc-U-Mat: Quality Laundry and Books Fricative Bookshop Bookbeat (所影响的行数为 6 行) 【例20】查询pubs数据库中的表stores中商店的名字，并按商店名的降序给商店排序。 use pubs go select stor_name from stores order by stor_name go 返回的结果是: stor_name ---------------------------------------- Barnum's Bookbeat Doc-U-Mat: Quality Laundry and Books Eric the Read Books Fricative Bookshop News & Brews (所影响的行数为 6 行) 如果在某一列中使用了一个计算列，例如对某一列的值使用了函数或表达式，而用户又希望 针对该列的值进行排序，那么必须在ORDER BY子句中再包含该函数或表达式，或者使用 为该计算列临时分配的列名。 【例21】查询titles中各类书的销售利润和书号，并按照各种书的销售利润降序排列。 use pubs go select title_id,'profit'=price*ytd_sales from titles order by profit desc go 查询结果如下: title_id profit -------- --------------------- PC1035 201501.0000 TC4203 180397.2000 PC8888 81900.0000 BU1032 81859.0500 BU7832 81859.0500 PS3333 81399.2800 …… (所影响的行数为 18 行) 用户也可以根据未曾出现在 SELECT列表中的值进行排序。 【例22】查询titles中各类书的销售利润和书号，并按照各种书的价格降序排列。 use pubs go select title_id,'profit'=price*ytd_sales from titles order by price desc go 查询结果为: title_id profit -------- --------------------- PC1035 201501.0000 PS1372 8096.2500 TC3218 7856.2500 PC8888 81900.0000 PS3333 81399.2800 „„ (所影响的行数为 18 行) 也可以根据两列或多列的结果进行排序，只要用逗号分隔开不同的排序关键字就可以了。 【例23】查询titles中各类书的书号、价格、年销售量和销售利润，并用年销售量和书价进行升序排列。 use pubs go select title_id,price,ytd_sales,'profit'=price*ytd_sales from titles order by ytd_sales,price go 查询结果为: title_id price ytd_sales profit -------- --------------------- ----------- --------------------- MC3026 NULL NULL NULL PC9999 NULL NULL NULL PS2106 7.0000 111 777.0000 TC3218 20.9500 375 7856.2500 PS1372 21.5900 375 8096.2500 MC2222 19.9900 2032 40619.6800 PS2091 10.9500 2045 22392.7500 „„ (所影响的行数为 18 行) 6.1.6 使用分组 在大多数情况下，使用统计函数返回的是所有行数据的统计结果。如果需要按某一列数据的值进行分类，在分类的基础上再进行查询，就要使用GROUP BY子句了。 1(简单分组 【例24】按书的种类分类，求出3种类型书籍的价格总和、平均价格以及各类书籍的数量。 use pubs go select type,sum(price)'sum_price',avg(price) 'avg_price',count(*) from titles where type in('business','mod_cook','trad_cook') group by type go 查询结果是: type sum_price avg_price ------------ --------------------- --------------------- ----------- business 54.9200 13.7300 4 mod_cook 22.9800 11.4900 2 trad_cook 47.8900 15.9633 3 (所影响的行数为 3 行) 通过这个结果可以看出，所有的统计函数都是对查询出的每一行数据进行分类以后再进行统计计算。所以在结果集合中，对所进行分类的列的每一种数据都有一行统计结果值与之对应。 说明:GROUP BY子句中不支持对列分配的假名，也不支持任何使用了统计函数的集合列。另外，对SELECT后面每一列数据除了出现在统计函数中的列以外，都必须在GROUP BY子句中应用。 下面的查询是错误的。 use pubs select pub_id, type, avg(price)'avg_price',sum(price)'sum_price' from titles where type in ('business','trade_cook','mod_cook') group by type go 服务器返回错误信息: 服务器: 消息 8120，级别 16，状态 1，行 2 列 'titles.pub_id' 在选择列表中无效，因为该列既不包含在聚合函数中，也不包含在 GROUP BY 子句中。 我们也可以根据多列进行分组。这时统计函数按照这些列的惟一组合来进行统计计算。 【例25】按书的种类和出版商代号分类，返回一个平均价格和总价格。 use pubs go select type,pub_id,avg(price)'avg_price',sum(price)'sum_price' from titles where type in ('business','trade_cook','mod_cook') group by type,pub_id go 查询结果是: type pub_id avg_price sum_price ------------ ------ --------------------- --------------------- business 0736 2.9900 2.9900 mod_cook 0877 11.4900 22.9800 business 1389 17.3100 51.9300 (所影响的行数为 3 行) 2(使用HAVING筛选结果 当完成数据结果的查询和统计后，可以使用HAVING关键字来对查询和统计的结果进行进一步的筛选。 【例26】查询所有价格超过10美元的书的种类和平均价格。 use pubs go select type , avg(price) 'avg_price' from titles where price>$10 group by type go 查询结果如下: type avg_price ------------ --------------------- business 17.3100 mod_cook 19.9900 popular_comp 21.4750 psychology 17.5100 trad_cook 15.9633 (所影响的行数为 5 行) 【例27】在所有价格超过10美元的书中，查询所有平均价格超过18美元的书的种类和平均价格。 use pubs go select type , avg(price) 'avg_price' from titles where price>10 group by type having avg(price)>$18 go 查询的结果是: type avg_price ------------ --------------------- mod_cook 19.9900 popular_comp 21.4750 (所影响的行数为 2 行) WHERE子句在求平均值之前从表中选择所需要的行，HAVING子句在进行统计计算后产生的结果中选择所需要的行。 6.2 Transact-SQL高级查询 6.2.1 连接查询 在数据库的应用中，经常需要从多个相关的表中查询数据，这就需要使用连接查询。 1(连接(Inner join) 内连接也叫自然连接，它是组合两个表的常用方法。自然连接将两个表中的列进行比较，将两个表中满足连接条件的行组合起来，作为结果。自然连接有两种形式的语法。 语法一: SELECT 列 FROM 表1 [insert] JION 表2 ON 表1.列=表2.列 语法二: SELECT 列 FROM 表1,表2 WHERE表1.列=表2.列 【例28】从titles和titleauthor表中查询书的书号、书名、作者号、类型和价格。 use pubs go select titles.title_id,title,au_id,type,price from titles join titleauthor on titles.title_id=titleauthor.title_id 得到结果为: title_id title au_id type price -------- ------------------------------------------------------------------------- ----------- -------------- ------------------ BU1032 The Busy Executive's Database Guide 213-46-8915 business 19.9900 BU1032 The Busy Executive's Database Guide 409-56-7008 business 19.9900 BU1111 Cooking with Computers: Surreptitious Balance Sheets 267-41-2394 business 11.9500 BU1111 Cooking with Computers: Surreptitious Balance Sheets 724-80-9391 business 11.9500 BU2075 You Can Combat Computer Stress! 213-46-8915 business 2.9900 „„ (所影响的行数为 25 行) 在上述查询中titles表与titleauthor表通过title_id列进行连接，这样可以在一次查询中 从两个表获得数据。 两个以上的表也可以进行连接。 【例29】从titles、authors和titleauthor表中查询书的书号、书名、作者号和作者名。 use pubs go select titles.title_id,title,authors.au_id,au_lname from titles join titleauthor on titles.title_id=titleauthor.title_id join authors on authors.au_id=titleauthor.au_id 得到的结果为: title_id title au_id au_lname -------- -------------------------------------------------------------------------------- ----------- ---------------------- PC1035 But Is It User Friendly?238-95-7766 Carson PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 724-80-9391 MacFeather PS1372 Computer Phobic AND Non-Phobic Individuals: Behavior Variations 756-30-7391 Karsen BU1111 Cooking with Computers: Surreptitious Balance Sheets 267-41-2394 O'Leary BU1111 Cooking with Computers: Surreptitious Balance Sheets 724-80-9391 MacFeather „„ (所影响的行数为 25 行) 通过上述查询可以将titles、authors和titleauthor 组合起来，把每一本书和它的作者对应。 在从两个或两个以上的表中进行查询时，如果两个表中的列名相同，需要在列名前面加上表名(或表的别名)作为前缀，如上述例子中的titles.title_id与titleauthor.title_id。在列名不同时，列名前可以不加表名，但有时也会加上表名，以增强可读性。 2(外连接(Outer join) 在自然连接中，只有在两个表中匹配的行才能在结果集中出现。而在外连接中可以只限制一个表，而对另外一个表不加限制(即所有的行都出现在结果集中)。 外连接分为左外连接、右外连接和全外连接。左外连接是对连接条件中左边的表不加限制;右外连接是对右边的表不加限制;全外连接对两个表都不加限制，所有两个表中的行都会包括在结果集中。 左外连接的语法为: SELECT 列 FROM 表1 LEFT[OUTER]JOIN 表2 ON 表1.列1=表2.列2 右外连接的语法为: SELECT select_list FROM 表1 RIGHT[OUTER]JOIN 表2 ON 表1.列1=表2.列2 全外连接的语法为: SELECT select_list FROM 表1 FULL[OUTER] JOIN 表2 ON 表1.列1=表2.列2 例如:查询书的书号、书名 和作者的作者号、作者名。 use pubs go select title_id,title,au_id,au_lname from titles full join authors on titles.title_id=authors.au_id 查询结果如下: title_id title au_id au_lname ------------------------------------------------------------------------------------------------------ NULL NULL 267-41-2394 O'Leary NULL NULL 274-80-9391 Straight NULL NULL 341-22-1782 Smith NULL NULL 09-56-7008 Bennet NULL NULL 427-17-2319 Dull NULL NULL 472-27-2349 Gringlesby NULL NULL 86-29-1786 Locksley NULL NULL 527-72-3246 Greene NULL NULL 648-92-1872 Blotchet-Halls NULL NULL 672-71-3249 Yokomoto NULL NULL 712-45-1867 del Castillo NULL NULL 722-51-5454 DeFrance NULL NULL 724-08-9931 Stringer NULL NULL 724-80-9391 MacFeather NULL NULL 756-30-7391 Karsen NULL NULL 807-91-6654 Panteley NULL NULL 846-92-7186 Hunter NULL NULL 893-72-1158 McBadden NULL NULL 899-46-2035 Ringer NULL NULL 998-72-3567 Ringer BU1032 The Busy Executive's Database Guide NULL NULL BU1111 Cooking with Computers: Surreptitious Balance Sheets NULL NULL BU2075 You Can Combat Computer Stress! NULL NULL BU7832 Straight Talk About Computers NULL NULL „„ (所影响的行数为 41 行) 3(自连接(Self join) 连接操作不仅可以在不同的表上进行，而且在同一张表内可以进行自身连接，即将同一个表的不同行连接起来。自连接可以看作一张表的两个副本之间的连接。在自连接中，必须为表指定两个别名，使之在逻辑上成为两张表。 例如:在titles表中查询同名的作者。 use pubs go select a1.au_fname,a2.au_fname,a1.au_lname from authors a1 join authors a2 on a1.au_lname=a2.au_lname where a1.au_id<> a2.au_id 查询结果为: au_fname au_fname au_lname -------------------- -------------------- ---------------------------------------- Albert Anne Ringer Anne Albert Ringer (所影响的行数为 2 行) 4(交叉连接 交叉连接也叫非限制连接，它将两个表不加任何约束地组合起来。在数学上，就是两个表的笛卡尔积。交叉连接后得到的结果集的行数是两个被连接表的行数的乘积。 语法如下: SELECT 列 FROM 表1 CROSS JOIN 表2 或 SELECT 列 FROM 表1 ，表2 例如:查询所有书号、书名 和所有的作者号、作者名。 use pubs go select title_id,title,au_id,au_lname from titles,authors 结果为: title_id title au_id au_lname -------- -------------------------------------------------------------------------------- ----------- ------------------------- PC1035 But Is It User Friendly? 409-56-7008 Bennet PC1035 But Is It User Friendly? 648-92-1872 Blotchet-Halls PC1035 But Is It User Friendly? 238-95-7766 Carson PC1035 But Is It User Friendly? 722-51-5454 DeFrance PC1035 But Is It User Friendly? 712-45-1867 del Castillo „„ (所影响的行数为 414 行) 在实际应用中使用交叉连接产生的结果集一般没有什么意义，但在数据库的数学模式上有重 要的作用。 6.2.2 合并结果集 使用UNION语句可以把两个或两个以上的查询产生的结果集合并为一个结果集。 语法格式如下: 查询语句 UNION [ALL] 查询语句 说明: (1)UNION中的每一个查询所涉及的列必须具有相同的列数、相同的数据类型，并以 相同的顺序出现。 (2)最后结果集中的列名来自第一个SELECT语句。 (3)若UNION中包含ORDER BY子句，则将对最后的结果集排序。 (4)在合并结果集时，默认从最后的结果集中删除重复的行，除非使用ALL关键字。 例如: use pubs go select pub_id from publishers union select title from titles where price>$20 union select au_lname from authors where au_lname like 'D%' 结果为: pub_id -------------------------------------------------------------------------------- 0736 0877 1389 1622 1756 9901 9952 9999 But Is It User Friendly? Computer Phobic AND Non-Phobic Individuals: Behavior Variations DeFrance del Castillo Dull Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean (所影响的行数为 14 行) 6.2.2 子查询 子查询是指一条SELECT语句作为另一条SELECT语句的一部分，外层的SELCT语句称为外部查询，内层的SELECT语句称为内部查询(或子查询)。子查询分两种:嵌套子查询和相关子查询。 1(嵌套子查询 嵌套子查询的执行不依赖于外部嵌套。 嵌套子查询的执行过程为:首先执行子查询，子查询得到的结果集不被显示出来，而是传给外部查询，作为外部查询的条件使用，然后执行外部查询，并显示查询结果。子查询可以多层嵌套。 嵌套子查询一般也分为两种:子查询返回单个值和子查询返回一个值列表。 (1)返回单个值，该值被外部查询的比较操作(如，= 、!=、<、<=、>、>=)使用，该值可以使子查询中使用集合函数得到的值。 【例30】查询所有价格高于平均价格的书。 use pubs go select title from titles where price> (select averageprice=avg(price) from titles) 执行结果为: title -------------------------------------------------------------------------------- The Busy Executive's Database Guide Straight Talk About Computers Silicon Valley Gastronomic Treats But Is It User Friendly? Secrets of Silicon Valley Computer Phobic AND Non-Phobic Individuals: Behavior Variations Prolonged Data Deprivation: Four Case Studies Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean Sushi, Anyone? (所影响的行数为 9 行) 在这个例子中，SQL Server首先获得“select averageprice=avg(price) from titles”的结果集，该结果集为单行单列，然后将其作为外部查询的条件执行外部查询，并得到最终的结果。 【例31】查询书号为pc1035的作者的作者号、作者姓名。 use pubs go select au_id,au_lname,au_fname from authors where au_id=(select au_id from titleauthor where title_id='pc1035' ) 得到的结果为: au_id au_lname au_fname ----------- ---------------------------------------- -------------------- 238-95-7766 Carson Cheryl (所影响的行数为 1 行) 例31的查询也可以用前面讲过的表连接来实现，代码如下: use pubs go select authors.au_id,au_lname,au_fname from authors,titleauthor where authors.au_id=titleauthor.au_id and title_id='pc1035' 得到的结果与例2使用子查询一样，但连接操作要比子查询快，所以能使用表连接的时候应 尽量使用表连接。 (2)返回一个值列表，该列表被外部查询的IN、NOT IN、ANY或ALL比较操作使用。 IN表示属于，即外部查询中用于判断的表达式的值与子查询返回的值列表中的一个值相等; NOT IN表示不属于。 【例32】查询所有出版了书的作者的信息。 use pubs go select au_id,au_lname,au_fname from authors where au_id in (select au_id from titleauthor) 得到的结果为: au_id au_lname au_fname ----------- ---------------------------------------- -------------------- 172-32-1176 White Johnson 213-46-8915 Green Marjorie 238-95-7766 Carson Cheryl 267-41-2394 O'Leary Michael „„ (所影响的行数为 19 行) 在这个例子中，子查询的结果集不是单行单列，而是多行单列。 【例33】查询没有出版书的作者的信息。 use pubs go select au_id,au_lname,au_fname from authors where au_id not in (select au_id from titleauthor) 查询结果为: au_id au_lname au_fname ----------- ---------------------------------------- -------------------- 527-72-3246 Greene Morningstar 893-72-1158 McBadden Heather 341-22-1782 Smith Meander 724-08-9931 Stringer Dirk (所影响的行数为 4 行) ANY、SOME和ALL用于一个值与一组值的比较，以“>”为例，ANY 表示大于一组值中 的任意一个，ALL表示大于一组值中的每一个。比如，>ANY(1,2,3)表示大于1;而>ALL(1,2,3) 表示大于3。SOME在SQL-92标准中与ANY含义相同。 2(相关子查询 在相关子查询中，子查询的执行依赖于外部查询，多数情况下是子查询的WHERE子 句中引用了外部查询的表。 相关子查询的执行过程与嵌套子查询完全不同，嵌套子查询中子查询只执行一次，而相 关子查询中的子查询需要重复地执行。相关子查询的执行过程如下: (1)子查询为外部查询的每一行执行一次，外部查询将子查询引用的列的值传给子查 询。 (2)如果子查询的任何行与其匹配，外部查询就返回结果行。 (3)再回到第一步，直到处理完外部表的每一行。 例如:查找销售量大于平均销售量的书的书号、书名。 select title_id from sales s1 where qty > (select avg(qty) from sales s2 where s1.title_id=s2.title_id) 得到结果如下: title_id -------- BU1032 MC3021 PS2091 (所影响的行数为 3 行) 与下面的程序比较一下: select title_id from sales where qty > (select avg(qty) from sales) 运行结果为: TITLE_ID -------- PC8888 PS2091 TC3218 MC3021 PS2106 PS7777 BU2075 BU1111 PC1035 (所影响的行数为 9 行) 3(在查询的基础上创建新表 使用SELECT INTO语句可以在查询的基础上创建新表，SELECT INTO语句首先创建一个新表，然后用查询的结果填充新表。 语法格式为: SELECT 列 INTO 新表 FROM 源表 [WHERE 条件1] [GROUP BY 表达式1] [HAVING 条件2] [ORDER BY 表达式2[ASC|DESC]] 例如:建立一个新表B_TITLEAUTHOR，内容为商业类书的情况。 select title,price,au_fname,au_lname into b_titleauthor from titles t join titleauthor ta on (t. title_id=ta. title_id ) join authors a on (a.au_id=ta.au_id) where type='business' 执行结果为: (所影响的行数为 6 行) select * from b_titleauthor 得到的结果为: title price au_fname au_lname -------------------------------------------------------------------------------- --------------------- -------------------- The Busy Executive's Database Guide 19.9900 Marjorie Green The Busy Executive's Database Guide 19.9900 Abraham Bennet Cooking with Computers: Surreptitious Balance Sheets 11.9500 Michael O'Leary Cooking with Computers: Surreptitious Balance Sheets 11.9500 Stearns MacFeather You Can Combat Computer Stress! 2.9900 Marjorie Green Straight Talk About Computers 19.9900 Dean Straight (所影响的行数为 6 行) 6.3 视图 6.3.1 视图的概念 视图是从一个或多个表(或视图)导出的表。例如:对于一个学校，其学生的情况保存数据库的一个或多个表中，而作为学校的不同职能部门，所关心的学生数据内容是不同的。即使是同样的数据，也能有不同的操作要求，于是就可以根据他们的不同需求，在物理的数据库上定义他们对数据库所要求的数据结构，这种根据用户观点所定义的数据结构就是视图。 视图与表(有时为了与视图区别，也称表为基本表)不同，视图是一个虚表，即视图所对应的数据不进行实际存储。数据库中只存储视图的定义，对视图的数据进行操作时，系统根据视图的定义去操作与视图相关联的基本表。 视图一经定义后，就可以像表一样被查询、修改、删除和更新。 视图有以下优点: (1)为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理。 (2)屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据表的更改也不影响用户对数据库的使用。 (3)简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性。 (4)便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次。 (5)可以重新组织数据以便输出到其他应用程序中。 使用视图时，要注意以下事项: (1)只有在当前数据库中才能创建视图。 (2)视图的命名必须遵循标识符命名规则，不能与表同名，且对每个用户视图名必须是惟一的，即对不同用户，即使是定义相同的视图，也必须使用不同的名字。 (3)不能把规则、默认值或触发器与视图相关联。 (4)不能在视图上建立任何索引，包括全文索引。 6.3.2 创建视图 视图在数据库中是作为一个对象来存储的。创建视图前，要保证创建视图的用户已被数据库所有者授权使用CREATE VIEW语句，并且有权操作视图所涉及的表或其他视图。在SQL Server 2000中，创建视图可以在企业管理器或视图向导中进行，也可以使用T-SQL的CREATE VIEW语句。 1(在企业管理器中创建视图 以在PUBS数据库中创建B_TITLEAUTHOR(描述商业类书的情况)视图说明在企业管理器中创建视图的过程。 (1)打开“企业管理器”，在数据库PUBS上单击鼠标右键，在弹出的快捷菜单上选择“新建”?“视图”。 说明:这一步也可以展开数据库，在“视图”上单击鼠标右键，然后选择“新建视图” 菜单项。 (2)在所出现的窗口的第一个子窗口中单击鼠标右键，将弹出一个“增加表”快捷菜单，如图6-1所示。 图6-1 “增加表”快捷菜单 (3)在快捷菜单中选择“添加表”菜单项，将出现如图6-2所示的“添加表”对话框。 图6-2 “添加表”对话框 (4)在如图6-2所示的“添加表”对话框中选择与视图相关联的表、视图或函数，可以使用Ctrl或Shift键进行多选，选择完毕后，单击“添加”按钮，如图6-3所示。 图6-3 选择表、视图或函数 (5)在如图6-1所示的窗口的第二个子窗口中选择创建视图所需的字段，可以指定列的别名、排序方式和规则(本例指定TYPE字段的规则为BUSSINESS)等，如图6-4所示。注意当视图中需要一个与原字段名不同的字段名，或视图的源表中有同名的字段，或视图中包含了计算列时，需要为视图中的这样的列重新指定名称。 图6-4 选择列 说明:这一步所选择的字段、规则等的情况相对应的SELECT语句将会自动显示在第三个小窗口中。也可以直接在该小窗口输入SELECT语句。 (6)上一步完成后，单击“保存”按钮，出现如图6-5所示的保存视图对话框。在其中输入视图名，并单击“确定”按钮，便完成了视图的创建。 视图一经创建成功，其中便包含了所选择的列数据。例如，若创建了B_TITLEAUTHOR视图，则可查看其结构及内容:在视图上单击鼠标右键，选择“设计视图”功能项可以查看并修改视图结构:选择“打开视图”?“返回所有行”将可查看视图的数据内容。 2(通过视图向导创建视图 (1)打开“企业管理器”，选择“工具”菜单中的“向导”菜单项，出现如图6-6所示的“选择向导”对话框。 图6-5 保存视图对话框 图6-6 “选择向导”对话框 (2)在“选择向导”对话框中展开“数据库”，选择“创建视图向导”菜单项，单击“确定”按钮，将出现欢迎使用向导界面。 (3)在欢迎使用向导界面中单击“下一步”，出现如图6-7所示的选择数据库对话框。选择数据库名称，单击“下一步”，出现选择对象对话框。 图6-7 选择数据库 (4)在如图6-8所示的选择对象对话框中选择与视图相关联的表，单击“下一步”，出现如图6-9所示的选择列对话框。 图6-8 选择对象对话框 图6-9 选择列对话框 (5)在选择列对话框中选择视图所需要的列，单击“下一步”，出现如图6-10所示的视图限制对话框。 图6-10 输入视图限制对话框 (6)在视图限制对话框中输入对视图的限制条件，本例为:WHERE TYPE='BUSSINESS'，单击“下一步”，出现如图6-11所示的输入视图名对话框。 图6-11 输入视图名对话框 (7)在输入视图名对话框中输入视图名，单击“下一步”，出现如图6-12所示的视图创建完成对话框，单击“完成”，则视图创建完毕。 图6-12 视图创建完成对话框 3(使用CREATE VIEW语句创建视图 T-SQL语言中用于创建视图的语句是CREATE VIEW语句。 语法格式为: CREATE VIEW 视图名[(列名1,列名2[,…n])] [WITH ENCRYPTION]] AS 查询语句 [WITH CHECK OPTION] 其中: 列名:视图中包含的列，可以有多个列名，最多可引用1024个列。若使用与源表或视图中相同的列名时，则不必给出列名。 ENCRYPTION:说明在系统表syscomments中存储CREATE VIEW语句时进行加密。 查询语句:用来创建视图的SELECT语句。可在SELECT语句中查询多个表或视图，以表明新创建的视图所参照的表或视图，但对SELECT语句有以下的限制: ? 定义视图的用户必须对所参照的表或视图有查询权限，即可执行SELECT语句。 ? 不能使用COMPUTE或COMPUTE BY子句。 ? 不能使用ORDER BY子句。 ? 不能使用INTO子句。 ? 不能在临时表或表变量上创建视图。 WITH CHECK OPTION:指出在视图上所进行的修改都要符合查询语句所指定的限制条件，这样可以确保数据修改后仍可通过视图看到修改的数据。例如对于B_TITLEAUTHOR视图，只能修改除TYPE字段以外的字段值，而不能把TYPE字段的值改为BUSSINESS以外的值，以保证仍可通过B_TITLEAUTHOR视图查询到修改后的数据。 【例34】创建视图B_TITLEAUTHOR。 use pubs go create view b_titleauthor as select title,price,au_fname,au_lname from titles t join titleauthor ta on (t. title_id=ta. title_id ) join authors a on (a.au_id=ta.au_id) where type='business' go 创建视图时，源表可以是基本表，也可以是视图。 【例35】创建商业类书的平均价格视图B_TITLEAUTHOR_AVG。 use pubs go create view b_titleauthor_avg(price_avg) as select avg(price) from b_titleauthor 6.3.3 查询视图 视图定义后，就可以像查询基本表那样对视图进行查询。 【例36】查询商业类书的书名和作者姓名。 use pubs go select title,au_lname,au_fname from b_titleauthor 执行结果为: title au_lname au_fname -------------------------------------------------------------------------------- ---------------------------------------- The Busy Executive's Database Guide Green Marjorie The Busy Executive's Database Guide Bennet Abraham Cooking with Computers: Surreptitious Balance Sheets O'Leary Michael Cooking with Computers: Surreptitious Balance Sheets MacFeather Stearns You Can Combat Computer Stress! Green Marjorie Straight Talk About Computers Straight Dean (所影响的行数为 6 行) 【例37】查找商业类书的平均价格。 use pubs go select * from b_titleauthor_avg 执行结果为: price_avg --------------------- 14.4766 (所影响的行数为 1 行) 从以上两例可以看出，创建视图可以向最终用户隐藏复杂的表连接，简化了用户的SQL程序设计。视图还可通过在创建视图时指定限制条件和指定列限制用户对基本表的访问。例如若限定某用户只能查询视图B_TITLEAUTHOR ，实际上就是限制它只能访问TITLES表的TYPE字段值为BUSSINESS的行。在创建视图时可以指定列，实际上也就是限制了用户只能访问这些列，从而视图也可看做数据库的安全设施。 使用视图查询时，若其关联的基本表中添加了新字段，则必须重新创建视图才能查询到 新字段。 如果与视图相关联的表或视图被删除，则该视图将不能再使用。 6.3.4 更新视图 通过更新视图数据(包括插入、修改和删除)可以修改基本表数据，但并不是所有的视图都可以更新，只有对满足更新条件的视图才能进行更新。 1(插入数据 使用INSERT语句通过视图向基本表插入数据。 【例38】创建视图B_TITLE(包括TITLE_ID、TITLE和PRICE3个字段)，并向B_TITLE视图中插入一条记录:("BU1122","Fishing with computer",56.00)。 use pubs go create view b_title as select title_id,title,price from titles where type='bussiness' go insert b_title values('BU1122','Fishing with computer',56.00) 使用SELECT语句查询基本表TITLES select* from titles 将会看到该表已添加了(BU1122 Fishing with computer UNDECIDED NULL 56.0000 NULL NULL NULL NULL)行。 当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。例如不能向视图B_TITLEAUTHOR插入数据，因为B_TITLEAUTHOR依赖三个基本表:TITLES、TITLEAUTHOR和AUTHORS。 2(修改数据 使用UPDATE语句可以通过视图修改基本表的数据。 例如:将B_TITLE视图中所有书的价格增加8美元。 update b_title set price=price+8 该语句实际上是将B_TITLE视图所依赖的基本表TITLES中所有商业类书的PRICE字 。 段值在原来基础上增加8 若一个视图依赖于多个基本表，则一次修改该视图只能变动一个基本表的数据。 3(删除数据 使用DELETE语句可以通过视图删除基本表的数据。但要注意，对于依赖于多个基本表的视图，不能使用DELETE语句。例如，不能通过对B_TITLEAUTHOR视图执行DELETE语句而删除与之相关的基本表TITLES、TITLEAUTHOR和AUTHORS 表的数据。 例如:删除B_TITLE中价格小于$10的记录。 delete from b_title1 where price<10 可以通过视图删除其所依赖的基本表的数据。 对视图的更新操作也可通过企业管理器的界面进行，操作方法与对表数据的插入、修改和删除的界面操作方法基本相同，在此不在举例说明。 6.3.5 删除视图 1(使用企业管理器删除视图 在企业管理器中删除视图的操作方法是:展开数据库和视图，在需删除的视图上单击鼠标右键，在弹出的快捷菜单上选择“删除”，出现如图6-13所示的对话框，再单击“全部除去”按钮即可删除指定的视图。 图6-13 删除视图 2(使用T-SQL语句删除视图 删除视图的T-SQL语句是DROP VIEW。 语法格式为: DROP VIEW 视图名[,…n] 使用DROP VIEW一次可删除多个视图。例如: drop view titl1,b_title 将删除视图TITLE1和B_TITLE。 第8章 备份与恢复 8.1 备份概述 8.1.1 备份的概念 备份和恢复组件是SQL Server 2000 的重要组成部分。备份就是指SQL Server 2000 数据库或事务日志进行拷贝，数据库备份记录了在进行备份这一操作时数据库中所有数据的状态 ，如果数据库因意外破坏而损坏，这些备份文件将在数据库恢复时用来恢复数据库。 由于SQL Server 2000支持在线备份，所以通常情况下可以一边进行备份，一边进行其他操作，但是在备份过程中不允许执行以下操作: (1)创建或删除数据库文件。 (2)创建索引。 (3)执行非日志操作。 (4)自动或手工缩小数据库或数据库文件大小。 如果以上各种操作正在进行当中，且准备进行备份，则备份处理将被终止:如果在备份过程中，打算执行以上任何操作，则操作将失败而备份继续进行。 进行备份和恢复的工作主要是由数据库管理员来完成的。实际上数据库管理员日常比较重要、比较频繁的工作就是对数据库进行备份和恢复。 8.1.2 备份类型 SQL Server 支持的备份类型包括: (1)完整数据库备份。它备份包括事务日志的整个数据库。 (2)异数据库备份。在完整数据库备份之间执行差异数据库备份。 (3)事务日志备份。日志备份序列提供了连续的事务信息链，可支持从数据库、差异或文件备份中快速恢复。 (4)文件和文件组备份。当时间限制使得完整数据库备份不切实际时，请使用BACKUP 备份数据库文件和文件组，而不是备份完整数据库。若要备份一个文件而不是整个数据库时，请合理安排步骤以确保数据库中所有的文件按规则备份，同时必须进行单独的事务日志备份。在恢复一个文件备份后，使用事务日志将文件内容前滚，使其与数据库其余部分一致。 8.1.3 备份设备 创建备份时，必须选择存放备份数据的备份设备。SQL Server 2000 将数据库、事务日志和文件备份到磁盘和磁带设备上。 1(磁盘设备 磁盘备份设备是硬盘或其他磁盘存储媒体上的文件，与常规操作系统文件一样。引用磁盘备份设备与引用任何其他操作系统文件一样。可以在服务器的本地磁盘上或共享网络资源的远程磁盘上定义磁盘备份设备，磁盘备份设备根据需要可大可小。最大的文件大小相当于 磁盘上可用的闲置空间。 2(命名管道设备 这是微软专门为第三方软件供应商提供的一个备份和恢复方式，命名管道设备不能通过企业管理器来建立和管理，若要将数据备份到一个命名管道设备，必须在BACKUP语句中提供管道的名字。 3(磁带设备 磁带备份设备的用法与磁盘设备相同，但必须将磁带设备物理连接到运行 SQL Server 实例的计算机上。不支持备份到远程磁带设备上。 如果磁带备份设备在备份操作过程中已满，但还需要写入一些数据，SQL Server 将提示更换新磁带并继续备份操作。 若要将 SQL Server(或Microsoft Windows NT 4.0 或 Windows 2000)数据备份到磁带，使用Windows NT 4.0或Windows 2000支持的磁带备份设备或磁带驱动器。另外，对特定的磁带驱动器只使用推荐的磁带(按照驱动器制造商的建议)。 4(物理和逻辑设备 SQL Server 使用物理设备名称或逻辑设备名称标识备份设备。 物理备份设备是操作系统用来标识备份设备的名称，如C:\Backups\Accounting\Full.bak。 逻辑备份设备是用来标识物理备份设备的别名或公用名称。逻辑设备名称永久地存储在 SQL Server 内的系统表中。使用逻辑备份设备的优点是引用它比引用物理设备名称简单。 例如，逻辑设备名称可以是Accounting_Backup，而物理设备名称则是C:\Backups\Accoun- ting\Full.bak。 备份或恢复数据库时，可以交替使用物理或逻辑备份设备名称。 8.1.4 备份的策略与规划 SQL Server 2000的备份和还原组件使得可以创建数据库的副本。可将此副本存储在某个位置，以便一旦运行 SQL Server 实例的服务器出现故障时使用。如果运行 SQL Server 实例的服务器出现故障，或者如果数据库遭到某种程度的损坏，可以用备份复本重新创建或还原数据库。 SQL Server 2000 有关如何备份和还原数据库的选项如下: (1)完整数据库备份是数据库的完整副本。 (2)事务日志备份仅复制事务日志。 (3)差异备份仅复制自上一次完整数据库备份之后修改过的数据库页。 (4)文件或文件组还原仅允许恢复数据库中位于故障磁盘上的那部分。 1(完全 数据非常重要并且必须能够恢复到故障点。记录所有的数据修改。可使用 SQL Server 2000 的所有恢复选项。 2(大容量日志记录 如有必要，可重播某些大容量操作(大容量复制操作、SELECT INTO、文本处理)，因此不完全记录这些操作。只能恢复到上一次数据库或日志备份的末尾。 3(简单 自上次备份后所做的所有数据更改都是可替代的，或是可重做的。记录开销最小，但不能恢复自上次备份结束后的内容。 8.2 备份数据库 8.2.1 数据库备份设备 1(使用企业管理器创建逻辑磁盘备份设备 使用企业管理器创建逻辑磁盘备份设备的步骤如下: (1)找到服务器上的“管理”文件夹。 (2)展开该文件夹，右击“备份”，单击“新建备份设备”命令。弹出如图10-1所示的对话框。 图10-1 备份设备属性对话框 (3)在“名称”框中输入该命名备份设备的名称。 (4)单击“文件名”，然后执行下列操作之一: 输入磁盘备份设备所使用的文件名。 单击“浏览”(...)按钮显示“备份设备位置”对话框，再选择磁盘备份设备所使用的本地计算机上的物理文件。 (5)单击“确定”创建备份设备。 2(使用系统存储过程 sp_addumpdevice完成数据库备份的创建 语法如下: sp_addumpdevice [ @devtype = ] 'device_type' , [ @logicalname = ] 'logical_name' , [ @physicalname = ] 'physical_name' [ , { [ @cntrltype = ] controller_type | [ @devstatus = ] 'device_status' } ] 其中: [@devtype =] 'device_type' 指定设备类型。 [@logicalname =] 'logical_name' 指定设备的逻辑名称。 [@physicalname =] 'physical_name' 指定设备的路径和文件名。 例如:这是一个网络连接的设备备份。 USE master EXEC sp_addumpdevice 'disk', 'networkdevice', '\\servername\sharename\path\filename.exe' go 8.2.2 备份数据库 1(使用企业管理器进行数据库备份 通过企业管理器进行数据库备份的步骤如下: 1)找到创建数据库备份的服务器。 ( (2)展开该服务器节点。 (3)选中指定的数据库，在工具菜单中选择“备份数据库”命令或右键单击要进行备份的数据库图标，在弹出的菜单中选择“所有任务”，再选择“备份数据库”，则弹出如图10-2所示的对话框。 图10-2 备份对话框 (4)选择备份的数据库，输入备份的名称和备份的描述，选择备份的类型，如果是针对文件或文件组进行备份，可以单击右边的按钮选择要备份的文件或文件组。 (5)单击“添加”按钮选择要备份的设备，弹出如图10-3所示的对话框，在该对话框上用户可以创建新的备份设备，也可以选择多个备份设备，一次备份到多个设备上。单击“确定”按钮完成添加。 图10-3 选择备份设备 (6)用户可以在“重写”选项组中，选择在设备上进行操作的方式，可以选择“追加到媒体”将新的备份添加到备份设备中，也可以选择“重写现有媒体”用新的备份来覆盖原来的备份。 (7)选择“调度”复选框，可对备份的时间表进行设置。单击右面的按钮改变备份的时间安排，在弹出的“编辑调度”对话框中设定备份操作何时进行，如图10-4所示。 图10-4 “编辑调度”对话框 (8)在“名称”中输入备份安排任务的名字，默认为 第1调度，在“调度类型”选项中可以单击“更改”按钮改变当前默认的备份时间设置，然后会弹出“编辑反复出现的作业调度”对话框，如图10-5所示。 图10-5 “编辑反复出现的作业调度”对话框 在“编辑反复出现的作业调度”对话框中按自己的要求设置备份的时间，然后单击“确定”按钮完成时间设置。 (9)在数据库备份对话框中选择“选项”选项卡进行附加设置，选择“选项”表后的对话框如图10-6所示。在选项栏中通过单击复选框进行选择，如果选中了“完成后验证备份”，表示SQL Server 会阅读全部备份，检查备份介质是否可读，保证介质的完整性。 (10)单击“确定”按钮完成备份。 2(使用T-SQL语句创建数据库备份 执行BACKUP DATABASE语句以创建数据库备份 语法格式如下: BACKUP DATABASE 数据库名[ 文件或文件组 [ ,...n ]] TO 备份设备 [ ,...n ] 图10-6 “选项”选项卡 【例1】创建用于存放 MyNwind 数据库完整备份的逻辑备份设备。 --创建备份设备 exec sp_addumpdevice 'disk', 'mynwind_1', disk ='c:\program files\microsoft sql server\mssql\backup\ lwzz _1.dat' --进行全面备份 backup database mynwind to mynwind_1 【例2】将整个lwzz数据库备份到磁带上。 use lwzz go backup database lwzz to tape = '\\.\tape0' with format, name = 'full backup of lwzz' go