牛客-SQL练习

小简计划

• 入门篇：【题数：39道题】【难度：🌟🌟｜ 用时：3☀️】
• 必会篇：【题数：50道题】【难度：🌟｜用时：3☀️】
• 进阶篇：【题数：46道题】【难度：🌟🌟🌟｜用时：7☀️】
• 真题篇：【题数：39道题】【难度：🌟🌟🌟｜用时：7☀️】

一、SQL入门篇

• 🌟入门篇：39道题
• 🕸题目地址：牛客网
• 🚩小简计划：预计3天写完
• 🎯开始时间：02-28 完成15道、03-01 完成13道、03-02 完成11道
• 🎉结束时间：03-02 按时完成🥳

01-基础查询

SQL1 查询多列

题目地址：查询多列_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university FROM user_profile

SQL2 查询所有列

题目地址：查询所有列_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT * FROM user_profile

SQL3 查询结果去重-DISTINCT

题目地址：查询结果去重_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT DISTINCT university FROM user_profile

题解2：

SELECT university FROM user_profile GROUP BY university

SQL4 查询结果限制返回行数-LIMIT

题目地址：查询结果限制返回行数_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT device_id FROM user_profile LIMIT 2

题解2：

SELECT device_id FROM user_profile LIMIT 0,2

• LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。

• LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

  • 如果只给定一个参数，它表示返回最大的记录行数目。
  • 如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。
  • 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1。

• 初始记录行的偏移量是 0(而不是 1)。

SQL5 将查询后的列重新命名-AS

题目地址：将查询后的列重新命名_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2

02-条件查询

SQL36 查找后排序-ASC

题目地址：查找后排序_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,age FROM user_profile ORDER BY age ASC

• 升序（ASC）
• 降序（DESC）

SQL37 查找后多列排序-ORDER BY

题目地址：查找后多列排序_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gpa,age FROM user_profile ORDER BY gpa ASC,age ASC

SQL38 查找后降序排列-DESC

题目地址：

题解：

SELECT device_id,gpa,age FROM user_profile ORDER BY gpa DESC,age DESC

SQL6 查找学校是北大的学生信息-=

题目地址：查找学校是北大的学生信息_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,university FROM user_profile WHERE university="北京大学"

SQL7 查找年龄大于24岁的用户信息->

题目地址：查找年龄大于24岁的用户信息_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university FROM user_profile WHERE age > 24

SQL8 查找某个年龄段的用户信息-BETWEEN AND

题目地址：查找某个年龄段的用户信息_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT device_id,gender,age FROM user_profile WHERE age>=20 AND age<=23

题解2：

SELECT device_id,gender,age FROM user_profile WHERE BETWEEN 20 AND 23

SQL9 查找除复旦大学的用户信息-NOT IN

题目地址：查找除复旦大学的用户信息_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT device_id,gender,age,university FROM user_profile WHERE university NOT IN ("复旦大学")

题解2：

SELECT device_id,gender,age,university FROM user_profile WHERE university != "复旦大学"

SQL10 用where过滤空值练习-IS NOT NULL

题目地址：用where过滤空值练习_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university FROM user_profile WHERE age IS NOT NULL

SQL11 高级操作符练习(1)-AND

题目地址：高级操作符练习(1)_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE gender = 'male' AND gpa > 3.5

SQL12 高级操作符练习(2)-OR

