实验目的
熟悉数据库的数据更新操作,能够使用 SQL 语句对数据库进行数据的增加、删除、修改操作。
实验内容和要求
针对自建数据库 test 设计单行插入、批量插入、修改和删除数据操作,理解和掌握insert、delete 和 update 语法结构的各个组成成分,结合嵌套 SQL 子查询,分别设计几种不同形式的语句,并调试通过。
实验步骤和实验结果
(1)给供应商表插入一条合理的记录:
INSERT
INTO supplier
VALUES('30847','河北工业大学','天津市北辰区双口镇西平道5340号','40','15212345678','0.00',null);
(2)新建一个顾客表(customernew),插入中国籍顾客信息:
CREATE TABLE customernew
AS SELECT customer.*
FROM customer,nation
WHERE customer.nationkey=nation.nationkey
AND nation.`name`='中国';
(3)新建一个顾客统计表(cusshopping),插入顾客编号、购物总数量、购物总价:
DROP TABLE IF EXISTS cusshopping;
CREATE TABLE cusshopping AS
SELECT orders.custkey,customer.name,quantity,SUM(totalprice) AS sumshop
FROM customer,lineitem,orders
WHERE customer.custkey=orders.custkey
AND lineitem.orderkey=orders.orderkey
GROUP BY orders.custkey
HAVING SUM(totalprice)
ORDER BY orders.custkey;
(4)把“深圳市鸿运贸易有限公司”供应的所有零件的供应价格降价 20%:
更新数据:
UPDATE partsupp
SET supplycost = supplycost * 0.8
WHERE
suppkey IN (
SELECT
suppkey
FROM
supplier
WHERE
NAME = '深圳市鸿运贸易有限公司'
);
查询数据:
SELECT supplier.name,partsupp.*
FROM partsupp,supplier
WHERE partsupp.suppkey=supplier.suppkey
AND supplier.name='深圳市鸿运贸易有限公司';
(5)更新lineitem表中的extendedprice(订单明细价格), extendedprice =零售价格(part.retailprice)* 零件数量(quantity):
UPDATE lineitem,part
SET extendedprice=quantity*retailprice
WHERE part.partkey=lineitem.partkey;
(6)删除顾客“童帅”的所有订单明细(lineitem)记录和订单(orders)记录:
查看童帅信息:
SELECT customer.name,orders.*,lineitem.*
FROM lineitem,orders,customer
WHERE lineitem.orderkey=orders.orderkey
AND customer.name='童帅'
GROUP BY orders.orderkey;
删除童帅信息:
DELETE
FROM lineitem
WHERE orderkey IN(
SELECT orderkey
FROM orders,customer
WHERE orders.custkey=customer.custkey
AND customer.name='童帅'
);
DELETE
FROM orders
WHERE custkey IN(
SELECT custkey
FROM customer
WHERE name='童帅'
);
删除后查看童帅信息:
SELECT customer.name,orders.*,lineitem.*
FROM customer,lineitem,orders
WHERE lineitem.orderkey=orders.orderkey
AND orders.custkey=customer.custkey
AND customer.name='童帅'
GROUP BY orders.orderkey;
(7)删除“澳大利亚”的所有供应商信息:
查看“澳大利亚”供应商信息:
SELECT nation.name,supplier.*,partsupp.*,lineitem.*
FROM supplier,nation,lineitem,partsupp
WHERE supplier.nationkey=nation.name
AND supplier.suppkey=partsupp.suppkey
AND supplier.suppkey=lineitem.suppkey
AND nation.name='澳大利亚';
删除“澳大利亚”供应商信息:
第一遍删除时出现了以下错误:
因为在partsupp和linitem中都引用了supplier中的suppkey(主键)作为外键,因此不能先删除父属性(parent row),只有先删除partsupp和lineitem中的信息后才能删除supplier中的相对应的信息。
再次删除:
DELETE
FROM lineitem
WHERE suppkey IN(
SELECT suppkey
FROM supplier
WHERE nationkey IN(
SELECT nationkey
FROM nation
WHERE `name`='澳大利亚'
)
);
DELETE
FROM partsupp
WHERE suppkey IN(
SELECT suppkey
FROM supplier
WHERE nationkey IN(
SELECT nationkey
FROM nation
WHERE `name`='澳大利亚'
)
);
DELETE
FROM supplier
WHERE nationkey IN(
SELECT nationkey
FROM nation
WHERE name='澳大利亚'
);
查看删除后的“澳大利亚”供应商信息:
“筛选”:
supplier:
lineitem:
partsupp:
(8) 删除顾客表中,没有下过订单的顾客信息。
查看没有下过订单的顾客信息
SELECT
*
FROM
customer
WHERE
custkey NOT IN ( SELECT custkey FROM orders );
删除没有下过订单的客户信息
DELETE
FROM
customer
WHERE
custkey NOT IN ( SELECT custkey FROM orders );
再次查询没有下过订单的客户信息
实验总结
(1)在DELETE操作中需要注意删除表内容的前后顺序,先删除子表中的数据,再删除父表中的对应数据,否则就会出现错误。