数据库实验报告（1-8）

最新推荐文章于 2023-06-08 21:42:37 发布

出云coding

最新推荐文章于 2023-06-08 21:42:37 发布

阅读量3.3k

点赞数 3

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

本文链接：https://blog.csdn.net/sxh195792/article/details/122288361

版权

数据库专栏收录该内容

1 篇文章 2 订阅

订阅专栏

文章是从文件导入的，图片转存失败，没有显示，文章些许格式错误可能是因为格式不兼容，文章末尾有实验报告资源连接，需要的可以下载

1-8完整实验报告
 数据库实验：高考志愿报名辅助管理系统

实验一数据库的定义及数据导入

1.实验目的

理解和掌握数据库DDL语言，能够熟练使用SQL、DDL语句，并能够完成数据导入

2.实验内容和要求

理解和掌握 SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法；使用 SQL 语句创建、修改和删除基本表；掌握 SQL 语句常见语法错误的调试方法；完成数据导入。

3.实验步骤

1.创建数据库

CREATE DATABASE test;

2.地区表region

CREATE TABLE region
(
regionkey INT PRIMARY KEY,
NAME CHAR(25),
COMMENT VARCHAR(150)
);

3.国家表nation

CREATE TABLE nation
(
nationkey INT PRIMARY KEY,
NAME CHAR(25),
regionkey INT ,
COMMENT VARCHAR(150),
FOREIGN KEY(regionkey) REFERENCES region(regionkey)
);

4.供应商基本表supplier

CREATE TABLE supplier
(suppkey INT PRIMARY KEY,
NAME CHAR(100),
address VARCHAR(100),
nationkey INT,
FOREIGN KEY(nationkey)REFERENCES nation(nationkey),
phone CHAR(30),
acctbal NUMERIC(12,2),
COMMENT VARCHAR(100)
);

5.零件基本表part

CREATE TABLE part
(partkey INT PRIMARY KEY,
NAME VARCHAR(100),
mfgr CHAR(50),
brand CHAR(50),
TYPE VARCHAR(25),
size INT,
container CHAR(10),
retailprice NUMERIC(8,2),
COMMENT VARCHAR(20)
);

6.零件供应联系表partsupp

CREATE TABLE partsupp
(partkey INT,
suppkey INT,
PRIMARY KEY(partkey,suppkey),
FOREIGN KEY(partkey)REFERENCES part(partkey), 
FOREIGN KEY(suppkey)REFERENCES supplier(suppkey), 
availqty INT,
supplycost NUMERIC(10,2),
COMMENT VARCHAR(200)
);

7.顾客表customer

CREATE TABLE customer
(
custkey INT PRIMARY KEY,
NAME VARCHAR(25),
address VARCHAR(40),
nationkey INT,
phone CHAR(30),
acctbal NUMERIC(12,2),
mktsegment CHAR(10),
COMMENT VARCHAR(10),
FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);

8.订单表orders

CREATE TABLE orders
(
orderkey INT PRIMARY KEY,
custkey INT,
orderstatus CHAR(1),
totalprice NUMERIC(10,2),
orderdate DATE,
orderpriority CHAR(15),
clerk CHAR(16),
shippriority CHAR(1),
COMMENT VARCHAR(60),
FOREIGN KEY(custkey) REFERENCES customer(custkey)
);

9.订单明细表lineitem


CREATE TABLE lineitem
(
orderkey INT,
partkey INT,
suppkey INT,
linenumber INT,
quantity INT,
extendedprice NUMERIC(8,2),
discount NUMERIC(3,2),
tax NUMERIC(3,2),
returnflag CHAR(1),
linestatus CHAR(1),
shipdate DATE NULL,
commitdate DATE,
receiptdate DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
COMMENT VARCHAR(40),
PRIMARY KEY(orderkey,linenumber),
FOREIGN KEY(orderkey) REFERENCES orders(orderkey),
FOREIGN KEY (partkey) REFERENCES part(partkey),
FOREIGN KEY (suppkey) REFERENCES supplier(suppkey),
FOREIGN KEY(partkey,suppkey)REFERENCES partsupp(partkey,suppkey)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VwebMTow-1641196020021)(代码.assets/建表.png)]

实验二数据查询

1.实验目的

理解 SQL 程序设计基本规范，熟练运用 SQL 语言实现数据查询，包括单表查询、分组统计查询和连接查询、嵌套查询合和集合查询。

2.实验内容和要求

针对 tpch 数据库或自建数据库 test 设计各种单表查询、分组统计查询和连接查询、嵌套查询合和集合查询，理解和掌握 SQL 查询语句各个子句的特点和作用，按照 SQL 程序设计规范写出具体的 SQL 查询语句，并调试通过。

3.实验步骤

(1)查询各个地区的编号和名称。

SELECT regionkey,NAME
FROM region;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AZUCdsET-1641196020022)(代码.assets/1.png)]

(2)查询各个供应商的编号、名称、地址、电话和供应商所在国家名称。

SELECT suppkey,supplier.name,address,phone,nation.name
FROM supplier,nation
WHERE supplier.nationkey=nation.nationkey;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yzlcK1dF-1641196020022)(代码.assets/2.png)]

(3)查询 2021 年 9 月份每个订单金额超过 10000 元的订单编号、顾客姓名、顾客所在国家名称、和订单日期。

SELECT orderkey,customer.name,nation.name,orderdate
FROM orders,customer,nation
WHERE orderdate BETWEEN '2021-09-01' AND '2021-09-30' AND 
totalprice>1000 AND orders.custkey=customer.custkey AND
customer.nationkey=nation.nationkey;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vAhJWqSq-1641196020023)(代码.assets/3.png)]

(4)统计每个顾客在 2020 年的订购总金额，显示顾客编号、顾客姓名、订购总金额。

SELECT SUM(totalprice),customer.custkey,name
FROM orders,customer
WHERE orderdate BETWEEN '2020-01-01' AND '2020-12-31' AND
orders.custkey=customer.custkey
GROUP BY orders.custkey;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vMCvtaeu-1641196020023)(代码.assets/4.png)]

(5)查询订单平均金额超过 5 万元的顾客编号、姓名、所在国家名称和订单平均金额。

SELECT AVG(totalprice),orders.custkey,customer.name,nation.name
FROM orders,customer,nation
WHERE orders.custkey=customer.custkey AND
customer.nationkey=nation.nationkey
GROUP BY orders.custkey
HAVING AVG(totalprice)>50000;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dAwaKm3t-1641196020024)(代码.assets/5.png)]

(6)查询与“金石印刷有限公司”在同一个国家的供应商编号、名称和地址信息。

