《数据库系统原理与应用》实验指导书

数据库原理与应用实验指导书
重庆文理学院技术师范学院二О一О年九月
实验1 初识SQL Server 2000
一、实验目的
1．了解SQL Server 2000的版本和软硬件需求；
2．掌握SQL Server 2000的安装；
3．了解SQL Server 2000的基本架构；
4．掌握SQL Server 2000服务管理器的启动；
5．掌握SQL Server 2000企业管理器的启动；
6．掌握SQL Server 2000查询分析器的启动；
7．掌握SQL Server 2000查询分析器的基本使用；
8．了解SQL Server 2000和其它数据库管理系统的区别。

二、实验要求
1.完成SQL Server 2000 指定版本的安装；
2.启动SQL Server 2000的各组成部件，并了解每一部件的基本操作；
3.启动查询分析器，并运行指定T-SQL语句。

三、实验步骤
1.了解SQL Server 2000安装步骤，安装SQL Server 2000。

我们以Windows 2000 Advanced Server操作系统作为示例，详细介绍安装SQL Server 2000企业版的过程，其详细安装步骤如下：
（1）企业版安装光盘插入光驱后，出现提示框。

请选择【安装SQL Server 2000 组件】，如图1-1所示。

（2）选择【安装数据库服务器】，如图1-2所示。

图1-2 “安装组件”选择窗口图1-1 安装SQL Server 2000 主界面
（3）选择【下一步】，进入欢迎介面，开始进行安装，如图1-3所示。

（4）选择【本地计算机】进行安装，如图1-4所示。

图1-3 “欢迎”窗口图1-4 “本地计算机”选择窗口（5）在【安装选择】窗口，选择【创建新的SQL Server实例... 】。

对于初次安装的用户，应选用这一安装模式，不需要使用【高级选项】进行安装，如图1-5所示。

（6）在【用户信息】窗口，输入用户信息，如图1-6所示。

图1-5 “安装选择”窗口图1-6 “用户信息”输入窗口（7）接受软件许可证协议，如图1-7所示。

（8）在【安装定义】窗口，选择【服务器和客户端工具】选项进行安装，如图1-8所示。

我们需要将服务器和客户端同时安装，这样在同一台机器上，我们可以完成相关的所有操作，对于我们学习SQL Server很有用处。

如果你已经在其它机器上安装了SQL Server，则可以只安装客户端工具，用于对其它机器上SQL Server的存取。

图1-7 接收“软件许可证协议”窗口图1-8 “安装定义”窗口
（9）在【实例名】窗口，选择【默认】的实例名称，如图1-9所示。

这时本SQL Server的名称将和Windows 2000服务器的名称相同。

例如作者的Windows服务器名称是【Teacher】，则SQL
Server的名字也是【Teacher】。

SQL Server 2000可以在同一台服务器上安装多个实例，也就是你可以重复安装几次。

这时您就需要选择不同的实例名称了。

建议将实例名限制在10个字符之内。

实例名会出现在各种SQL Server 和系统工具的用户界面中，因此，名称越短越容易读取。

另外，实例名称不能是【Default】或【MSSQLServer】以及SQL Server的保留关键字等。

（10）在【安装类型】窗口，选择【典型】安装选项，并指定【目的文件夹】，如图1-10所示。

程序和数据文件的默认安装位置都是“C:\Program Files\Microsoft SQL Server\”。

如果您的数据库数据有10万条以上的话，请预留至少1G的存储空间，以应付需求庞大的日志空间和索引空间。

图1-9 “实例名”输入窗口图1-10 “安装类型”选择窗口（11）在【服务帐号】窗口，请选择【对每个服务使用统一帐户... 】的选项，如图1-11所示。

在【服务设置】处，可以选择【使用本地系统帐户】。

如果需要【使用域用户帐户】的话，请将该用户添加至Windows Server的本机管理员组中。

（12）在【身份验证模式】窗口，请选择【混合模式... 】选项，并设置管理员“sa”帐号的密码，如图1-12所示。

如果您的目的只是为了学习的话，可以将该密码设置为空，以方便登录。

如果是真正的应用系统，则千万需要设置和保管好该密码！如果需要更高的安全性，则可以选择【Windows身份验证模式】，这时就只有Windows Server的本地用户和域用户才能使用SQL Server 了。

当然在安装完成后也可以修改这一设置，在以后的章节中我们会介绍修改方法。

最后特别指出，如果用户试图通过提供空白登录名称连接到SQL Server的实例，则SQL Server 将使用Windows身份验证，与这里的设置无关。

