实验二 数据查询和更新
一、
1. 2. 3. 4. 5. 6. 7. 二、
实验目的和要求
了解脚本文件的使用；
掌握SELECT语句在单表查询中的应用； 掌握复杂查询的使用方法； 掌握多表连接的方法； 掌握子查询的使用方法 掌握SQL的数据更新操作
掌握视图的定义、查询和更新操作 实验内容
1、 使用脚本文件（create.sql）创建CAP数据库。
操作步骤：
① 新建查询，将附录中的create.sql的内容输入到查询窗口中，并保存为
create.sql ② 分析并执行 2、 使用T-SQL装载数据
① 设置外围应用配置器
在开始菜单中：所有程序－SQL Server 2005 －配置工具 －SQL Server 外围应用配置器。
在“外围应用配置器”窗口中选择“功能的外围应用配置器”，并在弹出的窗口里选择“启用OPENROWSET和OPENDATASOURCE支持”。单击“确定”按钮。
② 新建查询，并输入以下语句：
--以下代码通过使用openrowset函数为特定目的导入数据 Insert into customers select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[产品$]); Insert into agents select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[代理商$]); insert into products select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[产品$]); insert into orders select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[订单$]);
灰色区域请大家改为cap.xls在本机的路径 3、 查询和更新操作
1) 找出所有客户、代理商和商品都在同一城市的三元组（cid, aid, pid）。
Select aid,cid,pid
from agents,customers,products
where agents.city=customers.city and agents.city=products.city and products.city=customers.city
2) 找出所有客户、代理商和商品不都在同一城市（可能有两个在同一城市）的三元
组（cid, aid, pid）。
Select aid,cid,pid
from agents,customers,products
where agents.city！=customers.city and agents.city！=products.city
and products.city！=customers.city
3) 找出所有在同一城市的代理商的aid对。
Select s1.aid,s2.aid from agents s1,agents s2 where s1.city=s2.city and s1.aid!=s2.aid
4) 找出同时订购了商品p01和p07的客户的cid值。（若找出客户的cname呢？）
Select cid from orders
where pid='p07'and cid IN ( SELECT cid from orders
where pid='p01' )
5) 统计各个产品的销售总量。
Select sum(dollars) from orders group by pid
6) 当某个代理商所订购的某样产品的总量超过1000时，打印出所有满足条件的产品
和代理商的ID以及这个总量。
7) 找出订购了产品p05的顾客的名字。
Select cname
from orders,customers
where pid='p05' and customers.cid=orders.cid
8) 检索满足以下条件的顾客-代理商的姓名对（cname,aname）,其中的顾客cname通
过代理商aname订了货。
9) 找出至少被两个顾客订购的产品的pid值。
10) 在customers表中插入一个新行。
Insert into customers(cid,cname,city) values (‘c007’,’WinDix’,’Dallas’); 11) 检索customers表中discnt值为空的行。
Select *
from customers
where discnt IS NULL
12) 检索客户以及他们订购商品的详细信息。（用外联接）
13) 检索有关住在Duluth或Dallas的代理商的所有信息。（要求使用IN谓词实现）
SELECT * from agents where city in (
select city from agents
where city='Duluth' or city='Dallas' )
14) 找出通过住在Duluth或Dallas的代理商订货的所有顾客的姓名和折扣率。（要求
使用IN谓词实现）
15) 求所有满足以下条件的顾客的cid值：该顾客的discnt的值小于任一住在Duluth
的顾客的discnt值。
16) 检索没有通过代理商a05订货的所有顾客的名字。
提示：可以使用not in 或 <>all方式实现。
17) 检索一个包含顾客所在的或者代理商所在的城市的名称。（使用UNION实现）
18) 在orders表中插入一个新行。
Insert in to orders(ordno,month,cid,aid,pid) values (1107,’aug’,‘c006’,’a04’,’p01’); 19) 创建一个名为swcusts的表，它包含住在西南部的所有顾客，并向该表中插入所有
来自Dallas或Austin的顾客。
20) 将所有住在New York的代理商的佣金率提高10%。
update agents set per=per+10
where city='New York'
21) 用customers表中最新的discnt值来更新swcusts表中的各行discnt的值。
22) 删除所有住在New York的代理商。
delete
from agents
where city='New York'
23) 创建一个agentorders视图，它扩展了表orders的行，包括订货的代理商的详细信
息。
24) 利用agentorders视图查询代理商Brown的所有订单信息
25) 创建cacities视图，该视图列出表customers和表agents中所有配对的城市，其中
该顾客通过该代理商订购了商品。
26) 创建custs视图
create view custs as select * from customers where discnt<=15.0 with check option; 27) 对custs视图进行更新操作。
Update custs set discnt=discnt+4;
4、 数据库备份
操作步骤：
③ 在“对象资源管理器”窗口中，右键单击要备份的数据库。在弹出菜单中选择“任
务”，单击“备份”。
④ 在上面窗口中，选择备份类型为“完整”。 ⑤ 单击“添加”按钮设置目标属性。（要求保持数据库备份到用户盘，如E盘或F盘，
便于下次实验实施数据库还原的操作）
示例是保存到f:\\backup文件夹下，备份文件名为cap.bak
⑥ 设置路径后，单击“确定”按钮。随后单击“备份”窗口中的“确定”按钮。这
将在指定位置创建数据库的一个备份。
三、
思考题
1、 空值是如何处理的？（排序、分组、比较、使用集合函数等） 四、
五、
附录
出现问题及解决方案
1、脚本文件（create.sql）的内容 -- create.sql
-- SQL statements for table creation for CAP database create database CAP
on
(name=cap_data,--数据文件的逻辑名称,注意不能与日志逻辑同名 filename='d:\\sql_data\\cap_data.mdf' ,--物理名称，注意路径必须存在 size=10,--数据初始长度为5M maxsize=50,--最大长度为10M
filegrowth=5%)--数据文件每次增长5% log on
( name=cap_log,
filename='d:\\sql_data\\cap_log.ldf ' , size=2 , maxsize=5 , filegrowth=1) go
use CAP go
create table customers (cid char(4) not null, cname varchar(13), city varchar(20), discnt real, primary key(cid));
create table agents (aid char(3) not null, aname varchar(13), city varchar(20), per smallint, primary key (aid));
create table products (pid char(3) not null, pname varchar(13), city varchar(20), quantity integer, price money, primary key(pid));
create table orders (ordno integer not null, month char(3), cid char(4), aid char(3), pid char(3),
qty integer, dollars money, primary key(ordno));
2、 脚本文件（import.sql）的内容
Insert into customers select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[客户$]);
Insert into agents select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[代理商$]);
insert into products select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[产品$]);
insert into orders select * from
openrowset('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=e:\\cap.xls',[订单$]);