MySQL的SQL语句

SQL1 查询所有列

SELECT * FROM user_profile

select id,device_id,gender,age,university,province from user_profile

SQL2 查询多列

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

SELECT device_id,gender,age,university FROM user_profile

SQL3 查询结果去重

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

第一种：使用distinct(关键词distinct用于返回唯一不同的值)

select distinct university from user_profile;

第二种：使用group by按照university进行分组

select university from user_profile group by university;

SQL4 查询结果限制返回行数

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

SELECT  device_id FROM user_profile ORDER BY id LIMIT 2

SQL5 将查询后的列重新命名

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

SELECT device_id AS user_infos_example FROM user_profile LIMIT 2

SQL36 查找后排序

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

SELECT device_id,age FROM user_profile ORDER BY age ASC

SELECTdevice_id,age
FROMuser_profile
ORDER BYage;

SQL37 查找后多列排序

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

SELECT device_id,gpa,age from user_profile order by gpa,age;默认以升序排列
SELECT device_id,gpa,age from user_profile order by gpa,age asc;
SELECT device_id,gpa,age from user_profile order by gpa asc,age asc;

SQL38 查找后降序排列

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

SELECTdevice_id,gpa,age
FROMuser_profile
ORDER BYgpa DESC,age DESC;

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

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

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

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

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

题目：现在运营想要针对24岁以上的用户开展分析，请你取出满足条件的用户明细数据。

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

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

题目：现在运营想要针对20岁及以上且23岁及以下的用户开展分析，请你取出满足条件的用户明细数据。

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 NOT IN ('复旦大学')

SQL10 用where过滤空值练习

题目：现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，请你取出所有年龄值不为空的用户明细数据。

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

SQL11 高级操作符练习(1)

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

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

SQL12 高级操作符练习(2）

题目：现在运营想要找到学校为北大或GPA在3.7以上的用户进行调研，请你取出相关数据

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

and的优先级大于or 如果不确定可以用()来改变运算的优先级

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

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

WHERE university LIKE '%北京%'

SQL16 查找GPA最高值

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

# 方法1select max(gpa) as gpafrom user_profilewhere university='复旦大学';# 方法2
select gpa
from user_profile
where university='复旦大学'
order by gpa desc limit 1

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

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

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

此题要注意的是暗含条件，保留一位小数
使用ROUND()函数，ROUND返回一个数值，舍入到指定的长度或精度

SQL18 分组计算练习题

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

select gender, university,count(device_id) as user_num,avg(active_days_within_30) as avg_active_days,avg(question_cnt) as avg_question_cnt
from user_profile
group by gender, university

SQL19 分组过滤练习题

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

selectuniversity,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

限定条件：平均发贴数低于5或平均回帖数小于20的学校，avg(question_cnt)<5 or avg(answer_cnt)<20，聚合函数结果作为筛选条件时，不能用where，而是用having语法，配合重命名即可；
按学校输出：需要对每个学校统计其平均发贴数和平均回帖数，因此group by university

SQL20 分组排序练习题

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

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

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

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

在这里插入图片描述

select qpd.device_id, qpd.question_id, qpd.result
from question_practice_detail as qpd
inner join user_profile as up
on up.device_id=qpd.device_id and up.university='浙江大学'
order by question_id

select device_id, question_id, result
from question_practice_detail
where device_id in (select device_id from user_profilewhere university='浙江大学'
)
order by question_id

限定条件：来自浙江大学的用户，学校信息在用户画像表，答题情况在用户练习明细表，因此需要通过device_id关联两个表的数据；方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university=‘浙江大学’ 方法2：先从画像表找到浙江大学的所有学生id列表where university=‘浙江大学’，再去练习明细表筛选出id在这个列表的记录，用where in

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

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

用户信息表 user_profile，其中device_id指终端编号（认为每个用户有唯一的一个终端），gender指性别，age指年龄，university指用户所在的学校，gpa是该用户平均学分绩点，active_days_within_30是30天内的活跃天数。在这里插入图片描述

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

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

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

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

select university,difficult_level,round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt
from question_practice_detail as qpdleft join user_profile as up
on up.device_id=qpd.device_idleft join question_detail as qd
on qd.question_id=qpd.question_idgroup by university, difficult_level

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

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

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

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_idand t2.question_id = t3.question_id
GROUP BYt3.difficult_level;

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

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

select device_id, gender, age, gpa
from user_profile
where university='山东大学'union allselect device_id, gender, age, gpa
from user_profile
where gender='male'

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

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

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

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

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

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

限定条件：无；
划分年龄段：数值条件判断，可以用多重if，不过更方便的是用case when [expr] then [result1]…else [default] end

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

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

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

限定条件：2021年8月，写法有很多种，比如用year/month函数的year(date)=2021 and month(date)=8，比如用date_format函数的date_format(date, “%Y-%m”)=“202108”
每天：按天分组group by date
题目数量：count(question_id)

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

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

解法一：

select count(date2) / count(date1) as avg_ret
from (selectdistinct qpd.device_id,qpd.date as date1,uniq_id_date.date as date2from question_practice_detail as qpdleft join(select distinct device_id, datefrom question_practice_detail) as uniq_id_dateon qpd.device_id=uniq_id_date.device_idand date_add(qpd.date, interval 1 day)=uniq_id_date.date
) as id_last_next_date

解法二：

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

问题分解：
限定条件：第二天再来。
解法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即可得平均概率。

SQL30 统计每种性别的人数

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

解法一：

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number
FROM user_submit 
GROUP BY gender;

解法二：

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

SQL32 截取出年龄

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

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

限定条件：无；
每个年龄：按年龄分组group by age，但是没有age字段，需要从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, ‘,’, 3)取出"180cm,75kg,27"，然后用substring_index(profile, ‘,’, -1)取出27。
当然也可以用substring_index(substring_index(profile, “,”, -2), “,”, 1)取出27。
附：substring_index函数解析
多少参赛者：计数统计，count(device_id)