SELECT name,suppkey,address
FROM supplier
WHERE nationkey IN(
SELECT nationkey
FROM supplier
WHERE name='金石印刷有限公司'
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-uUAqDvCw-1641196020024)(代码.assets/6.png)]

(7)查询供应价格小于零售价格的零件编号、零件名称、制造厂、品牌、零售价格、供应商名称和供应价格。

SELECT part.partkey,part.name,mfgr,brand,retailprice,supplier.name,supplycost
FROM part,partsupp,supplier
WHERE part.partkey=partsupp.partkey AND 
supplycost<retailprice AND 
partsupp.suppkey=supplier.suppkey;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jvGvOrmI-1641196020024)(代码.assets/7.png)]

(8)查询顾客“曹玉书”订购的订单编号、订单金额及其订购的零件编号、数量和订单明细价格。

SELECT lineitem.orderkey,totalprice,partkey,quantity,extendedprice,customer.name
FROM orders,lineitem,customer
WHERE orders.custkey=customer.custkey AND 
orders.orderkey=lineitem.orderkey AND
customer.name='曹玉书';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q7Bk8fOT-1641196020025)(代码.assets/8.png)]

(9)查询订购了“南昌矿山机械厂”制造的“缝盘机”的顾客编号、姓名。

SELECT customer.custkey,customer.name
FROM part,customer,lineitem,orders
WHERE part.name='缝盘机' AND 
part.mfgr='南昌矿山机械厂' AND
part.partkey=lineitem.partkey AND
lineitem.orderkey=orders.orderkey AND
orders.custkey=customer.custkey;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QzvLY8N3-1641196020025)(代码.assets/9.png)]

(10)查询订单平均金额超过 1 万元的顾客中的中国籍顾客的顾客编号、姓名。

SELECT AVG(totalprice),orders.custkey,customer.name,nation.name
FROM orders,customer,nation
WHERE orders.custkey=customer.custkey AND
customer.nationkey=nation.nationkey AND
nation.name='中国'
GROUP BY orders.custkey
HAVING AVG(totalprice)>10000;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kxXMwhoZ-1641196020025)(代码.assets/10.png)]

(11)查询顾客“刘玉龙”和“钱岚”都订购过的全部零件的信息。

SELECT part.partkey
FROM part,lineitem,orders,customer
WHERE part.partkey=lineitem.partkey AND
lineitem.orderkey=orders.orderkey AND
orders.custkey=customer.custkey AND
customer.name='刘玉龙' AND part.partkey IN(
SELECT part.partkey
FROM part,lineitem,orders,customer
WHERE part.partkey=lineitem.partkey AND
lineitem.orderkey=orders.orderkey AND
orders.custkey=customer.custkey AND
customer.name='钱岚'
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mj9sRBEG-1641196020026)(代码.assets/11.1刘.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4DZyj7pa-1641196020026)(代码.assets/11.2钱.png)]

(12)查询顾客“刘玉龙”或“钱岚”订购的全部零件的信息。

SELECT *
FROM part
WHERE partkey IN
(
	SELECT partkey FROM lineitem WHERE orderkey IN
	(
		SELECT orderkey FROM orders WHERE custkey=
		(
			SELECT custkey FROM customer WHERE name='刘玉龙'
		)
	)
UNION
	SELECT partkey FROM lineitem WHERE orderkey IN
	(
		SELECT orderkey FROM orders WHERE custkey=
		(
			SELECT custkey FROM customer WHERE name='钱岚'
		)
	)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-4gCwczsG-1641196020026)(代码.assets/12.png)]

(13)查询顾客“刘玉龙”订购过而“钱岚”没订购过的零件的信息。

SELECT *
FROM part
WHERE partkey IN
(
SELECT part.partkey
FROM part,lineitem,orders,customer
WHERE part.partkey=lineitem.partkey AND
lineitem.orderkey=orders.orderkey AND
orders.custkey=customer.custkey AND
customer.name='刘玉龙' AND part.partkey NOT IN(
SELECT part.partkey
FROM part,lineitem,orders,customer
WHERE part.partkey=lineitem.partkey AND
lineitem.orderkey=orders.orderkey AND
orders.custkey=customer.custkey AND
customer.name='钱岚'
)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bEPNuUPq-1641196020027)(代码.assets/13.png)]

实验三数据更新实验

1.实验目的

熟悉数据库的数据更新操作，能够使用 SQL 语句对数据库进行数据的增加、删除、修改操作。

2.实验内容和要求

针对 tpch 数据库或自建数据库 test 设计单行插入、批量插入、修改和删除数据操作，理解和掌握 insert、delete 和 update 语法结构的各个组成成分，结合嵌套 SQL 子查询，分别设计几种不同形式的语句，并调试通过。

3.实验步骤

（1）供应商表插入一条记录，每列的值要给的合理。

SELECT COUNT(*) FROM supplier;#查询表中有多少条数据

INSERT INTO supplier
VALUES(30900,'天庭贸易','凌霄宝殿',40,'18812671937',0.00,'VIP');

select supplier.* 
from supplier where supplier.suppkey=30900;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tw0t4e5j-1641196020027)(代码.assets/1-16396528437251.png)]

（2）把所有中国籍顾客的所有信息插入到一个新的顾客（customernew）中。

CREATE TABLE newcustomer
(
custkey INT PRIMARY KEY,
name varchar(25),
address VARCHAR(40),
nationkey INT,
phone CHAR(30),
acctbal NUMERIC(12,2),
mktsegment CHAR(10),
comment VARCHAR(10),
FOREIGN KEY(nationkey) REFERENCES nation(nationkey)
);

INSERT INTO newcustomer
SELECT *
FROM customer
WHERE nationkey=(
SELECT nationkey
FROM nation
WHERE name='中国'
);

SELECT *
FROM newcustomer;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Q7tpS4zR-1641196020027)(代码.assets/2-16396528549342.png)]

（3）把每个顾客及其购物总数量和购物总价等信息插入到顾客统计表（cusshopping）

CREATE TABLE cusshopping
(
custkey INT PRIMARY KEY,#顾客编号
goodsnum INT,
goodsprice NUMERIC(10,2)
);

INSERT INTO cusshopping
SELECT customer.custkey,SUM(lineitem.quantity),SUM(orders.totalprice)
FROM customer,lineitem,orders
WHERE customer.custkey=orders.orderkey AND orders.orderkey=lineitem.orderkey
GROUP BY customer.custkey;

SELECT *
FROM cusshopping;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A59TOwy9-1641196020028)(代码.assets/3-16396528617693.png)]

