中南大学数据库实验报告

中南大学
数据库实验
实验题目
班级
姓名
学号
一、实验内容
实验一：创建表、更新表和实施数据完整性
1．运行给定的SQL Script，建立数据库GlobalToyz。

2．创建所有表的关系图。

3．列出所有表中出现的约束（包括Primary key, Foreign key, check constraint, default, unique）
4．对Recipient表和Country表中的cCountryId属性定义一个用户自定义数据类型，并将该属性的类型定义为这个自定义数据类型。

5．把价格在$20以上的所有玩具的材料拷贝到称为PremiumToys 的新表中。

6．对表Toys实施下面数据完整性规则：（1）玩具的现有数量应在0到200之间；（2）玩具适宜的最低年龄缺省为1。

7．不修改已创建的Toys表，利用规则实现以下数据完整性：（1）玩具的价格应大于0；（2）玩具的重量应缺省为1。

8．给id为‘000001’玩具的价格增加$1。

实验二：查询数据库
1.显示属于California和Illinoi州的顾客的名、姓和emailID。

2.显示定单号码、商店ID，定单的总价值，并以定单的总价值的升
序排列。

3.显示在orderDetail表中vMessage为空值的行。

4.显示玩具名字中有“Racer”字样的所有玩具的材料。

5.根据2000年的玩具销售总数，显示“Pick of the Month”玩具
的前五名玩具的ID。

6.根据OrderDetail表，显示玩具总价值大于￥50的定单的号码和
玩具总价值。

7.显示一份包含所有装运信息的报表，包括：Order Number,
Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date –Shipment Date)
8.显示所有玩具的名称、商标和种类（Toy Name, Brand, Category）。

9.显示玩具的名称和所有玩具的购物车ID。

如果玩具不在购物车中，
则显示NULL值。

10.以下列格式显示所有购物者的名字和他们的简称：（Initials,
vFirstName, vLastName）,例如Angela Smith的Initials为A.S。

11.显示所有玩具的平均价格，并舍入到整数。

12.显示所有购买者和收货人的名、姓、地址和所在城市。

13.显示没有包装的所有玩具的名称。

（要求用子查询实现）
14.显示已发货定单的定单号码以及下定单的时间。

（要求用子查询实
现）
实验三：视图与触发器
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
FROM vwOrderWrapper
WHERE cOrderNo = ‘000001’
修改更新命令，以更新基表中的值。

4.在OrderDetail上定义一个触发器，如果购物者改变了定单的数
量，玩具的成本也自动地改变。

（提示：Toy cost = Quantity * Toy Rate）
实验四：存储过程
1.编写一段程序，将每种玩具的价格提高￥0.5，直到玩具的平均价
格接近$24.5为止。

此外，任何玩具的最大价格不应超过$53。

2.创建一个称为prcCharges的存储过程，它返回某个定单号的装运
费用和包装费用。

3.创建一个称为prcHandlingCharges的过程，它接收定单号并显示
经营费用。

PrchandlingCharges过程应使用prcCharges过程来得到装运费和礼品包装费。

提示：经营费用=装运费+礼品包装费
实验五：事务与游标
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 Then
‘00000’+Convert(char,@OrderNo+1)
WHEN @OrderNo>=9 and @OrderNo<99 Then
‘0000’+Convert(char,@OrderNo+1)
WHEN @OrderNo>=99 and @OrderNo<999 Then
‘000’+Convert(char,@OrderNo+1)
WHEN @OrderNo>=999 and @OrderNo<9999 Then
‘00’+Convert(char,@OrderNo+1)
WHEN @OrderNo>=9999 and @OrderNo<99999 Then
‘0’+Convert(char,@OrderNo+1)
WHEN @OrderNo>=99999 Then Convert(char,@OrderNo+1) END
RETURN
当购物者确认定单时，应该出现下面的步骤：
（1）用上面的过程产生定单号。

（2）定单号，当前日期，购物车ID，和购物者ID应该加到Orders 表中。

（3）定单号，玩具ID，和数量应加到OrderDetail表中。

（4）在OrderDetail表中更新玩具成本。

（提示：Toy cost =
Quantity * Toy Rate）.
将上述步骤定义为一个事务。

编写一个过程以购物车ID和购物者ID为参数，实现这个事务。

2.编写一个程序显示每天的定单状态。

如果当天的定单值总合大于
170，则显示“High sales”,否则显示”Low sales”.报告中要求列出日期、定单状态和定单总价值。

二、实验目的
熟悉SQL语言，加深对数据库知识的认识。

学会用SQL语言解决实际数据库问题。

