几个SQL的高级写法

news/2024/10/31 5:27:17/

一、ORDER BY FLELD() 自定义排序逻辑

MySql 中的排序 ORDER BY 除了可以用 ASC DESC,还可以通过 ORDER BY FIELD(str,str1,...) 自定义字符串/数字来实现排序。这里用 order_diy 表举例,结构以及表数据展示:

 ORDER BY FIELD(str,str1,...) 自定义排序sql如下:

SELECT * from order_diy ORDER BY FIELD(title,'九阴真经', 
'降龙十八掌','九阴白骨爪','双手互博','桃花岛主',
'全真内功心法','蛤蟆功','销魂掌','灵白山少主');

查询结果如下:

 如上,我们设置自定义排序字段为 title 字段,然后将我们自定义的排序结果跟在 title 后面。

二、EXISTS用法

在日常开发中,应该都对关键词 exists 用的比较少,估计使用 in 查询偏多。这里给大家介绍一下 exists 用法,引用官网文档:

 可知 exists 后面是跟着一个子查询语句,它的作用是根据主查询的数据,每一行都放到子查询中做条件验证,根据验证结果(TRUE 或者 FALSE),TRUE的话该行数据就会保留,下面用 emp 表和 dept 表进行举例,表结构以及数据展示:

 既入我们现在想找到 emp 表中 dept_name 与 dept表 中 dept_name 对应不上员工数据,sql 如下:

SELECT * from emp e where exists (
SELECT * from dept p where e.dept_id = p.dept_id 
and e.dept_name != p.dept_name
)

查询结果:

 我们通过 exists 语法将外层 emp 表全部数据 放到子查询中与一一与 dept 表全部数据进行比较,只要有一行记录返回true。画个图展示主查询所有记录与子查询交互如下:

  • 第一条记录与子查询比较时,全部返回 false,所以第一行不展示。
  • 第二行记录与子查询比较时,发现 销售部门 与 dept 表第二行 销售部 对应不上,返回 true,所以主查询该行记录会返回。
  • 第二行以后记录执行结果同第一条。

三、自连接查询

自连接查询是 sql 语法里常用的一种写法,掌握了自连接的用法我们可以在 sql 层面轻松解决很多问题。这里用 tree 表举例,结构以及表数据展示:

 tree 表中通过 pid 字段与 id 字段进行父子关联,假如现在有一个需求,我们想按照父子层级将 tree 表数据转换成 一级职位 二级职位 三级职位 三个列名进行展示,sql 如下:

SELECT t1.job_name '一级职位', t2.job_name '二级职位', t3.job_name '三级职位' 
from tree t1 join tree t2 on t1.id = t2.pid left join tree t3 on t2.id = t3.pid 
where t1.pid = 0;

结果如下:

我们通过 tree t1 join tree t2 on t1.id = t2.pid 自连接展示 一级职位 二级职位,再用 left join tree t3 on t2.id = t3.pid 自连接展示 二级职位 三级职位,最后通过where 条件 t1.pid = 0过滤掉非一级职位的展示,完成这个需求。

四、更新emp表和dept表关联数据

 这里继续使用上文提到的 emp 表和 dept 表,数据如下:

 可以看到上述 emp 表中 jack这个人 的部门名称与 dept 表实际不符合,现在我们想将 jack 的部门名称更新成 dept 表的正确数据,sql 如下:

update emp, dept set emp.dept_name = dept.dept_name
where emp.dept_id = dept.dept_id;

查询结果:

 我们可以直接关联 emp 表和 dept 表并设置关联条件,然后更新 emp 表的 dept_name 为 dept 表的 dept_name。

五、ORDER BY 空值 NULL排序

ORDER BY 字句中可以跟我们要排序的字段名称,但是当字段中存在 null 值时,会对我们的排序结果造成影响。我们可以通过 ORDER BY IF(ISNULL(title), 1, 0) 语法将 null 值转换成0或1,来达到将 null 值放到前面还是后面进行排序的效果。这里继续用 order_diy 表举例,sql 如下:

SELECT * FROM order_diy ORDER BY  IF(ISNULL(title), 0, 1), money;

六、with rollup 分组统计数据的基础上,再进行统计汇总

MySql 中可以使用 with rollup 在分组统计数据的基础上再进行统计汇总,即用来得到 group by 的汇总信息。这里继续用order_diy 表举例,sql 如下:

SELECT name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

 可以看到通过 GROUP BY name WITH ROLLUP 语句,查询结果最后一列显示了分组统计的汇总结果。但是 name 字段最后显示为 null,我们可以通过 coalesce(val1, val2, ...) 函数,这个函数会返回参数列表中的第一个非空参数。

SELECT coalesce(name, '总金额') name, SUM(money) as money 
FROM order_diy GROUP BY name WITH ROLLUP;

 六、with as 提取临时表别名