（4）把“深圳市鸿运贸易有限公司”供应的所有零件的供应价格降价 20%。

UPDATE partsupp
SET supplycost=supplycost*0.8
WHERE suppkey=(
SELECT suppkey
FROM supplier
WHERE name='深圳市鸿运贸易有限公司'
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qXNsNdlr-1641196020028)(代码.assets/4-16396528693644.png)]

（5）更新订单明细表中的订单明细价格，订单明细价格（extendedprice）=零售价格(part.retailprice)* 零件数量（quantity）。

UPDATE lineitem,part
SET extendedprice=part.retailprice*lineitem.quantity
WHERE lineitem.partkey=part.partkey;

SELECT lineitem.extendedprice
FROM lineitem;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bj48CE3M-1641196020028)(代码.assets/5.2.png)]

（6）删除顾客“童帅”的所有订单明细记录和订单记录。

#删除订单明细记录
DELETE 
FROM lineitem
WHERE orderkey IN(
	SELECT orderkey
	FROM orders
	WHERE custkey=(
		SELECT custkey
		FROM customer
		WHERE name='童帅'
	)
);
#查询删除后结果
SELECT *
FROM lineitem
WHERE orderkey IN(
	SELECT orderkey
	FROM orders
	WHERE custkey=(
		SELECT custkey
		FROM customer
		WHERE name='童帅'
	)
);

#删除订单记录
DELETE
FROM orders
WHERE custkey=(
		SELECT custkey
		FROM customer
		WHERE name='童帅'
);
#查询删除后结果
SELECT *
FROM orders
WHERE custkey=(
		SELECT custkey
		FROM customer
		WHERE name='童帅'
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D1WCwwF2-1641196020029)(代码.assets/6.11.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-tPALrWMG-1641196020029)(代码.assets/6.21.png)]

（7）删除“澳大利亚”的所有供应商信息。

SET FOREIGN_key_checks=0;#先设置外键约束检查关闭

DELETE
FROM supplier
WHERE nationkey=(
SELECT nationkey
FROM nation
WHERE name='澳大利亚'
);

SELECT *
FROM supplier
WHERE nationkey=(
SELECT nationkey
FROM nation
WHERE name='澳大利亚'
);
SET FOREIGN_key_checks=1;#开启外键约束检查，保持表结构完整性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AmmWDIjC-1641196020029)(代码.assets/7.11.png)]

实验四视图实验

1.实验目的

熟悉 SQL 语言有关视图的操作，能够熟练使用 SQL 语句来创建需要的视图，定义数据库外模式，并能使用所创建的视图实现数据管理。

2.实验内容和要求

针对 tpch 数据库或自建数据库 test 创建视图和带 with check option 的视图，并验证 with check option 选项的有效性。理解和掌握视图消解执行原理，掌握可更新视图和不可更新视图的区别。

3.实验步骤

（1）创建一个“河北省华信集团”供应商供应的零件视图 Viewpart1,要求列出供应商编号、供应商名称、零件编号、零件名称、可用数量、零售价格和供应价格。

#建立试图
CREATE VIEW Viewpart1(suppkey,suppliername,partkey,partname,availqty,retailprice,supplycost)
AS
SELECT supplier.suppkey,supplier.name,part.partkey,part.name,partsupp.availqty,part.retailprice,partsupp.supplycost
FROM supplier,part,partsupp
WHERE supplier.name='河北省华信集团' AND 
supplier.suppkey=partsupp.suppkey AND
partsupp.partkey=part.partkey;

#查询试图
SELECT *
FROM Viewpart1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7cjZGl5O-1641196020030)(代码.assets/1.1.1.png)]

（2）创建一个视图 ViewCust1,按顾客统计 2020 年订单的购买总金额和零件总数量，要求输出顾客编号、姓名、购买总金额和购买零件总数量。

#创建试图
CREATE VIEW ViewCust1(custkey,name,sumtotalprice,sumquantity)
AS
SELECT orders.custkey,customer.name,SUM(orders.totalprice),SUM(lineitem.quantity)
FROM orders,customer,lineitem
WHERE orders.orderdate BETWEEN '2020-01-01' AND '2020-12-31' AND
orders.custkey=customer.custkey AND
orders.orderkey=lineitem.orderkey
GROUP BY orders.custkey;
#查询创建结果
SELECT *
FROM ViewCust1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZHA5vuMt-1641196020030)(代码.assets/2.0.2.png)]

（2.1）针对刚创建的视图，插入一条记录，看看是否能通过验证，并说明原因。

#对视图进行插入，发现不能插入，视图的字段来自表达式，故不允许进行更新的插入操作P127
INSERT INTO ViewCust1
VALUES(999999,'赵子龙',99.00,500);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KU2Wubid-1641196020030)(代码.assets/2.1.png)]

（2.2）针对刚创建的视图，查询购买总金额超过 10 万的顾客编号、姓名、购买总金额和购买零件总数量。

SELECT *
FROM viewcust1
WHERE sumtotalprice>100000;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EgTrlmup-1641196020031)(代码.assets/2.2.png)]

（3）创建一个“河北钢铁集团有限公司”供应商供应的零件视图 Viewpart2,要求列出供应零件的编号、供应商编号、可用数量和供应价格。

创建视图

## 建立视图
CREATE VIEW Viewpart2(partkey,suppkey,availqty,supplycost)
AS
SELECT partkey,suppkey,availqty,supplycost
FROM partsupp
WHERE suppkey=(
SELECT suppkey
FROM supplier
WHERE name='河北钢铁集团有限公司'
);

## 查看建立的视图
SELECT *
FROM Viewpart2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ACTc0YGS-1641196020031)(代码.assets/3.1.png)]

插入操作

##增加一条
INSERT INTO Viewpart2
VALUES(6666,24706,100,20);
## 查询插入结果
SELECT *
FROM Viewpart2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aOy0cpAj-1641196020031)(代码.assets/3插入.png)]

修改操作

##修改数据
UPDATE Viewpart2
SET supplycost=21
WHERE partkey=6666;

SELECT *
FROM Viewpart2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YE2sohSv-1641196020031)(代码.assets/3修改.png)]

删除操作

##删除数据
DELETE FROM Viewpart2
WHERE partkey=6666;

SELECT *
FROM Viewpart2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-84sq9Nvd-1641196020032)(代码.assets/3删除.png)]

（4）使用 with check option 创建一个“河北钢铁集团有限公司”供应商供应的零件视图 Viewpart3,要求列出供应零件的编号、供应商编号、可用数量和供应价格。然后通过该视图分别增加、删除和修改一条“河北钢铁集团有限公司”零件供应记录，验证该视图是否可更新，并比较和“（3）”的实验结果有无异同。

