牛客-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 结果表达式2WHEN 布尔表达式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-窗口函数

SQL27 每类试卷得分前3名

题解:


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

题解:


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

题解:


四、SQL真题篇

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

❤️Sponsor

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

支付宝支付 微信支付

文章作者: 简简
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 简简 !
评论
填上邮箱会收到评论回复提醒哦!!!
 上一篇
Java-网络编程 Java-网络编程
前言哈喽!大家好,我是小简。今天开始学习《Java-网络编程》,此系列是我做的一个 “Java 从 0 到 1 ” 实验,给自己一年左右时间,按照我自己总结的 Java-学习路线,从 0 开始学 Java 知识,并不定期更新所学笔记,期待一
2022-03-04
下一篇 
Java-IO流 Java-IO流
前言哈喽!大家好,我是小简。今天开始学习《Java-IO流》,此系列是我做的一个 “Java 从 0 到 1 ” 实验,给自己一年左右时间,按照我自己总结的 Java-学习路线,从 0 开始学 Java 知识,并不定期更新所学笔记,期待一年
2022-02-27
  目录