SQL快速上手(知识点总结+训练资料)
文章目录
- 一 SQL训练资料
- 二 SQL知识点总结
- 1.SQL语句的执行顺序
- 2.窗口函数
- 3.字符串处理函数
- 模糊查询
- 三 SQL题目的总结
一 SQL训练资料
牛客SQL题目
猴子数据分析题目
关注的公众号
猴子数据分析
二 SQL知识点总结
1.SQL语句的执行顺序
每一个子句产生的中间结果供接下来的子句使用(阶段性)
开始->FROM子句->WHERE子句->GROUP BY子句->HAVING子句->SELECT子句->ORDER BY子句->LIMIT子句->最终结果
select e.number,count(e.name) as num
from employees e --(先从表格拿数据)
where e.number>100--(然后筛选出number大于100的数据)
group by e.gender--(group是在where筛选出来的数据之后进行操作)
having num>100--(having对group by 产生的数据进行筛选)
order by num desc --(对group筛选出的数据进行排序)
limit 0,1;--(limit对最后)
2.窗口函数
将聚合的数据放到原数据的后方
参考资料:
窗口函数总结
窗口函数
查询每个部门的当前的最高薪水情况
薪水可以聚类max,但是员工号不可以,所以得用窗口函数
--部门、员工号、薪水
select a.dept_no,a.emp_no,a.salary
from
(select d.dept_no,d.emp_no,s.salary,rank() over(partition by d.dept_noorder by s.salary desc) as rkfrom dept_emp d inner join salaries son d.emp_no=s.emp_nowhere d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;
排序函数
总共有3种,形式为
rank() over(
partition by
order by desc
) as
| 序号函数名 | 组内排序后例子(1,2,3) |
|---|---|
| rank | 1,1,3 |
| row_number | 1,2,3 |
| dense_rank | 1,1,2 |
取值函数
①要查询的每一个数据,根据当前数据的位置确定
| 之前 | 之后 |
|---|---|
| lag | lead |
lag/lead() over()的使用lag(col,n,default):用于统计窗口往上第n行值:第一个参数为列名;第二个参数为往上第n行(默认为1);第三个参数为默认值(当往上第n行为null的时候,取默认值,如果不指定,则取null)。同理:lead(col,n,default):用于统计窗口往下第n行值:
例:找到车辆上一次的锁车记录
那么首先根据锁车的时间排序(降序),然后在这次锁车的时间的前一个
select fence,bike_id,unlock_time, -- 开锁lock_time, -- 锁车lag(lock_time,1,null) over(partition by fence,hour(unlock_time),bike_id) as last_lock_time
from bike_hour_inc
②根据在over()窗口中的位置确定
| 第一个 | 最后一个 | 第n个 |
|---|---|---|
| first_value | last_value | nth_value |
以下语句统计了不同产品最低销售额、最高销售额以及第三高销售额所在的月份:
不同产品最高销售额所在月份
不同产品(分组),最高销售额(排序),所在月份(第一行)
SELECT product AS "产品", ym AS "年月",amount AS "销售额",FIRST_VALUE(m.ym) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最高销售额月份",LAST_VALUE(m.ym) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "最低销售额月份",NTH_VALUE(m.ym,3) OVER (PARTITION BY m.product ORDER BY m.amount DESCROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "第三高销售额月份"FROM sales_monthly mORDER BY product, ym;
3.字符串处理函数
字符串拼接,截取函数
将A的前两个字符与B的前3个字符拼接起来组成D,最后全部变为大写
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
upper()
substring(A,1,2):对A从第一个字母开始,选择2个字母
contract(A,B):将A,B连接起来
读取日期字符串的年份、月份
year()
month()
模糊查询
占位符
| % | _ |
|---|---|
| 任意(0~∞)个字符 | 任意1个字符 |
查找包含toy的名字
select name from A where name like "%toy%"
查找以toy作为第二个字符的名字
select name from A where name like "_toy%"
查找以toy作为结尾、或者开头的名字
select name from A where (name like "%toy") | (name like "toy%")
三 SQL题目的总结
①205所有员工当前的manager
题目拆解,当前(where时间上进行筛选),所有员工(左外连接)
---所有员工(左外连接),当前(筛选to_date='9999-01-01')
select d.emp_no,m.emp_no as manager
from dept_emp d inner join dept_manager m
on d.dept_no=m.dept_no
where d.emp_no != m.emp_no and d.to_date='9999-01-01' and m.to_date='9999-01-01';
②206题获取每个部门当前员工最大薪水信息
需要用到group by (分组函数)
错误实例
select d.dept_no,d.emp_no,max(s.salary) as maxSalary
from dept_emp d inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by d.dept_no
order by d.dept_no asc;--这里,对于分组得到的每一列数据都需要进行聚合,
--首先按部门分组,那么部门号是聚合了的
--然后最大薪水是对组内的薪水进行了聚合
--而员工号不能进行聚合,所以结果错误
正确实例
select a.dept_no,a.emp_no,a.salary
from
(select d.dept_no,d.emp_no,s.salary,rank() over(partition by d.dept_noorder by s.salary desc) as rkfrom dept_emp d inner join salaries son d.emp_no=s.emp_nowhere d.to_date='9999-01-01' and s.to_date='9999-01-01'
) a
where rk=1
order by a.dept_no;
③211获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
薪水第二多的员工可能有多个,所以先找出第二的薪水的值。
先查一遍唯一值薪水,然后找出薪水为此值的员工
select emp_no,salary
from salaries
where
salary=
(select distinct salaryfrom salarieswhere to_date='9999-01-01'order by salary desclimit 1,1
)
④212获取当前薪水第二多的员工的emp_no以及其对应的薪水salary
不能用order by 语句
找出当前薪水第二多的薪水,先找出最大的,然后排除最大的再找一次
select e.emp_no,s.salary,e.last_name,e.first_name
from employees e ,salaries s
where e.emp_no=s.emp_no
and s.salary=(select max(salary)from salaries where to_date='9999-01-01'and salary !=(select max(salary)from salaries where to_date='9999-01-01'))and s.to_date ='9999-01-01';
215查找在职员工自入职以来的薪水涨幅情况
在职员工薪水涨幅情况,涉及两个极端情况
分别查极端,然后内连接构建新表,最后计算得出结果
入职的薪水:入职日期等于雇佣日期的薪水
现在的薪水:雇佣日期为现在
SELECT s1.emp_no AS "emp_no", s2.salary - s1.salary AS "growth"
FROM (SELECT salaries.emp_no, salaryFROM salaries inner join employeeson salaries.emp_no=employees.emp_nowhere salaries.from_date=employees.hire_date
) s1
INNER JOIN (SELECT emp_no, salaryFROM salariesWHERE to_date = '9999-01-01'
) s2 ON s1.emp_no = s2.emp_no -- 因为INNER JOIN只会连接匹配行,所以s2中筛除的已离职员工则不会被显示
ORDER BY growth ASC
;
216统计各个部门的工资记录数
首先
对于对各个部门的工资记录数进行聚类运算,注意对于所有工资进行统计,没有部门的排除(左外连接),对部门号进行分组统计。
然后
对于部门名称进行表连接
select a.dept_no,a.dept_name,b.sum
from departments a
inner join
( select d.dept_no,count(*) as sumfrom dept_emp d right join salaries son d.emp_no =s.emp_nowhere d.dept_no is not nullgroup by d.dept_no
) b
on a.dept_no=b.dept_no
order by a.dept_no asc;
217对所有员工的薪水按照salary降序进行1-N的排名
利用dense_rank() over ()窗口函数
首先得到每一行的排名,然后最后输出数据时要进行降序排序
SELECT emp_no,salary,dense_rank () over (
ORDER BY salary DESC) AS `rank`
FROMsalaries
WHERE to_date = '9999-01-01' ;
相关文章:
SQL快速上手(知识点总结+训练资料)
文章目录一 SQL训练资料二 SQL知识点总结1.SQL语句的执行顺序2.窗口函数3.字符串处理函数模糊查询三 SQL题目的总结一 SQL训练资料 牛客SQL题目 猴子数据分析题目 关注的公众号 猴子数据分析 二 SQL知识点总结 1.SQL语句的执行顺序 每一个子句产生的中间结果供接下来的子句…...
无需经验的steam搬砖,每天操作1小时,轻松创业赚钱!
我作为一个95后社畜,就喜欢倒腾各种赚钱的事情,8年老韭菜告诉你,副业创收一点都不难,难就难在是否找对项目,俗话说方向不对,努力白费! 什么做苦力、技能、直播卖货,电商等等对比我这…...
如何创建你的公司的FAQ页面?
很多企业考虑为公司搭建一个“常见问题”页面,作为帮助客户回答关于产品和服务的常见问题的一种方式。 FAQ页面和登录/销售页面不同,没有展现出直接的投资回报,但是为团队节省了其他成本,据了解,高达67%的客户相比于跟…...
CK-GW06-E03与欧姆龙PLC配置指南
CK-GW06-E03与欧姆龙PLC配置指南CK-GW06-E03是一款支持标准工业EtherCAT协议的网关控制器,方便用户集成到PLC等控制系统中。本控制器提供了网络 POE 供电和直流电源供电两种方式,确保用户在使用无POE供电功能的交换机时可采用外接电源供电;系统还集成了六…...
使用docker-compose部署RocketMQ5.0
简介:使用docker-compose部署rocketmq5.0。文中会介绍docker-compose版本以及需要注意的项第一步:进入hub.docker.com搜索rocketmq我们选择第一个,因为第一个是7个月前更新的,(我看有很多博客使用的依旧是最下面的那种…...
嵌入式ARM设计编程(四) ARM启动过程控制
文章和代码已归档至【Github仓库:hardware-tutorial】,需要的朋友们自取。或者公众号【AIShareLab】回复 嵌入式 也可获取。 一、实验目的 (1) 掌握建立基本完整的ARM 工程,包含启动代码,C语言程序等&…...
企业维基都说好,今天我们来看看 wiki 软件的缺点有哪些?
企业维基企业wiki和内部知识库可能看起来是一回事——但它们实际上是非常不同的软件类型。也许您可能不知道你在寻找的是知识基础软件,还是wiki软件。 无论哪种方式,缺乏知识都是生产力的巨大瓶颈。事实上,未能分享知识是财富500强企业每年亏…...
08- 汽车产品聚类分析综合项目 (机器学习聚类算法) (项目八)
找出性价比较高的车 LabelEncoder: python:sklearn标签编码(LabelEncoder) sklearn.preprocessing.LabelEncoder的使用:在训练模型之前,通常都要对数据进行一定得处理。将类别编号是一种常用的处理方法,比如把类别“电脑”,“手机…...
揭开苹果供应链,如何将其命运与中国深度捆绑
前 言 诺基亚在2007年时拥有9亿用户,在手机市场上占据主导地位,福布斯在当时以“谁能赶上手机之王?”为标题刊登了一篇关于该公司的报道,与此同时,苹果公司推出了iPhone系列产品。16年后,苹果公司以充足的…...
Mybatis 之useGeneratedKeys注意点
一.例子 Order.javapublic class Order {private Long id;private String serial; }orderMapper.xml<?xml version"1.0" encoding"UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org/DTD Mapper 3.0" "http://mybatis.org/dtd…...
数据结构---时间复杂度
专栏:数据结构 个人主页:HaiFan. 专栏简介:开学数据结构,接下来会慢慢坑新数据结构的内容!!!! 时间复杂度前言1.算法效率1.1如何衡量一个算法的好坏1.2算法的复杂度2.时间复杂度2.1大…...
如何保证集合是线程安全的 ConcurrentHashMap如何实现高效地线程安全?
第10讲 | 如何保证集合是线程安全的? ConcurrentHashMap如何实现高效地线程安全? 我在之前两讲介绍了 Java 集合框架的典型容器类,它们绝大部分都不是线程安全的,仅有的线程安全实现,比如 Vector、Stack,在性能方面也…...
C++对象模型和this指针
成员变量和成员函数分开存储:基本概念:在C中,类内的成员变量和成员函数分开存储只有非静态成员变量才属于类的对象上每个空对象都会有一个独一无二的内存地址,所以,空对象占用内存空间的大小为1代码实现:#i…...
kubernetes教程 --Pod调度
Pod调度 在默认情况下,一个Pod在哪个Node节点上运行,是由Scheduler组件采用相应的算法计算出来的,这个过程是不受人工控制的。但是在实际使用中,这并不满足的需求,因为很多情况下,我们想控制某些Pod到达某…...
功率放大器科普知识(晶体管功率放大器的注意事项)
虽然功率放大器是电子实验室的常用仪器,但是很多人对于它却没有清晰的认识,下面就让安泰电子来为大家介绍功率放大器的科普内容以及使用注意事项,希望大家可以对功率放大器有清晰的认识。功率放大器可以把输入信号的功率放大,以满…...
CentOS 7转化系统为阿里龙蜥Anolis OS 7
转载:原社区CentOS 7迁移Anolis OS 7迁移手册 一、注意事项 Anolis OS 7生态上和依赖管理上保持跟CentOS7.x兼容,一键式迁移脚本centos2anolis.py,实现CentOS7.x到Anolis OS 7的平滑迁移。 使用迁移脚本前需要注意如下事项: 迁…...
【快速复习】一文看懂 Mysql 核心存储 隔离级别 锁 MVCC 机制
一文看懂 Mysql 核心存储 & 隔离级别 & 锁 & MVCC 机制 Mysql InnoDB 引擎下核心存储 数据&索引存储 IBD 文件 mysql 实际存储采用 B 树结构。 B 树是一种多路搜索树,其搜索性能高于 B 树 所有叶节点在同一深度,保证搜索效率仅叶节…...
面试题----集合
概述 从上图可以看出,在Java 中除了以 Map 结尾的类之外, 其他类都实现了 Collection 接⼝。 并且,以 Map 结尾的类都实现了 Map 接⼝List,Set,Map List (对付顺序的好帮⼿): 存储的元素是有序的、可重复的。 Set (注重独⼀⽆⼆…...
XSS注入基础入门篇
XSS注入基础入门篇1.XSS基础概念2. XSS的分类以及示例2.1 反射型XSS2.1.1 示例1:dvwa low 级别的反射型XSS2.1.2 攻击流程2.2 DOM型XSS2.2.1 示例2:DOM型XSS注入1.环境部署2.基础版本3.进阶绕过2.3 存储型XSS2.3.1 示例1:dvwa low示例2.3.2 攻…...
刷题 - 数据结构(二)链表
1. 链表 1.1 题目:合并两个有序链表 链表的建立与插入:关键在于留出头部,创建迭代指针。 ListNode* head new ListNode; // 通过new 创建了一个数据类型为ListNode的数据 并把该数据的地址赋值给ListNodeListNode* p 0; // 再创建一个数据…...
SciencePlots——绘制论文中的图片
文章目录 安装一、风格二、1 资源 安装 # 安装最新版 pip install githttps://github.com/garrettj403/SciencePlots.git# 安装稳定版 pip install SciencePlots一、风格 简单好用的深度学习论文绘图专用工具包–Science Plot 二、 1 资源 论文绘图神器来了:一行…...
Day131 | 灵神 | 回溯算法 | 子集型 子集
Day131 | 灵神 | 回溯算法 | 子集型 子集 78.子集 78. 子集 - 力扣(LeetCode) 思路: 笔者写过很多次这道题了,不想写题解了,大家看灵神讲解吧 回溯算法套路①子集型回溯【基础算法精讲 14】_哔哩哔哩_bilibili 完…...
【解密LSTM、GRU如何解决传统RNN梯度消失问题】
解密LSTM与GRU:如何让RNN变得更聪明? 在深度学习的世界里,循环神经网络(RNN)以其卓越的序列数据处理能力广泛应用于自然语言处理、时间序列预测等领域。然而,传统RNN存在的一个严重问题——梯度消失&#…...
Leetcode 3577. Count the Number of Computer Unlocking Permutations
Leetcode 3577. Count the Number of Computer Unlocking Permutations 1. 解题思路2. 代码实现 题目链接:3577. Count the Number of Computer Unlocking Permutations 1. 解题思路 这一题其实就是一个脑筋急转弯,要想要能够将所有的电脑解锁&#x…...
STM32标准库-DMA直接存储器存取
文章目录 一、DMA1.1简介1.2存储器映像1.3DMA框图1.4DMA基本结构1.5DMA请求1.6数据宽度与对齐1.7数据转运DMA1.8ADC扫描模式DMA 二、数据转运DMA2.1接线图2.2代码2.3相关API 一、DMA 1.1简介 DMA(Direct Memory Access)直接存储器存取 DMA可以提供外设…...
SpringBoot+uniapp 的 Champion 俱乐部微信小程序设计与实现,论文初版实现
摘要 本论文旨在设计并实现基于 SpringBoot 和 uniapp 的 Champion 俱乐部微信小程序,以满足俱乐部线上活动推广、会员管理、社交互动等需求。通过 SpringBoot 搭建后端服务,提供稳定高效的数据处理与业务逻辑支持;利用 uniapp 实现跨平台前…...
如何为服务器生成TLS证书
TLS(Transport Layer Security)证书是确保网络通信安全的重要手段,它通过加密技术保护传输的数据不被窃听和篡改。在服务器上配置TLS证书,可以使用户通过HTTPS协议安全地访问您的网站。本文将详细介绍如何在服务器上生成一个TLS证…...
Ascend NPU上适配Step-Audio模型
1 概述 1.1 简述 Step-Audio 是业界首个集语音理解与生成控制一体化的产品级开源实时语音对话系统,支持多语言对话(如 中文,英文,日语),语音情感(如 开心,悲伤)&#x…...
06 Deep learning神经网络编程基础 激活函数 --吴恩达
深度学习激活函数详解 一、核心作用 引入非线性:使神经网络可学习复杂模式控制输出范围:如Sigmoid将输出限制在(0,1)梯度传递:影响反向传播的稳定性二、常见类型及数学表达 Sigmoid σ ( x ) = 1 1 +...
七、数据库的完整性
七、数据库的完整性 主要内容 7.1 数据库的完整性概述 7.2 实体完整性 7.3 参照完整性 7.4 用户定义的完整性 7.5 触发器 7.6 SQL Server中数据库完整性的实现 7.7 小结 7.1 数据库的完整性概述 数据库完整性的含义 正确性 指数据的合法性 有效性 指数据是否属于所定…...
