第4章 SQL Server 2008学习案例

SQL Server数据库平台产品，作为一种成熟的企业技术，已经应用于各行各业。微软公司为了更好地展示该产品的新特性和新功能，在每次发布产品的同时，都会向用户提供完整的示例，方便用户理解和应用该产品。例如，在SQL Server 2000数据库产品中，提供了Northwind和pubs示例数据库。同样， SQL Server 2008数据库在发布时，微软公司也提供了一系列的经典示例。本章讲述的知识点有：

❑ 下载和安装实例。

❑ 帮助用户理解和使用这些示例数据库的内容。

❑ 学习SQL Server 2008中数据库架构的设计。

❑ 了解基本的SQL语法知识。

4.1 AdventureWorks 2008示例数据库介绍

AdventureWorks2008示例数据库是SQL Server 2008数据库产品的示例数据库。它是以虚拟的大型、生产型跨国公司为背景的企业应用数据库。该示例数据库在其设计和管理上，有许多独到之处，非常适合学习、测试和分析研究，该示例有利于理解和应用SQL Server 2008数据库的各种新特性。

4.1.1 案例背景

SQL Server 2008数据库引入的AdventureWorks2008示例数据库是以名为Adventure Works Cycles （以下称为AW自行车）的虚拟公司为背景的。主要包括AdventureWorks2008示例OLTP数据库、AdventureWorksDW示例数据仓库和AdventureWorksAS示例分析服务数据库。由于本书着重于数据库的基础知识，所以着重以OLTP示例数据库为主进行说明。

AdventureWorks2008示例数据库，是以AW自行车虚构公司为背景设计的数据库。虚构的公司是一家大型跨国生产公司，其产品主要包括生产金属和复合材料的自行车。公司总部设在华盛顿州，有数百名雇员；该公司在世界各地都建立了区域性的销售团队。

4.1.2 安装示例

SQL Server 2008中的示例数据库包括联机事务处理（OLTP）、联机分析处理（OLAP）和数据仓库示例数据库及其附带例程。默认情况下，这些示例不安装在SQL Server 2008数据库中。用户可以通过以下方式安装该示例数据库。

（1）在浏览器中输入“http://msftdbprodsamples.codeplex.com/Release/ProjectReleases.aspx? ReleaseId=18407”，打开示例数据库的下载页面，如图4.1所示。

图4.1 下载安装包

（2）下载完成后的文件名是SQL2008.AdventureWorks_All_Databases.x86.msi，因为扩展名是.msi，所以可以直接双击运行。打开安装向导后的第一个界面是欢迎界面，直接单击“Next”按钮即可。

（3）打开安装协议，必须选择“接收”复选框，然后单击“Next”按钮。

（4）此时打开自定义安装项目对话框，如图4.2所示。首先单击“Browse...”按钮选择安装路径，然后在列表框中选择要安装的文件，这里选择默认即可。最后单击“Next”按钮。

图4.2 自定义安装项目

（5）现在打开数据库安装页面，需要选择数据库所在的服务器名称，如图4.3所示。读者的机器上是自己在安装时定义的名称，可能与本例不同。

图4.3 选择服务器

（6）单击“Next”按钮，出现准备安装界面，然后单击“Install”按钮。安装过程非常快，因为只是几个数据库文件，所以等待几分钟后，就可以出现安装完成界面，直接单击“Finish”按钮就可以。

安装完成后，如果已经打开了SQL Server Management Studio管理器，则需要关闭再重新打开，安装完后的对象资源管理器效果如图4.4所示。其中，AdventureWorks2008是专门为支持SQL Server 2008而设计的数据库。

图4.4 安装示例后

4.2 AdventureWorks 2008示例数据库的结构

本节介绍AdventureWorks 2008示例数据库的结构、查看这些基础结构的常用操作。AdventureWorks 2008数据库结构，主要包括数据类型、架构与表、存储过程和函数。其中，用户自定义的数据类型是该数据库设计的基础，架构和表是数据库的数据存储的主体，存储过程和函数定义了数据的业务完整性和部分业务处理逻辑。

4.2.1 AdventureWorks 2008数据库的数据类型

与所有数据库一样，AdventureWorks 2008数据库以各种数据类型存储业务数据，这包括系统提供的数据类型和用户自定义的数据类型。

1. 数据类型的基础信息

AdventureWorks 2008示例数据库，根据业务逻辑的需求，使用了SQL Server 2008提供的绝大多数数据类型，并定义了多个别名数据类型。

数据类型可以使用“标量类型目录视图”查看数据类型的基础信息。标量类型目录视图包括“sys.types”和“sys.assembly_types”，其中“sys.types”视图中，每一行数据都代表一个系统类型或者用户定义类型。