三、实验源代码
实验一、
-3．
/*sp_helpconstraint @objname=Category
go
sp_helpconstraint @objname=Country
go
sp_helpconstraint @objname=OrderDetail
go
sp_helpconstraint @objname=PickOfMonth
go
sp_helpconstraint @objname=Category
go
sp_helpconstraint @objname=Recipient
go
sp_helpconstraint @objname=Shipment
go
sp_helpconstraint @objname=ShippingMode
go
sp_helpconstraint @objname=ShippingRate
go
sp_helpconstraint @objname=Shopper
go
sp_helpconstraint @objname=ShoppingCart
go
sp_helpconstraint @objname=ToyBrand
go
sp_helpconstraint @objname=Toys
go
sp_helpconstraint @objname=Wrapper； */
--4．
create type xianke from char(3)
alter table Recipient
alter column cCountryId kk
alter table Country
alter column cCountryId kk
5.
USE GlobalToyz
GO
CREATE TABLE PremiumToys
(
cToyId char(6) primary key,
vToyName varchar(20) not null,
vToyDescription varchar(250),
cCategoryId char(3) ,
mToyRate money not null,
cBrandId char(3),
imPhoto image,
siToyQoh smallint not null,
siLowerAge smallint not null,
siUpperAge smallint not null,
siToyWeight smallint,
vToyImgPath varchar(50) null
)
INSERT INTO PremiumToys
SELECT * FROM Toys
WHERE mToyRate>20
6.7.
ALTER Table Toys
ADD siToyQoh smallint CHECK（siToyQoh>=0 AND siToyQoh<=200）, ALTER TABLE Toys
ADD siLowerAge smallint CHECK(siLowerAge=’1’),
ALTER TABLE Toys
ADD mToyRate CHECK (mToyRate>0)
ALTER TABLE Toys
ADD siToyWeight CHECK(siToyWeigh=’1’)
(
cToyId char(6) primary key,
vToyName varchar(20) not null,
vToyDescription varchar(250),
cCategoryId char(3),
mToyRate money not null(mToyRate>0), cBrandId char(3),
imPhoto image,
siLowerAge smallint not null,
siUpperAge smallint not null,
siToyWeight smallint,
vToyImgPath varchar(50) null
)
8.update Toys
Set mToyRate=mToyRate+1
Where cToyId=’000001’
实验二
1. SELECT vFirstName
,vLastName
,vEmailId
FROM GlobalToyz.dbo.Shopper
WHERE cState = 'California'
OR cState = 'Illinois'
GO
2. SELECT cOrderNo
,cShopperId
,mTotalCost
FROM GlobalToyz.dbo.Orders
ORDER BY mTotalCost ASC
GO
3. SELECT *
FROM GlobalToyz.dbo.OrderDetail
WHERE vMessage IS NULL
GO
4. SELECT top 5
cToyId
,siMonth
,iYear
,iTotalSold
FROM GlobalToyz.dbo.PickOfMonth
WHERE iYear = 2000
ORDER BY iTotalSold DESC
GO
5. SELECT top 5
cToyId
,siMonth
,iYear
,iTotalSold
FROM GlobalToyz.dbo.PickOfMonth
WHERE iYear = 2000
ORDER BY iTotalSold DESC
GO
6. SELECT cOrderNo
,mToyCost
FROM GlobalToyz.dbo.OrderDetail
WHERE mToyCost > 50
GO
7. SELECT vToyName
,ToyBrand.cBrandId
,ToyBrand.cBrandName
,ategoryId
,ategory
,Category.vDescription
FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ToyBrand, GlobalToyz.dbo.Category
WHERE toys.cBrandId = ToyBrand.cBrandId
AND ategoryId = ategoryId
GO
8. SELECT vToyName
,ToyBrand.cBrandId
,ToyBrand.cBrandName
,ategoryId
,ategory
,Category.vDescription
FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ToyBrand, GlobalToyz.dbo.Category
WHERE toys.cBrandId = ToyBrand.cBrandId
AND ategoryId = ategoryId
GO
9. SELECT vToyName
,artId
FROM GlobalToyz.dbo.Toys, GlobalToyz.dbo.ShoppingCart
WHERE Toys.cToyId = ShoppingCart.cToyId
GO
10. SELECT FLOOR(AVG(mToyRate))
FROM GlobalToyz.dbo.Toys
GO
11. SELECT FLOOR(AVG(mToyRate))
FROM GlobalToyz.dbo.Toys
GO
12. SELECT Shopper.vFirstName
,Shopper.vLastName
,Shopper.vAddress
,ity
,Recipient.vFirstName
,Recipient.vLastName
,Recipient.vAddress
,ity
FROM GlobalToyz.dbo.Shopper, GlobalToyz.dbo.Recipient
GO
13. SELECT vToyName
FROM GlobalToyz.dbo.Toys
WHERE cToyId IN
(
SELECT cToyId
FROM GlobalToyz.dbo.OrderDetail
WHERE cWrapperId IS NULL
)
GO
14. SELECT cOrderNo
,dOrderDate
FROM GlobalToyz.dbo.Orders
WHERE cOrderNo IN
(
SELECT cOrderNo
FROM GlobalToyz.dbo.Shipment
WHERE cDeliveryStatus = 'd'
)
GO
实验三
1. CREATE VIEW IS_CHAXUN
AS
SELECT
Shopper.vFirstName,Shopper.vLastName,ity,Toys.vTo
yName,Toys.mToyRate,OrderDetail.siQty
FROM Shopper,Orders,OrderDetail,Toys
WHERE Orders.cOrderNo=OrderDetail.cOrderNo AND
Shopper.cShopperId=Orders.cShopperId AND
OrderDetail.cToyId=Toys.cToyId;
2. SELECT vFirstName,vLastName,vToyName,siQty
FROM IS_CHAXUN
WHERE cCity='California';
3. UPDATE vwOrderWrapper
SET siQty = 2, mWrapperRate = mWrapperRate + 1
FROM vwOrderWrapper
WHERE cOrderNo = ‘000001’
4. Create Trigger truOD
ON OrderDetail
FOR UPDATE
AS
IF UPDATE(siQty)
BEGIN
DECLARE @siQty int
SELECT @siQty=deleted.siQty
FROM deleted
UPDATE OrderDetail
SET mToyCost=mToyCost/@siQty*siQty
WHERE cOrderNo IN
(SELECT cOrderNo
FROM deleted)
END
实验四
1. SELECT cOrderNo
,dOrderDate
FROM GlobalToyz.dbo.Orders
WHERE cOrderNo IN
(
SELECT cOrderNo
FROM GlobalToyz.dbo.Shipment
WHERE cDeliveryStatus = 'd'
)
GO
2. CREATE PROCEDURE prcCharges
@cOrderNo int,@mShippingCharges money OUTPUT, @mGiftWrapCharges money OUTPUT
AS
BEGIN
SELECT @cOrderNo=cOrderNo, @mShippingCharges =mShippingCharges, @mGiftWrapCharges =mGiftWrapCharges
FROM Orders
WHERE cOrderNo=@cOrderNo
END
3. CREATE PROCEDURE prcHandlingCharges @cOrderNo int
AS
BEGIN
DECLARE @mScharges money
DECLARE @mGWcharges money
EXEC prcCharges @cOrderNo,
@mScharges output, @mGWcharges output
RETURN @mScharges+@mGWcharges
END
实验五
1. CREATE PROCEDURE tianjia @cCartId char(6),@cShopperId
char(6),@cToyId char(6),@siQty smallint AS
BEGIN
BEGIN TRANSACTION
DECLARE @date datetime;
DECLARE @mToyRate money
DECLARE @cOrderNo char(6)
SELECT @date=GETDATE();
EXEC @cOrderNo= prcGenOrder
INSERT
INTO Orders
VALUES(@cOrderNo,@date,@cCartId,@cShopperId,null,null,null,null,n ull,null);
INSERT
INTO OrderDetail
VALUES(@cOrderNo,@cToyId,@siQty,null,null,null,null)
SELECT @mToyRate=mToyRate
FROM Toys
WHERE cToyId=@cToyId
UPDATE OrderDetail
SET mToyCost=siQty*@mToyRate
COMMIT TRANSACTION
END
2. CREATE PROCEDURE repor
AS
BEGIN
DECLARE @date datetime;
DECLARE @totalcost money;
SELECT @date=GETDATE();
SELECT @totalcost=SUM(mTotalCost) FROM Orders
WHERE dOrderDate =@date
IF(@totalcost>170)
PRINT'High Sales'
ELSE
PRINT'Low Sales'
END
四、调用示例
实验三
实验四
实验四
实验五
五、实验心得
通过这几次实验，我加深对数据库SQL语言的理解和认识，并学会了用它解决一些简单的实际问题。

我加深了SQL语言的理解。

丰富了我的关于数据库领域的视野。

在实验中，由于自己的基础知识不牢固，遇到了很多的问题，我也认识到了课本中理论的重要性。

在实验之前一定要吃透课本。

只有把课本的基础知识掌握牢固了才能进一步地做好实际操作。

并在实验中发现问题，自己看书，自己解决问题。

实验的收获也是可观的。

不但培养了我的动手操作能力，知识运用能力，独立思考能力，而且让我学会了如何在做计算机方面的实验时，应该注意的一些东西。