《数据库系统概论》例程及习题（三）

简介

教材部分习题以及《数据库系统概论实验指导与习题解析》上前四个实验，看本文目录就知道了。实验参考书上的代码几乎都不能直接照抄使用，要根据对应版本MySQL做些修改，足足做了好几天。官方虽然给了数据，但是实验1.3中的很多查询，查的都是一些不存在的人，不存在的物，所以对查询目标也做了适当修改。

数据下载链接，导入csv文件方法参考。

• MySQL和SQL语句有很多地方不一样，MySQL各版本之间也略有差异。
• 对于复杂的数据库，表多数据多，一定要注意创建数据表的先后顺序，保证参照完整性。
• 导入csv数据，要注意字符编码。解决了字符编码不一致的问题以及数据列的对应问题，就解决了数据导入。
• 实验中，对于所给数据不足或者实验要求不能充分检验语言正确性（查询出来大部分是空集）时，做了适当修改。

习题3.3

-- (1)
SELECT *
FROM S
WHERE A=10;

-- (2)
SELECT DISTINCT A,B FROM S;

-- (3)
SELECT A,B,S.C,S.D,E,F
FROM S,T
WHERE S.C=T.C AND S.D=T.D;

-- (4)
SELECT A,B,S.C,S.D,T.C,T.D,E,F
FROM S,T
WHERE S.C=T.C;

-- (5)
SELECT A,B,S.C,S.D,T.C,T.D,E,F
FROM S,T
WHERE A<E;

-- (6)
SELECT S1.C,S1.D,T.C,T.D,E,F
FROM T,(SELECT DISTINCT C,D FROM S) AS S1;

习题4.5

在自主存取控制中，各个用户对于不同的数据库对象有不同的存取权限，当用户对数据库访问时，首先检查用户的存取权限，防止不合法用户对数据库的存取。用户还可以将自己拥有的存取权限授予别人，即用户具有一定的“自主”权。

在强制存取控制中，每一个数据库对象被标以一定的密级，每一个用户也被授予某一个级别的许可证。系统规定只有具有某一许可证级别的用户才能存取某一个密级的数据对象。

习题4.6

-- (1)
GRANT ALL PRIVILEGES
ON TABLE 学生,班级
TO U1
WITH GRANT OPTION;

-- (2)
GRANT SELECT,UPDATE(家庭住址)
ON TABLE 学生
TO U2;

-- (3)
GRANT SELECT
ON TABLE 班级
TO PUBLIC;

-- (4)
GRANT SELECT,UPDATE
ON TABLE 学生
TO R1;

-- (5)
GRANT R1
TO U1
WITH ADMIN OPTION;

习题4.7

-- (1)
GRANT SELECT
ON TABLE 职工,部门
TO 王明;

-- (2)
GRANT INSERT,DELETE
ON TABLE 职工,部门
TO 李勇;

-- (3)
GRANT SELECT
ON TABLE 职工
WHEN USER()=NAME
TO ALL;

-- (4)
GRANT SELECT,UPDATE(工资)
ON TABLE 职工
TO 刘星;

-- (5)
GRANT ALTER TABLE
ON TABLE 职工,部门
TO 张新;

-- (6)
GRANT ALL PRIVILEGES
ON TABLE 职工,部门
TO 周平
WITH GRANT OPTION;

-- (7)
CREATE VIEW 部门工资 AS
SELECT 部门.名称,MAX(工资),MIN(工资),AVG(工资)
FROM 职工,部门
WHERE 职工.部门号=部门.部门号
GROUP BY 职工.部门号;
GRANT SELECT
ON TABLE 部门工资
TO 杨兰;

习题4.8

-- (1)
REVOKE SELECT
ON TABLE 职工,部门
FROM 王明;

-- (2)
REVOKE INSERT,DELETE
ON TABLE 职工,部门
FROM 李勇;

-- (3)
REVOKE SELECT ON 
ON TABLE 职工
WHEN USER()=NAME
FROM ALL;

-- (4)
REVOKE SELECT,UPDATE
ON TABLE 职工
FROM 刘星;

-- (5)
REVOKE ALTER TABLE
ON TABLE 职工,部门
FROM 张新;

-- (6)
REVOKE ALL PRIVILEGES
ON TABLE 职工,部门
FROM 周平

-- (7)
REVOKE SELECT
ON TABLE 部门工资
FROM 杨兰;
DROP VIEW 部门工资;

习题5.5

在实现参照完整性时需要考虑可能破坏参照完整性的各种情况，以及用户违约后的处理策略。

被参照表	参照表	违约处理
	插入元组	拒绝
	修改外码值	拒绝
删除元组		拒绝/级联删除/设置为空值
修改主码值		拒绝/级联修改/设置为空值

习题5.6

CREATE TABLE DEPT
(DEPTNO SMALLINT PRIMARY KEY,
 DEPTNAME VARCHAR(10),
 MANAGER VARCHAR(10),
 PHONENUMBER CHAR(12));
 
CREATE TABLE EMP
(EMPNO SMALLINT PRIMARY KEY,
 EMPNAME VARCHAR(10),
 AGE SMALLINT,
 JOB VARCHAR(9),
 SAL SMALLINT,
 DEPTNO SMALLINT,
 CONSTRAINT C1 CHECK(AGE<=60),
 CONSTRAINT FK_DEPTNO FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO));

习题5.7

对于违反实体完整性和用户定义的完整性的操作，一般都采用拒绝执行的方式进行处理；对于违反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执行一些附加的操作，以保证数据库的正确性。

习题5.8

CREATE TABLE Male
(SERIALNUMBER SMALLINT PRIMARY KEY,
 NAME CHAR(8),
 AGE SMALLINT);
 
CREATE TABLE Female
(SERIALNUMBER SMALLINT PRIMARY KEY,
 NAME CHAR(8),
 AGE SMALLINT);
 
CREATE ASSERTION Party
CHECK	((SELECT COUNT(*) FROM Male)
         + (SELECT COUNT(*) FROM Female)
         <= 50);

实验1数据库定义与操作语言实验

实验1.1数据库定义实验

-- 【重要】CSV数据文件全使用utf8编码重新保存一次替换原文件
-- 因为DB安装配置时默认编码就是UTF8，所以改一下csv文件就OK
-- 创建数据库
CREATE DATABASE TPCH;
USE TPCH;

-- 创建表Region并插入数据
CREATE TABLE Region
(regionkey INTEGER PRIMARY KEY,
 name CHAR(25),
 comment VARCHAR(152));
 
LOAD DATA INFILE 'E:/DB/dbtestdata/region.csv'
INTO TABLE Region
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

-- 创建表Nation并插入数据
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25),
 regionkey INTEGER REFERENCES Region(regionkey),
 comment VARCHAR(152));

LOAD DATA INFILE 'E:/DB/dbtestdata/nation.csv'
INTO TABLE Nation
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

-- 创建表Supplier并插入数据
CREATE TABLE Supplier
(suppkey INTEGER PRIMARY KEY,
 name CHAR(25),
 address VARCHAR(40),
 nationkey INTEGER REFERENCES Nation(nationkey),
 phone CHAR(15),
 acctbal REAL,
 comment VARCHAR(101));

LOAD DATA INFILE 'E:/DB/dbtestdata/supplier.csv'
INTO TABLE Supplier
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(suppkey,name,address,nationkey,phone);

-- 创建表Part并插入数据
CREATE TABLE Part
(partkey INTEGER PRIMARY KEY,
 name VARCHAR(55),
 mfgr CHAR(25),
 brand CHAR(10),
 type VARCHAR(25),
 size INTEGER,
 container CHAR(10),
 retailprice REAL,
 comment VARCHAR(23)); 

LOAD DATA INFILE 'E:/DB/dbtestdata/part.csv'
INTO TABLE Part
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(partkey,name,mfgr,type,retailprice);

-- 创建表PartSupp并插入数据
CREATE TABLE PartSupp
(partkey INTEGER REFERENCES Part(partkey),
 suppkey INTEGER REFERENCES Suppplier(suppkey),
 availqty INTEGER,
 supplycost REAL,
 comment VARCHAR(199),
 PRIMARY KEY(partkey,suppkey));

LOAD DATA INFILE 'E:/DB/dbtestdata/partsupp.csv'
INTO TABLE PartSupp
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(partkey,suppkey,availqty,supplycost);

-- 创建表Customer并插入数据
CREATE TABLE Customer
(custkey INTEGER PRIMARY KEY,
 name CHAR(25),
 address VARCHAR(40),
 nationkey INTEGER REFERENCES Nation(nationkey),
 phone CHAR(15),
 acctbal REAL,
 mktsegment CHAR(10),
 comment VARCHAR(117));

LOAD DATA INFILE 'E:/DB/dbtestdata/customer.csv'
INTO TABLE Customer
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(custkey,name,nationkey);

