NIUKE SQL：非技术快速入门

He_jinian

于 2024-05-09 11:09:46 发布

阅读量1.3k

点赞数 53

分类专栏：牛客SQL 文章标签： sql 算法数据库

本文链接：https://blog.csdn.net/He_jinian/article/details/138588793

版权

牛客SQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

01 基础查询

基础查询

SQL1 查询所有列

题目：现在运营想要查看用户信息表中所有的数据，请你取出相应结果

SELECT * FROM user_profile

SQL2 查询多列

题目：现在运营同学想要用户的设备id对应的性别、年龄和学校的数据，请你取出相应数据

SELECT device_id,gender,age,university FROM user_profile

简单处理查询结果

SQL3 查询结果去重

题目：现在运营需要查看用户来自于哪些学校，请从用户信息表中取出学校的去重数据

SELECT DISTINCT university FROM user_profile

SQL4 查询结果限制返回行数

题目：现在运营只需要查看前2个用户明细设备ID数据，请你从用户信息表 user_profile 中取出相应结果

SELECT device_id FROM user_profile LIMIT 2

SQL5 将查询后的列重新命名

题目：现在你需要查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example',请你从用户信息表取出相应结果。

SELECT device_id AS 'user_infos_example' FROM user_profile LIMIT 2

AS

select 字段名 as 别名 from 表名 limit 条数

02 条件查询

基础排序

SQL36 查找后排序

题目：现在运营想要取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序

SELECT device_id,age FROM user_profile ORDER BY age

SQL37 查找后多列排序

题目：现在运营想要取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出，请取出相应数据

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

SQL38 查找后降序排列

题目：现在运营想要取出用户信息表中对应的数据，并先按照gpa、年龄降序排序输出，请取出相应数据

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

基础操作符

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

题目：现在运营想要筛选出所有北京大学的学生进行用户调研，请你从用户信息表中取出满足条件的数据，结果返回设备id和学校

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

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

题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的设备ID、性别、年龄、学校

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

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

题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的设备ID、性别、年龄

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

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

题目：现在运营想要查看除复旦大学以外的所有用户明细，请你取出相应数据

SELECT device_id,gender,age,university FROM user_profile WHERE university<>'复旦大学'

SQL10 用where过滤空值练习

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息

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

高级操作符

SQL11 高级操作符练习

题目：现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据

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

SQL12 高级操作符练习

题目：现在运营想要找到学校为北大或GPA在3.7以上(不包括3.7)的用户进行调研，请你取出相关数据（使用OR实现）

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

SQL13 Where in 和Not in

题目：现在运营想要找到学校为北大、复旦和山大的同学进行调研，请你取出相关数据

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

SQL14 操作符混合运用

题目：现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研，请你取出相应数据

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

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

题目：现在运营想查看所有大学中带有北京的用户的信息，请你取出相应数据

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

03 高级查询

计算函数

SQL16 查找GPA最高值

题目：运营想要知道复旦大学学生gpa最高值是多少，请你取出相应数据

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

SQL17 计算男生人数以及平均GPA

题目：现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少，用以辅助设计相关活动，请你取出相应数据。

SELECT COUNT(id),AVG(gpa) FROM user_profile WHERE gender='male'

分组查询

SQL18 分组计算

题目：现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量

SELECT gender,university,COUNT(id),AVG(active_days_within_30),AVG(question_cnt) 
FROM user_profile
GROUP BY gender,university

SQL19 分组过滤

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校

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

SQL20 分组排序

题目：现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列，请你取出相应数据

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

04 多表查询

子查询

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

题目：现在运营想要查看所有来自浙江大学的用户题目回答明细情况，请你取出相应数据

SELECT user_profile.device_id,question_id,result FROM question_practice_detail
JOIN user_profile ON user_profile.device_id=question_practice_detail.device_id
WHERE university='浙江大学'

链接查询

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

运营想要了解每个学校答过题的用户平均答题数量情况，请你取出数据

SELECT university,COUNT(question_id)/COUNT( DISTINCT user_profile.device_id) AS 'avg_answer_cnt'
FROM user_profile 
JOIN question_practice_detail ON user_profile.device_id=question_practice_detail.device_id
GROUP BY university
ORDER BY university

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

题目：运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量，请你写SQL取出相应数据

SELECT university, difficult_level, COUNT(qpd.question_id)/COUNT( DISTINCT qpd.device_id) AS 'avg_answer_cnt' FROM question_practice_detail AS qpd

JOIN question_detail AS qd ON qpd.question_id=qd.question_id
JOIN user_profile AS up ON qpd.device_id=up.device_id

GROUP BY university,difficult_level

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

题目：运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数，请取出相应数据

SELECT university, difficult_level, COUNT(qpd.question_id)/COUNT( DISTINCT qpd.device_id) AS 'avg_answer_cnt' FROM question_practice_detail AS qpd

JOIN question_detail AS qd ON qpd.question_id=qd.question_id
JOIN user_profile AS up ON qpd.device_id=up.device_id

WHERE university='山东大学'
GROUP BY university,difficult_level

组合查询

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

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重

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"

UNION 操作符用于合并两个或多个 SELECT 语句的结果集

UNION 内部的 SELECT 语句必须拥有相同数量的列

UNION

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

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

UNION ALL

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名

05 必会常用函数

条件函数

SQL26 计算25岁以上和以下的用户数量

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

本题注意：age为null 也记为 25岁以下

SELECT 
    IF(age<25 OR age IS null,"25岁以下","25岁及以上") AS age_cut,
    COUNT(user_profile.device_id) AS number
FROM user_profile
GROUP BY age_cut

IF

IF(expr,result_true,result_false)

