SQL 非技术快速入门篇

用户信息表：user_profile

设备id（device_id）

性别（gender）

年龄（age）

学校（university）

30天内活跃天数字段（active_days_within_30）

发帖数量字段（question_cnt）

回答数量字段（answer_cnt）

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male	NULL	复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	male	28	复旦大学	3.6	9	6	52

题库练习明细表：question_practice_detail

设备id（device_id）

问题id（question_id）

问题答案对错（result）

id	device_id	question_id	result	date
1	2138	111	wrong	2021-05-03
2	3214	112	wrong	2021-05-09
3	3214	113	wrong	2021-06-15
4	6543	111	right	2021-08-13
5	2315	115	right	2021-08-13
6	2315	116	right	2021-08-14
7	2315	117	wrong	2021-08-15

表：question_detail

问题id（question_id）

问题难易程度（difficult_level）

id	question_id	difficult_level
1	111	hard
2	112	medium
3	113	easy
4	115	easy
5	116	medium
6	117	easy

示例：user_submit

用户信息（profile）

博客地址（blog_url）

device_id	profile	blog_url
2138	180cm,75kg,27,male	http:/url/bigboy777
3214	165cm,45kg,26,female	http:/url/kittycc
6543	178cm,65kg,25,male	http:/url/tiger
4321	171cm,55kg,23,female	http:/url/uhksd
2131	168cm,45kg,22,female	http:/urlsydney

01 基础查询

基础查询

SQL1 查询多列

题目：查看用户的设备id、性别、年龄和学校的数据。

SELECT device_id, gender, age, university FROM user_profile

SQL2 查询所有列

题目：查看用户信息表中所有的数据。

SELECT id, device_id, gender, age, university, province FROM user_profile

SQL3 查询结果去重

题目：查看用户来自于哪些学校的去重数据。

SELECT DISTINCT university FROM user_profile

SQL4 查询结果限制返回行数

题目：查看前2个用户明细设备ID数据。

SELECT device_id FROM user_profile WHERE id BETWEEN 1 AND 2

SQL5 将查询后的列重新命名

题目：查看前2个用户明细设备ID数据，并将列名改为 'user_infos_example'。

SELECT device_id as user_infos_example FROM user_profile LIMIT 2

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 NOT university = '复旦大学'

SQL10 用where过滤空值练习

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

SELECT device_id, gender, age, university FROM user_profile WHERE age != 0

高级操作符

SQL11 高级操作符练习(1)

题目：取出男性且GPA在3.5以上(不包括3.5)的用户数据。

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

