MySQL常用函数、关键词、实操

小白|一枚

已于 2024-06-20 14:55:32 修改

阅读量1.9k

点赞数 2

分类专栏：笔记 MySQL 文章标签： mysql sql 数据库

于 2021-09-27 16:54:42 首次发布

本文链接：https://blog.csdn.net/weixin_44231416/article/details/118793562

版权

笔记同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

一、关键词

1、unsigned : 非负数，定义非负，可以在定义主键的自增列时使用。

	column_name int UNSIGNED AUTO_INCREMENT

2、desc ：降序，从大到小；

asc 或缺省：为升序，从小到大，和order by 配合使用。

	order by column_name desc

3、union ：连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。

column_name 为列名，table 为表名，[] 为可选条件，where conditions 为检索条件，
all 结果集中包含重复数据，distinct 为默认结果集中不包含重复数据

	select column_name1,column_name2...
	from table1
	[where conditions] 
	union [all | distinct]
	select column_name1,column_name2...
	from table2
	[where conditions]

4、is null ：当列的值是 NULL,此运算符返回 true；

is not null ：当前列的值不为空，运算符返回true。

	select * from table column_name is null;
	select * from table column_name is not null;

5、inner join、join ：内连接，获取两个表中字段匹配关系的记录；

left join ：左连接，获取左表所有记录，即使右表没有对应匹配的记录；
right join ：右连接，与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

	select table1.column_name1, teable2.column_name2...
	from table1
	left join table2 on table1.column_name1= table2.column_name2;

6、between ：配合WHERE使用，选取介于value1和value2之间的数据范围，值可以是数值、文本或者日期。如需过滤范围之外的，可在 between 前加 not 关键词（not between value1 and value2）

	SELECT column_name FROM teable WHERE column_name BETWEEN value1 AND value2;

7、case：条件判断关键词

CASE [column_name] WHEN [value1] THEN [result1]... ELSE [default] END
select
	CASE 'a'
	WHEN 'a' THEN '1'
	WHEN 'b' THEN '2'
	ELSE '3' END; -- 1
CASE WHEN [expr] THEN [result1]... ELSE [default] END
select
	CASE
	WHEN 1 >= 2 THEN 'a'
	WHEN 3 >= 2 THEN 'b'
	ELSE 'c' END; -- b

8、binary：二进制数据类型，也可以强制将其后的字符串转换为字节

select 'A' = 'a' -- 1

select binary 'A' = binary 'a' -- 0

二、函数

（一）、处理字符串

1、replace(str,from_str,to_str)

	把str中的from_str替换为to_str。
	from_str 为null时会将str置为null，str不局限于字符型，但是to_str的类型需要和str一致。
	replace('abcd','b',',') -- a,cd

2、substring_index(str, delimiter, number)

	返回从字符串 str 的第 number 个出现的分隔符 delimiter 之后的子串。
	如果 number 是正数，返回第 number 个字符左边的字符串。
	如果 number 是负数，返回第(number 的绝对值(从右边数))个字符右边的字符串。
	substring_index('aa*bb' , '*' , 1) -- aa
	substring_index('aa*bb' , '*' , -1) -- bb

3、substr(str, start, length)

	从字符串 str 的 start 位置截取长度为 length 的子字符串
	下标从1开始包括start和length []闭合区间。
		substr('abcd',2,2) -- bc

4、trim(str)

	去掉字符串 str 开始和结尾处的空格。
	trim('  qwer  ') -- qwer

5、position(str1 IN str)

	从字符串 str 中获取 str1 的开始位置
	从1开始，返回位置包括 str1。
	position( 'aad' in 'qweraadqwre') -- 5

6、character_length(str)

	返回str的字符数。
	character_length('asdf') -- 4
	character_length('嘎嘎') -- 2

7、length(str)

	返回str的字节数
	utf8编码下,一个汉字3个字节，一个数字或字母1个字节
	gbk编码下,一个汉字2个字节，一个数字或字母1个字节。
	length('哈哈！') -- 9
	length('qwer') -- 4

8、instr(field, str)

	返回串 str 的位置，没找到返回0，从1开始
	第一个参数 field 是字段，第二个参数 str 是要查询的串。
	instr('qwer','e') -- 3

9、concat(str1,str2…)

	返回多个字符串的连接值，
	如果有任何一个参数为null，则返回值为null。
	select concat('aa','-','bb') -- aa-bb
	select concat('a',',','b',',','c') -- a,b,c

10、concat_ws(separator,str1,str2…)

	和concat()一样，将多个字符串连接成一个字符串，
		但是可以一次性指定分隔符，str参数为null时可正常返回连接值，
	separator参数：
		分隔符，如果分隔符为null，则返回值为null。
	select concat_ws('-','a','b') -- a-b
	select concat_ws(',','a','b','c') -- a,b,c
	select concat_ws(',','a','b','c',null,'d',null,null) -- a,b,c,d

11、group_concat(column_name1,column_name2…)

	将group by产生的同一个分组中的值连接起来，返回一个字符串结果。分组字符串连接。
	注意：有最大长度的限制，超过最大长度就会被截断掉，默认长度为1024字符。
		获取当前设置的长度
			select @@global.group_concat_max_len;
			show variables like "group_concat_max_len";
		修改全局变量设置的长度，之后重启MySQL服务。
			SET GLOBAL group_concat_max_len=10240;
			SET SESSION group_concat_max_len=10240; 
			或者在MySQL配置文件中my.conf或my.ini中添加:
			　　#[mysqld]
			　　group_concat_max_len=10240
	group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator 'separator'] )
		[]为选参数，
		distinct，去重，通过使用distinct可以排除重复值；
		order by 排序字段 asc/desc ，如果要对连接结果集中的值进行排序，可以使用order by子句；
		separator 'separator'，是一个字符串值（可以是换行符），如果缺省不写，默认为一个逗号。

12、find_in_set(str,strlist)

	查询str于strlist中的位置，下标从1开始，如在strlist中未找到，则返回0
	str参数：
		如为null，则返回null；如参数包含“,”，则无法进行使用，返回0。
	strlist参数：
		以“,”分隔，如：(1,2,6,8,10,22)、"1,2,3"；如为null，则直接返回null。
		也可以为具体某个字段。
	select find_in_set(null,"1,2,3") -- null
	select find_in_set("","1,2,3") -- 0
	select find_in_set("1,2","1,2,3") -- 0
	select find_in_set(0,"1,0,3") -- 2
	select find_in_set(2,null) -- null

（二）、处理日期

1、timediff(time1 , time2)

	返回time1,time2两个时间相减得到的差值；返回差值格式为，时:分:秒
	time1-time2。
	timediff( '2021-11-12 10:00:00' , '2021-11-12 18:00:00') -- -08:00:00

2、time_to_sec(time1)

	返回time1的秒值，
	时间值转换为秒值，可配合timediff()使用。
	time_to_sec( '8:00:00' ) -- 28800
	time_to_sec ( timediff( '2021-11-12 10:00:00' , '2021-11-12 18:00:00') ) -- -28800

3、timestampdiff(interval, time1,time2)

	timediff的升级版，可以定义返回单位，
	返回time1,time2两个时间相减得到的差值,结果单位由interval参数给出，
	time2-time1，
	interval参数：
		frac_second 毫秒（低版本不支持MySQL 5.6之后才支持毫秒的记录和计算），
		second 秒，minute 分钟，hour 小时，day 天，week 周，month 月，quarter 季度，year 年。
	timestampdiff(second, '2021-11-12 10:00:00' , '2021-11-12 18:00:00') -- 28800

4、date_sub(date,INTERVAL expr type)、date_add(date,INTERVAL expr type)

	date_sub() 函数从日期减去指定的时间间隔，
	date_add() 函数从日期加上指定的时间间隔，
	date 参数是合法的日期表达式，
	expr 参数是希望添加的时间间隔（可以为负），
	type 参数：常用参数
		Second 秒,Minute 分钟,Hour 小时,Day 天,Week 周,Month 月,Quarter 季度,Year 年...
	
	select now(),date_add(now(),interval 60 second) -- 当前时间加60秒
	select now(),date_sub(now(),interval -60 second) -- 当前时间加60秒

5、timestampadd(interval, expr ,date )

	与date_add()相仿，从日期加上指定的时间间隔，
	interval参数：
		frac_second 毫秒（低版本不支持MySQL 5.6之后才支持毫秒的记录和计算），
		second 秒，minute 分钟，hour 小时，day 天，week 周，month 月，quarter 季度，year 年。
	expr 参数是希望添加的时间间隔（可以为负），
	date 参数是合法的日期表达式。
	select now(),timestampadd(second,60,now()) -- 当前时间加60秒

