实验-子查询

不会喷火的小火龙

已于 2023-11-06 13:14:37 修改

阅读量1.2k

点赞数 2

分类专栏：数据库文章标签：数据库 sql mysql

于 2023-04-12 19:49:06 首次发布

本文链接：https://blog.csdn.net/m0_60496161/article/details/130114997

版权

数据库专栏收录该内容

20 篇文章 18 订阅

订阅专栏

子查询

1. 查询每门被选修的课程信息，输出课程号，课程名

题目

查询每门被选修的课程信息，输出课程号，课程名，结果按课程号降序排列。

其中课程表course结构为

在这里插入图片描述

选修表sc结构为

在这里插入图片描述

代码

select distinct sc.cno,cname from course,sc
where course.cno=sc.cno
order by sc.cno desc;

2、查询每门课程被选修的情况，输出课程号，课程名，被选修次数

题目

查询每门课程被选修的情况，输出课程号，课程名，被选修次数，结果按选修次数降序课程号降序排列。

其中课程表course结构为

在这里插入图片描述

选修表sc结构为

在这里插入图片描述

代码

select distinct course.cno,course.cname,count(*) from course,sc
where course.cno=sc.cno
group by sc.cno 
order by count(*) desc, sc.cno desc;

题解

首先是从哪些表中查询数据，这里是从两张表 COURSE 和 SC 中查询。

FROM course, sc
接下来，需要指定查询的条件，这里的条件是 COURSE 表和 SC 表中的 CNO 字段相等。

WHERE course.cno = sc.cno
然后，将查询结果按照课程号进行分组，以便统计每个课程的选课人数。

GROUP BY sc.cno
接下来，需要选取要查询的列，包括课程号、课程名称和选课人数。

SELECT DISTINCT course.cno, course.cname, COUNT(*)

注意这里使用了 DISTINCT 关键字去除重复行。

然后，需要对查询结果进行排序，按照选课人数降序排列，如果选课人数相同，则按照课程号升序排列。

ORDER BY count(*) DESC, sc.cno ASC
最后，得到所有课程的课程号、课程名称和选课人数，按照选课人数从多到少排序，如果选课人数相同，则按照课程号从小到大排序。

3、查询每门课程被选修的情况(包括从未被选修过的课程)

题目

查询每门课程被选修的情况(包括从未被选修过的课程)，输出课程号，课程名，被选修次数，结果按选修次数升序课程号升序排列。

其中课程表course结构为
在这里插入图片描述

选修表sc结构为

在这里插入图片描述

代码

select course.cno,cname,count(sno) 
from course left join sc
on  course.cno=sc.cno 
group by course.cno
order by count(sno) asc ,course.cno asc

题解

使用 LEFT JOIN 关键字将 COURSE 表和 SC 表联结起来。

FROM course LEFT JOIN sc ON course.cno = sc.cno

这里使用了 LEFT JOIN，即使 SC 表中没有与 COURSE 表中的某些记录匹配的记录，也会返回 COURSE 表中的所有记录。

使用 GROUP BY 关键字按照课程号分组。

GROUP BY course.cno

该语句将查询结果按照 COURSE.CNO 字段的值进行分组。

使用 COUNT 函数统计每组中的记录数量。

COUNT(sno)

该函数用于统计每组中的记录数量。

使用 ORDER BY 关键字按照记录数量升序排列，并以每组中的第一个记录的 CNO 升序排列。

ORDER BY count(sno) ASC, course.cno ASC

该语句将查询结果按照记录数量升序排列，如果记录数量相同，则按照 COURSE.CNO 的升序排列。
使用 SELECT 关键字选取要查询的列。

SELECT course.cno, cname, COUNT(sno)

该语句选取了 COURSE.CNO、COURSE.CNAME 和 COUNT(SNO) 三个列。

因此，最终查询结果将会是每个课程的课程号、课程名和选课人数。注意，由于使用了 LEFT JOIN，即使某些课程没有被选课，它们也会被包含在查询结果中，其选课人数为 0。最后，查询结果将按照选课人数从少到多排序，如果选课人数相同，则按照课程号从小到大排序。

