SQL server操作题练习

文章目录

• 1.0基础查询

• 1.1 查询所有列（1）
• 1.2 查询多列（2）
• 1.3 查询结果去重（3）
• 1.4 查询结果限制返回行数（4）
• 1.5 将查询后的列重新命名（5）

• 2.0条件查询

• 2.1 查找后排序（6）
• 2.2 查找后多列排序（7）
• 2.3 查找后降序排序（8）
• 2.4 查找学校是北大的学生信息（9）
• 2.5 查找年龄大于24的学生信息（10）
• 2.6 查找某个年龄段的学生信息（11）
• 2.7 查找除复旦大学的用户信息（12）
• 2.8 用where过滤空值练习（13）
• 2.9 高级操作符练习（14）
• 2.10 高级操作符练习（15）
• 2.11 Where in和Not in（16）
• 2.12 操作符混合运用（17）
• 2.13 查看学校名称中包含北京的用户（18）

• 3.0 高级查询

• 3.1 查找GPA最高值（19）
• 3.2 计算男生人数以及平均GPA（20）
• 3.3 分组计算练习题（21）
• 3.4 分组过滤练习题（22）
• 3.5 分组排序练习题（23）

• 4.0 多表查询

• 4.1 浙江大学用户题目回答情况（24）
• 4.2 统计每个学校的答过题的用户的平均答题数（25）
• 4.3 统计每个学校各难度的平均刷题数（26）
• 4.4 统计每个用户的平均刷题数（27）
• 4.5 查找山东大学或者性别为男生的信息（28）

• 5.0 必会的常用函数

• 5.1 计算25岁以上和以下的用户数量（29）
• 5.2 查看不同年龄段的用户明细（30）
• 5.3 计算用户8月每天的练题数量（31）
• 5.4 计算用户的平均次日留存率（32）
• 5.5 统计每种性别的人数（33）
• 5.6 截取出年龄（34）
• 5.7 提取博客URL中的用户名（35）
• 5.8 找出每个学校GPA最低的同学（36）

• 6.0综合练习

• 6.1 统计复旦用户8月练题情况（37）
• 6.2 浙大不同难度题目的正确率（38）
• 6.3 21年8月份练题总数（39）

• 7.0知识点总结

• 7.1常用关键字总结
• 7.2常用函数总结
• 7.3连接查询总结

• 7.3.1INNER JOIN 关键字
• 7.3.2LEFT JOIN 关键字
• 7.3.3RIGHT JOIN 关键字
• 7.3.4FULL OUTER JOIN 关键字

1.0基础查询

1.1 查询所有列（1）

■题解：

select * from user_profile

◆涉及知识点：

略

1.2 查询多列（2）

题解：

select  device_id,gender,age,university from user_profile

**涉及知识点：**略

1.3 查询结果去重（3）

■题解：

select distinct university from user_profile

◆涉及知识点：

distinct用于去重，放在要查询的列前面

1.4 查询结果限制返回行数（4）

■题解：

select device_id 
from user_profile
where id between 1 and 2;

◆涉及知识点：

查询id在1~2之间的内容

1.5 将查询后的列重新命名（5）

■题解：

select device_id  user_infos_example #对列进行重命名
from user_profile                 
limit 2            #限制输出为前两行

◆涉及知识点：

limit限制输出

将需要更改的列名放在查询列名的后面即可对列名进行修改

2.0条件查询

2.1 查找后排序（6）

■题解：

select device_id,age 
from user_profile
order by age

◆涉及知识点：

order by排序

2.2 查找后多列排序（7）

■题解：

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

◆涉及知识点：

order by排序

2.3 查找后降序排序（8）

■题解：

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

◆涉及知识点：

order by默认为升序排序即从小到大进行排序

在排序的列后，加上desc则表示为降序排序

2.4 查找学校是北大的学生信息（9）

■题解：

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

◆涉及知识点：

where语句的使用

2.5 查找年龄大于24的学生信息（10）

■题解：

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

◆涉及知识点：

where与运算符的使用

2.6 查找某个年龄段的学生信息（11）

■题解：

select device_id,gender,age 
from user_profile
where age BETWEEN 20 and 23

◆涉及知识点：

between的是使用

2.7 查找除复旦大学的用户信息（12）

■题解：

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

◆涉及知识点：

2.8 用where过滤空值练习（13）

■题解：

select device_id,gender,age,university
from user_profile
where age!="NULL"

◆涉及知识点：

运算符=与!=的使用

2.9 高级操作符练习（14）

■题解：

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

◆涉及知识点：

and的使用

2.10 高级操作符练习（15）

■题解：

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

◆涉及知识点：

or的使用

2.11 Where in和Not in（16）

■题解：

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

◆涉及知识点：

如果只包含四所大学，那么就可以用not in

2.12 操作符混合运用（17）

■题解：

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

◆涉及知识点：

2.13 查看学校名称中包含北京的用户（18）

■题解：

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

◆涉及知识点：

模糊查询

3.0 高级查询

3.1 查找GPA最高值（19）

■题解：

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

◆涉及知识点：

max（）函数的使用

3.2 计算男生人数以及平均GPA（20）

■题解：

select  count(gender) mail_num,avg(gpa) avg_gpa 
from user_profile
where gender='male'

◆涉及知识点：

count ()函数

avg()函数

3.3 分组计算练习题（21）

■题解：

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

◆涉及知识点：

group by()分组语句的使用

3.4 分组过滤练习题（22）

■题解：

select university,
       avg(question_cnt) 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

◆涉及知识点：

Having允许指定条件来过滤将出现在最终结果中的分组结果，对分组后情况进行过滤

WHERE字句在所选列上设置条件，而Having子句则在由GROUP By字句创建的分组上设置条件

3.5 分组排序练习题（23）

■题解：

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

◆涉及知识点：

先分组再排序

4.0 多表查询

4.1 浙江大学用户题目回答情况（24）

■题解：

#解题方式一：子查询
select device_id,question_id,result
from question_practice_detail
where device_id in(
    select device_id
    from user_profile
    where university="浙江大学"
)

#解题方式二：连表查询
select q.device_id,q.question_id,q.result
from question_practice_detail q,user_profile u
where q.device_id=u.device_id and u.university="浙江大学"


#解题方式三：join字句
select a.device_id, a.question_id, a.result
from question_practice_detail as a 
inner join user_profile as b 
on b.device_id=a.device_id and b.university="浙江大学"

◆涉及知识点：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

4.2 统计每个学校的答过题的用户的平均答题数（25）

■题解：

SELECT university,count(question_id)/count(distinct q.device_id)
FROM user_profile u inner JOIN question_practice_detail q ON u.device_id=q.device_id
GROUP BY university

◆涉及知识点：

4.3 统计每个学校各难度的平均刷题数（26）

描述

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

用户信息表：user_profile

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

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为4321，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

题库练习明细表：question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6534	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong
8	5432	117	wrong
9	5432	112	wrong
10	2131	113	right
11	5432	113	wrong
12	2315	115	right
13	2315	116	right
14	2315	117	wrong
15	5432	117	wrong
16	5432	112	wrong
17	2131	113	right
18	5432	113	wrong
19	2315	117	wrong
20	5432	117	wrong
21	5432	112	wrong
22	2131	113	right
23	5432	113	wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

…

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

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

第一行表示: 题目id为111的难度为hard

…

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算不同学校、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university	difficult_level	avg_answer_cnt
北京大学	hard	1.0000
复旦大学	easy	1.0000
复旦大学	medium	1.0000
山东大学	easy	4.5000
山东大学	medium	3.0000
浙江大学	easy	5.0000
浙江大学	medium	2.0000

解释：

第一行：北京大学有设备id为2138，6543这2个用户，这2个用户在question_practice_detail表下都只有一条答题记录，且答题题目是111，从question_detail可以知道这个题目是hard，故 北京大学的用户答题为hard的题目平均答题为2/2=1.0000

第二行，第三行：复旦大学有设备id为3214，4321这2个用户，但是在question_practice_detail表只有1个用户(device_id=3214有答题，device_id=4321没有答题，不计入后续计算)有2条答题记录，且答题题目是112，113各1个，从question_detail可以知道题目难度分别是medium和easy，故 复旦大学的用户答题为easy, medium的题目平均答题量都为1(easy=1或medium=1) /1 (device_id=3214)=1.0000