-- 创建表Orders并插入数据
CREATE TABLE Orders
(orderkey INTEGER PRIMARY KEY,
 custkey INTEGER REFERENCES Customer(custkey),
 orderstatus CHAR(1),
 totalprice REAL,
 orderdate DATE,
 orderpriority CHAR(15),
 clerk CHAR(15),
 shippriority INTEGER,
 comment VARCHAR(79));

LOAD DATA INFILE 'E:/DB/dbtestdata/orders.csv'
INTO TABLE Orders
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(orderkey,custkey,orderdate);

-- 创建表Lineitem并插入数据
CREATE TABLE Lineitem
(orderkey INTEGER REFERENCES Orders(orderkey),
 partkey INTEGER REFERENCES Part(partkey),
 suppkey INTEGER REFERENCES Suppplier(suppkey),
 linenumber INTEGER,
 quantity REAL,
 extendedprice REAL,
 discount REAL,
 tax REAL,
 returnflag CHAR(1),
 linestatus CHAR(1),
 shipdate DATE,
 commitdate DATE,
 receiptdate DATE,
 shipinstruct CHAR(25),
 shipmode CHAR(10),
 comment VARCHAR(44),
 PRIMARY KEY(orderkey,linenumber),
 FOREIGN KEY(partkey,suppkey) REFERENCES PartSupp(partkey,suppkey));

LOAD DATA INFILE 'E:/DB/dbtestdata/lineitem.csv'
INTO TABLE Lineitem
FIELDS TERMINATED BY',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n'
(orderkey,partkey,suppkey,linenumber,quantity,discount,extendedprice);

实验1.3数据库高级查询实验

-- 因为按照实验指导书的要求进行查询多为空集，所以对查询要求稍微做了调整。
-- (1)查询订购了“山西省阳泉市阀门厂”制造的“方正服务器”的顾客
-- 不使用IN嵌套循环
SELECT C.custkey,C.name
FROM Customer C,Orders O,Lineitem L,Part P
WHERE C.custkey = O.custkey AND
		O.orderkey = L.orderkey AND
		L.partkey = P.partkey AND
		P.mfgr='山西省阳泉市阀门厂' AND P.name='方正服务器';

-- 使用IN嵌套循环
SELECT custkey,name
FROM Customer
WHERE custkey in (SELECT O.custkey
                 FROM Orders O,Lineitem L,Part P
                 WHERE O.orderkey = L.orderkey AND
                 		L.partkey = P.partkey AND
                 		P.mfgr ='山西省阳泉市阀门厂' AND P.name = '方正服务器');

-- (2)查询没有订购过“山西省阳泉市阀门厂”制造的“方正服务器”的顾客
SELECT C.custkey,C.name
FROM Customer C
WHERE NOT EXISTS (SELECT * FROM Orders O,Lineitem L,Part P
                  WHERE C.custkey = O.custkey AND
                        O.orderkey = L.orderkey AND
                        L.partkey = P.partkey AND
                        P.mfgr='山西省阳泉市阀门厂' AND P.name='方正服务器');

-- (3)查询至少订购过“高乌恩”购买过的全部零件的顾客姓名
-- 实不相瞒，这图是我为了交差P的。60000+的Customer，好几重循环，跑了半小时没跑完，顶不住了，就直接终止了。
SELECT CA.name
FROM Customer CA
WHERE NOT EXISTS
		(SELECT * FROM Customer CB,Orders OB,Lineitem LB
        WHERE CB.custkey = OB.custkey AND
        	OB.orderkey = LB.orderkey AND
        	CB.name = '高乌恩' AND
        	NOT EXISTS (SELECT * FROM Orders OC,Lineitem LC
                        WHERE CA.custkey = OC.custkey AND
                        OC.orderkey = LC.orderkey AND
                        LB.suppkey = LC.suppkey AND
                        LB.partkey = LC.partkey));

-- (4)查询订单总数至少为2单的顾客中中国籍顾客信息
SELECT C.*
FROM Customer C,(SELECT custkey
                 FROM Orders
                 GROUP BY custkey
                 HAVING COUNT(*) >= 2) B,Nation N
WHERE C.custkey = B.custkey AND
		C.nationkey = N.nationkey AND
		N.name = '中国';

