msyql 常用函数

叁歲伎倆 2024-04-20 22:19 32阅读 0赞

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

### 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博客][Link 4]》

下一篇： 《[mysql 基本查询][mysql]》

[Link 1]: https://blog.csdn.net/qq_35461948/article/details/136918694
[Link 2]: https://blog.csdn.net/qq_35461948/article/details/136919740
[Link 3]: https://blog.csdn.net/qq_35461948/article/details/136920705
[Link 4]: https://blog.csdn.net/qq_35461948/article/details/136937262
[mysql]: https://blog.csdn.net/qq_35461948/article/details/136963960?csdn_share_tail=%7B%22type%22%3A%22blog%22%2C%22rType%22%3A%22article%22%2C%22rId%22%3A%22136963960%22%2C%22source%22%3A%22qq_35461948%22%7D