数据库原理课程实验报告

Rainbow_Pie

于 2024-07-16 09:03:43 发布

阅读量1.9k

点赞数 28

分类专栏：大二下文章标签：数据库 mysql

本文链接：https://blog.csdn.net/2301_76154383/article/details/140422960

版权

大二下专栏收录该内容

9 篇文章 0 订阅

订阅专栏

写在开头：

严格按平时作业打平时分只能说有好有坏吧。好处在于如果你好好地完成了平时的作业，那么平时分至少不会被恶意打低，85分以上还是绰绰有余的；坏处就是，当期末考试简单的时候，很容易出现平时分比考试分低的情况，心里会有点不平衡。

声明：本人mysql入门水平，佬们没必要浪费时间看这坨代码。

如果有任何违规的地方，请第一时间联系我，我会立即删除本文。

实验一

任务一：（数据库CAP）

1.创建数据库CAP，包含4张表Customers、Products、Agents和Orders。

代码：

CREATE database if not exists CAP;

CREATE TABLE CAP.Agents (
aid varchar(10) NOT NULL,
aname varchar(10) NOT NULL,
city varchar(10) NOT NULL,
percent int NOT NULL,
PRIMARY KEY (aid)
);

CREATE TABLE CAP.Customers (
cid varchar(10) NOT NULL,
cname varchar(10) NOT NULL,
city varchar(10) NOT NULL,
discnt double NOT NULL,
PRIMARY KEY (cid)
);

CREATE TABLE CAP.Orders (
ordno int NOT NULL,
month varchar(10) NOT NULL,
cid varchar(10) NOT NULL,
aid varchar(10) NOT NULL,
pid varchar(10) NOT NULL,
qty int NOT NULL,
dollars double NOT NULL,
PRIMARY KEY (ordno)
);

CREATE TABLE CAP.Products (
pid varchar(10) NOT NULL,
pname varchar(10) NOT NULL,
city varchar(10) NOT NULL,
quantity int NOT NULL,
price double DEFAULT NULL,
PRIMARY KEY (pid)
);

运行结果:

2. 利用SQL语句向4张表中增加一些示例数据。

代码：

INSERT INTO CAP.Agents values('A01','Smith','New York',6);
INSERT INTO CAP.Agents values('A02','Jones','Newark',6);
INSERT INTO CAP.Agents values('A03','Brown','Tokyo',7);
INSERT INTO CAP.Agents values('A04','Gray','New York',6);
INSERT INTO CAP.Agents values('A05','Otasi','Duluth',5);
INSERT INTO CAP.Agents values('A06','Smith','Daalas',5);

INSERT INTO CAP.Customers values('C001','TipTop','Duluth',10.00);
INSERT INTO CAP.Customers values('C002','Basics','Dallas',12.00);
INSERT INTO CAP.Customers values('C003','Allied','Dallas',8.00);
INSERT INTO CAP.Customers values('C004','ACME','Duluth',8.00);
INSERT INTO CAP.Customers values('C006','ACME','Kyoto',0.00);

INSERT INTO CAP.Orders values(1011,'Jan','C001','A01','P01',1000,450.00);
INSERT INTO CAP.Orders values(1012,'Jan','C001','A01','P01',1000,450.00);
INSERT INTO CAP.Orders values(1019,'Feb','C001','A02','P02',400,180.00);
INSERT INTO CAP.Orders values(1017,'Feb','C001','A06','P03',600,540.00);
INSERT INTO CAP.Orders values(1018,'Feb','C001','A03','P04',600,540.00);
INSERT INTO CAP.Orders values(1023,'Mar','C001','A04','P05',500,450.00);
INSERT INTO CAP.Orders values(1022,'Mar','C001','A05','P06',400,720.00);

INSERT INTO CAP.Products values('P01','comb','Dallas',111400,0.50);
INSERT INTO CAP.Products values('P02','brush','Newark',203000,0.50);
INSERT INTO CAP.Products values('P03','razor','Duluth',150600,1.00);
INSERT INTO CAP.Products values('P04','Pen','Duluth',125300,1.00);
INSERT INTO CAP.Products values('P05','pencil','Dallas',221400,1.00);
INSERT INTO CAP.Products values('P06','folder','Dallas',123100,2.00);
INSERT INTO CAP.Products values('P07','case','Newark',100500,null);

运行结果：

3.创建一张表Orders_Jan，表的结构与Orders相同，将Orders表中month为‘Jan’的订单记录复制到表Orders_Jan中。

代码：

CREATE table CAP.Orders_Jan as select * FROM CAP.Orders WHERE 1=0;
INSERT INTO CAP.Orders_Jan
select * FROM CAP.Orders o WHERE o.month = 'Jan';

运行结果：

4.将Orders表中month为‘Jan’的订单记录全部删掉。

代码：

DELETE 
FROM CAP.Orders o
WHERE o.month = 'Jan';

运行结果：

5.对曾经下过金额（dollars）大于500的订单的客户，将其discnt值增加2个百分点（+2）。

代码：

UPDATE CAP.Customers c
SET c.discnt = c.discnt + 2
WHERE c.cid IN (SELECT DISTINCT o.cid 
FROM CAP.Orders o
WHERE o.dollars>500);

运行结果：

任务二：（数据库GlobalToyz）

