SQL快速入门40题-牛客

1.查询所有列

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

星号*的使用

select *
from user_profile

2.查询多列

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

select
    device_id
    ,gender
    ,age
    ,university
from user_profile

3.结果去重

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

写法一（distinct去重）：

select 
	distinct university
from user_profile

写法二（group by去重）：

select
	university
from user_profile
group by 1

4.查询结果限制返回行数

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

关键字limit

select
	device_id
from user_profile
limit 2

5.将查询后的列重新命名

题目描述：需要查看前2个用户明细设备ID数据，并将列user_infos_example’，请你从用户信息表取出相应结果。

关键字as

select
device_id as users_infos_example
from user_profile
limit 2

6.查询学校是北大的学生信息

关键字where

select
	device_id
	,university
from user_profile
where university = '北京大学'

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

age：int可用＞ ＜ =符号

select
	device_id
	,gender
	,age
	,university
from user_profile
where age > 24

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

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

select
	device_id
	,gender
	,age
from user_profile
where age >= 20 
and age <=23

9.查找去除复旦大学的用户信息

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

• university：varchar，字符串要用单引号 ''包裹起来
• 不等于：！=

select
    device_id
    ,gender
    ,age
    ,university
from user_profile
where university != '复旦大学'

10.用where过滤空值练习

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

• 取字段的非空值使用：is not null

select
	device_id
	,gender
	,age
	,university
from user_profile
where age is not null

11.操作符练习（1）

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

• gender：varchar
• gpa：float

select
	device_id
	,gender
	,age
	,university
	,gpa
from user_profile
where gender = 'male' and gpa >3.5

12.操作符练习（2）

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

• university：varchar
• gpa：float

select
	device_id
	,gender
	,age
	,university
	,gpa
from user_profile
where university = '北京大学' or gpa >3.7

13.Where in

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

写法一：

select
	device_id
	,gender
	,age
	,university
	,gpa
from user_profile
where university = '北京大学'
or university = '复旦大学'
or university = '山东大学'

写法二（通常使用这个写法，写法一只是便于理解）：

select
	device_id
	,gender
	,age
	,university
	,gpa
from user_profile
where university in ('北京大学', '复旦大学', '山东大学')

14.操作符混合运用

题目描述：找到GPA在3.5以上(不包括3.5)的山东大学用户 或 GPA在3.8以上(不包括3.8)的复旦大学用户进行调研，请你取出相应数据。

==优先级==：NOT > AND > OR，可以用括号()改变优先级

select
	device_id
	,gender
	,age
	,university
	,gpa
from user_profile
where 
	gpa>3.5 and university='山东大学'
	or
	gpa>3.8 and university='复旦大学'

加不加括号都行：

where 
	(gpa>3.5 and university='山东大学')
	or
	(gpa>3.8 and university='复旦大学')

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

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

• 关键字like
• 占位符(通配符)%

select
    device_id,
    age,
    university
from
    user_profile
where
    university like '%北京%'

16.查找GPA最高值

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

执行顺序是先where再from（准确的说这里是max函数的使用）

select
	round(max(gpa), 1)
from user_profile
where university = '复旦大学'

• excel里也是先筛选再聚合？？为什么捏?
  • 好处是：先筛选之后数据量就变少了，然后再计算（或者更复杂的操作），数据量越少，执行速度就快，大概率

17.计算男生人数以及他们的平均GPA

函数count()

可以是

• count(字段)：统计某个列中非 NULL 值的数量
• count(*)：统计表中的总行数（包括NULL）
• count(distinct 字段id)：统计id去重后的非NULL值数量

select
	count(*) as male_num
	,round(avg(gpa), 1) as avg_gpa
from user_profile
where gender='male'

• 历史背景
  • COUNT(1) 是早期数据库优化不佳时的”民间偏方”
  • 现代数据库优化器会自动将两者识别为等效操作
  • 推荐使用 COUNT(*)：标准、规范、易读

18.分组计算练习题

分组聚合

select
    gender
    ,university
    ,count(*) as user_num
    ,round(avg(active_days_within_30), 1) as avg_active_day
    ,round(avg(question_cnt), 1) as avg_question_cnt
