devbox
IT小白
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

牛客sql快速入门_sql学习路线牛客

作者：IT小白 | 2024-04-17 03:32:36

赞

踩

sql学习路线牛客

notion原文地址
SQL数据库实战题_面试必刷+解析_牛客题霸_牛客网

基础练习

1. 常用字段

distinct()去重
MAX()
avg()求平均值
union all将多个查询结果合在一起，不去重
union将多个查询结果合在一起 去重
SUBSTRING_INDEX(profile, ‘,’, -1)截取最后一个，如果是 -2 就是截取最后两个

2. 小技巧

  • 取表中的前几条数据

    // sql 取前两条数据
select  device_id from user_profile limit 0, 2;

// oracle
select * from user_profile where rownum <= 2

    • 1
    • 2
    • 3
    • 4
    • 5

  • 某个字段不为空

    // is not null
SELECT device_id, gender, age, university from user_profile where age is not null

    • 1
    • 2

  • 字段中包含某些文字

    // 包含北京
SELECT device_id, age, university
from user_profile
where university LIKE '%北京%'

    • 1
    • 2
    • 3
    • 4

  • 多列分组

    // group by 列1， 列2
SELECT gender, university, 
    COUNT(1) user_num, 
    AVG(active_days_within_30) avg_active_day, 
    AVG(question_cnt) avg_question_cnt
from user_profile
group by  gender, university

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

  • Group by 跟 where， having 联用

    1. where 是在分分组前对数据进行过滤，不能包含聚合函数
    2. have 是筛选出符合条件的组，是在分组后
    SELECT university,
    AVG(question_cnt) avg_question_cnt,
    AVG(answer_cnt) avg_answer_cnt
from user_profile
where age > 18
GROUP by university  
HAVING AVG(question_cnt) < 5
    or AVG(answer_cnt) < 20

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8

  • case 函数

    https://blog.nowcoder.net/n/693e3098b4844ba4931020347cb7c87a?f=comment

    知识

    CASE函数

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

    简单 CASE函数

    SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个WHEN子句的简单表达式进行比较。如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。如果所有简单表达式的值与测试表达式的值都不相等，若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；若没有指定ELSE子句，则返回NULL。

    例48. 查询班级表中的学生的班号、班名、系号和班主任号，并对系号作如下处理：当系号为1时，显示 “计算机系”；当系号为2时，显示 “软件工程系”；当系号为3时，显示 “物联网系”。

    SELECT 班号 ,班名,
CASE 系号
WHEN 1 THEN '软件工程系'
WHEN 2 THEN '计算机系'
WHEN 3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    搜索CASE函数

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

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6

    按从上到下的书写顺序计算每个WHEN子句的布尔表达式。返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。如果没有取值为TRUE的布尔表达式，则当指定了ELSE子句时,返回ELSE子句中指定的结果；如果没有指定ELSE子句，则返回NULL。

    例48用搜索CASE来做：

    SELECT 班号 ,班名,
CASE
WHEN 系号=1 THEN '软件工程系'
WHEN 系号=2 THEN '计算机系'
WHEN 系号=3 THEN '物联网系'
END AS 系号,班主任号
FROM 班级表

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7

    例49.查询“M01F011”号课程的考试情况，列出学号、课程号和成绩，同时将百分制成绩显示为等级。

    SELECT 学号,课程号,
CASE
WHEN 成绩 >= 90 THEN '优'
WHEN 成绩 BETWEEN 80 AND 89 THEN '良'
WHEN 成绩 BETWEEN 70 AND 79 THEN '中'
WHEN 成绩 BETWEEN 60 AND 69 THEN '及格'
WHEN 成绩 <60 THEN '不及格'
END 成绩
FROM 成绩表
WHERE 课程号 = 'M01F011'

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10

    CASE函数(续)

    例50.统计每个班男生和女生的数量各是多少，统计结果的表头为，班号，男生数量，女生数量。

    SELECT 班号,
