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; // 再创建一个数据…...
用于隔离PWM的光耦合器选择和使用
光耦合器(或光隔离器)是一种将电路电隔离的器件,不仅在隔离方面非常出色,而且允许您连接到具有不同接地层或在不同电压电平下工作的电路。光耦合器具有“故障安全”功能,因为如果受到高于最大额定值的电压,…...
面试完阿里,字节,腾讯的测试岗,复盘以及面试总结
前段时间由于某些原因辞职了,最近一直在面试。面试这段时间,经历过不同业务类型的公司(电商、酒店出行、金融、新能源、银行),也遇到了很多不同类型的面试官。 参加完三家大厂的面试聊聊我对面试的一些看法࿰…...
分享一个外贸客户案例
春节期间一个外贸人收到了客户的回复,但因为自己的处理方式造成了一个又一个问题,我们可以从中学到一些技巧和知识。“上次意大利的客人询价后,一直没回复(中间有打过电话,对方说口语不行,我写过邮件跟进过…...
【Kubernetes】第二篇 - 购买阿里云 ECS 实例
一,前言 上一篇,简单介绍了 CI/CD 的概念以及 ECS 服务规划,搭建整套服务需要三台服务器,配置如下: ECS 配置启动服务说明2核4GJenkins Nexus Dockerci-server2核4GDocker Kubernetesk8s-master1核1GDocker Kube…...
数影周报:据传国内45亿条快递数据泄露,聆心智能完成Pre-A轮融资
本周看点:据传国内45亿条快递数据泄露;消息称微软解雇150 名云服务销售;消息称TikTok计划在欧洲再开两个数据中心;衣服长时间放购物车被淘宝客服嘲讽;聆心智能完成Pre-A轮融资......数据安全那些事据传国内45亿条快递数…...
Leetcode力扣秋招刷题路-0073
从0开始的秋招刷题路,记录下所刷每道题的题解,帮助自己回顾总结 73. 矩阵置零 给定一个 m x n 的矩阵,如果一个元素为 0 ,则将其所在行和列的所有元素都设为 0 。请使用 原地 算法。 示例 1: 输入:mat…...
遥感数字图像处理
遥感数字图像处理 来源:慕课北京师范大学朱文泉老师的课程 遥感应用:遥感制图、信息提取 短期内了解知识结构–>有选择的剖析经典算法原理–>系统化知识结构、并尝试实践应用 跳出算法(尤其是数学公式) 关注原理及解决问…...
深度学习常用的python函数(一)
由于我只简单的学过python和pytorch,其中有很多函数的操作都还是一知半解的,其中有些函数经常见到,所以就打算记录下来。 1.zip zip(*a):针对单个可迭代对象压缩成n个元组,元组数量n等于min(a中元素的最小长度) a [(1, 2), (3…...
2023年美国大学生数学建模A题:受干旱影响的植物群落建模详解+模型代码(一)
目录 前言 一、题目理解 背景 解析: 要求 二、建模 1.相关性分析 2.相关特征权重 只希望各位以后遇到建模比赛可以艾特认识一下我,我可以提供免费的思路和部分源码,以后的数模比赛只要我还有时间肯定会第一时间写出免费开源思路&…...
PPS文件如何转换成PPT?附两种方法
在工作中,PPS文件的使用还是很广泛的,因为作为幻灯片放映文件,点击后就能直接播放,十分方便。但如果想要修改PPS里的内容,PPS是无法编辑的,我们需要把文件转换成PPT,再进行修改。 那PPS文件如何…...
网站视频怎么做的好处/各平台推广费用
过去有很多网页设计师喜欢将他们的网页效果图用table布局实现成网页,但是这样做会遇到一个比较麻烦的问题就是,后期调试和维护会相当的困难。现在,越来越多的前端开发er们开始使用xHTML CSS替代最初的table布局完成网页的整体布局,…...
做调查问卷赚钱的网站/网络营销产品策略
亲测可用,若有疑问请私信 错误信息: The nginx plugin is not working; there may be problems with your existing configuration. The error was: NoInstallationError() Certbot默认使用nginx的路径,需求如下配置 ln -s /usr/local/ngi…...
代码演示插件wordpress/清远市发布
most from reference命名规则 1.使用驼峰式命名规则,尽量避免在命名中使用下划线 2.类型以大写字母开头 3.方法和属性以小写字母开头 4.使用4个空格缩进 5.public的方法加上注释说明,以便他们能出现在Kotlin Doc中 冒号 分隔子类和父类的冒号前面应该加上…...
小程序怎么做网站/营销网址
现在阶段的学习完全到了自学,那么整理文献也必须有自己的一套思路和办法。不像硕士阶段导师给论文,而是自我指导读论文,必须有一种类似版本控制和库的快速调用的机制。而Endnote越来越必须了。 首先遇到的问题是导入的格式问题,这…...
wordpress+纯净主题/十大营销案例分析
点击上方蓝色字体,选择“设为星标”回复”资源“获取更多资源大数据技术与架构点击右侧关注,大数据开发领域最强公众号!大数据真好玩点击右侧关注,大数据真好玩!数据仓库的同步方法我们的数据仓库长久以来一直使用天级…...
php企业门户网站模板/好的seo公司营销网
慕虎7371278谢邀,现在少用的 Java,所以这些东西不熟了。处理办法有两类,一类是你自己把类型转换成 String,这样生成的 JSON 肯定是带引号的,但是这样需要修改模型或者添加适配模型另一类方法是从 jackson 的文档中去找…...