1.对GlobalToyz，创建数据库关系图。

代码：dbeaver自带ER图，好像不需要关系图的创建

运行结果：

2.查找属于California州并且使用Master Card支付的购物者的名、姓和邮件地址。

代码：

SELECT s.vFirstName,s.vLastName,s.vEmailId
from GlobalToyz.Shopper s 
WHERE s.cState = 'California' AND s.vCreditCardType = 'Master Card';

运行结果：

3.查找2021年，每个购物者的ID和定单总额，并以定单总额的升序排列。

代码：

SELECT s.cShopperId,SUM(o.mTotalCost) as total
from GlobalToyz.Shopper s 
left outer join GlobalToyz.Orders o
on s.cShopperId =o.cShopperId AND o.dOrderDate LIKE '2021%'
GROUP BY s.cShopperId
ORDER BY total ASC;

运行结果：

4.查找在orderDetail表中vMessage为空值的行。

代码：

SELECT *
FROM GlobalToyz.OrderDetail od 
WHERE od.vMessage IS NULL ;

运行结果：

5.查找密码（password）长度不足6个字符的购买者的ID和姓名。

代码：

SELECT s.cShopperId,s.vFirstName,s.vLastName
from GlobalToyz.Shopper s 
WHERE s.cPassword LIKE '_' or s.cPassword LIKE '__' or s.cPassword LIKE '___' or s.cPassword LIKE '____' or s.cPassword LIKE '_____';

运行结果：

6.查找电话号码以“123”开头的收货人（Recipient）的姓名和所在的国家。

代码：

SELECT DISTINCT r.vFirstName ,r.vLastName ,c.cCountry 
FROM GlobalToyz.Recipient r ,GlobalToyz.Country c 
WHERE r.cCountryId = c.cCountryId AND r.cPhone LIKE '123%';

运行结果：

7.根据pickofmonth表，查找2020年销售总量排在前三名的玩具的ID。

代码：

SELECT pom.cToyId 
FROM GlobalToyz.PickOfMonth pom
WHERE pom.iYear = 2020
group by pom.cToyId
ORDER BY SUM(pom.iTotalSold) DESC limit 3;

运行结果：

8.根据OrderDetail表，查找玩具总价值大于￥50的定单的号码和玩具总价值。

代码：

SELECT od.cOrderNo ,SUM(od.mToyCost) as Total
FROM GlobalToyz.OrderDetail od 
group by od.cOrderNo 
HAVING Total>50;

运行结果：

9.查找一份包含所有装运信息的报表，包括：Order Number, Shipment Date, Actual Delivery Date, Days in Transit. (提示：Days in Transit = Actual Delivery Date – Shipment Date)

代码：

SELECT s.cOrderNo ,s.dShipmentDate ,s.dActualDeliveryDate ,DATEDIFF(s.dActualDeliveryDate,s.dShipmentDate) as DaysInTransit
FROM GlobalToyz.OrderDetail od ,GlobalToyz.Shipment s 
WHERE od.cOrderNo = s.cOrderNo ;

运行结果：

10.查找所有玩具的名称、品牌和类别（Toy Name, Brand, Category）。

代码：

SELECT t.vToyName ,tb.cBrandName ,c.cCategory 
FROM GlobalToyz.Toys t ,GlobalToyz.ToyBrand tb ,GlobalToyz.Category c 
WHERE t.cBrandId = tb.cBrandId AND t.cCategoryId = c.cCategoryId ;

运行结果：

11.查找“Activity”这个类别的玩具的最高价格、最低价格和平均价格。

代码：

SELECT MAX(t.mToyRate) as maxprice,MIN(t.mToyRate) as minprice, AVG(t.mToyRate) as avgprice
FROM GlobalToyz.Toys t ,GlobalToyz.Category c
WHERE t.cCategoryId = c.cCategoryId AND c.cCategory = 'Activity';

运行结果：

12.查找玩具的名称和所有玩具的购物车ID。如果玩具不在购物车中，也需在结果中出现。

代码：

SELECT t.vToyName ,sc.cCartId 
FROM GlobalToyz.Toys t 
LEFT JOIN GlobalToyz.ShoppingCart sc
ON t.cToyId =sc.cToyId ;

运行结果：

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

代码：

SELECT CONCAT(SUBSTRING(s.vFirstName,1,1),'.',SUBSTRING(s.vLastName,1,1)) as Initials, s.vFirstName ,s.vLastName 
FROM GlobalToyz.Shopper s ;

运行结果：

14.查找“Standard Shipping”这种快递模式的单价最低的国家的名称。

代码：

SELECT c.cCountry 
FROM GlobalToyz.ShippingRate sr ,GlobalToyz.ShippingMode sm ,GlobalToyz.Country c 
WHERE sr.cModeId = sm.cModeId AND sr.cCountryId = c.cCountryId AND sm.cMode = 'Standard Shipping'
ORDER BY sr.mRatePerPound ASC LIMIT 1;

运行结果：

15.查找买过名称为“Kitchen Set”的玩具的购物者的ID、姓名、邮件地址及所在的城市。

代码：

SELECT DISTINCT s.cShopperId ,s.vFirstName ,s.vLastName ,s.vEmailId ,s.cCity 
FROM GlobalToyz.Shopper s ,GlobalToyz.Orders o ,GlobalToyz.OrderDetail od ,GlobalToyz.Toys t 
WHERE s.cShopperId = o.cShopperId AND o.cOrderNo = od.cOrderNo AND od.cToyId = t.cToyId AND t.vToyName = 'Kitchen Set';