4、查询 2016 年没有下订单的客户

题目

查询 2016 年没有下订单的客户，输出客户编号和客户名称，结果按客户名称升序排列。

其中客户表customer结构为

在这里插入图片描述

订单表`order`结构为

在这里插入图片描述

代码

select customer_id,customer_name from customer
where not exists(select * from `order` where
        year(order_date)=2016 and customer_id = customer.customer_id)
order by customer_name

题解

使用 FROM 关键字指定要查询的数据来源。

FROM customer

该语句将 CUSTOMER 表作为查询的数据来源。
使用 WHERE 关键字指定查询的条件。

WHERE NOT EXISTS (SELECT * FROM `order` WHERE YEAR(order_date) = 2016 AND customer_id = customer.customer_id)

该语句使用 NOT EXISTS 子句来排除在 2016 年没有下过订单的客户。子查询中查询订单表 (ORDER) 中的订单日期 (ORDER_DATE) 并判断是否为 2016 年，以及订单表中的客户 ID (CUSTOMER_ID) 是否与主查询中的客户 ID 相等，如果子查询中没有查询到符合条件的记录，则返回主查询中的记录。
使用 ORDER BY 关键字按照客户名字升序排列。

ORDER BY customer_name

该语句将查询结果按照 CUSTOMER_NAME 字段的值进行升序排列。
使用 SELECT 关键字选取要查询的列。

SELECT customer_id, customer_name

该语句选取了 CUSTOMER_ID 和 CUSTOMER_NAME 两个列。

因此，最终查询结果将是在 2016 年至少下过一次订单的客户列表，按照客户名字升序排列。注意，使用了 NOT EXISTS 子句来排除在 2016 年没有下过订单的客户，而不是使用 INNER JOIN 或 LEFT JOIN 等连接操作，这是一种常见的查询优化技巧。

5、查询销售数量总数超过600的产品信息

题目

查询销售数量总数超过600的产品信息，输出产品号，产品名称。

其中客户表customer表结构为
在这里插入图片描述

订单表`order`表结构为
在这里插入图片描述

订单详情表order_detail表结构为

在这里插入图片描述

产品表product表结构为

在这里插入图片描述

代码

select product.product_id,product.product_name 
from product,order_detail
where  product.product_id = order_detail.product_id
group by product.product_id
having sum(buy_number)>600

题解

使用 FROM 关键字指定要查询的数据来源。

FROM product, order_detail

该语句将 PRODUCT 表和 ORDER_DETAIL 表作为查询的数据来源。
使用 WHERE 关键字指定查询的条件。

WHERE product.product_id = order_detail.product_id

该语句用于指定 PRODUCT 表和 ORDER_DETAIL 表的连接条件，即连接字段为 PRODUCT_ID。
使用 GROUP BY 关键字按照产品 ID 分组。

GROUP BY product.product_id

该语句将查询结果按照 PRODUCT_ID 字段的值进行分组。
使用 HAVING 关键字筛选出购买数量之和大于 600 的产品。

HAVING SUM(order_detail.buy_number) > 600

该语句使用 HAVING 子句来筛选出购买数量之和大于 600 的产品。SUM(order_detail.buy_number) 表示计算 ORDER_DETAIL 表中 BUY_NUMBER 列的总和。
使用 SELECT 关键字选取要查询的列。

SELECT product.product_id, product.product_name

该语句选取了 PRODUCT_ID 和 PRODUCT_NAME 两个列。

因此，最终查询结果将是购买数量之和大于 600 的产品列表，包括产品 ID 和产品名称两列。注意，由于使用了 GROUP BY 子句，每个产品只会出现一次，如果一个产品在多个订单中出现，它们的购买数量会被合并计算。

6、查询一次订购了4,7,8号产品的订单号

题目

查询一次订购了4,7,8号产品的订单号。

其中客户表cutomer表结构为

在这里插入图片描述

订单表`order`表结构为

在这里插入图片描述

订单详情表order_detail表结构为

在这里插入图片描述

产品表product表结构为

在这里插入图片描述

代码

