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

数据库实验二：数据查询数据库结构1，查询各个地区的编号和名称。2、查询各个供应商的编号、名称、地址、电话和供应商所在国家名称。查询2021 年9 月份每个订单金额超过10000 元的订单编号、顾客姓名、顾客所在国家名称、和订单日期。数据库结构1，查询各个地区的编号和名称。SELECT regionkey,name FROM region;2、查询各个供应商的编号、名称、地址、电话和供应商所在国家名

ALAN_CF

10156人浏览 · 2021-10-10 13:47:03

ALAN_CF · 2021-10-10 13:47:03 发布

数据库实验二：数据查询

数据库结构

在这里插入图片描述

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

SELECT regionkey,name FROM region;

在这里插入图片描述

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

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

在这里插入图片描述
wait 几分钟

原数据CSV数据表格如下：除去表头一行，则数据共30810行，与实验最终跑出来的数据数量吻合

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

SELECT ord.orderkey,cus.name,nat.name,ord.orderdate
 FROM orders ord,customer cus,nation nat
  WHERE ord.orderdate between '2021-09-01' and '2021-09-30'
   && ord.totalprice>10000 && ord.custkey=cus.custkey && cus.nationkey=nat.nationkey;

SELECT ord.orderkey,cus.name,nat.name,ord.orderdate
 FROM orders ord,customer cus,nation nat
  WHERE ord.orderdate LIKE '2021-09%' 
   && ord.totalprice>10000 && ord.custkey=cus.custkey && cus.nationkey=nat.nationkey;

在这里插入图片描述
WAIT 几分钟

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

SELECT cus.custkey,cus.name,SUM(ord.totalprice) AS sum_totalprice
 FROM customer cus,orders ord
  WHERE ord.custkey=cus.custkey && ord.orderdate like '2020-%'
   GROUP BY cus.custkey
    ORDER BY cus.custkey;

在这里插入图片描述

wait 几分钟
在这里插入图片描述

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

SELECT cus.custkey,cus.name,nat.name,AVG(ord.totalprice) AS avg_price
 FROM customer cus,orders ord,nation nat
  WHERE nat.nationkey=cus.nationkey && cus.custkey=ord.custkey
   GROUP BY cus.custkey
    HAVING avg_price>50000
     ORDER BY cus.custkey;

在这里插入图片描述

wait 几分钟
在这里插入图片描述

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

SELECT sup.suppkey,sup.name,sup.address
 FROM supplier sup,supplier supx
  WHERE supx.name='金石印刷有限公司' && sup.nationkey=supx.nationkey;

在这里插入图片描述

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

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 \G;

在这里插入图片描述
wait 几分钟

在这里插入图片描述

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

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;

在这里插入图片描述

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

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

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;

在这里插入图片描述

10 查询订单平均金额超过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>10000
     ORDER BY avg_price;

在这里插入图片描述

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

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='刘玉龙'
           )
       )
    )
   ;

在这里插入图片描述
是我写错了？

SELECT par.partkey 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 par.partkey 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;

在这里插入图片描述
还真没有两个人都订购过的零件

12、查询顾客刘玉龙或钱岚订购过得全部零件的信息

先只查零件编号

SELECT par.partkey 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;

在这里插入图片描述
查零件全部信息

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 \G;

在这里插入图片描述

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 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 \G;