运行结果：

16.查找所有购物者和收货人的名、姓、地址和所在城市，要求保留结果中的重复记录。

代码：

SELECT s.vFirstName ,s.vLastName ,s.vAddress ,s.cCity 
FROM GlobalToyz.Shopper s 
UNION ALL
select r.vFirstName,r.vLastName,r.vAddress,r.cCity
FROM GlobalToyz.Recipient r ;

运行结果：

17.查找没有包装的所有玩具的名称。（要求用子查询实现）

代码：

SELECT t.vToyName 
FROM GlobalToyz.Toys t 
WHERE t.cToyId NOT IN (SELECT od.cToyId 
FROM GlobalToyz.OrderDetail od 
WHERE od.cGiftWrap = 'Y');

运行结果：

18.查找已收货定单的定单号码以及下定单的时间。（要求用子查询实现）

代码：

SELECT o.cOrderNo ,o.dOrderDate 
FROM GlobalToyz.Orders o ,GlobalToyz.Shipment s1
WHERE o.cOrderNo = s1.cOrderNo AND s1.cOrderNo IN (SELECT s2.cOrderNo
FROM GlobalToyz.Shipment s2
WHERE s2.cDeliveryStatus = 'd');

运行结果：

19.查找从来没有下过订单的购物者。

代码：

SELECT *
FROM GlobalToyz.Shopper s 
WHERE s.cShopperId NOT IN (SELECT o.cShopperId
FROM GlobalToyz.Orders o
);

运行结果：

20.删除“Largo”牌的所有玩具。

代码：

set foreign_key_checks = 0;
DELETE 
FROM GlobalToyz.Toys t
WHERE t.cBrandId IN (SELECT tb.cBrandId
FROM GlobalToyz.ToyBrand tb
WHERE tb.cBrandName = 'Largo');
set foreign_key_checks = 1;

运行结果：

实验二

任务一（数据库CAP）

1.写一段程序，向表Orders中增加50000条记录，要求订单尽可能均匀地分布在12个月中。

代码：

drop procedure if exists CAP.input1;

create procedure CAP.input1()
begin
declare count1 int default 0;#计数器
declare ordno int default 1024;#编号计数器
declare imonths int;
declare months varchar(10);
declare icid int;
declare cid varchar(10);
declare iaid int;
declare aid varchar(10);
declare ipid int;
declare pid varchar(10);
declare price double;#价格随机产生
declare qty int;#数量随机产生
declare dollars double;#数量乘以价格
while count1 < 50000 DO
set imonths = FLOOR(12*RAND());#产生一个随机数，每个随机数对应一个月份，且随机数的产生满足均匀分布
CASE imonths
WHEN 0 THEN set months='Jan';
WHEN 1 THEN set months='Feb';
WHEN 2 THEN set months='Mar';
WHEN 3 THEN set months='Apr';
WHEN 4 THEN set months='May';
WHEN 5 THEN set months='Jun';
WHEN 6 THEN set months='Jul';
WHEN 7 THEN set months='Aug';
WHEN 8 THEN set months='Sep';
WHEN 9 THEN set months='Oct';
WHEN 10 THEN set months='Nov';
WHEN 11 THEN set months='Dec';
ELSE signal sqlstate '45000' set message_text='日期异常!';
END CASE;
set icid = FLOOR(5*RAND());#产生一个随机数，一个数对应一个客户
CASE icid
WHEN 0 THEN set cid='C001';
WHEN 1 THEN set cid='C002';
WHEN 2 THEN set cid='C003';
WHEN 3 THEN set cid='C004';
WHEN 4 THEN set cid='C006';
ELSE signal sqlstate '45000' set message_text='cid异常!';
END CASE;
set iaid = FLOOR(6*RAND());#产生一个随机数，一个数对应一个卖家
CASE iaid
WHEN 0 THEN set aid='A01';
WHEN 1 THEN set aid='A02';
WHEN 2 THEN set aid='A03';
WHEN 3 THEN set aid='A04';
WHEN 4 THEN set aid='A05';
WHEN 5 THEN set aid='A06';
ELSE signal sqlstate '45000' set message_text='aid异常!';
END CASE;
set ipid = FLOOR(7*RAND());#产生一个随机数，一个数对应一个产品
CASE ipid
WHEN 0 THEN set pid='P01';
WHEN 1 THEN set pid='P02';
WHEN 2 THEN set pid='P03';
WHEN 3 THEN set pid='P04';
WHEN 4 THEN set pid='P05';
WHEN 5 THEN set pid='P06';
WHEN 6 THEN set pid='P07';
ELSE signal sqlstate '45000' set message_text='pid异常!';
END CASE;
set qty = CEILING (10000*RAND());
#set price = 1.5*RAND()+0.5; 
SELECT p.price into price
FROM CAP.Products p 
WHERE p.pid = pid;
set dollars = qty*price;
INSERT INTO CAP.Orders values(ordno,months,cid,aid,pid,qty,dollars);
set ordno = ordno+1;
set count1 = count1+1;
END WHILE;
end
CALL CAP.input1();