题目地址：高级操作符练习(2）_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university = '北京大学' OR gpa > 3.7

SQL13 WHERE IN和NOT IN

题目地址：Where in 和Not in_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university IN ('北京大学','复旦大学','山东大学')

题解2：

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE university NOT IN ('浙江大学')

SQL14 操作符混合运用-OR、AND

题目地址：操作符混合运用_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,gender,age,university,gpa FROM user_profile WHERE (university='山东大学' and gpa > 3.5)
OR (university="复旦大学" and gpa > 3.8)

SQL15 查看学校名称中含北京的用户-LIKE

题目地址：查看学校名称中含北京的用户_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT device_id,age,university FROM user_profile WHERE university LIKE "%北京%"

• 列名 [NOT] LIKE

• 匹配串中可包含如下四种通配符：

• _：匹配任意一个字符；

• %：匹配0个或多个字符；

• [ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；

• [^ ]：不匹配[ ]中的任意一个字符。

03-高级查询

SQL16 查找GPA最高值-MAX

题目地址：查找GPA最高值_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT MAX(gpa) FROM user_profile WHERE university="复旦大学"

SQL17 计算男生人数以及平均GPA-AVG、ROUND、COUNT

题目地址：计算男生人数以及平均GPA_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT COUNT(gender) AS 'male_num',ROUND(AVG(gpa),1) AS 'avg_gpa' FROM user_profile WHERE gender="male"

• 此题要注意的是暗含条件，保留一位小数
• 使用ROUND()函数，ROUND返回一个数值，舍入到指定的长度或精度

SQL18 分组计算练习题-GROUP BY

题目地址：分组计算练习题_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT gender,university,COUNT(device_id) AS 'user_num',AVG(active_days_within_30) AS 'avg_active_day',AVG(question_cnt) AS 'avg_question_cnt' FROM user_profile GROUP BY gender,university 

SQL19 分组过滤练习题-HAVING

题目地址：分组过滤练习题_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT university,AVG(question_cnt) AS 'avg_question_cnt',AVG(answer_cnt) AS 'avg_answer_cnt' FROM user_profile GROUP BY university HAVING avg_question_cnt < 5 OR avg_answer_cnt < 20 

• HAVING子句来指定一组行或聚合的过滤条件。

• HAVING子句通常与 GROUP BY 子句一起使用

SQL20 分组排序练习题-ORDER BY

题目地址：分组排序练习题_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT university,AVG(question_cnt) AS avg_question_cnt FROM user_profile GROUP BY university ORDER BY avg_question_cnt ASC

04-多表查询

SQL21 浙江大学用户题目回答情况-WHERE IN

题目地址：浙江大学用户题目回答情况_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT device_id,question_id,result FROM question_practice_detail WHERE device_id 
IN (SELECT device_id FROM user_profile WHERE university='浙江大学')

题解2：

SELECT t1.device_id, t1.question_id, t1.result
FROM question_practice_detail AS t1
INNER JOIN user_profile AS t2
ON t2.device_id = t1.device_id AND t2.university='浙江大学'

SQL22 统计每个学校的答过题的用户的平均答题数-INNER IN

题目地址：统计每个学校的答过题的用户的平均答题数_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT t1.university, ROUND(COUNT(t2.question_id)/ COUNT(DISTINCT t2.device_id),4)AS avg_answer_cnt
FROM question_practice_detail AS t2
INNER JOIN user_profile as t1
ON t1.device_id = t2.device_id
GROUP BY university
ORDER BY university ASC

SQL23 统计每个学校各难度的用户平均刷题数-LEFT JOIN

题目地址：统计每个学校各难度的用户平均刷题数_牛客题霸_牛客网 (nowcoder.com)

题解：

问题分解：

• 每个学校：按学校分组group by university
• 不同难度：按难度分组group by difficult_level
• 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
• 来自上面信息三个表，需要联表，t1与t2用device_id连接，t3与t2用question_id连接。

SELECT 
        t1.university,
        t3.difficult_level,
        ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id), 4) AS avg_answer_cnt
FROM question_practice_detail AS t2
LEFT JOIN user_profile AS t1
ON t1.device_id = t2.device_id
LEFT JOIN question_detail AS t3
ON t3.question_id = t2.question_id
GROUP BY t1.university, t3.difficult_level

题解2:

SELECT 
    t1.university,
    t3.difficult_level,
    ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM 
    user_profile t1,
    question_practice_detail t2,
    question_detail t3
WHERE
    t1.device_id = t2.device_id
    and 
    t2.question_id = t3.question_id
GROUP BY t1.university,t3.difficult_level;

SQL24 统计每个用户的平均刷题数-INNER JOIN

题目地址：统计每个用户的平均刷题数_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT 
    t1.university,
    t3.difficult_level,
    ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM 
    user_profile AS t1,
    question_practice_detail AS t2,
    question_detail AS t3
WHERE
    t1.device_id = t2.device_id
    and
    t2.question_id = t3.question_id
    and
    university ="山东大学"
GROUP BY  t3.difficult_level

题解2:

SELECT 
    university,
    difficult_level,
    ROUND(COUNT(t2.question_id) / COUNT(DISTINCT t2.device_id),4) AS avg_answer_cnt
FROM question_practice_detail AS t2
INNER JOIN user_profile AS t1
ON t1.device_id = t2.device_id AND t1.university="山东大学"
INNER JOIN question_detail AS t3
ON t3.question_id = t2.question_id
GROUP BY difficult_level

SQL25 查找山东大学或者性别为男生的信息-UNION ALL

题目地址：查找山东大学或者性别为男生的信息_牛客题霸_牛客网 (nowcoder.com)

题解：

• 限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；

• 分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

• 默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL

SELECT device_id,gender,age,gpa
FROM user_profile
WHERE university='山东大学'
UNION ALL
SELECT device_id, gender, age, gpa
FROM user_profile
WHERE gender='male'

05-必会的常用函数

SQL26 计算25岁以上和以下的用户数量-IF、COUNT(*)

题目地址：计算25岁以上和以下的用户数量_牛客题霸_牛客网 (nowcoder.com)

CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

CASE
WHEN 布尔表达式1 THEN 结果表达式1
WHEN 布尔表达式2 THEN 结果表达式2 …
WHEN 布尔表达式n THEN 结果表达式n
[ ELSE 结果表达式n+1 ]
END

IF函数

IF( expr1 , expr2 , expr3 )

• expr1 的值为 TRUE，则返回值为 expr2
• expr1 的值为FALSE，则返回值为 expr3

题解1:

SELECT CASE 
        WHEN age < 25 OR age is null THEN '25岁以下'
        WHEN age >= 25 THEN '25岁及以上'
                END age_cut,COUNT(*) AS number
FROM user_profile
GROUP BY age_cut

题解2:

SELECT CASE 
        WHEN age >= 25 THEN '25岁及以上'
        ELSE '25岁以下'
                END age_cut,COUNT(*) AS number
FROM user_profile
GROUP BY age_cut

题解3:

SELECT IF(age >= 25,"25岁及以上","25岁以下") AS age_cut,count(*) AS number
FROM user_profile
GROUP BY age_cut;

SQL27 查看不同年龄段的用户明细-CASE

题目地址：查看不同年龄段的用户明细_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT 
    device_id,
    gender,
    CASE 
        WHEN age >= 25 THEN "25岁及以上"
        WHEN age < 20 THEN "20岁以下"
        WHEN 20 <= age <= 24 THEN "20-24岁"
         ELSE "其他"
    END AS age_cut
FROM user_profile

SQL28 计算用户8月每天的练题数量-YEAR

题目地址：计算用户8月每天的练题数量_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT DAY(date) AS day,COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date)="2021" and MONTH(date)="08"
GROUP BY day

SQL29 计算用户的平均次日留存率-DATE_ADD

题目地址：计算用户的平均次日留存率_牛客题霸_牛客网 (nowcoder.com)

题解：

次日留存率 = 去重的数据表中符合次日留存的条目数目 / 去重的数据表中所有条目数目

SELECT 
    COUNT(t2.device_id) / COUNT(t1.device_id) AS avg_ret 
FROM 
    (SELECT DISTINCT device_id, date FROM question_practice_detail)as t1
LEFT JOIN
    (SELECT DISTINCT device_id, date FROM question_practice_detail) AS t2
ON t1.device_id = t2.device_id AND t2.date = DATE_ADD(t1.date, interval 1 day)

SQL30 统计每种性别的人数-substring_index

题目地址：统计每种性别的人数_牛客题霸_牛客网 (nowcoder.com)

substring_index函数

substring_index(str,delim,count)

• str:要处理的字符串
• delim:分隔符
• count:计数

str = 'www.wikidm.cn'

//取前面
substring_index(str,'.',1)
结果是：www

substring_index(str,'.',2)
结果是：www.wikidm

//取后面
substring_index(str,'.',-1)
结果为：cn

substring_index(str,'.',-2)
结果为：wikidm.cn

//取中间
substring_index(substring_index(str,'.',-2),'.',1);
结果为：wikidm

题解1:

SELECT  substring_index(profile,',',-1) AS gender,  COUNT(*) AS number
FROM user_submit
GROUP BY gender

题解2：

SELECT  CASE 
    WHEN `profile` LIKE "%,male" THEN 'male'
    WHEN `profile` LIKE "%,female" THEN 'female'
    END AS gender,  COUNT(device_id) AS number
FROM user_submit
GROUP BY gender

题解3:

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

SQL32 截取出年龄-SUBSTR

题目地址：截取出年龄_牛客题霸_牛客网 (nowcoder.com)

题解1：

SELECT 
    SUBSTRING_INDEX(SUBSTRING_INDEX(profile,',',-2),',',1) AS age,
    COUNT(*) AS number 
FROM user_submit
GROUP BY age

SUBSTR函数

截取字符串

substr(string str,num start,num length);

• str为字符串；
• start为起始位置；
• length为长度。

题解2:

SELECT
    SUBSTR(profile,12,2) AS age,
    COUNT(*) AS number
FROM user_submit
GROUP BY age

SQL31 提取博客URL中的用户名-replace

题目地址：提取博客URL中的用户名_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT
# 替换法 replace(string, '被替换部分','替换后的结果')
  device_id, replace(blog_url,'http:/url/','') as user_name
 
# 删除法 trim('被删除字段' from 列名)
# device_id, trim('http:/url/' from blog_url) as user_name
 
# 截取法 substr(string, start_point, length*可选参数*)
# device_id, substr(blog_url,11,length(blog_url)-10) as user_nam

# 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
# device_id, substring_index(blog_url,'/',-1) as user_name
 
FROM user_submit

SQL33 找出每个学校GPA最低的同学

题目地址：找出每个学校GPA最低的同学_牛客题霸_牛客网 (nowcoder.com)

题解1：用 group by 把学校分组，然后计算得到每个学校最低 gpa，再去找这个学校里和这个 gpa 相等的同学 device_id

SELECT device_id,university,gpa
FROM user_profile
WHERE (university,gpa) 
    IN (SELECT university,MIN(gpa) FROM user_profile GROUP BY university)
ORDER BY university ASC

题解2:窗口函数

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

窗口函数语法：通俗易懂的学会：SQL窗口函数

SELECT device_id,university,gpa
FROM (SELECT *,rank() over (partition by university
                order by gpa) AS rk FROM user_profile)  AS uni_min
WHERE uni_min.rk=1
ORDER BY university 

06-综合练习

SQL34 统计复旦用户8月练题情况-SUM、IF

题目地址：统计复旦用户8月练题情况_牛客题霸_牛客网 (nowcoder.com)

题解：

SELECT
    t1.device_id,
    t1.university,
    SUM(IF(result IS NOT NULL, 1, 0)) AS questino_cnt,
    SUM(IF(t2.result = "right",1,0)) AS right_question_cnt
