MySQL各种套路统计/坑/小知识点

置顶 tsuyokuninaru

已于 2023-04-03 21:00:49 修改

阅读量757

点赞数 1

分类专栏： mysql 文章标签： mysql 数据库 java

于 2018-12-17 14:25:58 首次发布

本文链接：https://blog.youkuaiyun.com/qq_30923243/article/details/85002269

版权

mysql 专栏收录该内容

12 篇文章

订阅专栏

本文深入解析SQL中的字段处理技巧，包括substring_index灵活分隔、CONCAT字符串拼接、INSERT与SUBSTRING的字符串操作、BETWEEN AND的边界选择、COUNT函数的使用、日期间天数计算、幂计算与进制转换等。同时探讨了WHERE条件的正则匹配与NULL值处理，以及数据结果的区间GROUP BY、UNION/UNION ALL合并、行转列、汉字首字母排序等高级处理方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

字段处理

1、substring_index（字符串，分隔标识符，分隔符位置）

select substring_index(substring_index('I#love#money', '#', 2), '#',-1); -- love
select substring_index('I#love#money', '#', 1);  -- I       第一个#号左边全部
select substring_index('I#love#money', '#', 2);  -- I#love  第二个#号左边全部
select substring_index('I#love#money', '#', -1); -- money  倒数第一个井号右边全部
select substring_index('I#love#money', '#', -2); -- love#money  倒数第二个井号右边

2、CONCAT字符串拼接

select CONCAT('I',' love',' money');  -- I love money

3、INSERT（字符串，起始位置，长度，替换内容）字符串替换，可用作脱敏

select INSERT('123456789',4,4,'****'); # 123****89
select INSERT('123456789',2,2,'**');   # 1**456789

4、SUBSTRING（字符串，起始位置，长度）字符串截取

select SUBSTRING('123456789',1,4); #1234
select SUBSTRING('123456789',2,3); #234

5、BETWEEN AND

函数包括左右边界，相当于>= 、<=。

索引btree和hash类型区别
a.btree树索引列是顺序组织存储的，所以很适合查找范围数据
b.hash索引不适合范围查找，但是一旦可以应用此索引，速度会大幅提升
c. innodb引擎有一个特殊的功能叫做自适应哈希索引，当innodb注意到某些索引值被使用的非常频繁时，
它会在内存中基于btree索引之上再创建一个哈希索引，这样就让btree索引也具有哈希索引的一些优点，
比如：快速的哈希查找，这是一个全自动的，内部的行为，用户无法控制或者配置，不过如果有必要，
可以选择关闭这个功能（innodb_adaptive_hash_index=OFF，默认为ON）。
大佬地址：https://www.cnblogs.com/xiaoboluo768/p/5164342.html

6、COUNT函数

SELECT COUNT(1) FROM t WHERE id<0; # 0
# count函数与group buy 组合使用，没有记录输出为null
SELECT COUNT(1) FROM t WHERE id<0 GROUP BY id; # NULL

7、计算两个日期之间的天数

# DAY 两个日期之间时间戳的差值/86400 ，其它的自己实践下哈
# SECOND/MINUTE/HOUR/DAY/MONTH/YEAR 以DAY为例
SELECT TIMESTAMPDIFF(DAY ,'2018-08-23','2018-08-31');# 8
SELECT TIMESTAMPDIFF(DAY ,'2018-08-23','2018-08-32');# NULL

8、幂计算/进制转换

select POW(2,8);             # 256
select conv(11111111, 2,10); # 255
select conv(255, 10,2);      # 11111111

WHERE条件

1、正则匹配

# . 匹配字符串中的任意一个字符，包括回车和换行
# * 匹配多个该符号之前的字符，包含0和1个
# + 匹配多个该字符之前的字符，包括1个
SELECT * FROM table WHERE column REGEXP '^a[bcd]e{2,3}f.z$';

2.、NOT IN对NULL值的处理