2.假设在表Orders上经常要执行的一个查询是“根据给定的月份，查询该月订单的总金额”。写出该查询的SQL语句，尝试通过创建索引提高查询的速度，并对比创建索引前后执行查询所消耗的时间。

sql语句：

DROP PROCEDURE IF EXISTS CAP.showsum;

CREATE PROCEDURE CAP.showsum(IN months varchar(10))
BEGIN
SELECT SUM(o.dollars) as sum
from CAP.Orders o
WHERE o.month = months;
END
CALL CAP.showsum('Mar');

耗费时间：0.027s。

索引：

ALTER table CAP.Orders DROP INDEX months;

CREATE INDEX months on CAP.Orders(month); 
CALL CAP.showsum('Mar');

耗费时间：0.012s 快了将近一倍。

任务二：视图（数据库GlobalToyz）

1.假设GlobalToyz数据库的用户经常需要查询某个购物者(shopper)的所有已收货订单的编号和待收货订单的编号。请通过定义视图解决这个问题。

代码：

CREATE view GlobalToyz.received as
select s.cShopperId ,sm.cOrderNo 
from GlobalToyz.Shopper s,GlobalToyz.Orders o,GlobalToyz.Shipment sm
WHERE s.cShopperId = o.cShopperId and o.cOrderNo = sm.cOrderNo and sm.cDeliveryStatus ='d';
#已收货的订单
CREATE view GlobalToyz.sending as
select s.cShopperId ,sm.cOrderNo 
from GlobalToyz.Shopper s,GlobalToyz.Orders o,GlobalToyz.Shipment sm
WHERE s.cShopperId = o.cShopperId and o.cOrderNo = sm.cOrderNo and sm.cDeliveryStatus ='s';
#待收货的订单

2.基于（1）中定义的视图，根据给定的购物者Id查询该购物者所有待收货玩具的Id,名称和描述。

代码：

drop procedure if exists GlobalToyz.showsendingtoys;

CREATE PROCEDURE GlobalToyz.showsendingtoys(IN shopperid varchar(10))
BEGIN
SELECT t.cToyId,t.vToyName,t.vToyDescription
from GlobalToyz.sending s,GlobalToyz.orderdetail o,GlobalToyz.Toys t
WHERE s.cShopperId = shopperid AND s.cOrderNo = o.cOrderNo AND o.cToyId = t.cToyId;
END
call showsendingtoys('000007');#测试000007号顾客

假设当系统中新增一个购物者（Shopper）的时候，需要为该购物者创建一个账号，并通过授权机制限制该购物者不能访问其他购物者的私有信息，例如其他购物者的订单、购物者的姓名、地址、邮件等个人信息。请给出解决的方案。

解决方案：创建一个私有信息视图，对于每个用户，给予select view权限，并且让筛选他们自己的私人信息。

root:

CREATE VIEW GlobalToyz.Shopperinfo AS
SELECT o.*,s.vFirstName,s.vLastName,s.vEmailId,s.vAddress
FROM GlobalToyz.Shopper s,GlobalToyz.Orders o 
WHERE s.cShopperId = o.cShopperId;
GRANT SELECT ON GlobalToyz.shopperinfo TO '000008'@'localhost';

user：

SELECT * 
FROM GlobalToyz.shopperinfo s 
where LOCATE(s.cShopperId,CURRENT_USER())!=0;

任务三：触发器（数据库GlobalToyz）

1.PickofMonth这张表是一张统计表，按年月统计某个玩具的销售总量。当用户下订单的时候，需要自动维护PickofMonth这张表。请利用触发器实现这个功能。

代码：

DROP trigger if exists GlobalToyz.changepom

CREATE trigger GlobalToyz.changepom
after INSERT on GlobalToyz.Orders
FOR EACH ROW 
BEGIN 
declare toyid char(6);
declare months int;
declare years int;
declare qty int;
set months = month(new.dOrderDate);
set years = year(new.dOrderDate);
SELECT o.siQty into qty
FROM GlobalToyz.orderdetail o 
WHERE o.cOrderNo = new.cOrderNo;
if(qty = null)
then signal sqlstate '45000' set message_text='无对应详细单号';
end if;
SELECT o.cToyId into toyid
FROM GlobalToyz.orderdetail o 
WHERE o.cOrderNo = new.cOrderNo;
if(toyid = null)
then signal sqlstate '45000' set message_text='无对应玩具编号';
end if;
if (exists(SELECT pom1.iTotalSold from GlobalToyz.PickOfMonth pom1 WHERE pom.cToyId = toyid AND pom.siMonth = months AND pom.iYear = years)) THEN 
UPDATE GlobalToyz.PickOfMonth pom
set pom.iTotalSold = pom.iTotalSold +qty
where pom.cToyId = toyid AND pom.siMonth = months AND pom.iYear = years;
else 
INSERT INTO GlobalToyz.PickOfMonth values (toyid,months,years,qty);
end if;
END

2.Orders表是GlobalToyz数据库里的一张核心的表，对这张表上做的任何更新动作（增、删、改）都需要记录下来，这是数据库审计（Audit）的基本思想。要求设计一张表存储对Orders表的更新操作，包括操作者、操作时间、操作类型、更新前的数据、更新后的数据。请通过设计触发器实现对Orders表的审计。

代码：

