MySQL | 查询数据和插入、更新、删除数据中一些容易遗忘的点

天下弈星~

已于 2023-04-05 17:25:06 修改

阅读量72

点赞数

分类专栏： MySQL8从入门到精通文章标签： mysql 数据库 sql 大数据 big data

于 2023-04-05 17:22:03 首次发布

本文链接：https://blog.csdn.net/m0_70452407/article/details/129972790

版权

MySQL8从入门到精通专栏收录该内容

14 篇文章 3 订阅

订阅专栏

一、GROUP_CONCAT()函数

二、在GROUP BY 子句中使用WITH ROLLUP

一、GROUP_CONCAT()函数

在MySQL中，可以在GROUP BY子句中使用GROUP_CONCAT()函数，将每个分组中各个字段的值显示出来。

二、在GROUP BY 子句中使用WITH ROLLUP

使用WITH ROLLUP关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算出巡出的所有记录的综合，即统计记录数量。

当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

三、带ANY、SOME关键字的子查询

ANY、SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。

SELECT num1 FROM tb11 WHERE num1 > ANY (SELECT num2 FROM tb12);

只要大于num2列的任意一个数即为符合条件的结果。

四、带ALL关键字的查询

ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。

五、带EXISTS关键字的子查询

EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果是true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。

例：查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录，SQL语句如下：

SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id=107);

EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

六、使用正则表达式查询

正则表达式常用字符匹配列表
选项	说明	例子	匹配值示例
^	匹配文本的开始字符	'^b'匹配以字母b开头的字符串	book,big,banana,bike
$	匹配文本的结束字符	'st$'匹配以st结尾的字符串	test,resist,persist
.	匹配任何单个字符	'b.t'匹配任何b和t之间有一个字符的字符串	bit,bat,but,bite
*	匹配零个或多个在它前面的字符	'f*n'匹配字符n前面有任意个字符f的字符串	fn,fan,faan,fabcn
+	匹配前面的字符1次或多次	'ba+'匹配以b开头后面紧跟至少有一个a的字符串	ba,bay,bare,battle
<字符串>	匹配包含指定的字符串的文本	'fa'匹配包含fa的字符串	fan,afa,faad
[字符集]	匹配字符集合中的任何一个字符	'[xz]'匹配包含x或者z的字符串	dizzy,zebra,x-ray,extra
[^]	匹配不在括号内的任何字符	'[^abc]'匹配任何不包含a、b或c的字符串	desk,fox,f8ke
字符串{n,}	匹配前面的字符串至少出现n次	b{2}匹配2个或更多的b	bbb,bbbb,bbbbbbb
字符串{n,m}	匹配前面的字符串至少n次，至多m次。如果n为0，此参数为可选参数。	b{2,4}匹配含最少2个、最多4个b的字符串	bb,bbb,bbbb

例：在fruits表中，查询f_name字段以字母'y'结尾的记录，SQL语句如下：

SELECT * FROM fruits WHERE f_name REGEXP 'y$';

记得不要忘了“REGEXP”。

七、通用表表达式

通用表表达式简称CTE。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。

示例：

WITH cte as (
SELECT * FROM goods)

CTE是一个可以重复使用的结果集。相比于子查询，CTE的效率会更高，因为非递归的CTE只会查询一次并且可以重复使用。

还有一种特殊的CTE，就是递归CTE，其子查询会引用自身。WITH子句必须以WITH RECURSIVE开头。

CTE递归子查询包括两部分：seed查询和recursive查询，中间由union[all]或union distinct分隔。seed查询会被执行一次，以创建初始数据子集。recursive查询会被重新执行以返回数据子集，直到获得完整的结果集。当迭代不会生成任何新行时，递归就会停止。

示例：

WITH RECURSIVE cte(n) AS (
SELECT 1
UNION ALL
SELECT n+1 FROM cte WHERE n<8)
SELECT * FROM cte;

结果如下：

n
1
2
3
4
5
6
7
8

八、为表增加计算列

计算列就是某一列的值是通过别的列计算得来的。

在MySQL 8.0中，CREAE TABLE和ALTER TABLE中都支持增加计算列。下面以CREAE TABLE为例进行讲解。

CREATE TABEL tb1(
id int(9) NOT NULL AUTO_INCREMENT,
a int(9) DEFAULT NULL,
b int(9) DEFAULT NULL,
c int(9) GENERATED ALWAYS AS ((a+b)) VIRTUAL,
PRIMARY KEY ('id')
);