创建视图

## 建立视图
CREATE VIEW Viewpart3(partkey,suppkey,availqty,supplycost)
AS
SELECT partkey,suppkey,availqty,supplycost
FROM partsupp
WHERE suppkey=(
SELECT suppkey
FROM supplier
WHERE name='河北钢铁集团有限公司'
)WITH CHECK OPTION;

SELECT *
FROM Viewpart3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-D4WVRCQf-1641196020032)(代码.assets/4建表.png)]

插入操作

##增加一条
INSERT INTO Viewpart3
VALUES(6666,24706,100,20);

## 查询插入结果
SELECT *
FROM Viewpart3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-THBjUdsU-1641196020032)(代码.assets/4插入-16396541406785.png)]

修改操作

##修改数据
UPDATE Viewpart3
SET supplycost=21
WHERE partkey=6666;

SELECT *
FROM Viewpart3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-y7Ehk3bG-1641196020033)(代码.assets/4修改.png)]

删除操作

##删除数据
DELETE FROM Viewpart3
WHERE partkey=6666;

SELECT *
FROM Viewpart3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g2KBEaf5-1641196020033)(代码.assets/4删除.png)]

（5）创建顾客订购零件明细视图 Viewcust2,要求列出顾客编号、姓名、购买零件数、金额；

##创建视图
CREATE VIEW ViewCust2(custkey,name,quantity,extendedprice)
AS
SELECT customer.custkey,customer.name,lineitem.quantity,lineitem.extendedprice
FROM customer,orders,lineitem
WHERE customer.custkey=orders.custkey AND
orders.orderkey=lineitem.orderkey;

SELECT *
FROM ViewCust2;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W3LsqdO5-1641196020033)(代码.assets/5创建视图.png)]

（5.1）然后在该视图的基础上再创建视图 Viewcust3,列出每个顾客的平均购买零件数和平均金额，显示顾客编号、姓名、平均购买零件数、平均金额；

#5.1创建视图
CREATE VIEW ViewCust3(custkey,name,avgquantity,avgprice)
AS
SELECT custkey,name,AVG(quantity),AVG(extendedprice)
FROM ViewCust2
GROUP BY custkey,name;

SELECT *
FROM ViewCust3;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JgnCcRkV-1641196020034)(代码.assets/5.1-16396544568336.png)]

（5.2）删除视图 Viewcust2。

DROP VIEW ViewCust3;
DROP VIEW ViewCust2;

实验五安全性语言实验

1.实验目的

掌握自主存取控制权限的定义和维护方法。

2.实验内容和要求

定义用户、角色，分配权限给用户和角色、回收权限，以相应的用户名登陆数据库验证权限分配是否正确。

3.实验步骤

（1）创建用户：

①为采购、销售和客户管理等三个部门的经理 David、Tom、Jerry 创建用户表示，要求具有创建用户或角色的权利。

#(1)David
CREATE USER David IDENTIFIED BY 'David123456';
GRANT CREATE ON test TO David;
#(1)Tom
CREATE USER Tom IDENTIFIED BY 'Tom123456';
GRANT CREATE ON test to Tom;
#(1)Jerry
CREATE USER Jerry IDENTIFIED BY 'Jerry123456';
GRANT CREATE ON test to Jerry;

②为采购、销售和客户管理等三个部门的职员 Marry、Jack、Mike 创建用户标识和用户口令。

#创建Marry
CREATE USER Marry IDENTIFIED BY 'David123456';

#创建Jack
CREATE USER Jack IDENTIFIED BY 'David123456';
#创建Mike
CREATE USER Mike IDENTIFIED BY 'David123456';

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rkSjiL1N-1641196020034)(代码.assets/5.1.2创建角色成功png.png)]

（2）创建角色并分配权限：

①各个部门分别创建一个查询角色，并分配相应的查询权限。

采购对应：零件表、供应商表、零件供应联系表

# 采购对应
CREATE role PurchaseQueryRole;
GRANT SELECT ON supplier TO PurchaseQueryRole WITH GRANT OPTION;
GRANT SELECT ON part TO PurchaseQueryRole WITH GRANT OPTION;
GRANT SELECT ON partsupp TO PurchaseQueryRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qq3Z2agq-1641196020034)(代码.assets/购买的创建和授权.png)]

销售对应：订单表和订单明细表

#销售对应
CREATE role SaleQueryRole;
GRANT SELECT ON orders TO SaleQueryRole WITH GRANT OPTION;
GRANT SELECT ON lineitem TO SaleQueryRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pgAtO6GP-1641196020034)(代码.assets/销售对应png.png)]

客户对应：顾客表、国家表、地区表

#销售对应
CREATE role SaleQueryRole;
GRANT SELECT ON orders TO SaleQueryRole WITH GRANT OPTION;
GRANT SELECT ON lineitem TO SaleQueryRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rLAHdHkx-1641196020035)(代码.assets/客户对应.png)]

②为各个部门分别创建一个职员角色，对本部门具有查看、插入权限。

采购对应职员

#采购对应
CREATE ROLE PurchaseEmployeeRole;
GRANT SELECT,INSERT ON supplier TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON part TO PurchaseEmployeeRole;
GRANT SELECT,INSERT ON partsupp TO PurchaseEmployeeRole;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A2uuL2ys-1641196020035)(代码.assets/采购对应职员.png)]

销售对应职员

#销售对应
CREATE ROLE SaleEmployeeRole;
GRANT SELECT,INSERT ON orders TO SaleEmployeeRole;
GRANT SELECT,INSERT ON lineitem TO SaleEmployeeRole;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-a9aKZMKb-1641196020035)(代码.assets/销售对应职员.png)]

客户对应职员

#客户对应
CREATE ROLE CustomerEmployeeRole;
GRANT SELECT,INSERT ON customer TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON nation TO CustomerEmployeeRole;
GRANT SELECT,INSERT ON region TO CustomerEmployeeRole;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-50UhnzRP-1641196020036)(代码.assets/客户对应职员-16396568869887.png)]

③为各部门创建一个经理角色，相应角色对本部门的信息具有完全控制权限，经理有权给本部门职员分配权限

创建购买经理角色

#创建购买经理角色
CREATE ROLE PurchaseManagerRole;
GRANT ALL ON supplier TO PurchaseManagerRole WITH GRANT OPTION;
GRANT ALL ON part TO PurchaseManagerRole WITH GRANT OPTION;
GRANT ALL ON partsupp TO PurchaseManagerRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2u7YolXa-1641196020036)(代码.assets/购买经理.png)]