FROM 
    user_profile AS t1
    LEFT JOIN question_practice_detail AS t2
    ON t1.device_id = t2.device_id AND MONTH(t2.date) = 8
WHERE  university = "复旦大学"
GROUP BY t1.device_id;

SQL35 浙大不同难度题目的正确率-SUM、IF

题目地址：

题解：题解 | #浙大不同难度题目的正确率#_牛客博客 (nowcoder.net)

SELECT
    difficult_level,
    sum(if(t2.result='right', 1, 0)) / count(t2.question_id) as correct_rate
FROM 
    user_profile AS t1
    INNER JOIN question_practice_detail AS t2
    ON t1.device_id = t2.device_id 
    INNER JOIN question_detail AS t3
    ON t2.question_id = t3.question_id 
WHERE  university = "浙江大学"
GROUP BY difficult_level
ORDER BY correct_rate

SQL39 21年8月份练题总数-date_format

题目地址：21年8月份练题总数_牛客题霸_牛客网 (nowcoder.com)

题解1：date_format

SELECT 
    COUNT(DISTINCT device_id) AS did_cnt,
    COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE date_format(date, '%Y-%m')='2021-08'

题解2：YEAR、MONTH

SELECT 
    COUNT(DISTINCT device_id) AS did_cnt,
    COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date) = 2021 AND MONTH(date) = 08

题解3：LIKE

SELECT 
    COUNT(DISTINCT device_id) AS did_cnt,
    COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE date LIKE "2021-08%"

二、SQL必知必会

• 🌟必知必会篇：50道题
• 🕸题目地址：牛客网
• 📚配套书籍：MySQL必知必会（文字版）.pdf
• 🚩小简计划：预计3天写完
• 🎯开始时间：03-04 完成 26 道、03-05 完成 24 道
• 🎉结束时间：03-05 提前一天完成🥳

01-检索数据

SQL1 从 Customers 表中检索所有的 ID

题解：

SELECT cust_id FROM Customers

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

题解：

SELECT DISTINCT prod_id FROM OrderItems

SQL3 仅检索顾客的ID

题解：

SELECT * FROM Customers
-- SELECT cust_id FROM Customers

02-排序检索数据

SQL4 检索顾客名称并且排序-DESC

题解：

SELECT cust_name FROM Customers ORDER BY cust_name DESC

SQL5 对顾客ID和日期排序-DESC

题解：

SELECT cust_id,order_num FROM Orders   ORDER BY cust_id,order_date DESC

SQL6 按照数量和价格排序-DESC

题解：

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

SQL7 检查SQL语句-DESC

题解：

SELECT vend_name 
FROM Vendors 
ORDER BY vend_name DESC

03-过滤数据

SQL8 返回固定价格的产品-=

题解：

SELECT prod_id,prod_name
FROM Products
WHERE prod_price = 9.49

SQL9 返回更高价格的产品->+

题解：

SELECT prod_id,prod_name
FROM Products
WHERE prod_price >= 9

SQL10 返回产品并且按照价格排序-BETWEEN AND

题解：

SELECT prod_name,prod_price
FROM Products
WHERE prod_price between 3 and 6
ORDER BY prod_price

SQL11 返回更多的产品->=

题解：

SELECT DISTINCT order_num
FROM OrderItems
WHERE quantity >= 100

04-高级数据过滤-in

SQL12 检索供应商名称-AND

题解：

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

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

题解：

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

SQL14 返回所有价格在3美元到6美元之间-BETWEEN AND

题解：

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

SQL15 纠错2-AND

题解：

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

05-用通配符进行过滤-like

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

题解：

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

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

题解：

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

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

题解：

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

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

题解：

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

06-创建计算字段

SQL20 别名-AS

题解：

SELECT vend_id,vend_name AS vname,vend_address AS vaddress,vend_city AS vcity
FROM Vendors 
ORDER BY vname

SQL21 打折-AS

题解：

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

07-使用函数处理数据

SQL22 顾客登录名-concat、substring、upper

题解：

