目录
BIN(x) 返回x的二进制格式(OCT返回八进制,HEX返回十六进制)
CEILING(x)/CEIL(x) 返回大于x的最小整数值
GREATEST(x1,x2,x3,....xn) 返回集合{x1~xn}的最大值
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
GROUP_CONCAT() 返回由属于一组的列值连接组合而成的结果
LEFT(str,n)/RIGHT(str,n) 截取字符串左/右n个字符
LPAD(s1,len,s2)和RPAD(s1,len,s2) 填充字符串
LTRIM(s),RTRIM(s),TRIM(s) 删除字符串中的空格
REPLACE(s,s1,s2) 将字符串s中的s1字符替换成s2
FIND_IN_SET(s1,s2) 返回字符串s1在字符串列表s2中出现的位置
CURDATE() /CURRENT_DATE() 返回当前日期
CURTIME() /CURRENT_TIME() 返回当前时间
DAYOFWEEK(date)/DAYOFMONTH(date)/DAYOFYEAR(date) 返回date是一周/月/年中的第几天
DAYNAME(date)/MONTHNAME(date) 返回date的星期名/月名
一、数学函数
+ - * / %(MOD) 加减乘除取余
mysql> select 30+20,30-20
-> ;
+-------+-------+-------+--------+-------+-----------+----------+
| 30+20 | 30-20 | 30*20 | 30/2 0 | 30%20 | 30 mod 20 | mod(5,3) |
+-------+-------+-------+--------+-------+-----------+----------+
| 50 | 10 | 600 | 1.5000 | 10 | 10 | 2 |
+-------+-------+-------+--------+-------+-----------+----------+
1 row in set (0.00 sec)
ABS(x) 返回绝对值
mysql> select abs(-32);
+----------+
| abs(-32) |
+----------+
| 32 |
+----------+
1 row in set (0.00 sec)
BIN(x) 返回x的二进制格式(OCT返回八进制,HEX返回十六进制)
mysql> select bin(5),oct(10),hex(20);
+--------+---------+---------+
| bin(5) | oct(10) | hex(20) |
+--------+---------+---------+
| 101 | 12 | 14 |
+--------+---------+---------+
1 row in set (0.00 sec)
CEILING(x)/CEIL(x) 返回大于x的最小整数值
mysql> select ceiling(3.2),ceiling(-3.2);
+--------------+---------------+
| ceiling(3.2) | ceiling(-3.2) |
+--------------+---------------+
| 4 | -3 |
+--------------+---------------+
1 row in set (0.01 sec)
FLOOR(x) 返回小于x的最大整数值
mysql> select floor(3.2),floor(-3.2);
+------------+-------------+
| floor(3.2) | floor(-3.2) |
+------------+-------------+
| 3 | -4 |
+------------+-------------+
1 row in set (0.00 sec)
ROUND(x,y) 返回x四舍五入y位小数后的值
mysql> select round(3.14,0),round(PI(),5);
+---------------+---------------+
| round(3.14,0) | round(PI(),5) |
+---------------+---------------+
| 3 | 3.14159 |
+---------------+---------------+
1 row in set (0.00 sec)
TRUNCATE(x,y) 将x截短为y位小数
mysql> select truncate(PI(),3);
+------------------+
| truncate(PI(),3) |
+------------------+
| 3.141 |
+------------------+
1 row in set (0.00 sec)
SIGN(x) 返回x的符号值
1为正数,-1为负数
mysql> select sign(32),sign(-32);
+----------+-----------+
| sign(32) | sign(-32) |
+----------+-----------+
| 1 | -1 |
+----------+-----------+
1 row in set (0.00 sec)
PI() 返回圆周率
mysql> select pi();
+----------+
| pi() |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)
GREATEST(x1,x2,x3,....xn) 返回集合{x1~xn}的最大值
mysql> select greatest(2,3,5,1,6);
+---------------------+
| greatest(2,3,5,1,6) |
+---------------------+
| 6 |
+---------------------+
1 row in set (0.00 sec)
SQRT(x) 返回x的平方根
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
avg() 返回某字段的平均值
count() 返回某字段的行数
max() 返回某字段的最大值
min() 返回某字段的最小值
sum() 返回某字段的和
mysql> select id,max(sale),min(sale),sum(sale),avg(sale),count(sale) from test t group by id;
+------+-----------+-----------+-----------+-----------+-------------+
| id | max(sale) | min(sale) | sum(sale) | avg(sale) | count(sale) |
+------+-----------+-----------+-----------+-----------+-------------+
| 1 | 300 | 100 | 800 | 200.0000 | 4 |
| 2 | 400 | 200 | 600 | 300.0000 | 2 |
| 3 | 100 | 100 | 100 | 100.0000 | 1 |
+------+-----------+-----------+-----------+-----------+-------------+
3 rows in set (0.00 sec)
GROUP_CONCAT() 返回由属于一组的列值连接组合而成的结果
mysql> select id,group_concat(id,name,sale) from test group by id;
+------+---------------------------------+
| id | group_concat(id,name,sale) |
+------+---------------------------------+
| 1 | 1aaa100,1bbb200,1ccc200,1ddd300 |
| 2 | 2eee400,2fff200 |
| 3 | 3abc100 |
+------+---------------------------------+
3 rows in set (0.00 sec)
三、字符串函数
mysql> select length('abc'),length('baidu'),length('百度');
+---------------+-----------------+------------------+
| length('abc') | length('baidu') | length('百度') |
+---------------+-----------------+------------------+
| 3 | 5 | 6 |
+---------------+-----------------+------------------+
1 row in set (0.00 sec)
ASCII(c) 返回字符的ASCII码
这里字符c必须要用'单引号'或"双引号"括起来,否则会报错
mysql> select ascii('a');
+------------+
| ascii('a') |
+------------+
| 97 |
+------------+
1 row in set (0.00 sec)
mysql> select ascii("a");
+------------+
| ascii("a") |
+------------+
| 97 |
+------------+
1 row in set (0.00 sec)
CHAN_LENGTH() 返回字符串中的字符个数
mysql> select char_length('abc'),char_length('baidu'),char_length('百度');
+--------------------+----------------------+-----------------------+
| char_length('abc') | char_length('baidu') | char_length('百度') |
+--------------------+----------------------+-----------------------+
| 3 | 5 | 2 |
+--------------------+----------------------+-----------------------+
1 row in set (0.01 sec)
LENGTH() 返回字符串长度
mysql> select length('abc'),length('baidu'),length('百度');
+---------------+-----------------+------------------+
| length('abc') | length('baidu') | length('百度') |
+---------------+-----------------+------------------+
| 3 | 5 | 6 |
+---------------+-----------------+------------------+
1 row in set (0.00 sec)
CONCAT(s1,s2) 合并字符串
mysql> select concat('123','abc','456');
+---------------------------+
| concat('123','abc','456') |
+---------------------------+
| 123abc456 |
+---------------------------+
1 row in set (0.00 sec)
CONCAT_WS(x,s1,s2) 合并字符串
mysql> select concat_ws('+','123','abc','456');
+----------------------------------+
| concat_ws('+','123','abc','456') |
+----------------------------------+
| 123+abc+456 |
+----------------------------------+
1 row in set (0.00 sec)
INSERT(s1,x,len,s2) 替换字符串
insert(s1,x,len,s2)
返回字符串s1
,其子字符串起始于x
位置和被字符串s2
取代的len
字符。如果x
超过字符串长度,则返回值为原始字符串。假如len
的长度大于其他字符串的长度,则从位置x
开始替换。若任何一个参数为null
,则返回值为null
。
mysql> select insert('123456789',2,3,'a');
+-----------------------------+
| insert('123456789',2,3,'a') |
+-----------------------------+
| 1a56789 |
+-----------------------------+
1 row in set (0.00 sec)
mysql> select insert('123456789',2,3,'aaaaaaa');
+-----------------------------------+
| insert('123456789',2,3,'aaaaaaa') |
+-----------------------------------+
| 1aaaaaaa56789 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select insert('123456789',2,0,'a');
+-----------------------------+
| insert('123456789',2,0,'a') |
+-----------------------------+
| 1a23456789 |
+-----------------------------+
1 row in set (0.00 sec)
LOWER(str)/LCASE(str) 大写转小写
mysql> select lower('aBc');
+--------------+
| lower('aBc') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
mysql> select lcase('aBc');
+--------------+
| lcase('aBc') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
UPPER(str)/UCASE(str) 小写转大写
mysql> select upper('aBc');
+--------------+
| upper('aBc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
mysql> select ucase('aBc');
+--------------+
| ucase('aBc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
LEFT(str,n)/RIGHT(str,n) 截取字符串左/右n个字符
mysql> select left('abcdefg',3),right('abcdefg',3);
+-------------------+--------------------+
| left('abcdefg',3) | right('abcdefg',3) |
+-------------------+--------------------+
| abc | efg |
+-------------------+--------------------+
1 row in set (0.00 sec)
LPAD(s1,len,s2)和RPAD(s1,len,s2) 填充字符串
返回长度为len的函数。len>s1,则对s1进行截取。len<s1,则不足的部分用s2填补。
mysql> select lpad('abcdefg',5,'123'),lpad('abc',5,'1'),lpad('abc',5,'12345');
+-------------------------+-------------------+-----------------------+
| lpad('abcdefg',5,'123') | lpad('abc',5,'1') | lpad('abc',5,'12345') |
+-------------------------+-------------------+-----------------------+
| abcde | 11abc | 12abc |
+-------------------------+-------------------+-----------------------+
1 row in set (0.01 sec)
mysql> select rpad('abcdefg',5,'123'),rpad('abc',5,'1'),rpad('abc',5,'12345');
+-------------------------+-------------------+-----------------------+
| rpad('abcdefg',5,'123') | rpad('abc',5,'1') | rpad('abc',5,'12345') |
+-------------------------+-------------------+-----------------------+
| abcde | abc11 | abc12 |
+-------------------------+-------------------+-----------------------+
1 row in set (0.00 sec)
LTRIM(s),RTRIM(s),TRIM(s) 删除字符串中的空格
只会删除字符串两端的空格,字符串中间的空格不会删除
mysql> select ltrim(' abc '),rtrim(' abc '),trim(' abc '),trim('a bc');
+----------------+----------------+---------------+--------------+
| ltrim(' abc ') | rtrim(' abc ') | trim(' abc ') | trim('a bc') |
+----------------+----------------+---------------+--------------+
| abc | abc | abc | a bc |
+----------------+----------------+---------------+--------------+
1 row in set (0.00 sec)
TRIM(s1 from s) 删除字符串s两端的s1
同样只能删除两端的字符串s
mysql> select trim('@' from '@aaa@aaa@');
+----------------------------+
| trim('@' from '@aaa@aaa@') |
+----------------------------+
| aaa@aaa |
+----------------------------+
1 row in set (0.00 sec)
REPEAT(x,n) 重复字符串x,n次
mysql> select repeat('a',5),repeat('a',0),repeat('a',null);
+---------------+---------------+------------------+
| repeat('a',5) | repeat('a',0) | repeat('a',null) |
+---------------+---------------+------------------+
| aaaaa | | NULL |
+---------------+---------------+------------------+
1 row in set (0.00 sec)
SPACE(n) 返回n个空格的字符串
mysql> select concat('[', space(5), ']');
+----------------------------+
| concat('[', space(5), ']') |
+----------------------------+
| [ ] |
+----------------------------+
1 row in set (0.00 sec)
REPLACE(s,s1,s2) 将字符串s中的s1字符替换成s2
mysql> select replace('abcde*fg*','*','@'),replace('aabbccdd','aa','@');
+------------------------------+------------------------------+
| replace('abcde*fg*','*','@') | replace('aabbccdd','aa','@') |
+------------------------------+------------------------------+
| abcde@fg@ | @bbccdd |
+------------------------------+------------------------------+
1 row in set (0.00 sec)
STRCMP(s1,s2) 比较字符串s1和s2的大小
s1<s2,则返回-1;s2>s2,返回1;相等返回0
mysql> select strcmp('test','test'),strcmp('test2','test1'),strcmp('test1','test2');
+-----------------------+-------------------------+-------------------------+
| strcmp('test','test') | strcmp('test2','test1') | strcmp('test1','test2') |
+-----------------------+-------------------------+-------------------------+
| 0 | 1 | -1 |
+-----------------------+-------------------------+-------------------------+
1 row in set (0.00 sec)
SUBSTRING(s,n,len) 截取字符串
从字符串s
返回一个长度与len
字符相同的子字符串,起始于位置n
。
mysql> select substring('abcdefg',2,5);
+--------------------------+
| substring('abcdefg',2,5) |
+--------------------------+
| bcdef |
+--------------------------+
1 row in set (0.01 sec)
REVERSE(str) 字符串逆序
mysql> select reverse('1234567');
+--------------------+
| reverse('1234567') |
+--------------------+
| 7654321 |
+--------------------+
1 row in set (0.00 sec)
FIND_IN_SET(s1,s2) 返回字符串s1在字符串列表s2中出现的位置
不区分大小写。
mysql> select find_in_set('A','bcd,A,ABCD');
+-------------------------------+
| find_in_set('A','bcd,A,ABCD') |
+-------------------------------+
| 2 |
+-------------------------------+
1 row in set (0.00 sec)
四、日期和时间函数
CURDATE() /CURRENT_DATE() 返回当前日期
mysql> select curdate(),current_date();
+------------+----------------+
| curdate() | current_date() |
+------------+----------------+
| 2022-12-13 | 2022-12-13 |
+------------+----------------+
1 row in set (0.00 sec)
CURTIME() /CURRENT_TIME() 返回当前时间
mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 18:36:56 | 18:36:56 |
+-----------+----------------+
1 row in set (0.00 sec)
NOW() 返回当前日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-12-13 18:38:33 |
+---------------------+
1 row in set (0.00 sec)
DAYOFWEEK(date)/DAYOFMONTH(date)/DAYOFYEAR(date) 返回date是一周/月/年中的第几天
mysql> select dayofweek(now()),dayofmonth(now()),dayofyear(now());
+------------------+-------------------+------------------+
| dayofweek(now()) | dayofmonth(now()) | dayofyear(now()) |
+------------------+-------------------+------------------+
| 3 | 13 | 347 |
+------------------+-------------------+------------------+
1 row in set (0.00 sec)
DAYNAME(date)/MONTHNAME(date) 返回date的星期名/月名
mysql> select dayname(now()),monthname(now());
+----------------+------------------+
| dayname(now()) | monthname(now()) |
+----------------+------------------+
| Tuesday | December |
+----------------+------------------+
1 row in set (0.00 sec)
WEEK(date) 返回date为一年中的第几周
YEAR(date) 返回当前年份
mysql> select month(now()),year(now());
+--------------+-------------+
| month(now()) | year(now()) |
+--------------+-------------+
| 12 | 2022 |
+--------------+-------------+
1 row in set (0.00 sec)
五、开窗函数
开窗函数这个连接写的比我全,我这里就直接引用了~~
MySQL开窗函数_weihuan2323的博客-CSDN博客_mysql开窗函数
六、staff函数
6.1 官方解释
STUFF 函数将字符串插入到另一个字符串中。 它从第一个字符串的开始位置删除指定长度的字符;然后将第二个字符串插入到第一个字符串的开始位置。
6.2 语法格式
STUFF ( character_expression , start , length ,replaceWith_expression )
参数详解
1、character_expression
字符数据的表达式。character_expression 可以是常量、变量,也可以是字段或二进制字段。
2、start
start用来指定删除和插入开始位置的数值。 如果 start 值为负或为零,则返回空字符串。 如果 start 的长度大于第一个 character_expression,则返回空字符串。start 的类型也可以是 bigint。注意:start 值1 表示第一个字符。
3、length
length用来指定要删除的字符个数。 如果 length值 为负,则返回空字符串。 如果 length 的长度大于第一个 character_expression,则最多可以删除到最后一个 character_expression 中的最后一个字符。 如果 length 为零,则插入在 start 位置发生,并且不会删除任何字符。length 的类型也可以是 bigint。
4、replaceWith_expression
字符数据的表达式。character_expression 可以是常量、变量,也可以是字段或二进制字段。 此表达式从 start 开始替换 length 个字符的 character_expression。 如果 replaceWith_expression 为 NULL,则在不插入任何内容的情况下删除字符。
5、返回类型
如果 character_expression 是支持的字符数据类型之一,则返回字符数据。如果 character_expression 是支持的二进制数据类型之一,则返回二进制数据。
6、备注
如果开始位置或长度值是负数,或者开始位置大于第一个字符串的长度,则返回 Null 字符串。 如果开始位置为 0,则返回 Null 值。 如果要删除的长度大于第一个字符串的长度,则删除到第一个字符串中的第一个字符。
如果结果值大于返回类型支持的最大值,则会引发错误。
6.3 Stuff函数用法示例
6.3.1 删除字符
select STUFF('hello',1,1,'') as 删除字符列 -- 删除第一个字符
6.3.2 替换字符
select STUFF('hello',4,2,'aa') as 替换字符列 -- 将lo 替换为 aa
6.3.2 字段拼接功能(实际开发当中比较常用)
1、创建测试数据表
CREATE TABLE [dbo].[T_user]( [id] [varchar](36) NOT NULL, [name] [varchar](36) NULL, [age] [int] NULL, [address] [nvarchar](50) NULL, [create_date] [datetime] NULL, CONSTRAINT [PK_T_user] PRIMARY KEY CLUSTERED( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
2、插入数据
1 admin 25 北京 2020-08-22 00:00:00.000
2 user 30 南京 2020-08-22 00:00:00.000
3、查询脚本示例
SELECT STUFF((SELECT ','+name FROM dbo.T_user FOR XML PATH('')),1,1,'') names
执行结果: