SQL基础查询--记录

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

select *

from user_profile

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

SELECT device_id,gender,age,university

from user_profile

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

两个解法：

（1） 使用DISTINCT：

SELECT DISTINCT university from user_profile

（2）使用group by：

SELECT university from user_profile GROUP BY university

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

select device_id from user_profile limit 2

select device_id from user_profile limit 0,2

select device_id from user_profile limit 2 offset 0

select device_id from user_profile where id in(1,2)

select device_id from user_profile where id <=2

select device_id from user_profile where id=1 or id=2

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

select device_id as user_infos_example

from user_profile limit 2

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

select device_id,university

from user_profile

where university = "北京大学"

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

用户信息表：user_profile

select device_id,gender,age,university

from user_profile

where age > 24

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

用户信息表：user_profile

select device_id,gender,age

from user_profile

where age >=20 and age<=23

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

select device_id,gender,age,university

from user_profile

where university <> "复旦大学"

# where university != "复旦大学"

# where not university = '复旦大学'

# where university not in('复旦大学')

# where university not like '复旦大学'

mysql中，其实单引号和双引号没啥区别，单引号和双引号都可以表示字符串

10.现在运营想要对用户的年龄分布开展分析，在分析时想要剔除没有获取到年龄的用户，

请你取出所有年龄值不为空的用户的设备ID，性别，年龄，学校的信息。

select device_id,gender,age,university

from user_profile

where age is not null and age <> ""

(在实际工作中，空字符串也会导致空值，所以在常规操作中 is not null and 列 <> "" 基本同时出现)

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

select device_id,gender,age,university,gpa

from user_profile

where gender = "male" and gpa > 3.5

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

select device_id,gender,age,university,gpa

from user_profile

where university = "北京大学" or gpa > 3.7

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

select device_id,gender,age,university,gpa

from user_profile

where university in ("北京大学","复旦大学","山东大学")

# WHERE university='北京大学' or university='复旦大学' or university='山东大学'

14.现在运营想要找到gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学进行用户调研

(1)

select device_id,gender,age,university,gpa

from user_profile

where (gpa > 3.5 and university = "山东大学")

or (gpa > 3.8 and university = "复旦大学")

(2)

SELECT device_id,gender,age,university,gpa FROM user_profile

WHERE gpa>3.5 AND university='山东大学'

UNION ALL

SELECT device_id,gender,age,university,gpa FROM user_profile

WHERE gpa>3.8 AND university='复旦大学'

ORDER BY device_id ASC

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

_：匹配任意一个字符；

SELECT * FROM 学生表 WHERE name LIKE '张__'//查询姓“张”且名字是3个字的学生姓名。

%：匹配0个或多个字符；

SELECT * FROM 学生表 WHERE 姓名 LIKE ‘张%’//查询学生表中姓‘张’的学生的详细信息。

[ ]：匹配[ ]中的任意一个字符(若要比较的字符是连续的，则可以用连字符“-”表 达 )；

SELECT * FROM 学生表 WHERE 姓名 LIKE '[张李刘]%’//查询学生表中姓‘张’、姓‘李’和姓‘刘’的学生的情况。

[^ ]：不匹配[ ]中的任意一个字符。

SELECT * FROM 学生表 WHERE 学号 LIKE '%[^235]' //从学生表表中查询学号的最后一位不是2、3、5的学生信息。

(1)select device_id,age,university

from user_profile

where university like '%北京%';

(2)SELECT?device_id,?age,?university

FROM?user_profile

WHERE?university?REGEXP?"北京"

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

#使用聚合函数取最大值

select max(gpa)

from user_profile

where university = '复旦大学'

***保留一位小数

select round(max(gpa),1)

from user_profile

where university = '复旦大学'

或者

#通过gpa倒叙，然后取第一条

select gpa

from user_profile

where university = '复旦大学'

order by gpa DESC

limit 1

17.现在运营想要看一下男性用户有多少人以及他们的平均gpa是多少（注意新生成列去计算）

select count(gender) as male_num,round(avg(gpa),1) as avg_gpa

from user_profile

where gender='male';

18.现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析，

请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。

SELECT gender, university, count(gender) 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 gender, university

19.现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，

请取出平均发贴数低于5的学校或平均回帖数小于20的学校。

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

sql聚合函数有：1、avg函数；2、count函数；3、max函数；4、min函数；5、sum函数；6、grouping函数；7、checksum函数；

8、stdev函数；9、stdevp函数；10、var函数；11、varp函数等等。

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

20.现在运营想要查看不同大学的用户平均发帖情况，并期望结果按照平均发帖情况进行升序排列

select university, avg(question_cnt) as avg_question_cnt

from user_profile

group by university

order by avg_question_cnt

21.现在运营想要查看所有来自浙江大学的用户题目回答明细情况

限定条件：来自浙江大学的用户，学校信息在用户画像表，答题情况在用户练习明细表，因此需要通过device_id关联两个表的数据；