select `order`.order_id from `order`,order_detail
where `order`.order_id = order_detail.order_id
and order_detail.product_id in(4,7,8)
group by `order`.order_id
having count(DISTINCT product_id)>=3;

题解

使用 FROM 关键字指定要查询的数据来源。

FROM order, order_detail

该语句将 ORDER 表和 ORDER_DETAIL 表作为查询的数据来源。
使用 WHERE 关键字指定查询的条件。

WHERE order.order_id = order_detail.order_id

AND order_detail.product_id IN (4, 7, 8)

该语句用于指定订单表和订单明细表的连接条件，即连接字段为 ORDER_ID，同时筛选出购买了编号为 4、7、8 的产品的订单。
使用 GROUP BY 关键字按照订单 ID 分组。

GROUP BY order.order_id

该语句将查询结果按照 ORDER_ID 字段的值进行分组。
使用 HAVING 关键字筛选出购买了至少三个以上的产品的订单。

HAVING COUNT(DISTINCT product_id) >= 3

该语句使用 HAVING 子句来筛选出购买了至少三个以上的产品的订单。COUNT(DISTINCT product_id) 表示计算订单中不同的产品数量。
使用 SELECT 关键字选取要查询的列。

SELECT order.order_id

该语句选取了 ORDER_ID 列。

因此，最终查询结果将是购买了至少三个编号为 4、7、8 的产品的订单列表，包括订单 ID 列。注意，由于使用了 GROUP BY 子句，每个订单只会出现一次，如果一个订单中购买了多个编号为 4、7、8 的产品，它们的数量会被合并计算。

7、查询一次订购了4,7,8号产品的客户名及电话

题目

查询一次订购了4,7,8号产品的客户名及电话。

其中客户表cutomer表结构为

在这里插入图片描述

订单表`order`表结构为

在这里插入图片描述

订单详情表order_detail表结构为

在这里插入图片描述

产品表product表结构为

在这里插入图片描述

代码

SELECT customer_name,phone FROM customer
WHERE customer_id in (SELECT customer_id from `order` 
       where not exists(select * from order_detail od WHERE product_id in(4,7,8) 
AND NOT EXISTS(select * FROM order_detail
WHERE order_id=`order`.order_id and od.product_id = product_id)));

题解

使用 FROM 关键字指定要查询的数据来源。

FROM customer

该语句将 CUSTOMER 表作为查询的数据来源。

使用 WHERE 关键字指定查询的条件。

该语句使用子查询来筛选出符合条件的客户 ID。首先，子查询中查询订单表 (ORDER) 中购买了编号为 4、7、8 的产品的订单，然后使用 NOT EXISTS 子句和子查询来排除购买了其他产品的订单，最后返回符合条件的订单所对应的客户 ID。

WHERE customer_id IN (
  SELECT customer_id FROM `order`
  WHERE NOT EXISTS (
    SELECT * FROM order_detail od
    WHERE product_id IN (4, 7, 8) AND NOT EXISTS (
      SELECT * FROM order_detail
      WHERE order_id = `order`.order_id AND od.product_id = product_id
    )
  )
)

使用 SELECT 关键字选取要查询的列。

SELECT customer_name, phone

该语句选取了 CUSTOMER_NAME 和 PHONE 两个列。

因此，最终查询结果将是购买了编号为 4、7、8 的产品但没有购买其他产品的客户列表，包括客户名称和电话号码两列。

8、统计查询订单金额超过该客户平均订单额（该客户订单金额平均值）的上海客户的订单情况

题目

统计查询订单金额超过该客户平均订单额（该客户订单金额平均值）的上海客户的订单情况，输出客户名、订单号、订单金额、该客户平均订单额（该客户订单金额平均值），结果按客户名升序订单号升序排列。

其中客户表cutomer表结构为

在这里插入图片描述

订单表`order`表结构为

在这里插入图片描述

代码

select customer_name,order_id,total_money,avgsum
from customer,`order`,(SELECT customer_id,avg(total_money) as avgsum from `order` group by customer_id) as a1 
where city='上海' and customer.customer_id = `order`.customer_id and a1.customer_id = `order`.customer_id
group by order_id
having total_money>avgsum
order by customer_name,order_id;