图1-11 “服务帐户”信息窗口图1-12 “身份验证模式”选择窗口（13）在【选择许可模式】窗口，根据您购买的类型和数量输入（0表示没有数量限制），如图1-13所示。

【每客户】表示同一时间最多允许的连接数，【处理器许可证】表示该服务器最多
能安装多少个CPU。

我们这里选择了【每客户】并输入了50作为示例。

（14）然后就是约10分钟左右的复制文件，如图1-14所示。

图1-13 “选择许可模式”窗口
图1-14 “开始复制文件”窗口
2.启动SQL Server 2000服务管理器并了解其基本组成。

从“Microsoft SQL Server”菜单中选择“服务管理器”选项，打开“SQL Server服务器”窗口，如图1-15所示。

从“服务器”下拉框中可以选择运行本地服务器或远程服务器，对于运行本地服务器的方式，输入的服务器的名称，可以是服务器的实际名称，如“DEVELOPER”，也可以输入“localhost”或“.”。

从“服务”下拉框中还可以选择在SQL Server服务器所运行的服务方式，如“SQL
3.启动SQL Server 2000企业管理器并了解其基本组成。

从“Microsoft SQL Server”菜单中选择“企业管理器”选项，打开“SQL Server Enterprise Manager”窗口，即企业管理器窗口，如图1-17所示。

4.启动SQL Server 2000查询分析器并了解其基本组成。

从“Microsoft SQL Server”菜单中选择“查询分析器”选项，打开“连接到SQL Server”窗口，如图1-18所示。

从“SQL Server服务器”下拉框中选择本地服务器“DEVELOPER”或者
“localhost”、“.”，也可以自己在下拉框中输入以上三个名称中的任何一项，再选择连接使用类型为“Windows身份验证”，单击“确定”按钮，将打开“SQL查询分析器”，如图1-19所示。

5.在SQL Server 2000查询分析器的命令窗格中输入如下语句：
USE PUBS
GO
SELECT * FROM authors
GO
6.按F5或点击工具栏上的运行按钮“”，查看运行结果，如图1-20所示：
四、注意事项
1.提供SQL Server 2000安装光盘或网络共享文件夹，SQL Server 2000版本可以自行选择，推
荐使用企业版。

2.提供满足上述SQL Server 2000版本安装条件的个人计算机。

3.SQL Server 2000版本和操作系统的关系。

4.安装过程中登录模式的选择。

5.安装过程中许可模式的选择。

实验2 SQL Server 2000数据库操作
一、实验目的
1.了解本书示例数据库student和eshop的基本组成。

2.掌握查询分析器的使用方法。

3.掌握应用企业管理器创建数据库的方法。

4.掌握应用企业管理器修改和查看数据库的方法。

5.掌握应用企业管理器删除数据库的方法。

6.掌握应用T-SQL创建数据库的方法。

7.掌握应用T-SQL修改和查看数据库的方法。

8.掌握通过T-SQL删除数据库的方法。

9.掌握SQL Server 2000数据库和操作系统物理文件的关系。

二、实验要求
1.创建实验所用到的“网上购物系统”数据库eshop。

2.保存实验结果到网络文件夹。

三、实验步骤
1．使用系统缺省方式创建“网上购物系统”数据库eshop。

首先打开“SQL Server企业管理器”，依次展开“SQL Serve组”和“SQL Server注册”，右击“数据库”选项，弹出快捷菜单，如图2-1所示。

从快捷菜单中选择“新建数据库”项，将打开“数据库属性”对话框，如图2-2所示。

在名称文本框中输入“eshop”，其他选择默认值，单击“确定”即
2．在操作系统环境下找到eshop数据库对应的物理文件的位置并查看其属性。

从Windows操作系统中打开“资源管理器”或者“我的电脑”，打开SQL Server 2000数据库文件的默认物理存储位置“E:\Microsoft SQL Server\MSSQL\Data”，可以看到这两个文件：“eshop_Data.MDF”和“eshop_Log.LDF”，它们分别代表eshop数据库的主数据文件和日志文件，其大小均为默认值1024KB，即1MB，如图2-3所示。

3．查看eshop数据库的相关信息。

打开“SQL Server企业管理器”，展开数据库，右击数据库“eshop”，弹出快捷菜单，如图2-4所示。

从快捷菜单中选择“属性”，将弹出“eshop属性”对话框，如图2-5所示。

