SQL奇难怪状知识点分享

SQL执行顺序

select 语句的完整结构：

select 
  去重 要查询的字段
from
  表（注意：表和字段可以取别名）
xxxx（left/right/full） join 要连接的表  on 等值判断（顺序：先on再where）
where
  （具体的值/子查询，不包含聚合函数的过滤条件）
group by
  （通过哪个字段来分组）
having
  （过滤分组后的信息，条件和where一样，位置不同，包含聚合函数的过滤条件）
order by
  通过哪个字段排序
limit
  （分页）

SQL语句的执行顺序：
from -> on -> join -> where -> group by -> having -> select -> distinct -> order by -> limit
image.png
可以尝试记一下sql的执行顺序，对自己写sql以及sql调优有帮助，理解记忆也不是很难。
sql查询最重要的当然是从哪张表中查询，第一个就是from，和join后的表，当然在这之前可以确定一下，关联的字段那就是on，接下来是过滤的条件，就是where，后面紧接着就是当然就是group by和having了，然后就是返回查询结果，select选择返回指定的列，当然是distinct去重后的列，然后便是按照要求返回的数据是不是要排序啊order，返回指定的个数limit。你看，理解记忆一下，是不是还挺简单的。

group by分组题 行转列

create table tableA (Name varchar(10),Course varchar(10) ,Reslut  int) ;  
insert into tableA(Name , Course, Reslut) values('N1' , '语文' , 74) ;
insert into tableA(Name , Course , Reslut) values('N1' , '数学' , 83) ;
insert into tableA(Name , Course , Reslut) values('N1' , '物理' , 93) ;
insert into tableA(Name , Course , Reslut) values('N2' , '语文' , 74) ;
insert into tableA(Name , Course , Reslut) values('N2' , '数学' , 84) ;
insert into tableA(Name , Course , Reslut) values('N2' , '物理' , 94);

image.png
我的记录：
建表之后，表的结构为：
image.png

SELECT
	Name,
	sum(if(course = '语文',reslut,0)) '语文',
	sum(if(course = '数学',reslut,0)) '数学',
	sum(if(course = '物理',reslut,0)) '物理'
FROM
	tablea
GROUP BY name;

这个题就是要注意sum和if函数的使用，其他没有什么难点，好像这道题是面试的重点题哦，好好记住它🥰
肯定是要用到group by函数，但是我group by函数不太熟悉，故记录一下此函数的学习记录。

聚合函数

SQL聚合函数是计算一组数据的集合并返回单个值。
除count以外，聚合函数忽略空值，如果count函数的应用对象是一个确定列名，并在该列存在空值，此时count仍会忽略空值。
因为聚合函数对一组值进行操作，所以它通常与select语句的group by子句一起使用，以计算每个分组提供信息的度量。

group by分组

分组是使用数据库时必须处理的最重要的任务之一。要将行分组，使用group by子句。
group by子句式select语句的可选子句，它根据指定列的匹配将行组合成组，每组返回一行。

在select子句中包含聚合函数不是强制的。但是，如果使用聚合函数，它将计算每个组的汇总值。
需要强调是的，在对行进行分组之前应用where子句，而在对行进行分组之后应用having子句。也就是说，where子句应用于行，而having子句应用于分组。
要对组进行排序，请在group by子句后添加order by子句。
group by子句中出现的列称为分组列，如果分组列包含null值，则所有null值都汇总到一个分组中，因为group by子句认为null值相等。
常见的聚合函数：sum、max、min、avg、count。
group by后面可以接多个列，表示按后面所有列相等分组。
在分组聚合的场景下，哪些字段可以出现在select子句中？

  • 1.常量
  • 2.聚合函数内的字段
  • 3.参与分组的字段

group_concat函数

将group by产生的同一个分组中的值连接起来，返回一个字符串结构。
group_concat函数首先根据group by指定的列进行分组，将同一组的列显示出来，并用分隔符分割。
group_concat函数的语法

group_concat(distinct 字段名 order by 排序字段 asc/desc separator '分隔符')

使用distinct可以排除重复值；
separator是一个字符串值，默认为逗号；

列转行

SELECT 
		lateral view explode()
from tablea

