SQL常见面试题总结（1）

题目来源于：牛客题霸 - SQL 必知必会

题目来源于：牛客题霸 - SQL 必知必会

牛客题霸 - SQL 必知必会

检索数据

检索数据

SELECT 用于从数据库中查询数据。

SELECT

从 Customers 表中检索所有的 ID

从 Customers 表中检索所有的 ID

现有表 Customers 如下：

Customers

cust_idABC

cust_id

cust_id

A

A

B

B

C

C

编写 SQL 语句，从 Customers 表中检索所有的 cust_id。

Customers

cust_id

答案：

SELECT cust_id
FROM Customers

SELECT cust_id
FROM Customers

检索并列出已订购产品的清单

检索并列出已订购产品的清单

表 OrderItems 含有非空的列 prod_id 代表商品 id，包含了所有已订购的商品（有些已被订购多次）。

OrderItems

prod_id

prod_ida1a2a3a4a5a6a7

prod_id

prod_id

a1

a1

a2

a2

a3

a3

a4

a4

a5

a5

a6

a6

a7

a7

编写 SQL 语句，检索并列出所有已订购商品（prod_id）的去重后的清单。

prod_id

答案：

SELECT DISTINCT prod_id
FROM OrderItems

SELECT DISTINCT prod_id
FROM OrderItems

知识点：DISTINCT 用于返回列中的唯一不同值。

DISTINCT

检索所有列

检索所有列

现在有 Customers 表（表中含有列 cust_id 代表客户 id，cust_name 代表客户姓名）

Customers

cust_id

cust_name

cust_idcust_namea1andya2bena3tonya4toma5ana6leea7hex

cust_idcust_name

cust_id

cust_name

a1andy

a1

andy

a2ben

a2

ben

a3tony

a3

tony

a4tom

a4

tom

a5an

a5

an

a6lee

a6

lee

a7hex

a7

hex

需要编写 SQL 语句，检索所有列。

答案：

SELECT cust_id, cust_name
FROM Customers

SELECT cust_id, cust_name
FROM Customers

排序检索数据

排序检索数据

ORDER BY 用于对结果集按照一个列或者多个列进行排序。默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用 DESC 关键字。

ORDER BY

DESC

检索顾客名称并且排序

检索顾客名称并且排序

有表 Customers，cust_id 代表客户 id，cust_name 代表客户姓名。

Customers

cust_id

cust_name

cust_idcust_namea1andya2bena3tonya4toma5ana6leea7hex

cust_idcust_name

cust_id

cust_name

a1andy

a1

andy

a2ben

a2

ben

a3tony

a3

tony

a4tom

a4

tom

a5an

a5

an

a6lee

a6

lee

a7hex

a7

hex

从 Customers 中检索所有的顾客名称（cust_name），并按从 Z 到 A 的顺序显示结果。

Customers

cust_name

答案：

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC

SELECT cust_name
FROM Customers
ORDER BY cust_name DESC

对顾客 ID 和日期排序

对顾客 ID 和日期排序

有 Orders 表：

Orders

cust_idorder_numorder_dateandyaaaa2021-01-01 00:00:00andybbbb2021-01-01 12:00:00bobcccc2021-01-10 12:00:00dickdddd2021-01-11 00:00:00

cust_idorder_numorder_date

cust_id

order_num

order_date

andyaaaa2021-01-01 00:00:00

andy

aaaa

2021-01-01 00:00:00

andybbbb2021-01-01 12:00:00

andy

bbbb

2021-01-01 12:00:00

bobcccc2021-01-10 12:00:00

bob

cccc

2021-01-10 12:00:00

dickdddd2021-01-11 00:00:00

dick

dddd

2021-01-11 00:00:00

编写 SQL 语句，从 Orders 表中检索顾客 ID（cust_id）和订单号（order_num），并先按顾客 ID 对结果进行排序，再按订单日期倒序排列。

Orders

cust_id

order_num

答案：

# 根据列名排序
# 注意：是 order_date 降序，而不是 order_num
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id,order_date DESC

# 根据列名排序
# 注意：是 order_date 降序，而不是 order_num
SELECT cust_id, order_num
FROM Orders
ORDER BY cust_id,order_date DESC

知识点：order by 对多列排序的时候，先排序的列放前面，后排序的列放后面。并且，不同的列可以有不同的排序规则。

order by

按照数量和价格排序

按照数量和价格排序

假设有一个 OrderItems 表：

OrderItems

quantityitem_price11001010032500

quantityitem_price

quantity

item_price

1100

1

100

101003

10

1003

2500

2

500

编写 SQL 语句，显示 OrderItems 表中的数量（quantity）和价格（item_price），并按数量由多到少、价格由高到低排序。

OrderItems

quantity

item_price

答案：

SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC,item_price DESC

SELECT quantity, item_price
FROM OrderItems
ORDER BY quantity DESC,item_price DESC

检查 SQL 语句

检查 SQL 语句

有 Vendors 表：

Vendors

vend_name海底捞小龙坎大龙燚

vend_name

vend_name

海底捞

海底捞

小龙坎

小龙坎

大龙燚

大龙燚

下面的 SQL 语句有问题吗？尝试将它改正确，使之能够正确运行，并且返回结果根据vend_name 逆序排列。

vend_name

SELECT vend_name,
FROM Vendors
ORDER vend_name DESC

SELECT vend_name,
FROM Vendors
ORDER vend_name DESC

改正后：

SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC

SELECT vend_name
FROM Vendors
ORDER BY vend_name DESC

知识点：

逗号作用是用来隔开列与列之间的。

ORDER BY 是有 BY 的，需要撰写完整，且位置正确。

过滤数据

过滤数据

WHERE 可以过滤返回的数据。

WHERE

下面的运算符可以在 WHERE 子句中使用：

WHERE

运算符描述=等于<>不等于。 注释： 在 SQL 的一些版本中，该操作符可被写成 !=>大于<小于>=大于等于<=小于等于BETWEEN在某个范围内LIKE搜索某种模式IN指定针对某个列的多个可能值

运算符描述

运算符

描述

=等于

=

等于

<>不等于。 注释： 在 SQL 的一些版本中，该操作符可被写成 !=

<>

不等于。 注释： 在 SQL 的一些版本中，该操作符可被写成 !=

大于

大于

<小于

<

小于

=大于等于

=

大于等于

<=小于等于

<=

小于等于

BETWEEN在某个范围内

BETWEEN

在某个范围内

LIKE搜索某种模式

LIKE

搜索某种模式

IN指定针对某个列的多个可能值

IN

指定针对某个列的多个可能值

返回固定价格的产品

返回固定价格的产品

有表 Products：

Products

prod_idprod_nameprod_pricea0018sockets9.49a0019iphone13600b0018gucci t-shirts1000

prod_idprod_nameprod_price

prod_id

prod_name

prod_price

a0018sockets9.49

a0018

sockets

9.49

a0019iphone13600

a0019

iphone13

600

b0018gucci t-shirts1000

b0018

gucci t-shirts

1000

【问题】从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9.49 美元的产品。

Products

prod_id

prod_name

答案：

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49

SELECT prod_id, prod_name
FROM Products
WHERE prod_price = 9.49

返回更高价格的产品

返回更高价格的产品

有表 Products：

Products