with as 语法需要 MySql 8.0以上版本,它有一个别名叫做 CTE,官方对它的说明如下

公用表表达式 (CTE) 是一个命名的临时结果集,它存在于单个语句的范围内,稍后可以在该语句中引用,可以多次引用。

的作用主要是提取子查询,方便后续共用,更多情况下会用在数据分析的场景上。

如果一整句查询中多个子查询都需要使用同一个子查询的结果,那么就可以用 with as,将共用的子查询提取出来,加个别名。后面查询语句可以直接用,对于大量复杂的SQL语句起到了很好的优化作用。这里继续用 order_diy 表举例,这里使用 with as 给出 sql 如下:

-- 使用 with as
with t1 as (SELECT * from order_diy where money > 30),
t2 as (SELECT * from order_diy where money > 60)
SELECT * from t1 
where t1.id not in (SELECT id from  t2) and t1.name = '周伯通';

 这个 sql 查询了 order_diy 表中 money 大于30且小于等于60之间并且 name 是周伯通的记录。可以看到使用 CTE 语法后,sql写起来会简洁很多。

七、存在就更新、不存在就插入

MySql 中通过on duplicate key update语法来实现存在就更新,不存在就插入的逻辑。插入或者更新时,它会根据表中主键索引或者唯一索引进行判断,如果主键索引或者唯一索引有冲突,就会执行on duplicate key update后面的赋值语句。 这里通过 news 表举例,表结构和说数据展示,其中 news_code 字段有唯一索引:

 添加sql:

-- 第一次执行添加语句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新闻3', '小花', 'wx-0003') 
on duplicate key update news_title = '新闻3';
-- 第二次执行修改语句
INSERT INTO `news` (`news_title`, `news_auth`, `news_code`) 
VALUES ('新闻4', '小花', 'wx-0003') 
on duplicate key update news_title = '新闻4';


http://www.ppmy.cn/news/483429.html

相关文章

关于微信小程序生成海报一个简单的办法

废话不多说,直接入题,先上GITHUB地址,这个组件很好用,有图形生成工具,你不用再自己写代码一个个元素对齐了,是不是很爽。 GITHUB:https://github.com/Kujiale-Mobile/Painter 生成painter代码…

TC8:SOMEIPSRV_OPTIONS_12-15

SOMEIPSRV_OPTIONS_12: Reserved field of the IPv4 Multicast Option 目的 IPv4 Multicast Option的Reserved字段应静态设置为0x00 这里指的是第二个Reserved字段 测试步骤 DUT CONFIGURE:启动具有下列信息的服务Service ID:SERVICE-ID-1Instance数量:1Tester:客户端-1发…

Jconsole 开启远程连接遇到的一些坑

最近在学习 JVM,其中涉及到性能、内存等指标分析需要使用工具分享,Java 提供了几个可视化工具来监控和管理 Java 应用,比如 Jconsole、JVisual、JMC,他们以图形化的界面实时的监控程序各种性能指标以及内存、CPU 的使用情况。 Jco…

Prometheus配置通过file_sd_configs中每个目标的module标签信息重置每个目标的metrics_path

配置方式如下: scrape_configs: - job_name: file_sd file_sd_configs:- files: - targets.jsonrelabel_configs:- source_labels: [__address__]regex: (http://)([^:])target_label: __address__ replacement: http://${2}- source_labels: [__port__] regex: (\d)target_la…

Davinci安装失败

Davinci安装失败 在安装Davinci的时候,遇到报错Failed to install DaVinci Resolve Panels. Continues with others components? 更新最新显卡驱动没有解决问题,后来想到可能是我把TMP,TEMP文件夹放到ramdisk,导致空间不够。在把…

基于FPGA的AD/DA实验

掌握并行DAC、ADC的接口时序 DDS信号的产生 参考《基于FPGA的DDS实现》用DDS合成信号,经过DAC输出用ADC采集信号 高速AD/DA转化器 AD9762是无符号的DAC器件 有符号补码需要先把高位取反再送给DAC* AD9200是无符号的ADC 最大电压对应MAX值 0电压对应0值 注意ADC芯…

电子设计大赛-AD与DA电路设计

全套资料免费下载: 关注v-x-公-众-号:【嵌入式基地】 后-台-回-复:【电赛】 即可获资料 回复【编程】即可获取 包括有:C、C、C#、JAVA、Python、JavaScript、PHP、数据库、微信小程序、人工智能、嵌入式、Linux、Unix、QT、物联网…

Davinci BI报表工具~

前文: 外部数据用SparingCloud做数据接口,对内部当然时用BI工具平台提供查询,释放开发,让运营想怎么查就怎么查,缩短开发流程。 一、安装 1.1 环境 1.1.1 JDK1.8 略 1.1.2 phantomjs #解压bzip2工具 yum -y insta…