牛客SQL刷题

发表于

本文主要介绍牛客网上SQL刷题的题目。

后端开发，必须要学SQL，不学习SQL怎么行。

关键词：SQL

构造数据

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32)  NOT NULL);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学','Shanghai');
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学','BeiJing');
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学','ZheJiang');
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学','Shandong');;

查询所有列

一般不用*

1
select id, device_id, gender, age, university, province from  user_profile;

查询多列

1
select device_id, gender, age, university from  user_profile;

查询用户所在学校并去重

1
2
select distinct university from user_profile;
select university from user_profile group by university;

查询前两条device_id

1
SELECT device_id FROM user_profile LIMIT 2;

给查询后的列重新命名

1
SELECT device_id as user_infos_example FROM user_profile LIMIT 2;

查询指定学校的信息

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

查询年龄大于24岁的信息

1
SELECT device_id, gender, age, university from user_profile where age > 24;

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

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

查询不是复旦大学的信息

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

查询年龄不为null的信息

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

查询GPA>3.5的男性信息

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

查询学校为北大或GPA在3.7以上(不包括3.7)信息

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

查询学校为北大、复旦和山大的同学

推荐使用第二种方法

1
2
select device_id, gender, age, university, gpa from user_profile where university = '北京大学' or university = '复旦大学' or university = '山东大学';
select device_id, gender, age, university, gpa from user_profile where university in('北京大学', '复旦大学', '山东大学'); # 复用

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

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

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

1
2
3
select device_id, age, university from user_profile where university REGEXP '.*北京.*'; # 正则
select device_id, age, university from user_profile where university LIKE '%北京%'; # 模糊匹配
select device_id, age, university from user_profile where locate('北京', university) > 0; # 查找子串

查找GPA最高值

1
2
select max(gpa) as gpa from user_profile where university = '复旦大学'; # max函数
select gpa from user_profile where university = '复旦大学' order by gpa desc limit 1; # 降序排列

计算男生人数平均GPA，其中平均GPA保留小数点后一位

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

计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量

1
2
3
4
5
6
7
select gender,
       university,
       count(*)                             as user_num,
       round(avg(active_days_within_30), 1) as avg_active_day,
       round(avg(question_cnt), 1)          as avg_question_cnt
from user_profile
group by gender, university;  #多分组，分组计算

取出平均发贴数低于5的学校或平均回帖数小于20的学校

1
2
3
4
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; # 分组过滤，不能用where，要用having

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