【实例4.1】下面列举一个使用sys.types目录视图，查看示例数据库中包括的数据类型结构信息的简单示例。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入代码如下，并单击“执行”按钮。

        01 USE AdventureWorks2008
        02 GO
        03 SELECT * FROM sys.types
        04 GO

【代码说明】

❑ USE AdventureWorks 2008表示打开AdventureWorks 2008数据库，对数据库中数据对象的操作，都要求先打开数据库再操作。

❑ 通过以上代码，用户可以获取AdventureWorks 2008数据库所使用的数据类型。

【运行效果】

运行结果如图4.5所示。可以在结果中通过移动滚动条，查看is_user_defined字段，以区分该项数据类型是否是用户创建的。

图4.5 AdventureWorks2008数据库使用的数据类型

2. 系统数据类型

AdventureWorks 2008示例数据库使用了以下系统数据类型：bit、char或nchar、datetime、decimal、int、money、numeric、smallint、smallmoney、sysname、tinyint、uniqueidentifier（GUID）、varbinary(max)、varchar或nvarchar、varchar(max)、nvarchar(max)、xml、date、time等，其中date、time是SQL Server 2008独有的类型。

3. 别名数据类型

AdventureWorks2008数据库中定义了一系列的别名数据类型。这些类型的使用，有助于更好地管理表中的数据，规范数据的存储。AdventureWorks 2008数据库中的别名数据类型如表4.1所示。

表4.1 别名数据类型

4. 查看AdventureWorks 2008数据表的数据类型

【实例4.2】查看Adventure Works2008数据库中“Production.Product数据表”的数据类型，可以按以下操作步骤（查看其他数据表也可参考以下操作步骤）进行。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。

（2）在“对象资源管理器”中，连接到SQL Server 2008数据库引擎实例，再展开该实例。

（3）在“对象资源管理器”的数据库目录，AdventureWorks 2008数据库节点的表目录下，右击数据表“Production.Product”，在弹出的快捷菜单中选择“设计”命令。

（4）在打开的“表设计器”中，用户可以看到数据表中所有列的数据类型，如图4.6所示。

图4.6 数据表设计器

4.2.2 AdventureWorks 2008数据库的架构与数据表

SQL Server 2008通过“架构”这一数据对象，设定数据表的命名空间。数据表通过“架构”进行分类，它是存储业务数据的核心。本小节就SQL Server 2008数据库中的架构，结合AdventureWorks 2008数据库进行说明，并通过部分示例代码说明常用的查看操作。

1. 查看示例数据库的架构

AdventureWorks2008示例数据库，将该企业的数据，根据业务类型，划分为若干部分，定义为不同的架构。主要划分为人力资源、产品、采购、生产等架构。

【实例4.3】用户可以使用“架构目录视图”查看架构的基础信息，架构目录视图为“sys.schemas”，使用架构目录视图查看架构信息可参考如下步骤。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 use AdventureWorks2008
        02 go
        03 select * from sys.schemas where principal_id = 1
        04 go

【代码说明】

❑ principal_id表示此架构所属的主体数据库的ID，关于架构会在后面的章节进行详细说明。

❑ 通过以上代码，用户可以获取AdventureWorks 2008数据库中的数据架构列表。

【运行效果】

执行上述代码可以得到如图4.7所示的架构，包括dbo、HumanResources、Person、Production、Purchasing、Sales架构，其中dbo为默认架构。

图4.7 AdventureWorks 2008数据库架构

2. 示例数据库架构说明

AdventureWorks 2008示例数据库，包含上述6个架构，每个架构代表一类业务数据。架构及其业务说明如表4.2所示。

表4.2 AdventureWorks 2008架构列表

【实例4.4】查看AdventureWorks 2008示例数据库中某一架构中包含的数据表，可参考如下代码：

        01 USE AdventureWorks 2008
        02 GO
        03 SELECT t.name   FROM sys.tables t ,sys.schemas m WHERE t.schema_id = m.schema_id
                  and m.name = 04   'HumanResources'
        05 GO

【代码说明】

❑ 在以上实例中sys.tables和sys.schemas存在一种关系，sys.tables的架构ID来自于sys.schema目录视图。

❑ 用户可以替换架构名'HumanResources'，以查看其他架构中包含的数据表。

【运行效果】

查看HumanResources架构中包含的数据表的结果，即上述代码的执行结果，如图4.8所示。

图4.8 'HumanResources'架构包含的数据表名

3. HumanResources架构

HumanResources架构包含Department、Employee、EmployeeDepartmentHistory、EmployeePayHistory、JobCandidate、Shift数据表。对这些数据表进行概要说明如下。

❑ Department数据表保存了示例公司的部门信息。

❑ Employee数据表保存了公司的雇员信息。

❑ EmployeeDepartmentHistory数据表保存了雇员及其所在部门的当前和历史数据。

❑ EmployeePayHistory数据表保存了雇员的当前和历史工资信息。