from user_profile
group by 1,2
order by 1,2

• 分组：严格指group by子句（分组操作）
• 分组后，核心聚合函数5个
  • count()：计数
  • sum()：求和
  • avg()：平均值

19.分组过滤练习题

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

在excel里会怎么做：先求出平均发帖和回帖情况（用数据透视表做），然后再筛选符合条件的

• 关键字：having
  • having是在聚合之后的数据里进行筛选

select
    university
    ,avg(question_cnt) as avg_question_cnt
    ,avg(answer_cnt) as avg_answer_cnt
from user_profile
group by 1
having avg_question_cnt<5 or avg_answer_cnt<20

解法2：可以用子查询

不过having存在，就是让你可以少写一层子查询，仅此

select
*
from(
    select
        university,
        avg(question_cnt) as avg_question_cnt,
        avg(answer_cnt) as avg_answer_cnt
    from
        user_profile
    group by
        university
) as a
where avg(question_cnt) < 5 or avg(answer_cnt) < 20

20.分组排序练习

关键字：order by

默认升序排列asc；降序加desc，如 order by 2 desc

select 
	university
	,avg(question_cnt) as avg_question_cnt
from user_profile
group by 1
order by 2

21.表连接

excel里XLOOKUP，只连接某个字段

关键字join，表连接

• 左连接：left join，保证左表完整性（左表所有记录都保留），左表不动，右表记录根据匹配条件”拼”到左表上，右表找到匹配记录 → 左右表字段合并显示，右表没找到匹配记录 → 左表字段正常显示，右表字段显示为NULL
  • 左表所有行都在结果中
  • 右表只有匹配上的行才会出现
  • 右表无法匹配的行（右表有但左表没有）确实会被丢弃
  • 匹配不上时右表字段值为NULL
• 右连接：同理
• 全连接：为空也保留，左右都保留
• 内连接：join，只要一边没连上全都丢掉

1）只连接指定列：

select 
	字段
from question_practice_detail a
left join(
    select
    device_id
    , university
    from user_profile
) b on a.device_id = b.device_id

2）连接所有列：

select 
	字段
from question_practice_detail a
left join user_profile b on a.device_id = b.device_id

先连接，后筛选（右表的university字段）

select 
	a.device_id as device_id
	,a.question_id as question_id
	,a.result as result
from question_practice_detail a
left join user_profile b on a.device_id = b.device_id
where b.university = '浙江大学'

==22==.统计每个学校的答过题的用户的平均题数

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

说明：某学校用户平均答题数量计算方式为该学校用户答题总次数除以答过题的不同用户个数

• 用户信息表user_profile
• 答题情况明细表question_practice_detail

select
	b.university as university
	,round(count(*) / count(distinct a.device_id), 4) as avg_answer_cnt
from question_practice_detail a
left join user_profile b on a.device_id = b.device_id
group by 1
order by 1

23.事实表vs维度表

（这题有详细解题步骤，因为开始变难了）

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

事实表、维度表、信息表？

• 事实表：带着业务的？
• 这里的用户信息表user_profile是一张维度表

bug?刚刚在牛客提交，明明是一样的答案提交错误，等了一会就可以了

select
	b.university as university
	,c.difficult_level as difficult_level
	,round(count(*) / count(distinct a.device_id), 4) as avg_answer_cnt
from question_practice_detail a
left join user_profile b on a.device_id = b.device_id
left join question_detail c on a.question_id = c.question_id
group by 1, 2

• 怎么区分事实表和维度表

• 给你个字段，问两个问题：

  1. 这个字段能进行数学运算吗？

  • 能算 → 事实（如：销售额可以求和）
  • 不能算 → 进入下一题

  2. 这个字段用来分组/筛选吗？

  • 用来分组 → 维度（如：按”省份”分组统计）
  • 不用来分组 → 可能是维度属性

  真实案例

  订单数据：

  • 订单金额、数量、折扣 → 事实表
  • 用户姓名、商品名称、下单日期、城市 → 维度表

• 事实表和维度表在查询时为什么要区分

  • 数据量差距 = 生死差距

    • 事实表：1亿行（订单明细）
    • 维度表：1万行（商品信息）

    如果不区分，你可能把1亿行 × 1亿行做JOIN，系统直接卡死。
    区分后，1亿行 JOIN 1万行，数据库可以轻松优化。

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