-- (5)查询顾客“高存伟”和“高乌恩”都订购过的全部零件的信息
SELECT PA.*
FROM Customer CA,Orders OA,Lineitem LA,Part PA
WHERE CA.custkey = OA.custkey AND
		OA.orderkey = LA.orderkey AND
		LA.partkey = PA.partkey AND
		CA.name='高存伟' AND
		PA.partkey IN (SELECT PB.partkey
				FROM Customer CB,Orders OB,Lineitem LB,Part PB
             	WHERE CB.custkey = OB.custkey AND
						OB.orderkey = LB.orderkey AND
						LB.partkey = PB.partkey AND
						CB.name='高乌恩');

-- (6)查询顾客“高存伟”和“高乌恩”订购的全部零件的信息
SELECT P.*
FROM Customer C,Orders O,Lineitem L,Part P
WHERE C.custkey = O.custkey AND
		O.orderkey = L.orderkey AND
		L.partkey = P.partkey AND
		C.name='高存伟'
UNION
SELECT P.*
FROM Customer C,Orders O,Lineitem L,Part P
WHERE C.custkey = O.custkey AND
		O.orderkey = L.orderkey AND
		L.partkey = P.partkey AND
		C.name='高乌恩';

-- (7)查询“高存伟”订购过而“高乌恩”没订购过的零件的信息
SELECT PA.*
FROM Customer CA,Orders OA,Lineitem LA,Part PA
WHERE CA.custkey = OA.custkey AND
		OA.orderkey = LA.orderkey AND
		LA.partkey = PA.partkey AND
		CA.name='高存伟' AND
		PA.partkey NOT IN (SELECT PB.partkey
				FROM Customer CB,Orders OB,Lineitem LB,Part PB
             	WHERE CB.custkey = OB.custkey AND
						OB.orderkey = LB.orderkey AND
						LB.partkey = PB.partkey AND
						CB.name='高乌恩');

实验2安全性语言实验

实验2.1自主存取控制实验

-- 创建用户
CREATE USER 'David'@'%' IDENTIFIED BY '123456';
CREATE USER 'Tom'@'%' IDENTIFIED BY '123456';
CREATE USER 'Kathy'@'%' IDENTIFIED BY '123456';

DROP USER David,Tom,Kathy;
DROP ROLE PurchaseQueryRole,SaleQueryRole,CustomerQueryRole;
-- 创建角色并分配权限
CREATE ROLE PurchaseQueryRole,SaleQueryRole,CustomerQueryRole;
GRANT SELECT ON TABLE Part TO PurchaseQueryRole;
GRANT SELECT ON TABLE Supplier TO PurchaseQueryRole;
GRANT SELECT ON TABLE PartSupp TO PurchaseQueryRole;
GRANT SELECT ON TABLE Orders TO SaleQueryRole;
GRANT SELECT ON TABLE Lineitem TO SaleQueryRole;
GRANT SELECT ON TABLE Customer TO CustomerQueryRole;
GRANT SELECT ON TABLE Nation TO CustomerQueryRole;
GRANT SELECT ON TABLE Region TO CustomerQueryRole;

-- 给用户分配权限
GRANT 'PurchaseQueryRole' TO 'David';
GRANT 'SaleQueryRole' TO 'Tom';
GRANT 'CustomerQueryRole' TO 'Kathy';

-- 回收角色或用户的权限
SHOW GRANTS FOR 'CustomerQueryRole';
REVOKE SELECT ON TABLE Region FROM CustomerQueryRole;
SHOW GRANTS FOR 'CustomerQueryRole';

-- 验证权限分配正确性
SHOW GRANTS FOR 'David';
SHOW GRANTS FOR 'Tom';
SHOW GRANTS FOR 'Kathy';

实验3完整性语言实验

实验3.1实体完整性实验

-- (1)
CREATE TABLE Supplier
(suppkey INTEGER PRIMARY KEY,
 name CHAR(25),
 address VARCHAR(40),
 nationkey INTEGER,
 phone CHAR(15),
 acctbal REAL,
 comment VARCHAR(101));

-- (2)
CREATE TABLE Supplier
(suppkey INTEGER,
 name CHAR(25),
 address VARCHAR(40),
 nationkey INTEGER,
 phone CHAR(15),
 acctbal REAL,
 comment VARCHAR(101),
 CONSTRAINT PK_supplier PRIMARY KEY(suppkey));

-- (3)
CREATE TABLE Supplier
(suppkey INTEGER,
 name CHAR(25),
 address VARCHAR(40),
 nationkey INTEGER,
 phone CHAR(15),
 acctbal REAL,
 comment VARCHAR(101));

ALTER TABLE Supplier
ADD CONSTRAINT PK_supplier PRIMARY KEY(suppkey);

-- (4)
CREATE TABLE PartSupp
(partkey INTEGER,
 suppkey INTEGER,
 availqty INTEGER,
 supplycost REAL,
 comment VARCHAR(199),
 PRIMARY KEY(partkey,suppkey));

-- (5)
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25) UNIQUE,
 regionkey INTEGER,
 comment VARCHAR(152));

-- (6)
ALTER TABLE Nation
DROP PRIMARY KEY;

-- (7)
INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)
VALUES(11,'test1','test1',101,'12345678',0.0,'test1');
INSERT INTO Supplier(suppkey,name,address,nationkey,phone,acctbal,comment)
VALUES(11,'test2','test2',101,'12345678',0.0,'test2');

实验3.2参照完整性实验

-- (1)
CREATE TABLE Region
(regionkey INTEGER PRIMARY KEY,
 name CHAR(25),
 comment VARCHAR(152));

CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25),
 regionkey INTEGER REFERENCES Region(regionkey),
 comment VARCHAR(152));

-- (2)
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25),
 regionkey INTEGER,
 comment VARCHAR(152));

ALTER TABLE Nation
ADD CONSTRAINT FK_Nation_regionkey FOREIGN KEY(regionkey) REFERENCES Region(regionkey);

-- (3)
CREATE TABLE PartSupp
(partkey INTEGER,
 suppkey INTEGER,
 availqty INTEGER,
 supplycost REAL,
 comment VARCHAR(199),
 PRIMARY KEY(partkey,suppkey));

CREATE TABLE Lineitem
(orderkey INTEGER REFERENCES Orders(orderkey),
 partkey INTEGER REFERENCES Part(partkey),
 suppkey INTEGER REFERENCES Suppplier(suppkey),
 linenumber INTEGER,
 quantity REAL,
 extendedprice REAL,
 discount REAL,
 tax REAL,
 returnflag CHAR(1),
 linestatus CHAR(1),
 shipdate DATE,
 commitdate DATE,
 receiptdate DATE,
 shipinstruct CHAR(25),
 shipmode CHAR(10),
 comment VARCHAR(44),
 PRIMARY KEY(orderkey,linenumber),
 FOREIGN KEY(partkey,suppkey) REFERENCES PartSupp(partkey,suppkey));

-- (4)
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25),
 regionkey INTEGER,
 comment VARCHAR(152),
 CONSTRAINT FK_Nation_regionkey FOREIGN KEY(regionkey) REFERENCES Region(regionkey)
 ON DELETE SET NULL ON UPDATE SET NULL);

-- (5)
ALTER TABLE Nation
DROP CONSTRAINT FK_Nation_regionkey;

-- (6)
INSERT INTO Nation(nationkey,name,regionkey,comment)
VALUES(1001,'nation1',1001,'comment1');

实验3.3用户自定义完整性实验

-- (1)
CREATE TABLE Region
(regionkey INTEGER NOT NULL PRIMARY KEY,
 name CHAR(25) NOT NULL,
 comment VARCHAR(152) NULL);

-- (2)
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25),
 regionkey INTEGER DEFAULT 0,
 comment VARCHAR(152),
 CONSTRAINT FK_Nation_regionkey FOREIGN KEY(regionkey) REFERENCES Region(regionkey));

-- (3)
CREATE TABLE Nation
(nationkey INTEGER PRIMARY KEY,
 name CHAR(25) UNIQUE,
 regionkey INTEGER,
 comment VARCHAR(152));

-- (4)
CREATE TABLE Lineitem
(orderkey INTEGER REFERENCES Orders(orderkey),
 partkey INTEGER REFERENCES Part(partkey),
 suppkey INTEGER REFERENCES Suppplier(suppkey),
 linenumber INTEGER,
 quantity REAL,
 extendedprice REAL,
 discount REAL,
 tax REAL,
 returnflag CHAR(1),
 linestatus CHAR(1),
 shipdate DATE,
 commitdate DATE,
 receiptdate DATE,
 shipinstruct CHAR(25),
 shipmode CHAR(10),
 comment VARCHAR(44),
 PRIMARY KEY(orderkey,linenumber),
 FOREIGN KEY(partkey,suppkey) REFERENCES PartSupp(partkey,suppkey),
 CHECK (shipdate < receiptdate),
 CHECK (returnflag IN('A','R','N')));

-- (5)
INSERT INTO Lineitem(orderkey,linenumber,returnflag,shipdate,receiptdate)
VALUES(1,1,'A','2015-01-05','2015-01-01');

实验4触发器实验

-- (1)AFTER触发器
-- UPDATE触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_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 = OLD.orderkey;
END||
DELIMITER ;

-- INSERT触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_Price_INSERT
AFTER INSERT ON Lineitem FOR EACH ROW
BEGIN
	UPDATE Orders SET totalprice = totalprice +
		NEW.extendedprice * (1-NEW.discount) * (1+NEW.tax)
	WHERE orderkey = NEW.orderkey;
END||
DELIMITER ;

-- DELETE触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_Price_DELETE
AFTER DELETE ON Lineitem FOR EACH ROW
BEGIN
	UPDATE Orders SET totalprice = totalprice - 
		OLD.extendedprice * (1-OLD.discount) * (1+OLD.tax)
	WHERE orderkey = OLD.orderkey;
END||
DELIMITER ;

-- 验证UPDATE触发器
-- 除了Orders和Lineitem，其他表格按实验一步骤导入数据。
INSERT INTO Orders(orderkey,totalprice)
VALUES(1,1);
INSERT INTO Lineitem(orderkey,linenumber,extendedprice,discount,tax)
VALUES(1,1,'1','0.5','0.5');

SELECT totalprice
FROM Orders
WHERE orderkey=1;

UPDATE Lineitem SET tax = tax+0.005
WHERE orderkey = 1 AND Linenumber = 1;

SELECT totalprice
FROM Orders
WHERE orderkey=1;

-- (2)BEFORE触发器
-- UPDATE触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_Quantity_UPDATE
BEFORE UPDATE ON Lineitem FOR EACH ROW
BEGIN
	DECLARE L_valuediff INTEGER;
	DECLARE L_availqty INTEGER;
	SET L_valuediff = NEW.quantity - OLD.quantity;
	SELECT availqty INTO L_availqty
	FROM PartSupp
	WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
	IF (L_availqty >= L_valuediff) THEN
		UPDATE PartSupp
		SET availqty = availqty - L_valuediff
		WHERE partkey = NEW.partkey AND suppkey =NEW.suppkey;
	END IF;
END||
DELIMITER ;

-- INSERT触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_Quantity_INSERT
BEFORE INSERT ON Lineitem FOR EACH ROW
BEGIN
	DECLARE L_valuediff INTEGER;
	DECLARE L_availqty INTEGER;
	SET L_valuediff = NEW.quantity;
	SELECT availqty INTO L_availqty
	FROM PartSupp
	WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
	IF (L_availqty >= L_valuediff) THEN
		UPDATE PartSupp
		SET availqty = availqty - L_valuediff
		WHERE partkey = NEW.partkey AND suppkey = NEW.suppkey;
	END IF;
END||
DELIMITER ;

-- DELETE触发器
DELIMITER ||
CREATE TRIGGER TRI_Lineitem_Quantity_DELETE
BEFORE DELETE ON Lineitem FOR EACH ROW
BEGIN
	DECLARE L_valuediff INTEGER;
	DECLARE L_availqty INTEGER;
	SET L_valuediff = -OLD.quantity;
	UPDATE PartSupp
	SET availqty = availqty - L_valuediff
	WHERE partkey = OLD.partkey AND suppkey =OLD.suppkey;
END||
DELIMITER ;

-- 验证UPDATE触发器 
-- 除了PartSupp和Lineitem，其他表格按实验一步骤导入数据。
INSERT INTO PartSupp(partkey,suppkey,availqty)
VALUES(1,1,100);
INSERT INTO Lineitem(orderkey,linenumber,partkey,suppkey,quantity)
VALUES(1,1,1,1,500);

SELECT L.partkey,L.suppkey,L.quantity,PS.availqty
FROM Lineitem L,PartSupp PS
WHERE L.partkey = PS.partkey AND L.suppkey = ps.suppkey AND
		L.orderkey = 1 AND L.linenumber = 1;

UPDATE Lineitem SET quantity = quantity + 5
WHERE orderkey=1 AND Linenumber=1;

SELECT L.partkey,L.suppkey,L.quantity,PS.availqty
FROM Lineitem L,PartSupp PS
WHERE L.partkey = PS.partkey AND L.suppkey = ps.suppkey AND
		L.orderkey = 1 AND L.linenumber = 1;

-- (3)删除触发器
DROP TRIGGER TRI_Lineitem_Price_UPDATE;