SQL刷题之单表查询

基础练习

查询所有列

sql:

select 
    *
from
    user_profile;

查询多列

sql:

select
  device_id,
  gender,
  age,
  university
from
  user_profile

查询结果去重

sql:

select
  distinct university
from
  user_profile

方法二：

SELECT university 
FROM user_profile
GROUP BY university 

查询结果限制返回行数

sql:

select
  device_id
from
  user_profile
limit
  2

将查询后的列重新命名

sql:

select
  device_id as 'user_infos_example'
from
  user_profile
where
  id between 1 and 2

查找学校是北大的学生信息

sql:

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

方法二：

select
  device_id,
  university
from
  user_profile
where
  university = '北京大学'
and device_id = user_profile.device_id; # 索引覆盖

查找后排序

sql:

select
  device_id,
  age
from
  user_profile
order by
  age

查找后多列排序

sql:

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

查找后降序排列

sql:

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

查找年龄大于24岁的用户信息

sql:

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

查找某个年龄段的用户信息

sql:

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

查找除复旦大学的用户信息

sql:

select
  device_id,
  gender,
  age,
  university
from
  user_profile
where
  university not in ('复旦大学')

或者：

select
  device_id,
  gender,
  age,
  university
from
  user_profile
where
  university != ('复旦大学')

用where过滤空值练习

sql:

select
  device_id,
  gender,
  age,
  university
from
  user_profile
where
  age is NOT NULL

高级操作符练习(1)

sql:

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

高级操作符练习(2）

sql:

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

Where in 和Not in

sql:

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

操作符混合运用

sql:

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

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

sql:

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

高级查询

查找GPA最高值

sql:

select
  gpa
from
  user_profile
where
  university = '复旦大学'
order by
  gpa desc # 降序
limit
  1 # 限制输出

或者：

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

计算男生人数以及平均GPA

sql:

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

分组计算练习题

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

分组过滤练习题

题目：现在运营想查看每个学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于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