CREATE table GlobalToyz.Audit_Orders
(
operator varchar(20) not null,
operationtime datetime not null,
operationkind varchar(10) not null,
cOrderNoB char(6),
dOrderDateB datetime,
cCartIdB char(6),
cShopperIdB char(6),
cModeIdB char(2),
mShippingChargesB decimal(8,4),
mGiftWrapChargesB decimal(8,4),
cOrderProcessedB char(1),
mTotalCostB decimal(8,4),
dExpDelDateB datetime,
cOrderNoA char(6),
dOrderDateA datetime,
cCartIdA char(6),
cShopperIdA char(6),
cModeIdA char(2),
mShippingChargesA decimal(8,4),
mGiftWrapChargesA decimal(8,4),
cOrderProcessedA char(1),
mTotalCostA decimal(8,4),
dExpDelDateA datetime
)

DROP trigger if exists GlobalToyz.inserts

CREATE trigger GlobalToyz.inserts
after INSERT on GlobalToyz.Orders
FOR EACH ROW 
BEGIN 
INSERT INTO GlobalToyz.Audit_Orders values(CURRENT_USER(),NOW(),'INSERT',null,null,null,null,null,null,null,null,null,null,new.cOrderNo,new.dOrderDate,new.cCartId,new.cShopperId,new.cModeId,new.mShippingCharges,new.mGiftWrapCharges,new.cOrderProcessed,new.mTotalCost,new.dExpDelDate);
END

DROP trigger if exists GlobalToyz.deletes

CREATE trigger GlobalToyz.deletes
after DELETE on GlobalToyz.Orders
FOR EACH ROW 
BEGIN 
INSERT INTO GlobalToyz.Audit_Orders values(CURRENT_USER(),NOW(),'INSERT',old.cOrderNo,old.dOrderDate,old.cCartId,old.cShopperId,old.cModeId,old.mShippingCharges,old.mGiftWrapCharges,old.cOrderProcessed,old.mTotalCost,old.dExpDelDate,null,null,null,null,null,null,null,null,null,null);
END

DROP trigger if exists GlobalToyz.updates

CREATE trigger GlobalToyz.updates
after UPDATE on GlobalToyz.Orders
FOR EACH ROW 
BEGIN 
INSERT INTO GlobalToyz.Audit_Orders values(CURRENT_USER(),NOW(),'INSERT',old.cOrderNo,old.dOrderDate,old.cCartId,old.cShopperId,old.cModeId,old.mShippingCharges,old.mGiftWrapCharges,old.cOrderProcessed,old.mTotalCost,old.dExpDelDatenew.cOrderNo,new.dOrderDate,new.cCartId,new.cShopperId,new.cModeId,new.mShippingCharges,new.mGiftWrapCharges,new.cOrderProcessed,new.mTotalCost,new.dExpDelDate);
END

任务四：存储过程与事务（数据库GlobalToyz）

1.当用户确认了一笔订单的时候，需要对数据库进行一系列的操作，例如向表Orders、表OrderDetail中添加记录、对表Toys中玩具的库存数量的修改等，请首先画出相应的处理流程，然后将处理流程定义为一个事务，通过一个存储过程来实现，存储过程以购物车ID（cCartId）和购物者ID(cShopperId)为参数。（这道题请充分考虑确认订单的各个环节）

代码：

DROP procedure if exists GlobalToyz.purchase;

CREATE procedure GlobalToyz.purchase(IN CartId char(6),IN ShopperId char(6),IN ModeId char(2),IN GiftWrap char(1),IN WrapperId char(3),IN Message varchar(256))
BEGIN
declare done int default 0;
#玩具信息
declare ToyId char(6);
declare Qty smallint;
declare ToyWeight smallint;
declare ToyRate decimal(8,4);
#订单时间信息
declare times datetime;
declare months smallint;
declare years int;
#订单编号
declare iOrderNo int;
declare OrderNo char(6);
#收件人信息
declare FirstName varchar(20);
declare LastName varchar(20);
declare Address varchar(20);
declare City char(15);
declare State char(15);
declare CountryId char(3);
declare ZipCode char(10);
declare Phone char(15);
#总费用
declare ShippingCharges decimal(8,4) default 0;
declare GiftWrapCharges decimal(8,4) default 0;
declare TotalCost decimal(8,4) default 0;
#运输单价
declare RatePerPound decimal(8,4);
#运输时间
declare MaxDelDays int;
#包装费
declare WrapperRate decimal(8,4);
#游标
declare cur CURSOR FOR
SELECT sc.cToyId,sc.siQty
from GlobalToyz.ShoppingCart sc 
WHERE sc.cCartId = CartId;
declare CONTINUE HANDLER FOR NOT FOUND set done = 1;
set months = MONTH(NOW());
set years = YEAR(NOW());
set times = NOW();

start transaction;
SELECT sm.iMaxDelDays into MaxDelDays from GlobalToyz.ShippingMode sm WHERE sm.cModeId = ModeId;
SELECT COUNT(*) into iOrderNo from GlobalToyz.Orders; 
SELECT LPAD(CONVERT(iOrderNo,char(6)),6,'0') into OrderNo; 
SELECT s.vFirstName,s.vLastName,s.vAddress,s.cCity,s.cState,s.cCountryId,s.cZipCode,s.cPhone into FirstName,LastName,Address,City,State,CountryId,ZipCode,Phone
FROM GlobalToyz.Shopper s 
WHERE s.cShopperId =ShopperId;

#更新Recipient和Shipment
INSERT into GlobalToyz.Recipient values(OrderNo,FirstName,LastName,Address,City,State,CountryId,ZipCode,Phone);#收件人确定（默认为自己）
INSERT into GlobalToyz.Shipment values(OrderNo,null,null,null);#由商家的行为决定
open cur;

read_loop: LOOP
fetch cur into ToyId,Qty;
if done THEN
LEAVE read_loop;
END if;
#修改Toys
UPDATE GlobalToyz.Toys t
set t.siToyQoh = t.siToyQoh - Qty
WHERE t.cToyId = ToyId;
#已经有触发器的前提下这段可以不要,修改PickOfMonth
if exists(SELECT * FROM GlobalToyz.PickOfMonth pom1 WHERE pom1.CToyId = ToyId AND pom1.siMonth = months AND pom1.iYear = years) THEN 
UPDATE GlobalToyz.PickOfMonth pom
SET pom.iTotalSold = pom.iTotalSold + Qty
WHERE pom.CToyId = ToyId AND pom.siMonth = months AND pom.iYear = years;
ELSE
INSERT into GlobalToyz.PickOfMonth values(ToyId,months,years,Qty);
END if;
#修改orderdetail
SELECT t.mToyRate into ToyRate
FROM GlobalToyz.Toys t 
WHERE t.cToyId = ToyId;
INSERT INTO GlobalToyz.orderdetail values(OrderNo,ToyId,Qty,GiftWrap,WrapperId,Message,Qty*ToyRate);
#计算费用
SELECT t.siToyWeight into ToyWeight FROM GlobalToyz.Toys t WHERE t.cToyId = ToyId;
SELECT sr.mRatePerPound into RatePerPound FROM GlobalToyz.ShippingRate sr WHERE sr.cCountryId = CountryId AND sr.cModeId = ModeId;
set ShippingCharges = ShippingCharges + ToyWeight*RatePerPound;
SELECT w.mWrapperRate into WrapperRate FROM GlobalToyz.Wrapper w WHERE w.cWrapperId = WrapperId;
set GiftWrapCharges = GiftWrapCharges + WrapperRate;
set TotalCost = TotalCost + Qty*ToyRate;
END LOOP;
set TotalCost = Qty*ToyRate + ShippingCharges + GiftWrapCharges;
#更新Orders
INSERT into GlobalToyz.Orders values(OrderNo,times,CartId,ShopperId,ModeId,ShippingCharges,GiftWrapCharges,'N',TotalCost,DATE_ADD(times,INTERVAL MaxDelDays DAY));
commit;

close cur;
END

任务五：游标与SQL（数据库GlobalToyz）

1.基于表Orders和Shopper，以下列格式生成报表：（要求用游标实现）

购货人ID XXX 购货人姓名 XXX

购货人地址 XXXXXX

定单号XXX 定单时间XXX 定单金额XXX

mysql代码:

DROP procedure if exists GlobalToyz.get_order_report;

CREATE procedure GlobalToyz.get_order_report()
BEGIN
declare done int default 0;
declare ShopperId char(6);
declare FirstName varchar(20);
declare LastName varchar(20);
declare Address varchar(40);
declare OrderNo char(6);
declare OrderDate datetime;
declare TotalCost decimal(8,4);
declare preShopperId char(6) default null;
declare cur CURSOR FOR
SELECT s.cShopperId,s.vFirstName,s.vLastName,s.vAddress,o.cOrderNo,o.dOrderDate,o.mTotalCost
FROM GlobalToyz.Shopper s 
LEFT JOIN GlobalToyz.Orders o 
ON s.cShopperId = o.cShopperId
Order BY s.cShopperId,o.dOrderDate;
declare CONTINUE HANDLER for not found set done = 1;
open cur;
read_loop: LOOP
fetch cur into ShopperId,FirstName,LastName,Address,OrderNo,OrderDate,TotalCost;
if done then
leave read_loop;
end if;
if preShopperId is null or preShopperId<>ShopperId THEN 
SELECT ShopperId as '购货人ID',FirstName as '购货人名',LastName as '购货人姓',Address as '购货人地址';
end if;
if OrderNo is not null or OrderDate is not null or TotalCost is not null THEN 
SELECT OrderNo as '订单号',OrderDate as '订单时间',TotalCost as '订单金额';
end if;
set preShopperId = ShopperId;
END LOOP;
CLOSE cur;
END

CALL GlobalToyz.get_order_report();

java代码：

package class6;

import java.sql.*;

public class dbtest {
public static void main(String[] args)
{
String url = "jdbc:mysql://localhost:3306/GlobalToyz";//输入数据库的路径
String username ="root";
String password = "";//此处填你的用户密码
String ShopperId = null;
String preShopperId = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
Connection connection = DriverManager.getConnection(url,username,password);
String sql = "{CALL get_order_report()}";
CallableStatement stmt = connection.prepareCall(sql);
boolean h =stmt.execute();
while(h)
{
ResultSet rs = stmt.getResultSet();
ResultSetMetaData metaData = rs.getMetaData();
while(rs.next())
{
for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
{
preShopperId = ShopperId;
if(metaData.getColumnName(i).equals("ShopperId"))
{
ShopperId =rs.getString(i);
}
if((!ShopperId.equals(preShopperId))&&preShopperId!=null)
{
System.out.println();
}
if(metaData.getColumnName(i).equals("Address"))
System.out.println();
System.out.print(metaData.getColumnName(i)+":"+rs.getString(i)+"\t");
}
}
System.out.println();
rs.close();
h = stmt.getMoreResults();
}
stmt.close();
connection.close();
}catch (Exception e) {e.printStackTrace();}
}
}