❑ JobCandidate数据表保存了申请人提交给人力资源部门的简历信息。

❑ Shift包含工作轮班时间的数据表。

4. Person架构

Person架构包含Address、AddressType、CountryRegion、StateProvince等数据表。下面对这些数据表进行概要说明。

❑ Address数据表保存了所有示例公司的客户、供应商和雇员的地址信息。客户和供应商可能具有多个地址。

❑ AddressType数据表是一个定义客户、供应商或雇员的地址类型的数据表。

❑ BusinessEntity数据表定义客户、供应商或雇员的实体数据表。

❑ BusinessEntityAddress数据表定义客户、供应商或雇员等地址信息的实体数据表。

❑ BusinessEntityContact数据表定义客户、供应商或雇员等联系信息的实体数据表。

❑ ContactType数据表保存了存储在表中的联系人的类型。

❑ CountryRegion数据表保存了国际上用来标识国家和地区的标准代码。

❑ EmailAddress数据表定义客户、供应商或雇员等的电子邮箱。

❑ Password数据表定义密码和密码加密方式。

❑ Person数据表定义客户、供应商或雇员等的姓名信息。

❑ PersonPhone数据表定义客户、供应商或雇员等的电话信息。

❑ PhoneNumberType保存电话的类型。

❑ StateProvince数据表保存了用于标识国家/地区中的省、市、自治区的国际标准代码的表。

5. Production架构

Production架构包含了ProductProductPhoto、ProductReview、TransactionHistory、ProductSubcategory、TransactionHistoryArchive、BillOfMaterials、UnitMeasure、WorkOrder、WorkOrderRouting、Culture、Document、Illustration、Location、Product、ScrapReason、ProductCategory、ProductCostHistory、ProductDescription、ProductDocument、ProductInventory、ProductListPriceHistory、ProductModel、ProductModelIllustration、ProductModelProductDescription-Culture、ProductPhoto数据表。下面对这些数据表进行概括性地说明。

❑ ProductProductPhoto数据表是关联产品和产品图像的交叉数据表。

❑ ProductReview数据表保存了客户对产品的评论。

❑ TransactionHistory数据表保存了当前年度的各采购订单、销售订单或工作订单事务。

❑ ProductSubcategory数据表保存了产品的子类别。

❑ TransactionHistoryArchive数据表保存了历史采购订单、销售订单或工作订单事务的所有记录。

❑ BillOfMaterials数据表保存了用于生产自行车及其子部件的所有组件。

❑ UnitMeasure数据表保存了标准测量单位的代码和说明的数据表。

❑ WorkOrder数据表保存了生产工作订单。

❑ WorkOrderRouting数据表保存了生产工作订单详细信息。

❑ Culture数据表保存了存储产品说明的语言信息。

❑ Document数据表保存了Microsoft Office Word格式的产品维护文档。

❑ Illustration数据表保存了作为.xml文件存储的产品部件关系图。

❑ Location数据表保存了产品库存和生产地点的数据表。

❑ Product数据表保存了售出的或在售出产品的生产过程使用的产品。

❑ ProductCategory数据表保存了产品的详细分类。

❑ ProductCostHistory数据表保存了历史产品成本。

❑ ProductDescription数据表保存了多种语言的产品说明。

❑ ProductDocument数据表保存了产品关联到相关产品文档的交叉引用表。

❑ ProductInventory数据表保存了产品库存信息。

❑ ProductListPriceHistory数据表保存了历史上对产品的标价所作的更改。

❑ ProductModel数据表保存了产品型号分类以及产品类别和生产说明。

❑ ProductModelIllustration数据表保存了定义产品型号和图示的关联关系的表。

❑ ProductModelProductDescriptionCulture数据表保存了产品型号说明和说明所用语言的表。

❑ ProductPhoto数据表保存了产品的图像。

6. Purchasing架构

Production架构包含了ProductVendor、Vendor、PurchaseOrderDetail、PurchaseOrderHeader、ShipMethod数据表。

❑ ProductVendor数据表保存了将供应商关联到其提供给公司的产品的信息。

❑ Vendor数据表保存了公司向其购买零件或其他商品的公司。

❑ PurchaseOrderDetail数据表保存了每个采购订单要采购的产品，一个订单可以订购多个产品。

❑ PurchaseOrderHeader数据表保存了常规或父级采购订单信息，详情在PurchaseOrderDetail表中。

❑ ShipMethod数据表保存了发货公司的字典。

7. Sales架构

Sales架构包含了CountryRegionCurrency、CreditCard、Currency、SalesOrderDetail、CurrencyRate、Customer、SalesOrderHeader、SalesOrderHeaderSalesReason、SalesPerson、SalesPersonQuotaHistory、SalesReason、SalesTaxRate、SalesTerritory、SalesTerritoryHistory、ShoppingCartItem、SpecialOffer、SpecialOfferProduct、Store数据表。