COUNT(CASE WHEN 性别=‘男’ THEN ‘男’ END) 男生数,
COUNT(CASE WHEN 性别=‘女’ THEN ‘女’ END) 女生数
FROM 学生表 GROUP BY 班号

    • 1
    • 2
    • 3
    • 4

    例51.判断成绩的等级，85-100为“优”，70-84为“良”，60-69为“及格”，60以下为“不及格”，并统计每一等级的人数。

    SELECT
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END 等级, COUNT(*) 人数
FROM SC
GROUP BY
CASE
WHEN GRADE BETWEEN 85 AND 100 THEN '优'
WHEN GRADE BETWEEN 70 AND 84 THEN '良'
WHEN GRADE BETWEEN 60 AND 69 THEN '及格'
ELSE '不及格'
END

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

    题解

    题目：现在运营想要将用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量

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

    • 1
    • 2
    • 3
    • 4
    • 5

    示例：user_profile

    https://uploadfiles.nowcoder.com/images/20210928/999991344_1632828974694/7219DA906D0C14B655E857C7349E6017

    根据示例，你的查询应返回以下结果：

    https://uploadfiles.nowcoder.com/images/20210928/999991344_1632831829937/75D997B10141AD2740283AC28486E68D

  • 日期函数

    DateOfWeek(‘1997-06-19’)这天是周几：0是周一，6是周日
    WeekDay(‘1997-06-19 14:19:00’)当前时间是周几：0周一
    DayOfMonth(‘1997-06-19’)这天是几号 19
    DayOfYear(‘1997-06-19’)这天是一年中的第几天
    Year(date)获取年份
    Month(date)获取月份
    DayName(date)返回星期的英文名
    MonthName(date)返回月份的英文名
    Quarter(date)返回季度 1~4
    Week(date)返回这一周是一年中的第几周 0~52
    Week(date,first)返回这一周是一年中的第几周，但是可以指定从周几开始，first = 0 从周日开始， first = 1 从周一开始
    Hour(time)返回当前时间是第几小时 0~23
    Minute(time)0~59
    Second(time)0~59
    Period_add(P,N)增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

    例如：若返回 199706
    select PERIOD_ADD(9704,2) |
    | Period_diff(P1,P2) | 返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。 且p1 > p2返回是正
    例如：求97年6月到 00年8月之间有几个月
    select PERIOD_DIFF(200008, 9706); |

  • 窗口函数

    1. 与group by 的区别

      group by 分组后会去重，减少原表的行数
      而窗口函数可以分组但不会·去重，不会改变原表的行数；并且可以排序

    2. 窗口函数只能写在select后面

    3. 窗口函数分类

      1. rank
      2. dense_rank
      3. row_number

      区别：

      学号分数班级
      1991
      2981
      3992
      4972
      5991
      61002
      /*  
		partition by 后面跟分组的列名  
		order by 后跟排序的列名 
*/
select *,
   rank() over (partition by 班级 order by 分数 desc) as ranking,
   dense_rank() over (partition by 班级 order by 分数desc) as dese_rank,
   row_number() over (partition by 班级 order by 分数desc) as row_num
from table_name

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

      结果：

      学号分数班级rankingdese_rankrow_num
      6991111
      1991112
      3981323
      51002111
      21002112
      4972323

  • 连接查询

    1. 内连接：结果只包含符合条件的行

      1. 显示内连接

        select difficult_level,
#    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up

inner join question_practice_detail as qpd
    on up.device_id = qpd.device_id

inner join question_detail as qd
    on qd.question_id = qpd.question_id

where up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

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

      2. 隐式内连接

        select difficult_level,
    avg(if(qpd.result='right', 1, 0)) as correct_rate
#    sum(if(qpd.result='right', 1, 0)) / count(qpd.question_id) as correct_rate
#    count(if(qpd.result='right', 1, null)) / count(qpd.question_id) as correct_rate
from user_profile as up, question_practice_detail as qpd, question_detail as qd
where up.university = '浙江大学'
	and up.device_id = qpd.device_id
	and qd.question_id = qpd.question_id
group by qd.difficult_level
order by correct_rate asc;

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10

    2. 外连接：连接结果包含符合条件的行，也包括不符合条件的行

      1. 左外连接：以左表为基准，左表全部展示，右表符合条件的展示，不符合条件的为null

        SELECT COUNT(date2) / COUNT(date1) AS avg_ret
FROM 
    (SELECT DISTINCT device_id, date AS date1
     FROM question_practice_detail) AS qpd1
LEFT join 
    (SELECT DISTINCT device_id, date AS date2
     FROM question_practice_detail) AS qpd2
ON qpd1.device_id = qpd2.device_id 
    and date2 = DATE_ADD(date1, interval 1 day)

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9

      2. 右外连接: 以右表为基准，右表全部展示，左表符合条件的展示，不符合条件的为null

        SELECT COUNT(date2) / COUNT(date1) AS avg_ret
FROM 
		(SELECT DISTINCT device_id, date AS date2
     FROM question_practice_detail) AS qpd2

right join 
		(SELECT DISTINCT device_id, date AS date1
     FROM question_practice_detail) AS qpd1
ON qpd1.device_id = qpd2.device_id 
    and date2 = DATE_ADD(date1, interval 1 day)

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10

3. 例题

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/IT小白/article/detail/437978
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号