2.编写代码，分析购物者、玩具和地域的关系，例如哪个城市的购买者对哪一种、哪一类或哪一个品牌的玩具更有兴趣。这道题是个开放的题目，同学们可以按照自己的理解从不同的角度进行分析。实验报告中需给出代码、结果截图和对分析结果的文字描述。

1.某个城市喜欢什么玩具的人最多

mysql代码：

SELECT s.cCity as '城市', od.siQty as '个数', t.vToyName as '玩具' 
FROM GlobalToyz.Shopper s ,GlobalToyz.Orders o ,GlobalToyz.orderdetail od ,GlobalToyz.Toys t 
WHERE s.cShopperId = o.cShopperId AND o.cOrderNo = od.cOrderNo AND od.cToyId = t.cToyId

结果截图：

文字描述：显然城市：Sunnyvale 最喜欢的是Robby the Whale 这个玩具
城市：Virginia Beach 最喜欢的是Victorian Family 这个玩具

城市：Las Vegas 最喜欢的是Beautifull Hair Doll这个玩具

2.同一种玩具，更受哪个城市喜欢

代码：

SELECT t.vToyName as '玩具' , od.siQty as '个数', s.cCity as '城市'
FROM GlobalToyz.Shopper s ,GlobalToyz.Orders o ,GlobalToyz.orderdetail od ,GlobalToyz.Toys t 
WHERE s.cShopperId = o.cShopperId AND o.cOrderNo = od.cOrderNo AND od.cToyId = t.cToyId 
order by t.vToyName;

结果截图：

文字描述：显然玩具Kitchen Set 在城市Hill Avenue更受欢迎

玩具Racing Truck在城市Sunnyvale更受欢迎

3.每个城市的人更喜欢哪个品牌的玩具

代码：

SELECT s.cCity '城市',od.siQty '个数',tb.cBrandName as '玩具品牌'
FROM GlobalToyz.Shopper s ,GlobalToyz.Orders o ,GlobalToyz.orderdetail od,GlobalToyz.Toys t ,GlobalToyz.ToyBrand tb 
WHERE s.cShopperId = o.cShopperId AND o.cOrderNo = od.cOrderNo AND od.cToyId = t.cToyId AND t.cBrandId = tb.cBrandId;

结果截图：

文字描述：显然城市Sunnyvale 更喜欢Bobby品牌的玩具。

实验三

实验任务书：

1.场景描述

假设某银行要开发一个手机银行App，需要设计一个数据库Bank Finance。针对手机银行App银行的业务，需要管理的对象包括客户、银行卡、存款、理财产品和基金。客户可以办理银行卡，同时可以购买不同的银行产品。

对象之间存在如下关系：

1）一个客户可以办理多张银行卡（包含储蓄卡和信用卡）；

2）一个客户可以办理多笔存款，同一类存款可由多个客户办理；

3）一个客户可以购买多个理财产品，同一类理财产品可由多个客户购买；

4）一个客户可以购买多个基金，同一类基金可由多个客户购买；

2.数据库建模

1）基于上述场景，调研银行业务，确定被管理的对象，包括客户、银行卡、存款、理财产品、保险和基金的属性以及对象之间的联系。

2）学习使用数据库辅助设计工具Sybase PowerDesigner，下载链接https://www.cnblogs.com/huangting/p/12654057.html。

3）使用PowerDesigner进行数据库概念结构建模，画出ER图。

4）使用PowerDesigner生成关系数据模型。

5）连接到数据库服务器上，生成数据库BankFinance，创建数据库表关系图。

3.模型验证

在数据库表中生成若干模拟数据，验证模型是否支持下列查询：

1）查询某客户的总资产。

2）查询某客户的总负债。

3）查询某客户购买的基金的总持仓收益和总持仓收益率。

4）查询某客户持有的所有银行卡的卡号、类型、状态、余额。

5）查询手机银行目前在售的所有理财产品的名称、编号、产品期限、起购金额。

PS：本人用的macOS，下不了这个软件，所以是直接手写这个实验的

1.业务调研的结果。以表格形式列出所有对象的属性及属性的描述。

对象	客户	银行卡	信用卡	储蓄卡	理财产品	存款	保险	基金
属性	客户编号	卡号	卡号	卡号	产品编号	类型	类型	类型
	姓名	类型	类型	类型	类型	名称	名称	名称
	性别	状态	状态	状态	名称	期限	期限	期限
	身份证号	可用余额	可用余额	可用余额	期限	起购金额	起购金额	起购金额
			额度		起购金额	利率	理赔倍率	基金市值
						关联的储蓄卡号

2.数据库概念结构建模的结果（ER图）。

因为我用的是macOS，而对应软件不支持该操作系统，所以图是我自己画的。