lateral view explode()是Hive SQL中的语法，属于UDF函数，explode()函数也被称为“炸裂函数”，顾名思义，就是将一列数据“炸裂”为多行数据，即有列转行的效果。
lateral view关键字用于扩展查询结构，使得能够在查询中使用函数或操作符处理数组或嵌套数据结构。
explode函数用于将数组或嵌套结构的数据展开成多行数据，每个元素或字段值对应一行。会在explode()函数中指定需要拆分的列名。
这段SQL语法的作用是将某列数据拆分成多行，方便后续的数据处理和分析。

连接ip人数最多

image.png

SELECT
	ip,count(ip) 'ip连接人数最多'
from ip
GROUP BY ip
ORDER BY COUNT(ip) DEsqlUserSC
LIMIT 1

limit和offset用法

limit

limit 开始值，结束值
select * from stu limit m,n;
m：开始值 从第m+1行开始；
n：结束值 供展示n条数据

不能在limit中使用变量，例题《第N高的薪水》
在SQL中，limit子句用于限制查询结构的行数。然后它不允许直接使用变量作为行数的参数。
这是因为在查询计划生成的时候，数据库需要确定查询的结果集大小，以便进行优化和执行。因此，limit子句只能接受常量值，不能接受变量。

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare m int;
set m = N-1;
  RETURN (
       select
                (
                    select 
                        distinct salary
                    from
                        Employee
                    order by salary desc
                    limit m,1
                )
        as getNthHighestSalary
);
END

offset

offset n 去掉几个值
就是跳过n个数据，取第n+1值

conclusion

数据中的数据，计算是从0开始的。

第N高的薪水

image.png
image.png

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    declare m int;
    set m = N - 1;
RETURN (
    select
        distinct salary
    from 
        Employee
    order by salary desc
    limit m,1
);
END

limit函数不能直接用变量，故需要自己定义常量接收，定义函数为：
declare m int;
set m = N;
先用declare关键字定义，再用set设置。
这个题目对我来说本身其实不难，难的其实是我对sql创建函数的陌生，多看看这道题，多写写牢记它🤣

group by+having 或group by + 临时表

image.png
group by + 临时表

select email
from (
  select email,count(email) as num
  from person
  group by email  
)as sta
where num>1;

很简单哇，临时表，就是括号as 表名，select查询出来时一张表，在这张表直接查询就行。
group by+having

select
  Email
FROM
  person
group by Email
having count(Email)>1;

having是用于group by之后对数据进行筛选。

删除重复邮件

image.png
先根据email分组，筛选出每个邮件的最小id；
由于需要的是具体id值来参与条件筛选，所以要筛选出具体id；
有了筛选条件就可以进行删除了。

delete 
from Person 
where Person.id not in (
    select e.i from (
        select min(id) as i from Person group by email
    ) as e
);

这道题最关键就是使用group by分组，然后用聚合函数min去找到最小的id。
测试的时候，我觉得可以使用如下代码，但是在报错了代码：1093 - you can’t specify target table

delete from person
where person.id not in
(
	select min(id) as id from Person group by email
)

image.png
原来是不能同时既在一张表select，又去update到这张表。所以需要增加一张临时表过渡一下。

上升的温度

image.png

select w1.id Id
from Weather w1
join Weather w2
on datediff(w1.recordDate ,w2.recordDate ) = 1
where w1.Temperature > w2.Temperature

datediff函数用于返回两个日期之间的边界数差异。这个函数可以计算两个日期之间的年、月、日、小时、分钟、秒或周数差异。
datediff(datepart，startdate，enddate)
datepart是指定要返回日期部分的参数，比如year、month、day、hour、minute、second、week等
startdate和enddate是要进行差异计算的日期，开始日期和结束日期。

IF表达式 和 case when条件表达式

IF(判断条件，结果1，结果2)
判断条件为true，则返回结果1；否则返回结果2。
if一般适合用于判断两值分类。
case when：

case 
when 列名 = ‘A’ then ‘A1',
when 列名 = ‘B’ then ‘B1’,
when 列名 = ‘C’ then  ‘C1'
else 'D1
end

then后面的值与else后面的值类型应一致。
也可以把列名全部提前到case后面。
case when 一般适用于两值即两值以上分类。
if和case可以作为最后生成的列返回。

事务

-- 开始事务
START TRANSACTION;

-- 执行一些SQL语句
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- 判断是否要提交还是回滚
IF (条件) THEN
    COMMIT; -- 提交事务
ELSE
    ROLLBACK; -- 回滚事务
END IF;

