MySQL数据库-实验二数据查询

爱喝矿泉水的猛男

已于 2024-02-20 12:04:54 修改

阅读量920

点赞数 7

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

于 2024-02-20 09:51:52 首次发布

本文链接：https://blog.csdn.net/weixin_62858623/article/details/136169900

版权

MySQL数据库实验专栏收录该内容

9 篇文章 1 订阅

订阅专栏

1.实验目的

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

2.实验内容和要求

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

要求每个查询语句都要有结果，并把查询结果截屏作为实验报告的内容，如果查询结果为空，需要自行修改所给表的数据，并把修改过程在实验报告里说明。

3.实验步骤

#1.查询各个地区的编号和名称。

select  regionkey,name from region;
#2.查询各个供应商的编号、名称、地址、电话和供应商所在国家名称。

select           supplier.suppkey,supplier.name,supplier.address,supplier.phone,nation.name
from supplier,nation  ;
#（3）查询 2023 年 9 月份订单金额超过 1 万元的订单编号、顾客姓名、顾客所在国家名称和订单日期

#更新时间2023

UPDATE orders

SET orderdate = CONCAT(2023, '-', LPAD(MONTH(orderdate), 2, '0'), '-', LPAD(DAY(orderdate), 2, '0'));

#查看表

select  * from orders;

select ord.orderkey,cus.name,nat.name,ord.orderdate

from orders ord,customer cus,nation nat

where ord.orderdate like '2023-09%' && ord.totalprice>10000 && ord.custkey=cus.custkey && cus.nationkey=nat.nationkey;
#（4）统计顾客在 2023 年的订购总金额，显示顾客编号、顾客姓名、订购总金额。

select cus.custkey,cus.name,sum(ord.totalprice)

from customer cus,orders ord

where ord.custkey=cus.custkey && ord.orderdate like '2023-%'

group by cus.custkey

order by cus.custkey;
 

#（5）查询零件的销售情况，统计在 2023 年（按照下订单时间）的销售总金额大于 100万的零件信息，显示零件编号、零件名称、品牌、制造厂、销售总金额。

select p.partkey,p.name,p.brand,p.mfgr,sum(p.retailprice*li.quantity) as sal_price

from part p,lineitem li,orders ord

where  ord.orderdate like '2023%' && li.partkey = p.partkey && li.orderkey = ord.orderkey

group by  p.partkey

having sal_price>1000000

order by ord.orderdate;
#（6）查询每个供应商供应的产品销售情况，统计在 2023 年（按照下订单时间）的销售总金额大于 100 万的供应商信息，显示供应商编号、供应商名称、销售总金额。

select s.suppkey,s.name,sum(ord.totalprice) as sale_total

from supplier s,orders ord,lineitem li

where ord.orderkey = li.orderkey && li.suppkey = s.suppkey && ord.orderdate like '2023%'

group by s.suppkey

having sale_total >10000000

order by ord.orderdate; 
本次查询没有结果，于是我对原来数据进行了如下更改：

将orders表中的totalprice每一元素值进行翻倍处理，然后查询结果如下：

update orders

set totalprice =  totalprice2;
#（7）查询订单平均金额超过 5 万元的顾客编号、姓名、所在国家名称和订单平均金额。

select  c.custkey,c.name,n.name,avg(o.totalprice) as avg_price

from customer  c, orders o,nation n

where c.custkey = o.custkey && c.nationkey = n.nationkey

group by c.custkey

having avg_price>50000

order by c.custkey;






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

select sup.suppkey,sup.name,sup.address

from supplier sup,supplier supx

where supx.name='金石印刷有限公司' && sup.nationkey=supx.nationkey;
#（9）查询供应价格小于零售价格的零件编号、零件名称、制造厂、品牌、零售价格、供应商名称和供应价格。

select par.partkey,par.name,par.mfgr,par.brand,par.retailprice,sup.name,parx.supplycost

from partsupp parx,part par,supplier sup

where par.partkey=parx.partkey && parx.supplycost<par.retailprice && sup.suppkey=parx.suppkey

order by par.partkey ;
#（10）查询顾客“曹玉书”订购的订单编号、订单金额及其订购的零件编号、数量和订单明细价格。

select lin.orderkey,ord.totalprice,lin.partkey,lin.quantity,lin.extendedprice

from lineitem lin,orders ord,customer cus

where cus.name='曹玉书' && cus.custkey=ord.custkey && lin.orderkey=ord.orderkey

order by lin.orderkey;
#（11）查询订购了“南昌矿山机械厂”制造的“缝盘机”的顾客编号、姓名。

SELECT cus.custkey,cus.name,par.name,par.mfgr

 FROM customer cus,part par

  WHERE  par.mfgr='南昌矿山机械厂' && par.name='缝盘机' &&

   cus.custkey IN

   (SELECT ord.custkey

     FROM orders ord,lineitem lin

      WHERE par.partkey=lin.partkey && ord.orderkey=lin.orderkey)

      ORDER BY cus.custkey;
#（12）查询订单平均金额超过 1 万元的顾客中的中国籍顾客的顾客编号、姓名。

select cus.custkey,cus.name,AVG(ord.totalprice) AS avg_price,SUM(ord.totalprice) AS sum_price

from customer cus,orders ord,nation nat

where ord.custkey=cus.custkey &&  nat.name='中国' && cus.nationkey=nat.nationkey

group by cus.custkey

having avg_price>20000

order by avg_price;
 

#（13）查询顾客“刘玉龙”和“钱岚”都订购过的全部零件的信息。

SELECT * FROM part par

 WHERE

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='钱岚'

             )

        )

   )

and

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='刘玉龙'

             )

        )

   )

order by par.partkey;

#查询钱岚订购的零件信息

SELECT * FROM part par

 WHERE

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='钱岚'

             )

        )

   )

order by par.partkey;

#查询刘玉龙订购的零件信息

 SELECT * FROM part par

 WHERE

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='刘玉龙'

             )

        )

   )

  order by par.partkey;
由于查询结果是空，所以为了验证两个人是否真的没有共同的零件信息，我对两个人进行了分别查询，经过结果发现确实没有交集：

由于查询没有结果，于是我在钱岚的lineitem内插入了刘玉龙的partkey为26360的信息，然后再次进行查询。

insert lineitem(orderkey, partkey, suppkey, linenumber, quantity, extendedprice, discount, tax) 

values (6084,26360,2158,3,11,0.00,0.06,0.98);
最终查询结果如下:

#（14）查询顾客“刘玉龙”或“钱岚”订购的全部零件的信息。

SELECT * FROM part par

 WHERE

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='钱岚' OR cus.name='刘玉龙'

             )

        )

   )

   order by par.partkey ;
#（15）查询顾客“刘玉龙”订购过而“钱岚”没订购过的零件的信息。

SELECT * FROM part par

 WHERE

 par.partkey IN

 (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='刘玉龙'

             )

        )

   )

   AND

   par.partkey NOT IN

    (SELECT lin.partkey

    FROM lineitem lin

     WHERE lin.orderkey IN

      (SELECT ord.orderkey

        FROM orders ord

         WHERE ord.custkey IN

          (SELECT cus.custkey

            FROM customer cus

             WHERE cus.name='钱岚'

             )

        )

   )

   order by par.partkey ;