prod_idprod_nameprod_pricea0018sockets9.49a0019iphone13600b0019gucci t-shirts1000

prod_idprod_nameprod_price

prod_id

prod_name

prod_price

a0018sockets9.49

a0018

sockets

9.49

a0019iphone13600

a0019

iphone13

600

b0019gucci t-shirts1000

b0019

gucci t-shirts

1000

【问题】编写 SQL 语句，从 Products 表中检索产品 ID（prod_id）和产品名称（prod_name），只返回价格为 9 美元或更高的产品。

Products

prod_id

prod_name

答案：

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9

SELECT prod_id, prod_name
FROM Products
WHERE prod_price >= 9

返回产品并且按照价格排序

返回产品并且按照价格排序

有表 Products：

Products

prod_idprod_nameprod_pricea0011egg3a0019sockets4b0019coffee15

prod_idprod_nameprod_price

prod_id

prod_name

prod_price

a0011egg3

a0011

egg

3

a0019sockets4

a0019

sockets

4

b0019coffee15

b0019

coffee

15

【问题】编写 SQL 语句，返回 Products 表中所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），然后按价格对结果进行排序。

Products

prod_name

prod_price

答案：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price

# 或者
SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 3 AND 6
ORDER BY prod_price

# 或者
SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 AND prod_price <= 6
ORDER BY prod_price

返回更多的产品

返回更多的产品

OrderItems 表含有：订单号 order_num，quantity产品数量

OrderItems

order_num

quantity

order_numquantitya1105a21100a2200a41121a510a219a75

order_numquantity

order_num

quantity

a1105

a1

105

a21100

a2

1100

a2200

a2

200

a41121

a4

1121

a510

a5

10

a219

a2

19

a75

a7

5

【问题】从 OrderItems 表中检索出所有不同且不重复的订单号（order_num），其中每个订单都要包含 100 个或更多的产品。

OrderItems

order_num

答案：

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100

SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING SUM(quantity) >= 100

高级数据过滤

高级数据过滤

AND 和 OR 运算符用于基于一个以上的条件对记录进行过滤，两者可以结合使用。AND 必须 2 个条件都成立，OR只要 2 个条件中的一个成立即可。

AND

OR

AND

OR

检索供应商名称

检索供应商名称

Vendors 表有字段供应商名称（vend_name）、供应商国家（vend_country）、供应商州（vend_state）

Vendors

vend_name

vend_country

vend_state

vend_namevend_countryvend_stateappleUSACAvivoCNAshenzhenhuaweiCNAxian

vend_namevend_countryvend_state

vend_name

vend_country

vend_state

appleUSACA

apple

USA

CA

vivoCNAshenzhen

vivo

CNA

shenzhen

huaweiCNAxian

huawei

CNA

xian

【问题】编写 SQL 语句，从 Vendors 表中检索供应商名称（vend_name），仅返回加利福尼亚州的供应商（这需要按国家[USA]和州[CA]进行过滤，没准其他国家也存在一个 CA）

Vendors

vend_name

答案：

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'

检索并列出已订购产品的清单

检索并列出已订购产品的清单

OrderItems 表包含了所有已订购的产品（有些已被订购多次）。

OrderItems

prod_idorder_numquantityBR01a1105BR02a21100BR02a2200BR03a41121BR017a510BR02a219BR017a75

prod_idorder_numquantity

prod_id

order_num

quantity

BR01a1105

BR01

a1

105

BR02a21100

BR02

a2

1100

BR02a2200

BR02

a2

200

BR03a41121

BR03

a4

1121

BR017a510

BR017

a5

10

BR02a219

BR02

a2

19

BR017a75

BR017

a7

5

【问题】编写 SQL 语句，查找所有订购了数量至少 100 个的 BR01、BR02 或 BR03 的订单。你需要返回 OrderItems 表的订单号（order_num）、产品 ID（prod_id）和数量（quantity），并按产品 ID 和数量进行过滤。

BR01

BR02

BR03

OrderItems

order_num

prod_id

quantity

答案：

SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01', 'BR02', 'BR03') AND quantity >= 100

SELECT order_num, prod_id, quantity
FROM OrderItems
WHERE prod_id IN ('BR01', 'BR02', 'BR03') AND quantity >= 100

返回所有价格在 3 美元到 6 美元之间的产品的名称和价格

返回所有价格在 3 美元到 6 美元之间的产品的名称和价格

有表 Products：

Products

prod_idprod_nameprod_pricea0011egg3a0019sockets4b0019coffee15

prod_idprod_nameprod_price

prod_id

prod_name

prod_price

a0011egg3

a0011

egg

3

a0019sockets4

a0019

sockets

4

b0019coffee15

b0019

coffee

15

【问题】编写 SQL 语句，返回所有价格在 3 美元到 6 美元之间的产品的名称（prod_name）和价格（prod_price），使用 AND 操作符，然后按价格对结果进行升序排序。

prod_name

prod_price

答案：

SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 and prod_price <= 6
ORDER BY prod_price

SELECT prod_name, prod_price
FROM Products
WHERE prod_price >= 3 and prod_price <= 6
ORDER BY prod_price

检查 SQL 语句

检查 SQL 语句

供应商表 Vendors 有字段供应商名称 vend_name、供应商国家 vend_country、供应商省份 vend_state

Vendors

vend_name

vend_country

vend_state

vend_namevend_countryvend_stateappleUSACAvivoCNAshenzhenhuaweiCNAxian

vend_namevend_countryvend_state

vend_name

vend_country

vend_state

appleUSACA

apple

USA

CA

vivoCNAshenzhen

vivo

CNA

shenzhen

huaweiCNAxian

huawei

CNA

xian

【问题】修改正确下面 sql，使之正确返回。

SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';

SELECT vend_name
FROM Vendors
ORDER BY vend_name
WHERE vend_country = 'USA' AND vend_state = 'CA';

修改后：

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name

SELECT vend_name
FROM Vendors
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name

ORDER BY 语句必须放在 WHERE 之后。

ORDER BY

WHERE

用通配符进行过滤

用通配符进行过滤

SQL 通配符必须与 LIKE 运算符一起使用

LIKE

在 SQL 中，可使用以下通配符：

通配符描述%代表零个或多个字符_仅替代一个字符[charlist]字符列中的任何单一字符[^charlist] 或者 [!charlist]不在字符列中的任何单一字符

通配符描述

通配符

描述

%代表零个或多个字符

%

%

代表零个或多个字符

_仅替代一个字符

_

_

仅替代一个字符

[charlist]字符列中的任何单一字符

[charlist]

[charlist]

字符列中的任何单一字符

[^charlist] 或者 [!charlist]不在字符列中的任何单一字符

[^charlist] 或者 [!charlist]

[^charlist]

[!charlist]

不在字符列中的任何单一字符

检索产品名称和描述（一）

检索产品名称和描述（一）

Products 表如下：

Products

prod_nameprod_desca0011usba0019iphone13b0019gucci t-shirtsc0019gucci toyd0019lego toy

prod_nameprod_desc

prod_name

prod_desc

a0011usb

a0011

usb

a0019iphone13

a0019

iphone13

b0019gucci t-shirts

b0019

gucci t-shirts

c0019gucci toy

c0019

gucci toy

d0019lego toy

d0019

lego toy

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称。

Products

prod_name

prod_desc

toy

答案：

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%'

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%'