创建销售经理角色

#创建销售经理角色
CREATE ROLE SaleManagerRole;
GRANT ALL ON orders TO SaleManagerRole WITH GRANT OPTION;
GRANT ALL ON lineitem TO SaleManagerRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mMzi4MTt-1641196020036)(代码.assets/销售经理.png)]

创建客户经理角色

#创建客户经理角色
CREATE ROLE CustomerManagerRole;
GRANT ALL ON customer TO CustomerManagerRole WITH GRANT OPTION;
GRANT ALL ON nation TO CustomerManagerRole WITH GRANT OPTION;
GRANT ALL ON region TO CustomerManagerRole WITH GRANT OPTION;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-f1njhPaw-1641196020037)(代码.assets/客户经理.png)]

（3）给用户分配权限

①给各部门经理分配权限。

给采购经理David

# 采购David
GRANT All ON supplier TO David;
GRANT All ON part TO David;
GRANT All ON partsupp TO David;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pLwsQKdT-1641196020037)(代码.assets/5.33.1给购买经理-16396577062658.png)]

给销售经理Tom

# 销售Tom
GRANT All ON orders TO Tom;
GRANT All ON lineitem TO Tom;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IXz0goAp-1641196020037)(代码.assets/5.3.1给销售经理-16396578308579.png)]

给客户经理Jerry

# 客户Jerry
GRANT All ON customer TO Jerry;
GRANT All ON nation TO Jerry;
GRANT All ON region TO Jerry;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SWvFjtn0-1641196020038)(代码.assets/5.3.1给客户经理.png)]

②给各部门职员分配权限。

给采购职员Marry

##给采购职员Marry
GRANT SELECT,INSERT ON supplier TO Marry;
GRANT SELECT,INSERT ON part TO Marry;
GRANT SELECT,INSERT ON partsupp TO Marry;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-29GXykoL-1641196020038)(代码.assets/5.3.2给购买职员.png)]

给销售职员Jack

##给销售职员Jack
GRANT SELECT,INSERT ON orders TO Jack;
GRANT SELECT,INSERT ON lineitem TO Jack;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6zLJMy5n-1641196020038)(代码.assets/5.3.2给销售职员.png)]

给客户职员Mike

#给客户职员Mike
GRANT SELECT,INSERT ON customer TO Mike;
GRANT SELECT,INSERT ON nation TO Mike;
GRANT SELECT,INSERT ON region TO Mike;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3favTAgW-1641196020039)(代码.assets/5.3.2给客户职员.png)]

（4）回收角色或用户权限

①收回客户经理角色的客户信息查看权限。

REVOKE SELECT ON customer FROM CustomerManagerRole;
REVOKE SELECT ON nation FROM CustomerManagerRole;
REVOKE SELECT ON region FROM CustomerManagerRole;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BK1IS76l-1641196020039)(代码.assets/5.4.1回收客户经理角色查看.png)]

②回收 Mike 的客户部门职员权限。

REVOKE SELECT,INSERT ON customer FROM Mike;
REVOKE SELECT,INSERT ON nation FROM Mike;
REVOKE SELECT,INSERT ON region FROM Mike;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lOgGx1bA-1641196020039)(代码.assets/5.4.2回收Mike权限.png)]

（5）验证权限分配的正确性

①以 David 用户名登录数据库，验证采购部门经理的权限。

验证登录[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oFs2kLMz-1641196020040)(代码.assets/验证登录.png)]

验证查看权限

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RCX1vFBp-1641196020040)(代码.assets/验证查看权限.png)]

②回收 Mike 的客户部门职员权限。

验证[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-6nfJfNr4-1641196020040)(代码.assets/回收权限验证.png)]




#5.4回收角色或用户权限
##①收回客户经理角色的客户信息查看权限。
REVOKE SELECT ON customer FROM CustomerManagerRole;
REVOKE SELECT ON nation FROM CustomerManagerRole;
REVOKE SELECT ON region FROM CustomerManagerRole;
##②回收 Mike 的客户部门职员权限。
REVOKE SELECT,INSERT ON customer FROM Mike;
REVOKE SELECT,INSERT ON nation FROM Mike;
REVOKE SELECT,INSERT ON region FROM Mike;

#5.5验证权限分配正确性
#1
SELECT *
FROM part;
SELECT *
FROM orders;
#2

SELECT *
FROM customer;
SELECT *
FROM part;

实验六完整性语言实验

1.实验目的

掌握实体完整性、参照完整性和自定义完整性的定义和维护方法。

2.实验内容和要求

定义实体完整性，删除实体完整性。能够写出两种方式定义实体完整性的 SQL 语句：创建表时、创建表后定义实体完整性。设计 SQL 语句验证完整性约束是否起作用。

定义参照完整性，定义参照完整性的违约处理，删除参照完整性。写出两种方式定义参照完整性的 SQL 语句：创建表时、创建表后定义参照完整性。

针对具体应用语义，选择 NULL/NOT NULL/DEFAULT/UNIQUE/CHECK 等，定义属性上的约束条件。

3.实验步骤

（1）参照实验一供应商的表结构，创建供应商表(Supplier1)时定义实体完整性（列级实体完整性）。

CREATE TABLE Supplier1
(suppkey INT PRIMARY KEY,
NAME CHAR(100),
address VARCHAR(100),
nationkey INT,
FOREIGN KEY(nationkey)REFERENCES nation(nationkey),
phone CHAR(30),
acctbal NUMERIC(12,2),
COMMENT VARCHAR(100)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-djo7MWY4-1641196020040)(代码.assets/1-163965974153611.png)]

（2）参照实验一供应商的表结构，创建供应商表(Supplier2)时定义实体完整性（表级实体完整性）。

CREATE TABLE Supplier2
(suppkey INT,
NAME CHAR(100),
address VARCHAR(100),
nationkey INT,
FOREIGN KEY(nationkey)REFERENCES nation(nationkey),
phone CHAR(30),
acctbal NUMERIC(12,2),
COMMENT VARCHAR(100),

PRIMARY KEY(suppkey)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pBHf3tCY-1641196020041)(代码.assets/6.2.png)]

（3）参照实验一供应商的表结构，创建供应商表(Supplier3)，在创建表后再定义实体完整性。

#创建表
CREATE TABLE Supplier3
(suppkey INT,
NAME CHAR(100),
address VARCHAR(100),
nationkey INT,
FOREIGN KEY(nationkey)REFERENCES nation(nationkey),
phone CHAR(30),
acctbal NUMERIC(12,2),
COMMENT VARCHAR(100)
);
#在增加完整性
ALTER TABLE Supplier3
ADD PRIMARY KEY(suppkey);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YNzqwIu4-1641196020041)(代码.assets/6.3.png)]

