Mysql习题

牛客网Sql习题

3.查询结果去重

//有两种方法
1.利用GROUP BY
select
university
from user_profile 
GROUP BY university
2.利用DISTINCT
select
DISTINCT university
from user_profile;

4.查询结果限制返回行数

SELECT 
device_id 
FROM user_profile
LIMIT 0,2;

5.将查询后的列重新命名

SELECT device_id as user_infos_example  FROM user_profile 
limit 0,2

6.查找后排序

select device_id,age 
from user_profile
order by age asc; 

7.查找后多列排序

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

8.降序排序

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

39. 21年8月份练题总数

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

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

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

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

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

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

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

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

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

14.用where过滤空值练习

1.
select
device_id,gender,age,university
from user_profile
where age is not null;
2.
select
device_id,gender,age,university
from user_profile
where age !='null';
3.
select
device_id,gender,age,university
from user_profile
where age<>'null';

15.现在运营想要找到男性且GPA在3.5以上(不包括3.5)的用户进行调研，请你取出相关数据。

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