msyql 常用函数

        前面学习了数学函数，字符串函数，时间函数和条件加密函数，把平时比较长用的函数汇总下。

1，数学函数

1，CEIL(x)获取不小于x的最小整数

        CEIL(x)和 CEILING(x)意义相同，返回不小于x的最小整数值，返回值转化为一个BIGINT。

SELECT CEIL(-3.35), CEILING(3.35);

2，FLOOR(x)获取大于x的最大整数值

        FLOOR(x)返回不大于x的最大整数值，返回值转化为一个BIGINT。

SELECT FLOOR(-3.35), FLOOR(3.35);

3，获取随机数的函数RAND()和 RAND(x)

        RAND(x)返回一个随机浮点值v，范围在0到1之间(即0≤v≤1.0)。若已指定一个整数参数x,则它被用作种子值，用a来产生重复序列。

SELECT RAND(), RAND(), RAND();

4，ROUND(x)返回最接近于参致x的整数，对x值进行四舍五入。

SELECT ROUND(-1.14),ROUND(-1.67),ROUND(1.14), ROUND(1.66);

5，获取1-100以内的随机数

        处理：用rand()获取随机数，用floor获取整数（FLOOR()函数用来获取不大于给定数字的最大整数）

SELECT FLOOR(1 + (RAND() * 100)) AS random_number;


SELECT FLOOR(1 + (RAND() * 100)) AS random_number1,
       FLOOR(1 + (RAND() * 100)) AS random_number2,
       FLOOR(1 + (RAND() * 100)) AS random_number3;

2，字符串函数

1，合并字符串函数CONCAT(s1,s2,...)、CONCAT_WS(x,s1,s2,...)x

        CONCAT(s1,s2…)返回结果为连接参数产生的字符串，或许有一个或多个参数。如有任何一个参数为NULL，则返回值为NULL。

SELECT CONCAT('My SQL','5.7'), CONCAT('My', NULL, 'SQL');

        CONCAT_WS(x,s1,s2,…)，CONCAT_WS 代表CONCAT With Separator，是CONCAT()的特殊形式，第一个参数x是其他参数的分隔符，分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其他参数。如果分隔符为NULL，则结果为NULL。函数会忽略任何分隔符参数后的NULL值。

SELECT CONCAT_WS('-','1st', '2nd', '3rd'), CONCAT_WS('*', '1st', NULL,   '3rd');

        如果连接值中有NULL，就用CONCAT_WS()，不然就得用ifnull()判断是否null

2，删除空格TRIM(s)

        TRIM(s)删除字符串s两侧的空格。

SELECT '( book )', CONCAT ('(', TRIM(' book '),')');

3，删除指定字符串的函数TRIM(s1 FROM s)

      从字符串中删除不需要的字符。

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);

        可以使用LEADING，TRAILING或BOTH选项明确指示TRIM()函数从字符串中删除前导，尾随或前导和尾随的不必要的字符。

        TRIM(s1 FROM s)删除字符串s 中两端所有的子字符串s1，s1为可选项,在未指定情况下，删除空格。

SELECT TRIM('xy' FROM 'xyxboxyokxxyXy');

        删除字符串末尾的换行符

SELECT TRIM(TRAILING '\r\n' FROM field_name);

        删除字符串末尾的逗号

SELECT TRIM(TRAILING ',' FROM '1007012,5005,6002005,7003,')
 AS result;

4，替换函数REPLACE(s,s1,s2)

        REPLACE(s,s1,s2)使用字符串替代字符串s中所有的字符串s1。

SELECT REPLACE('xxx.mysql.com', 'x', 'w');

5，获取子串的函数SUBSTRING(s,n,len)

        SUBSTRING(s,n,len)带有len参数的格式，从字符串s返回一个长度同 len字符相同的子字符串，起始于位置n，也可能对n使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的n字符，即倒数第n个字符，而不是字符串的开头位置。

 SELECT SUBSTRING('breakfast', 5) AS coll,
 SUBSTRING('breakfast', 5,3) AS co12,
SUBSTRING('lunch', -3) AS co13,
SUBSTRING('lunch', -5, 3)AS col4;

6，GROUP_CONCAT() 将多行字段数据合并为一行 

group_concat()函数可以将多行字段数据合并为一行；且内容之间可以指定分隔符；

1、默认以逗号分隔符连接