第四行，第五行：山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

■题解：

SELECT university,count(question_id)/count(distinct q.device_id)
FROM user_profile u inner JOIN question_practice_detail q ON u.device_id=q.device_id
GROUP BY university

◆涉及知识点：

4.4 统计每个用户的平均刷题数（27）

描述

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

用户信息表：user_profile

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

第一行表示:id为1的用户的常用信息为使用的设备id为2138，性别为男，年龄21岁，北京大学，gpa为3.4，在过去的30天里面活跃了7天，发帖数量为2，回答数量为12

最后一行表示:id为7的用户的常用信息为使用的设备id为432，性别为男，年龄28岁，复旦大学，gpa为3.6，在过去的30天里面活跃了9天，发帖数量为6，回答数量为52

题库练习明细表：question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6534	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong
8	5432	117	wrong
9	5432	112	wrong
10	2131	113	right
11	5432	113	wrong
12	2315	115	right
13	2315	116	right
14	2315	117	wrong
15	5432	117	wrong
16	5432	112	wrong
17	2131	113	right
18	5432	113	wrong
19	2315	117	wrong
20	5432	117	wrong
21	5432	112	wrong
22	2131	113	right
23	5432	113	wrong

第一行表示:id为1的用户的常用信息为使用的设备id为2138，在question_id为111的题目上，回答错误

…

最后一行表示:id为23的用户的常用信息为使用的设备id为5432，在question_id为113的题目上，回答错误

表：question_detail

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

第一行表示: 题目id为111的难度为hard

…

第一行表示: 题目id为117的难度为easy

请你写一个SQL查询，计算山东、不同难度的用户平均答题量，根据示例，你的查询应返回以下结果(结果在小数点位数保留4位，4位之后四舍五入)：

university	difficult_level	avg_answer_cnt
山东大学	easy	4.5000
山东大学	medium	3.0000

山东大学有设备id为5432和2131这2个用户，这2个用户总共在question_practice_detail表下有12条答题记录，且答题题目是112，113，117，且数目分别为3，6，3，从question_detail可以知道题目难度分别为medium,easy,easy，所以，easy共有9个，故easy的题目平均答题量= 9(easy=9)/2 (device_id=3214 or device_id=5432) =4.5000，medium共有3个，medium的答题只有device_id=5432的用户，故medium的题目平均答题量= 3(medium=9)/1 ( device_id=5432) =3.0000

■题解：

select university ,difficult_level,count(qpd.question_id)/count(DISTINCT qpd.device_id) as avg_answer_cnt
from user_profile as u
inner join question_practice_detail as qpd
on u.device_id = qpd.device_id

inner join question_detail as qd
on qpd.question_id = qd.question_id

where university = '山东大学'

group by difficult_level

◆涉及知识点：

两次 inner join on的使用

4.5 查找山东大学或者性别为男生的信息（28）

■题解：

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

SQL UNION 操作符合并两个或多个 SELECT 语句的结果

5.0 必会的常用函数

5.1 计算25岁以上和以下的用户数量（29）

■题解：

select
case when age<25 or age is null then "25岁以下"
     when age>=25 then "25岁及以上"
     end as age_cut,count(*) number
from user_profile
group by age_cut;

◆涉及知识点：

when…then…end语句的使用

5.2 查看不同年龄段的用户明细（30）

■题解：

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

◆涉及知识点：

5.3 计算用户8月每天的练题数量（31）

■题解：

select day(date)  day,count(question_id)  question_cnt
from question_practice_detail
where date like '2021-08-%'
group by date

◆涉及知识点：

日期函数的使用

5.4 计算用户的平均次日留存率（32）

■题解：

SELECT
count(t2.device_id)/count(t1.device_id) as avg_ret
from (
    SELECT DISTINCT device_id, date
    FROM question_practice_detail
) as t1
left join(
    select distinct device_id,date
    from question_practice_detail
) as t2
on t1.device_id=t2.device_id and t2.date=date_add(t1.date,interval 1 day)

◆涉及知识点：

时期函数的使用

5.5 统计每种性别的人数（33）

■题解：

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

◆涉及知识点：

sql中字符的截取操作函数

SUBSTRING_INDEX(s, delimiter, number)

返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

5.6 截取出年龄（34）

■题解：

SELECT
    substring_index(SUBSTRING_INDEX(profile,',',-2),',',1) age,
    count(*)
FROM user_submit
GROUP BY age

◆涉及知识点：

5.7 提取博客URL中的用户名（35）

■题解：

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

◆涉及知识点：

5.8 找出每个学校GPA最低的同学（36）

■题解：

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;

◆涉及知识点：

6.0综合练习

6.1 统计复旦用户8月练题情况（37）

■题解：

select a.device_id,a.university,count(b.question_id) question_cnt,sum(if(b.result = 'right',1,0))

from user_profile a left JOIN question_practice_detail b on a.device_id = b.device_id and month(b.date) = 8 #过滤日期 

where a.university = '复旦大学'

group by a.device_id

◆涉及知识点：

6.2 浙大不同难度题目的正确率（38）

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

示例： user_profile

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		复旦大学	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	female	26	复旦大学	3.6	9	6	52

示例： question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

根据示例，你的查询应返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000

■题解：

SELECT difficult_level, sum(if(result = 'right',1,0)) / count(1) correct_rate
from question_practice_detail a1
left join user_profile a2
on a1.device_id = a2.device_id

left join question_detail a3
on a1.question_id = a3.question_id

where university = '浙江大学'

group by difficult_level
order by correct_rate ASC

◆涉及知识点：

6.3 21年8月份练题总数（39）

■题解：

select count(distinct device_id) did_cnt,count(question_id) question_cnt
from question_practice_detail
where year(date) = 2021 and month(date) = 8

◆涉及知识点：

7.0知识点总结

7.1常用关键字总结

（1）SELECT 语句用于从数据库中选取数据，结果被存储在一个结果表中，称为结果集。

（2）SELECT DISTINCT 语句用于返回唯一不同的值

（3）WHERE 子句用于提取那些满足指定条件的记录

（4） AND & OR 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条 记录

（5）ORDER BY 关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字

（6）INSERT INTO 语句用于向表中插入新记录

（7）UPDATE 语句用于更新表中已存在的记录

（8）DELETE 语句用于删除表中的行

（9）SQL通配符

通配符	描述
%	替代 0 个或多个字符
_	替代一个字符

（10）IN 操作符允许您在 WHERE 子句中规定多个值

（11）BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期

7.2常用函数总结

（1）AVG() 函数返回数值列的平均值。

（2）COUNT() 函数返回匹配指定条件的行数

（3）FIRST() 函数返回指定的列中第一个记录的值

（4）LAST() 函数返回指定的列中最后一个记录的值。

（5）MAX() 函数返回指定列的最大值。

（6）MIN() 函数返回指定列的最小值。

（7）SUM() 函数返回数值列的总数。

（8）GROUP BY 语句可结合一些聚合函数来使用

（9）HAVING 子句可以让我们筛选分组后的各组数据

（10）EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False

（11）UCASE() 函数把字段的值转换为大写

（12）LCASE() 函数把字段的值转换为小写

（13）LEN() 函数返回文本字段中值的长度

（14）日期函数

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间的单独部分
DATE_ADD()	向日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

（15）SUBSTRING_INDEX(s, delimiter, number)

返回从字符串 s 的第 number 个出现的分隔符 delimiter 之后的子串。
如果 number 是正数，返回第 number 个字符左边的字符串。
如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。

SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c

7.3连接查询总结

7.3.1INNER JOIN 关键字

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

INNER JOIN 关键字在表中存在至少一个匹配时返回行

7.3.2LEFT JOIN 关键字

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL

7.3.3RIGHT JOIN 关键字

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL

7.3.4FULL OUTER JOIN 关键字

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;

FULL OUTER JOIN 关键字只要左表（table1）和右表（table2）其中一个表中存在匹配，则返回行.