检索产品名称和描述（二）

检索产品名称和描述（二）

Products 表如下：

Products

prod_nameprod_desca0011usba0019iphone13b0019gucci t-shirtsc0019gucci toyd0019lego toy

prod_nameprod_desc

prod_name

prod_desc

a0011usb

a0011

usb

a0019iphone13

a0019

iphone13

b0019gucci t-shirts

b0019

gucci t-shirts

c0019gucci toy

c0019

gucci toy

d0019lego toy

d0019

lego toy

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

Products

prod_name

prod_desc

toy

答案：

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%'
ORDER BY prod_name

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc NOT LIKE '%toy%'
ORDER BY prod_name

检索产品名称和描述（三）

检索产品名称和描述（三）

Products 表如下：

Products

prod_nameprod_desca0011usba0019iphone13b0019gucci t-shirtsc0019gucci toyd0019lego carrots toy

prod_nameprod_desc

prod_name

prod_desc

a0011usb

a0011

usb

a0019iphone13

a0019

iphone13

b0019gucci t-shirts

b0019

gucci t-shirts

c0019gucci toy

c0019

gucci toy

d0019lego carrots toy

d0019

lego carrots toy

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

Products

prod_name

prod_desc

toy

carrots

AND

LIKE

答案：

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE "%carrots%"

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE "%carrots%"

检索产品名称和描述（四）

检索产品名称和描述（四）

Products 表如下：

Products

prod_nameprod_desca0011usba0019iphone13b0019gucci t-shirtsc0019gucci toyd0019lego toy carrots

prod_nameprod_desc

prod_name

prod_desc

a0011usb

a0011

usb

a0019iphone13

a0019

iphone13

b0019gucci t-shirts

b0019

gucci t-shirts

c0019gucci toy

c0019

gucci toy

d0019lego toy carrots

d0019

lego toy carrots

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

%

LIKE

答案：

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%'

SELECT prod_name, prod_desc
FROM Products
WHERE prod_desc LIKE '%toy%carrots%'

创建计算字段

创建计算字段

别名

别名

别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）。有表 Vendors 代表供应商信息，vend_id 供应商 id、vend_name 供应商名称、vend_address 供应商地址、vend_city 供应商城市。

Vendors

vend_id

vend_name

vend_address

vend_city

vend_idvend_namevend_addressvend_citya001tencent cloudaddress1shenzhena002huawei cloudaddress2dongguana003aliyun cloudaddress3hangzhoua003netease cloudaddress4guangzhou

vend_idvend_namevend_addressvend_city

vend_id

vend_name

vend_address

vend_city

a001tencent cloudaddress1shenzhen

a001

tencent cloud

address1

shenzhen

a002huawei cloudaddress2dongguan

a002

huawei cloud

address2

dongguan

a003aliyun cloudaddress3hangzhou

a003

aliyun cloud

address3

hangzhou

a003netease cloudaddress4guangzhou

a003

netease cloud

address4

guangzhou

【问题】编写 SQL 语句，从 Vendors 表中检索 vend_id、vend_name、vend_address 和 vend_city，将 vend_name 重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address 重命名为 vaddress，按供应商名称对结果进行升序排序。

Vendors

vend_id

vend_name

vend_address

vend_city

vend_name

vname

vend_city

vcity

vend_address

vaddress

答案：

SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity
FROM Vendors
ORDER BY vname
# as 可以省略
SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity
FROM Vendors
ORDER BY vname

SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity
FROM Vendors
ORDER BY vname
# as 可以省略
SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity
FROM Vendors
ORDER BY vname

打折

打折

我们的示例商店正在进行打折促销，所有产品均降价 10%。Products 表包含 prod_id 产品 id、prod_price 产品价格。

Products

prod_id

prod_price

【问题】编写 SQL 语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）。

Products

prod_id

prod_price

sale_price

sale_price

答案：

SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price
FROM Products

SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price
FROM Products

注意：sale_price 是对计算结果的命名，而不是原有的列名。

sale_price

使用函数处理数据

使用函数处理数据

顾客登录名

顾客登录名

我们的商店已经上线了，正在创建顾客账户。所有用户都需要登录名，默认登录名是其名称和所在城市的组合。

给出 Customers 表 如下：

Customers

cust_idcust_namecust_contactcust_citya1Andy LiAndy LiOak Parka2Ben LiuBen LiuOak Parka3Tony DaiTony DaiOak Parka4Tom ChenTom ChenOak Parka5An LiAn LiOak Parka6Lee ChenLee ChenOak Parka7Hex LiuHex LiuOak Park

cust_idcust_namecust_contactcust_city

cust_id

cust_name

cust_contact

cust_city

a1Andy LiAndy LiOak Park

a1

Andy Li

Andy Li

Oak Park

a2Ben LiuBen LiuOak Park

a2

Ben Liu

Ben Liu

Oak Park

a3Tony DaiTony DaiOak Park

a3

Tony Dai

Tony Dai

Oak Park

a4Tom ChenTom ChenOak Park

a4

Tom Chen

Tom Chen

Oak Park

a5An LiAn LiOak Park

a5

An Li

An Li

Oak Park

a6Lee ChenLee ChenOak Park

a6

Lee Chen

Lee Chen

Oak Park

a7Hex LiuHex LiuOak Park

a7

Hex Liu

Hex Liu

Oak Park

【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

cust_id

cust_name

user_login

cust_contact

cust_city

答案：

SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) AS user_login
FROM Customers

SELECT cust_id, cust_name, UPPER(CONCAT(SUBSTRING(cust_contact, 1, 2), SUBSTRING(cust_city, 1, 3))) AS user_login
FROM Customers

知识点：

截取函数SUBSTRING()：截取字符串，substring(str ,n ,m)（n 表示起始截取位置，m 表示要截取的字符个数）表示返回字符串 str 从第 n 个字符开始截取 m 个字符；

截取函数SUBSTRING()：截取字符串，substring(str ,n ,m)（n 表示起始截取位置，m 表示要截取的字符个数）表示返回字符串 str 从第 n 个字符开始截取 m 个字符；

SUBSTRING()

substring(str ,n ,m)

拼接函数CONCAT()：将两个或多个字符串连接成一个字符串，select concat(A,B)：连接字符串 A 和 B。

拼接函数CONCAT()：将两个或多个字符串连接成一个字符串，select concat(A,B)：连接字符串 A 和 B。

CONCAT()

大写函数 UPPER()：将指定字符串转换为大写。

大写函数 UPPER()：将指定字符串转换为大写。

UPPER()

返回 2020 年 1 月的所有订单的订单号和订单日期

返回 2020 年 1 月的所有订单的订单号和订单日期

Orders 订单表如下：

Orders

order_numorder_datea00012020-01-01 00:00:00a00022020-01-02 00:00:00a00032020-01-01 12:00:00a00042020-02-01 00:00:00a00052020-03-01 00:00:00

order_numorder_date

order_num

order_date

a00012020-01-01 00:00:00

a0001

2020-01-01 00:00:00

a00022020-01-02 00:00:00

a0002

2020-01-02 00:00:00

a00032020-01-01 12:00:00

a0003

2020-01-01 12:00:00

a00042020-02-01 00:00:00

a0004

2020-02-01 00:00:00

a00052020-03-01 00:00:00

a0005

2020-03-01 00:00:00