1.开启事务：使用begin或者start transaction命令来开始一个事务。
2：执行SQL语句：
3.判断是否提交或回滚：
根据业务逻辑判断是否要提交或回滚事务。
使用IF条件来判断，如果满足条件则执行commit提交事务，否则执行rollback回滚事务。
4.结束事务：
使用commit命令来提交事务，将所有的修改永久保存到数据库；
使用rollback命令来回滚事务，撤销自上次提交以来所做的所有更改。因为事务是多条sql语句同时执行，要是在事务运行过程中发现了某种故障，事务不能继续执行，系统将事务中对数据库所有已完成的操作全部撤销，滚回到书屋开始时的状态。

窗口函数

它对查询结果的一个窗口（也称为分区）执行计算并返回结果。
窗口函数，也叫OLAP函数（联机分析处理），可以对数据库数据进行实时分析处理，它的基本语法如下：
<窗口函数> over （partition by <用于分组的列名> order by <用于排序的列名>）
窗口函数.png
lag()：查询当前行向上偏移n行对应的结果（过去）
lead()：查询当前行向下偏移n行对应的结果（未来）
窗口函数原则上只能写在**select**子句中。
窗口函数的名称来源于：over子句中的partition by分组，这里的窗口表示数据的范围。总之，窗口函数具备分组和排序功能，不减少原表的行数。
窗口函数简单理解就是rank()、dense_rank()用来获取排名，可以用partition by分组，order by对某一字段的数据进行排名。
当然，也可以省略掉partition by语句，此时表示不进行分区，对目标表内所有的数据根据某列进行排序（不过此时，窗口函数就失去了其功能）。

join和union的区别

  • join(连接查询)：是将两个查询（或表），以‘两两横同对接’的方式。

所得到的所有行，即表示表中的某行，跟另一个表中的某行。
进行‘横向对接’，得到一个新行。

  • union(联合查询)：指将2个或2个以上的字段数量相同的查询结构，‘纵向堆叠’后合并为一个结果。

union操作符用于合并两个或多个select语句的结果集
union操作符只能连接字段与字段，而不能连接字段与表或者表与表，哪怕字段以及字段名称、格式都一样也不行。因为union操作符前后是不能带有括号的，因为带有括号的话，sql就容易判定这是一个子查询表，连接会报错。
union all的效率更高，不用去判断是否存在重复值。

部门工资最高的员工

image.png

select  d.name Department,e.name Employee,salary Salary
from Employee e
join Department d
on e.departmentId = d.id
where (d.id,salary) in (
    select departmentId,max(salary)
    from Employee 
    group by departmentId
)

聚合函数中的max和min只会返回1行，不会将所有的最大值都返回回来。
这道官方题解给出的是，直接找到每个部门最大薪资作为子查询，然后再在总表中找到某个部门与该薪资对应的人，就可以返回所有最高薪资的员工。

select  Department.name Department,r.name Employee,salary Salary
from (
    select 
    name,
    salary,
    departmentId,
    dense_rank() over (partition by departmentId order by salary desc) 'rank'
from Employee e
)r
join Department
on r.departmentId = Department.id and r.rank=1

也可以用dense_rank()窗口函数实现，最高薪资，就是薪水排名第一，只要将所有薪水排名第一的雇员输出即可。窗口函数是将每一行的数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据，这是一个新生成的字段，不能用于筛选，需要作为表返回后，对表进行筛选。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/603579.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

内存卡罢工，数据危机？别急，有救！

内存卡罢工，数据危机？别急，有救！

在日常生活和工作中&#xff0c;我们越来越依赖于各种电子设备来存储重要数据。其中&#xff0c;内存卡因其便携性和大容量而广受欢迎。然而&#xff0c;当内存卡突然损坏打不开时&#xff0c;我们该如何应对&#xff1f;本文将为您详细解析这一问题&#xff0c;并提供有效的解…
阅读更多...
JAVA版本的ATM编程问题记录

JAVA版本的ATM编程问题记录

前段时间用C语言写了个银行ATM系统&#xff0c;还写了一篇文章记录了一些&#xff0c;C语言的ATM文章。后来又用IDEA写了一个JAVA版本的银行ATM。有人就会问为啥浪费这个时间写ATM呢&#xff1f;&#x1f9d0;其实是我本科代码没学好&#xff0c;所以现在想利用比较熟悉的ATM系…
阅读更多...
探秘编程之旅：Baidu Comate 智能代码助手的魔法揭秘

探秘编程之旅：Baidu Comate 智能代码助手的魔法揭秘

