恶补SQL中）牛客SQL题

SQL1 查询所有列

select * from user_profile;

SQL2 查询多列 

select device_id,gender,age,university from user_profile;

SQL3 查询结果去重 

select distinct university from user_profile;

 SQL4 查询结果限制返回行数

select device_id from user_profile limit 2;

SQL5 将查询后的列重新命名

select device_id as 'user_infos_example' from user_profile limit 2;

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

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

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

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

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

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

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

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

 SQL10 用where过滤空值练习

select device_id,gender,age,university from user_profile where age is not null;

 SQL36 查找后排序

select device_id,age from user_profile order by age asc;

 SQL37 查找后多列排序

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

 SQL38 查找后降序排列

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

 SQL11 高级操作符练习(1)

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

 SQL12 高级操作符练习(2）

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

 SQL13 Where in 和Not in

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

 SQL14 操作符混合运用

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

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

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