SQL刷题之单表查询

基础练习

查询所有列

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

恭喜

单表查询你已经基本的知识会了，恭喜入门！本篇题目都很简单，如果平均每个题你能在一分钟内完成就算基本掌握了。