GXU14数据库原理实验指导书

数据库原理上机指导

目录

实验一 SQL Server的安装及环境介绍 .................................................................................. 1 实验二 SQL Server常用管理工具和使用 .............................................................................. 6 实验三 创建数据库 ................................................................................................................. 9 实验四 管理数据库 ............................................................................................................... 15 实验五 表的创建 ................................................................................................................... 21 实验六 表的维护 ................................................................................................................... 26 实验七 数据查询初步 ........................................................................................................... 32 实验八 数据查询巩固 ........................................................................................................... 37 实验九 稍复杂的查询（多表查询、子查询） ................................................................... 45 实验十 视图的管理 ............................................................................................................... 50 考评方式 ................................................................................................................................. 56

请大家先看 考评方式 部分！！ 实验一 SQL Server的安装及环境介绍

实验目的

1. 掌握SQL Server 服务器的安装方法（以SQL Server 2008为例）。 2. 了解SQL Server 服务器的环境（以SQL Server 2008为例）。

实验要求

1. 完成SQL Server的安装、启动。

实验内容

1. 安装SQL Server。

实验指导：

相关知识

Microsoft SQL Server是由美国微软公司所推出的关系数据库解决方案，最新的版本是SQL Server 2012，已经在2012年3月6日发布。 数据库的内置语言原本是采用美国标准局（ANSI）和国际标准组织（ISO）所定义的SQL语言，但是微软公司对它进行了部分扩充而成为作业用SQL（Transact-SQL）。 几个初始版本适用于中小企业的数据库管理，但是近年来它的应用范围有所扩展，已经触及到大型、跨国企业的数据库管理。

SQL Server 2008在2008年8月6日正式发表，并且同时发布SQL Server 2008 Express版本，研发代号为“Katmai”。其实无论是SQL Server 2005还是SQL Server 2008，其安装过程并没有太大的区别，下面我们就以SQL Server 2008为例来说明SQL Server数据库服务器的安装过程。 实验步骤

（1）插入SQL Server 2008 安装光盘，自动运行后出现“SQL Server安装中心”。在左侧的目录树中选择“安装”。

（2）在右侧的选择项中，选择第1项目“全新安装或向现有安装添加功能”，然后就进入了安装程序。

1

（3）在这个准备过程里，首先安装程序要扫描本机的一些信息，用来确定在安装过程中不会出现异常。如果在扫描中发现了一些问题，则必须在修复这些问题之后才可能重新运行安装程序进行安装。然后输入产口密钥，许可条款，安装程序支持文件。

（4）接下来，才是正式安装SQL Server程序。这个步骤看起来跟刚才在准备过程中的一样，都是扫描本机，防止在安装过程中出现异常。现在并不是在重复刚才的步骤，从下图明显看出这次扫描的精度更细，扫描的内容也更多。

（5）功能选择。在这里我们可以勾选需要安装的组件，可以全选，但如果只做为普通数据引擎使用，常常是只勾选：“数据库引擎服务”和“管理工具-基本”。接下来又会进行一次扫描。

2

（6）实例配置。我们这里安装一个默认实例。系统自动将这个实例命名为：MSSQLSERVER 。下一步，我们可以看到安装所需要的磁盘空间要求。

（7）服务账户配置。首先要配置服务器的服务帐户，也就是让操作系统用哪个帐户启动相应的服务。 方便起见，这里可以选择“对所有SQL Server服务使用相同的帐户”。这里有一个地方要注意，如果使用Administrator作为服务账户，那这个Administrator账户在Windows中是不能设置为空密码的。另一个选择是使用NT AUTHORITY\\SYSTEM账户，用最高权限来运行服务。排序规则默认即可。

（8）数据库引擎配置。帐户设置中，一般MSSQLSERVER都做为网络服务器存在，可以使用混合身份验证，设置自己的用户密码。当然，最简单的配置还是使用Windows身份验证模式，然后点击“添加当前用户”

3

（9）之后的配置无需改动，一路下一步即可，等待安装成功。 请你练习

1.如果在安装时设置身份验证模式为混合模式，那SQL Server默认生成的系统管理员账户名是？

2. SQL Server 2008中有哪几种身份验证模式？ 3.什么是实例、默认实例和命名实例？在一台计算机上是否可以安装多个实例？（思考） 相关知识

SQL Server 2008企业版

SQL Server 2008企业版是一个全面的数据管理和业务智能平台，为关键业务应用提供了企业级的可扩展性、数据仓库、安全、高级分析和报表支持。这一版本将为你提供更加坚固的服务器和执行大规模在线事务处理。

SQL Server 2008标准版

SQL Server 2008标准版是一个完整的数据管理和业务智能平台，为部门级应用提供了最佳的易用性和可管理特性。

SQL Server 2008工作组版

SQL Server 2008工作组版是一个值得信赖的数据管理和报表平台，用以实现安全的发布、远程同步和对运行分支应用的管理能力。 这一版本拥有核心的数据库特性，可以很容易地升级到标准版或企业版。

SQL Server 2008 Web版

SQL Server 2008 Web版是针对运行于Windows服务器中要求高可用、面向Internet Web服务的环境而设计。这一版本为实现低成本、大规模、高可用性的Web应用或客户托管解决方案提供了必要的支持工具。

SQL Server 2008开发者版

4

SQL Server 2008开发者版允许开发人员构建和测试基于SQL Server的任意类型应用。这一版本拥有所有企业版的特性，但只限于在开发、测试和演示中使用。基于这一版本开发的应用和数据库可以很容易地升级到企业版。

SQL Server 2008 Express版

SQL Server 2008 Express版是SQL Server的一个免费版本，它拥有核心的数据库功能，其中包括了SQL Server 2008中最新的数据类型，但它是SQL Server的一个微型版本。这一版本是为了学习、创建桌面应用和小型服务器应用而发布的，也可供ISV再发行使用。

SQL Server Compact 3.5版

SQL Server Compact是一个针对开发人员而设计的免费嵌入式数据库，这一版本的意图是构建独立、仅有少量连接需求的移动设备、桌面和Web客户端应用。 SQL Server Compact可以运行于所有的微软Windows平台之上，包括Windows XP和Windows Vista操作系统，以及Pocket PC和SmartPhone设备。

5

实验二 SQL Server常用管理工具和使用

实验目的

1. 理解服务的概念，掌握SQL Server服务的启动、暂停和停止。

2. 熟悉SQL Server配置管理工具的使用，掌握使用管理工具查看数据库信息的方法。 3. 掌握帮助文档的使用

实验要求

1. 使用多种方法启动、暂停和停止SQL Server服务。 2. 使用管理工具查看当前数据库信息。

实验内容

1. 启动、暂停和停止SQL Server服务。 2. 查看帮助文档

实验指导：

相关知识

在Windows NT的操作系统中，Windows服务是一个在后台运行的计算机程序。它在概念上类似于Unix的守护进程。Windows服务必须符合Service Control Manager的接口规则和协议，这个组件负责管理Windows服务。

