1. Toy模板网首页
  2. > Toy博客

学会这7种SQL进阶用法，让你少走99%的弯路!

6月前 作者：码农Academy 分类：Toy博客 阅读(56) 违法举报

这篇具有很好参考价值的文章主要介绍了学会这7种SQL进阶用法，让你少走99%的弯路!。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

引言

在日常业务开发中，熟练掌握SQL语言是至关重要的。除了基础的增删改查操作外，了解和掌握一些进阶的SQL用法能够让你更高效地处理各种复杂的数据操作。本文将介绍几种SQL进阶用法，让你少走99%的弯路，提高数据处理效率。

自定义排序

在MySQL中，你可以通过使用自定义排序来指定特定字段的排序顺序。通常情况下，MySQL的排序是按照默认的升序（ASC）或降序（DESC）进行的。但是，有时候你可能需要按照特定的顺序进行排序，而不仅仅是升序或降序。这时就可以使用自定义排序。

下面我们来通过一个示例来讲解自定义排序。

假设你有一个students表，其中包含学生的姓名和成绩。现在，你想按照自定义的顺序对学生进行排序，而不是按照成绩的大小。例如，你想按照"A"、"B"、"C"、"D"、"E"这样的顺序进行排序。

在MySql8.0以前我们需要使用ORDER BY结合CASE语句来自定义一个排序规则去实现。例如：

SELECT * 
FROM students 
ORDER BY 
  CASE 
    WHEN grade = 'A' THEN 1 
    WHEN grade = 'B' THEN 2 
    WHEN grade = 'C' THEN 3 
    WHEN grade = 'D' THEN 4 
    WHEN grade = 'E' THEN 5 
    ELSE 6 -- 处理其他情况，例如如果有其他成绩值
  END;

执行结果如下：

而在MySql8.0之后可以直接使用自定义排序规则通过FIELD()函数来实现自定义排序。FIELD()函数接受一个字段和一个值列表作为参数，并返回该字段在值列表中的位置。上述示例我们可以改造为：

SELECT * 
FROM students 
ORDER BY  FIELD(grade,'A','B','C','D','E');

再例如我们在按照自定义的名称的规则排序：

SELECT *
FROM students 
ORDER BY  FIELD(name, 'Eve','David','Charlie','Bob','Alice');

执行结果：


需要注意的是，FIELD()函数会返回字段在值列表中的位置，如果字段的值不在值列表中，则返回0。所以如果你的字段可能包含不在值列表中的值，可能需要在排序时进行适当的处理。

这种方法相对简单，并且在某些情况下非常方便，但也有一些限制。特别是，当值列表很长时，这种方法可能不够灵活，因为需要在ORDER BY子句中硬编码所有的值。

空值NULL排序

在MySQL中，当涉及到NULL值排序时，通常，NULL值在升序排序（ASC）中被视为小于任何非NULL值，而在降序排序（DESC）中则被视为大于任何非NULL值。

SELECT * FROM students ORDER BY age ASC;

执行结果：

SELECT * FROM students ORDER BY age DESC;

执行结果：

如果你希望NULL值出现在排序结果的末尾或开头，可以使用COALESCE或IFNULL函数或者ORDER BY IF(ISNULL(字段), 0, 1)来替换NULL值，从而显式控制其排序位置：

  1. 升序时将NULL值放在最后面：
SELECT * FROM students ORDER BY COALESCE(age, 999999) ASC;

或者

SELECT * FROM students ORDER BY IFNULL(age, 999999) ASC;

或者

SELECT * FROM students ORDER BY IF(ISNULL(age), 1, 0), age ASC;

执行结果如下：

  1. 降序时把NULL值放在最前面：
SELECT * FROM students ORDER BY COALESCE(age, 999999) DESC;

或者

SELECT * FROM students ORDER BY IFNULL(age, 999999) DESC;

或者

SELECT * FROM students ORDER BY IF(ISNULL(age), 0, 1), age DESC;

执行结果如下：

假如对需要排序的列的最大最小的边界值可以确定，即逻辑上不会出现这么大的值的时候上述三种方式都可以实现，否则建议使用ORDER BY IF(ISNULL(age), 0, 1)的方式，当然也建议不管那种情况都要使用这种方式。

CASE表达式

CASE表达式是一种条件表达式，类似于其他编程语言中的switch语句。它允许根据条件的不同返回不同的值。

CASE表达式的基本语法如下：

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END
  • condition1、condition2等是条件，可以是任何逻辑表达式。
  • result1、result2等是对应条件为真时要返回的结果。
  • ELSE子句是可选的，用于处理所有条件都不满足的情况，如果省略了ELSE子句且所有条件都不满足，则返回NULL。
