# SQL语法-题库题解

主要以MySQL的语法解决问题
用实战的方式，熟悉SQL语法

# 目录

目录
《SQL必知必会》配套题单-题解
SQL大厂面试真题
参考资料

[TOC]

# 《SQL必知必会》配套题单-题解

链接：https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=298 (opens new window)

# 1.检索数据

# 60. 从 Customers 表中检索所有的 ID

select cust_id from Customers

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

记忆：distinct

select distinct(prod_id) from OrderItems

# 62. 检索所有列

select * from Customers

# 2.排序检索数据

# 63. 检索顾客名称并且排序

select cust_name 
from Customers 
order by cust_name desc

1
2
3

# 64. 对顾客ID和日期排序

select cust_id , order_num 
from Orders 
order by cust_id , order_date desc

1
2
3

# 65. 按照数量和价格排序

select quantity , item_price 
from OrderItems
order by quantity desc, item_price desc

1
2
3

# 66. 检查SQL语句

SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC;

1
2
3

# 3.过滤数据

# 67. 返回固定价格的产品

select prod_id , prod_name
from Products
where 9.49=prod_price

1
2
3

# 68.返回更高价格的产品

select prod_id , prod_name
from Products
where prod_price >= 9

1
2
3

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

select prod_name , prod_price
from Products 
where prod_price between 3 and 6
order by prod_price

1
2
3
4

# 70.返回更多的产品

select distinct(order_num)
from OrderItems
where quantity >= 100

1
2
3

# 4.高级数据过滤

# 71.检索供应商名称

select vend_name
from Vendors
where vend_country='USA' and vend_state='CA'

1
2
3

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

select order_num ,  prod_id , quantity
 from OrderItems 
 where prod_id in ( 'BR01' , 'BR02' , 'BR03' ) and quantity >= 100

1
2
3

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

select prod_name , prod_price
from Products
where prod_price between 3 and 6
order by prod_price

1
2
3
4

# 74.纠错2

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

1
2
3
4

# 5.用通配符进行过滤

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

select prod_name , prod_desc
from Products
where prod_desc like '%toy%'

1
2
3

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

select prod_name , prod_desc
from Products
where not prod_desc like '%toy%'

1
2
3

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

select prod_name , prod_desc
from Products
where 
prod_desc like '%toy%' 
and 
prod_desc like '%carrots%'

1
2
3
4
5
6

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

select prod_name , prod_desc
from Products
where 
prod_desc like '%toy%carrots%'

1
2
3
4

# 6.创建计算字段

# 79.别名

select vend_id
, vend_name as vname
,vend_address as vaddress
,vend_city as vcity
from Vendors
order by vend_name

1
2
3
4
5
6

# 80.打折

select prod_id , prod_price 
, prod_price*0.9 as sale_price
from Products

1
2
3

# 7.使用函数处理数据

# 81.顾客登录名

select cust_id, cust_name , 
upper( 
    concat( 
        substring(cust_contact,1,2)
        , substring(cust_city,1,3)
        )
    )
as user_login
from Customers

1
2
3
4
5
6
7
8
9

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

select order_num , order_date
from Orders
where substring(order_date,1,7)='2020-01'
order by order_date

1
2
3
4

# 8.汇总数据

# 83.确定已售出产品的总数

select sum(quantity) as items_ordered
from OrderItems

1
2

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

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'

1
2
3

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

select sum(quantity) as items_ordered
from OrderItems
where prod_id='BR01'

1
2
3

# 9.分组数据

# 86.返回每个订单号各有多少行数 (opens new window)

select order_num , count(*) as order_lines
from OrderItems
group by order_num
order by order_lines

1
2
3
4

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

select vend_id, min(prod_price)  as cheapest_item
from Products
group by vend_id
order by cheapest_item

1
2
3
4

# 88.确定最佳顾客

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

1
2
3
4
5

# 89.确定最佳顾客的另一种方式（一）

select
  order_num,
  sum(quantity * item_price) as total_price
from
  OrderItems
group by
  order_num
having
  sum(quantity * item_price) >= 1000
order by
  order_num

1
2
3
4
5
6
7
8
9
10
11

# 90.纠错3

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

1
2
3
4
5

# 10.使用子查询

# 91.返回购买价格为 10 美元或以上产品的顾客列表 (opens new window)

select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num

1
2
3
4
5

下面的不行！！！必须是上面的where

select order_num
from OrderItems
where sum(quantity)>=100   (错误答案)
group by order_num
order by order_num

1
2
3
4
5

# 92.确定哪些订单购买了 prod_id 为 BR01 的产品（一） (opens new window)

select cust_id,order_date
from Orders
where order_num in
(
select order_num
from OrderItems
where prod_id ='BR01')

1
2
3
4
5
6
7

# 93.返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一） (opens new window)

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'))

1
2
3
4
5
6
7
8

# 94.返回每个顾客不同订单的总金额 (opens new window)

坑点：OrderItems.order_num = Orders.order_num需要考虑，2个订单是否完整

select
  cust_id, 
  (
      select sum( item_price*quantity ) as total_ordered
      from OrderItems
      where OrderItems.order_num = Orders.order_num
      group by order_num
   ) as total_ordered
from
  Orders
order by  total_ordered desc

1
2
3
4
5
6
7
8
9
10
11

# 95.从 Products 表中检索所有的产品名称以及对应的销售 (opens new window)

select prod_name,
(
select sum(quantity) as quant_sold
from OrderItems
where Products.prod_id=OrderItems.prod_id
group by prod_name
)as quant_sold
from Products

1
2
3
4
5
6
7
8

# 11.联结表

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

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

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

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

# 100.确定最佳顾客的另一种方式（二）

# 12.创建高级联结

# 101.确定最佳顾客的另一种方式（二）

# 102.检索每个顾客的名称和所有的订单号（二）

# 103.返回产品名称和与之相关的订单号

# 104.返回产品名称和每一项产品的总订单数

# 105.列出供应商及其可供产品的数量

# 13.组合查询

# 106.将两个 SELECT 语句结合起来(一)

select
  prod_id,
  quantity
from
  OrderItems
where
  quantity = 100

union

select
  prod_id,
  quantity
from
  OrderItems
where
  prod_id like 'BNBG%';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 107.将两个 SELECT 语句结合起来(一)

# 108.组合 Products 表中的产品名称和 Customers 表中的顾客名称

# 109.纠错4

# SQL大厂面试真题

链接：https://www.nowcoder.com/exam/oj?page=1&tab=SQL%E7%AF%87&topicId=268 (opens new window)

# 1.某音短视频

# 156 各个视频的平均完播率

# 2.用户增长场景（某度信息流）

# 3.电商场景（某东商城）

# 4.出行场景（某滴打车）

# 5.某宝店铺分析（电商模式）

# 6.牛客直播课分析（在线教育行业）

# 7.某乎问答（内容行业）

# 190 某乎问答11月份日人均回答量

# 191 某乎问答高质量的回答中用户属于各级别的数量

# 192 某乎问答单日回答问题数大于等于3个的所有用户

# 193 某乎问答回答过教育类问题的用户里有多少用户回答过

# 194.某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级

# 参考资料

《SQL必知必会》
- 还有人整理了：CyC2018的SQL语法 (opens new window)
《MySQL必知必会》
廖雪峰的SQL (opens new window)
自学SQL网，站点 (opens new window)『有实践题』
知乎上关于SQL练习的平台 (opens new window)
阿里巴巴，SQL代码编码原则和规范 (opens new window)
- 恒生电子，SQL编码规范2020