（4）参照实验一供应关系表的结构，定义供应关系表（PartSupp1）的实体完整性。

（5）参照实验一国家表的结构，定义国家表（nation1）的实体完整性，其中 nationkey和 name 都是候选码，选择 nationkey 作主码，name 上定义唯一性约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MwERGdGR-1641196020041)(代码.assets/5-163965998071912.png)]

验证name唯一性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-pNemIHN6-1641196020042)(代码.assets/验证name唯一性.png)]

（6）给国家表（nation1）增加两条相同记录，验证实体完整性是否起作用。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AgNsAzTh-1641196020042)(代码.assets/6-163966000021913.png)]

（7）删除国家表（nation1）的主码。

#7删除nation1的住吗
ALTER TABLE nation1
DROP PRIMARY KEY;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oeCG31XD-1641196020042)(代码.assets/7-163966000807614.png)]

（8）参照实验一地区表和国家表的结构，先定义地区表（region1）的实体完整性，再定义国家表(nation2)的列级参照完整性和国家表(nation3)的表级参照完整性。

地区表region1的实体完整性

##定义 region1 实体完整性
CREATE TABLE region1
(
regionkey INT PRIMARY KEY,
NAME CHAR(25),
COMMENT VARCHAR(150)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Aue3Ie9Y-1641196020043)(代码.assets/8region1实体完整性.png)]

国家表nation2的列级参照性

##定义 nation2 的列级参照完整性
CREATE TABLE nation2
(
nationkey INT PRIMARY KEY,
NAME CHAR(25),
regionkey INT REFERENCES region1(regionkey),
COMMENT VARCHAR(150)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZUdudDfB-1641196020043)(代码.assets/8nation2的列级参照性.png)]

国家表nation3的表级参照性

##定义 nation3 的表级参照完整性
CREATE TABLE nation3
(
nationkey INT PRIMARY KEY,
NAME CHAR(25),
regionkey INT ,
COMMENT VARCHAR(150),
FOREIGN KEY(regionkey) REFERENCES region1(regionkey)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gTN4VVaX-1641196020043)(代码.assets/8nation3的表级参照性.png)]

（9）参照实验一订单明细表的结构，定义订单明细表（Lineitem1）的参照完整性。

CREATE TABLE lineitem1
(
orderkey INT,
partkey INT,
suppkey INT,
linenumber INT,
quantity INT,
extendedprice NUMERIC(8,2),
discount NUMERIC(3,2),
tax NUMERIC(3,2),
returnflag CHAR(1),
linestatus CHAR(1),
shipdate DATE NULL,
commitdate DATE,
receiptdate DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
COMMENT VARCHAR(40),
PRIMARY KEY(orderkey,linenumber),
FOREIGN KEY(orderkey) REFERENCES orders(orderkey),
FOREIGN KEY (partkey) REFERENCES part(partkey),
FOREIGN KEY (suppkey) REFERENCES supplier(suppkey),
FOREIGN KEY(partkey,suppkey)REFERENCES partsupp(partkey,suppkey)
);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ieD5O8Et-1641196020043)(代码.assets/9-163966034411715.png)]

（10）删除国家表（nation3）的外码。

#删除nation3里的外键
SHOW CREATE TABLE nation3;

ALTER TABLE nation3
DROP FOREIGN KEY nation3_ibfk_1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8cqcZEMW-1641196020044)(代码.assets/10删除nation3的外键.png)]

（11）给国家表（nation3）插入一条记录，验证参照完整性是否起作用。

INSERT INTO nation3
VALUES(1,'天庭',66,'VIP');

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0oGGWz2J-1641196020044)(代码.assets/11起作用.png)]

（12）定义国家表（nation4）的 regionkey 的缺省属性值为 0 值，表示其他地区。

CREATE TABLE nation4
(
nationkey INT PRIMARY KEY,
NAME CHAR(25),
regionkey INT DEFAULT 0,
COMMENT VARCHAR(150),
FOREIGN KEY(regionkey) REFERENCES region1(regionkey)
);

（13）参照实验一订单明细表的结构，使用 CHECK 定义订单明细表（Lineitem2）中某些属性应该满足的约束。如：装运日期 < 签收日期，退货标记为 A,R 或 N 中某一个。修改 Lineitem2 的一条记录，验证是否违反 CHECK 约束。

建表

#13参照
CREATE TABLE lineitem2
(
orderkey INT,
partkey INT,
suppkey INT,
linenumber INT,
quantity INT,
extendedprice NUMERIC(8,2),
discount NUMERIC(3,2),
tax NUMERIC(3,2),
returnflag CHAR(1),
linestatus CHAR(1),
shipdate DATE NULL,
commitdate DATE,
receiptdate DATE,
shipinstruct CHAR(25),
shipmode CHAR(10),
COMMENT VARCHAR(40),
PRIMARY KEY(orderkey,linenumber),
FOREIGN KEY(orderkey) REFERENCES orders(orderkey),
FOREIGN KEY (partkey) REFERENCES part(partkey),
FOREIGN KEY (suppkey) REFERENCES supplier(suppkey),
FOREIGN KEY(partkey,suppkey)REFERENCES partsupp(partkey,suppkey),
CHECK(shipdate<receiptdate),  /*装运日期<签收日期*/
CHECK(returnflag IN ('A','R','N'))

);

验证

#验证

INSERT INTO lineitem2(orderkey,partkey,suppkey,linenumber)
VALUES(1,44930,18848,1);

UPDATE lineitem2
SET shipdate='2021-10-30',receiptdate='2021-10-31'
WHERE orderkey=1 AND linenumber=1;


UPDATE lineitem2
SET shipdate='2021-10-30',receiptdate='2021-10-25';
WHERE orderkey=1 AND linenumber=1;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AZH6b4cf-1641196020044)(代码.assets/最后.png)]

实验七存储过程实验

1.实验目的

掌握数据库存储过程的设计和使用方法。

2.实验内容和要求

存储过程的定义，存储过程运行，存储过程更名，存储过程删除。

3.实验步骤

（1）定义一个存储过程 proc1，更新所有订单(含税折扣价)的总价，执行这个存储过程。

/*含税折扣总价=extendedprice*(税率+1）*折扣*/
##(1)定义一个存储过程 proc1，更新所有订单(含税折扣价)的总价，执行这个存储过程。
CREATE PROCEDURE proc1()
BEGIN
	UPDATE orders,
	(SELECT orderkey,SUM(extendedprice*(1-discount)*(1 + tax)) AS sum_account
	FROM lineitem
	GROUP BY orderkey) AS temp
	SET orders.totalprice = temp.sum_account 
	WHERE orders.orderkey= temp.orderkey;