SQL12 高级操作符练习(2）

题目：取出学校为北大或GPA在3.7以上(不包括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)

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

题目：取出所有大学中带有北京的用户的信息。

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

03 高级查询

计算函数

SQL16 查找GPA最高值

题目：取出复旦大学学生gpa最高值的相应数据。

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

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

题目：取出男性用户有多少人以及他们的平均 gpa 的相应数据。

SELECT COUNT(gender) as male_num, AVG(gpa) as avg_gpa
FROM user_profile
WHERE gender = 'male'

分组查询

SQL18 分组计算练习题

题目：取出每个学校每种性别的用户数、30天内平均活跃天数 和 平均发帖数量。

SELECT gender, university, count(device_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 p.device_id, q.question_id, q.result
FROM user_profile p, question_practice_detail q
WHERE p.university = '浙江大学' AND p.device_id = q.device_id
ORDER BY q.question_id

链接查询

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

题目：取出每个学校答过题的用户平均答题数量情况的数据。

SELECT u.university, count(u.university) / count(DISTINCT q.device_id) AS avg_answer_cnt
FROM user_profile u, question_practice_detail q
WHERE u.device_id=q.device_id
GROUP BY u.university

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

题目：取出参加答题的不同学校、不同难度的用户平均答题量的相应数据。

SELECT university, difficult_level, COUNT(q.question_id) / COUNT(DISTINCT q.device_id) avg_answer_cnt
FROM question_practice_detail q, user_profile u, question_detail d
WHERE q.device_id=u.device_id AND q.question_id=d.question_id
GROUP BY university, difficult_level

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

题目：查看参加答题的山东大学的用户在不同难度下的平均答题题目数的相应数据。

SELECT university, difficult_level, COUNT(q.question_id) / COUNT(DISTINCT q.device_id) as avg_answer_cnt
FROM user_profile as u
INNER JOIN question_practice_detail as q
ON u.device_id = q.device_id
INNER JOIN question_detail as d
ON q.question_id = d.question_id
WHERE university = '山东大学'
GROUP BY 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'

05 必会的常用函数

条件函数

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

题目：分别查看25岁以下和25岁及以上两个年龄段的用户数量。(age为null 也记为 25岁以下)

SELECT if(age >= 25, '25岁及以上', '25岁以下') as age_cut, COUNT(device_id) as number
FROM user_profile
GROUP BY age_cut

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

题目：分别查看20岁以下，20-24岁，25岁及以上三个年龄段，用户的明细情况。（注：若年龄为空请返回其他。）

SELECT device_id, gender, CASE WHEN age < 20 THEN '20岁以下'
WHEN age >= 20 AND age <= 24 THEN '20-24岁'
WHEN age >= 25 THEN '25岁及以上'
WHEN age is null THEN '其他'
END 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
WHERE date LIKE '2021-08-%'
GROUP BY date;

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

题目：取出用户在某天刷题后第二天还会再来刷题的平均概率的相应数据。

SELECT COUNT(DISTINCT q2.device_id, q2.date) / COUNT(DISTINCT q1.device_id, q1.date) as avg_ret
FROM question_practice_detail q1
LEFT JOIN question_practice_detail q2
ON q1.device_id = q2.device_id and DATEDIFF(q1.date, q2.date) = 1

文本函数

SQL30 统计每种性别的人数

题目：取出每个性别的用户分别有多少参赛者的相应结果。

SELECT substring_index(profile, ',', -1) as gender, COUNT(*) AS number
FROM user_submit
GROUP BY gender

SQL32 截取出年龄

题目：取出每个年龄的用户分别有多少参赛者的相应结果。

SELECT age, COUNT(*) number
FROM (SELECT SUBSTR(PROFILE, 12, 2) age
FROM user_submit) ages
GROUP BY age

SQL31 提取博客URL中的用户名

题目：申请参与比赛的用户的 blog_url 字段中url字符后的字符串为用户个人博客的用户名，取出用户的个人博客用户字段单独记录为一个新的字段。

SELECT device_id , substring_index(blog_url, '/', -1) as user_name
FROM user_submit

窗口函数

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

题目：取出每个学校的最低gpa。

SELECT device_id, university, gpa
FROM (
    SELECT *, ROW_NUMBER() over (PARTITION BY university ORDER BY gpa) AS rn
    FROM user_profile
) AS temp
WHERE temp.rn = 1

06 综合练习

综合练习

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

题目： 取出复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，没有练习过的用户，答题数结果返回0。

SELECT
t1.device_id,
t1.university,
SUM(CASE WHEN t2.result IS NOT null THEN 1 ELSE 0 END),
SUM(CASE WHEN t2.result = 'right' THEN 1 ELSE 0 END)
FROM user_profile t1
LEFT JOIN question_practice_detail t2 
ON t1.device_id = t2.device_id AND MONTH(t2.date) = '08'
WHERE t1.university = '复旦大学'
GROUP BY t1.device_id

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

题目：取出浙江大学的用户在不同难度题目下答题的正确率情况，并按照准确率升序输出。

SELECT d.difficult_level, 
(SUM(CASE WHEN q.result='right' THEN 1 ELSE 0 END) / COUNT(q.result)) correct_rated
FROM user_profile u, question_practice_detail q, question_detail d
WHERE u.university = '浙江大学' AND u.device_id = q.device_id AND q.question_id = d.question_id
GROUP BY d.difficult_level
ORDER BY correct_rated ASC

SQL39 21年8月份练题总数

题目： 取出2021年8月份所有练习过题目的总用户数和练习过题目的总次数的相应结果。

SELECT COUNT(DISTINCT device_id), COUNT(question_id)
FROM question_practice_detail
WHERE date LIKE '2021-08%'