汇总员工姓名：

 SELECT GROUP_CONCAT(NAME)  FROM employee;

2、可自定义对字段去重排序和指定分隔符

1、自定义分隔符

语法：

SELECT GROUP_CONCAT(字段名 SEPARATOR '分隔符号') from 表名;

汇总员工姓名，用“|”作为分隔符：

SELECT GROUP_CONCAT(NAME SEPARATOR '|') FROM employee;

3，日期时间函数

1，DATEDIFF(date1,date2)计算两个日期相差天数

        DATEDIFF(date1,date2)返回起始时间datel和结束时间date2之间的天数。date1和 date2为日期或date-and-time表达式。计算中只用到这些值的日期部分。

SELECT DATEDIFF('2010-12-31 23:59:59', '2010-12-30') AS col1,
 DATEDIFF('2010-11-30 23:59:59', '2010-12-31')AS col23;

2，将日期和时间格式化的函数

        DATE_FORMAT(date,format)根据format指定的格式显示date值

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y') AS co11,
DATE_FORMAT('1997-10-04 22:23:00', '%D %y %a %d %m %b %j') AS co12,
DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s') AS co13,
DATE_FORMAT('1999-01-01', '%X %V') AS col4;

3，将日期和时间字符串转为日期和时间

        STR_TO_DATE('日期时间字符串', '格式') 把日期时间字符串转为时间

SELECT STR_TO_DATE('2024-02-28 12:15:16', '%Y-%m-%d %H:%i:%s');
SELECT STR_TO_DATE('2024-02-28', '%Y-%m-%d');
SELECT STR_TO_DATE('12:15:16', '%H:%i:%s'); 

4，条件判断函数

1，IF(expr,v1,v2)函数

        IF(expr, vl, v2)，如果表达式expr是TRUE(expr > 0 and expr  NULL)，则IF()的返回值为v1；否则返回值为v2。IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

SELECT IF(1>2, 2, 3),
IF(1<2, 'yes' , 'no'),
IF(STRCMP('test', 'test1'), 'no', ' yes');

2，IFNULL(v1,v2)函数

        IFNULL(v1,v2)假如vI不为NULL，则IFNULL()的返回值为vl；否则其返回值为v2。

        IFNULL()的返回值是数字或是字符串，具体情况取决于其所在的语境。

SELECT IFNULL(1,21) IFNULL(NULL,10), IFNULL(1/0, 'wrong');

3，CASE 函数.

        CASE expr WHEN v1 THEN rl [WHEN v2 THEN r2] [ELSE m] END

        该函数表示，如果expr值等于某个vn,则返回对应位置THEN后面的结果。如果与所有值都不相等，则返回ELSE后面的m。

SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more!' END; 

        CASE WHEN v1 THEN rl [WHEN v2 THEN r2] [ELSE m] END

        该函数表示，某个vn值为TRUE时，返回对应位置THEN后面的结果，如果所有值都不

为TRUE，则返回ELSE后的m。

 SELECT CASE WHEN 1<0 THEN 'true' ELSE 'false' END;

5，加/解密函数

1，加密函数MD5(str)

        MD5(str)为字符串算出一个 MD5 128比特校验和。该值以32位十六进制数字的二进制字符串形式返回，若参数为NULL，则会返回NULL。

SELECT MD5('mypwd');


-- 加密后转成大写
SELECT UPPER(MD5('mypwd'));

MD5加密是不可逆的。

6，其他函数

1，格式化函数FORMAT(x,n)

        FORMAT(x,n)将数字x格式化，并以四舍五入的方式保留小数点后n位，结果以字符串的形式返回。若n为0，则返回结果函数不含小数部分。

SELECT FORMAT(12332.123456,4), FORMAT(12332,1,4), FORMAT(12332,2,0);

2，改变数据类型的函数

        CAST(x , AS type)和CONVERT(x, type)函数将一个类型的值转换为另一个类型的值，可转换的type有：BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNSIGNED。

SELECT CAST(100 AS CHAR(2)), CONVERT('2010-10-01 12:12:12', TIME);

总结：

        把常用的函数汇总起来，把语法和作用做一个说明，下次忘记怎么用打开来就一清二楚了。函数，再处理数据，查询时间也会经常用到。 后续有遇到常用的函数，再补充。

        上一篇：《mysql 条件/系统/加密/其它函数-CSDN博客》

        下一篇： 《mysql 基本查询》