2．使用UPDATE命令修改基本表中的数据
1）将编号为‘F70006’的商品的单价改为12.00元 update supplying set price=12.00 where cid='F70006'
（2）将名称为‘家乐福’的客户的等级改为VIP客户。 update customer set CUlevel=2
where CUname='家福乐'
（3） 将编号为‘A102’ 供应商供应商品的价格全部打八折 update saleDetail set SDdiscount = 0.8
from supplying a, commodity b, saleDetail c
where a.Cid=b.Cid and a.Sid='A102' and c.Cid=b.Cid 3．使用DELETE命令删除基本表中的数据 （1）删除采购明细表中的所有记录 delete from stockDetail
（2）从采购单表中删除2005年以前签订的采购单 delete from stock
where year(CGdate)<=2005
4．使用SELECT…INTO语句向表中成批插入记录
（1）将供应商‘A102’供应的商品和供应价格存到一个名字为‘A102_commodity’的表中 create table A102_commodity (
Sid char(10), --供应商ID Cid char(10), --商品ID price float, --商品单价 PRIMARY KEY (Sid,Cid) )
insert into A102_commodity select *
from supplying where Sid='A102'
11
（2）将所有的VIP客户信息存储到一个名字为‘VIP_Customer’的表中 create table VIP_Customer (
CUid char(10) PRIMARY KEY, --客户ID CUname char(30), --客户姓名
CUtelephone char(16), --客户联系电话
CUlevel int --客户等级：1，普通客户；2，VIP客户 )
insert into VIP_Customer select *
from customer where CUlevel=2
5．使用INSERT…SELECT语句向表中成批插入记录
（1）新建一个名称为‘achievement’的表，其属性包括销售单ID，销售单总金额，销售日
期
create table achievement (
SAid char(12) PRIMARY KEY, --销售单ID SAmoney float ,--销售单总金额 SAdate datetime ,---销售日期 )
（2）将编号为‘S0002’的销售员签订的销售单信息存储到achievement表中。 insert into achievement(SAid,SAmoney,SAdate) select SAid,SAmoney,SAdate from sale
where SAid in ( select SAid from sale where Eid= 'S0002') 三、自我测试
1．从采购明细表中删除所有从供应商‘A153’处采购的编号为‘F70006’商品的明细信息 delete from stockDetail
where Cid in(select Cid from stockDetail where Cid='F70006')
2．从销售明细表中删除2005年以前签订的销售单的销售明细记录 delete from saleDetail
where SAid in(select b.SAid from saleDetail a,sale b where a.SAid=b.SAid and year(SAdate)<'2005')
12
实验三 单表查询
一、实验目的与要求
1．掌握基本的SELECT…FROM语句的用法
2．能应用SELECT语句及其子句解决常见的查询问题 3．掌握模糊查询的使用方法
4．能应用聚集函数和GROUP BY子句进行统计查询
二、实验内容
1． 查找所有商品的名称和库存信息 2． select Cname,Cstorage 3． from commodity
4．
5． 查找‘北京’地区的供应商 6． select *
7． from supplier
8． where Sarea='北京'
9． 查找所有销售人员的基本信息
select *
from Employee
10． 查找签订日期在‘2005-5-1’和‘2006-3-1’之间签订的采购单的基本信息
select *
13
from stock
where CGdate between '2005-5-1'and'2006-3-1'
11． 查找2006年签订的所有销售单，要求显示销售单ID、供应商ID和销售单总金额
select SAid,Cuid,SAmoney from sale
where year(SAdate)='2006'
12． 查找每个采购员所签订的采购单的数目，显示结果按采购单数目降序排序
select Eid,count(Eid)采购单数目 from stock group by Eid
order by count(Eid) desc
13． 查询每个销售员签订的销售单总金额
select Eid,sum(SAmoney)销售单总金额 from sale, group by Eid
14． 查找2005年与每个客户签订的销售单的总金额，只显示总金额大于500000万的客
户ID和总金额
select CUid,sum(SAmoney)销售单总金额 from sale
group by CUid,SAmoney,SAdate
having year(SAdate)=2005 AND sum(SAmoney)>500000
15．
查询2005年签订的金额最大的一笔销售单，要求显示采购员ID、客户ID和销售
14
金额
select Eid,CUid,SAmoney from sale
where SAmoney in(select max(SAmoney) from sale where year(SAdate)=2005 )
10． 查询2005年与编号为‘A002’的供应商签订的所有采购单的总金额
select Sid,sum(CGmoney) from stock
where year(CGdate)=2005 AND Sid='A002' group by Sid
11． 查询名称中含有‘机’字的所有商品的ID、名称和库存量
select Cid,Cname,Cstorage from commodity
where Cname like'%机%'
12． 查找第二个汉字是‘翔’的商品名称和单价。
select Cname,price
from supplying a,commodity b
where a.Cid=b.Cid and Cname like '_翔%'
三、自我测试
1．查询2005年签订的销售合同总额排名前5名的销售员的ID和合同总额，要求结果按照合同总额降序排列
select top 5 Eid销售员ID,sum(SAmoney)合同总额 from sale
where year(SAdate)=2005 group by Eid
order by sum(SAmoney)desc
15