SELECT 
    name,
    score,
    CASE
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        ELSE 'D'
    END AS grade_level
FROM
    students;

执行结果如下：

CASE表达式在SQL查询中用途广泛，可用于SELECT、UPDATE的SET等语句中，实现动态计算列值、根据条件更新值等功能。

分组连接函数：GROUP_CONCAT

GROUP_CONCAT函数用于将每个组内的行连接成一个字符串，并返回该字符串。通常在对结果集进行分组后，需要将每个分组内的多个值合并成一个字符串时使用。该函数在MySQL中非常有用，特别是在处理与分组相关的数据时。

GROUP_CONCAT函数的语法：

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])
  • DISTINCT：可选项，用于去除重复的值。
  • expr：要连接的表达式。
  • ORDER BY：可选项，用于指定连接后的字符串的排序方式。
  • SEPARATOR：可选项，用于指定连接后的字符串的分隔符，默认为逗号,。
SELECT
	class,
	GROUP_CONCAT( NAME ) AS students_list 
FROM
	students 
GROUP BY
	class;

查询结果：

使用GROUP_CONCAT函数时，还可以通过ORDER BY子句可以指定连接后的字符串的排序方式。

SELECT
	class,
	GROUP_CONCAT( NAME ORDER BY age DESC SEPARATOR ';') AS students_list 
FROM
	students 
GROUP BY
	class;

执行结果：

默认情况下GROUP_CONCAT函数的最大连接长度是1024个字符。如果你的连接超过了这个限制，可以使用SET SESSION group_concat_max_len = val;语句来调整最大连接长度。

分组统计数据后在进行统计汇总：WITH ROLLUP

WITH ROLLUP是MySQL中用于执行聚合查询并生成总计行（rollup summary）的选项之一。它允许在GROUP BY子句中创建一个额外的行，该行包含了对分组数据的汇总信息。这在需要同时查看详细数据和总计数据时非常有用。

使用WITH ROLLUP时，查询结果会包含每个分组的详细数据，以及一个总计行，该总计行汇总了所有分组的数据。

SELECT
	class,
	AVG( age ) 
FROM
	students 
WHERE
	age IS NOT NULL 
GROUP BY
	class WITH ROLLUP;

查询结果：

结果中包含了每个年级的平均年龄，并且返回所有的班级的学生平均年龄。

需要注意的是，总计行中分组键列的值为NULL，表示汇总了所有分组的数据。这使得我们可以通过检查分组键列是否为NULL来确定行是否为总计行。

子查询提取：WITH AS

在MySQL中，WITH AS子句（也称为子查询提取函数）允许你在一个查询中创建一个临时的命名子查询（也称为公共表表达式），然后在该查询的后续部分引用这个子查询。这有助于提高查询的可读性和简洁性，尤其是当查询中需要多次引用相同的子查询时。

WITH AS子句的基本语法：

WITH cte_name AS (
    SELECT columns
    FROM table
    WHERE conditions
)
SELECT columns
FROM cte_name
WHERE conditions;

假如我们查询分数大于80的，并且评级为A的并且不是2班的学生信息：

WITH s1 AS ( SELECT * FROM students WHERE score > 80 ),
s2 AS ( SELECT * FROM students WHERE class = 2 ) 
SELECT
	* 
FROM
	s1 
WHERE
	s1.id NOT IN ( SELECT s2.id FROM s2 ) 
	AND s1.grade = 'A';

结果如下：

WITH AS是从MySql 8.0.1开始提供

优雅处理数据插入、更新时主键或者唯一键冲突

在日常开发中，我们尝尝在插入数据时会遇到唯一键冲突导致插入失败的问题。如下：

假如我们的唯一键为：

ALTER TABLE students ADD UNIQUE KEY `uk_class_name` (`name`,`class`) USING BTREE;

我们执行以下sql：

SELECT * FROM students WHERE class = 1 and name = 'Alice';

查询结果：

此时我们在插入一条数据：

INSERT INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

发现报错：

我们通常的做法时先按照唯一键查询一遍数据，如果存在则先删除数据，在进行插入。这种做法略显麻烦，并且可能会误删数据。此时我们就可以采取以下三种方法区优雅处理。

IGNORE

IGNORE关键字用于在执行插入、更新或删除操作时，忽略特定类型的错误，而不是中止整个操作。特别是在插入或更新数据时，如果存在唯一索引冲突或主键冲突，使用IGNORE关键字可以使得操作继续进行而不是因为错误而中断。

语法如下：

INSERT IGNORE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

