sql查询面试题复习

一、单表查询

1.1 查所有的数据

select * from user_profile;

1.2 查询某几列

select device_id,gender,age,university from user_profile;

1.3 取出学校的去重数据（去重）

select distinct university from user_profile; #distinct 关键字

1.4 查看前2个用户明细设备ID数据 （前n行数据）

select device_id from user_profile where id<=2;
或者
select device_id from user_profile limit 2;  # 关键字limit用于限制查询结果返回的数据的数量
select device_id from user_profile limit 0,2;

1.5 查看前2个用户明细设备ID数据，并将列名改为 ‘user_infos_example’（修改列名）

select device_id as user_infors_example from user_profile limit 2; # as可以被用作重命名列名或者表名

1.6 取出用户信息表中的用户年龄，请取出相应数据，并按照年龄升序排序。(排序)

select device_id,age from user_profile order by age; # order by 关键字

1.7 取出用户信息表中的年龄和gpa数据，并先按照gpa升序排序，再按照年龄升序排序输出 (多字段排序)

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

1.8 取出用户信息表中的年龄和gpa数据，并先按照gpa降序排序，再按照年龄降序排序输出(多字段排序，降序)

select device_id,gpa,age from user_profile order by gpa desc,age desc; # desc关键件字

1.9 选出所有北京大学的学生device_id,university字段 （条件查询 = ）

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

1.10 取24岁以上的设备ID、性别、年龄、学校。（条件查询 >）

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

1.11查看所有大学中带有北京的用户的信息 (模糊查询 )

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

1.12 对20岁及以上，23及以下的用户开展分析,取出满足条件的设备ID、性别、年龄。（多条件查询 and ）

select device_id,gender,age from user_profile where age>=20 and age<=23;

1.13 找到男性且GPA在3.5以上（多条件查询 and）

select device_id,gender,age,university,gpa from user_profile where gender = "male" and gpa>3.5; 
# 单等号

查看除复旦大学以外的所有用户明细 （条件查询 !=）

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

用where过滤空值练习 （条件查询 过滤）

select device_id,gender,age,university from user_profile where age !='';
或者
select device_id,gender,age,university from user_profile where age is not null;

学校为北大或GPA在3.7以上（多条件查询 or）

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

复旦大学学生gpa最高值是多少 (max 函数)

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

gpa在3.5以上(不包括3.5)的山东大学用户 或 gpa在3.8以上(不包括3.8)的复旦大学同学

select device_id,gender,age,university,gpa from user_profile where (gpa>3.5 and
 university='山东大学') or (gpa>3.8 and university='复旦大学');
 # and的优先级大于or

男性用户人数以及他们的平均gpa是多少 （avg函数，count函数）

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

学校用户的平均发贴和回帖情况，寻找低活跃度学校进行重点运营，请取出平均发贴数低于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; # group by 

不同大学的用户平均发帖情况，并按照平均发帖情况进行升序排列

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

二、多表查询

select user_profile.device_id,question_id,result
from question_practice_detail,user_profile 
where question_practice_detail.device_id = user_profile.device_id 
and  university='浙江大学';

每个学校答过题的用户平均答题数量情况，请你取出数据

select
  university,
  count(question_id)/count(distinct user_profile.device_id) as avg_answer_cnt
from
  user_profile,
  question_practice_detail
where
  user_profile.device_id = question_practice_detail.device_id
  group by university;

用户划分为25岁以下和25岁及以上两个年龄段，分别查看这两个年龄段用户数量 （case when end）

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