中 南 大 学
《数据库原理》实验报告
学生姓名 学 号 专业班级
指导教师 盛津芳 学 院 信息科学与工程学院 完成时间 2011年1月
实验一、实验二
一、实验目的：
1、 2、
了解DBMS 的工作环境和系统构架； 熟悉通过SQL 对数据库进行操作
二、实验内容及结果（附代码）：
1. 创建表、更新表和实施数据完整性
（1）运行给定的SQL Script，建立数据库GlobalToyz 。 （2）了解表的结构，建立所有表的关系图。
（3）利用系统定义的存储过程sp_helpdb查看数据库的相关信息，例如所有者、大小、创建日期等。
（4）查看所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）
（5）把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys 的新表中。
select *
into PremiumToys from Toys
where mToyRate>20
（6）对表Toys 实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。
alter table toys
add check(siToyQoh>0 and siToyQoh
（7）给id 为‘000001’玩具的价格增加$1。
update toys
set mToyRate=mToyRate+1 where cToyId=000001;
（8）列出表PickofMonth 中的所有记录，并显示中文列标题。
2. 查询数据库
（1）显示属于California 和Illinoi 州的顾客的名、姓和emailID
select vFirstName,vLastName,vEmailId from dbo.Shopper
where cState IN ('California','Illinoi');
（2）显示定单号码、顾客ID ，定单的总价值，并以定单的总价值的升序排列。
select cOrderNo,cShopperId,mTotalCost from dbo.Orders
ORDER BY mTotalCost ASC;
（3）显示在orderDetail 表中vMessage 为空值的行。
select *
From dbo.OrderDetail
where vMessage IS NULL;
（4）显示玩具名字中有“Racer ”字样的所有玩具的基本资料。
select *
From dbo.Toys
Where vToyName LIKE '%Racer%';
（5）根据2000年的玩具销售总数，显示“Pick of the Month”玩具的前五名玩具的ID 。
select top 5 cToyId,SUM(iTotalSold) as total from PickOfMonth where iYear='2000' group by cToyId
Order By total DESC;
（6）根据OrderDetail 表，显示玩具总价值大于￥50的定单的号码和玩具总价值。
select cOrderNo,mToyCost From dbo.OrderDetail
Where mToyCost>50;
（7）显示一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date)
select
cOrderNo,dShipmentDate,dActualDeliveryDate,datediff(dd,dShipmentDate,dActualDeliveryDate) DaysInTransit
from shipment
（8）显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。
select vToyName,cBrandName,cCategory from Category,Toys,ToyBrand
where toys.cCategoryId=Category.cCategoryId and Toys.cBrandId=ToyBrand.cBrandId order by cBrandName
（9）以下列格式显示所有购物者的名字和他们的简称：（Initials, vFirstName, vLastName）, 例如Angela Smith的Initials 为A.S 。
select left(vFirstName,1)+'.'+left(vLastName,1) Initials,vFirstName,vLastName from shopper
（10）显示所有玩具的平均价格，并舍入到整数。
select ROUND(avg(mtoyrate),0) averagerate from toys
（11）显示所有购买者和收货人的名、姓、地址和所在城市，要求显示结果中的重复记录。
select vFirstName,vLastName,vAddress,cCity from Shopper union
select vFirstName,vLastName,vAddress,cCity from Recipient
（12）显示没有包装的所有玩具的名称。（要求用子查询实现）
select vToyName from toys
where cToyId IN (select cToyId from OrderDetail
where cWrapperId is null)
（13）显示已收货定单的定单号码以及下定单的时间。（要求用子查询实现）
select cOrderNo,dOrderDate from Orders
where cOrderNo IN (select cOrderNo from Shipment
where dActualDeliveryDate is not null)
（14）显示一份基于Orderdetail 的报表，包括cOrderNo,cToyId 和mToyCost ，记录以cOrderNo 升序排列，并计算每一笔定单的玩具总价值。（提示：使用运算符COMPUTE BY）。 3. 视图
（1）定义一个视图，包括购买者的姓名、所在州和他们所订购玩具的名称、价格和数量。
（2）基于（1）中定义的视图，查询显示所有California 州的购买者的姓名和他们所订购玩具的名称及数量。
（3）视图定义如下：
CREATE VIEW vwOrderWrapper AS
SELECT cOrderNo, cToyId, siQty, vDescription, mWrapperRate FROM OrderDetail JOIN Wrapper
ON OrderDetail.cWrapperId = Wrapper.cWrapperId
以下更新命令，在更新siQty 和mWrapperRate 属性使用了以下更新命令时出现错误：
UPDATE vwOrderWrapper
SET siQty = 2, mWrapperRate = mWrapperRate + 1 WHERE cOrderNo = ‘000001’
修改更新命令，以更新基表中的值，观察基表中的修改结果。
三、参考书籍：
《数据库系统概论》（第四版） 王珊，萨师煊 高等教育出版社
实验三、实验四
一、实验目的：
1、 了解DBMS 的工作环境和系统构架； 2、 熟悉通过SQL 对数据库进行操作
二、实验内容：
1. 实验三：存储过程与触发器
（1）编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价格接近$24.5为止。此外，任何玩具的最大价格不应超过$53。
（2） 创建一个称为prcCharges 的存储过程，它返回某个定单号的装运费用和包装费用。
（3）创建一个称为prcHandlingCharges 的过程，它接收定单号并显示经营费用。PrchandlingCharges 过程应使用prcCharges 过程来得到装运费和礼品包装费。
提示：经营费用=装运费+礼品包装费
（4）在OrderDetail 上定义一个触发器，如果购物者改变了定单的数量，玩具的成本也自动地改变。（提示：Toy cost = Quantity * Toy Rate） 2. 实验四：事务与游标
（1）名为prcGenOrder 的存储过程产生存在于数据库中的定单号： CREATE PROCEDURE prcGenOrder @OrderNo char(6) OUTPUT as
SELECT @OrderNo=Max(cOrderNo) FROM Orders SELECT @OrderNo= CASE
WHEN @OrderNo>=0 and @OrderNo=9 and @OrderNo=99 and @OrderNo
‘000’+Convert(char,@OrderNo+1) WHEN @OrderNo>=999 and @OrderNo=9999 and @OrderNo
WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1) END RETURN
当购物者确认定单时，应该出现下面的步骤： a. 用上面的过程产生定单号。
b. 定单号，当前日期，购物车ID ，和购物者ID 应该加到Orders 表中。 c. 定单号，玩具ID ，和数量应加到OrderDetail 表中。
d. 在OrderDetail 表中更新玩具成本。（提示：Toy cost = Quantity * Toy Rate ）. 将上述步骤定义为一个事务。编写一个过程以购物车ID 和购物者ID 为参数，实现这个事务。
（2）编写一个程序显示每天的定单状态。如果当天的定单值总合大于170，则显示“High sales”, 否则显示”Low sales”. 报告中要求列出日期、定单状态和定单总价值。
三、实验结果：
1、 实验三：
（1）select *from toys
create proc avgpriceup_proc as
declare @mToyRate money
while{round((select avg(mToyRate) from toys)-24.5,1)0} begin
update Toys set mToyRate=mToyRate+0.5 select @mToyRate=mToyRate from Toys if(@mToyRate-53>0) break end go
exec avgpriceup_proc
select *from toys
（2）alter proc prCharges
@corderno char(6)
@mshippingcharges money output
@mgiftwrapcharges money output
as
select
@mshippingcharges=mshippingcharges
@mgiftwrapcharges=mgiftwrapcharges
from orders
where CorderNo=@corderno
go
declare @mshippingcharges money
mshippingcharges money
exec prcharges’000001’,@ mshippingcharges output
@mgiftwrapcharges output
print’装运费用’:’+couvert(varchar(10),@mshippingcharges)
print’包装费用’:’+couvert(varchar(10),@ mgiftwrapcharges)
（3）create pro pycHandlingcharge
@orderno char(6)
@runcost money output
As
Declare @ mshippingcharges money
@mshippingcharges money
exec prccharges’000001’,@mshippingcharges output
@mgiftwrapcharges output
Set @runcost=@mshippingcharges+@mgiftwrapcharges
Go
Declare @runcost money
Exec prcHandlingcharges’000001’,@runcost output
Print’经营费用’:+convert(varchar(10),@runcost)
（4）create trigger OrderDetail_tyg
on OrderDetail
for update
as
declare @siQty int
declare @mToyCost money
declare @ToyRate money
select @ToyRate=mToyRate from Toys
select @siQty=siQty from inserted
set @mToyCost=@siQty*@ToyRate
update OrerDetail
set mToyCost=@mToyCost
go
2、 实验四：
（1）begin transaction
declare @count int /*用来记录用户错误号*/
set @count=0 /*初始化*/
declare @OrderNo char(6)
exec prcGenorder @orderno output
set @count=@count+@@ERROR 记录上条语句是否发生错误 declare @ccartid char(6)
declare @cshopperid char(6)
declare @toyid char(6)
declare @siqty int
declare @mToyRate money
set @ccartid=’000009’ 自定义购物车ID
set @cshopper=’000007’ 自定义购物者ID
set @toyid=’000008’ 自定义玩具ID
set @siqty=2 自定义玩具数量
select @mToyRate=mToyRate from Toys where cToyId=@toyid
insert into orders values
(@OrderNo,GETDATE(),@ccartid,@cshopperid,null,null,null,null,null,null) Set @count=@count+@@ERROR 记录上条语句是否发生错误 Insert into OrderDetail values
(@OrderNo,@toyid,@siqty,null,null,null,@mToyRate*@siQty)
Set @count=@count+@@ERROR 记录上条语句是否发生错误
If(@count>0)
Begin
Print’发生错误’
rollback
end
else commit
select *from Orders
select *from OrderDetail
（2）create pro typeorder
@datetime datetime
As
Declare @totalcost money
Set @totalcost=(select sum(mTotalcost) from Orders
Where dOrderDate=@datetime)
If(@totalcost>170)
Print’High sales’
Else
Print’Low sales’
Exec typeorder ‘2010-12-20’ 输入查询日期
四、参考书籍：
《数据库系统概论》（第四版） 王珊，萨师煊 高等教育出版社