2021.12.04 牛客SQL数据库实战：非技术快速入门

SQL1 查询多列

这个没有什么难度，就是按照题目要求的顺序表示字段，就可以了。

select `device_id`,`gender`,`age`,`university` from user_profile

SQL2 查询所有列

select  FROM user_profile

SQL3 查询结果去重

内容去重，就是在查询字段名称前面加上distinct

select distinct `university` from user_profile

SQL4 查询结果限制返回行数

select `device_id` from user_profile limit 2

SQL5 将查询后的列重新命名

select `device_id` as `user_infos_example` from user_profile limit 2

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

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

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

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

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

select `device_id`,`gender`,`age` from `user_profile` where `age`>=20 and `age`<=23;
select `device_id`,`gender`,`age` from `user_profile` where `age`in (20,21,22,23)  %()里面表示列表，可选内容

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

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

SQL10 用where过滤空值练习

怎么做错了呢？

不是!=null，而是is not null

SELECT `device_id`,`gender`,`age`,`university` from `user_profile` where `age` is not NULL

SQL11 高级操作符练习(1)

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

SQL12 高级操作符练习(2）

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 操作符混合运用

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

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

%通配符：%通配符的作用是用来替代一个或多个字符。

% 替代 0 个或多个字符

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

! SQL16 查找GPA最高值

select max(`gpa`) from `user_profile` where `university`='复旦大学'
select `gpa` from `user_profile` where `university`='复旦大学' order by `gpa` desc limit 1

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

select count(*),avg(`gpa`) from `user_profile` where `gender`='male'

! SQL18 分组计算练习题

这个题目看着难，实际上不难。就是把内容累加上去就行了。

select `gender`,`university`, count(*) 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 分组过滤练习题

聚合函数结果作为筛选条件时，不能用where，而是用having语法

启示：这里起别名的时候，不能用’’，而是用``。

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 分组排序练习题

没有看到关于where的要求

select `university`,avg(`question_cnt`) as `avg_question_cnt`
from `user_profile`
group by `university`
order by `avg_question_cnt` asc;

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

连表查询的内容

select qpd.`device_id`,`question_id`,`result`
from `question_practice_detail` as qpd,`user_profile` as up
where `university`='浙江大学' and 
qpd.`device_id`=up.`device_id`

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

题解 | #统计每个学校的用户平均答题数#

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

精华题解

发表于 2021-09-03 17:45:02

题意明确：

每个学校的用户平均答题数量

---

问题分解：

• 限定条件：无；
• 每个学校：按学校分组，group by university
• 平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。
• 表连接：学校和答题信息在不同的表，需要做连接

---

细节问题：

• 表头重命名：as

完整代码：

select `university`,count(`question_id`)/count(distinct `up`.`device_id`)
from `user_profile` as `up`
inner join `question_practice_detail` as `qpd`
on `up`.`device_id`=`qpd`.`device_id`
group by `university`

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

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

精华题解

发表于 2021-09-03 17:54:33

题意明确：

计算每个学校用户不同难度下的用户平均答题题目数

---

问题分解：

• 限定条件：无；
• 每个学校：按学校分组group by university
• 不同难度：按难度分组group by difficult_level
• 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)
• 来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

---

细节问题：

• 表头重命名：as
• 平均值精度未明确要求，忽略

完整代码：

SELECT 
    `university`,
    `difficult_level`,
    count(`qpd`.`question_id`)/ count(distinct `qpd`.`device_id`) as `avg_answer_cnt`
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 `qpd`.`question_id`=`qd`.`question_id`
group by `university`,`difficult_level`

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

问题分解：

• 限定条件：山东大学的用户 up.university="山东大学"；
• 不同难度：按难度分组group by difficult_level
• 平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id) 来自上面信息三个表，需要联表，up与qpd用device_id连接并限定大学，qd与qpd用question_id连接。

---

细节问题：

• 表头重命名：as
• 平均值精度未明确要求，忽略

完整代码：

复制代码

select `university`,`difficult_level`,count(`qpd`.`question_id`)/ count(distinct `up`.`device_id`) as `avg_answer_cnt`
from `user_profile` as `up`
inner join `question_practice_detail` as `qpd` on `up`.`device_id`=`qpd`.`device_id` and `university`='山东大学'
inner join `question_detail` as `qd` on `qpd`.`question_id`=`qd`.`question_id`

