牛客sql快速入门

最新推荐文章于 2023-06-28 22:33:36 发布
最新推荐文章于 2023-06-28 22:33:36 发布
阅读量638 收藏
点赞数
分类专栏： 数据库 文章标签： 算法 leetcode 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/AK47red/article/details/121598586
版权

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

  • 某个字段不为空

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

  • 字段中包含某些文字

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

  • 多列分组

    // 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

  • 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

  • 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 班级表

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

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

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

    搜索CASE函数

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

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

    例48用搜索CASE来做：

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

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

    CASE函数(续)

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

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

    例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

    题解

    题目：现在运营想要将用户划分为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

    示例：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

      结果：

      学号分数班级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;

      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;

    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)

      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)

3. 例题

HelloWorld. py
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL 牛客SQL实战
一只老风铃
08-12 400
题目描述 查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL, `salary` int(11) NOT NULL, `from_date` date NOT NULL, `to_date` date NOT NULL, PRIMARY KEY (`emp_no`,`from_date`)); 输出描述: emp_no t 10001 17 10004 16.
牛客SQL刷题 Day-07
12-14
目录 1、获取select * from employees对应的执行计划 2、将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分 3、创建一个actor表，包含如下列信息 4、对于表actor批量插入如下...
SQL编程题练习题（基础）
大数据精读周刊
01-25 3848
SQL1 查询多列 题目：现在运营同学想要用户的设备id对应的年龄、性别和学校的数据，请你取出相应数据 select device_id, gender,age,university from user_profile SQL2 查询所有列 题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果 select * from user_profile SQL3 查询结果去重 题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据。 select distinct u
(SQL) 牛客 在线编程 非技术快速入门
cuber-lotus的博客
12-26 1592
本文为牛客的在线编程的SQL非技术快速入门题集的个人解析牛客网 - SQL非技术快速入门虽然这个题集叫：非技术快速入门，但还是学到很多东西的(主要是本身sql太菜了)评论区各位大神的各种思路都很棒，同一个效果的sql，不同的人真的可以写出不同的形式。
牛客SQL--SQL必知必会刷题记录
qq_43164662的博客
04-22 872
SQL必知必会刷题笔记
牛客SQL题解 – 从titles表获取按照title进行分组
12-14
题目描述 从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。 CREATE TABLE IF NOT EXISTS “titles” ( `emp_no` int(11) NOT NULL, `title` varchar(50) NOT NULL, ...
【牛客SQL】01查找最晚入职员工的所有信息
12-14
在SQL（结构化查询语言）中，查找最晚入职员工的所有信息是一个常见的需求，这通常涉及到对员工数据库的查询。这个任务的关键在于确定"入职时间"字段，并找到该字段的最大值，代表最晚的入职日期。以下是实现这一...
【牛客SQL】02 查找入职员工时间排名倒数第三的员工所有信息
12-14
02查找入职员工时间排名倒数第三的员工所有信息 文章目录02查找入职员工时间排名倒数第三的员工所有信息
牛客SQL编程练习（持续更新）
ABC_gt的博客
06-10 690
SQL学习
牛客SQL 全部题目 SQL266-SQL277 12套代码及解析（7）
qq118640594X的博客
06-28 508
SQL266 考试分数(一) SQL267 考试分数(二) SQL268 考试分数(三) SQL269 考试分数(四) SQL270 考试分数(五) SQL271 牛客的课程订单分析(一) SQL272 牛客的课程订单分析(二) SQL273 牛客的课程订单分析(三)
牛客 数据库sql实战(每天写十道）
t1471464672的博客
03-27 344
牛客的要比leetcode简单一点，就是题意很模糊，答案判定也感觉乱七八糟，而且没有结果对比，只能用sql写 q.查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部 ①题目分为两部分，先查出所包含电影数量多于5部的分类，再从这些分类里面查找包含root的电影 ② having是先select 在筛选，where是先筛选在select，where...
牛客《数据库SQL实战》练习总结(一)
meowing的博客
03-22 559
1.查找最晚入职员工的所有信息 难点：最晚，即排序后只选一条，可用LIMIT SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1 2.查找入职员工时间排名倒数第三的员工所有信息 难点：倒数第三，还是可以用LIMIT。 SELECT * FROM employees ORDER BY hire_date DESC LIMIT ...
牛客网数据库sql实战剖析(二）
ITRunner123的博客
06-12 922
牛客网数据库sql实战笔记
牛客sql_全部题目
Today_2018的博客
03-15 1104
SQL1查找最晚入职员工的所有信息 select * from employees where hire_date = (select max(hire_date) from employees) SQL2查找入职员工时间排名倒数第三的员工所有信息 select emp_no ,birth_date ,first_name ,last_name ,gender ,hire_date from ( select * ,dense_rank
050.牛客SQL必知必会篇
Yora123的博客
04-26 235
SQL必知必会篇；
牛客网之SQL刷题练习——一个实用的网站
qq_43499848的博客
10-16 8328
推荐一个免费在线刷题神器，希望我们都能早日拿到互联网大厂入门券！
牛客网数据库SQL 实战答案解析
韩旭051的博客
04-12 983
????牛客网数据库SQL 实战答案解析 ????目录 文章目录????牛客网数据库SQL 实战答案解析????目录????OJ刷题 地址题解????目录????急速零基础入门数据库**????基本概念****????基本SQL语句****????基本常用函数**????联系方式 ????OJ刷题 地址 ????数据库SQL 实战 ????题解仓库 题解????目录 oj平台为????牛客网在线评测平台 语法规范：????SQLite3.7.9 题解参考牛客网->试题广场->讨论...
牛客网sql题目总结（一）
0117
07-19 873
select * from employees where hire_date = ( select max(hire_date) from employees ) 思路：因为说是最xxx，所以可以直接用聚合函数max，最晚入职，就是hire_date，所以可以用嵌套select语句，先找到最晚入职员工的日期，再用日期作为where条件，查询到所有信息 select * from empl...
牛客竞赛算法入门班怎么样
最新发布
01-14
牛客竞赛算法入门班是一个针对算法竞赛初学者的培训班。它提供了系统的课程和习题，帮助学员掌握算法竞赛中常用的算法和数据结构，并提高解题能力。通过参加这个培训班，学员可以学到以下内容： 1. 算法基础知识：...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值