SQL31 提取博客URL中的用户名

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

select 
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name-- 截取法 substr(string, start_point, length*可选参数*)
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name-- 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
device_id, substring_index(blog_url,'/',-1) as user_namefrom user_submit;

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

题目：现在运营想要找到每个学校gpa最低的同学来做调研，请你取出每个学校的最低gpa。
在这里插入图片描述

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

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

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

select up.device_id, '复旦大学' as university,count(question_id) as question_cnt,sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as upleft join question_practice_detail as qpdon qpd.device_id = up.device_id and month(qpd.date) = 8where up.university = '复旦大学'
group by up.device_id

限定条件：需要是复旦大学的（来自表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

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

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

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 upinner join question_practice_detail as qpdon up.device_id = qpd.device_idinner join question_detail as qdon qd.question_id = qpd.question_idwhere up.university = '浙江大学'
group by qd.difficult_level
order by correct_rate asc;

限定条件：浙江大学的用户；
不同难度：difficult_level（question_detail表中的列），需要分组统计，因此用到group by，语法详情；
正确率：表面理解就是正确数÷总数，正确的是result=‘right’（question_practice_detail表），数目用函数count，总数是count(question_id)；
多张表联合查询：需要用到join，join有多种语法，因为条件限定需要是浙江大学的用户，所以需要是user_profile表的并且能统计出题目难度的记录，因此用user_profile表inner join另外两张表。
join语法：语法详解，图解：
在这里插入图片描述

SQL39 21年8月份练题总数

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

selectcount(distinct device_id) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date like "2021-08%"

限定条件：2021年8月份，匹配date字段即可，匹配方法主要有三种：
（1）like语法：date like “2021-08%”
（2）year、month函数：year(date)=‘2021’ and month(date)=‘08’；
（3）date_format函数：date_format(date, ‘%Y-%m’)=‘2021-08’;
2：总用户数：count函数计数，因为用户有重复，所以需要distinct去重，即count(distinct device_id)
3：总次数：count(question_id)即可