Windows服务可以被配置为启动操作系统时启动，然后伴随Windows操作系统的运行，一直运行在后台。这些服务可以在计算机启动时自动启动，可以暂停和重新启动而且不显示任何用户界面。这使服务非常适合在服务器上使用，或任何时候，为了不影响在同一台计算机上工作的其他用户，需要长时间运行功能时使用。还可以在不同于登录用户的特定用户帐户或默认计算机帐户的安全上下文中运行服务。

SQL Server 是作为Windows 网络操作系统的一个服务运行的。通过设置，可以在启动操作系统时自动启动SQL Server，也可以远程启动和停止SQL Server。可以使用下列工具手工启动、暂停和停止SQL Server服务。

1.控制面板中的“服务” 2. SQL Server配置管理器

3.在命令提示符中使用net命令 实验步骤

（1）打开控制面板，找到管理工具，点开“服务”。

6

（2）找到一项名为“SQL Server (MSSQLSERVER)”的服务，查看其描述和状态。

（3）停止该服务。 （4）启动该服务。

（5）暂停该服务。 （6）恢复该服务。

（7）在SQL Server配置管理器中尝试重复上述操作。

（8）在SQL Server配置管理器中还可以查看到SQL Server服务占用的端口和本机IP地址。点击左侧“MSSQLSERVER的协议”选择“TCP/IP”,然后选择IP地址选项卡。可以看到端口号。

（9）使用命令NET管理MSSQLSERVER服务。停止服务。命令：NET STOP MSSQLSERVER

（10）启动服务。命令：NET START MSSQLSERVER （11）暂停服务。命令：NET PAUSE MSSQLSERVER

7

（12）重新开启服务。命令：NET CONTINUE MSSQLSERVER

（13）联机丛书的使用。联机丛书中包含所有SQL Server的使用说明。在本课程的学习中，各位同学应该掌握联机丛书的使用，以便在工作和学习中面对不同的SQL Server版本也能迅速地适应。

相关知识

NET命令是功能强大的以命令行方式执行的工具。它包含了管理网络环境、服务、用户、登陆等Windows 98/NT/2000 中大部分重要的管理功能。使用它可以轻松的管理本地或者远程计算机的网络环境，以及各种服务程序的运行和配置。或者进行用户管理和登陆管理等。 请你练习

1.上网查找在osql命令的作用和使用方法，并使用该命令登录SQL Server查看版本。

8

实验三 创建数据库

实验目的

1. 掌握在图形界面下创建数据库的方法。 2. 掌握使用SQL语句创建数据库的方法。

3. 熟悉SQL Server Management Studio的环境。

实验要求

1. 熟练使用两种方法创建数据库。

实验内容

1. 创建数据库。

实验指导：

相关知识

CREATE DATABASE database_name [ CONTAINMENT = { NONE | PARTIAL } ] [ ON

[ PRIMARY ] [ ,...n ] [ , [ ,...n ] ]

[ LOG ON [ ,...n ] ] ]

[ COLLATE collation_name ] [ WITH [,...n ] ] [;]

database_name

新数据库的名称。 数据库名称在 SQL Server 的实例中必须唯一，并且必须符合标识符规则。

除非没有为日志文件指定逻辑名称，否则 database_name 最多可以包含 128 个字符。 如果未指定逻辑日志文件名称，则 SQL Server 将通过向 database_name 追加后缀来为日志生成 logical_file_name 和 os_file_name。 这会将 database_name 限制为 123 个字符，从而使生成的逻辑文件名称不超过 128 个字符。

如果未指定数据文件的名称，则 SQL Server 使用 database_name 作为 logical_file_name 和 os_file_name。 默认路径从注册表中获得。 可以使用 Management Studio 中的“服务器属性”（“数据库设置”页）更改默认路径。 更改默认路径要求重新启

9

动 SQL Server。

CONTAINMENT

指定数据库的包含状态。 NONE = 非包含数据库。 PARTIAL = 部分包含的数据库。 ON 指定显式定义用来存储数据库数据部分的磁盘文件（数据文件）。 当后面是以逗号分隔的、用以定义主文件组的数据文件的 项列表时，需要使用 ON。 主文件组的文件列表可后跟以逗号分隔的、用以定义用户文件组及其文件的 项列表（可选）。

PRIMARY 指定关联的 列表定义主文件。 在主文件组的 项中指定的第一个文件将成为主文件。 一个数据库只能有一个主文件。 有关详细信息，请参阅数据库文件和文件组。

如果没有指定 PRIMARY，那么 CREATE DATABASE 语句中列出的第一个文件将成为主文件。

LOG ON 指定显式定义用来存储数据库日志的磁盘文件（日志文件）。 LOG ON 后跟以逗号分隔的用以定义日志文件的 项列表。 如果没有指定 LOG ON，将自动创建一个日志文件，其大小为该数据库的所有数据文件大小总和的 25% 或 512 KB，取两者之中的较大者。 此文件放置于默认的日志文件位置。 有关此位置的信息，请参阅查看或更改数据文件和日志文件的默认位置 (SQL Server Management Studio)。

不能对数据库快照指定 LOG ON。 COLLATE collation_name

指定数据库的默认排序规则。 排序规则名称既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 如果没有指定排序规则，则将 SQL Server 实例的默认排序规则分配为数据库的排序规则。 不能对数据库快照指定排序规则名称。

不能使用 FOR ATTACH 或 FOR ATTACH_REBUILD_LOG 子句指定排序规则名称。 有关如何更改附加数据库的排序规则的信息，请访问此 Microsoft 网站。

有关 Windows 和 SQL 排序规则名称的详细信息，请参阅 COLLATE (Transact-SQL)。 实验步骤

（1）明确需求。设有一学籍管理系统，其数据库名为“EDUC”，初始大小为 10MB，最大为50MB，数据库自动增长，增长方式是按5％比例增长；日志文件初始为2MB，最大可增长到5MB，按1MB增长。数据库的逻辑文件名为“student_data”, 物理文件名为“student_data.mdf，存放路径为“C:\\sql_data”。日志文件的逻辑文件名为“student_log”, 物理文件名为“student_log.ldf”，存放路径为“C:\\sql_data”。

（2）使用向导创建上诉描述的数据库。使用SQL Server Management Studio（简称SSMS）创建数据库。启动SSMS。在开始菜单中：所有程序－Microsoft SQL Server 2008 －SQL Server Management Studio。

10

（3）单击“连接”按钮，便可以进入【SQL Server Management Studio】窗口。如果身份验证选择的是“混合模式”，则要输入sa的密码。

（4）在“数据库”节点上右击，弹出如下图所示的快捷菜单，选择“新建数据库” 命令。

（5）在“常规”数据页的“数据库名称”文本框中，输入要创建的数据库名称和其他属性信息。

11

（6）单击“确定”。数据库建立成功。

