71、连接查询（1）
数据表：Customers，包含顾客名称：cust_name、顾客 id：cust_id，表中数据如下：
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
数据表：Orders（订单信息表），包含订单号：order_num，顾客 id：cust_id，表中数据如下：
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
【问题】编写 SQL 语句，查询 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。查询结果如下：
| cust_name | order_num |
|---|---|
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
表结构及数据如下：
解答：
mysql> select c.cust_name, o.order_num
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name, o.order_num;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
+-----------+-----------+
6 rows in set (0.00 sec)
72、连接查询（2）
数据表：Customers，包含顾客名称：cust_name，顾客id：cust_id，表中数据如下：
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
数据表：Orders（订单信息表），包含订单号：order_num，顾客id：cust_id，表中数据如下：
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表：OrderItems，包含商品订单号：order_num，订购数量：quantity，单价：item_price，表中数据如下：
| order_num | quantity | item_price |
|---|---|---|
| a1 | 1000 | 10 |
| a2 | 200 | 10 |
| a3 | 10 | 15 |
| a4 | 25 | 50 |
| a5 | 15 | 25 |
| a7 | 7 | 7 |
【问题】查询顾客名称、订单号以及每个订单的总价（OrderTotal），并按顾客名称再按订单号对结果进行升序排序。查询结果如下：
| cust_name | order_num | OrderTotal |
|---|---|---|
| an | a5 | 375 |
| andy | a1 | 10000 |
| ben | a2 | 2000 |
| hex | a7 | 49 |
| tom | a4 | 1250 |
| tony | a3 | 150 |
表结构及数据如下：
解答：
mysql> select cust_name, o.order_num,
-> sum(oi.quantity * oi.item_price) OrderTotal
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> join OrderItems oi on o.order_num = oi.order_num
-> group by cust_name, o.order_num
-> order by cust_name, o.order_num;
+-----------+-----------+------------+
| cust_name | order_num | OrderTotal |
+-----------+-----------+------------+
| an | a5 | 375 |
| andy | a1 | 10000 |
| ben | a2 | 2000 |
| hex | a7 | 49 |
| tom | a4 | 1250 |
| tony | a3 | 150 |
+-----------+-----------+------------+
6 rows in set (0.00 sec)
73、连接查询（3）
数据表：OrderItems，表中数据如下：
| prod_id | order_num |
|---|---|
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
数据表：Orders，表中数据如下：
| order_num | cust_id | order_date |
|---|---|---|
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
【问题】编写 SQL 语句，查询哪些订单购买了 prod_id 为 “BR01” 的产品，显示每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。查询结果如下：
| cust_id | order_date |
|---|---|
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
表结构及数据如下：
解答：
-- 使用子查询
mysql> select cust_id, order_date
-> from Orders where order_num in
-> (select order_num from OrderItems where prod_id = 'BR01')
-> order by order_date;
+---------+---------------------+
| cust_id | order_date |
+---------+---------------------+
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
+---------+---------------------+
2 rows in set (0.00 sec)
-- 使用连接查询
mysql> select o.cust_id, o.order_date
-> from Orders o join OrderItems oi on o.order_num = oi.order_num
-> where oi.prod_id = 'BR01'
-> order by o.order_date;
+---------+---------------------+
| cust_id | order_date |
+---------+---------------------+
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
+---------+---------------------+
2 rows in set (0.00 sec)
74、连接查询（4）
数据表：OrderItems，表中数据如下：
| prod_id | order_num |
|---|---|
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
数据表：Orders，表中数据如下：
| order_num | cust_id | order_date |
|---|---|---|
| a0001 | cust10 | 2022-01-01 00:00:00 |
| a0002 | cust1 | 2022-01-01 00:01:00 |
| a0003 | cust1 | 2022-01-02 00:00:00 |
| a0013 | cust2 | 2022-01-01 00:20:00 |
数据表：Customers，表中数据如下：
| cust_id | cust_email |
|---|---|
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
【问题】查询购买 prod_id 为 “BR01” 的产品的所有顾客的电子邮件。查询结果如下：
| cust_email |
|---|
| cust10@cust.com |
| cust1@cust.com |
表结构及数据如下：
解答：
-- 使用子查询
mysql> select cust_email from Customers where cust_id in
-> (select cust_id from Orders where order_num in
-> (select order_num from OrderItems where prod_id = 'BR01')
-> );
+-----------------+
| cust_email |
+-----------------+
| cust10@cust.com |
| cust1@cust.com |
+-----------------+
2 rows in set (0.00 sec)
-- 使用连接查询
mysql> select c.cust_email
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> join OrderItems oi on o.order_num = oi.order_num
-> where prod_id = 'BR01';
+-----------------+
| cust_email |
+-----------------+
| cust10@cust.com |
| cust1@cust.com |
+-----------------+
2 rows in set (0.00 sec)
75、连接查询（5）
数据表：OrderItems，表中数据如下：
| order_num | item_price | quantity |
|---|---|---|
| a1 | 10 | 105 |
| a2 | 1 | 1100 |
| a2 | 1 | 200 |
| a4 | 2 | 1121 |
| a5 | 5 | 10 |
| a2 | 1 | 19 |
| a7 | 7 | 5 |
数据表：Orders，表中数据如下：
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表：Customers，表中数据如下：
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
【问题】编写 SQL 语句，查询订单总价不小于 1000 的客户名称和总额（item_price * quantity），按总额对结果进行排序。查询结果如下：
| cust_name | total_price |
|---|---|
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |
表结构及数据如下：
解答：
mysql> select c.cust_name,
-> sum(oi.item_price * oi.quantity) total_price
-> from OrderItems oi join Orders o on oi.order_num = o.order_num
-> join Customers c on o.cust_id = c.cust_id
-> group by c.cust_name
-> having total_price >= 1000
-> order by total_price;
+-----------+-------------+
| cust_name | total_price |
+-----------+-------------+
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |
+-----------+-------------+
3 rows in set (0.00 sec)
76、连接查询（6）
数据表：Customers，表中数据如下：
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
数据表：Orders，表中数据如下：
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
【问题】编写 SQL语句，查询每个顾客的名称和所有的订单号，根据顾客姓名 cust_name 升序排序。查询结果如下：
| cust_name | order_num |
|---|---|
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
表结构及数据如下：
解答：
mysql> select c.cust_name, o.order_num
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
+-----------+-----------+
6 rows in set (0.00 sec)
77、连接查询（7）
数据表：Orders，表中数据如下：
| order_num | cust_id |
|---|---|
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表：Customers，表中数据如下：
| cust_id | cust_name |
|---|---|
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
| cust40 | ace |
【问题】检索每个顾客的名称和所有的订单号，列出所有的顾客，即使他们没有下过订单。查询结果如下：
| cust_name | order_num |
|---|---|
| ace | NULL |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
表结构及数据如下：
解答：
mysql> select c.cust_name, o.order_num
-> from Customers c left join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name;
+-----------+-----------+
| cust_name | order_num |
+-----------+-----------+
| ace | NULL |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
+-----------+-----------+
7 rows in set (0.00 sec)
78、连接查询（8）
数据表：Products，表中数据为：
| prod_id | prod_name |
|---|---|
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |
数据表：OrderItems，表中数据为：
| prod_id | order_num |
|---|---|
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |
【问题】编写 SQL 语句，查询产品名称（prod_name）和与之相关的订单号（order_num）的列表，并按照产品名称升序排序。查询结果如下：
| prod_name | order_num |
|---|---|
| coffee | a1121 |
| cola | a5 |
| cola | a19 |
| cola | a10 |
| egg | a105 |
| sockets | a200 |
| sockets | a1100 |
| soda | NULL |
表结构及数据如下：
解答：
mysql> select p.prod_name, oi.order_num
-> from Products p left join OrderItems oi on p.prod_id = oi.prod_id
-> order by p.prod_name;
+-----------+-----------+
| prod_name | order_num |
+-----------+-----------+
| coffee | a1121 |
| cola | a10 |
| cola | a19 |
| cola | a5 |
| egg | a105 |
| sockets | a1100 |
| sockets | a200 |
| soda | NULL |
+-----------+-----------+
8 rows in set (0.00 sec)
79、连接查询（9）
数据表：Products，表中数据如下：
| prod_id | prod_name |
|---|---|
| a0001 | egg |
| a0002 | sockets |
| a0013 | coffee |
| a0003 | cola |
| a0023 | soda |
数据表：OrderItems，表中数据如下：
| prod_id | order_num |
|---|---|
| a0001 | a105 |
| a0002 | a1100 |
| a0002 | a200 |
| a0013 | a1121 |
| a0003 | a10 |
| a0003 | a19 |
| a0003 | a5 |
【问题】查询产品名称（prod_name）和每一项产品的总订单数，并按产品名称升序排序。查询结果如下：
| prod_name | orders |
|---|---|
| coffee | 1 |
| cola | 3 |
| egg | 1 |
| sockets | 2 |
| soda | 0 |
解答：
mysql> select p.prod_name, count(order_num) orders
-> from Products p left join OrderItems oi on p.prod_id = oi.prod_id
-> group by p.prod_name
-> order by p.prod_name;
+-----------+--------+
| prod_name | orders |
+-----------+--------+
| coffee | 1 |
| cola | 3 |
| egg | 1 |
| sockets | 2 |
| soda | 0 |
+-----------+--------+
5 rows in set (0.00 sec)
80、连接查询（10）
数据表：Vendors，表中数据如下：
| vend_id |
|---|
| a0002 |
| a0013 |
| a0003 |
| a0010 |
数据表：Products，表中数据如下：
| vend_id | prod_id |
|---|---|
| a0001 | egg |
| a0002 | prod_id_iphone |
| a00113 | prod_id_tea |
| a0003 | prod_id_vivo phone |
| a0010 | prod_id_huawei phone |
【问题】列出供应商（Vendors 表中的 vend_id）及可供产品的数量（包括没有产品的供应商），根据 vend_id 升序排序。查询结果如下：
| vend_id | prod_id |
|---|---|
| a0002 | 1 |
| a0003 | 1 |
| a0010 | 1 |
| a0013 | 0 |
表结构及数据如下：
解答：
mysql> select v.vend_id, count(prod_id) prod_id
-> from Vendors v left join Products p on v.vend_id = p.vend_id
-> group by v.vend_id
-> order by v.vend_id;
+---------+---------+
| vend_id | prod_id |
+---------+---------+
| a0002 | 1 |
| a0003 | 1 |
| a0010 | 1 |
| a0013 | 0 |
+---------+---------+
4 rows in set (0.00 sec)
81、union 联合查询
数据表：OrderItems，表中数据如下：
| prod_id | quantity |
|---|---|
| a0001 | 105 |
| a0002 | 100 |
| a0002 | 200 |
| a0013 | 1121 |
| a0003 | 10 |
| a0003 | 19 |
| a0003 | 5 |
| BNBG | 10002 |
【问题】查询数量（quantity）为 100 的产品信息和产品 id（prod_id）以【BNBG】开头的产品信息，按产品 id 对结果进行升序排序。查询结果如下：
| prod_id | quantity |
|---|---|
| a0002 | 100 |
| BNBG | 10002 |
表结构及数据如下：
解答：
-- 使用 union
mysql> select * from OrderItems where quantity = 100
-> union
-> select * from OrderItems where prod_id like 'BNBG%'
-> order by prod_id;
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002 | 100 |
| BNBG | 10002 |
+---------+----------+
2 rows in set (0.03 sec)
-- 使用 or 构造查询条件
mysql> select * from OrderItems
-> where quantity = 100 or prod_id like 'BNBG%'
-> order by prod_id;
+---------+----------+
| prod_id | quantity |
+---------+----------+
| a0002 | 100 |
| BNBG | 10002 |
+---------+----------+
2 rows in set (0.00 sec)