❑ CountryRegionCurrency数据表保存了国际标准化组织（ISO）的货币代码关联到其他国家或地区。

❑ CreditCard数据表保存了客户的信用卡信息。

❑ Currency数据表保存了国际标准化组织的货币说明。

❑ SalesOrderDetail数据表保存了与特定销售订单关联的各个产品，一个销售订单可以订购多个产品。

❑ CurrencyRate数据表保存了外币汇率字典信息。

❑ Customer数据表保存了当前的客户信息，客户可分为个人客户或零售商店。

❑ SalesOrderHeader数据表保存了常规或父级销售订单信息。

❑ SalesOrderHeaderSalesReason数据表保存了定义销售订单与销售原因代码的信息字典。

❑ SalesPerson数据表保存了当前销售代表的销售信息。

❑ SalesPersonQuotaHistory数据表保存了销售代表的历史销售信息。

❑ SalesReason数据表保存了包含客户采购特定产品的原因的字典。

❑ SalesTaxRate数据表是一个税率字典。

❑ SalesTerritory数据表保存了由本公司销售团队负责的销售地区的字典。

❑ SalesTerritoryHistory数据表保存了跟踪将销售代表调动到其他销售地区的情况。

❑ ShoppingCartItem数据表保存了尚未提交或取消的在线客户订单。

❑ SpecialOffer数据表保存了包含销售折扣的字典。

❑ SpecialOfferProduct数据表保存了将产品关联到销售折扣的信息。

❑ Store数据表保存了客户和经销商信息。

4.2.3 AdventureWorks 2008数据库的存储过程

本节介绍的AdventureWorks 2008数据库中，使用T-SQL语言编写存储过程。主要的存储过程如表4.3所示。

表4.3 AdventureWorks 2008数据库的T-SQL存储过程

1. 使用dbo.uspGetBillOfMaterials存储过程的示例

【实例4.5】以下实例使用dbo.uspGetBillOfMaterials存储过程，返回产品ID为811的产品信息，具体操作请参考如下实例。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008;
        02 GO
        03 DECLARE @dateTemp DATETIME;
        04 SET @dateTemp = GETDATE();
        05 EXEC [AdventureWorks2008].[dbo].[uspGetBillOfMaterials] 811, @dateTemp;
        06 GO

【代码说明】

❑ 以上实例运行dbo.uspGetBillOfMaterials存储过程。

❑ 该过程用来返回产品(ProductID 811)的组件的层次列表。

❑ GETDATE()为取得当前日期的函数。

❑ EXEC用于执行存储过程。

【运行效果】

执行结果如图4.9所示。

图4.9 执行dbo.uspGetBillOfMaterials存储过程

2. 使用dbo.uspGetEmployeeManagers存储过程的示例

【实例4.6】以下实例使用dbo.uspGetEmployeeManagers存储过程，返回编号为3的员工的全部领导，请参考如下实例。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 EXEC dbo.uspGetEmployeeManagers 3;
        04 GO

【代码说明】

❑ 以上实例运行uspGetEmployeeManagers存储过程。

❑ 该过程返回EmployeeID 3的直接和间接领导的层次列表。

【运行效果】运行结果如图4.10所示。

图4.10 执行dbo.uspGetEmployeeManagers存储过程

3. 使用dbo.uspGetManagerEmployees存储过程的示例

【实例4.7】使用dbo.uspGetManagerEmployees存储过程，返回编号为185经理有哪些下属的员工，这些员工要求包括直接的下属和间接的下属，请参考如下实例。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks 2008;
        02 GO
        03 EXEC dbo.uspGetManagerEmployees 185;
        04 GO

【代码说明】

❑ 以上实例第3行运行uspGetManagerEmployees存储过程。

❑ 该过程返回ManagerID 185的直接和间接下属的层次列表。

【运行效果】

运行结果如图4.11所示。

图4.11 执行dbo.uspGetManagerEmployees存储过程

4.2.4 AdventureWorks 2008数据库的用户定义函数

用户也可以根据需要编写自己的函数，表4.4中列出了AdventureWorks 2008示例OLTP数据库中包含的一些使用T-SQL编写的用户定义函数。

表4.4 AdventureWorks 2008数据库的T-SQL存储过程

1. 使用dbo.ufnLeadingZeros函数的示例

【实例4.8】使用dbo.ufnLeadingZeros函数，将数字型的客户编号转成8位的字符型客户号，请参考如下实例。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks 2008;
        02 GO
        03 SELECT CustomerID AS Id,
        04    ('CN' + dbo.ufnLeadingZeros(CustomerID)) AS NewAccountNumber
        05 FROM Sales.Customer
        06 ORDER BY CustomerID;
        07 GO

【代码说明】