【问题】编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

order_num

order_date

答案：

SELECT order_num, order_date
FROM Orders
WHERE month(order_date) = '01' AND YEAR(order_date) = '2020'
ORDER BY order_date

SELECT order_num, order_date
FROM Orders
WHERE month(order_date) = '01' AND YEAR(order_date) = '2020'
ORDER BY order_date

也可以用通配符来做：

SELECT order_num, order_date
FROM Orders
WHERE order_date LIKE '2020-01%'
ORDER BY order_date

SELECT order_num, order_date
FROM Orders
WHERE order_date LIKE '2020-01%'
ORDER BY order_date

知识点：

日期格式：YYYY-MM-DD

YYYY-MM-DD

时间格式：HH:MM:SS

HH:MM:SS

日期和时间处理相关的常用函数：

函 数说 明ADDDATE()增加一个日期（天、周等）ADDTIME()增加一个时间（时、分等）CURDATE()返回当前日期CURTIME()返回当前时间DATE()返回日期时间的日期部分DATEDIFF计算两个日期之差DATE_FORMAT()返回一个格式化的日期或时间串DAY()返回一个日期的天数部分DAYOFWEEK()对于一个日期，返回对应的星期几HOUR()返回一个时间的小时部分MINUTE()返回一个时间的分钟部分MONTH()返回一个日期的月份部分NOW()返回当前日期和时间SECOND()返回一个时间的秒部分TIME()返回一个日期时间的时间部分YEAR()返回一个日期的年份部分

函 数说 明

函 数

说 明

ADDDATE()增加一个日期（天、周等）

ADDDATE()

ADDDATE()

增加一个日期（天、周等）

ADDTIME()增加一个时间（时、分等）

ADDTIME()

ADDTIME()

增加一个时间（时、分等）

CURDATE()返回当前日期

CURDATE()

CURDATE()

返回当前日期

CURTIME()返回当前时间

CURTIME()

CURTIME()

返回当前时间

DATE()返回日期时间的日期部分

DATE()

DATE()

返回日期时间的日期部分

DATEDIFF计算两个日期之差

DATEDIFF

DATEDIFF

计算两个日期之差

DATE_FORMAT()返回一个格式化的日期或时间串

DATE_FORMAT()

DATE_FORMAT()

返回一个格式化的日期或时间串

DAY()返回一个日期的天数部分

DAY()

DAY()

返回一个日期的天数部分

DAYOFWEEK()对于一个日期，返回对应的星期几

DAYOFWEEK()

DAYOFWEEK()

对于一个日期，返回对应的星期几

HOUR()返回一个时间的小时部分

HOUR()

HOUR()

返回一个时间的小时部分

MINUTE()返回一个时间的分钟部分

MINUTE()

MINUTE()

返回一个时间的分钟部分

MONTH()返回一个日期的月份部分

MONTH()

MONTH()

返回一个日期的月份部分

NOW()返回当前日期和时间

NOW()

NOW()

返回当前日期和时间

SECOND()返回一个时间的秒部分

SECOND()

SECOND()

返回一个时间的秒部分

TIME()返回一个日期时间的时间部分

TIME()

TIME()

返回一个日期时间的时间部分

YEAR()返回一个日期的年份部分

YEAR()

YEAR()

返回一个日期的年份部分

汇总数据

汇总数据

汇总数据相关的函数：

函 数说 明AVG()返回某列的平均值COUNT()返回某列的行数MAX()返回某列的最大值MIN()返回某列的最小值SUM()返回某列值之和

函 数说 明

函 数

说 明

AVG()返回某列的平均值

AVG()

AVG()

返回某列的平均值

COUNT()返回某列的行数

COUNT()

COUNT()

返回某列的行数

MAX()返回某列的最大值

MAX()

MAX()

返回某列的最大值

MIN()返回某列的最小值

MIN()

MIN()

返回某列的最小值

SUM()返回某列值之和

SUM()

SUM()

返回某列值之和

确定已售出产品的总数

确定已售出产品的总数

OrderItems 表代表售出的产品，quantity 代表售出商品数量。

OrderItems

quantity

quantity10100100010001215

quantity

quantity

10

10

100

100

1000

1000

10001

10001

2

2

15

15

【问题】编写 SQL 语句，确定已售出产品的总数。

答案：

SELECT Sum(quantity) AS items_ordered
FROM OrderItems

SELECT Sum(quantity) AS items_ordered
FROM OrderItems

确定已售出产品项 BR01 的总数

确定已售出产品项 BR01 的总数

OrderItems 表代表售出的产品，quantity 代表售出商品数量，产品项为 prod_id。

OrderItems

quantity

prod_id

quantityprod_id10AR01100AR101000BR0110001BR010

quantityprod_id

quantity

prod_id

10AR01

10

AR01

100AR10

100

AR10

1000BR01

1000

BR01

10001BR010

10001

BR010

【问题】修改创建的语句，确定已售出产品项（prod_id）为"BR01"的总数。

prod_id

答案：