（7）使用T-SQL语言创建数据库。需求：使用查询分析器新建一个数据库，名称为“LIB”，其主要数据文件大小为4M，最大文件大小为10M，每次增长2M；次要数据文件大小为1M；日志文件大小为1M；三个文件的文件名自定，上述没有说明的选项都采用默认值。

12

（8）点击“新建查询”，键入SQL语句。 CREATE DATABASE LIB ON

( NAME = bdata1,

FILENAME = 'c:\\sql_data\\bdata1.mdf', SIZE = 2,

MAXSIZE = 10, FILEGROWTH = 2 ), ( NAME = bdata2,

FILENAME = 'c:\\sql_data\\bdata2.ndf', SIZE = 1 ) LOG ON

( NAME = blog,

FILENAME = 'c:\\sql_data\\blog.ldf', SIZE = 1 )

（9）点击“执行”，若命令无错，则会看到：

请你练习

13

1. 熟悉SQL语言建库的语法。

2. 创建一个test数据库，其主数据文件逻辑名test_data，物理文件名test_data.mdf，初始

大小10MB，最大尺寸为无限大，增长速度1MB；数据库日志文件逻辑名称为test_log，物理文件名为test_log.ldf，初始大小为1MB，最大尺寸为5MB，增长速度为10%。 3. 自行查找删除数据库T-SQL语句。

14

实验四 管理数据库

实验目的

1. 2. 3. 4.

进一步熟练企业管理器和查询分析器的使用。 掌握附加和分离数据库的方法。 熟悉数据库的更名。 掌握数据库的删除。

实验要求

1. 使用T-SQL语句创建和修改数据库。 2. 掌握其他常用的数据库管理操作。

实验内容

1. 使用T-SQL语句修改数据库。 2. 其他数据库管理操作。

实验指导：

相关知识

ALTER DATABASE { database_name | CURRENT } {

MODIFY NAME = new_database_name | COLLATE collation_name | | } [;]

database_name

要修改的数据库的名称。 CURRENT

指定应更改当前使用的数据库。 MODIFY NAME =new_database_name

使用 new_database_name 指定的名称重命名数据库。 COLLATE collation_name

指定数据库的排序规则。 collation_name 既可以是 Windows 排序规则名称，也可以是 SQL 排序规则名称。 如果不指定排序规则，则将 SQL Server 实例的排序规则指定为数

15

据库的排序规则。 实验步骤

（1）建库需求。创建一个名为“Test”数据库。主要数据文件：逻辑文件名为TestData1，实际文件名为TestData1.mdf；初始容量为1MB，最大容量为10MB，递增量为1MB。次要数据文件：逻辑文件名为TestData2，实际文件名为TestData2.ndf；初始容量为1MB，最大容量为10MB，递增量为1MB。事务日志文件：逻辑文件名为TestLog，实际文件名为TestLog.ldf；初始容量为1MB，最大容量为5MB，递增量为1MB。

（2）修改需求。按照下面的要求修改数据库Test。主要数据文件的容量为2MB，最大容量为20MB，递增量为2MB。次要数据文件的容量为2MB，最大容量为20MB，递增量为2MB。事务日志文件的容量为2MB，最大容量为10MB，递增量为2MB。

（3）启动“企业管理器”，在控制面板的“树形目录”中选择“数据库”节点，单击鼠标右键，在弹出菜单中选择“新建数据库”命令，出现 “数据库属性”对话框。

（4）设置常规选项卡：在“名称”文本框中输入数据库名称“Test”；

（5）设置数据文件选项卡：在文件名字段中输入数据文件逻辑文件名“TestData1”；设置该文件初始大小为1MB；标记“文件属性”栏下的“文件自动增长” 复选框，并选中“按兆字节”设置文件容量递增值为1。在“最大文件大小”对话框中选择“将文件增长限制为(MB)”为10。同样，在第二行输入次要数据文件信息，并进行相应设置。设置事务日志选项卡：在文件名字段中输入数据文件逻辑文件名“TestLog”；设置该文件初始大小为1MB，标记“文件属性”栏下的“文件自动增长”复选框，并选中“按兆字节”设置文件容量递增值为1；在“最大文件大小”对话框中，选择“将文件增长限制为(MB)”为5。

（6）单击“确定”按钮，完成数据库的创建。

（7）在“树状目录窗格”中，选择新建的数据库“Test”，单击鼠标右键，在弹出菜单中选择“属性”命令，打开数据库属性窗口；

（8）选择数据文件选项卡，在其中将主要数据文件和次要数据文件的容量改为2MB，最大容量改为20MB，递增量改为2MB。

（9）选择事务日志选项卡，在其中将事务日志文件的容量改为2MB，最大容量改为10，递增量改为2。

（10）使用Transact-SQL语句创建和修改数据库。 CREATE DATABASE Test ON

PRIMARY (NAME = TestData1,

FILENAME = 'C:\\sql_data\\TestData1.MDF', SIZE = 3, MAXSIZE =10, FILEGROWTH = 1), (NAME = TestData2,

FILENAME = 'C:\\sql_data\\TestData2.NDF', SIZE = 1, MAXSIZE =10, FILEGROWTH = 1) LOG ON

(NAME = TestLog,

FILENAME = 'C:\\sql_data\\TestLog.LDF', SIZE = 1, MAXSIZE = 5, FILEGROWTH = 1) GO

16

（11）修改数据库。 ALTER DATABASE Test MODIFY FILE

(NAME=TestData1, SIZE=2, MAXSIZE=20, FILEGROWTH=2) (NAME=TestData2, SIZE=2, MAXSIZE=20, FILEGROWTH=2) (NAME = TestLog, SIZE=2, MAXSIZE=10, FILEGROWTH=2) GO

（12）点击“执行”，执行修改。

（13）在“树状目录窗格”中，选择新建的数据库“Test”，单击鼠标右键，在弹出菜单中选择“属性”命令，打开数据库属性窗口，查看更新是否已执行。

（14）重命名数据库。选择数据库“Test”，单击鼠标右键，在弹出菜单中选择“重命名”命令。

相关知识

可以分离数据库的数据和事务日志文件，然后将它们重新附加到同一或其他 SQL Server 实例。 如果要将数据库更改到同一计算机的不同 SQL Server 实例或要移动数据库，分离和附加数据库会很有用。

在 64 位和 32 位环境中，SQL Server 磁盘存储格式均相同。 因此，可以将 32 位环

17

境中的数据库附加到 64 位环境中，反之亦然。 从运行在某个环境中的服务器实例上分离的数据库可以附加到运行在另一个环境中的服务器实例。

分离数据库是指将数据库从 SQL Server 实例中删除，但使数据库在其数据文件和事务日志文件中保持不变。 之后，就可以使用这些文件将数据库附加到任何 SQL Server 实例，包括分离该数据库的服务器。

附加数据库时，所有数据文件（MDF 文件和 NDF 文件）都必须可用。 如果任何数据文件的路径不同于首次创建数据库或上次附加数据库时的路径，则必须指定文件的当前路径。