expr是一个条件表达式，如果结果为true，则返回result_true，否则返回result_false

SELECT "25岁以下" AS age_cut,COUNT(device_id)
FROM user_profile
WHERE age<25 OR age IS null
UNION ALL
SELECT "25岁及以上" AS age_cut,COUNT(device_id)
FROM user_profile
WHERE age>=25

SELECT 
    CASE
        WHEN age<25 OR age IS null THEN "25岁以下"
        else "25岁及以上"
    END 
    AS age_cut,
    COUNT(device_id) AS number
FROM user_profile
GROUP BY age_cut

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

题目：现在运营想要将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，请取出相应数据（注：若年龄为空请返回其他）

SELECT device_id,gender,
IF(age>=25,'25岁及以上',IF(age BETWEEN 20 AND 24,'20-24岁',IF(age<20,'20岁以下','其他'))) AS age_cut 
FROM user_profile

日期函数

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

题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据

SELECT DAY(date) AS day,COUNT(question_id) AS question_cnt
FROM question_practice_detail
GROUP BY date

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

题目：现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据

SELECT 
COUNT(DISTINCT a.device_id,a.date)/COUNT(DISTINCT b.device_id,b.date) 
AS avg_ret 
FROM question_practice_detail a
RIGHT JOIN question_practice_detail b ON a.device_id=b.device_id
AND datediff(a.date,b.date)=1

DATEDIFF

DATEDIFF() 函数返回两个日期之间的时间

DATEDIFF(datepart,startdate,enddate)

datepart表示时间计数类型：年/季度/月/日/秒......

文本函数

SQL30 统计每种性别的人数

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

SELECT
    CASE
        WHEN profile LIKE '%,male' then 'male'
        WHEN profile LIKE '%,female' then 'female'
        else '其他'
    end as gender,
    COUNT(*) AS number
FROM
    user_submit
GROUP BY
    gender

SQL31 提取博客URL中的用户名

题目：对于申请参与比赛的用户，blog_url字段中url字符后的字符串为用户个人博客的用户名，现在运营想要把用户的个人博客用户字段提取出单独记录为一个新的字段，请取出所需数据

SELECT device_id,
REPLACE(blog_url,'http:/url/','') AS user_name
FROM user_submit

REPLACE

REPLACE ( string , A , B ) —— 将string中A替换成B

string ：搜索的字符串表达式
A ：查找的子字符串
B ：替换字符串

SELECT device_id,
SUBSTR(blog_url,11) AS user_name
FROM user_submit

SUBSTR

SUBSTR(string , pos , len) —— 从pos开始的位置，截取len个字符

SELECT device_id,
TRIM('http:/url/' FROM blog_url) AS user_name
FROM user_submit

TRIM

TRIM ( characters FROM string )

characters：要删除的特定字符

string：要从中删除空格或字符的字符串

SELECT device_id,
SUBSTRING_INDEX(blog_url,'/',-1) AS user_name
FROM user_submit

SUBSTRING_INDEX

substring_index(字符串,分隔符,序号)

参数说明

string：用于截取目标字符串的字符串

sep：分隔符

num：序号，为非0整数：若为整数则表示从左到右数，若为负数则从右到左数

SQL32 截取出年龄

题目：现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

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

窗口函数

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

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa

SELECT x.device_id,x.university,x.gpa
FROM user_profile x
JOIN (SELECT university,MIN(gpa) AS gpa FROM user_profile y GROUP BY university) AS y
ON y.university=x.university

WHERE x.gpa=y.gpa
ORDER BY university

SELECT device_id,university,gpa FROM

    (SELECT device_id,university,gpa, 
        RANK() over (PARTITION BY university ORDER BY gpa) AS rk 
    FROM user_profile) AS a

WHERE a.rk=1;

窗口函数

又称OLAP函数（Online Anallytical Processing，联机分析处理）

窗口函数原则上只能写在select子句中

基本语法：

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

1）专用窗口函数，包括rank, dense_rank, row_number等专用窗口函数

2）聚合函数，如sum. avg, count, max, min等

RANK

    SELECT device_id,university,gpa, 
        RANK() over (PARTITION BY university ORDER BY gpa) AS rk 
    FROM user_profile

1）PARTITION BY 用来对表分组 所以指定按university分组
2）ORDER BY 对分组后的结果进行排序 默认是按升序（ASC）排列

RANK：如果有并列名次的行，会占用下一名次的位置 [ 1 1 1 4 ]

DENSE_RANK：如果有并列名次的行，不占用下一名次的位置 [ 1 1 1 2 ]

ROW_NUMBER：不考虑并列名次的情况 [ 1 2 3 4 ]

06 综合练习

SQL34 统计复旦用户8月练题情况

题目：现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0

SELECT up.device_id,university,
    COUNT(question_id) AS question_cnt,
    SUM(IF(result='right',1,0)) AS right_question_cnt

FROM user_profile AS up
    LEFT JOIN question_practice_detail AS qpd 
    ON up.device_id=qpd.device_id AND MONTH(qpd.date)=8

WHERE university='复旦大学'
GROUP BY device_id

SQL35 浙大不同难度题目的正确率

题目：现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出

SELECT difficult_level,
    SUM(IF(result='right',1,0))/COUNT(qpd.question_id) AS correct_rate

FROM question_practice_detail AS qpd
    JOIN user_profile AS up ON qpd.device_id=up.device_id
    JOIN question_detail AS qd ON qpd.question_id=qd.question_id

WHERE university='浙江大学'
GROUP BY difficult_level
ORDER BY correct_rate

SQL39 21年8月份练题总数

题目：现在运营想要了解2021年8月份所有练习过题目的总用户数和练习过题目的总次数，请取出相应结果

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)=8