SELECT Sum(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01'

SELECT Sum(quantity) AS items_ordered
FROM OrderItems
WHERE prod_id = 'BR01'

确定 Products 表中价格不超过 10 美元的最贵产品的价格

确定 Products 表中价格不超过 10 美元的最贵产品的价格

Products 表如下，prod_price 代表商品的价格。

Products

prod_price

prod_price9.496001000

prod_price

prod_price

9.49

9.49

600

600

1000

1000

【问题】编写 SQL 语句，确定 Products 表中价格不超过 10 美元的最贵产品的价格（prod_price）。将计算所得的字段命名为 max_price。

Products

prod_price

max_price

答案：

SELECT Max(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10

SELECT Max(prod_price) AS max_price
FROM Products
WHERE prod_price <= 10

分组数据

分组数据

GROUP BY：

GROUP BY

GROUP BY 子句将记录分组到汇总行中。

GROUP BY

GROUP BY 为每个组返回一个记录。

GROUP BY

GROUP BY 通常还涉及聚合COUNT，MAX，SUM，AVG 等。

GROUP BY

COUNT

MAX

SUM

AVG

GROUP BY 可以按一列或多列进行分组。

GROUP BY

GROUP BY 按分组字段进行排序后，ORDER BY 可以以汇总字段来进行排序。

GROUP BY

ORDER BY

HAVING：

HAVING

HAVING 用于对汇总的 GROUP BY 结果进行过滤。

HAVING

GROUP BY

HAVING 必须要与 GROUP BY 连用。

HAVING

GROUP BY

WHERE 和 HAVING 可以在相同的查询中。

WHERE

HAVING

HAVING vs WHERE：

HAVING

WHERE

WHERE：过滤指定的行，后面不能加聚合函数（分组函数）。

WHERE

HAVING：过滤分组，必须要与 GROUP BY 连用，不能单独使用。

HAVING

GROUP BY

返回每个订单号各有多少行数

返回每个订单号各有多少行数

OrderItems 表包含每个订单的每个产品

OrderItems

order_numa002a002a002a004a007

order_num

order_num

a002

a002

a002

a002

a002

a002

a004

a004

a007

a007

【问题】编写 SQL 语句，返回每个订单号（order_num）各有多少行数（order_lines），并按 order_lines 对结果进行升序排序。

order_num

order_lines

order_lines

答案：

SELECT order_num, Count(order_num) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines

SELECT order_num, Count(order_num) AS order_lines
FROM OrderItems
GROUP BY order_num
ORDER BY order_lines

知识点：

count(*),count(列名)都可以，区别在于，count(列名)是统计非 NULL 的行数；

count(*)

count(列名)

count(列名)

order by 最后执行，所以可以使用列别名；

order by

分组聚合一定不要忘记加上 group by ,不然只会有一行结果。

group by

每个供应商成本最低的产品

每个供应商成本最低的产品

有 Products 表，含有字段 prod_price 代表产品价格，vend_id 代表供应商 id

Products

prod_price

vend_id

vend_idprod_pricea0011100a00190.1b00191000b00196980b001920

vend_idprod_price

vend_id

prod_price

a0011100

a0011

100

a00190.1

a0019

0.1

b00191000

b0019

1000

b00196980

b0019

6980

b001920

b0019

20

【问题】编写 SQL 语句，返回名为 cheapest_item 的字段，该字段包含每个供应商成本最低的产品（使用 Products 表中的 prod_price），然后从最低成本到最高成本对结果进行升序排序。

cheapest_item

Products

prod_price

答案：

SELECT vend_id, Min(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item

SELECT vend_id, Min(prod_price) AS cheapest_item
FROM Products
GROUP BY vend_id
ORDER BY cheapest_item

返回订单数量总和不小于 100 的所有订单的订单号

返回订单数量总和不小于 100 的所有订单的订单号

OrderItems 代表订单商品表，包括：订单号 order_num 和订单数量 quantity。

OrderItems

order_num

quantity

order_numquantitya1105a21100a2200a41121a510a219a75

order_numquantity

order_num

quantity

a1105

a1

105

a21100

a2

1100

a2200

a2

200

a41121

a4

1121

a510

a5

10

a219

a2

19

a75

a7

5

【问题】请编写 SQL 语句，返回订单数量总和不小于 100 的所有订单号，最后结果按照订单号升序排序。

答案：

# 直接聚合
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING Sum(quantity) >= 100
ORDER BY order_num

# 子查询
SELECT a.order_num
FROM (SELECT order_num, Sum(quantity) AS sum_num
    FROM OrderItems
    GROUP BY order_num
    HAVING sum_num >= 100) a
ORDER BY a.order_num

# 直接聚合
SELECT order_num
FROM OrderItems
GROUP BY order_num
HAVING Sum(quantity) >= 100
ORDER BY order_num

# 子查询
SELECT a.order_num
FROM (SELECT order_num, Sum(quantity) AS sum_num
    FROM OrderItems
    GROUP BY order_num
    HAVING sum_num >= 100) a
ORDER BY a.order_num

知识点：

where：过滤过滤指定的行，后面不能加聚合函数（分组函数）。

where

having：过滤分组，与 group by 连用，不能单独使用。

having

group by

计算总和

计算总和

OrderItems 表代表订单信息，包括字段：订单号 order_num 和 item_price 商品售出价格、quantity 商品数量。

OrderItems

order_num

item_price

quantity

order_numitem_pricequantitya110105a211100a21200a421121a5510a2119a775

order_numitem_pricequantity

order_num

item_price

quantity

a110105

a1

10

105

a211100

a2

1

1100

a21200

a2

1

200

a421121

a4

2

1121

a5510

a5

5

10

a2119

a2

1

19

a775

a7

7

5

【问题】编写 SQL 语句，根据订单号聚合，返回订单总价不小于 1000 的所有订单号，最后的结果按订单号进行升序排序。

提示：总价 = item_price 乘以 quantity

答案：

SELECT order_num, Sum(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num

SELECT order_num, Sum(item_price * quantity) AS total_price
FROM OrderItems
GROUP BY order_num
HAVING total_price >= 1000
ORDER BY order_num

检查 SQL 语句

检查 SQL 语句

OrderItems 表含有 order_num 订单号

OrderItems

order_num

order_numa002a002a002a004a007

order_num

order_num

a002

a002

a002

a002

a002

a002

a004

a004

a007

a007

【问题】将下面代码修改正确后执行

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY items
HAVING COUNT(*) >= 3
ORDER BY items, order_num;

修改后：

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING items >= 3
ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING items >= 3
ORDER BY items, order_num;

使用子查询

使用子查询

子查询是嵌套在较大查询中的 SQL 查询，也称内部查询或内部选择，包含子查询的语句也称为外部查询或外部选择。简单来说，子查询就是指将一个 SELECT 查询（子查询）的结果作为另一个 SQL 语句（主查询）的数据来源或者判断条件。

SELECT

子查询可以嵌入 SELECT、INSERT、UPDATE 和 DELETE 语句中，也可以和 =、<、>、IN、BETWEEN、EXISTS 等运算符一起使用。

SELECT

INSERT

UPDATE

DELETE

=

<

>

IN

BETWEEN

EXISTS

子查询常用在 WHERE 子句和 FROM 子句后边：

WHERE

FROM

当用于 WHERE 子句时，根据不同的运算符，子查询可以返回单行单列、多行单列、单行多列数据。子查询就是要返回能够作为 WHERE 子句查询条件的值。

WHERE

当用于 FROM 子句时，一般返回多行多列数据，相当于返回一张临时表，这样才符合 FROM 后面是表的规则。这种做法能够实现多表联合查询。

FROM

FROM

注意：MySQL 数据库从 4.1 版本才开始支持子查询，早期版本是不支持的。

注意：MySQL 数据库从 4.1 版本才开始支持子查询，早期版本是不支持的。

用于 WHERE 子句的子查询的基本语法如下：

WHERE

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name operator
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name operator
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])

子查询需要放在括号( )内。

( )

operator 表示用于 WHERE 子句的运算符，可以是比较运算符（如 =, <, >, <> 等）或逻辑运算符（如 IN, NOT IN, EXISTS, NOT EXISTS 等），具体根据需求来确定。

operator

WHERE

=

<

>

<>

IN

NOT IN

EXISTS

NOT EXISTS

用于 FROM 子句的子查询的基本语法如下：

FROM

SELECT column_name [, column_name ]
FROM (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]) AS temp_table_name [, ...]
[JOIN type JOIN table_name ON condition]
WHERE condition;

SELECT column_name [, column_name ]
FROM (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]) AS temp_table_name [, ...]
[JOIN type JOIN table_name ON condition]
WHERE condition;

用于 FROM 的子查询返回的结果相当于一张临时表，所以需要使用 AS 关键字为该临时表起一个名字。

FROM

子查询需要放在括号 ( ) 内。

( )

可以指定多个临时表名，并使用 JOIN 语句连接这些表。

JOIN

返回购买价格为 10 美元或以上产品的顾客列表

返回购买价格为 10 美元或以上产品的顾客列表

OrderItems 表示订单商品表，含有字段订单号：order_num、订单价格：item_price；Orders 表代表订单信息表，含有顾客 id：cust_id 和订单号：order_num

OrderItems

order_num

item_price

Orders

id：cust_id

order_num

OrderItems 表:

OrderItems

order_numitem_pricea110a21a21a42a55a21a77

order_numitem_price

order_num

item_price

a110

a1

10

a21

a2

1

a21

a2

1

a42

a4

2

a55

a5

5

a21

a2

1

a77

a7

7

Orders 表：

Orders