（15）分离数据库。右键单击数据库名称，指向“任务”，再单击“分离”。 将出现“分离数据库”对话框。要分离的数据库：列出要分离的数据库。数据库名称：显示要分离的数据库的名称。删除连接：断开与指定数据库的连接。

（16）分离数据库准备就绪后，请单击“确定”。

（17）右键单击“数据库”，然后单击“附加”。在“附加数据库”对话框中，若要指定要附加的数据库，请单击“添加”，然后在“定位数据库文件”对话框中选择数据库所在的磁盘驱动器并展开目录树，以查找并选择数据库的 .mdf 文件。

（18）单击“确定”，附加数据库。

18

（19）删除数据库。选择数据库“Test”，单击鼠标右键，在弹出菜单中选择“删除”命令。然后在弹出的窗口中勾选“关闭现有连接”

19

请你练习

1.认真完成本节数据库管理的相关练习，自己制定需求，然后按“建立-修改-重命名-分离-附加-删除”顺序操作。

2.查询“分离”和“附加”操作的T-SQL命令分别是什么？

20

实验五 表的创建

实验目的

1. 2. 3. 4.

熟练掌握用Transact-SQL语句创建基本表的方法。 熟练掌握基本表的特性。

了解SQL Server的基本数据类型。 使用SSMS管理平台创建基本表。

实验要求

1. 熟练使用企业管理器创建和删除基本表。

2. 是用查询分析器，完成用SQL语句创建和删除基本表。

实验内容

1. 创建、删除基本表。

实验指导：

相关知识

CREATE TABLE

[ database_name . [ schema_name ] . | schema_name . ] table_name [ AS FileTable ]

( { |

| | [ ] [ ,...n ] } )