3.ER图转换的关系数据模型的截图，以及自动生成的建表的SQL语句。

CREATE database if not exists Bank_Finance;

CREATE table Bank_Finance.Customers
(
iId int not null,
vName varchar(20),
vSex varchar(10),
vId_card varchar(10),
primary key(iId),
check (vSex IN('male','female')) 
)

CREATE table Bank_Finance.Credit_Card
(
cKind char(10) not null,
iLimit int not null,
primary key(cKind)
)

CREATE table Bank_Finance.Card
(
vCardId varchar(10) not null,
cKind char(10) not null,
cState char(10) not null,
iBalance int not null,
primary key(vCardId)
)

CREATE table Bank_Finance.Customers_Card_details
(
iId int not null,
vCardId varchar(10) not null,
dData date not null,
primary key (iId,vCardId),
foreign key (iId) REFERENCES Bank_Finance.Customers(iId),
foreign key (vCardId) REFERENCES Bank_Finance.Card(vCardId)
)

CREATE table Bank_Finance.Deposit
(
cKind char(10) not null,
vName varchar(20) not null,
dLastingDate Date,
iMinValue int not null,
dRate decimal(8,4) not null,
primary key (cKind)
)

CREATE table Bank_Finance.Insurance
(
cKind char(10) not null,
vName varchar(20) not null,
dLastingDate Date not null,
iMinValue int not null,
dRate decimal(8,4) not null,
primary key (cKind)
)

CREATE table Bank_Finance.Fund
(
cKind char(10) not null,
vName varchar(20) not null,
dLastingDate Date not null,
iMinValue int not null,
primary key (cKind)
)

CREATE table Bank_Finance.FundDetails
(
iOrderId int not null,
iValue int not null,
foreign key (iOrderId) REFERENCES Bank_Finance.Orders(iOrderId)
)

CREATE table Bank_Finance.Products
(
iId int not null,
cKind char(10) not null,
primary key (iId)
)

CREATE table Bank_Finance.Orders
(
iOrderId int not null,
C_iId int not null,
P_iId int not null,
iValue int not null,
dDate date not null,
vCardId varchar(10),
primary key (iOrderId),
foreign key (C_iId) REFERENCES Bank_Finance.Customers(iId),
foreign key (P_iId) REFERENCES Bank_Finance.Products(iId),
foreign key (vCardId) REFERENCES Bank_Finance.Card(vCardId)
)

4.在数据库中生成的数据库表关系图。

5.进行模型验证时的SQL语句以及执行结果的截图。

1.查询某客户的总资产。

SELECT c1.vName as '查找对象的姓名',SUM(c2.iBalance)+SUM(o.iValue) as '总资产'
FROM Bank_Finance.Customers c1 ,Bank_Finance.Customers_Card_details ccd ,Bank_Finance.Card c2 ,Bank_Finance.Orders o 
WHERE c1.iId = ccd.iId and ccd.vCardId = c2.vCardId and o.C_iId = c1.iId and c1.iId = 1;

2. 查询某客户的总负债。

SELECT c1.vName as '查找对象的姓名',SUM(cc.iLimit)-SUM(c2.iBalance) as '总负债'
FROM Bank_Finance.Customers c1 , Bank_Finance.Customers_Card_details ccd ,Bank_Finance.Card c2 ,Bank_Finance.Credit_Card cc 
WHERE c1.iId = ccd.iId and ccd.vCardId = c2.vCardId and c2.cKind = cc.cKind and c1.iId = 2;

3.查询某客户购买的基金的总持仓收益和总持仓收益率。

SELECT c.vName as '查找对象的姓名',SUM(fd.iValue)-SUM(o.iValue) as '总持仓收益',(SUM(fd.iValue)-SUM(o.iValue))/SUM(o.iValue) as '总持仓收益率'
from Bank_Finance.Customers c ,Bank_Finance.Orders o ,Bank_Finance.FundDetails fd 
WHERE c.iId = o.C_iId and o.iOrderId = fd.iOrderId and c.iId =3;

4.查询某客户持有的所有银行卡的卡号、类型、状态、余额。

SELECT c1.vName as '查找对象的姓名',c2.vCardId as '卡号',c2.cKind as '类型',c2.cState as '状态',c2.iBalance '余额'
FROM Bank_Finance.Customers c1 ,Bank_Finance.Customers_Card_details ccd ,Bank_Finance.Card c2
WHERE c1.iId = ccd.iId and ccd.vCardId = c2.vCardId and c1.iId =1;

5.查询手机银行目前在售的所有理财产品的名称、编号、产品期限、起购金额。

SELECT d.vName as '名称',p.iId as '编号',d.dLastingDate as '产品期限',d.iMinValue as '起购金额'
FROM Bank_Finance.Deposit d,Bank_Finance.Products p 
WHERE p.cKind = d.cKind
UNION 
SELECT i.vName as '名称',p.iId as '编号',i.dLastingDate as '产品期限',i.iMinValue as '起购金额'
FROM Bank_Finance.Insurance i,Bank_Finance.Products p 
WHERE p.cKind = i.cKind
UNION 
SELECT f.vName as '名称',p.iId as '编号',f.dLastingDate as '产品期限',f.iMinValue as '起购金额'
FROM Bank_Finance.Fund f,Bank_Finance.Products p 
WHERE p.cKind = f.cKind;