24题与23题只有些许不同

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

• 要在join的on就把“山东大学”的用户筛选出来→打了题∩山东大学

select
    b.university as university
    ,c.difficult_level as difficult_level
	,round(count(*) / count(distinct a.device_id), 4) as avg_answer_cnt
from question_practice_detail a
join user_profile b
    on a.device_id = b.device_id
    and b.university = '山东大学'
left join question_detail c on a.question_id = c.question_id
group by 1,2

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

题目：现在运营想要分别查看学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，请取出相应结果，结果不去重。根据示例，你的查询应返回以下结果（注意输出的顺序，先输出学校为山东大学再输出性别为男生的信息）

关键字：union，上下拼接（列的数量要匹配）

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

• 为什么使用 union all 而不是where university = '山东大学' or gender = 'male'

  • 结果不去重
  • 顺序，先山大再男性

• union all的经典用法
  可以合并不同表的”同类”数据，且支持字段计算转换

  SELECT 
      order_id,
      amount,
      '正向订单' AS order_type,
      create_time
  FROM formal_orders
  WHERE create_date BETWEEN '2024-01-01' AND '2024-01-07'
  
  UNION ALL
  
  SELECT 
      refund_id AS order_id,
      -amount AS amount,  -- 退款为负值
      '退款订单' AS order_type,
      refund_time AS create_time
  FROM refund_orders
  WHERE refund_date BETWEEN '2024-01-01' AND '2024-01-07';

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

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

select
    if(age < 25 or age is null, '25岁以下', '25岁及以上') as age_cut
    ,count(*) as number
from user_profile
group by 1

excel中，用if函数做辅助列

=IF(判断条件, 条件成立时的值, 条件不成立时的值)

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

case when的使用

当条件有三个或以上的时候就不用if了，改用case when

select
    device_id
    ,gender
    ,case 
        when age < 20 then '20岁以下'
        when age >= 20 and age <= 24 then '20-24岁'
        when age >= 25 then '25岁及以上'
    else '其他' end as age_cut
from user_profile

28.day() left()

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

date：date

select
    day(date) as day
    ,count(*) as question_cnt
from question_practice_detail
where left(date, 7) = '2021-08'
group by 1

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

with tmp as(
    select
    device_id
    ,date
    from question_practice_detail
    group by 1,2
)
select
count(b.device_id) / count(a.device_id) as avg_ret
from tmp a
left join tmp b
	on a.device_id = b.device_id
	and a.date = date_sub(b.date, interval 1 day)

• 步骤1：获取每个用户每天的刷题日期（去重）
  步骤2：通过自连接，将每个用户每天的记录与第二天的记录连接起来。
  步骤3：统计所有的记录数作为分母，统计第二天有记录的记录数作为分子，然后计算比例。
• 自连接说的是inner join吗
  • 我们通常说自连接（self-join）是指同一个表自己和自己连接，可以使用INNER JOIN，也可以使用LEFT JOIN等

30.substring_index()

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

profile：varchar

select
    substring_index(profile, ',', -1) as gender
    ,count(*) as number
from user_submit
group by 1

• substring_index()的使用
  • 用于根据分隔符提取子字符串
  • substring_index(str, delim, count)
    参数说明：
    • str：要处理的原始字符串
    • delim：分隔符（可以是单个字符或多个字符）
    • count：计数值
      • 正数：从左向右查找，返回第 count 个分隔符之前的所有字符
      • 负数：从右向左查找，返回第 abs(count) 个分隔符之后的所有字符
      • 零：返回空字符串

31.提取博客URL中的用户名

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

select
    device_id
    ,substring_index(blog_url, '/', -1) as gender
from user_submit

32.截取出年龄

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

profile 字段的格式是：身高,体重,年龄,性别

select
	substring_index(substring_index(profile, ',', 3), ',', -1) as age
	,count(device_id) as number
from user_submit
group by 1

• 如果不是最前/最后一个字段的话，substring_index()就要写嵌套

substring_index(substring_index(profile, ',', 正数第几个), ',', -1) as 字段名