order_numcust_ida1cust10a2cust1a2cust1a4cust2a5cust5a2cust1a7cust7

order_numcust_id

order_num

cust_id

a1cust10

a1

cust10

a2cust1

a2

cust1

a2cust1

a2

cust1

a4cust2

a4

cust2

a5cust5

a5

cust5

a2cust1

a2

cust1

a7cust7

a7

cust7

【问题】使用子查询，返回购买价格为 10 美元或以上产品的顾客列表，结果无需排序。

答案：

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT DISTINCT order_num
    FROM OrderItems
    where item_price >= 10)

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT DISTINCT order_num
    FROM OrderItems
    where item_price >= 10)

确定哪些订单购买了 prod_id 为 BR01 的产品（一）

确定哪些订单购买了 prod_id 为 BR01 的产品（一）

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date

OrderItems

prod_id

Orders

cust_id

order_date

OrderItems 表：

OrderItems

prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013

prod_idorder_num

prod_id

order_num

BR01a0001

BR01

a0001

BR01a0002

BR01

a0002

BR02a0003

BR02

a0003

BR02a0013

BR02

a0013

Orders 表：

Orders

order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00

order_numcust_idorder_date

order_num

cust_id

order_date

a0001cust102022-01-01 00:00:00

a0001

cust10

2022-01-01 00:00:00

a0002cust12022-01-01 00:01:00

a0002

cust1

2022-01-01 00:01:00

a0003cust12022-01-02 00:00:00

a0003

cust1

2022-01-02 00:00:00

a0013cust22022-01-01 00:20:00

a0013

cust2

2022-01-01 00:20:00

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

OrderItems

prod_id

Orders

cust_id

order_date

答案：

# 写法 1：子查询
SELECT cust_id,order_date
FROM Orders
WHERE order_num IN
    (SELECT order_num
     FROM OrderItems
     WHERE prod_id = 'BR01' )
ORDER BY order_date;

# 写法 2: 连接表
SELECT b.cust_id, b.order_date
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num AND a.prod_id = 'BR01'
ORDER BY order_date

# 写法 1：子查询
SELECT cust_id,order_date
FROM Orders
WHERE order_num IN
    (SELECT order_num
     FROM OrderItems
     WHERE prod_id = 'BR01' )
ORDER BY order_date;

# 写法 2: 连接表
SELECT b.cust_id, b.order_date
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num AND a.prod_id = 'BR01'
ORDER BY order_date

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）

你想知道订购 BR01 产品的日期，有表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date；Customers 表含有 cust_email 顾客邮件和 cust_id 顾客 id

OrderItems

prod_id

Orders

cust_id

order_date

Customers

cust_email

cust_id

OrderItems 表：

OrderItems

prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013

prod_idorder_num

prod_id

order_num

BR01a0001

BR01

a0001

BR01a0002

BR01

a0002

BR02a0003

BR02

a0003

BR02a0013

BR02

a0013

Orders 表：

Orders

order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00

order_numcust_idorder_date

order_num

cust_id

order_date

a0001cust102022-01-01 00:00:00

a0001

cust10

2022-01-01 00:00:00

a0002cust12022-01-01 00:01:00

a0002

cust1

2022-01-01 00:01:00

a0003cust12022-01-02 00:00:00

a0003

cust1

2022-01-02 00:00:00

a0013cust22022-01-01 00:20:00

a0013

cust2

2022-01-01 00:20:00

Customers 表代表顾客信息，cust_id 为顾客 id，cust_email 为顾客 email

Customers

cust_id

cust_email

cust_idcust_emailcust10cust10@cust.comcust1cust1@cust.comcust2cust2@cust.com

cust_idcust_email

cust_id

cust_email

cust10cust10@cust.com

cust10

cust10@cust.com

cust10@cust.com

cust1cust1@cust.com

cust1

cust1@cust.com

cust1@cust.com

cust2cust2@cust.com

cust2

cust2@cust.com

cust2@cust.com

【问题】返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（Customers 表中的 cust_email），结果无需排序。

prod_id

BR01

Customers

cust_email

提示：这涉及 SELECT 语句，最内层的从 OrderItems 表返回 order_num，中间的从 Customers 表返回 cust_id。

SELECT

OrderItems

order_num

Customers

cust_id

答案：

# 写法 1：子查询
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'))

# 写法 2: 连接表（inner join）
SELECT c.cust_email
FROM OrderItems a,Orders b,Customers c
WHERE a.order_num = b.order_num AND b.cust_id = c.cust_id AND a.prod_id = 'BR01'

# 写法 3：连接表（left join）
SELECT c.cust_email
FROM Orders a LEFT JOIN
  OrderItems b ON a.order_num = b.order_num LEFT JOIN
  Customers c ON a.cust_id = c.cust_id
WHERE b.prod_id = 'BR01'

# 写法 1：子查询
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'))

# 写法 2: 连接表（inner join）
SELECT c.cust_email
FROM OrderItems a,Orders b,Customers c
WHERE a.order_num = b.order_num AND b.cust_id = c.cust_id AND a.prod_id = 'BR01'

# 写法 3：连接表（left join）
SELECT c.cust_email
FROM Orders a LEFT JOIN
  OrderItems b ON a.order_num = b.order_num LEFT JOIN
  Customers c ON a.cust_id = c.cust_id
WHERE b.prod_id = 'BR01'

返回每个顾客不同订单的总金额

返回每个顾客不同订单的总金额

我们需要一个顾客 ID 列表，其中包含他们已订购的总金额。

OrderItems 表代表订单信息，OrderItems 表有订单号：order_num 和商品售出价格：item_price、商品数量：quantity。

OrderItems

OrderItems

order_num

item_price

quantity

order_numitem_pricequantitya000110105a000211100a00021200a001321121a0003510a0003119a000375

order_numitem_pricequantity

order_num

item_price

quantity

a000110105

a0001

10

105

a000211100

a0002

1

1100

a00021200

a0002

1

200

a001321121

a0013

2

1121

a0003510

a0003

5

10

a0003119

a0003

1

19

a000375

a0003

7

5

Orders 表订单号：order_num、顾客 id：cust_id

Orders

order_num

cust_id

order_numcust_ida0001cust10a0002cust1a0003cust1a0013cust2

order_numcust_id

order_num

cust_id

a0001cust10

a0001

cust10

a0002cust1

a0002

cust1

a0003cust1

a0003

cust1

a0013cust2

a0013

cust2

【问题】

编写 SQL 语句，返回顾客 ID（Orders 表中的 cust_id），并使用子查询返回 total_ordered 以便返回每个顾客的订单总数，将结果按金额从大到小排序。

Orders

cust_id

total_ordered

答案：

# 写法 1：子查询
SELECT o.cust_id, SUM(tb.total_ordered) AS `total_ordered`
FROM (SELECT order_num, SUM(item_price * quantity) AS total_ordered
    FROM OrderItems
    GROUP BY order_num) AS tb,
  Orders o
WHERE tb.order_num = o.order_num
GROUP BY o.cust_id
ORDER BY total_ordered DESC;

# 写法 2：连接表
SELECT b.cust_id, Sum(a.quantity * a.item_price) AS total_ordered
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC

# 写法 1：子查询
SELECT o.cust_id, SUM(tb.total_ordered) AS `total_ordered`
FROM (SELECT order_num, SUM(item_price * quantity) AS total_ordered
    FROM OrderItems
    GROUP BY order_num) AS tb,
  Orders o
WHERE tb.order_num = o.order_num
GROUP BY o.cust_id
ORDER BY total_ordered DESC;

# 写法 2：连接表
SELECT b.cust_id, Sum(a.quantity * a.item_price) AS total_ordered
FROM OrderItems a,Orders b
WHERE a.order_num = b.order_num
GROUP BY cust_id
ORDER BY total_ordered DESC

关于写法一详细介绍可以参考： issue#2402：写法 1 存在的错误以及修改方法。

issue#2402：写法 1 存在的错误以及修改方法

从 Products 表中检索所有的产品名称以及对应的销售总数

从 Products 表中检索所有的产品名称以及对应的销售总数

Products 表中检索所有的产品名称：prod_name、产品 id：prod_id

Products

prod_name

prod_id

prod_idprod_namea0001egga0002socketsa0013coffeea0003cola

prod_idprod_name

prod_id

prod_name

a0001egg

a0001

egg

a0002sockets

a0002

sockets

a0013coffee

a0013

coffee

a0003cola

a0003

cola

OrderItems 代表订单商品表，订单产品：prod_id、售出数量：quantity

OrderItems

prod_id

quantity

prod_idquantitya0001105a00021100a0002200a00131121a000310a000319a00035

prod_idquantity

prod_id

quantity

a0001105

a0001

105

a00021100

a0002

1100

a0002200

a0002

200

a00131121

a0013

1121

a000310

a0003

10

a000319

a0003

19

a00035

a0003

5

【问题】

编写 SQL 语句，从 Products 表中检索所有的产品名称（prod_name），以及名为 quant_sold 的计算列，其中包含所售产品的总数（在 OrderItems 表上使用子查询和 SUM(quantity) 检索）。

Products

prod_name

quant_sold

OrderItems

SUM(quantity)

答案：

# 写法 1：子查询
SELECT p.prod_name, tb.quant_sold
FROM (SELECT prod_id, Sum(quantity) AS quant_sold
    FROM OrderItems
    GROUP BY prod_id) AS tb,
  Products p
WHERE tb.prod_id = p.prod_id

# 写法 2：连接表
SELECT p.prod_name, Sum(o.quantity) AS quant_sold
FROM Products p,
  OrderItems o
WHERE p.prod_id = o.prod_id
GROUP BY p.prod_name（这里不能用 p.prod_id，会报错）

# 写法 1：子查询
SELECT p.prod_name, tb.quant_sold
FROM (SELECT prod_id, Sum(quantity) AS quant_sold
    FROM OrderItems
    GROUP BY prod_id) AS tb,
  Products p
WHERE tb.prod_id = p.prod_id

# 写法 2：连接表
SELECT p.prod_name, Sum(o.quantity) AS quant_sold
FROM Products p,
  OrderItems o
WHERE p.prod_id = o.prod_id
GROUP BY p.prod_name（这里不能用 p.prod_id，会报错）

连接表

连接表

JOIN 是“连接”的意思，顾名思义，SQL JOIN 子句用于将两个或者多个表联合起来进行查询。

连接表时需要在每个表中选择一个字段，并对这些字段的值进行比较，值相同的两条记录将合并为一条。连接表的本质就是将不同表的记录合并起来，形成一张新表。当然，这张新表只是临时的，它仅存在于本次查询期间。

使用 JOIN 连接两个表的基本语法如下：

JOIN

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_column1 = table2.common_column2;

SELECT table1.column1, table2.column2...
FROM table1
JOIN table2
ON table1.common_column1 = table2.common_column2;

table1.common_column1 = table2.common_column2 是连接条件，只有满足此条件的记录才会合并为一行。您可以使用多个运算符来连接表，例如 =、>、<、<>、<=、>=、!=、between、like 或者 not，但是最常见的是使用 =。

table1.common_column1 = table2.common_column2

between

like

not

当两个表中有同名的字段时，为了帮助数据库引擎区分是哪个表的字段，在书写同名字段名时需要加上表名。当然，如果书写的字段名在两个表中是唯一的，也可以不使用以上格式，只写字段名即可。

另外，如果两张表的关联字段名相同，也可以使用 USING子句来代替 ON，举个例子：

USING

ON

# join....on
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
ON c.cust_id = o.cust_id
ORDER BY c.cust_name

# 如果两张表的关联字段名相同，也可以使用USING子句：JOIN....USING()
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name

# join....on
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
ON c.cust_id = o.cust_id
ORDER BY c.cust_name

# 如果两张表的关联字段名相同，也可以使用USING子句：JOIN....USING()
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name

ON 和 WHERE 的区别：

ON

WHERE

连接表时，SQL 会根据连接条件生成一张新的临时表。ON 就是连接条件，它决定临时表的生成。

ON

WHERE 是在临时表生成以后，再对临时表中的数据进行过滤，生成最终的结果集，这个时候已经没有 JOIN-ON 了。

WHERE

所以总结来说就是：SQL 先根据 ON 生成一张临时表，然后再根据 WHERE 对临时表进行筛选。

SQL 允许在 JOIN 左边加上一些修饰性的关键词，从而形成不同类型的连接，如下表所示：

JOIN

连接类型说明INNER JOIN 内连接（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。LEFT JOIN / LEFT OUTER JOIN 左(外)连接返回左表中的所有行，即使右表中没有满足条件的行也是如此。RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接返回右表中的所有行，即使左表中没有满足条件的行也是如此。FULL JOIN / FULL OUTER JOIN 全(外)连接只要其中有一个表存在满足条件的记录，就返回行。SELF JOIN将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。CROSS JOIN交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

连接类型说明

连接类型

说明

INNER JOIN 内连接（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。

INNER JOIN 内连接

（默认连接方式）只有当两个表都存在满足条件的记录时才会返回行。

LEFT JOIN / LEFT OUTER JOIN 左(外)连接返回左表中的所有行，即使右表中没有满足条件的行也是如此。

LEFT JOIN / LEFT OUTER JOIN 左(外)连接

返回左表中的所有行，即使右表中没有满足条件的行也是如此。

RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接返回右表中的所有行，即使左表中没有满足条件的行也是如此。

RIGHT JOIN / RIGHT OUTER JOIN 右(外)连接

返回右表中的所有行，即使左表中没有满足条件的行也是如此。

FULL JOIN / FULL OUTER JOIN 全(外)连接只要其中有一个表存在满足条件的记录，就返回行。

FULL JOIN / FULL OUTER JOIN 全(外)连接

只要其中有一个表存在满足条件的记录，就返回行。

SELF JOIN将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。

SELF JOIN

将一个表连接到自身，就像该表是两个表一样。为了区分两个表，在 SQL 语句中需要至少重命名一个表。

CROSS JOIN交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

CROSS JOIN

交叉连接，从两个或者多个连接表中返回记录集的笛卡尔积。

下图展示了 LEFT JOIN、RIGHT JOIN、INNER JOIN、OUTER JOIN 相关的 7 种用法。

如果不加任何修饰词，只写 JOIN，那么默认为 INNER JOIN

JOIN

INNER JOIN

对于 INNER JOIN 来说，还有一种隐式的写法，称为 “隐式内连接”，也就是没有 INNER JOIN 关键字，使用 WHERE 语句实现内连接的功能