题解

使用 FROM 关键字指定要查询的数据来源。
```
FROM customer, `order`, (
  SELECT customer_id, AVG(total_money) AS avgsum FROM `order` GROUP BY customer_id
) AS a1
```
该语句将 CUSTOMER 表、ORDER 表和子查询的结果作为查询的数据来源。子查询用于计算每个客户的平均订单金额，并将其命名为 avgsum。
使用 WHERE 关键字指定查询的条件。
```
WHERE city = '上海'
  AND customer.customer_id = `order`.customer_id
  AND a1.customer_id = `order`.customer_id
```
该语句用于指定查询条件，包括限定城市为上海，订单表和客户表的连接条件，以及将子查询结果和订单表连接的条件。
使用 GROUP BY 关键字按照订单 ID 分组。

GROUP BY order_id

该语句将查询结果按照 ORDER_ID 字段的值进行分组。
使用 HAVING 关键字筛选出订单金额高于该客户的平均订单金额的订单。

HAVING total_money > avgsum

该语句使用 HAVING 子句来筛选出订单金额高于该客户的平均订单金额的订单。total_money 表示订单总金额，avgsum 表示客户的平均订单金额。
使用 SELECT 关键字选取要查询的列。

SELECT customer_name, order_id, total_money, avgsum

该语句选取了 CUSTOMER_NAME、ORDER_ID、TOTAL_MONEY 和 AVGSUM 四个列。
使用 ORDER BY 关键字按照客户名称和订单 ID 进行排序。

ORDER BY customer_name, order_id

该语句将查询结果按照客户名称和订单 ID 进行排序，以便更好地展示数据。

因此，最终查询结果将是在上海市下单且订单金额高于该客户的平均订单金额的订单列表，包括客户名称、订单 ID、订单总金额和客户平均订单金额四列，并按照客户名称和订单 ID 进行排序。

9、查询一次订购了4,7,8号三种商品的订单信息

题目

查询一次订购了4,7,8号三种商品的订单信息，输出订单号、订单日期。

其中客户表cutomer表结构为

在这里插入图片描述

订单表`order`表结构为

在这里插入图片描述

订单详情表order_detail表结构为

在这里插入图片描述

产品表product表结构为

在这里插入图片描述

代码

SELECT o.order_id, o.order_date
FROM `order` o
left JOIN order_detail od ON o.order_id = od.order_id
WHERE od.product_id IN (4, 7, 8)
GROUP BY o.order_id, o.order_date
HAVING COUNT(DISTINCT od.product_id) = 3;

题解

使用 FROM 关键字指定要查询的数据来源。
```
FROM `order` o
LEFT JOIN order_detail od ON o.order_id = od.order_id
```
该语句将 ORDER 表和 ORDER_DETAIL 表作为查询的数据来源，并使用 LEFT JOIN 连接这两个表。LEFT JOIN 表示左连接，它保证了即使订单表中没有对应的订单详情，也能查询到该订单。
使用 WHERE 关键字指定查询的条件。

WHERE od.product_id IN (4, 7, 8)

该语句用于指定查询条件，限定了产品的编号为 4、7、8。
使用 GROUP BY 关键字按照订单 ID 和订单日期分组。

GROUP BY o.order_id, o.order_date

该语句将查询结果按照 ORDER_ID 和 ORDER_DATE 两个字段的值进行分组，以便后续使用 HAVING 子句来筛选符合条件的订单。
使用 HAVING 关键字筛选出购买了编号为 4、7、8 的所有产品的订单。

HAVING COUNT(DISTINCT od.product_id) = 3

该语句使用 HAVING 子句来筛选符合条件的订单。COUNT(DISTINCT od.product_id) 表示订单中购买了多少个不同的产品，如果等于 3，说明订单中购买了编号为 4、7、8 的所有产品。
使用 SELECT 关键字选取要查询的列。

SELECT o.order_id, o.order_date

该语句选取了 ORDER_ID 和 ORDER_DATE 两个列。