END;
#运行
CALL proc1();

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-r13cXN7v-1641196020044)(代码.assets/1-163966124315216.png)]

（2）定义一个存储过程 proc2，更新给定订单的(含税折扣价)的总价，执行这个存储过程

##(2)定义一个存储过程 proc2，更新给定订单的(含税折扣价)的总价，执行这个存储过 程
CREATE PROCEDURE proc2(number int)
BEGIN
	UPDATE orders
	SET orders.totalprice=(
		SELECT SUM(extendedprice*(1-discount)*(1+tax)) AS sum_account
		FROM lineitem
		GROUP BY orderkey
		HAVING number=orderkey)
	WHERE orders.orderkey=number;
END;

#调用，更新1号订单的总价
CALL proc2(1);

将数据修改错误

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dgnvAmWO-1641196020045)(代码.assets/2修改错误.png)]

执行后

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HB7neb0T-1641196020045)(代码.assets/2执行后.png)]

（3）定义一个存储过程 proc3，更新某个顾客的所有订单的（含税折扣价）总价，执行这个存储过程

##3）定义一个存储过程 proc3，更新某个顾客的所有订单的（含税折扣价）总价，执行 这个存储过程。
##参数为顾客编号,更具顾客编号找到顾客的订单号,在根据订单号来查询
CREATE PROCEDURE proc3(number int)
BEGIN
	DECLARE corderkey INTEGER;/*定义变量*/
	SELECT orderkey INTO corderkey
	FROM orders
	WHERE orders.custkey=number;

	UPDATE orders
	SET orders.totalprice=(
		SELECT SUM(extendedprice*(1-discount)*(1+tax)) AS sum_account
		FROM lineitem
		GROUP BY orderkey
		HAVING corderkey=orderkey)
	WHERE orders.orderkey=corderkey;
END;

## 执行，更新顾客编号为518445的顾客所有订单
CALL proc3(518445);

将数据修改错误

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bYnXtmAJ-1641196020046)(代码.assets/3改成错误数据.png)]

执行后

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-8Nyzspd8-1641196020046)(代码.assets/3执行后.png)]

（4）定义一个存储过程 proc4，更新某个顾客的所有订单的（含税折扣价）总价并输出该总价，执行这个存储过程

CREATE PROCEDURE proc4(number int,OUT account DOUBLE)
BEGIN 
	UPDATE orders,
	(
		SELECT orderkey,SUM(extendedprice*(1-discount)*(1+tax)) AS sum_account
		FROM lineitem
		GROUP BY orderkey
	) AS temp
	SET orders.totalprice=sum_account
	WHERE orders.custkey=number AND orders.orderkey=temp.orderkey;
	
	SELECT SUM(orders.totalprice) INTO account
	FROM orders
	GROUP BY custkey
	HAVING orders.custkey=number;
	
END;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jC1GZtmt-1641196020047)(代码.assets/4结果.png)]

一到四存储过程

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ohkethck-1641196020047)(代码.assets/1-4的存储过程.png)]

（5）删除存储过程 proc4

#(4)删除存储过程proc4
DROP PROCEDURE proc4;

实验八触发器实验

1.实验目的

掌握数据库触发器的设计和使用方法

2.实验内容和要求

定义 BEFORE 触发器和 AFTER 触发器。能够理解不同类型触发器的作用和执行原理，验证触发器的有效性。

3.实验步骤

（1）AFTER 触发器

①在 Lineitem 表上定义一个 UPDATE 触发器，当修改订单明细（即修改订单明细价格extendeprice、折扣 discount、税率 tax）时，自动修改订单 Orders 的 TotalPrice，以保持数据一致性。


CREATE TRIGGER price_update AFTER UPDATE ON lineitem FOR EACH ROW
BEGIN
		UPDATE orders
		SET totalprice=totalprice+new.extendedprice*(1-new.discount)*(1+new.tax)-old.extendedprice*(1-old.discount)*(1+old.tax)
		WHERE orderkey=new.orderkey;
END;

原数据价格

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-muwQSVea-1641196020047)(代码.assets/1原数据价格.png)]

将tax改为0.25

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-9Mr3O32F-1641196020048)(代码.assets/1将tax改为0.25.png)]

价格自动修改

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0hjOxobI-1641196020048)(代码.assets/1价格自动修改.png)]

②在 Lineitem 表上定义一个 INSERT 触发器，当增加一项订单明细时，自动修改订单Orders 的 TotalPrice，以保持数据的一致性。

CREATE TRIGGER price_insert AFTER INSERT ON lineitem FOR EACH ROW
BEGIN 
	UPDATE orders
	SET totalprice=totalprice+new.extendedprice*(1-new.discount)*(1+new.tax)
	WHERE orders.orderkey=new.orderkey;
END;

#插入一条数据
INSERT INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity,extendedprice,discount,tax)
VALUES(1,42522,13503,3,46,389000,0.25,0.65);

原数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GkG2g5Hz-1641196020048)(代码.assets/2原总价.png)]

插入数据

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q15FIUq6-1641196020048)(代码.assets/2插入数据.png)]

价格改变

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0dbXYmb2-1641196020049)(代码.assets/2价格改变.png)]

③在 Lineitem 表上定义一个 DELETE 触发器，当删除一项订单明细时，自动修改订单Orders 的 TotalPrice，以保持数据一致性

##3在 Lineitem 表上定义一个 DELETE 触发器，当删除一项订单明细时，自动修改订单 Orders 的 TotalPrice，以保持数据一致性。
CREATE TRIGGER price_delete AFTER DELETE ON lineitem FOR EACH ROW
BEGIN
	UPDATE orders
	SET totalprice=totalprice-old.extendedprice*(1-old.discount)*(1+old.tax)
	WHERE orders.orderkey=old.orderkey;
END;

#删除一条数据
DELETE 
FROM lineitem
WHERE orderkey=1 AND linenumber=3;

删除一条数据 将orderkey=1 and linenumber=3删除

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0dZtRAJx-1641196020049)(代码.assets/3删除一条数据.png)]

删除后总价

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YyLxl28X-1641196020049)(代码.assets/3删除后总价.png)]

（2）BEFORE 触发器

①在 Lineitem 表上定义一个 BEFORE UPDATE 触发器，当修改订单明细中的数量（quantity）时，先检查供应表 PartSupp 中的可用数量 availqty 是否足够。