UPDATE IGNORE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

上述插入语句的示例中，我们使用IGNORE关键字：

INSERT IGNORE INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

执行成功。

查询数据发现，数据没有变化：

如果UPDATE使用了IGNORE关键字，在更新数据时忽略那些会导致错误的行，比如更新更新后会违反唯一约束。

UPDATE IGNORE students SET name = 'Alice' WHERE id = 3;

此时会执行成功：

IGNORE在使用时应该谨慎，它可能会掩盖潜在的业务逻辑错误或数据一致性问题。并且在事务安全性和完整性要求较高的场景下可能并不适用。

REPLACE

REPLACE INTO是MySQL中用于向表中插入或替换数据的一种特殊语法。它类似于INSERT INTO语句，但是在插入数据时，如果发现表中已存在具有相同唯一索引或主键的记录，则会删除现有记录，然后插入新记录，而不是抛出错误或导致插入操作失败。即有则删除在插入，没有则插入。

语法如下：

REPLACE INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);

你也可以使用REPLACE INTO与SELECT语句结合使用来替换表中的数据：

REPLACE INTO table_name (column1, column2, ...)
SELECT column1, column2, ...
FROM another_table
WHERE condition;

我们执行如下sql：

REPLACE INTO students (name, grade, age, score, class) VALUES ('Alice', 'A', 18, 98, 1);

执行成功，查询这条记录：

此时我们发现记录的id重新生成了，并且数据页是插入的最新的数据。

REPLACE INTO会首先尝试删除表中具有相同唯一索引或主键的记录，然后再插入新记录。因此，在使用REPLACE INTO时应当谨慎，以确保不会意外删除需要保留的数据。

ON DUPLICATE KEY UPDATE

在MySQL中，ON DUPLICATE KEY UPDATE是用于在执行INSERT语句时，如果发生唯一键冲突（即违反了唯一索引或主键约束），则执行后面的更新操作。

当你想要向表中插入一行数据，但是如果这一行数据已经存在（即主键或唯一索引已经存在），则更新该行数据而不是插入新的行，即有则更新，没有则插入。

语法如下：

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE column1 = value1, column2 = value2, ...;

我们执行以下sql：

INSERT IGNORE INTO students ( NAME, grade, age, score, class )
VALUES
	( 'Alice', 'A', 18, 98, 1 ) 
	ON DUPLICATE KEY UPDATE score = 100,
	grade = 'B';

执行成功，重新看一下这条数据：

数据已执行了更新操作。

这种方式适用于需要维护唯一记录（如用户名、邮箱等）的情况下，无需预先检查是否存在重复，就能保证数据的一致性和完整性。同时，它也减少了对数据库的请求次数，提高了处理效率。

总结

掌握以上这几种SQL进阶用法，能够让你在日常的数据处理工作中更加得心应手，少走很多弯路。通过优化查询逻辑、处理异常情况以及执行插入或更新操作，你可以更高效地管理和操作数据库，提升工作效率，从而更好地应对各种复杂的数据处理需求。

本文已收录于我的个人博客：码农Academy的博客，专注分享Java技术干货，包括Java基础、Spring Boot、Spring Cloud、Mysql、Redis、Elasticsearch、中间件、架构设计、面试题、程序员攻略等文章来源地址https://www.toymoban.com/news/detail-837794.html

到了这里，关于学会这7种SQL进阶用法，让你少走99%的弯路!的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处： 如若内容造成侵权/违法违规/事实不符，请点击违法举报进行投诉反馈，一经查实，立即删除！

分享到：
领支付宝红包 赞助服务器费用