❑ 以上实例根据Customer表的现有CustomerID列来生成客户账号。

❑ 代码第4行使用ufnLeadingZeros函数产生格式化后的客户账号。

【运行效果】

运行结果如图4.12所示。

图4.12 使用ufnLeadingZeros函数

2. 使用dbo.ufnGetContactInformation函数的示例

【实例4.9】使用dbo.ufnGetContactInformation表值函数，该函数用于返回一张数据表。本示例中使用SELECT语句返回编号为100的联系人的信息，请参考如下实例。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks 2008;
        02 GO
        03 SELECT PersonID, FirstName, LastName, JobTitle, BusinessEntityType
        04 FROM dbo.ufnGetContactInformation(100);
        05 GO

【代码说明】

❑ 示例数据库将雇员、客户和供应商代表的姓名存储在Person表中。

❑ 表值函数的返回值可以作为数据表使用。第4行的表值函数ufnGetContactInformation根据指定的PersonID返回一行。

【运行效果】

运行结果如图4.13所示。

图4.13 使用ufnGetContactInformation函数

3. 使用dbo.ufnGetProductDealerPrice函数的示例

【实例4.10】使用dbo.ufnGetProductDealerPrice函数，该函数按折扣率60%的作为批发价返回给执行者，请参考如下实例。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks 2008;
        02 GO
        03 SELECT ProductID AS N'产品编号', ListPrice AS N'标价',
        04      dbo.ufnGetProductDealerPrice(ProductID, StartDate) AS N'批发价',
        05      StartDate AS N'开始日期',EndDate AS N'结束日期'
        06 FROM Production.ProductListPriceHistory
        07 WHERE ProductID = 707
        08 ORDER BY ProductID, StartDate;
        09 GO

【代码说明】

❑ 实例返回ProductListPriceHistory表编号为707的产品的折算批发价。

❑ 使用第4行的ufnGetProductDealerPrice函数计算批发价。

❑ 第8行的ORDER BY关键字用于将结果按产品排序显示。

【运行效果】

运行结果如图4.14所示。

图4.14 使用ufnGetProductDealerPrice函数

4.3 AdventureWorks 2008示例数据库逻辑结构

AdventureWorks 2008示例数据库，为用户提供了一个完整的企业级的案例，其数据库结构囊括了SQL Server 2008数据库设计的几乎所有的准则。本节详细介绍AdventureWorks 2008数据库的设计、业务逻辑方案。

AdventureWorks 2008示例数据库，从业务上划分为销售和营销方案、产品方案、采购方案和供应商方案、生产方案四个业务模块，如表4.5所示。

表4.5 AdventureWorks 2008示例数据库的业务模型

4.3.1 销售和营销结构

客户和销售的相关数据是AdventureWorks 2008示例数据库的重要组成。AW自行车公司有两类客户：个人客户和商店客户。个人客户是从在线商店购买产品的消费者，商店客户是从AW自行车公司销售代表处购买产品后，进行转售的零售店或批发店。每位客户在Customer表中都有一条记录，而客户类型是Person表中的PersonType列，其主要用来指示客户是个人消费者或者是商店等。

1. 查看个人客户基本信息的示例

【实例4.11】查看客户的基本联系信息，可以通过查询Person.Person和Sales.Customer数据表的联接来实现，联接表将在第7章中进行详细的讲述。具体步骤可以参考如下所示。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT C.*
        04 FROM Person.Person AS C
        05      JOIN Sales.Customer AS Cu
        06           ON C.BusinessEntityID = Cu.PersonID
        07 WHERE   C.PersonType = 'IN'   AND LastName='Brown'
        08 ORDER BY LastName, FirstName ;
        09 GO

【代码说明】

❑ 实例返回个人客户中姓“Brown”的客户联系方式信息。

❑ 第5行的JOIN关键字用于联接表，将多个数据表的信息，按其内在的关系进行组合。

❑ 第7行的WHERE语句用于指定要查询的客户的条件。

❑ 第8行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.15所示。

图4.15 姓Brown客户的联系方式信息

2. 查看商店客户基本信息的示例

【实例4.12】以下实例用于查看商店的基本信息，可以通过Sales.Store、Sales.Customer和Person.Person数据表的联接来实现，联接表将在第7章中进行详细的讲述。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT Name,C.AccountNumber
        04 FROM Sales.Store AS S
        05      JOIN Sales.Customer AS C
        06           ON S.BusinessEntityID= C.PersonID
        07      JOIN Person.Person AS P
        08           ON S.BusinessEntityID=P.BusinessEntityID
        09 WHERE P.PersonType = N'SC'
        10 ORDER BY Name ;
        11 GO

【代码说明】

❑ 实例将返回每个商店客户的名称和账号。

❑ 第5行的JOIN关键字用于联接表，将多个数据表的信息，按其内在的关系进行组合。