CREATE TRIGGER availqty_update BEFORE UPDATE ON lineitem FOR EACH ROW
BEGIN
	DECLARE availqty_now int;
	
	SELECT availqty INTO availqty_now
	FROM partsupp
	WHERE partkey=new.partkey AND suppkey=new.suppkey;
	
	IF(availqty_now-new.quantity+old.quantity>=0) THEN
		UPDATE partsupp
		SET availqty=availqty-new.quantity+old.quantity
		WHERE partkey=new.partkey AND suppkey=new.suppkey;
	ELSE
		SIGNAL SQLSTATE '45000' set message_text='available quantity is not enough';
	END IF;
END;

#检测程序
## 库存不足
UPDATE lineitem
SET lineitem.quantity=1000
WHERE orderkey=1 AND linenumber=2;
## 库存足够
UPDATE lineitem
SET lineitem.quantity=2
WHERE orderkey=1 AND linenumber=2;

库存不足

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JxgnzOEA-1641196020050)(代码.assets/1库存不足.png)]

库存足够

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-owgwBZub-1641196020050)(代码.assets/1库存足够.png)]

②在 Lineitem 表上定义一个 BEFORE INSERT 触发器，当插入订单明细时，先检查供应表 PartSupp 中的可用数量 availqty 是否足够

#创建触发器
CREATE TRIGGER availqty_insert BEFORE INSERT ON lineitem FOR EACH ROW
BEGIN
	DECLARE availqty_now int;
	
	SELECT availqty INTO availqty_now
	FROM partsupp
	WHERE partkey=new.partkey AND suppkey=new.suppkey;
	
	IF(availqty_now-new.quantity>=0) THEN
		UPDATE partsupp
		SET availqty=availqty-new.quantity
		WHERE partkey=new.partkey AND suppkey=new.suppkey;
	ELSE
		SIGNAL SQLSTATE '45000' set message_text='available quantity is not enough';
	END IF;
END;

插入一条不够的

## 测试
## 插入一条不够的
INSERT INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity,extendedprice,discount,tax)
VALUES(1,42522,13503,3,10000,389000,0.25,0.65)

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-A5ReZ5Iw-1641196020050)(代码.assets/2库存不够.png)]

插入一条足够的

##插入一条库存足够的
INSERT INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity,extendedprice,discount,tax)
VALUES(1,42522,13503,3,1,389000,0.25,0.65);

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-t5gGNEy0-1641196020051)(代码.assets/2库存足够.png)]

③在 Lineitem 表上定义一个 BEFORE DELETE 触发器，当删除订单明细时，该订单明细项订购的数量要归还于对应的零件供应记录。

##触发器
CREATE TRIGGER availqty_delete BEFORE DELETE ON lineitem FOR EACH ROW
BEGIN 
	UPDATE partsupp
	SET availqty=availqty+old.quantity
	WHERE partkey=old.partkey AND suppkey=old.suppkey;
END;

#查询42522,13503的数量
SELECT availqty
FROM partsupp
WHERE partkey=42522 AND suppkey=13503;

#删除一条数据
DELETE
FROM lineitem
WHERE orderkey=1 AND linenumber=3;

#查询42522,13503的数量
SELECT availqty
FROM partsupp
WHERE partkey=42522 AND suppkey=13503;

删除之前的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KmDf2dmL-1641196020051)(代码.assets/3删除之前的.png)]

删除之后的

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rnLYOnmu-1641196020051)(代码.assets/3删除之后的.png)]

②在 Lineitem 表上定义一个 BEFORE INSERT 触发器，当插入订单明细时，先检查供应表 PartSupp 中的可用数量 availqty 是否足够

#创建触发器
CREATE TRIGGER availqty_insert BEFORE INSERT ON lineitem FOR EACH ROW
BEGIN
	DECLARE availqty_now int;
	
	SELECT availqty INTO availqty_now
	FROM partsupp
	WHERE partkey=new.partkey AND suppkey=new.suppkey;
	
	IF(availqty_now-new.quantity>=0) THEN
		UPDATE partsupp
		SET availqty=availqty-new.quantity
		WHERE partkey=new.partkey AND suppkey=new.suppkey;
	ELSE
		SIGNAL SQLSTATE '45000' set message_text='available quantity is not enough';
	END IF;
END;

插入一条不够的

## 测试
## 插入一条不够的
INSERT INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity,extendedprice,discount,tax)
VALUES(1,42522,13503,3,10000,389000,0.25,0.65)

[外链图片转存中…(img-A5ReZ5Iw-1641196020050)]

插入一条足够的

##插入一条库存足够的
INSERT INTO lineitem(orderkey,partkey,suppkey,linenumber,quantity,extendedprice,discount,tax)
VALUES(1,42522,13503,3,1,389000,0.25,0.65);

[外链图片转存中…(img-t5gGNEy0-1641196020051)]

③在 Lineitem 表上定义一个 BEFORE DELETE 触发器，当删除订单明细时，该订单明细项订购的数量要归还于对应的零件供应记录。

##触发器
CREATE TRIGGER availqty_delete BEFORE DELETE ON lineitem FOR EACH ROW
BEGIN 
	UPDATE partsupp
	SET availqty=availqty+old.quantity
	WHERE partkey=old.partkey AND suppkey=old.suppkey;
END;

#查询42522,13503的数量
SELECT availqty
FROM partsupp
WHERE partkey=42522 AND suppkey=13503;

#删除一条数据
DELETE
FROM lineitem
WHERE orderkey=1 AND linenumber=3;

#查询42522,13503的数量
SELECT availqty
FROM partsupp
WHERE partkey=42522 AND suppkey=13503;

删除之前的

[外链图片转存中…(img-KmDf2dmL-1641196020051)]

删除之后的

[外链图片转存中…(img-rnLYOnmu-1641196020051)]

出云coding

关注

3
点赞
踩
38

收藏

觉得还不错? 一键收藏
打赏
3
评论
数据库实验报告（1-8）

文章是从文件导入的，图片转存失败，没有显示，文章些许格式错误可能是因为格式不兼容，文章末尾有实验报告资源连接，需要的可以下载实验一数据库的定义及数据导入1.实验目的理解和掌握数据库DDL语言，能够熟练使用SQL、DDL语句，并能够完成数据导入2.实验内容和要求理解和掌握 SQL DDL 语句的语法，特别是各种参数的具体含义和使用方法；使用 SQL 语句创建、修改和删除基本表；掌握 SQL 语句常见语法错误的调试方法；完成数据导入。3.实验步骤1.创建数据库CREATE DATABASE.
复制链接

扫一扫