目录 Baidu Comate智能代码助手1.场景需求2.安装步骤3.功能介绍3.1 /指令3.2 插件3.3 #知识 4.使用体验5.总结 Baidu Comate智能代码助手 智能编程助手的意义在于提升编程体验和效率&#xff0c;使开发人员能够更轻松、更快速地完成编码任务&#xff0c;是如今人工智能技术的一…
阅读更多...
Flink DataSink介绍

Flink DataSink介绍

介绍 Flink DataSink是Apache Flink框架中的一个重要组件&#xff0c;它定义了数据流经过一系列处理后最终的输出位置。以下是关于Flink DataSink的详细介绍&#xff1a; 概念&#xff1a;DataSink主要负责对经过Flink处理后的流进行一系列操作&#xff0c;并将计算后的数据结…
阅读更多...
Linux学习笔记1

Linux学习笔记1

1.背景认知 可能很多人还没有接触Linux&#xff0c;会有点畏惧&#xff0c;我们可以把Linux类比成Windows&#xff0c; 下面是Windows和Linux的启动对比 Windows&#xff1a;上电后一开始屏幕是黑黑的---bios在启动Windows----Windows之后找到c盘启动各种应用程序 Linux&am…
阅读更多...
OFDM802.11a的FPGA实现（十）导频插入（含verilog和matlab代码）

OFDM802.11a的FPGA实现（十）导频插入（含verilog和matlab代码）

原文链接&#xff08;相关文章合集&#xff09;&#xff1a;OFDM 802.11a的xilinx FPGA实现 目录 1.前言2.插入导频原理3.硬件实现4.Matlab仿真5.ModelSim仿真6.结果对比验证7.verilog代码 1.前言 前面一篇文章完成了星座图的映射&#xff0c;今天继续设计后面的模块。在接收机…
阅读更多...
【Keil程序大小】Keil编译结果Code-RO-RW-ZI分析

【Keil程序大小】Keil编译结果Code-RO-RW-ZI分析

【Keil程序大小】Keil编译结果Code-RO-RW-ZI分析 下图为keil编译后的结果&#xff1a; 单位为Byte。Code是程序大小。RO是常量大小。RW是读写变量占用大小&#xff0c;如已初始化的静态变量和全局变量。ZI是全零变量占用大小&#xff0c;如未初始化的static修饰的静态变量、全局…
阅读更多...
聊聊BitLocker

聊聊BitLocker

最近有消息称微软决定在Windows 11 24H2中默认开启BitLocker&#xff0c;这个消息在网上引起了不小的波澜。有人说&#xff0c;对于我们这些普通用户来说&#xff0c;BitLocker真的有必要吗&#xff1f; 什么是BitLocker BitLocker 是一项 Windows 安全功能&#xff0c;可为整…
阅读更多...
如何使用多协议视频汇聚/视频安防系统EasyCVR搭建智慧园区视频管理平台？

如何使用多协议视频汇聚/视频安防系统EasyCVR搭建智慧园区视频管理平台？

智慧园区作为现代化城市发展的重要组成部分&#xff0c;不仅承载着产业升级的使命&#xff0c;更是智慧城市建设的重要体现。随着产业园区竞争的逐渐白热化&#xff0c;将项目打造成完善的智慧园区是越来越多用户关注的内容。 然而我们往往在规划前期就开始面临众多难题&#…
阅读更多...
如何制作有趣的gif？这个方法别错过

如何制作有趣的gif？这个方法别错过

是否在社交媒体上看到过很多有趣好玩的gif动图&#xff0c;有的搞笑有趣有的又很可爱。大家有没有想过自己动手制作gif动画呢&#xff1f;接下来&#xff0c;就给大家分享一招gif在线制作&#xff08;https://www.gif5.net/&#xff09;的方法&#xff0c;超简单不需要下载任何…
阅读更多...
什么牌子的洗地机质量最好？四款耐用高分产品推荐

什么牌子的洗地机质量最好？四款耐用高分产品推荐

洗地机具备了吸尘、擦拭、除菌等多种功能&#xff0c;可以一次完成多种清洁任务&#xff0c;帮助用户更高效地保持家居整洁&#xff0c;节省时间和精力&#xff0c;备受人们的喜爱。但是怎么挑选到优质的洗地机一直是大家关注的问题。今天&#xff0c;笔者将结合自己在家电行业…
阅读更多...
什么是驱动数字签名？如何获取驱动数字签名？

什么是驱动数字签名？如何获取驱动数字签名？

Windows 驱动程序承载着计算机实现的各种内核和用户模式功能。如果驱动程序被黑客攻击&#xff0c;可能会产生很多问题。Windows通过数字签名来验证驱动程序包的完整性及发布者的身份。2020年10月的安全更新中&#xff0c;微软加强了对驱动软件的验证&#xff0c;如果Windows无…
阅读更多...
【微积分听课笔记】全微分，二元极值，Double Integral

【微积分听课笔记】全微分，二元极值，Double Integral

6.6 二元函数的极值_哔哩哔哩_bilibili 此笔记为听课笔记&#xff0c;宋浩老师微积分~ 最近诸事缠身&#xff0c;会有种会不会只做一件事好些。实际上&#xff0c;关键在于动力&#xff0c;我不可能每次都准备充分。动力&#xff0c;分配&#xff0c;这是目前进入大学我正在学…
阅读更多...
【yolov8 项目打包】pyinstaller 打包pyQt5 界面为exe

【yolov8 项目打包】pyinstaller 打包pyQt5 界面为exe

创建一篇博客文章&#xff0c;介绍如何使用PyInstaller将PyQt5界面打包为exe文件&#xff0c;并且处理与YOLOv8模型相关的文件&#xff0c;可以按照以下结构进行&#xff1a; 标题&#xff1a;使用PyInstaller将PyQt5界面与YOLOv8模型打包为Windows可执行文件 引言 在机器学习…
阅读更多...
vue视图不刷新强制更新数据this.$forceUpdate()

vue视图不刷新强制更新数据this.$forceUpdate()

在vue中&#xff0c;更新视图数据&#xff0c;不刷新页面&#xff0c;需要强制更新数据才可以 前言 在对数据就行添加和删除时&#xff0c;发现页面视图不更新&#xff0c;排除发现需要强制更新才可以 点击添加或删除&#xff0c;新增数据和删除就行&#xff0c;但在不使用fo…
阅读更多...
如何vscode中刷力扣

如何vscode中刷力扣

推荐你阅读 互联网大厂万字专题总结 Redis总结 JUC总结 操作系统总结 JVM总结 Mysql总结 微服务总结 互联网大厂常考知识点 什么是系统调用 CPU底层锁指令有哪些 AQS与ReentrantLock原理 旁路策略缓存一致性 Java通配符看这一篇就够 Java自限定泛型 技术分享 如何vscode中刷力扣…
阅读更多...
视频号小店想要长久发展，做店的核心是什么？一篇详解！

视频号小店想要长久发展，做店的核心是什么？一篇详解！

大家好&#xff0c;我是电商小V 想要做好视频号小店&#xff0c;那么他的核心是什么呢&#xff1f; 视频号小店的核心还是商品&#xff0c;其实电商运营底层的逻辑都是一样的&#xff0c;都是以商品为核心去运营的&#xff0c;再说的浮夸一点就是&#xff0c;你的商品选择的好&…
阅读更多...
【实战】采用jenkins pipeline实现自动构建并部署至k8s

【实战】采用jenkins pipeline实现自动构建并部署至k8s

文章目录 前言部署jenkins编写docker-compose-jenkins.yaml配置maven源启动jenkins解锁jenkins Jenkins默认插件及git、镜像仓库、k8s凭证配置host key verification configuration修改为不验证Gitee ssh阿里云镜像仓库ssh编写pipeline安装以下常用插件将kubectl命令文件拷贝到…
阅读更多...
System Verilog通过CORDIC算法迭代16次求sin和cos值

System Verilog通过CORDIC算法迭代16次求sin和cos值

求5~85度的sin和cos值 其它角度和Verilog实现代码类似&#xff0c;查表、移位和加法器 define DIE 16 //迭代次数 define PIE 3.1415926 define MUL 100_000_000 //同比放大 initial begin int die; int x[17]; int y[17]; int z[17…
阅读更多...
学习软考----数据库系统工程师25

学习软考----数据库系统工程师25

关系规范化 1NF&#xff08;第一范式&#xff09; 2NF&#xff08;第二范式&#xff09; 3NF&#xff08;第三范式&#xff09; BCNF&#xff08;巴克斯范式&#xff09; 4NF&#xff08;第四范式&#xff09; 总结
阅读更多...
最新文章

Copyright @ 2022~2023