• 示例：对于 profile = '180cm,75kg,27,male'：

-- 提取第一个字段（身高）：
SUBSTRING_INDEX(profile, ',', 1) -- '180cm'

-- 提取第二个字段（体重）：
SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ',', 2), ',', -1) -- '75kg'
-- 内层：SUBSTRING_INDEX(profile, ',', 2) = '180cm,75kg'
-- 外层：SUBSTRING_INDEX('180cm,75kg', ',', -1) = '75kg'

-- 提取第三个字段（年龄）：
SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ',', 3), ',', -1) -- '27'
-- 内层：SUBSTRING_INDEX(profile, ',', 3) = '180cm,75kg,27'
-- 外层：SUBSTRING_INDEX('180cm,75kg,27', ',', -1) = '27'

-- 提取第四个字段（性别）：
SUBSTRING_INDEX(profile, ',', -1) -- 'male'
-- 或者：SUBSTRING_INDEX(SUBSTRING_INDEX(profile, ',', 4), ',', -1)

33.找出每个学校GPA最低的同学

这33题有很多的解法，主要还是关注对表格结构（结果和原表）的分析和理解

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。输出结果按university升序排序

如果一个学校有多个最低值—>使用窗口函数

解法一：子查询

select
	device_id
	,university
	,gpa
from user_profile
where (university, gpa) in (
	select university, min(gpa)
    from user_profile
    group by 1
)
order by 2

子查询的使用

• 记得加别名

解法二：join，性能更好

select
	t.device_id
	,t.university
	,t.gpa
from user_profile t
join(
	select university,min(gpa) as min_gpa
    from user_profile
    group by 1
)t1 
	on t.university=t1.university
	and t.gpa=t1.min_gpa
order by 2    

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

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

• 对象：复旦大学的每个用户（就像前面的题会有：答过题的用户）

select
	t1.device_id as device_id
	,t1.university as university
	,count(t2.question_id) as question_cnt
	,sum(case when t2.result='right' then 1 else 0 end) as right_question_cnt
from user_profile t1
left join question_practice_detail t2
	on t1.device_id=t2.device_id
	and t2.date >= '2021-08-01'
	and t2.date <= '2021-08-31'
where t1.university='复旦大学'
group by 1

• 日期在t2
  • join时，在on子句就要过滤！
  • 如果把日期条件放在where子句中，left join会退化成inner join，无法显示未练习的用户
• question_cnt数做了多少题
• right_question_cnt把答对的题标记为1，把这些1相加

步骤1：筛选复旦大学用户

从用户表 user_profile 中过滤出 university 为“复旦大学”的用户。

SELECT 
	字段
FROM user_profile
WHERE university = '复旦大学'

步骤2：关联答题表并限制时间范围

使用 左连接 将用户表与答题表 question_practice_detail 关联，并限定答题时间为8月份（date 在 2021-08-01 到 2021-08-31 之间）。
⚠️ 注意：时间条件必须放在 LEFT JOIN 的 ON 子句中，而不是 WHERE 子句，否则会错误过滤掉无答题记录的用户。

SELECT 
    字段
FROM user_profile u
LEFT JOIN question_practice_detail q
    ON u.device_id = q.device_id
    AND q.date BETWEEN '2021-08-01' AND '2021-08-31'
WHERE u.university = '复旦大学'

步骤3：聚合统计答题数

通过 COUNT 统计总题数，使用 SUM(CASE ...) 统计正确题数。
🔑 关键点：

• COUNT(q.question_id) 会自动忽略 NULL，因此无答题记录的用户结果为0。
• SUM(CASE WHEN q.result = 'right' THEN 1 ELSE 0 END) 对正确结果计数，无记录时结果为0。

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

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

select
    t2.difficult_level
    ,round(sum(if(t1.result='right', 1, 0)) / count(*), 4) as correct_rate
from question_practice_detail t1
join question_detail t2 on t1.question_id=t2.question_id
join (
    select device_id
    from user_profile
    where university = '浙江大学'
) t3 on t1.device_id=t3.device_id
group by 1
order by 2

1. 致命错误：COUNT(IF(...))用法错误

count(if(t1.result='right', 1, 0))  -- 错误！