❑ 第9行的WHERE语句用于指定要查询的客户的类型。

❑ 第10行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.16所示，这里没有显示数据，因为Sales.Customer中的PersonID都没有数据，读者可以在学习完本章后添加测试数据。

图4.16 商店客户的名称和账号信息

3. 查看指定商店联系人的示例

【实例4.13】以下实例是通过Sales.Store、Sales.Customer数据表的联接，来查看联系人的情况信息，联系人的主要信息保存在Person.Person表中。具体可以参考以下步骤。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 SELECT S.Name AS N'商店', C.LastName AS '人名', TC.Name AS N'职位'
        03 FROM (Person.Person AS C
        04      JOIN Person.BusinessEntityContact AS BC ON
        05           C.BusinessEntityID=BC.PersonID
        06      JOIN Person.ContactType AS TC ON
        07           TC.ContactTypeID = BC.ContactTypeID
        08      JOIN Sales.Customer AS SC ON
        09           SC.PersonID=C.BusinessEntityID
        10      JOIN Sales.Store AS S ON S.SalesPersonID = SC.PersonID)
        11 WHERE (S.Name='Catalog Store')
        12 ORDER BY S.Name ;
        13 GO

【代码说明】

❑ 实例返回购买AW自行车公司产品的名为“Catalog”商店客户的联系人的基本信息。

❑ 第4～10行的JOIN关键字用于联接表，将数据表Person.BusinessEntityContact、Person. ContactType、Sales.Store进行组合。

❑ 第12行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.17所示。

图4.17 Catalog商店的联系人

4. 根据位置查看商店信息的示例

【实例4.14】以下的实例是在AdventureWorks2008数据库中查看某一位置的商店信息。商店的主要信息都保存在Sales.Store表中，但州和国家的名称等信息保存在字典表中，用户要根据地区查看商店的基本信息，要求将相关表Person.StateProvince、Person.CountryRegion等字典信息进行联接，并使用联接后的信息作为过滤条件。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT S.SalesPersonID, S.Name AS Store, A.City, SP.Name AS State, CR.Name
        04      AS CountryRegion
        05 FROM Sales.Store AS S
        06      JOIN Person.BusinessEntity AS CA ON CA.BusinessEntityID = S.SalesPersonID
        07      Join Person.BusinessEntityAddress AS BA ON CA.BusinessEntityID=BA.BusinessEntityID
        08      JOIN Person.Address AS A ON A.AddressID = BA.AddressID
        09      JOIN Person.StateProvince SP ON
        10           SP.StateProvinceID = A.StateProvinceID
        11      JOIN Person.CountryRegion CR ON
        12           CR.CountryRegionCode = SP.CountryRegionCode
        13 WHERE SP.Name = 'Texas' and CR.Name='United States'
        14 ORDER BY S.SalesPersonID ;
        15 GO

【代码说明】

❑ 实例显示“Texas”州的商店的基础信息。

❑ 第6～12行的JOIN关键字用于联接表，将数据表Sales.Store、Sales.CustomerAddress、Person.StateProvince的信息进行组合。

❑ 第14行的ORDER BY关键字用于将结果按SalesPersonID号排序显示。

【运行效果】

运行结果如图4.18所示。

图4.18 Texas州的商店的基础信息

5. 查看某一商店的历史订单情况的示例

【实例4.15】以下实例用于查看某一商店的历史订单情况信息。历史订单信息保存在SalesOrderHeader数据表中，商店的信息保存在Sales.Store表，用户使用JOIN关键字将两表的信息进行关联，并使用WHERE Name = 'A Bike Store'关键字进行商店名称条件的指定。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT Name, SalesOrderNumber, OrderDate, TotalDue
        04 FROM Sales.Store AS S
        05      JOIN Sales.Customer AS SP ON S.SalesPersonID=SP.PersonID
        06      JOIN Sales.SalesOrderHeader AS SO ON SP.CustomerID = SO.CustomerID
        07 WHERE Name = 'A Bike Store'
        08 ORDER BY Name, OrderDate ;
        09 GO

【代码说明】

❑ 实例列出“A Bike Store”商店客户的历史销售订单的情况。

❑ 第6行的JOIN关键字将两表Sales.Store、Sales.SalesOrderHeader的信息进行关联。

❑ 第8行的ORDER BY关键字用于将结果按名称、日期排序显示。

【运行效果】

运行结果如图4.19所示。

图4.19 A Bike Store商店的订单情况

4.3.2 产品结构

AdventureWorks 2008示例数据库中的产品信息，也是最为重要的业务数据，产品结构中所包含的数据对象如表4.6所示。作为生产公司，AW自行车公司提供以了四类产品自行车、自行车的组件（如车轮、踏板）、从供应商购买的转售给客户的自行车装饰、从供应商购买的转售给客户的自行车附件。