在“eshop属性”对话框中，默认的当前选项卡是“常规”项，可以查看当前数据库的名称、所有者和创建日期等信息，用户也可以依次选择其他选项卡，如“数据文件”、“事务选项”等，将可以看到有关当前数据库的数据文件和事务日志文件的物理存储位置、大小和所属组等信息，如图2-6
4．删除eshop数据库。

打开“SQL Server企业管理器”，展开数据库，右击数据库“eshop”，弹出快捷菜单，如图2-8所示。

从快捷菜单中选择“删除”，将弹出“删除数据库－eshop”对话框，为了彻底删除数据库eshop 的信息，建议勾选“为数据库删除备份并还原历史记录”项，单击“是”按钮，删除eshop数据库，
5．在E盘（根据机房环境选择）创建文件夹mydata。

从Windows操作系统中打开“资源管理器”或者“我的电脑”，打开E盘，在E盘根文件夹下新建一子文件夹“mydata”，如图2-10所示，稍后将在此文件夹中保存数据库文件。

6．在E:\mydata文件夹下创建名为eshop的数据库，同时指定eshop_dat为数据库主文件名，eshop_log为数据库日志文件名，文件初始大小为10M，最大为50M，文件增长为5M，SQL 代码如下所示：
CREATE DATABASE eshop
ON PRIMARY
(
NAME=eshop_dat,
FILENAME='E:\mydata\eshop_dat.mdf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
LOG ON
(
NAME=eshop_log,
FILENAME='E:\mydata\eshop_log.ldf',
SIZE=10MB,
MAXSIZE=50MB,
FILEGROWTH=5MB
)
GO
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-11所示：
7．在操作系统环境下找到eshop数据库对应的物理文件的位置并查看其属性。

从Windows操作系统中打开“资源管理器”或者“我的电脑”，打开eshop数据库的数据文件的物理存储位置“E:\mydata”，可以看到这两个文件：“eshop_dat.mdf”和“eshop_log.ldf”，它们分别代表eshop数据库的主数据文件和日志文件，其大小均为指定值10240KB，即10MB，如
8．查看新建数据库和数据库文件的相关信息，SQL代码如下所示：
sp_helpdb eshop
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-13所示：
9．在eshop数据库中添加一个次要数据库文件（eshop_dat2），文件初始大小为5MB，最大容量为100MB，文件增长为5MB，SQL代码如下所示：
ALTER DATABASE eshop
ADD FILE
(
NAME=eshop_dat2,
FILENAME='E:\mydata\eshop_dat2.ndf',
SIZE=5MB,
MAXSIZE=100MB,
FILEGROWTH=5MB
)
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-14所示：
10．查看修改后的数据库和数据库文件的相关信息，SQL代码如下：
sp_helpdb eshop
11．删除步骤9中所添加次要数据库文件eshop_dat2，SQL代码如下所示：ALTER DATABASE eshop
REMOVE FILE eshop_dat2
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-16所示：
12．将eshop数据库改名为eshop_bak，SQL代码如下所示：
sp_renamedb 'eshop','eshop_bak'
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-17所示。

13．删除eshop_bak数据库，SQL代码如下所示：
DROP DATABASE eshop_bak
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图2-18所示。

14．将步骤6-13命令脚本（DB01.SQL）保存上交以备教师检查。

15．重复执行步骤6，并将建库脚本（DB02.SQL）保存上交以备教师检查和后续实验使用。

四、注意事项
1.SQL Server数据库对应的物理文件。

2.存储过程sp_helpdb的功能和执行。

3.数据库大小的估算和设置。

实验3 SQL Server 2000表操作
一、实验目的
1.了解表设计和表结构相关知识。

2.了解SQL Server 2000中的常用数据类型。

3.掌握应用企业管理器创建和修改表的方法。

4.掌握应用企业管理器查看和删除表方法。

5.掌握应用T-SQL创建和修改表的方法。

6.掌握应用T-SQL查看和删除表的方法。

7.了解表和数据库的关系。

二、实验要求
1.创建好的“网上购物系统”数据库eshop中的members表、products表和orders表。

2.保存实验结果到网络文件夹。

三、实验步骤
1.在eshop数据库中创建表会员表members，SQL代码如下所示：
USE eshop
CREATE TABLE members
(
m_account V ARCHAR(20) NOT NULL,
m_name V ARCHAR(20),
m_sex CHAR(2),
m_birth DATETIME,
m_address VARCHAR(50),
m_salary MONEY,
m_password V ARCHAR(20)
)
GO
2.查看members表的相关信息，SQL代码如下所示：
USE eshop
EXEC sp_help members
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图3-1所示。

3.在members表中新增一列电子邮件m_mail（V ARCHAR，20），SQL代码如下所示：
USE eshop
ALTER TABLE members
ADD m_mail VARCHAR(20)
4.将members表中的现有列m_address修改为（V ARCHAR,30），SQL代码如下所示：
USE eshop
ALTER TABLE members
ALTER COLUMN m_address V ARCHAR(30)
5.删除步骤3中所建列m_mail，SQL代码如下所示：
USE eshop
ALTER TABLE members
DROP COLUMN m_mail
6.删除表members，SQL代码如下所示：
DROP TABLE members
7.将步骤1-6脚本保存（TABLE01.SQL）上交以备教师检查
8.应用企业管理器完成步骤1-6，并与T-SQL操作进行比较。

9.在eshop数据库中按要求创建表会员表members，SQL代码如下所示：USE eshop
CREATE TABLE members
(
m_account V ARCHAR(20),
m_name V ARCHAR(20),
m_sex CHAR(2),
m_birth DATETIME,
m_address VARCHAR(50),
m_salary MONEY,
m_password V ARCHAR(20)
)
GO
10.在eshop数据库中按要求创建表商品表products，SQL代码如下所示：
USE eshop
CREATE TABLE products
(
p_no CHAR(10),
p_name V ARCHAR(30),
p_date DA TETIME,
p_quantity INT,
p_price MONEY,
p_information V ARCHAR(50)
)
GO
11.在eshop数据库中按要求创建表订单表orders，SQL代码如下所示：
USE eshop
CREATE TABLE orders
(
m_account V ARCHAR(20),
p_no CHAR(10),
o_quantity INT,
o_date DA TETIME,
o_confirm_state CHAR(1),
o_pay_state CHAR(1),
o_send_state CHAR(1)
)
GO
12.将步骤8-10建表脚本（TABLE02.SQL）保存上交以备教师检查及后续实验使用。

四、注意事项
1．数据类型CHAR和V ARCHAR的区别。

2．在对表进行操作之前，必须选择表所在数据库。

3．存储过程sp_help的功能和执行。

4．修改表命令的多种类型。

实验4 SQL Server 2000表中记录操作
一、实验目的
1.掌握INSERT INTO语句的方法。

2.了解INSERT FROM语句的方法。

3.掌握UPDATE语句的方法。

4.掌握DELETE语句的方法。

二、实验要求
1.在已经创建好的eshop数据库中的各表中添加样例数据。

2.保存实验结果到网络文件夹。

三、实验步骤
1.在eshop数据库的members表中增加2条记录，内容如下：
‘jinjin’,‘津津有味’,‘女’,‘1982-04-14’,‘北京市’,8200.0,‘jinjin’
‘liuzc518’,‘刘志成’,‘男’,‘1972-05-18’,‘湖南株洲’,3500.0,‘liuzc518’
SQL代码如下所示：
USE eshop
INSERT INTO members V ALUES('jinjin','津津有味','女','1982-04-14','北京市',8200.0, 'jinjin')
INSERT INTO members V ALUES('liuzc518','刘志成','男','1972-05-18','湖南株洲',3500.0,'liuzc518')
2.将姓名为“津津有味”的姓名修改为“刘津”，SQL代码如下所示：
USE eshop
UPDA TE members
SET m_name='刘津'
WHERE m_name='津津有味'
3.将m_sex（性别）为‘男’且m_address（家庭地址）为‘湖南株洲’的会员的m_salary（月薪）
增加20%，SQL代码如下所示：
USE eshop
UPDA TE members
SET m_salary=m_salary*(1+0.20)
WHERE m_sex='男'
AND m_address='湖南株洲'
4.删除m_address（家庭地址）为‘北京市’的会员记录，SQL代码如下所示：
USE eshop
DELETE
FROM members
WHERE m_address='北京市'
5.删除members表中所有记录，SQL代码如下所示：
USE eshop
DELETE
FROM members
6.将步骤1-5脚本（RECORD01.SQL）保存上交以备教师检查。

7.应用企业管理器完成步骤1-5，并与T-SQL操作进行比较。

8.在eshop数据库的members表中添加所有样例数据，SQL代码如下所示：
USE eshop
INSERT INTO members V ALUES('Jinjin', '津津有味', '女', '1982-04-14', '北京市', 8200.0, 'jinjin') INSERT INTO members V ALUES('Lfz', '刘法治', '男', '1976-08-26', '天津市', 4500.0, 'lfz0826') INSERT INTO members V ALUES('liuzc518', '刘志成', '男', '1972-05-18', '湖南株洲', 3500.0, 'liuzc518')
INSERT INTO members V ALUES('Wangym', '王咏梅', '女', '1974-08-06', '湖南长沙', 4000.0, 'wangym0806')
INSERT INTO members V ALUES('Zhangzl', '张自梁', '男', '1975-04-20', '湖南株洲', 4300.0, 'zhangzl')
INSERT INTO members V ALUES('zhao888', '赵爱云', '男', '1972-02-12', '湖南株洲', 5500.0, 'zhao888')
9.在eshop数据库的products表中添加所有样例数据，SQL代码如下所示：
USE eshop
INSERT INTO products V ALUES('0130810324', '清华同方电脑', '2005-12-11', 7, 8000.0, '优惠多多')
INSERT INTO products V ALUES('0140810330', '洗衣粉', '2005-05-31', 1000, 8.6, '特价销售') INSERT INTO products V ALUES('0140810332', '红彤彤腊肉', '2005-05-20', 43, 15.0, '是一种卫生食品')
INSERT INTO products V ALUES('0140810333', '力士牌香皂', '2005-05-06', 22, 6.0, '是一种清洁用品')
INSERT INTO products V ALUES('024*******', '电动自行车', '2005-05-31', 10, 1586.0, '价廉物美') INSERT INTO products V ALUES('024*******', '自行车', '2005-05-31', 10, 586.0, '价廉物美') INSERT INTO products V ALUES('0910810001', '爱国者MP3', '2005-05-31', 100, 450.0, '价廉物美') INSERT INTO products V ALUES('0910810002', '商务通', '2005-05-20', 10, 850.0, '价廉物美') INSERT INTO products V ALUES('0910810003', '名人好记星', '2005-05-31', 100, 550.0, '价廉物美') INSERT INTO products V ALUES('0910810004', '奥美嘉U盘', '2005-05-31', 100, 350.0, '价廉物美')
10.在eshop数据库的orders表中添加所有样例数据，SQL代码如下所示：
USE eshop
INSERT INTO orders V ALUES('jinjin', '0910810004', 2, '2005-06-06', '1', '0', '0')
INSERT INTO orders V ALUES('jinjin', '0910810004', 1, '2005-08-09', '1', '1', '1')
INSERT INTO orders V ALUES('lfz', '0910810001', 1, '2005-08-09', '0', '0', '0')
INSERT INTO orders V ALUES('lfz', '0910810004', 2, '2005-06-06', '1', '1', '1')
INSERT INTO orders V ALUES('lfz', '0910810004', 2, '2005-08-09', '1', '1', '1')
INSERT INTO orders V ALUES('liuzc518', '0140810324', 1, '2005-10-09', '0', '0', '0')
INSERT INTO orders V ALUES('liuzc518', '0910810001', 1, '2005-10-09', '1', '1', '0')
INSERT INTO orders V ALUES('liuzc518', '0910810004', 2, '2005-10-09', '1', '1', '0')
INSERT INTO orders V ALUES('wangym', '0910810001', 1, '2005-08-09', '1', '0', '0')
INSERT INTO orders V ALUES('zhao888', '024*******', 2, '2005-06-06', '1', '1', '0')
11.将步骤8-10命令脚本（RECORD02.SQL）保存上交以备教师检查及后续实验使用。

四、注意事项
1.使用“SELECT * FROM <表名>”语句查看修改记录。

2.往基本表中插入记录时表名后面可带列名表（插入指定列及列顺序，指定对应列的值），也
可不带列名表（指定所有列的值，按固定顺序）。

实验5 SQL Server 2000简单查询
一、实验目的
1.掌握SELECT语句的基本方法。

2.掌握从表中查询特定行的方法。

3.掌握从表中查询前N行的方法。

4.掌握从查询结果中去掉重复行的方法。

5.掌握使用列的别名的方法。

6.掌握从表中查询特定列的方法。

7.掌握查询表中计算列的方法。

8.掌握查询语句中的通配符的使用。

二、实验要求
1.应用SELECT语句对数据库eshop中数据进行指定条件的简单查询。

2.保存实验结果到网络文件夹。

三、实验步骤
1.查询products表中p_price（商品价格）在800以上的商品详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM products
WHERE m_price > 800
2.查询products表中p_quantity（商品数量）在20和50之间的商品编号、商品名称和商品数
量，SQL代码如下所示：
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity >= 20
AND p_quantity <= 50
或
USE eshop
SELECT p_no, p_name, p_quantity
FROM products
WHERE p_quantity BETWEEN 20 AND 50
3.查询orders表中各会员购买商品的总量，并以汉字列标题形式输出会员帐号，商品总额，
SQL代
码如下所示：
USE eshop
SELECT m_account 会员帐号, o_quantity 商品总额
FROM orders
4.查询members表中家庭地址为“湖南”的会员详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_address LIKE '湖南%'
5.查询members表中年龄大于30且性别为“男”的会员详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE DATEDIFF(YY,m_birth,GETDA TE())>30
AND m_sex = '男'
6.查询orders表各商品销售总量前3名的商品编号和销售总量，SQL代码如下所示：
USE eshop
SELECT TOP 3 p_no, o_quantity
FROM orders
ORDER BY o_quantity DESC
7.查询orders表中购买过商品的会员帐号，要求去掉重复行，SQL代码如下所示：
USE eshop
SELECT DISTINCT m_account
FROM orders
8.查询orders表已确认、已支付和已配送的订单详细信息，SQL代码如下所示：
USE eshop
SELECT *
FROM orders
WHERE o_confirm_state = '1'
AND o_pay_state = '1'
AND o_send_state = '1'
9.将步骤1-8命令脚本（SELECT01.SQL）保存上交以备教师检查。

四、注意事项
1.注意查询要求的详细描述，先确定要查询的表然后确定要输出的列和行。

2.T-SQL日期函数的使用。

3.如果没有指定输出列，默认为输出所有列。

实验6 SQL Server 2000高级查询
一、实验目的
1.掌握查询结果排序的方法。

2.掌握排序结果进行计算的方法。

3.掌握排序结果分组的方法。

4.掌握排序结果分组后再选择的方法。

二、实验要求
1.应用SELECT语句对数据库eshop中数据进行指定条件的高级查询。

2.保存实验结果到网络文件夹。

三、实验步骤
1.查询性别为“男”的会员详细信息，查询结果按月薪降序排列，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_sex = '男'
ORDER BY m_salary DESC
2.查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄
降序排列，SQL代码如下所示：
USE eshop
SELECT m_account, m_name, YEAR(GETDA TE())-YEAR(m_birth) 年龄
FROM members
ORDER BY m_address, m_birth
或
USE eshop
SELECT m_account, m_name, DATEDIFF(YY, m_birth, GETDATE()) 年龄
FROM members
ORDER BY m_address, m_birth
3.查询会员帐号为’liuzc’所购买的商品号和订购日期，并按订购日期升序排列，SQL代码如
下所示：
USE eshop
SELECT p_no, o_date
FROM orders
WHERE m_account = 'liuzc'
ORDER BY o_date
4.查询购买商品号为’0910810004’总人数，SQL代码如下所示：
USE eshop
SELECT COUNT(*)
FROM orders
WHERE p_no = '0910810004'
5.查询2005年6月6日前，所有商品的订购总量，要求输出商品号和订购总量，SQL代码如
下所示：
USE eshop
SELECT p_no, SUM(o_quantity)
FROM orders
WHERE o_date < '2005-6-6'
GROUP BY p_no
6.查询所有会员的平均月薪，最高月薪和最低月薪之和，SQL代码如下所示：
USE eshop
SELECT A VG(m_salary)+MAX(m_salary)+MIN(m_salary)
FROM members
7.查询所有会员购买商品的种类和，要求输出会员号和商品种类和，SQL代码如下所示：
USE eshop
SELECT m_account, COUNT(DISTINCT p_no)
FROM orders
GROUP BY m_account
8.查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量，SQL代码
如下所示：
USE eshop
SELECT TOP 1 p_no, SUM(o_quantity)
FROM orders
GROUP BY p_no
HA VING SUM(o_quantity) > 10
ORDER BY SUM(o_quantity) DESC
9.将步骤1-8命令脚本（SELECT02.SQL）保存上交以备教师检查。

四、注意事项
1.聚合函数的作用范围（在未使用GROUP BY子句时，其作用范围为要输出的所有记录，使
用了GROUP BY子句，则其作用范围为分组后的记录）。

2.分组后输出列的选择。

输出列要么在GROUP BY子句中，要么在聚合函数中。

3.WHERE和HA VING的区别。

4.本次实验不涉及联接查询。

实验7 SQL Server 2000联接查询
一、实验目的
1.熟悉等值联接查询的方法。

2.熟悉非等值联接查询的方法。

3.熟悉自身联接查询的方法。

4.熟悉外联接查询的方法。

5.熟悉复合条件联接的方法。

6.熟悉集合查询的方法。

7.熟悉子查询的方法。

8.子查询和联接查询的区别及联系。

9.子查询和联接查询的相互转换。

二、实验要求
1.应用SELECT语句对数据库eshop中数据进行指定条件的联接查询。

2.保存实验结果到网络文件夹。

三、实验步骤
1.查询购买了商品号为“0910810004”的会员号和姓名，并以汉字标题显示，SQL代码如下
所示：
USE eshop
SELECT DISTINCT members.m_account 会员号, m_name 姓名
FROM members
JOIN orders
ON members.m_account = orders.m_account
WHERE p_no = '0910810004'
2.查询购买了商品名称为“爱国者MP3”的会员号、姓名和商品价格，SQL代码如下所示：
USE eshop
SELECT members.m_account, m_name,p_price
FROM members
JOIN orders
ON members.m_account = orders.m_account
JOIN products
ON orders.p_no = products.p_no
AND p_name = '爱国者mp3'
3.查询比“张自梁”月薪高的而和他不是同一地址的会员姓名和年龄，SQL代码如下所示：
USE eshop
SELECT A.m_name,YEAR(GETDATE())-YEAR(A.m_birth)
FROM members A
JOIN members B
ON A.m_account <> B.m_account
AND B.m_name = '张自梁'
AND A.m_salary > B.m_salary
AND A.m_address <> B.m_address
4.使用exists查询购买了“0910810004”商品的会员号和姓名，SQL代码如下所示：
USE eshop
SELECT m_account, m_name
FROM members
WHERE EXISTS ( SELECT *
FROM orders
WHERE members.m_account = orders.m_account
AND p_no = '0910810004')
5.使用in查询与“刘法治”购买至少同一种商品的会员号和商品号，SQL代码如下所示：USE eshop
SELECT DISTINCT A.m_account, A.p_no
FROM orders A
WHERE p_no IN ( SELECT p_no
FROM orders B
WHERE A.m_account <> B.m_account
AND B.m_account IN (SELECT m_account
FROM members
WHERE B.m_account = members.m_account
AND m_name='刘法治'))
6.使用简单查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，SQL
代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图7-1所示。

7.使用集合查询家庭地址为“湖南株洲”的会员以及年龄在30岁以上的会员详细信息，并与
步骤6进行比较，SQL代码如下所示：
USE eshop
SELECT *
FROM members
WHERE m_address = '湖南株洲'
UNION
SELECT *
FROM members
WHERE (YEAR(GETDATE())-YEAR(m_birth)) > 30
再按F5或点击工具栏上的运行按钮“”，查看运行结果，如图7-2所示。

8.将members表和orders表之间的左向外联接包括所有会员的信息，包括没有购买商品的会
员，SQL代码如下所示：
USE eshop
SELECT members.*, orders.*
FROM members
LEFT OUTER JOIN orders
ON members.m_account = orders.m_account
9.将步骤1-8命令脚本（SELECT03.SQL）保存上交以备教师检查。

四、注意事项
1.联接的类型（内联接，左外联接，右外联接，完整外部联接）
2.子查询的应用。

3.联接查询和子查询的相互转换。

4.使用JOIN与使用WHERE子句的区别。

实验8 SQL Server 2000的视图操作
一、实验目的
1.了解视图的功能。

2.掌握应用企业管理器创建和查看视图的方法。

3.掌握应用企业管理器视图修改和删除视图的方法。

4.掌握应用T-SQL创建和查看视图的方法。

5.掌握应用T-SQL修改和删除视图的方法。

二、实验要求
1.创建student数据库中的相关视图。

2.保存实验结果到网络文件夹。

三、实验步骤
1.在members表中创建地址为“湖南株洲”的会员的视图V_addr，SQL代码如下所示：
CREATE VIEW V_addr
AS
SELECT *
FROM members
WHERE m_address = '湖南株洲'
2.在orders表中创建购买了商品号为“0910810004”商品的视图V_buy，SQL代码如下所示：
CREATE VIEW V_buy
AS
SELECT *
FROM orders
WHERE p_no = '0910810004'
3.在members和orders表上创建“湖南株洲”的会员购买了商品号为“0910810004”商品的
视图V_addr_buy，SQL代码如下所示：
CREATE VIEW V_addr_buy
AS
SELECT members.*
FROM members
JOIN orders
ON members.m_account = orders.m_account
AND p_no = '0910810004'
AND m_address = '湖南株洲'
4.在视图V_addr上查询性别为“男”的会员信息，SQL代码如下所示：
USE eshop
SELECT *
FROM V_addr
WHERE m_sex = '男'
5.在视图V_addr中增加一条记录（内容如下），并查看members表中记录的改变情况。

记录
内容如下：（T-SQL）
‘fengxk’，‘冯向克’，‘男’，‘1978-06-28’，‘北京市’，5000.0，‘fxk0628’
SQL代码如下所示：
USE eshop
INSERT INTO V_addr V ALUES('fengxk', '冯向克', '男', '1978-06-28', '北京市',5000.0, ' fxk0628')
6.将视图V_addr中会员号为“liuzc518”的会员的密码修改为“liuzc0518”，并查看members
中记录的改变情况，SQL代码如下所示：
USE eshop
UPDA TE V_addr
SET m_password = 'liuzc0518'
WHERE m_password = 'liuzc518'
7.在V_addr中删除会员号为“fengxk”的记录，并查看members中记录的改变情况，SQL代
码如下所示：
USE eshop
DELETE
FROM V_addr
WHERE m_account = 'fengxk'
再按F5或点击工具栏上的运行按钮“”，比较运行前后的结果，发现members中记录并未发
8.删除视图V_addr_buy、V_buy和V_addr，SQL代码如下所示：
USE eshop
DROP VIEW V_addr_buy, V_buy, V_addr
9.将步骤1-8命令脚本（VIEW01.SQL）保存上交以备教师检查。

10.在企业管理器中完成步骤1-8，并与T-SQL操作进行比较。

四、注意事项
1.创建视图时SELECT语句的使用。

2.视图定义的修改和通过视图修改表中数据区别。

3.视图定义删除和通过视图删除表中的数据区别。

4.视图（虚表）和基表的操作的区别。

实验9 SQL Server 2000的存储过程
一、实验目的
1.掌握使用向导创建存储过程并更新相应数据
2.掌握使用T-SQL编程的方法
3.掌握使用T-SQL语句创建一个存储过程并验证
4.掌握创建和执行带参数的存储过程
5.熟练使用系统存储过程、系统函数
6.掌握用在企业管理器中管理存储过程
二、实验要求
1.创建一个不带参数的存储过程。

2.创建一个带参数的存储过程p_count。

3.保存并上交实验结果。

三、实验步骤
1.写一个程序，计算9到999的和，SQL代码如下所示：
DECLARE @i INT,@sum INT
SELECT @i=9,
@sum=0
WHILE @i <= 99
BEGIN
SELECT @sum = @sum + @i,
@i = @i + 1
END
PRINT '9+10+...+99 = '+CONVERT(V ARCHAR,@sum)
2.创建存储过程pr_buy，返回指定会员帐号（M_account ）已付款购买的商品信息，SQL代
码如下所示：
USE eshop
GO
CREATE PROCEDURE pr_buy
@account V ARCHAR(20)
AS
SELECT *
FROM orders
WHERE m_account = @account
3.执行存储过程pr_buy显示帐号为liuzc518会员的购买商品信息，SQL代码如下所示：
USE eshop
EXEC pr_buy 'liuzc'
4.将步骤1-3命令脚本（PROC01.SQL）保存上交以备教师检查。

5.在企业管理器中，对pr_buy进行如下的操作：
（1）查看其定义的文本
打开“SQL Server企业管理器”，定位到eshop数据库，展开eshop数据库的对象，再定位到“存储过程”项，右击pr_buy存储过程，弹出快捷菜单，如图9-1所示。

从快捷菜单中选择“属性”，将
（2）改名为pr_test
打开“SQL Server企业管理器”，定位到eshop数据库，展开eshop数据库的对象，再定位到“存储过程”项，右击pr_buy存储过程，弹出快捷菜单，如图9-3所示。

从快捷菜单中选择“重命名”，
将存储过程pr_buy的名称重命名为pr_test后，SQL Server系统将弹出“重命名”对话框，以提示用
户是否使重命名存储过程有效，单击“是”使重命名生效，单击“否”使重命名失效，即维护存储过程的名称不变，如图9-5所示。

选择“是”以后，如果系统执行重命名存储过程成功，将弹出已成功重命名存储过程对象的对话框，如图9-6所示，用户单击“确定”即可；否则返回重命名存储过程不成功的信息。