INNER JOIN

INNER JOIN

WHERE

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name;

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name;

返回顾客名称和相关订单号

返回顾客名称和相关订单号

Customers 表有字段顾客名称 cust_name、顾客 id cust_id

Customers

cust_name

cust_id

cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hex

cust_idcust_name

cust_id

cust_name

cust10andy

cust10

andy

cust1ben

cust1

ben

cust2tony

cust2

tony

cust22tom

cust22

tom

cust221an

cust221

an

cust2217hex

cust2217

hex

Orders 订单信息表，含有字段 order_num 订单号、cust_id 顾客 id

Orders

order_num

cust_id

order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217

order_numcust_id

order_num

cust_id

a1cust10

a1

cust10

a2cust1

a2

cust1

a3cust2

a3

cust2

a4cust22

a4

cust22

a5cust221

a5

cust221

a7cust2217

a7

cust2217

【问题】编写 SQL 语句，返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），并按顾客名称再按订单号对结果进行升序排序。你可以尝试用两个不同的写法，一个使用简单的等连接语法，另外一个使用 INNER JOIN。

Customers

cust_name

Orders

order_num

答案：

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name,o.order_num

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name,o.order_num;

# 隐式内连接
SELECT c.cust_name, o.order_num
FROM Customers c,Orders o
WHERE c.cust_id = o.cust_id
ORDER BY c.cust_name,o.order_num

# 显式内连接
SELECT c.cust_name, o.order_num
FROM Customers c
INNER JOIN Orders o
USING(cust_id)
ORDER BY c.cust_name,o.order_num;

返回顾客名称和相关订单号以及每个订单的总价

返回顾客名称和相关订单号以及每个订单的总价

Customers 表有字段，顾客名称：cust_name、顾客 id：cust_id

Customers

cust_name

cust_id

cust_idcust_namecust10andycust1bencust2tonycust22tomcust221ancust2217hex

cust_idcust_name

cust_id

cust_name

cust10andy

cust10

andy

cust1ben

cust1

ben

cust2tony

cust2

tony

cust22tom

cust22

tom

cust221an

cust221

an

cust2217hex

cust2217

hex

Orders 订单信息表，含有字段，订单号：order_num、顾客 id：cust_id

Orders

order_num

cust_id

order_numcust_ida1cust10a2cust1a3cust2a4cust22a5cust221a7cust2217

order_numcust_id

order_num

cust_id

a1cust10

a1

cust10

a2cust1

a2

cust1

a3cust2

a3

cust2

a4cust22

a4

cust22

a5cust221

a5

cust221

a7cust2217

a7

cust2217

OrderItems 表有字段，商品订单号：order_num、商品数量：quantity、商品价格：item_price

OrderItems

order_num

quantity

item_price

order_numquantityitem_pricea1100010a220010a31015a42550a51525a777

order_numquantityitem_price

order_num

quantity

item_price

a1100010

a1

1000

10

a220010

a2

200

10

a31015

a3

10

15

a42550

a4

25

50

a51525

a5

15

25

a777

a7

7

7

【问题】除了返回顾客名称和订单号，返回 Customers 表中的顾客名称（cust_name）和 Orders 表中的相关订单号（order_num），添加第三列 OrderTotal，其中包含每个订单的总价，并按顾客名称再按订单号对结果进行升序排序。

Customers

cust_name

Orders

order_num

OrderTotal

# 简单的等连接语法
SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name, o.order_num

# 简单的等连接语法
SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name, o.order_num
ORDER BY c.cust_name, o.order_num

注意，可能有小伙伴会这样写：

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name
ORDER BY c.cust_name,o.order_num

SELECT c.cust_name, o.order_num, SUM(quantity * item_price) AS OrderTotal
FROM Customers c,Orders o,OrderItems oi
WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num
GROUP BY c.cust_name
ORDER BY c.cust_name,o.order_num

这是错误的！只对 cust_name 进行聚类确实符合题意，但是不符合 GROUP BY 的语法。

cust_name

GROUP BY

select 语句中，如果没有 GROUP BY 语句，那么 cust_name、order_num 会返回若干个值，而 sum(quantity item_price) 只返回一个值，通过 group by cust_name 可以让 cust_name 和 sum(quantity item_price) 一一对应起来，或者说聚类，所以同样的，也要对 order_num 进行聚类。

GROUP BY

cust_name

order_num

sum(quantity * item_price)

group by

cust_name

cust_name

sum(quantity * item_price)

order_num

一句话，select 中的字段要么都聚类，要么都不聚类

一句话，select 中的字段要么都聚类，要么都不聚类

确定哪些订单购买了 prod_id 为 BR01 的产品（二）

确定哪些订单购买了 prod_id 为 BR01 的产品（二）

表 OrderItems 代表订单商品信息表，prod_id 为产品 id；Orders 表代表订单表有 cust_id 代表顾客 id 和订单日期 order_date

OrderItems

prod_id

Orders

cust_id

order_date

OrderItems 表：

OrderItems

prod_idorder_numBR01a0001BR01a0002BR02a0003BR02a0013

prod_idorder_num

prod_id

order_num

BR01a0001

BR01

a0001

BR01a0002

BR01

a0002

BR02a0003

BR02

a0003

BR02a0013

BR02

a0013

Orders 表：

Orders

order_numcust_idorder_datea0001cust102022-01-01 00:00:00a0002cust12022-01-01 00:01:00a0003cust12022-01-02 00:00:00a0013cust22022-01-01 00:20:00

order_numcust_idorder_date

order_num

cust_id

order_date

a0001cust102022-01-01 00:00:00

a0001

cust10

2022-01-01 00:00:00

a0002cust12022-01-01 00:01:00

a0002

cust1

2022-01-01 00:01:00

a0003cust12022-01-02 00:00:00

a0003

cust1

2022-01-02 00:00:00

a0013cust22022-01-01 00:20:00

a0013

cust2

2022-01-01 00:20:00

【问题】

编写 SQL 语句，使用子查询来确定哪些订单（在 OrderItems 中）购买了 prod_id 为 "BR01" 的产品，然后从 Orders 表中返回每个产品对应的顾客 ID（cust_id）和订单日期（order_date），按订购日期对结果进行升序排序。

OrderItems

prod_id

Orders

cust_id

order_date

提示：这一次使用连接和简单的等连接语法。

# 写法 1：子查询
SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01')
ORDER BY order_date

# 写法 2：连接表 inner join
SELECT cust_id, order_date
FROM Orders o INNER JOIN
  (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01') tb ON o.order_num = tb.order_num
ORDER BY order_date

# 写法 3：写法 2 的简化版
SELECT cust_id, order_date
FROM Orders
INNER JOIN OrderItems USING(order_num)
WHERE OrderItems.prod_id = 'BR01'
ORDER BY order_date

# 写法 1：子查询
SELECT cust_id, order_date
FROM Orders
WHERE order_num IN (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01')
ORDER BY order_date

# 写法 2：连接表 inner join
SELECT cust_id, order_date
FROM Orders o INNER JOIN
  (SELECT order_num
    FROM OrderItems
    WHERE prod_id = 'BR01') tb ON o.order_num = tb.order_num
ORDER BY order_date

# 写法 3：写法 2 的简化版
SELECT cust_id, order_date