• 字符串的截取：substring(字符串，起始位置，截取字符数）
• 字符串的拼接：concat(字符串1，字符串2，字符串3,…)
• 字母大写：upper(字符串）

SELECT 
    cust_id,
    cust_name,
    upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) AS user_login
FROM Customers 

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

题解1：

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

题解2:

SELECT order_num,order_date
FROM Orders 
WHERE date_format(order_date,'%Y-%m')='2020-01'
ORDER BY order_date

08-汇总数据-sum、count、max

SQL24 确定已售出产品的总数-sum

题解：

SELECT  sum(quantity) AS items_ordered
FROM OrderItems

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

题解：

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

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

题解：

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

09-分组数据-group by

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

题解：

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

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

题解：

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

SQL29 确定最佳顾客-HAVING

题解：

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

SQL30 确定最佳顾客的另一种方式（一）-HAVING

题解：

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

SQL31 纠错3-HAVING

题解：

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

10-使用子查询

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

题解：

SELECT cust_id
FROM Orders 
WHERE order_num in(
    SELECT order_num 
    FROM OrderItems 
    GROUP BY order_num 
    HAVING  SUM(item_price) >= 10
)

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

题解：

SELECT cust_id,order_date
FROM Orders 
WHERE order_num in(
    SELECT order_num 
    FROM OrderItems 
    WHERE  prod_id = 'BR01'
    ORDER BY cust_id,order_date
)

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

题解：

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

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

题解：

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

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

题解：

select prod_name, SUM(quantity) quant_sold 
from Products AS a, OrderItems AS b 
where a.prod_id = b.prod_id
group by prod_name

11-联结表-where、join

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

联结表：

• 1、使用where。 from 表1，表2 where 表1.列名x = 表2.列名x
• 2、使用inner join。from 表1 inner join 表2 on 表1.列名x=表2.列名x

题解1：

SELECT cust_name,order_num
FROM Customers,Orders
WHERE Customers.cust_id = Orders.cust_id
ORDER BY cust_name,order_num

题解2:

SELECT cust_name,order_num
FROM Customers AS t1
INNER JOIN Orders AS t2
ON t1.cust_id = t2.cust_id
ORDER BY cust_name,order_num

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

题解1：

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

题解2：

select cust_name,Orders.order_num,sum(quantity*item_price) as OrderTotal
from Orders
inner join Customers on Orders.cust_id = Customers.cust_id
inner join OrderItems on Orders.order_num = OrderItems.order_num
group by cust_name,Orders.order_num
order by cust_name,Orders.order_num

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

题解1：

SELECT cust_id,order_date
FROM OrderItems,Orders
WHERE OrderItems.order_num = Orders.order_num AND prod_id = "BR01"
ORDER BY order_date

题解2：

SELECT cust_id, order_date
FROM Orders 
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
WHERE prod_id = "BR01"
ORDER BY order_date 

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

题解1：

SELECT cust_email
FROM OrderItems,Orders,Customers
WHERE OrderItems.order_num = Orders.order_num 
AND Orders.cust_id = Customers.cust_id AND prod_id = "BR01"
ORDER BY order_date

题解2：

SELECT cust_email
FROM Orders 
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
INNER JOIN Customers ON Orders.cust_id = Customers.cust_id
WHERE prod_id = "BR01"
ORDER BY order_date 

SQL41 确定最佳顾客的另一种方式（二）

题解：

SELECT cust_name,sum(item_price*quantity) AS total_price
FROM Orders 
INNER JOIN OrderItems ON OrderItems.order_num = Orders.order_num
INNER JOIN Customers ON Orders.cust_id = Customers.cust_id
GROUP BY Customers.cust_name
HAVING total_price >= 1000
ORDER BY total_price 

12-创建高级联结-Join

SQL42 检索每个顾客的名称和所有的订单号（一）

题解：

SELECT cust_name,order_num
FROM Orders 
INNER JOIN Customers ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name

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

• 内联结：inner join。取两列的交集。
• 外联结：
• • left join。左连接，以左边表的列为主，取两列的交集，对于不在右边列存在的名称取null。
• • right join。右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null。

题解：

SELECT cust_name,order_num
FROM Customers 
LEFT JOIN Orders ON Customers.cust_id = Orders.cust_id
ORDER BY cust_name

SQL44 返回产品名称和与之相关的订单号

题解：

SELECT prod_name,order_num
FROM Products 
LEFT JOIN  OrderItems ON Products.prod_id = OrderItems.prod_id
ORDER BY prod_name

SQL45 返回产品名称和每一项产品的总订单数

题解：

SELECT prod_name, COUNT(order_num) AS orders
FROM Products 
LEFT JOIN  OrderItems ON Products.prod_id = OrderItems.prod_id
GROUP BY prod_name
ORDER BY prod_name

SQL46 列出供应商及其可供产品的数量

SELECT Vendors.vend_id,COUNT(prod_id) AS prod_id
FROM Vendors 
LEFT JOIN  Products ON Products.vend_id = Vendors.vend_id
GROUP BY vend_id
ORDER BY vend_id

13-组合查询-union

SQL47 将两个 SELECT 语句结合起来（一）

题解：

SELECT prod_id,quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE "BNBG%"

SQL48 将两个 SELECT 语句结合起来（二）

题解：

SELECT prod_id,quantity
FROM OrderItems
WHERE quantity = 100
UNION
SELECT prod_id,quantity
FROM OrderItems
WHERE prod_id LIKE "BNBG%"
ORDER BY prod_id

SQL49 组合 Products 表中的产品名称和 Customers 表中的顾客名称

题解：

SELECT prod_name FROM Products
UNION
SELECT cust_name FROM Customers
ORDER BY prod_name

SQL50 纠错4

题解：

SELECT cust_name, cust_contact, cust_email FROM Customers 
WHERE cust_state = 'MI' 
#ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email FROM Customers 
WHERE cust_state = 'IL'
ORDER BY cust_name;

三、SQL进阶篇

• 🌟进阶篇：46道题
• 🕸题目地址：牛客网
• 🚩小简计划：预计5天写完
• 🎯开始时间：03-07 13道 03-08 5道
• 🎉结束时间：03-0x

01-增删改操作

SQL1 插入记录（一）

题解：

INSERT INTO exam_record VALUES(null,1001,9001,'2021-09-01 22:11:12','2021-09-01 23:01:12',90),(null,1002,9002,'2021-09-04 07:01:02',null,null)

SQL2 插入记录（二）

• 把一个表的数据插入到另一个表中(指定字段)

INSERT  INTO 目标表 (字段1, 字段2, ...) SELECT 字段1, 字段2,... FROM 来源表  [WHERE key=value]

题解：

INSERT INTO exam_record_before_2021(uid, exam_id, start_time, submit_time, score)
SELECT uid, exam_id, start_time, submit_time, score
FROM exam_record
WHERE YEAR(submit_time) < '2021';

SQL3 插入记录（三）-replace

• replace into 跟 insert into 功能类似

• 不同点在于：replace into 首先尝试插入数据到表中

  • 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据；

  • 否则，直接插入新数据。

• 要注意的是：插入数据的表必须有主键或者是唯一索引！否则的话，replace into 会直接插入数据，这将导致表中出现重复的数据。

题解1：

REPLACE INTO examination_info VALUES(null,9003,"SQL","hard",90,"2021-01-01 00:00:00")

题解2：

DELETE FROM examination_info
WHERE exam_id=9003;
INSERT INTO examination_info
VALUES(NULL,9003, 'SQL','hard', 90, '2021-01-01 00:00:00')

SQL4 更新记录（一）

题解：

UPDATE examination_info 
SET tag = "Python" 
WHERE tag = "PYTHON"

SQL5 更新记录（二）

题解：

UPDATE exam_record 
SET submit_time = "2099-01-01 00:00:00",score = 0 
WHERE start_time < "2021-09-01" AND submit_time is null

SQL6 删除记录（一）-TIMESTAMPDIFF

时间差函数：

• TIMESTAMPDIFF(interval, time_start, time_end)可计算time_start-time_end的时间差，单位以指定的interval为准，常用可选：
  • SECOND 秒
  • MINUTE 分钟（返回秒数差除以60的整数部分）
  • HOUR 小时（返回秒数差除以3600的整数部分）
  • DAY 天数（返回秒数差除以3600*24的整数部分）
  • MONTH 月数
  • YEAR 年数

题解：

DELETE FROM exam_record 
WHERE timestampdiff(minute,start_time,submit_time) < 5 AND score < 60

SQL7 删除记录（二）-LIMIT

题解：

DELETE FROM exam_record 
WHERE submit_time is null OR timestampdiff(minute,start_time,submit_time) < 5
ORDER BY start_time
LIMIT 3

SQL8 删除记录（三）-truncate

1.DROP TABLE　清除数据并且销毁表，是一种数据库定义语言(DDL Data Definition Language), 执行后不能撤销，被删除表格的关系，索引，权限等等都会被永久删除。

2.TRUNCATE TABLE　只清除数据，保留表结构，列，权限，索引，视图，关系等等，相当于清零数据，是一种数据库定义语言(DDL Data Definition Language)，执行后不能撤销。

3.DELETE TABLE　删除（符合某些条件的）数据，是一种数据操纵语言(DML Data Manipulation Language)，执行后可以撤销。

题解：

# -- 清空表，并将自增主键设置为1
truncate table exam_record
  
# -- 清空表数据，自增主键不恢复
DELETE FROM exam_record;
ALTER TABLE exam_record auto_increment = 1;

02-表与索引操作

SQL9 创建一张新表

题解：

create table user_info_vip(
    id int(11) not null primary key auto_increment Comment "自增ID" ,
    uid int(11) not null unique comment "用户ID",
    nick_name varchar(64) comment "昵称",
    achievement	int(11) default 0 comment "成就值",
    level int(11) comment "用户等级",
    job	varchar(32) comment "职业方向",
    register_time datetime default CURRENT_TIMESTAMP comment "注册时间"
)character set utf8 collate utf8_general_ci engine innodb

SQL10 修改表

题解：

alter table user_info
add school varchar(15) after `level`,
change job profession varchar(10),
modify achievement int(11) default 0

SQL11-删除表

题解：

DROP TABLE IF EXISTS exam_record_2011,exam_record_2012,exam_record_2013,exam_record_2014

SQL12-创建索引

索引创建：

• 1.1 create方式创建索引：

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 
(column1[(length) [DESC|ASC]] [,column2,...]) -- 可以对多列建立组合索引 

# UNIQUE -- 唯一索引
# FULLTEXT -- 全文索引 
# 不指定唯一索引或全文索引时默认为普通索引

• 1.2 alter方式创建索引：

ALTER TABLE 表名 
ADD [UNIQUE | FULLTEXT] INDEX 索引名(column)`

索引删除

• 2.1 drop方式删除索引：DROP INDEX <索引名> ON <表名>
• 2.2 alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

索引使用：

• 索引使用时满足最左前缀匹配原则，即对于组合索引(col1, col2)，在不考虑引擎优化时，条件必须是col1在前col2在后，或者只使用col1，索引才会生效；
• 索引不包含有NULL值的列
• 一个查询只使用一次索引，where中如果使用了索引，order by就不会使用
• like做字段比较时只有前缀确定时才会使用索引
• 在列上进行运算后不会使用索引，如year(start_time)<2020不会使用start_time上的索引

题解1：

CREATE INDEX idx_duration ON examination_info(duration);
CREATE UNIQUE INDEX uniq_idx_exam_id ON examination_info(exam_id);
CREATE FULLTEXT INDEX full_idx_tag ON examination_info(tag);

题解2:

-- 唯一索引
ALTER TABLE examination_info
ADD UNIQUE INDEX uniq_idx_exam_id(exam_id);
 
-- 全文索引
ALTER TABLE examination_info
ADD FULLTEXT INDEX full_idx_tag(tag);
 
-- 普通索引
ALTER TABLE examination_info
ADD INDEX idx_duration(duration);

SQL13-删除索引

删除

• 1、drop方式删除索引：DROP INDEX <索引名> ON <表名>
• 2、alter方式删除索引：ALTER TABLE <表名> DROP INDEX <索引名>

题解1：

DROP INDEX uniq_idx_exam_id ON examination_info;
DROP INDEX full_idx_tag ON examination_info;

题解2:

ALTER TABLE examination_info DROP INDEX uniq_idx_exam_id;
ALTER TABLE examination_info DROP INDEX full_idx_tag;

03-聚合分组查询

SQL14 SQL类别高难度试卷得分的截断平均值

题解：

select 
    tag,
    difficulty,
    round((sum(score)-(max(score)+min(score)))/(count(score)-2),1) clip_avg_score
from examination_info t1
inner join exam_record t2 
on t1.exam_id = t2.exam_id
where tag = "SQL" and difficulty = "hard"

SQL15 统计作答次数

题解1:

SELECT
    COUNT(exam_id) AS total_pv,
    COUNT(submit_time) AS complete_pv,
    COUNT(DISTINCT exam_id AND score IS NOT NULL) AS complete_exam_cnt
FROM exam_record

题解2：

SELECT
    COUNT(exam_id) AS total_pv,
    COUNT(submit_time) AS complete_pv,
    COUNT(DISTINCT IF(score IS NOT NULL,exam_id,NULL)) AS complete_exam_cnt
FROM exam_record

SQL16 得分不小于平均分的最低分

题解1：聚合函数求出平均值，作为表中的一个筛选条件

select min(t1.score) min_score_over_avg 
from exam_record t1
left join examination_info t2 on t1.exam_id = t2.exam_id
where t2.tag = 'SQL' 
and t1.score >= (
    select avg(t1.score) 
    from exam_record t1
    left join examination_info t2 on t1.exam_id = t2.exam_id
    where t2.tag = 'SQL');

题解2:用窗口函数直接求出avg，作为一列。然后score与avg比较

select min(t.score) min_score_over_avg 
from (select t1.score,avg(t1.score) over() avg_score 
      from exam_record t1
      left join examination_info t2 on t1.exam_id = t2.exam_id
      where t2.tag = 'SQL' and t1.score is not null) as t
where t.score >= t.avg_score; 

SQL17 平均活跃天数和月活人数

本题陷阱在于九月份有个用户同一天做了两种卷子，直接count统计活跃天数会多一天，即用户ID和做题日期submit_time要同时去重才能得出正确的活跃天数.

题解：

select 
    date_format(start_time,"%Y%m") as month,
    round(count(distinct uid,date_format(submit_time,'%Y%m%d'))/count(distinct uid),2) as avg_active_days,
    count(distinct uid) as mau
from exam_record
where year(submit_time) = 2021
group by month

SQL18 月总刷题数和日均刷题数

last_day()函数：获取对应月的最后一天的日期

day()函数：取出天数

因为有多个 submit_time，所以再 avg() 取个值

题解：

select
   date_format(submit_time,"%Y%m") as submit_month,
   count(date_format(submit_time,"%Y%m")) as month_q_cnt,
   round(count(submit_time)/avg(day(last_day(submit_time))),3) as avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
group by DATE_FORMAT(submit_time, "%Y%m")
union ALL
SELECT '2021汇总' as submit_month,
count(question_id) month_q_cnt,
round(count(id)/31,3) avg_day_q_cnt
from practice_record
where year(submit_time) = 2021
order by submit_month;

SQL19 未完成试卷数大于1的有效用户

• 对于每条作答tag，用:连接日期和tag：concat_ws(':', date(start_time), tag)
• 对于一个人（组内）的多条作答，用 ; 连接去重后的作答记录：group_concat(distinct concat_ws(':', date(start_time), tag) separator ';')

题解：

select 
    uid,
    (count(start_time) - count(submit_time)) as incomplete_cnt,
    count(submit_time) as complete_cnt,
    group_concat(distinct concat(date(start_time),':',tag) order by start_time asc separator ';') as detail
from exam_record as t1
left join examination_info as t2
on t1.exam_id = t2.exam_id
where year(start_time) = 2021
group by t1.uid
having complete_cnt>=1 and incomplete_cnt<5 and incomplete_cnt>1 
order by incomplete_cnt desc

04-多表查询

SQL20 月均完成试卷数不小于3的用户爱作答的类别

• 筛选月均完成数不小于3的用户。知识点：
  • 按用户分组 group by uid
  • 统计当前用户完成试卷总数 count(exam_id)
  • 统计该用户有完成试卷的月份数 count(distinct DATE_FORMAT(start_time, "%Y%m"))
  • 分组后过滤 having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3

题解：

select tag,count(tag) as tag_cnt
from exam_record as t1
inner join examination_info as t2
on t1.exam_id = t2.exam_id 
where uid in (
    select uid
    from exam_record
    where submit_time is not null
    group by uid
    having count(exam_id) / count(distinct DATE_FORMAT(start_time, "%Y%m")) >= 3
)
group by tag
order by tag_cnt desc

SQL21 试卷发布当天作答人数和平均分

题解：

select 
    t2.exam_id,
    count(distinct t1.uid) as uv,
    round(avg(score),1) as avg_score
from user_info as t1
inner join exam_record as t3 on t1.uid = t3.uid
inner join examination_info as t2 on t2.exam_id = t3.exam_id
where t1.level > 5 and t2.tag = "SQL" and date(submit_time) = date(release_time) and score is not null
GROUP BY exam_id
order by uv desc,avg_score

SQL22 作答试卷得分大于过80的人的用户等级分布

题解1：

• select * from () t1 union select * from () t2

select level,count(level) as level_cnt
from exam_record as t3
inner join examination_info as t2
on t3.exam_id = t2.exam_id
inner join user_info as t1
on t1.uid = t3.uid
where tag = 'SQL' and score > 80
group by level
order by level_cnt desc

题解2:

select exam_id as tid,count(distinct er.uid) as uv,count(exam_id) as pv
from exam_record er 
group by exam_id
union all
select question_id as tid,count(distinct pr.uid) as uv,count(question_id) as pv
from practice_record pr 
group by question_id
order by left(tid,1) desc, uv desc,pv desc 

SQL23 每个题目和每份试卷被作答的人数和次数

题解：

select * from
(select exam_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from exam_record
group by exam_id
order by uv desc, pv desc) as t1
union
select * from
(select question_id as tid,
       count(distinct uid) as uv,
       count(*) as pv
from practice_record
group by question_id
order by uv desc, pv desc) as t2

暂停刷题，后面再接着刷。。。

SQL24 分别满足两个活动的人

题解：

SQL25 满足条件的用户的试卷完成数和题目练习数

题解：

SQL26 每个6/7级用户活跃情况

题解：

05-窗口函数

1）窗口函数：有三种排序方式

• rank() over() 1 2 2 4 4 6 (计数排名，跳过相同的几个，eg.没有3没有5)
• row_number() over() 1 2 3 4 5 6 (赋予唯一排名)
• dense_rank() over() 1 2 2 3 3 4 (不跳过排名，可以理解为对类别进行计数)

2）聚合函数：通常查找最大值最小值的时候，首先会想到使用聚合函数。

a.group by的常见搭配：常和以下聚合函数搭配

• avg（）– 求平均值
• count（）– 计数
• sum（）– 求和
• max（） – 最大值
• min（）– 最小值

b.group by 的进阶用法，和with rollup一起使用。

3）左右连接

左连接：表1 left join 表2 on 表1.字段=表2.字段 （以表1为准，表2进行匹配）

右连接：表1 right join 表2 on 表1.字段=表2.字段 （以表2为准，表1进行匹配）

全连接：表1 union all 表2 （表1 和表2的列数必须一样多，union 去除重复项，union all 不剔除重复项）

内连接：表1 inner join 表2（取表1和表2相交部分）

外连接：表1 full outer join 表2 （取表1和表2不相交的部分）

ps：MYSQL 不支持外连接，可以用左右连接后再全连接代替

SQL27 每类试卷得分前3名

求：找到每类试卷得分的前3名，如果两人最大分数相同，选择最小分数大者，如果还相同，选择uid大者。

题解：

select tag, uid, ranking
from(
    select ei.tag,er.uid,row_number() over (partition by tag
        order by
            max(er.score) DESC,
            min(er.score) DESC,
            er.uid DESC
        ) ranking
        from exam_record as er
        left join examination_info as ei on er.exam_id = ei.exam_id
        group by ei.tag, er.uid
    ) t1
where
    ranking <= 3

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

题解：

SQL29 连续两次作答试卷的最大时间窗

题解：

四、SQL真题篇

• 🌟进阶篇：39道题
• 🕸题目地址：牛客网
• 🚩小简计划：预计一周写完
• 🎯开始时间：0x-0x
• 🎉结束时间：0x-0x

❤️Sponsor

您的支持是我不断前进的动力，如果您恰巧财力雄厚，又感觉本文对您有所帮助的话，可以考虑打赏一下本文，用以维持本博客的运营费用，拒绝白嫖，从你我做起！🥰🥰🥰

支付宝支付	微信支付