group by `difficult_level`

题解2 | #统计每个用户的平均刷题数#

发表于 2021-09-20 20:48:22

下面的这个方法就很好啊。

SELECT 
    t1.university,
    t3.difficult_level,
    COUNT(t2.question_id) / COUNT(DISTINCT(t2.device_id)) as avg_answer_cnt
from 
    user_profile as t1,
    question_practice_detail as t2,
    question_detail as t3
WHERE 
    t1.university = '山东大学'
    and t1.device_id = t2.device_id
    and t2.question_id = t3.question_id
GROUP BY
    t3.difficult_level;

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

题意明确：

分别查看 学校为山东大学或者性别为男性的用户的device_id、gender、age和gpa数据，结果不去重

---

问题分解：

• 限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；
• ==分别查看&结果不去重：==所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，分别去查满足条件1的和满足条件2的，然后合在一起不去重

---

细节问题：

• 不去重：union all

• SQL UNION 操作符

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

  请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

  SQL UNION 语法

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

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

  SQL UNION ALL 语法

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

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

完整代码：

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

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

# if判断

SELECT IF(age<25 OR age IS NULL,'25岁以下','25岁以及上') age_cut,COUNT(device_id) Number
FROM user_profile
GROUP BY age_cut

# 联合查询

SELECT '25岁以下' as age_cut,COUNT(device_id) Number
FROM user_profile
WHERE age < 25 OR age IS NULL
UNION
SELECT '25岁及以上' as age_cut,COUNT(device_id) Number
FROM user_profile
WHERE age >= 25

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

不知道为啥错了

select device_id,gender,'20岁以下' as age_cut from user_profile where age<20
UNION
select device_id,gender,'20-24岁' as age_cut from user_profile where age>=20 and age<=24
UNION
select device_id,gender,'25岁及以上' as age_cut from user_profile where age>=25
UNION
select device_id,gender,'其他' as age_cut from user_profile where age is null

正确答案如下内容：

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

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

select day(`date`),count(question_id) as question_cnt
from question_practice_detail
where month(`date`)=8 and year(`date`)=2021
group by day(`date`)

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

题意明确：

用户在某天刷题后第二天再来刷题的平均概率

---

问题分解：

• 限定条件：第二天再来。

  • 解法1：表里的数据可以看作是全部第一天来刷题了的，那么我们需要构造出第二天来了的字段，因此可以考虑用left join把第二天来了的拼起来，限定第二天来了的，可以用date_add(date1, interval 1 day)=date2筛选，并用device_id限定是同一个用户。

    【】

  • 解法2：用lead函数将同一用户连续两天的记录拼接起来。先按用户分组partition by device_id，再按日期升序排序order by date，再两两拼接（最后一个默认和null拼接），即lead(date) over (partition by device_id order by date)

• 平均概率：

  • 解法1：可以count(date1)得到左表全部的date记录数作为分母，count(date2)得到右表关联上了的date记录数作为分子，相除即可得到平均概率
  • 解法2：检查date2和date1的日期差是不是为1，是则为1（次日留存了），否则为0（次日未留存），取avg即可得平均概率。

• 附：lead用法，date_add用法，datediff用法，date函数

---

细节问题：

• 表头重命名：as
• 去重：需要按照devece_id,date去重，因为一个人一天可能来多次
• 子查询必须全部有重命名

完整代码：

