数据库实验报告

数据库实验报告
数据库实验报告
班级：计算机科学与技术1005
学号：0909102518
姓名：赵书剑
指导教师：盛津芳
⽬录⼀．实验⽬的 (1)
⼆．实验要求 (1)
三．实验内容 (1)
四．运⾏结果 (6)
五．实验总结 (20)
⼀．实验⽬的
1．了解DBMS的⼯作环境和系统框架；
2．通过SQL语⾔对数据库进⾏操作；
3．熟悉SQL语句。

⼆．实验要求
1．所有的SQL语句和源代码；
2．要求有适当的注释；
3．性约束实施、实验三、实验四和实验五要求给出相应的测试⽤例。

三．实验内容
实验⼀：创建表、更新表和实施数据完整性
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.以下列格式显⽰所有购物者的名字和他们的简称：
（Initials,vFirstName,vLastName）,例如Angela
Smith的Initials为A.S。

10.显⽰所有玩具的平均价格，并舍⼊到整数。

11.显⽰所有购买者和收货⼈的名、姓、地址和所在城市，要求
显⽰结果中的重复记录。

12.显⽰没有包装的所有玩具的名称。

（要求⽤⼦查询实现）
13.显⽰已收货定单的定单号码以及下定单的时间。

（要求⽤⼦
查询实现）
14.显⽰⼀份基于Orderdetail的报表，包括
cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每⼀笔定单的玩具总价值。

（提⽰：使⽤运算符COMPUTE BY）。

15.把价格在$20以上的所有玩具的信息拷贝到称为
PremiumToys的新表中。

实验三：视图与触发器
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>=0and@OrderNo<9Then
‘00000’+Convert(char,@OrderNo+1) WHEN@OrderNo>=9and@OrderNo<99Then
‘0000’+Convert(char,@OrderNo+1) WHEN@OrderNo>=99and@OrderNo<999Then
‘000’+Convert(char,@OrderNo+1)
WHEN@OrderNo>=999and@OrderNo<9999 Then
‘00’+Convert(char,@OrderNo+1)
WHEN@OrderNo>=9999and@OrderNo<99999 Then
‘0’+Convert(char,@OrderNo+1)
WHEN@OrderNo>=99999Then Convert(char,@OrderNo+1)
END
RETURN
当购物者确认定单时，应该出现下⾯的步骤：
（1）⽤上⾯的过程产⽣定单号。

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

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

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

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

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

编写⼀个程序显⽰每天的定单状态。

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

四.运⾏结果
实验⼀：
表的关系图如下：
实验⼆:
1．显⽰属于California和Illinoi州的顾客的名、姓和emailID select vFirstName,vLastName,vEmailId from Shopper
where cState='California'or cState='Illinois';
2．显⽰定单号码、商店ID，定单的总价值，并以定单的总价值的升序排列。

select cOrderNo,cShopperId,mTotalCost from Orders order by mTotalCost;
3．显⽰在orderDetail表中vMessage为空值的⾏。

select*from OrderDetail where vMessage is NULL;
4．显⽰玩具名字中有“Racer”字样的所有玩具的材料。

select*from Toys where vToyName like'%Racer%';
5．根据2000年的玩具销售总数，显⽰“Pick of the Month”玩具的前五名玩具的ID。

select cToyId from PickOfMonth group by cToyId order by sum(iTotalSold)desc limit5;
6．根据OrderDetail表，显⽰玩具总价值⼤于￥50的定单的号码和玩具总价值。

select cOrderNo,sum(mToyCost)from OrderDetail group by cOrderNo having sum(mToyCost)>50;
7．显⽰⼀份包含所有装运信息的报表，包括：Order Number, Shipment Date,Actual Delivery Date,Days in Transit.(提⽰：Days in Transit=Actual Delivery Date–Shipment Date) select cOrderNo as'Order Number',dShipmentDate as
'Shipment Date',dActualDeliveryDate as
'ActualDeliveryDate',datediff(dActualDeliveryDate,dShipme ntDate)as'Days in Transit'from Shipment;
8．显⽰所有玩具的名称、商标和种类（Toy Name,Brand, Category）。

select vToyName,cBrandName,cCategory from(Toys natural
join Toybrand)join Category using(cCategoryid);
9．以下列格式显⽰所有购物者的名字和他们的简称：（Initials, vFirstName,vLastName）,例如Angela Smith的Initials 为A.S
select concat(left(vFirstName,1),'.',left(vLastName,1)) as initials,vFirstName,vLastName from Shopper;
10．显⽰所有玩具的平均价格，并舍⼊到整数。

select round(sum(mToyRate)/count(*))as average_rate from Toys;
11．显⽰所有购买者和收货⼈的名、姓、地址和所在城市，要求显⽰结果中的重复记录。

(select vFirstName,vLastName,vAddress,cCity from Recipient)union all(select vFirstName,vLastName,vAddress,cCity from Shopper);
12．显⽰没有包装的所有玩具的名称。

（要求⽤⼦查询实现）select vToyName from Toys where cToyId in(select cToyId from OrderDetail where cGiftWrap='N');
13.显⽰已收货定单的定单号码以及下定单的时间。

（要求
⽤⼦查询实现）
select cOrderNo,dOrderDate from Orders where cOrderNo in (select cOrderNo from Shipment where cDeliveryStatus= 'd');
14.显⽰⼀份基于Orderdetail的报表，包括cOrderNo,cToyId和mToyCost，记录以cOrderNo升序排列，并计算每⼀笔定单的玩具总价值。

（提⽰：使⽤运算符COMPUTE BY）。

SET@gt=0;#grand total counter
SET@st=0;#subtotal counter
SET@pg='';#previous group
SELECT cOrderNo,cToyId,mToyCost,(@gt:=@gt+mToyCost) AS GrandTotal,
(@st:=IF(@pg!=cOrderNo,1,@st+1))AS SubTotal,@pg:=cOrderNo
FROM OrderDetail
ORDER BY cOrderNo;
15.把价格在$20以上的所有玩具的信息拷贝到称为PremiumToys的新表中。

create table PremiumToys select*from Toys where mToyRate>20;
16.给id为‘000001’玩具的价格增加$1。

update Toys set mToyRate=mToyRate+1where cToyId= '000001';
17.删除“Largo”牌的所有玩具。

delete from Toys where cBrandId in(select cBrandId from ToyBrand where cBrandName='Largo');
有外键约束存在，⽆法删除
实验三：
1.定义⼀个视图，包括购买者的姓名、所在州和他们所订购玩具的
名称、价格和数量。

CREATE VIEW demo(firstname,lastname,location,toyname,toyprice,toynum) AS
SELECT vFirstName,
vLastName,
cState,
vToyName,
mToyRate,
siQty
FROM(((Shopper
JOIN Orders USING(cShopperId))
JOIN OrderDetail USING(cOrderNo))
JOIN Toys USING(cToyId));
2.基于（1）中定义的视图，查询显⽰所有California州的购买者的姓名和他们所订购玩具的名称及数量。

SELECT firstname,
lastname,
toyname,
toynum
FROM demo
WHERE LOCATION='California';。