【第3天】SQL快速入门-高级查询(SQL 小虚竹)

x33g5p2x  于2022-08-17 转载在 其他  
字(5.8k)|赞(0)|评价(0)|浏览(294)

回城传送–》《32天SQL筑基》

零、前言

今天是学习 SQL 打卡的第 3 天,每天我会提供一篇文章供群成员阅读( 不需要订阅付钱 )。

希望大家先自己思考,如果实在没有想法,再看下面的解题思路,自己再实现一遍。在小虚竹JAVA社区 中对应的 【打卡贴】打卡,今天的任务就算完成了,养成每天学习打卡的好习惯。

​ 虚竹哥会组织大家一起学习同一篇文章,所以有什么问题都可以在群里问,群里的小伙伴可以迅速地帮到你,一个人可以走得很快,一群人可以走得很远,有一起学习交流的战友,是多么幸运的事情。

​ 我的学习策略很简单,题海策略+ 费曼学习法。如果能把这些题都认认真真自己实现一遍,那意味着 SQL 已经筑基成功了。后面的进阶学习,可以继续跟着我,一起走向架构师之路。

今天的学习内容是:高级查询

一、练习题目

题目链接难度
计算函数:SQL16 查找GPA最高值★★☆☆☆
计算函数:SQL17 计算男生人数以及平均GPA★★☆☆☆
分组查询:SQL18 分组计算练习题★★★☆☆
分组查询:SQL19 分组过滤练习题★★☆☆☆
分组查询:SQL20 分组排序练习题★★☆☆☆

二、SQL思路

计算函数:SQL16 查找GPA最高值

初始化数据

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,
`gpa` float);
INSERT INTO user_profile VALUES(1,2234,'male',21,'北京大学',3.2);
INSERT INTO user_profile VALUES(2,2235,'male',null,'复旦大学',3.8);
INSERT INTO user_profile VALUES(3,2236,'female',20,'复旦大学',3.5);
INSERT INTO user_profile VALUES(4,2237,'female',23,'浙江大学',3.3);
INSERT INTO user_profile VALUES(5,2238,'male',25,'复旦大学',3.1);
INSERT INTO user_profile VALUES(6,2239,'male',25,'北京大学',3.6);
INSERT INTO user_profile VALUES(7,2240,'male',null,'清华大学',3.3);
INSERT INTO user_profile VALUES(8,2241,'female',null,'北京大学',3.7);

解法

第一种解法:
使用最大值函数:max
补充:最小值函数:min

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

第二种解法:
对gpa字段进行倒序排列,然后取第一个值

select gpa
from user_profile
where university='复旦大学'
order by  gpa DESC
LIMIT 0,1;

第三种解法:
使用mysql 的all 函数

,all关键字必须与一个比较操作符一起使用。any关键词可以理解为“对于子查询返回的列中的任一数值,如果比较结果为true,则返回true”

select gpa 
from user_profile 
where university = '复旦大学' 
and gpa >= all (select gpa from user_profile where university = '复旦大学')

注:这个用法其实用在这题不合适,效率差,只为了演示all 函数的用法

计算函数:SQL17 计算男生人数以及平均GPA

初始化数据

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,
`gpa` float);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8);
INSERT INTO user_profile VALUES(6,2131,'male',28,'北京师范大学',3.3);

解法

要求统计:

  • 男性用户有多少人
  • 他们的平均gpa是多少
  • 平均gpa结果保留到小数点后面1位(1位之后的四舍五入)
    分析:
  • 所以条件是男性
  • 多少人要用计数,所以用count函数
  • 计算平均gpa,要用avg函数
  • 平均值可能小数点位数很多,按照示例保存一位小数,用round函数
  • 对查询的结果列要进行重命名,才符合题目要求
SELECT count(gender) as male_num	,round(avg(gpa),1) as avg_gpa
from user_profile
where gender='male'

分组查询:SQL18 分组计算练习题

初始化数据

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,
`gpa` float,
`active_days_within_30` float,
`question_cnt` float,
`answer_cnt` float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

解法

要求统计:

  • 每个学校每种性别的用户数
  • 30天内平均活跃天数
  • 平均发帖数量

分析:

  • 每个学校的每种性别,这里就是要学校和性别进行分组,使用关键词:group by
  • 每个学校每种性别的用户数,就是要对设备ID进行计数,在分组的基础上进行计数,使用关键字:count
  • 30天内平均活跃天数,是对字段active_days_within_30进行求平均,使用关键词:avg ,而且结果保留1位小数,1位小数之后的四舍五入,这里要用上:round函数
  • 平均发帖数量,是对字段question_cnt进行求平均,使用关键词:avg ,而且结果保留1位小数,1位小数之后的四舍五入,这里要用上:round函数
  • 按题目要求进行对查询列的重命名。
SELECT
  gender,
  university,
  count(device_id) 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
  university,
  gender

分组查询:SQL19 分组过滤练习题

初始化数据

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,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` float,
`answer_cnt` float
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

解法

要求统计:

  • 每个学校用户的平均发贴数
  • 每个学校用户的平均回帖数
  • 条件是平均发贴数低于5的学校或平均回帖数小于20的学校。

分析:

  • 这里是要学校进行分组,使用关键词:group by
  • 平均发贴,是对字段question_cnt进行求平均,使用关键词:avg ,而且结果保留3位小数,3位小数之后的四舍五入,这里要用上:round函数
  • 回帖情况,是对字段answer_cnt进行求平均,使用关键词:avg ,而且结果保留3位小数,3位小数之后的四舍五入,这里要用上:round函数
  • 条件是平均发贴数低于5的学校或平均回帖数小于20的学校,聚合函数结果作为筛选条件时,不能用where,而是用:having语法
  • 按题目要求进行对查询列的重命名。
-- 查询1
SELECT
  university,
  round(AVG(question_cnt),3) as avg_question_cnt,
  round(AVG(answer_cnt),3) as avg_answer_cnt
FROM
  user_profile
GROUP BY
  university
HAVING
  avg_question_cnt < 5
  OR avg_answer_cnt < 20;

分组查询:SQL20 分组排序练习题

初始化数据

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,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int 
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);

解法

要求统计:

  • 查看不同大学的用户平均发帖情况
  • 按照平均发帖情况进行升序排列
  • 隐藏需求:平均发帖情况,结果保留4位小数。

分析:

  • 这里是要学校进行分组,使用关键词:group by
  • 平均发贴,是对字段question_cnt进行求平均,使用关键词:avg ,而且结果保留4位小数,4位小数之后的四舍五入,这里要用上:round函数
  • 按题目要求进行对查询列的重命名。
  • 按照平均发帖情况进行升序排列,使用关键词:order by
select
  university,
  round(avg(question_cnt),4) as avg_question_cnt
from
  user_profile
group by
  university
order by
  avg_question_cnt asc

相关文章