方法1：join两个表，用inner join，条件是on up.device_id=qpd.device_id and up.university='浙江大学'

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

方法2：先从画像表找到浙江大学的所有学生id列表where university='浙江大学'，再去练习明细表筛选出id在这个列表的记录，用where in

select device_id, question_id, result

from question_practice_detail

where device_id in (

select device_id from user_profile

where university='浙江大学'

)

order by question_id

22.运营想要了解每个学校答过题的用户平均答题数量情况

限定条件：无；

每个学校：按学校分组，group by university

平均答题数量：在每个学校的分组内，用总答题数量除以总人数即可得到平均答题数量count(question_id) / count(distinct device_id)。

表连接：学校和答题信息在不同的表，需要做连接

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

23.运营想要计算一些参加了答题的不同学校、不同难度的用户平均答题量

限定条件：无；

每个学校：按学校分组group by university

不同难度：按难度分组group by difficult_level

平均答题数：总答题数除以总人数count(qpd.question_id) / count(distinct qpd.device_id)

来自上面信息三个表，需要联表，up与qpd用device_id连接，qd与qpd用question_id连接。

select

university,

difficult_level,

round(count(qpd.question_id) / count(distinct qpd.device_id), 4) as avg_answer_cnt

from question_practice_detail as qpd

left join user_profile as up

on up.device_id=qpd.device_id

left join question_detail as qd

on qd.question_id=qpd.question_id

group by university, difficult_level

24.运营想要查看参加了答题的山东大学的用户在不同难度下的平均答题题目数

select

"山东大学" as university,

difficult_level,

count(qpd.question_id) / count(distinct qpd.device_id) as avg_answer_cnt

from question_practice_detail as qpd

inner join user_profile as up

on up.device_id=qpd.device_id and up.university="山东大学"

inner join question_detail as qd

on qd.question_id=qpd.question_id

group by difficult_level

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

限定条件：学校为山东大学或者性别为男性的用户：university='山东大学', gender='male'；

分别查看&结果不去重：所以直接使用两个条件的or是不行的，直接用union也不行，要用union all，

分别去查满足条件1的和满足条件2的，然后合在一起不去重

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'

26.

知识：

CASE函数

是一种多分支的函数，可以根据条件列表的值返回多个可能的结果表达式中的一个。

可用在任何允许使用表达式的地方，但不能单独作为一个语句执行。

分为：

简单CASE函数

搜索CASE函数

简单 CASE函数

CASE 测试表达式

WHEN 简单表达式1 THEN 结果表达式1

WHEN 简单表达式2 THEN 结果表达式2 …

WHEN 简单表达式n THEN 结果表达式n

[ ELSE 结果表达式n+1 ]

END

计算测试表达式，按从上到下的书写顺序将测试表达式的值与每个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岁及以上两个年龄段，分别查看这两个年龄段用户数量

（1）

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

（2）

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

FROM user_profile

GROUP BY age_cut

27.将用户划分为20岁以下，20-24岁，25岁及以上三个年龄段，分别查看不同年龄段用户的明细情况，年龄为空输出为其他

（1）

select

device_id,

gender,

case

when age>=25 then '25岁及以上'

when age>=20 then '20-24岁'

when age<20 then '20岁以下'

else '其他'

end as age_cut

from user_profile

（2）

SELECT device_id, gender,

IF (age <20, '20岁以下',

IF(age >=25, '25岁及以上',

IF(age BETWEEN 20 AND 24, '20-24岁', '其他'))) AS age_cut

FROM user_profile

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

Select day(date) as day, count(question_id) as question_cnt

From question_practice_detail

Where year(date)=2021 and month(date)=08

Group by day

--法一：like运算符

select

day(date) as day,

count(question_id) as question_cnt

from question_practice_detail

where date like '2021-08%'

group by day(date);

--法二：regexp运算符

select

day(date) as day,

count(question_id) as question_cnt

from question_practice_detail

where date regexp '2021-08'

group by day(date);

--法三：substring提取日期

select

day(date) as day,

count(question_id) as question_cnt

from question_practice_detail

where substring(date,1,7) = '2021-08'

group by day(date);

29.现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。

（1)

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

30.现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，

现在运营想要统计每个性别的用户分别有多少参赛者，请取出相应结果

SELECT SUBSTRING_INDEX(profile,",",-1) gender,COUNT(*) number

FROM user_submit

GROUP BY gender;

31.对于申请参与比赛的用户，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_name

from user_submit;

32.现在运营举办了一场比赛，收到了一些参赛申请，表数据记录形式如下所示，

现在运营想要统计每个年龄的用户分别有多少参赛者，请取出相应结果

select

substring_index(substring_index(profile, ',', 3), ',', -1) as age,

count(device_id) as number

from user_submit

group by age

33.现在运营想要找到每个学校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

34.现在运营想要了解复旦大学的每个用户在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 up

left join question_practice_detail as qpd

on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'

group by up.device_id