SELECT * FROM `learn`; #[id,bro] => [1=>a,2=>b, 3=>null]
SELECT * FROM `learn` WHERE bro NOT IN ('a'); # [2=>b]
# 如果想要[2=>b,3=>null]
select * FROM learn WHERE bro NOT IN ('a') OR bro is NULL;
## ps: unique约束对null值无效，null值还会降低索引效率，所以无特殊情况，字段应设置为not null

数据结果处理

1、指定区间group by

表 test

id	money
1	1
2	3
3	5
4	7
5	9
6	10

SELECT
	elt(
		INTERVAL (money, 0, 2, 5, 10),
		'less2',
		'2to5',
		'5to10',
		'more10'
	) AS yb_level,
	SUM(money) as total_money,
	count(1) as num
FROM
	test
where id<=6
GROUP BY                       #按指定区间分组
	yb_level
ORDER BY FIELD(yb_level,'less2', #按指定顺序排序
		'2to5',
		'5to10',
		'more10'); 
输出：
yb_level | total_money | num
less2	1	1
2to5	3	1
5to10	21	3
more10	10	1  --此分组方式左闭右开

2、UNION / UNION ALL 数据合并时与单独子查询的字段名无关，与字段位置有关

# 对应下面第一个图
SELECT id as aid,money FROM test WHERE id<3
UNION ALL
SELECT id,money FROM test WHERE id<5;

# 对应下面第二个图
SELECT id as aid,money FROM test WHERE id<3
UNION ALL
SELECT money,id FROM test WHERE id<5;

在这里插入图片描述

3、行转列

大佬地址： https://www.cnblogs.com/ooo0/p/9085224.html

SELECT * FROM	property;
####结果如下####
id  name course score
1	张三	 数学	3
2	张三	 语文	4
3	张三	 英语	5
4	李四	 数学	6
5	李四	 语文	7
6	李四	 英语	8
7	王五	 数学	9
8	王五	 语文	10
9	王五	 英语	11

行转列sql

SELECT name,course,
MAX(CASE course WHEN '数学' THEN score ELSE 0 END) as '数学',
MAX(CASE course WHEN '语文' THEN score ELSE 0 END) as '语文',
MAX(CASE course WHEN '英语' THEN score ELSE 0 END) as '英语'
FROM `property` GROUP BY name;
####结果如下####
name course 数学 语文 英语
张三	 数学	3	 4	 5
李四	 数学	6	 7	 8
王五	 数学	9	 10	 11

中间sql，便于理解：
SELECT name,course,
CASE course WHEN '数学' THEN score ELSE 0 END as '数学',
CASE course WHEN '语文' THEN score ELSE 0 END as '语文',
CASE course WHEN '英语' THEN score ELSE 0 END as '英语'
FROM `property`;
####结果如下####
name course 数学 语文 英语
张三  数学	3	0	0
张三	 语文	0	4	0
张三	 英语	0	0	5
李四	 数学	6	0	0
李四	 语文	0	7	0
李四	 英语	0	0	8
王五	 数学	9	0	0
王五	 语文	0	10	0
王五	 英语	0	0	11

4、汉字按首字母排序

SELECT * FROM `city`;
id  name
11	北京
12	哈尔滨
13	齐齐哈尔
14	大连

SELECT * FROM `learn` order by CONVERT( bro USING gbk ) COLLATE gbk_chinese_ci ASC;
id  name
11	北京
14	大连
12	哈尔滨
13	齐齐哈尔

反向查询

1.查找某个表属于哪个数据库

SELECT table_schema FROM information_schema.TABLES WHERE table_name = ‘表名字’;
1

2.查找某个字段在哪个数据库和表中有使用到：

select table_schema,table_name from information_schema.columns where column_name = ‘字段名字’
————————————————
版权声明：本文为优快云博主「胡萝卜说」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/qidaihuimou/article/details/123658757