MySQL实验-实验三 数据更新实验

一、实验目的

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

二、实验内容和要求

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

三、实验步骤

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

   insert into supplier values (0,'XXX','天津市北辰区西平道5340号',40,151******55,0.00,NULL);

   

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

   create table customernew as
   select *from customer 
   where nationkey in (select nationkey from nation where nation.name = '中国');
   

   

3. 新建一个顾客统计表（cusshopping），把每个顾客编号、购物总数量、购物总价插入到顾客统计表（cusshopping）。

   create table cussshopping
   as
   select cus.custkey,sum(lin.quantity) as '购物总数量',ord.totalprice as '购物总价'
   from orders as ord ,customer as cus,lineitem as lin
   where ord.custkey = cus.custkey and lin.orderkey = ord.orderkey
   group by lin.orderkey;
   

   

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

   update partsupp
   set supplycost=supplycost*0.8
   where suppkey in (select suppkey from supplier where name = '深圳市鸿运贸易有限公司' );
   

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

   select  extendedprice from lineitem;
   
   update lineitem,part
   set extendedprice = part.retailprice * lineitem.quantity
   where part.partkey = lineitem.partkey;
   
   select extendedprice from lineitem;

   

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

   select linenumber from lineitem
   where  orderkey in
          (select orderkey from orders where custkey in
         (select custkey from customer where customer.name = '童帅'));
   
   select distinct ord.orderkey
   from lineitem lin ,orders ord , customer cus
   where lin.orderkey = ord.orderkey and cus.custkey = ord.custkey and cus.name = '童帅';
   
   delete  from lineitem
   where  orderkey
   in (select orderkey from orders where custkey
   in (select custkey from customer where name = '童帅')  );
   
   delete  from orders
   where custkey in (select custkey from customer where name = '童帅' );
   

   查看“童帅”的所有订单明细记录和订单：                                                    查看删除之后的记录，发现结果为空，说明已经被删除了:

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

   select sup.* from nation na, supplier sup
   where na.nationkey = sup.nationkey and na.nationkey = 11;
   
   SET FOREIGN_KEY_CHECKS=0;
   
   delete  from supplier
   where nationkey in
   (select  nationkey from nation where nation.nationkey = 11);
   
   SET FOREIGN_KEY_CHECKS=1;
   
   select sup.* from nation na, supplier sup
   where na.nationkey = sup.nationkey and na.nationkey = 11;
   

   由于partsupp是supplier的外键，所以需要禁用外键约束，才可以删除操作，完成后必须再次将外键约束打开。查询“澳大利亚”的所有供应商信息如上，删除操作以后结果如下，删除成功：

8. 删除顾客表中，没有下过订单的顾客信息。 首先查询发现没有没下过单的订单客户信息，于是我要插入一条没有下过单的客户订单信息。但是由于外键约束，无法插入，所以仍然需要提前把外键约束禁用完成后再打开。具体操作如下：

   select cus.* from customer cus , orders ord
   where cus.custkey = ord.custkey and ord.totalprice = 0;
   
   SET FOREIGN_KEY_CHECKS=0;
   
   insert  into orders(orderkey, custkey, totalprice, orderdate)
   values(0,0000,0,'2023-10-09');
   
   insert into customer (custkey, name, nationkey)
   values (0,'程大爷',40);
   
   select cus.* from customer cus , orders ord
   where cus.custkey = ord.custkey and ord.totalprice = 0;
   
   delete  from customer
   where custkey in (select  custkey from orders where orders.totalprice = 0);
   
   select cus.* from customer cus , orders ord
   where cus.custkey = ord.custkey and ord.totalprice = 0;
   
   SET FOREIGN_KEY_CHECKS=1;
   

   首先查询没有下过订单的客户，发现是空的：于是插入一个没有下过单的用户和订单：最终再次查询没有下过订单的客户，结果使我插入的信息，没有问题：