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

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

转载作者：知者更新时间：2024-03-12 08:19:44

回城传送–》《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

文章推荐：【第1天】SQL快速入门-基础查询（SQL 小虚竹)

文章推荐： B+树与B树的区别、Hash索引与B+树索引的区别

文章推荐：【Redis】Redis 的 Java 客户端（Jedis、SpringDataRedis）

16、Hadoop 入门：Hive-入门
Hive —— 入门 Hive介绍 Apache Hive是一款建立在Hadoop之上的开源数据仓库系统，可以将存储在Hadoop文件中的结构化、半结构化数据文件映射为一张数据库表，基于表提供了一
18、Hadoop 入门：HBase-入门
HBase —— 入门 HBase介绍 HBase是一个分布式的、面向列的开源数据库，该技术来源于 Fay Chang 所撰写的Google论文“Bigtable：一个结构化数据的分布式存储系统”
Vue-入门
零：前端目前形势前端的发展史 HTML(5)、CSS(3)、JavaScript(ES5、ES6)：编写一个个的页面 -> 给后端(PHP、Python、Go、Java) ->
JavaScript 入门
在本教程中，您将了解在计算机上运行 JavaScript 的不同方法。 JavaScript 是一种流行的编程语言，具有广泛的应用程序。 JavaScript 以前主要用于使网页具有交
python爬虫三个小案例(入门)
我曾经是一个对编程一窍不通的小白，但因为对互联网世界的好奇心和求知欲的驱使，我踏入了编程的殿堂。在学习的过程中，我发现了一门神奇的编程语言——Python。Python有着简洁、易读的语法，让初学者能
python爬虫三个小案例(入门)
嗨，亲爱的读者们！今天我要给大家分享一些关于Python爬虫的小案例。你是否曾为了获取特定网页上的数据而烦恼过？或者是否好奇如何从网页中提取信息以供自己使用？那么，这篇文章将会给你一些启示和灵感。
iPhone - 入门
关闭。这个问题是opinion-based 。目前不接受答案。想要改进这个问题吗？更新问题，以便 editing this post 可以用事实和引文来回答它。 . 已关闭 8 年前。 Improv
javascript - 入门
我想创建一个像https://apprtc.appspot.com/?r=04188292这样的应用程序。我对 webrtc 了解一点，但无法掌握 google app-engine。如何为 java
Python - 入门
我刚刚开始使用 Python 并编写了一个简单的周边程序。但是，每当我在终端中键入 python perimeter.py 时，都会收到以下错误，我不知道如何解决。 >>> python perime
通俗易懂的Redis数据结构基础教程(入门)
Redis有5个基本数据结构，string、list、hash、set和zset。它们是日常开发中使用频率非常高应用最为广泛的数据结构，把这5个数据结构都吃透了，你就掌握了Redis应用知识的一半了
Servlet第一个项目的发布(入门)
创建发布web项目具体步骤： 1.在开发工具中创建一个dynamic web project helloword 2.在webContent中创建index.html文件 3.发布web应用到
入门：如何更改Ubuntu的终端字体和大小
如果你在 Ubuntu 上使用终端的时间很长，你可能会希望调整终端的字体和大小以获取一种良好的体验。更改字体是一种最简单但最直观的 Linux 的终端自定义的方法。让我
ADODB 入门
1. 前言 ADODB 是 Active Data Objects Data Base 的简称，它是一种 PHP 存取数据库的函式组件。现在 SFS3 系统 (校园自由软件交流网学务系统) 计划的
nosql - neo4j 入门
我对 neo4j 完全陌生，我很抱歉提出这样一个基本问题。我已经安装了neo4j，我正在使用shell“localhost:7474/webadmin/#/console/” 我正在寻找一个很好的例子
ios4 - 核心音频指导/入门
我正在阅读 ios 4 的核心音频，目的是构建一个小测试应用程序。在这一点上，我对所有 api 的研究感到非常困惑。理想情况下，我想知道如何从两个 mp3 中提取一些样本到数组中。然后在回调循环中
ubuntu - GNOME 入门
关闭。这个问题不符合Stack Overflow guidelines .它目前不接受答案。要求我们推荐或查找工具、库或最喜欢的场外资源的问题对于 Stack Overflow 来说是无关紧要的，因
ide - GNUStep 入门
我下载了 GNUStep并安装了它，但是我不确定在哪里可以找到 IDE。有谁知道什么程序可以用作 GNUStep IDE/从哪里获取它们？否则，有没有人知道有关如何创建和编译基本 GNUStep 程序
solr - Solr 入门
我正在尝试开始使用 Apache Solr，但有些事情我不清楚。通读tutorial ，我已经设置了一个正在运行的 Solr 实例。我感到困惑的是 Solr 的所有配置(架构等)都是 XML 格式的。
gis - BruTile 入门
请问有没有关于如何开始使用 BruTile 的文档？我目前正在使用 SharpMap，我需要预缓存切片以加快进程最佳答案我今天正在研究这个:)Mapsui项目site严重依赖 SharpMap
emacs - CEDET 入门
尽我所能，我无法让 CEDET 做任何事情。 Emacs 24.3。我下载了最新的 CEDET 快照。我从他的底部(不是这样)Gentle Introduction 中获取了 Alex Ott 的设置

知者

个人简介

我是一名优秀的程序员,十分优秀！

作者热门文章

滴滴打车优惠券免费领取

全站热门文章

首页

博学

6Ren·AI

商城