相关文章

  • 3分钟让你学会axios在vue项目中的基本用法（建议收藏）

    3分钟让你学会axios在vue项目中的基本用法（建议收藏）

    目录 Axios Axios简介 一、axios是干啥的 二、安装使用 三、Axios请求方式 1、axios可以请求的方法： 2、get请求 3、post请求 4、put和patch请求 5、delete请求 6、并发请求 四、Axios实例 1、创建axios实例 2、axios全局配置 3、axios实例配置 4、axios请求配置 五、拦截器 1、请求拦截器 2、响应

    2024年01月20日
    浏览(29)
  • 【Mysql】一篇学会SQL中的递归的用法

    【Mysql】一篇学会SQL中的递归的用法

             SQL递归查询是一种用于处理具有层次结构的数据的技术。它使用递归函数来遍历树形结构，例如组织结构、分类结构等等。         递归查询通常使用 \\\" WITH RECURSIVE \\\"  语句实现。         WITH RECURSIVE 语句包含两部分：             a.递归部分: 定义了如

    2024年02月13日
    浏览(82)
  • MyBatis进阶：掌握MyBatis动态SQL与模糊查询、结果映射，让你在面试中脱颖而出！！

    MyBatis进阶：掌握MyBatis动态SQL与模糊查询、结果映射，让你在面试中脱颖而出！！

    目录 一、引言 二、MyBatis动态SQL 2.1.if元素使用 2.2.foreach元素使用 三、MyBatis模糊查询 ①使用#{字段名} ②使用${字段名} ③使用concat{\\\'%\\\',#{字段名},\\\'%\\\'} 总结 四、MyBatis结果映射 4.1.案例演示 4.1.1.resultType进行结果映射 4.1.2.resultMap进行结果映射 在当今的软件开发环境中，数据库的使

    2024年02月11日
    浏览(37)
  • 干货分享：AI绘图学习心得-Midjourney绘画AI，让你的AI绘画之路少走弯路

    干货分享：AI绘图学习心得-Midjourney绘画AI，让你的AI绘画之路少走弯路

    本篇没有什么长篇大论，全部都是实用心得总结。接下来，我们将分享关于Midjourney绘画AI的实用心得总结，包括构图指令结构、常用指令、操作技巧、常用风格词汇和构图词汇。 如果你想入门MidJourney可以查看这篇教程：Midjourney最全操作指南，从入门到精通 AI绘画的核心就是

    2024年02月11日
    浏览(38)
  • [C++历练之路]C++模板还能这么玩，已经走了好多弯路，后悔没有早点学会到。

    [C++历练之路]C++模板还能这么玩，已经走了好多弯路，后悔没有早点学会到。

    W...Y的主页 😊  代码仓库分享💕    🍔前言：  在C++的世界里，模板是一种强大而神奇的工具，宛如编程的瑰宝匣，蕴藏着无限的可能性。它们不仅能够让我们编写通用的代码，还能够在编译时实现类型安全的抽象。然而，模板的奥秘并非易见，它们像是编码世界中的魔法

    2024年02月04日
    浏览(29)

  • 功能测试进阶自动化测试如何摸清学习方向，少走弯路呢？

    目录 抛开疑问，只做学术探讨 小白在想什么？ 盖楼之前先打好地基，首先需要学习一门语言 语言入门后，正式踏上开始自动化成神之路，入门篇Selenium 玩腻了Selenium 开始接触自动化框架unittest/testNG 不满足于单元测试框架的功能 学习自动化框架 初始接口测试 尝试学习Requ

    2024年02月13日
    浏览(53)
  • 让你立刻学会指针

    让你立刻学会指针

    ☃️个人主页：fighting小泽 🌸作者简介：目前正在学习C语言和数据结构 🌼博客专栏：C语言学习 🏵️欢迎关注：评论👊🏻点赞👍🏻留言💪🏻 指针是什么？ 指针理解的2个要点： 指针是内存中一个最小单元的编号，也就是地址 平时口语中说的指针，通常指的是指针变量

    2023年04月21日
    浏览(43)
  • ssh详解–让你彻底学会ssh

    ssh详解–让你彻底学会ssh

    SSH全称secure shell，安全外壳协议（安全的shell），是一个计算机网络协议（默认端口号为22）。通过ssh协议可以在客户端 安全 （提供身份认证、信息加密）的 远程连接 LInux服务器或其他设备。 使用广泛的Xshell软件就是基于SSH协议远程连接。 SSH远程连接之后能干什么？ SSH远程

    2024年02月05日
    浏览(39)
  • 学会 CompletableFuture：让你的代码免受阻塞之苦！

    学会 CompletableFuture：让你的代码免受阻塞之苦！

    来源：https://juejin.cn/post/6844904024332828685 通过阅读本篇文章你将了解到： CompletableFuture的使用 CompletableFure异步和同步的性能测试 已经有了Future为什么仍需要在JDK1.8中引入CompletableFuture CompletableFuture的应用场景 对CompletableFuture的使用优化 查询所有商店某个商品的价格并返回，并

    2024年02月08日
    浏览(32)
  • 视频内容转文字怎么转，让你学会3个简单方法

    视频内容转文字怎么转，让你学会3个简单方法

    随着视频内容的普及，视频中包含的文字信息也变得越来越重要。为了更好地获取视频中的文字信息，将视频内容转换成文字成为一种必要的操作。今天和大家分享视频内容转文字怎么转，手把手教你3个方法，快学起来吧。 方法一：使用工具转换 “视频转文字助手”是一款

    2024年02月07日
    浏览(78)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包，优惠每天领

二维码1

领取红包

二维码2

领红包