表4.6 产品数据表结构

1. 查看产品子类别和型号信息

【实例4.16】以下实例用于在Production架构中查看产品类别、型号等信息。产品的主要信息保存在Production.Product表中；Production.ProductSubcategory中保存了类别的字典信息，用于检索条件的生成。用户可以通过以下步骤实现这一功能。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT PC.Name AS N'产品分类', PSC.Name AS N'子类型', PM.Name AS N'型号', P.Name AS N'产品名'
        04 FROM Production.Product AS P
        05      FULL JOIN Production.ProductModel AS PM ON PM.ProductModelID = P.ProductModelID
        06      FULL JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID =
        07 P.ProductSubcategoryID
        08 JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
        09 WHERE PC.Name = 'Bikes'
        10 ORDER BY PC.Name, PSC.Name ;
        11 GO

【代码说明】

❑ 实例列出了自行车产品的子类产品名称。

❑ 第9行的WHERE指定了产品分类名。

【运行效果】

运行结果如图4.20所示。

图4.20 自行车产品的子类型表

2. 按产品型号查看中文产品说明的示例

【实例4.17】以下实例根据产品型号查看中文产品说明信息，该信息主要保存在Production.ProductModel表中，而中文的字典信息保存在Production.Culture表中，根据产品型号找到数据表的说明信息需要将两表进行联接。具体操作可以参考如下步骤。

执行过程同【实例4.16】中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT PM.ProductModelID, PM.Name AS [Product Model], Description, PL.CultureID, CL.Name AS
        04      Language
        05 FROM Production.ProductModel AS PM
        06      JOIN Production.ProductModelProductDescriptionCulture AS PL
        07           ON PM.ProductModelID = PL.ProductModelID
        08      JOIN Production.Culture AS CL ON CL.CultureID = PL.CultureID
        09      JOIN Production.ProductDescription AS PD
        10            ON PD.ProductDescriptionID = PL.ProductDescriptionID
        11 WHERE CL.Name = 'Chinese'
        12 ORDER BY PM.ProductModelID ;
        13 GO

【代码说明】

❑ 实例显示每一产品说明的中文语言版本。

❑ 第12行的ORDER BY关键字用于将结果按ProductModelID排序显示。

【运行效果】

运行结果如图4.21所示。

图4.21 中文产品说明书

4.3.3 采购方案和供应商结构

在AW自行车公司，采购部门购买原材料和零件的数据结构主要涉及本节所介绍的数据表。AW自行车公司也购买一些产品以进行转售，例如，自行车装饰件和自行车附件。这些转售的产品及其供应商的信息也都存储在该示例数据库的供应商和采购表中。表4.7简要说明了存储在这些表中的数据。

表4.7 采购方案和供应商数据表结构

1. 按位置查看供应商信息的示例

【实例4.18】以下实例实现按位置查看供应商的基本信息，其中Purchasing.Vendor表保存了供应商的主要信息，通过使用JOIN联接Person.CountryRegion，语句使用WHERE关键字选择Person.StateProvince州的信息。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT V.Name AS N'供应商名', A.AddressLine1 as N'地址', A.City as N'城市', SP.Name AS N'州'
        04 FROM Purchasing.Vendor AS V
        05      JOIN Person.BusinessEntity AS BE ON V.BusinessEntityID=BE.BusinessEntityID
        06      JOIN Person.BusinessEntityAddress AS VA ON VA.BusinessEntityID = BE.BusinessEntityID
        07      JOIN Person.Address AS A on A.AddressID = VA.AddressID
        08      JOIN Person.StateProvince AS SP on SP.StateProvinceID = A.StateProvinceID
        09      JOIN Person.CountryRegion AS CR ON CR.CountryRegionCode = SP.CountryRegionCode
        10 WHERE SP.Name = 'California'
        11 GROUP BY V.BusinessEntityID, V.Name, A.AddressLine1, A.City, SP.Name, CR.Name
        12 ORDER BY V.BusinessEntityID;
        13 GO

【代码说明】

❑ 实例列出供应商及其地址。

❑ 第10行的GROUP BY关键字用于将结果按名称分类显示。

❑ 第11行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.22所示。

图4.22 在California州的供应商地址

2. 查看某一供应商提供的产品的示例

【实例4.19】下列实例列举了某一供应商所提供的产品信息，供应商的信息保存在Purchasing.ProductVendor表中，产品信息保存在Production.Product表中，将表进行联接并通过Purchasing.Vendor找到供应商的名称，用户可以通过以下步骤实现该示例。

执行过程同【实例4.18】中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT P.ProductNumber as N'产品号',P.Name as N'产品名', V.Name as N'供应商名'
        04 FROM Production.Product AS P
        05      JOIN Purchasing.ProductVendor AS PV ON P.ProductID = PV.ProductID
        06      JOIN Purchasing.Vendor AS V ON V. BusinessEntityID= PV. BusinessEntityID
        07 WHERE V.Name ='Green Lake Bike Company'
        08 ORDER BY P.Name ;
        09 GO