• IF()返回0时，COUNT()也会计入0，因为COUNT只忽略NULL，不忽略0
• 这会导致分子 = 分母，正确率永远为1.0000

正确写法：

-- 方法1：IF返回NULL
count(if(t1.result='right', 1, null))

-- 方法2：用SUM代替COUNT
sum(if(t1.result='right', 1, 0))

-- 方法3：CASE WHEN（最规范）
sum(case when t1.result='right' then 1 else 0 end)

2.子查询先筛选→再join关联

join (
    select device_id
    from user_profile
    where university = '浙江大学'
) t3

join user_profile t3 on t1.device_id=t3.device_id
where t3.university = '浙江大学'

哪个性能优？

36.查找后排序

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

select
    device_id
    ,age
from user_profile
order by age

37.查找后多列排序

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

select
    device_id
    ,gpa
    ,age
from user_profile
order by gpa asc,age asc

38.查找后降序排列

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

select
    device_id
    ,gpa
    ,age
from user_profile
order by gpa desc,age desc

39.21年8月份练题总数

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

select
    count(distinct device_id) as did_cnt
    ,count(*) as question_cnt
from question_practice_detail

40.正则表达式regexp

题目：在一张contacts表中，存储了用户的联系信息。请查询出所有符合以下条件的电话号码，并按id升序输出所有字段：

1. 电话号码必须是 10 位数字。
2. 电话号码的第一位不能以 0 开头。
3. 电话号码的格式可以是连续的 10 位数字，或以-分隔的格式（如123-456-7890）。

• 读题：
  • – 10位数字且非0开头
  • – 3-3-4格式电话号码

select *
from contacts
where phone_number regexp '^[1-9][0-9]{9}$'
or phone_number regexp '^[1-9][0-9]{2}-[0-9]{3}-[0-9]{4}$'
order by id

regexp在MySQL中用于正则表达式匹配，正确的用法是：字段名 REGEXP ‘正则表达式’

where phone_number regexp '^([1-9][0-9]{9}|[1-9][0-9]{2}-[0-9]{3}-[0-9]{4})$'

语法分解：

• ^ - 字符串开始
• () - 分组
• | - 或逻辑（匹配左侧或右侧模式）不能有空格！！！
• [1-9] - 非零数字
• [0-9]{9} - 恰好9个数字
• [0-9]{2} - 恰好2个数字
• [0-9]{3} - 恰好3个数字
• [0-9]{4} - 恰好4个数字
• $ - 字符串结束

正则表达式中的空格是有特殊含义的——它们会被当作字面意义上的字符来匹配！

-- ❌ 错误示例（管道符周围有空格）
where phone_number regexp '^([1-9][0-9]{9} | [1-9][0-9]{2}-[0-9]{3}-[0-9]{4})$'

-- 这个表达式实际要求：
-- 要么是：10位数字 + 一个空格
-- 要么是：一个空格 + 3-3-4格式的号码

---

常用的

count(*)             -- 总行数
count(device_id)     -- 非NULL值数量
count(distinct 字段)  -- 去重用户数

sum(if(条件, 1, 0)) as 字段名
sum(case when 条件 then 1 else 0 end) as 字段名
-- 注意：COUNT(IF(...))会统计0，导致错误！

where 字段 like '%北京%'  -- 包含北京
where 字段 like '北京%'   -- 以北京开头

day(字段) as day          -- 提取日（1-31）
left(字段, 7) as month    -- 提取年月（2026-01）
month(字段) as month_num  -- 提取月份（1）

data_sub(字段, interval 1 day)    -- 前一天
date_add(字段, interval 1 month)  -- 后一月

substring_index(字段, 分隔符, 计数值)  -- 分隔符

字段 regexp '正则表达式'  -- 匹配正则表达式

-- 自连接计算留存率
with tmp as(
    select
    device_id
    ,date
    from question_practice_detail
    group by 1,2  -- 去重
)
select
count(b.device_id) / count(a.device_id) as avg_ret
from tmp a
left join tmp b
	on a.device_id = b.device_id
	and a.date = date_sub(b.date, interval 1 day)

• 优先使用GROUP BY去重：比DISTINCT性能更好