（三）、服务器信息

1、version()

	返回mysql服务器信息，
	mycat也可以使用。
	mycat：
		version() -- 5.5.8-mycat-1.5.1-RELEASE-20160816173057 
	mysql：
		version() -- 5.7.26-log

（四）、处理JSON

1、JSON_CONTAINS()

JSON_CONTAINS(target_json, candidate_json)
JSON_CONTAINS(target_json, candidate_json, path)
target_json
必需的。一个 JSON 文档。
candidate_json
必需的。被包含的 JSON 文档。
path
可选的。一个路径表达式。

2、JSON_ARRAY()

JSON_ARRAY(value1[, value2[, ...]])
返回一个包含了所有参数的 JSON 数组。

3、JSON_OBJECT()

JSON_OBJECT([key, val[, key, val] ...])
json_object函数的作用,就是将一列键值对转换为json对象,
同时呢,如果是奇数个参数,key为NULL都会报错,
如果有多个key,相同的,则会将后面的key给丢弃掉,即使两个key对应的value不同

（四）、处理数值

1、round() 四舍五入保留n位小数

SELECT round(column_name, 2) FROM table_name;
SELECT round(12.123, 2); -- 12.12

2、greatest(value1,value2…) 取最大值

如果参数中包含null，则函数将返回null；如果参数由数字和字符串组成，则直接截取数字部分作为数字进行比较；

SELECT greatest(column1,column2) FROM table_name;
select greatest(1,123,'12ww'); -- 123
select greatest(1,null,'12ww'); -- null

3、least(value1,value2…) 取最小值

如果参数中包含null，则函数将返回null；如果参数由数字和字符串组成，则直接截取数字部分作为数字进行比较；

SELECT least(column1,column2) FROM table_name;
select least(1,123,'12ww'); -- 1
select least(1,null,'12ww'); -- null

（五）、转换

1、cast(value as type) 类型转换

value 表示待转数据，as 固定语法格式，type 表示转换后的数据类型。

select cast('3.12' as signed); -- 3

2、convert(value,type) 类型转换

value 表示待转数据，type 表示转换后的数据类型。

cast(value as type) 函数和 convert(value,type)函数，type支持的数据类型。

binary：二进制类型；
char：字符类型；
date：日期类型；
time：时间类型；
datetime：日期时间类型；
decimal：浮点型；
signed：整型；
unsigned：无符号整型。

select convert('3.12',signed); -- 3

3、convert(value USING transcoding_name) 转换指定字符集

用于将字符串 value 的字符集变成transcoding_name。

SELECT charset('ABC'); -- utf8mb4

SELECT charset(convert('ABC' USING gbk)); -- gbk

三、运算符

在这里插入图片描述

四、转义符

&lt; --<
&gt; -->

五、实操

（一）、DML语句

1、快速将把一个表的数据插入到另一个表中（insert … select …）

select语句查询结果与insert语句的插入字段一一对应，如不对应及报错，select语句与正常的语法、用法相同。

-- 表完全相同，并且插入全部数据，table2的数据插入到table1中。
insert into table1 select * from table2;
-- 插入部分字段的全部数据，table2表中的`code`和`name`插入到table1中的`id`和`name`。
insert into `table1` (`id`,`name`) select `code`,`name` from `table2`;

2、单引号转义符

-- 如查询名称为lisi's的用户，姓名中包含了单引号
select * from user where username='lisi's'; -- 报错
-- 在单引号前再添加一个单引号，即lisi''s。注意，是两个单引号，而不是一个双引号。
select * from user where username='lisi''s'; -- 可以正常查询

3、两个不同编码的字段比较问题

如果关联查询关联字段，编码不同即会报错：
1267 - Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation ‘=’
可以使用collate关键词，设置字段编码。

select a.*,b.* from tableA a left join tableB b on a.code=b.code;
-- 编码不同报错

select a.*,b.* from tableA a
left join tableB b on a.code=b.code collate utf8mb4_general_ci;
-- 转换为相同编码后可以查询

4、查看数据库所有索引

查看指定表的索引

show index from table_name;

查看指定数据库中的所有索引

select * from information_schema.statistics where table_schema = 'database_name'