7种SQL进阶用法【转】

1.自定义排序(ORDER BY FIELD)

在MySQL中ORDER BY排序除了可以用ASC和DESC之外,还可以使使用自定义排序方式来实现

CREATE TABLE movies (
id INT PRIMARY KEY AUTO_INCREMENT,
movie_name VARCHAR(255),
actors VARCHAR(255),
price DECIMAL(10,2) DEFAULT 50,
release date DATE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO movies(movie_name,actors,price,release_date) VALUES 
('咱们结婚吧','新东',43.2,'2013-04-12'),
('四大名捕','刘亦菲',62.5,'2013-12-21'),
('猎场','新东',68.5,'2017-11-03'),
('芳华','范冰冰',55.0,'2017-09-15'),
('功夫瑜伽','成龙',91.8,'2017-01-28'),
('惊天解密','新东',96.9,'2019-08-13'),
('铜雀台',null,65,'2025-12-16'),
('天下无贼','刘亦菲',44.9,'2004-12-16'),
('建国大业','范冰冰',70.5,'2009-09-21'),
('赛尔号4:疯狂机器城','范冰冰',58.9,'2021-07-30'),
('花木兰','刘亦菲',89.0,'2020-09-11'),
('警察故事','成龙',68.0,'1985-12-14'),
('神话','成龙'.86.5.'2005-12-22');

select * from movies order by movie_name asc;

select * from movies order by FIELD(movie_name,'神话','猎场','芳华','花木兰','铜雀台','警察故事','天下无贼','四大名捕','惊天解密','建国大业','功夫瑜伽','咱们结婚吧','赛尔号4','疯狂机器城');

 

2.空值NULL排序(ORDERBY IF(ISNULL))

在MySQL中使用ORDERBY关键字加上我们需要排序的字段名称就可以完成该字段的排序。如果字段中存在NULL值就会对我们的排序结果造成影响,
这时候我们可以使用ORDERBYIF(ISNULL(字段),0,1)语法将NULL值转换成0或1,实现NUL值数据排序到数据集前面还是后面。

select * from movies order by actors,price asc;

select * from movies order by if(ISNULL(actors,0,1)),actors,price asc;

select * from movies order by if(ISNULL(actors,2,1)),actors,price asc;

3.CASE表达式(CASE...WHEN)

在实际开发中我们经常会写很多if...elseif...else,这时候我们可可以使用CASE...WHEN表达式解决这个问题。
以学生成绩举例。比如说:学生90分以上评为优秀,分数80-90平为良好,分数60-80评为一般,分数低于60评为"较差"。那么我们可以使用下面这种查询方式:

CREATE TABLE student (
student_id varchar(10) NOT NULL COMMENT'学号",
sname varchar(20) DEFAULT NULL COMMENT"姓名",
sex char(2) DEFAULT NULL COMMENT '性别',
age int(11) DEFAULT NULL COMMENT '年龄",
score float DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';


INSERT INTO student(student_id,snane,sex,age,score)
VALUES  ('001','张三','男',20,95),
        ('002','李四','女',22,88),
        ('003','王五','男',21,90),
        ('004','赵六','女',20,74),
        ('005','陈七','女',19,92),
        ('006','杨八','男',23,78),
        ('007','周九','女',20,55),
        ('008','吴十','男',22,91),
        ('009','刘一','女',21,87),
        ('010','孙二','男',19,60);
select *,case when score > 90 then '优秀' 
        when score > 80 then '良好'  
        when score > 60 then '一般' 
        else'较差' end level 
from student;

4.分组连接函数(GROUP CONCAT)

分组连接函数可以在分组后指定字段的字符串连接方式,并且还可以指定排序逻辑;连接字符串默认为英文逗号。
比如说根据演员进行分组,并将相应的电影名称按照票价进行降序排列,而且电影名称之间通过""拼接。用法如下:

select actors,
GROUP_CONCAT(movie_name),
GROUP_CONCAT(price) from movies GROUP BY actors;

select actors,
GROUP_CONCAT(movie_name order by price desc SEPARATOR '_'),
GROUP_CONCAT(price      order by price desc SEPARATOR '_'),
from movies GROUP BY actors;

5.分组统计数据后再进行统计汇总(with rollup)

在MySql中可以使用with rollup在分组统计数据的基础上再进行数据统汁汇总,即将分组后的数据进行汇总。

SELECT actors,SUM(price) FROM movies GROUP BY actors;

SELECT actors,SUM(price) FROM movies GROUP BY actors WITH ROLLUP;

6.子查询提取(with as)

如果一整句查询中多个子查询都需要使用同一个子查询的结果,那么就可以用with as将共用的子查询提取出来并取一个别名。后面查询语句可以直接用,对于大量复杂的
SQL语句起到了很好的优化作用。
需求:获取演员刘亦菲票价大于50且小于65的数据。

with ml as (select * from movies where price > 509),
     m2 as (select * from movies where price >= 65) 
select * from m1 where m1.id not in (select m2.id from m2) and m1.actors='刘亦菲';

7.优雅处理数据插入、更新时主键、唯一键重复

在MySql中插入、更新数据有时会遇到主键重复的场景,通常的做法就是先进行删除在插入达到可重复执行的效果,但是这种方法有时候会错误删除数据
1.插入数据时我们可以使用IGNORE,它的作用是插入的值遇到主键或者唯一键重复时自动忽略重复的数据,不影响后面数据的插入,即有则忽略,无则插入。示例如下:

select * from movies where id >= 13;

INSERT INTO movies (id, movie_name, actors, pricce, release_date) VALUES
(13,"神话","成龙",100,"2005-12-22");

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(13,"神话","成龙",100,'2005-12-22');

INSERT IGNORE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14,"神话2',"成龙',114,'2005-12-22');

2.还可以使用REPLACE关键字,当插入的记录遇到主键或者唯一键重复时先删除表中重复的记录行再插入,即有则删除+插入,无无则插入,示例如下:

REPLACE INTO movies (id, movie_name, actors, price, release_date) VALUES
(14,'神话2','成龙',100,'2005-12-22');

REPLACE INTO movies (id, movie_name, actors, pricce, release_date) VALUES
(15,'神话3','成龙',115,'2005-12-22');

3.更新数据时使用on duplicate key update,它的作用就是当插入的记录遇到主键或者唯一键重复时,会执行后面定义的UPDATE操作。相当于先执行Insert操作,再根
据主键或者唯一键执行update操作,即有就更新,没有就插入。示例如下:

INSERT INTO movies (id,movie_name,actors,price,release_date) VALUES 
(15,'神话3','成龙',115,'2005-12-22') on duplicate key update price = price + 10;

INSERT INTO movies (id,movie_name,actors,price,release_date) VALUES 
(16,'神话4','成龙',75,'2005-12-22') on duplicate key update price = price + 10;

详见：学会这7种SQL进阶用法，让你少走99%的弯路!_哔哩哔哩_bilibili

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

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

相关文章

文本三剑客（grep，awk，sed）

文本三剑客（grep，awk，sed）

一.正则表达式 注意事项：使用正则表达式必须加引号。 元字符 表示字符 ① . ：在正则表达式中.表示任意单个字符。 [rootpc1 data]#grep -o r.t /etc/passwd #过滤passwd文件中开头为r中间任意单个字符结尾为t的内容 rat rat rat [rootpc1 data]#g…
阅读更多...
Open Feign 源码解析(二) --- 如何发送http请求

Open Feign 源码解析(二) --- 如何发送http请求

Open Feign 源码解析二 如何发送http请求？ 如何组件化？ 定义接口 public interface Client {Response execute(Request request, Options options) throws IOException; }是否存在已有的方案？ 1）rest template http client o…
阅读更多...
《微信小程序开发从入门到实战》学习三十三

《微信小程序开发从入门到实战》学习三十三

第四章 云开发 本章云开发技术的功能与使用，包括以下几点： 1.学习使用云开发控制台 2.学习云开发JSON数据库功能 3.学习云开文件存储功能 4.学习云函数功能 5.使用云开发技术实现投票小程序的服务端功能 投票小程序大部分已经实现。需要实现&#…
阅读更多...
【算法萌新闯力扣】：合并两个有序链表

【算法萌新闯力扣】：合并两个有序链表

力扣题目：合并两个有序链表 开篇 今天是备战蓝桥杯的第24天及算法村开营第2天。根据算法村的讲义，来刷链表的相关题目。今天要分享的是合并两个有序链表。 题目链接: 21.合并两个有序链表 题目描述 代码思路 通过创建一个新链表，然后遍历…
阅读更多...
振南技术干货集：znFAT 硬刚日本的 FATFS 历险记（7）

振南技术干货集：znFAT 硬刚日本的 FATFS 历险记（7）

注解目录 1、znFAT 的起源 1.1 源于论坛 （那是一个论坛文化兴盛的年代。网友 DIY SDMP3 播放器激起了我的兴趣。） 1.2 硬盘 MP3 推了我一把 （“坤哥”的硬盘 MP3 播放器，让我深陷 FAT 文件系统不能自拔。） 1.3 我…
阅读更多...
Android Bitmap 模糊效果实现 (二)

Android Bitmap 模糊效果实现 (二)

文章目录 Android Bitmap 模糊效果实现 (二)使用 Vukan 模糊使用 RenderEffect 模糊使用 GLSL 模糊RS、Vukan、RenderEffect、GLSL 效率对比 Android Bitmap 模糊效果实现 (二) 本文首发地址 https://blog.csdn.net/CSqingchen/article/details/134656140 最新更新地址 https:/…
阅读更多...
将用户的session改为分布式共享session

将用户的session改为分布式共享session

将用户的session改为分布式session 分布式session理解 使用分布式session的原因： 后台服务器是分布式的（比如要负载均衡），在A服务器请求的的信息（如用户登录信息）存在A的session中，B服务器并不…
阅读更多...
C++之哈希

C++之哈希

unordered系列容器的效率之所以比较高(尤其是查找),是因为它底层使用了哈希结构,即哈希表. 哈希概念 前言: 顺序结构以及平衡树中, 元素关键码与其存储位置之间没有对应的关系, 因此在查找一个元素 时, 必须要经过关键码的多次比较. 顺序查找时间复杂度为O(N), 平衡树中为树的…
阅读更多...
香港科技大学广州｜智能制造学域博士招生宣讲会—天津大学专场

香港科技大学广州｜智能制造学域博士招生宣讲会—天津大学专场

时间：2023年12月07日（星期四）15:30 地点：天津大学卫津路校区26楼B112 报名链接：https://www.wjx.top/vm/mmukLPC.aspx# 宣讲嘉宾： 汤凯教授 学域主任 https://facultyprofiles.hkust-gz.edu.cn/faculty-p…
阅读更多...
解决：AttributeError: module ‘os’ has no attribute ‘mknod’

解决：AttributeError: module ‘os’ has no attribute ‘mknod’

解决：AttributeError: module ‘os’ has no attribute ‘mknod’ 文章目录 解决：AttributeError: module os has no attribute mknod背景报错问题报错翻译报错位置代码报错原因解决方法今天的分享就到此结束了 背景 在使用之前的代码时，报错…
阅读更多...
借助arthas 性能调优全过程

借助arthas 性能调优全过程

使用 arthas 的trace 命令分析方法耗时瓶颈： 可以看出 bindReloadZoneTimeLimite 耗时最久， 通过分析Bind 底层，将业务粒度进行拆分，加入并发执行 再次使用arthas 追踪单个方法耗时时间： 核心耗时方法&#xff0c…
阅读更多...
使用Postman如何在接口测试前将请求的参数进行自定义处理

使用Postman如何在接口测试前将请求的参数进行自定义处理

1、前言 当我们使用 Postman 进行接口测试时，对于简单的不需要处理的接口，直接请求即可，但是对于需要处理的接口，如需要转码、替换值等，则就麻烦一些，一般我们都是先手动把修改好的值拷贝到请求里再进行请…
阅读更多...
使用Arthas排查性能问题

使用Arthas排查性能问题

Arthas 是一款线上监控诊断产品，通过全局视角实时查看应用 load、内存、gc、线程的状态信息，并能在不修改应用代码的情况下，对业务问题进行诊断，包括查看方法调用的出入参、异常，监测方法执行耗时，类加载信…
阅读更多...
unity学习笔记10

unity学习笔记10

一、生命周期函数 1.Awake() 调用时间：对象被激活或创建时。 用途：通常用于初始化对象的状态，获取组件引用或执行其他在脚本生命周期早期需要完成的任务。 2.OnEnable(): 调用时间：对象激活时，包括对象被创建和Se…
阅读更多...
每天五分钟计算机视觉：经典架构的力量与启示

每天五分钟计算机视觉：经典架构的力量与启示

在深度学习和计算机视觉领域，卷积神经网络（Convolutional Neural Networks，简称CNN）无疑是最为经典的架构之一。近年来，随着研究的不断深入和新架构的不断涌现，许多初学者可能会忽视这些经典架构的重要性。然而，理解并学习这些经典架构，对于我们深入理解卷积神经网络的…
阅读更多...
操作系统 选择题 期末试题 考研真题 + 参考答案

操作系统 选择题 期末试题 考研真题 + 参考答案

1.（考研真题，单项选择题）单道批处理系统的主要缺点是（ ）。 A. CPU利用率不高 B.失去了交互性 C.不具备并行性 D.以上都不是 【参考答案】A 【解析】单道批处理系统的内存中只有一道程序，当该程序…
阅读更多...
苍穹外卖项目笔记（5）——Redis

苍穹外卖项目笔记（5）——Redis

1 入门 1.1 Redis 简介 Redis 是一个基于内存的 key-value 结构数据库，官网链接（中文）：https://www.redis.net.cn 特点： 基于内存存储，读写性能高适合存储热点数据（热点商品、资讯、新闻&am…
阅读更多...
vue3(二)-基础入门之列表循环、数组变动检测、filter模糊查询、事件修饰符

vue3(二)-基础入门之列表循环、数组变动检测、filter模糊查询、事件修饰符

一、列表循环 of 和 in 都是一样的效果 html代码&#xff1a; <div id"app"><ul><li v-for"item of datalist">{{ item }}</li></ul><ul><li v-for"item in dataobj">{{ item }}</li></u…
阅读更多...
3D点云目标检测:CT3D解读(未完)

3D点云目标检测:CT3D解读(未完)

CT3D 一、RPN for 3D Proposal Generation二、Proposal-to-point Encoding Module2.1、Proposal-to-point Embedding2.2、Self-attention Encoding 三、Channel-wise Decoding Module3.1、Standard Decoding3.2、Channel-wise Re-weighting3.3、Channel-wise Decoding Module 四…
阅读更多...
数据库之索引的底层数据逻辑及应用

数据库之索引的底层数据逻辑及应用

索引&#xff08;index&#xff09;是帮助数据库高效获取数据的数据结构。 索引的数据结构 堆存储 使用二叉树存储 极端情况下的单链形式 大数据量下&#xff0c;层级越深&#xff0c;查询效率越低。 平衡二叉树 多路平衡查找树 B树的结构 所有的数据都存储在叶结点中…
阅读更多...
最新文章

Copyright @ 2022~2023