select count(date2) / count(date1) as avg_ret
from (
    select
        distinct qpd.device_id,
        qpd.date as date1,
        uniq_id_date.date as date2
    from question_practice_detail as qpd
    left join(
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
    on qpd.device_id=uniq_id_date.device_id
        and date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date

解法2：

复制代码

select avg(if(datediff(date2, date1)=1, 1, 0)) as avg_ret
from (
    select
        distinct device_id,
        date as date1,
        lead(date) over (partition by device_id order by date) as date2
    from (
        select distinct device_id, date
        from question_practice_detail
    ) as uniq_id_date
) as id_last_next_date

！SQL30 统计每种性别的人数

需要将内容进行拆分

题意明确：

统计每个性别的用户分别有多少参赛者

---

问题分解：

• 限定条件：无；
• 每个性别：按性别分组group by gender，但是没有gender字段，需要从profile字段截取，按字符,分割后取出即可。可使用substring_index函数可以按特定字符串截取源字符串。
  substring_index(FIELD, sep, n)可以将字段FIELD按照sep分隔：
  (1).当n大于0时取第n个分隔符(n从1开始)之前的全部内容；
  (2).当n小于0时取倒数第n个分隔符(n从-1开始)之后的全部内容；
  因此，本题可以直接用substring_index(profile, ',', -1)取出性别。
  附：substring_index函数解析
• 多少参赛者：计数统计，count(device_id)

---

细节问题：

• 表头重命名：as

完整代码：

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

【MySQL】字符串截取之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

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容

相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容，如：

substring_index(str,’.’,-2)

结果为：wikidm.cn

有人会问，如果我要中间的的wikidm怎么办？

注意这里

很简单的，两个方向：

从右数第二个分隔符的右边全部，再从左数的第一个分隔符的左边：

substring_index(substring_index(str,’.’,-2),’.’,1);

SQL31 提取博客URL中的用户名

这个不难，因为有上面的题目作为内容参考。

select device_id,SUBSTRING_INDEX(blog_url,'/',-1) as user_name from user_submit

SQL32 截取出年龄

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

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

题意明确：

每个学校里gpa最低的同学

问题分解：

• 限定条件：gpa最低，看似min(gpa)，但是要留意，是每个学校里的最低，不是全局最低。min(gpa)的时候对应同学的ID丢了，直接干是拿不到最低gpa对应的同学ID的；
• 每个学校最低：
  第一种方式是用group by把学校分组，然后计算得到每个学校最低gpa，再去找这个学校里和这个gpa相等的同学ID。注意这样如果最低gpa对应多个同学，都会输出，题目没有明确此种情况，心理明白就行。
  第二种方式是利用窗口函数，先按学校分组计算排序gpa，得到最低gpa的记录在用子查询语法拿到需要的列即可。此题中rou_number可以得到排序后的位序，取位序为1即可得到最小值（升序时）。
• 窗口函数语法：row_number/rank/dense_rank over (partition by FIELD1 order by FIELD2)，传送链接

---

细节问题：

• 如果题目明确了有多个最低gpa情况下，输出结果情况，需要留意
• 表头重命名：as

完整代码：

并不理解吧

SELECT a.device_id,a.university,a.gpa FROM user_profile as a
inner JOIN 
(SELECT university,min(gpa) as gpa FROM user_profile GROUP BY university) as b
on a.university=b.university and a.gpa=b.gpa
ORDER BY university;

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

自己为什么没有做对呢，因为少看了一张表的数据。

题意明确：

复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况

---

问题分解：

• 限定条件：需要是复旦大学的（来自表user_profile.university），8月份练习情况（来自表question_practice_detail.date）
• 从date中取month：用month函数即可；
• 总题目：count(question_id)
• 正确的题目数：sum(if(qpd.result='right', 1, 0))
• 按列聚合：需要输出每个用户的统计结果，因此加上group by up.device_id

---

细节问题：

• 8月份没有答题的用户输出形式：题目要求『对于在8月份没有练习过的用户，答题数结果返回0』因此明确使用left join即可，即输出up表中复旦大学的所有用户，如果8月没有练习记录，输出0就好了
• 老样子-表头：as语法重命名后两列就好

完整代码：

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
where university='复旦大学' and month(date)=8
group by up.device_id

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

细节问题：

• 表头重命名：根据输出示例，正确率用as语法重命名
• 升序输出：order by xxx asc
• 正确率的计算方式：判断result是否为right，是的话赋值为1，对于正确的数目，可以用count，也可以用sum，正确率还可以直接用avg计算。
• join方式选择：如果前面inner join改成left join，为了防止结果中有难度为None的结果，需要在order by前加一句 having qd.difficult_level != 'None'

---

因此完整代码呼之欲出：

复制代码

select difficult_level,sum(if(result='right',1,0))/ count(result) 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 qpd.question_id=qd.question_id
where university='浙江大学'
group by difficult_level
order by correct_rate asc

SQL36 查找后排序

select device_id,age from user_profile
order by age asc

SQL37 查找后多列排序

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

SQL38 查找后降序排列

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

SQL39 21年8月份练题总数

select count(distinct qpd.device_id) as did_cnt,count(question_id) as question_cnt
from user_profile as up
inner join question_practice_detail as qpd 
on up.device_id=qpd.device_id
where month(`date`)=8 and year(`date`)=2021