SQL刷题之单表查询
【摘要】
文章目录
基础练习查询所有列查询多列查询结果去重查询结果限制返回行数将查询后的列重新命名查找学校是北大的学生信息查找后排序查找后多列排序查找后降序排列查找年龄大于24岁的用户信息查找某个年龄段的用...
基础练习
查询所有列
sql:
select
*
from
user_profile;
- 1
- 2
- 3
- 4
查询多列
sql:
select
device_id,
gender,
age,
university
from
user_profile
- 1
- 2
- 3
- 4
- 5
- 6
- 7
查询结果去重
sql:
select
distinct university
from
user_profile
- 1
- 2
- 3
- 4
方法二：
SELECT university
FROM user_profile
GROUP BY university
- 1
- 2
- 3
查询结果限制返回行数
sql:
select
device_id
from
user_profile
limit
2
- 1
- 2
- 3
- 4
- 5
- 6
将查询后的列重新命名
sql:
select
device_id as 'user_infos_example'
from
user_profile
where
id between 1 and 2
- 1
- 2
- 3
- 4
- 5
- 6
查找学校是北大的学生信息
sql:
select
device_id,
university
from
user_profile
where
university = '北京大学'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
方法二：
select
device_id,
university
from
user_profile
where
university = '北京大学'
and device_id = user_profile.device_id; # 索引覆盖
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
查找后排序
sql:
select
device_id,
age
from
user_profile
order by
age
- 1
- 2
- 3
- 4
- 5
- 6
- 7
查找后多列排序
sql:
select
device_id,
gpa,
age
from
user_profile
order by
gpa,
age
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查找后降序排列
sql:
select
device_id,
gpa,
age
from
user_profile
order by
gpa desc,
age desc
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查找年龄大于24岁的用户信息
sql:
select
device_id,
gender,
age,
university
from
user_profile
where
age > 24
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
查找某个年龄段的用户信息
sql:
select
device_id,
gender,
age
from
user_profile
where
age between 20 and 23
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
查找除复旦大学的用户信息
sql:
select
device_id,
gender,
age,
university
from
user_profile
where
university not in ('复旦大学')
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
或者：
select
device_id,
gender,
age,
university
from
user_profile
where
university != ('复旦大学')
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
用where过滤空值练习
sql:
select
device_id,
gender,
age,
university
from
user_profile
where
age is NOT NULL
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
高级操作符练习(1)
sql:
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
gpa > 3.5
and gender = 'male'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
高级操作符练习(2）
sql:
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
university = '北京大学'
or gpa > 3.7
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
Where in 和Not in
sql:
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
university in ('北京大学', '复旦大学', '山东大学')
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
操作符混合运用
sql:
select
device_id,
gender,
age,
university,
gpa
from
user_profile
where
gpa > 3.5
and university = '山东大学'
or gpa > 3.8
and university = '复旦大学'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
查看学校名称中含北京的用户
sql:
select
device_id,
age,
university
from
user_profile
where
university like '北京%'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
高级查询
查找GPA最高值
sql:
select
gpa
from
user_profile
where
university = '复旦大学'
order by
gpa desc # 降序
limit
1 # 限制输出
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
或者：
select
max(gpa)
from
user_profile
where
university = '复旦大学'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
计算男生人数以及平均GPA
sql:
select
count(gender) as male_num,
avg(gpa) as avg_gpa
from
user_profile
where
gender = 'male'
- 1
- 2
- 3
- 4
- 5
- 6
- 7
分组计算练习题
sql:
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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
分组过滤练习题
题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
sql:
#聚合函数结果作为筛选条件时，不能用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
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
分组排序练习题
sql:
select
university,
avg(question_cnt) as avg_question_cnt
from
user_profile
group by
university
order by
avg_question_cnt
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
恭喜
单表查询你已经基本的知识会了，恭喜入门！本篇题目都很简单，如果平均每个题你能在一分钟内完成就算基本掌握了。
文章来源: chuanchuan.blog.csdn.net，作者：川川菜鸟，版权归原作者所有，如需转载，请联系作者。
原文链接：chuanchuan.blog.csdn.net/article/details/126669368
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）