【代码说明】

❑ 实例列出供应商Green Lake Bike Company向AW自行车公司提供的产品。

❑ 第8行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.23所示。

图4.23 Green Lake Bike Company供应的产品

4.3.4 生产结构

AdventureWorks 2008示例数据库中AW自行车生产的数据是该示例数据库中的核心，本节介绍常用的数据表关系的示例查询。AW自行车公司的生产数据主要包括：

❑ 生产过程包括物料清单、工作订单、生产区和库存区位置以及生产车间等信息。

❑ 产品库存：包括产品在仓库或生产区中的实际位置和可用数量的信息。

❑ 工程文档：包括自行车或自行车组件的技术规范和维护文档。其中生产表数据表的结构设计具有典型的关系特性，在后面章节的示例中可以使用这些数据库进行试验。

表4.8简要说明了生产表中存储的数据的信息。

表4.8 生产数据表结构

1. 查看产品库存的示例

【实例4.20】以下实例用于查看某一产品的库存信息情况，产品的信息保存在Production.Product表中，库存信息保存在Production.ProductInventory数据表中，仓库的位置保存在Production.Location表中。用户可以通过以下步骤查看产品库存信息。

（1）单击“开始|所有程序|Microsoft SQL Server 2008|SQL Server Management Studio”命令，启动SQL Server Management Studio工具。在SQL Server Management Studio工具中，单击“新建查询”按钮，打开一个“新建查询”窗口。

（2）在查询窗口中，输入如下代码，并单击“执行”按钮。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT P.Name AS N'产品', L.Name AS N'库存位置',
        04      SUM(PI.Quantity)AS N'存量'
        05 FROM Production.Product AS P
        06      JOIN Production.ProductInventory AS PI ON P.ProductID = PI.ProductID
        07      JOIN Production.Location AS L ON PI.LocationID = L.LocationID
        08 WHERE P.Name = 'Road-650 Black, 60'
        09 GROUP BY P.Name, L.Name
        10 ORDER BY P.Name ;
        11 GO

【代码说明】

❑ 实例列出了产品“Road-650 Black, 60”的相应库存位置及库存数量。

❑ 第4行的SUM用于统计ProductInventory表中的数量的总和信息。

❑ 第9行的GROUP BY关键字用于将结果按名称分类显示。

❑ 第10行的ORDER BY关键字用于将结果按名称排序显示。

【运行效果】

运行结果如图4.24所示。

图4.24 “Road-650 Black, 60”产品的库存信息

2. 按产品查看工作订单的示例

【实例4.21】以下实例用于按产品查看工作订单信息，工作订单信息保存在Production.WorkOrder表中，产品信息保存在Production.Product数据表中，查看该信息要求使用JOIN将两个数据表进行联接。

执行过程同1中的步骤，输入如下代码。

        01 USE AdventureWorks2008;
        02 GO
        03 SELECT WorkOrderID As N'工作号',
        04 P.Name AS N'产品名',
        05 OrderQty AS N'数量',
        06 DueDate AS N'日期'
        07 FROM Production.WorkOrder W
        08      JOIN Production.Product P ON W.ProductID = P.ProductID
        09 WHERE P.ProductSubcategoryID IN (1, 2, 3)
        10 AND P.Name = 'Road-550-W Yellow, 44'
        11 ORDER BY P.Name, DueDate ;
        12 GO

【代码说明】

❑ 实例列出了产品“Road-550-W Yellow, 44”工作订单信息。

❑ 第9行的WHERE ....IN，表示指定的ID在（1,2,3）这3个数字中。

【运行效果】

运行结果如图4.25所示。

图4.25 产品“Road-550-W Yellow, 44”工作订单信息

4.4 小结

本章从架构方面入手，详细介绍了SQL Server 2008数据库的实例数据库AdventureWorks2008，从它的下载、安装开始，逐步讲解了这个数据库的功能和组成。通过这个完整的数据库，笔者演示了二十几个实例，让读者可以通过这些实例，简单了解数据如何读取，该如何多表查询数据，数据存储过程是什么，等等。希望通过本章的学习，读者能了解基本的数据库是什么样子的。

4.5 本章练习

1. SQL Server安装后是否自己带案例？（ ）

A. 是

B. 不是

2. 以下描述正确的是（ ）？

A. AdventureWorks是针对SQL Server 2008设计的数据库。

B. AdventureWorks2008是针对SQL Server 2008设计的数据库。

C. AdventureWorks2008是针对SQL Server 2005设计的数据库。

3. 在进行数据库查询前都需要使用USE AdventureWorks2008;语句，其意义是什么？