[ ON { partition_scheme_name ( partition_column_name ) | filegroup | \

[ { TEXTIMAGE_ON { filegroup | \

[ FILESTREAM_ON { partition_scheme_name | filegroup | \

[ WITH ( [ ,...n ] ) ] [ ; ]

database_name

要在其中创建表的数据库的名称。 database_name 必须指定现有数据库的名称。 如果未指定，则 database_name 默认为当前数据库。 当前连接的登录名必须与 database_name 所指定数据库中的一个现有用户 ID 关联，并且该用户 ID 必须具有 CREATE TABLE 权限。

schema_name

新表所属架构的名称。

21

table_name

新表的名称。 表名必须遵循有关标识符的规则。 除了本地临时表名（以单个数字符号 (#) 为前缀的名称）不能超过 116 个字符外，table_name 最多可包含 128 个字符。

AS FileTable 将新表创建为 FileTable。 您无需指定列，因为 FileTable 具有固定架构。 有关 FileTable 的详细信息，请参阅FileTable (SQL Server)。

column_name 表中列的名称。 列名称必须遵循标识符的规则，且在表中必须唯一。 column_name 最多可以有 128 个字符。 对于使用 timestamp 数据类型创建的列，可以省略 column_name 。 如果未指定 column_name，则 timestamp 列的名称默认为 timestamp。

computed_column_expression

定义计算列的值的表达式。 计算列是虚拟列，并非实际存储在表中，除非此列标记为 PERSISTED。 该列由同一表中的其他列通过表达式计算得到。 例如，计算列可以定义为 cost AS price * qty。 表达式可以是非计算列的列名、常量、函数、变量，也可以是用一个或多个运算符连接的上述元素的任意组合。 表达式不能是子查询，也不能包含别名数据类型。

PERSISTED 指定 SQL Server 数据库引擎将在表中物理存储计算值，而且，当计算列依赖的任何其他列发生更新时对这些计算值进行更新。 将计算列标记为 PERSISTED 可允许您对具有确定性、但不精确的计算列创建索引。 有关详细信息，请参阅 计算列上的索引。 必须将用作已分区表的分区依据列的任何计算列显式标记为 PERSISTED。 指定 PERSISTED 时，computed_column_expression 必须具有确定性。

ON { | filegroup | \指定存储表的分区架构或文件组。 如果指定了 ，则该表将成为已分区表，其分区存储在 所指定的一个或多个文件组的集合中。 如果指定了 filegroup，则该表将存储在命名的文件组中。 数据库中必须存在该文件组。 如果指定了 \，或者根本未指定 ON，则表存储在默认文件组中。 CREATE TABLE 中指定的表的存储机制以后不能进行更改。 实验步骤

（1）打开企业管理器。

（2）选择一个创建好的数据库test，单击数据库中的表对象，然后右击窗口右侧选择新建表，弹出如图所示窗体。

（3）在这个窗体中，列名列表示表的字段名，可以在这个窗体中为字段选择数据类型和长度以及是否可以为空值。以下为示例数据。注意：在查找数据类型时，可以按下要查找数据类型的首字母，更快寻找。

22

相关知识

SQL Server支持的数据类型： 精确数字 近似数字 字符串 Unicode 字符串 其他数据类型 bigint float char nchar cursor numeric real varchar nvarchar timestamp bit 日期和时间 text ntext hierarchyid smallint date 二进制字符串 uniqueidentifier decimal datetimeoffset binary sql_variant smallmoney datetime2 varbinary xml int smalldatetime image table tinyint datetime 空间类型 money time （4）在“id”数据项上设置主键。右键单击该数据项选择“设置主键”，可以看到“允许Null值”自动变为未勾选。

（5）通常在此处还可以设置主键的自动增长。在下方的“列属性”中找到“标识规范”，选择“是”，标识增量和标识种子可以自定义，这里按照默认。设置自动增长后，插入输入时可以不填主键，主键可以自动生成。

（6）填完正在创建表的其他信息，单击工具栏上的保存图标，系统会提示输入表的名称。尽量选择一个有意义的名字。

（7）输入表的名称后，单击确定系统会创建表。回到企业管理器界面，在相应数据库中查看表的信息，如下图，Student表已创建。

（8）在查询分析器中利用SQL语句创建表。点击“新建查询”。 （9）在查询窗体中输入创建表的T-SQL语句。

23

use Test go

create table Student( id int,

name varchar(10), sex char(2),

phone varchar(20) )

（10）单击工具栏上的图标按钮，执行分析查询，以检查输入的SQL语句是否存在语法错误。如果没有错误，在结果框中给出“命名已成功完成”的提示。然后单击工具栏上的图标按钮，就可以正式执行命令了。如果成功系统会给出创建成功提示。

（11）下面我们来创建一个更为复杂的学生表，引入SQL Server创建表的更多特性。需求：

字段名称 数据类型 约束 说明 Sno int primary key 学号 Clno char(6) not null 班级编号 Sname char(8) not null 姓名 Ssex char(2) not null 性别 Sbir detetime 出生日期 增加要求：为Sno增加唯一性约束，为SSex进行修改，为其增加检查约束，要求性别只能是“男”或“女”，并同时增加字段Sage，要求年龄18≤Sage≤100。

（12）在查询窗体中输入T-SQL语句 use Test go

if exists ( select name from sys.tables where name='Student' )

drop table Student go

create table Student ( Sno int identity(1,1) primary key, Clno char(6) not null, Sname char(8) not null, Ssex char(2) check (Ssex in ('男','女')) not null, Sbir datetime, Sage int check(Sage>=18 and Sage<=100) )

代码的第3到7行用来检测系统中是否已经有了Student表，如果存在则删去Student表，在这里出现了drop语句的用法，希望大家自行查询该语句用法。identity(1,1)用来设置标志列，自动增长，步长为1，起始值为1。primary key,用于设置主键。check (Ssex in ('男','女'))用来设置check约束。

24

请你练习

1.建表练习，按下列要求通过SSMS创建表。 属性列 数据类型 长度 空值 st_id nVarChar 9 Not Null st_nm nVarChar 8 Not Null st_sex nVarChar 2 Null st_birth datetime Null st_score int Null st_date datetime Null st_from nChar 20 Null st_dpid nVarChar 2 Null st_mnt tinyint Null 2. 建表练习，按下列要求通过T-SQL语句创建表。 属性列 数据类型 长度 空值 cs_id nVarChar 4 Not Null cs_nm nVarChar 20 Not Null cs_tm int Null cs_sc int Null

3. 建表练习，按下列要求通过T-SQL语句创建表。 列约束 PK 列约束 PK 说明 学生学号 学生姓名 学生性别 出生日期 入学成绩 入学日期 学生来源 所在系编号 学生职务 说明 课程编号 课程名称 课程学时 课程学分 属性列 cs_id st_id score sltdate 数据类型 长度 nVarChar 4 nVarChar 9 int datetime 空值 Not Null Not Null Null Null 列约束 FK FK 说明 课程编号 学生编号 课程成绩 选课日期 25

实验六 表的维护

实验目的

1. 熟练掌握使用T-SQL语句对表的结构进行修改。 2. 熟练掌握使用SSMS管理工具删除表、重命名表。 3. 熟练掌握使用T-SQL语句删除表、重命名表。

实验要求

1. 了解常用的修改表定义语句。

2. 完成已经创建成功的表上的修改表定义的操作。

实验内容

1. 修改表定义练习。

实验指导：

相关知识

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name {

ALTER COLUMN column_name {

[ type_schema_name. ] type_name [ ( { precision [ , scale ] | max | xml_schema_collection } ) ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ SPARSE ]

| {ADD | DROP }

{ ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE } }

| [ WITH { CHECK | NOCHECK } ]

| ADD {

| |

26

| } [ ,...n ] } [ ; ]

database_name

要在其中创建表的数据库的名称。 schema_name

表所属架构的名称。 table_name

要更改的表的名称。 如果表不在当前数据库中，或者不包含在当前用户所拥有的架构中，则必须显式指定数据库和架构。

ALTER COLUMN 指定要更改命名列。

修改后的列不能为下列任何一种列： 数据类型为 timestamp 的列。 表的 ROWGUIDCOL 列。 计算列或用于计算列的列。

用在 CREATE STATISTICS 语句生成的统计信息中的列，除非该列的数据类型为 varchar、nvarchar 或 varbinary，数据类型没有更改，新的大小等于或大于旧的大小，或者该列从非 NULL 更改为 NULL。 首先，用 DROP STATISTICS 语句删除统计信息。 由查询优化器自动生成的统计信息将被 ALTER COLUMN 自动删除。

用于 PRIMARY KEY 或 [FOREIGN KEY] REFERENCES 约束中的列。

用于 CHECK 或 UNIQUE 约束中的列。 但是，允许更改用于 CHECK 或 UNIQUE 约束中的长度可变的列的长度。

与默认定义关联的列。 但是，如果不更改数据类型，则可以更改列的长度、精度或小数位数。

仅能通过下列方式更改 text、ntext 和 image 列的数据类型： text 改为 varchar(max)、nvarchar(max) 或 xml ntext 改为 varchar(max)、nvarchar(max) 或 xml image 改为varbinary(max)

某些数据类型的更改可能导致数据的更改。 例如，如果将 nchar 或 nvarchar 列改为 char 或 varchar，则可能导致转换扩展字符。 有关详细信息，请参阅 CAST 和 CONVERT (Transact-SQL)。 降低列的精度或减少小数位数可能导致数据截断。

无法更改已分区表的列的数据类型。

不能更改索引中包括的列的数据类型，除非该列数据类型为 varchar、nvarchar 或 varbinary，数据类型没有更改以及新的大小等于或者大于旧的大小。

主键约束包含的列不能从 NOT NULL 更改为 NULL。 column_name

要更改、添加或删除的列的名称。 column_name 最多可以包含 128 个字符。 对于新列，如果创建列时使用的数据类型为 timestamp，则可以省略 column_name。 如果没有为 timestamp 数据类型列指定 column_name，则使用名称 timestamp。 实验步骤

（1）右键点击所要修改的学生表，选择“设计表”，会弹出如图3-4所示的窗体，在这个窗体中可以更改数据表的字段。在空白处点右键，选择“属性”，弹出另一窗体，在此可

27

以更改约束，也可以增加约束。

（2）利用Transact-SQL语句修改表。使用上一节最后完成的Student表。先做好准备，通过SSMS删除Sage上的check约束，Ssex上的check约束，sno上的主键约束。

（3）修改Sage列，允许空值，类型为smallint。添加一列列名为Sdept，类型为varchar(20)。 use Test

alter table Student

add Sage smallint not null go

alter table Student add Sdept varchar(20)

（4）更改表以删除列。 use Test go

alter table Student drop column Sage

（5）修改表的列以增加约束。

28

字段名数据类型 约束 称 Sno char(8) primary key Clno char(6) not null Sname char(8) uinque Ssex char(2) “男”或“女” Sbir detetime Sage smallint 18≤Sage≤100 Sdept varchar(20) default('计算机与信息科学系') use Test go

--添加PRIMARY KEY约束 Alter table Student

add constraint pk primary key(sno) go

--添加unique约束 alter table Student

add constraint un unique(Sname) go

--添加check约束

alter table Student with nocheck

add constraint ck check(Sage>=18 and Sage<=100) go

alter table Student with nocheck --添加check约束

add constraint cksex check(Ssex in('男','女')) go

--添加default约束

alter table Student with nocheck

add constraint df default('计算机与信息科学系') for Sdept （6）查看Student表上添加的约束。 exec sp_helpconstraint Student

说明 学号 班级编号 姓名 性别 出生日期 年龄 系别 29

（7）修改表的列以删除约束。 use Test go

alter table Student drop constraint ck go

alter table Student drop constraint cksex go

alter table Student drop constraint df go

alter table Student drop constraint pk go

alter table Student drop constraint un go

exec sp_helpconstraint Student

（8）修改列的属性。将Student表中的Sname长度改为20。 use Test go

alter table Student

alter column Sname char(20)

（9）将Student表重新命名成Stu。 use Test go

Sp_rename 'Student','stu'

（10）将stu表的列Sname重命名为T-Sname。

30

use Test go

Sp_rename 'Student，Sname','T-Sname','column' 请你练习

1.为“stu”表添加“dp_count”列（数据类型为nvarchar，长度为3，允许为空）。 2.修改“stu”表的“dp_count”列数据类型为int。 3.删除“stu”表的“dp_count”列。 4.删除表“stu”。

31

实验七 数据查询初步

实验目的

1. 掌握常规的select语句的使用方法。 2. 掌握其他数据操纵语句的使用。 3. 熟练通过SQL语句导入数据

实验要求

1. 掌握简单select语句使用方法。

2. 熟悉关系型数据库查询的基本思想。

实验内容

1. Select语句简单查询。 2. T-SQL数据操纵语句。

实验指导：

相关知识

从数据库中检索行，并允许从 SQL Server 2012 中的一个或多个表中选择一个或多个行或列。 虽然 SELECT 语句的完整语法较复杂，但其主要子句可归纳如下：

[ WITH ] SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression] [ HAVING search_condition]

[ ORDER BY order_expression [ ASC | DESC ] ]

可在查询之间使用 UNION、EXCEPT 和 INTERSECT 运算符，以便将各个查询的结果合并或比较到一个结果集中。

SELECT 语句中的子句顺序非常重要。 可以省略可选子句，但这些子句在使用时必须按适当的顺序出现。

只有当这些语句的选择列表包含给函数的局部变量赋值的表达式时，在用户定义函数中才允许有 SELECT 语句。

对于由四部分组成的名称，若其中的服务器名称使用的是 OPENDATASOURCE 函数，则该名称可以在表名能够在 SELECT 语句内出现的任何位置作为表源使用。

某些应用于 SELECT 语句的语法限制涉及到远程表。 SELECT 语句的逻辑处理顺序

32

以下步骤显示 SELECT 语句的逻辑处理顺序（即绑定顺序）。 此顺序确定在一个步骤中定义的对象何时可用于后续步骤中的子句。 例如，如果查询处理器可以绑定到（访问）在 FROM 子句中定义的表或视图，则这些对象及其列可用于所有后续步骤。 相反，因为 SELECT 子句处于步骤 8，所以，在该子句中定义的任何列别名或派生列都无法被之前的子句引用。 不过，它们可由 ORDER BY 子句之类的后续子句引用。 请注意，该语句的实际物理执行由查询处理器确定，因此顺序可能与此列表不同。

源 ON JOIN WHERE GROUP BY

WITH CUBE 或 WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP 实验步骤

（1）在关系数据库中，常见的修改数据的语句有三中T-SQL语句。首先是insert语句，我们使用insert语句向数据库中插入数据。以上节使用的stu数据库为例，想其中插入一条数据。要求：班号为1，名字是gxustu，性别男，出生日期为1990年1月2日，今年24岁，就读于计算机系。SQL语句如下。

INSERT INTO [Test].[dbo].[stu] ([Clno] ,[Sname] ,[Ssex] ,[Sbir] ,[Sage] ,[Sdept]) VALUES (1

,'gxustu' ,'男'

,1990-1-2 ,24

,'Computer Science') GO

（2）打开企业管理器，右键点击stu表，选择“选择前1000行”（SQL Server 2008用法，其他版本类似）可以看到数据已插入。

（3）现在介绍update语句。update语句可以用来更新行。下面我们将刚才插入的数据

33

修改一下，将该学生的出生日期改为1989年，这样他的年龄就变成了25岁。实际上，像这样将年龄和出生日期同存于一张表中的设计是不符合范式的，想一想不符合那个范式，这样为什么不好？我们这里仅用于示例SQL语句的使用。

use Test UPDATE stu SET

Sbir = '1991-1-2' ,Sage = 25

WHERE Sname = 'gxustu' GO

（4）delete语句。delete语句用于删除数据行。用法如下。 DELETE FROM [Test].[dbo].[stu] WHERE Sname = 'gxustu' GO

相关知识

注意：如果想清除表中的所有数据但不删除这个表，可以使用TRUNCATE TABLE语句。该语句相当于不带where子句的的DELETE语句，并且该语句不记录日志，删除的所有数据无法恢复。TRUNCATE TABLE比DELETE速度快，且使用的系统和事务日志资源少。DELETE语句每次删除一行，并且在事务日志中为所删除的每行记录一项。而TRUNCATE TABLE是通过释放“存储表数据”的数据页来删除数据的，并且只在事务日志中记录页的释放，导致数据无法恢复。

（5）下面是简单的数据查询学习。首先是创建表。新建查询，然后将下列语句执行一次，这也是使用SQL语句导入数据的过程。

use Test --部门表--

create table DEPT(

DEPTNO int primary key, DNAME varchar(20), LOCATION varchar(20) )

insert into DEPT values(10,'财务部','上海'); insert into DEPT values(20,'技术开发部','昆明'); insert into DEPT values(30,'销售部','沈阳'); insert into DEPT values(40,'计划部','北京');

--雇员表--

create table EMP

(EMPNO int primary key not null, ENAME varchar(20) not null, JOB varchar(20) not null, MGR int,

34

HIREDATE varchar(100) not null, SAL int, COMM int,

DEPTNO int foreign key (DEPTNO) references DEPT(DEPTNO) )

insert into EMP values(7369,'王晓明','办事员',7902,'17-DEC-80',80,null,20); insert into EMP values(7499,'唐强','推销员',7698,'20-FEB-81',160,300,30); insert into EMP values(7521,'王明德','推销员',7698,'22-FEB-81',125,500,30); insert into EMP values(7566,'张国强','部门经理',7839,'02-APR-81',297,null,20); insert into EMP values(7654,'马丁','推销员',7698,'28-SEP-81',125,400,30);

insert into EMP values(7698,'孙国强','部门经理',7839,'17-DEC-80',285,null,30); insert into EMP values(7782,'周浩','部门经理',7839,'09-JUN-81',245,null,10); insert into EMP values(7788,'李文辉','分析员',7566,'09-DEC-82',300,null,20); insert into EMP values(7839,'宋利霞','总经理',NULL,'17-NOV-79',500,null,10); insert into EMP values(7844,'王婷芳','推销员',7698,'08-SEP-81',150,null,30); insert into EMP values(7876,'李成','办事员',7788,'01-MAY-81',110,null,20); insert into EMP values(7900,'罗松','办事员',7698,'20-FEB-81',95,null,30); insert into EMP values(7902,'杜强','分析员',7566,'09-DEC-82',300,null,20); insert into EMP values(7934,'张鸣','办事员',7782,'17-NOV-79',130,null,10);

（6）查询EMP表中的全部信息。 select * from [Test].[dbo].[EMP];

（7）查询EMP表中的EMPNO、ENAME、SAL三列，并将名称显示为职工号、姓名、工

35

资。

select EMPNO 职工号,ENAME 姓名,SAL 工资 from [Test].[dbo].[EMP];

（8）查询EMP表中的ENAME、DEPTNO、JOB、SAL四列，并将名称显示为姓名、部门号、工作、工资，并使用SAL为依据排序。

select ENAME 姓名,DEPTNO 部门号,Job 工作,SAL 工资 from [Test].[dbo].[EMP] ORDER BY SAL; 请你练习

1. 查找insert、delete、update语句的格式，练习使用。

2. 按下图显示的表定义，通过SSMS建表。写出插入这些数据的SQL语句。

36

实验八 数据查询巩固

实验目的

1. 巩固上节数据查询操作。 2. 分组、排序查询。

3. 了解示例数据库Northwind结构

实验要求

1. 熟练掌握各种数据查询方法。

2. 掌握关系型数据库查询的基本思想。

实验内容

1. 数据查询练习。

2. 附加Northwind数据库，了解其结构。

实验指导：

相关知识

⑴ Categories:种类表 相应字段:

CategoryID :类型ID； CategoryName:类型名; Description:类型说明; Picture:产品样本

⑵ CustomerCustomerDemo:客户类型表1 相应字段：

CustomerID：客户ID；

CustomerTypeID：客户类型ID

⑶ CustomerDemographics:客户类型表2 相应字段：

CustomerTypeID：客户类型ID； CustomerDesc：客户描述

⑷ Customers:客户表 相应字段：

37

CustomerID:客户ID；

CompanyName:所在公司名称； ContactName:客户姓名； ContactTitle:客户头衔； Address：联系地址； City：所在城市; Region:所在地区; PostalCode:邮编; Country:国家 Phone:电话； Fax：传真

⑸ Employees：员工表 相应字段：

EmployeeID:员工代号;

LastName + FirstName:员工姓名; Title:头衔;

TitleOfCourtesy:尊称; BirthDate:出生日期; HireDate:雇用日期; Address：家庭地址； City:所在城市; Region:所在地区; PostalCode:邮编; Country:国家用； HomePhone：宅电; Extension:分机; Photo:手机; notes:照片； ReportsTo：上级; PhotoPath:照片

⑹ EmployeeTerritories：员工部门表 相应字段：

EmployeeID:员工编号； TerritoryID：部门代号

⑺ Order Details：订单明细表 相应字段：

OrderID:订单编号； ProductID：产品编号； UnitPrice：单价; Quantity:订购数量； Discount:折扣

38

⑻ Orders:订单表 相应字段：

OrderID:订单编号； CustomerID:客户编号; EmployeeID:员工编号； OrderDate：订购日期；

RequiredDate：预计到达日期; ShippedDate:发货日期; ShipVia:运货商; Freight:运费;

ShipName:货主姓名； ShipAddress:货主地址 ShipCity:货主所在城市； ShipRegion：货主所在地区； ShipPostalCode：货主邮编; ShipCountry:货主所在国家

⑼ Products:产品表 相应字段：

ProductID:产品ID；

ProductName：产品名称； SupplierID：供应商ID； CategoryID：类型ID； QuantityPerUnit：数量； UnitPrice：单价;

UnitsInStock：库存数量； UnitsOnOrder：订购量； ReorderLevel：再次订购量; Discontinued：中止

⑽ Region:地区表 相应字段：

RegionID：地区ID；

RegionDescription：地区描述

⑾ Shippers:运货商 相应字段：

ShipperID:运货商ID；

CompanyName：公司名称; Phone:联系电话

⑿ Suppliers：供应商表 相应字段：

39

ShipperID：供应商ID；

CompanyName：供应商姓名； Phone；联系电话

⒀ Territories：地域表 相应字段：

TerritoryID:地域编号；

TerritoryDescription：地域描述； RegionID：地区编号

40

实验步骤

（1）Northwind数据库是较为贴近实际的示例数据库，非常适合进行查询练习。以上是Northwind数据库的表结构。本节会在上节创建的数据库上进行进一步的查询练习，然后使用Northwind数据库进行查询练习。从EMP数据库中查出有多少种不同的工作，列名为“工作”。

select distinct JOB 工作 from [Test].[dbo].[EMP]

（2）从EMP数据库中查出“DEPTNO”等于30的所有人员信息。 select * from [Test].[dbo].[EMP] where DEPTNO=30 （3）查出所有办事员的姓名、职工号、所属部门。 select ENAME 姓名,EMPNO 职工号,DNAME 部门 from [Test].[dbo].[EMP],[Test].[dbo].[DEPT]

where JOB='办事员'

（4）查出部门号为30，薪水大于100的员工信息。

select * from [Test].[dbo].[EMP] where DEPTNO=30 and SAL>=100

41

（5）查出所在部门号为10，职位不是办事员又不是部门经理的所有人。 select * from [Test].[dbo].[EMP]

where DEPTNO=10 and JOB!='办事员' and JOB!='部门经理' （6）在EMP中选择所有姓王的雇员信息。

select * from [Test].[dbo].[EMP] where ENAME like'王%' （7）按部门升序、工资降序输出EMP表。 select * from [Test].[dbo].[EMP] where DEPTNO=10 order by SAL desc

（8）按部门升序，工资降序输出EMP表。 select * from [Test].[dbo].[EMP]

where DEPTNO=10 order by SAL desc

（9）下面进行Northwind数据库的查询练习。首先要附加数据库。右键单击“数据库”选择“附加”。在弹出的对话框中选择添加，选择Northwind数据库。

（10）在NorthWind中查询出每个供应商所提供产品的平均价格。

select SupplierID,avg(UnitPrice) from [Northwind].[dbo].[Products] group by SupplierID

（11）在NorthWind中查询得到不同的地区有多少家供应商的信息。 select Country,count(*)

from [Northwind].[dbo].[Suppliers] group by Country

42

（12）在NorthWind中查询显示哪些供应商有传真。 select CompanyName,Fax

from [Northwind].[dbo].[Suppliers] where fax is not null

（13）在NorthWind中查询不同产品所订购的总价。 select ProductID,sum(Quantity*UnitPrice) from [Northwind].[dbo].[Order Details] group by ProductID order by ProductID

（14）在NorthWind中查询出生日期在1940年至1960年之间的雇员信息。 select *

from [Northwind].[dbo].[Employees]

where BirthDate >= '1940-1-1' and BirthDate < '1960-1-1'

（15）查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。条件是“地区等于华北”并且“联系人头衔等于销售代表”。

Select SupplierID,CompanyName,Address,City from [Northwind].[dbo].[suppliers]

where Region='华北' and ContactTitle='销售代表'

（16）查询“Northwind”示例数据库中供应商的ID、公司名称、地区、城市和电话字段的值。其中的一些供应商位于华东或华南地区，另外一些供应商所在的城市是天津。

Select SupplierID,CompanyName,Region,City,Phone

43

from [Northwind].[dbo].[Suppliers]

where Region in ('东北','华南') or City='天津'

（17）查询“Northwind”示例数据库中位于“华东”或“华南”地区的供应商的ID、公司名称、地区、城市和电话字段的值。

Select SupplierID,CompanyName,Region,City,Phone from [Northwind].[dbo].[Suppliers] where Region in ('东北','华南') 请你练习

1.在NorthWind数据库中查询所有产品的信息。

2.查询NorthWind中雇员姓名、所在城市及其家庭电话，姓名以格式“姓.名”显示。 3.在NorthWind中查询出单价最贵的前10种产品的信息。

4.在NorthWind中查询出所有单价超过50美元的货物名称、货物代号及其单位重量。 5.在NorthWind中查询出职务为销售代表，称呼为小姐的所有职员姓名和生日。

44

实验九 稍复杂的查询（多表查询、子查询）

实验目的

1. 2. 3. 4.

掌握SSMS查询编辑器的使用方法，加深对SQL语言的嵌套查询语句的理解。 熟悉基本的连接查询，掌握内连接与外连接查询。 掌握相关子查询的使用方法。

学会嵌套子查询的使用方法、集合查询。

实验要求

1. 掌握在实际的数据关系中设计有意义的查询。 2. 掌握较复杂的数据查询方法。

实验内容

1. 数据查询联系。

实验指导：

实验步骤

（1）今天我们来学习稍复杂的查询，其中包含多表查询和子查询的使用，同时还会对较为贴近实例的查询进行练习。希望大家在充分掌握前几节内容的基础上，透彻理解本节每个查询的意义，思考数据是如何被查出的。现在首先使用查询初步中创建的数据库。查询唐强的工作所在部门名称和部门所在地。

select DNAME,LOCATION from [Test].[dbo].[DEPT] where DEPTNO = ( select DEPTNO from [Test].[dbo].[EMP] where ENAME='唐强')

（2）在沈阳工作的雇员信息。 select * from [Test].[dbo].[EMP] where DEPTNO = ( select DEPTNO from [Test].[dbo].[DEPT]

where LOCATION='沈阳')

45

（3）找出在沈阳工作的雇员信息及其所在部门信息。 select * from [Test].[dbo].[EMP],[Test].[dbo].[DEPT]

where DEPT.LOCATION='沈阳' and DEPT.DEPTNO=EMP.DEPTNO

（4）找出工资超过经理的职工，列出姓名、工资及其经理的姓名、工资。 select first.ENAME 职工,second.ENAME 经理

from [Test].[dbo].[EMP] first,[Test].[dbo].[EMP] second where first.JOB not like'%经理'

and second.JOB in ('总经理','部门经理') and first.SAL>second.SAL

（5）下面使用Northwind数据库进行练习。查询Boise和Albuquerque的客户在1996年订购的所有订单的订单ID、所订购的产品名称和数量

use Northwind

select orders.orderid,productname,quantity,city from [order details] join products

on [order details].productid=products.productid join orders

on [order details].orderid=orders.orderid join customers

on orders.customerid=customers.customerid where city in('Boise' ,'Albuquerque') and

OrderDate between '1996-1-1' and '1996-12-31'

（6）查询客户的每份订单的订单ID、产品名称和销售金额 use Northwind

select orders.orderid,productname,[order details].unitprice*quantity as 销售金额

46

from [order details] join products

on [order details].productid=products.productid join orders

on [order details].orderid=orders.orderid join customers

on orders.customerid=customers.customerid

（7）按运货商公司名称，统计1997年由各个运货商承运的订单的总数量 use Northwind

select companyname,count(*) from shippers join orders

on shippers.shipperid=orders.shipvia where year(orderdate)=1997 group by companyname

（8）找出1997年订单id为10400的订单详情。 select * from [order details] join orders on [order details].orderid=orders.orderid

where orders.orderid=10400 and year(orderdate)=1997 （9）统计各类产品的平均价格 use Northwind

select categories.categoryname,avg(unitprice) from products join categories

on products.categoryid=categories.categoryid group by categories.categoryname

47

请你练习

以下查询均基于Northwind数据库。

1. 查询所有运货商的公司名称和电话。

2. 查询所有客户的公司名称、电话、传真、地址、联系人姓名和联系人头衔。 3. 查询单价介于10至30元的所有产品的产品ID、产品名称和库存量。

4. 查询单价大于20元的所有产品的产品名称、单价以及供应商的公司名称、电话。 5. 统计1997年上半年的每份订单上所订购的产品的总数量。 6. 统计各地区客户的总数量。

（从此处开始的剩余练习写到另一张实验报告上）

练习加强：下面的查询基于AdventureWorks数据库，以附件的形式提供给大家。大家在进行查询练习前一定要先熟悉一下该数据库的表结构和用到的表的字段的含义。这些准备工作可以通过创建数据库关系图或者上网搜索AdventureWorks2008数据字典来完成。下列这些查询操作涉及你们所学的各种查询知识，是数据库查询的综合运用，请大家尽力完成。

7. 在Product表中查出所有产品信息（列：Name, ProductNumber, ListPrice），按产品名升序排列。

8. 查出每种产品的销售总额和折扣总额。涉及表：Product、SalesOrderDetail，按产品名升序排列。共三列。必须使用join。

9. 查出销售订单中每种产品的收入金额（扣除折扣后）。涉及表：Product、SalesOrderDetail，按产品名降序排列。共三列。必须使用join。

10. 查出产品型号为“Long-Sleeve Logo Jersey*”（*为任意字符）的每种产品名。涉及表：Product、ProductModel。一列。使用EXISTS和IN两种方法完成。

11. 查出每种产品类型均价等于该类产品最高标价的产品类型号。涉及表：Product。GROUP BY、子查询。

12. 查出每个销售订单的销售总额。两列：订单号、销售总额。涉及表：SalesOrderDetail。 13. 查出每种产品销售平均价格以及该产品迄今为止的总销售额。涉及表：SalesOrderDetail。三列：ID、均价、总额。

14. 查出标价大于$1000的产品，求出这些产品的均价并按类型号分组。涉及表：Product。

15. 查出完成了相同单笔销售额的订单的平均销售产品数量和这个销售总额。涉及表：SalesOrderDetail。

16. 查出单笔销售产品数量大于10的订单销售的产品ID和均价。分组。涉及表：SalesOrderDetail。

17. 查出总销量大于5的产品ID，并排序。必须使用HAVING。涉及表：SalesOrderDetail。 18. 查出单价小于25，且平均订单量大于5的产品ID。涉及表：SalesOrderDetail。

48

19. 查出总销售额大于$10000且平均单笔销量小于3的产品。三列：ID、均价、总销售额。涉及表：SalesOrderDetail。

20. 统计在订单中至少包含9项的产品ID及其销售总额。

49

本文来源：https://www.bwwdw.com/article/49hx.html