Mysql 中必知的单行函数

---

每博一文案

钱钟书就曾感慨道，在我一知半解的几国语言中，没有比中国古语
所谓的素交，更能表现出友谊骨髓，素是一切颜色的基础，同时也是
一切颜色的调和，像白日包含了基色，真正的交情，看来像素淡。
自有超越此生的厚意，越是简单的圈子，越不用费心经营，
越是干净的圈子，在关键时刻越帮的上忙。颜色艳丽的花往往没有香气，
反而素净的百花，有浓郁的香味。人也是如此，越朴素，单纯的人越有内在
的芳香。一段朴素简单的友情，看起来频频无常，但在经历困境考验后。
才能看出其中的珍贵之处。
愿你能在这个复杂的世界拥有一段简单而纯粹的关系，有三两好友肝胆相照。
                                        ——————   一禅心灵庙语
1
2
3
4
5
6
7
8
9
10

---

---

函数

函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既可以提高代码的效率，又可以提高代码的可维护性。在SQL中我们也同样存在函数，我们可以使用函数对检索出来的数据进行操作。使用这些函数，可以极大的提高用户对数据库的管理效率。大家来看看吧

在SQL 中存在这两种函数：分别是：单行函数，多行函数

单行函数的特点是：一个输入对应着会产生一个输出，可以嵌套

多行函数的特点是：多个输入对应着会产生一个输出，如sum( )，求和，多个输入数据，一个输出

---

1.数值函数

用来进行数值计算的函数

---

1.1 ABS(x) 绝对值

ABS(x) 返回数值的绝对值，就是去除数值的符号，当 x = 0 ,返回 0， 当 x = NULL, 返回 NULL

SELECT ABS(-9), ABS(9),ABS(0),ABS(NULL)
FROM DUAL; /*dual 是伪表显示*/
1
2

---

1.2 SIGN(x) 获取符号

SIGN(x) : 获取数值的符号，正数时，返回 1， 负数时，返回 -1，0时，返回 0，NULL时，返回 NULL

SELECT SIGN(-2), SIGN(2),SIGN(0),SIGN(NULL)
FROM DUAL; /*dual 伪表*/
1
2

---

1.3 PI() 返回圆周率的值

注意 不可以在 括号中带值，不然会报错的

SELECT PI(), PI()
FROM DUAL;
1
2

---

1.4 CEIL(x)，CEILING(x) 返回 >= 最近最大的整数值

CEIL(x), CEILING(x) 这两个函数作用效果是一样的，返回大于或等于其(x)数值的最近的最大数值，(x)：可以是负数，小数，整数，当然，如果是整数的话，没有影响，返回原来的整数数值

SELECT CEIL(21.33), CEIL(-21.35), CEIL(33), CEILING(21.33), CEILING(-21.35)
FROM DUAL;
1
2

---

1.5 FLOOR(x)返回 <= 最近的最小的整数值

FLOOR(x) ,返回最接近的最小的整数值，(x): 可以是负数，小数，整数，当然，如果是整数的话，没有影响，返回原来的整数数值

SELECT FLOOR(21.33), FLOOR(-21.35), FLOOR(33)
FROM DUAL;
1
2

---

1.6 MOD(x,y) 取余数

MOD(x,y) : 求其余数，和Java中的 %运算符的效果是一样的，取余数的符号，与被除数的符号有关，与其他数值的符号无关

SELECT MOD(3,2), MOD(3,-2), MOD(-3,2), MOD(-3,-2)
FROM DUAL; /*取余数结果的符号，与被除数的符号一致*/
1
2

---

1.7 RAND() RAND(x) 随机值

RAND() 不带种子值，返回0~1之间的随机值，带种子值 RAND(x) ,返回 0~1 之间的随机值，其中 (x)的值作为种子值，相同的 (x)种子值会产生相同的随机数,无论执行多少次，带有种子值的产生的随机数都是固定的。

SELECT RAND(), RAND(), RAND(99), RAND(99), RAND(99)
FROM DUAL;
1
2

---

1.8 ROUND(x)，ROUND(x,y) 四舍五入

ROUND(x) ： 保留整数，四舍五入，

ROUND(x,y) : 保留有效数值 y 位，四舍五入，当 y = 0, 表示保留整数，当 y = 1,表示保留小数点后 1位，当 y = -1,表示保留小数点前一位数值，也就是十位上的数，不足 5 的，补 0 代替

SELECT ROUND(123.456), ROUND(123.456,0), ROUND(123.456,2)
FROM DUAL;
1
2

---

/* 当y为负数时，保留小数点前 y 位的数值，不足5，补 0*/
SELECT ROUND(123.456,-1), ROUND(123.456,-2), ROUND(123.456,-3)
/*           保留 十位        保留 百位       保留 千位，没有千位 0*/
FROM DUAL;
1
2
3
4

---

1.9 TRUNCATE(x,y) 数值的截取

TRUNCATE(x,y) ：截取 x 数值 保留 小数点后 y 位的数值，注意是，截取，所以不会进行四舍五入的取舍的，同样 y 也可以是负数，和上面的 ROUND(x,y) 是一样的，当 y = -1 时，表示 保留 到百位，不足 补 0

SELECT TRUNCATE(123.456,0), TRUNCATE(123.456,1), TRUNCATE(123.456,-1)
FROM DUAL;
1
2

---

1.10 SQRT(x) 求平方根

SQRT(x) 返回数值 x 的平方根，注意 x = 负数时，返回 NULL, 因为 求平方根的数值不可小于 0

SELECT SQRT(9), SQRT(0), SQRT(-1), SQRT(1)
FROM DUAL;
1
2

---

1.11 POW(x,y) , POWER(X,Y)求次方

POW(x,y),POWER(X,Y) 返回数值 x 的 y 次方，

SELECT POW(2,3),POW(0,9), POWER(2,3)
FROM DUAL;
1
2

---

2.字符串函数

用来进行字符串操作的函数

---

2.1 CHAR_LENGTH(s)，LENGTH(s) 求字符串的长度

CHAR_LENGTH(s) 返回字符串s 中含有的字符个数，单位是字符个数

**LENGTH(s) ** 返回字符串s 中所占的字节数，单位是 字节

SELECT CHAR_LENGTH('hello'), CHAR_LENGTH('中国'),
       LENGTH('hello'), LENGTH('中国')
FROM DUAL;
1
2
3

中文在 UTF8 中一个中文占 3个字节空间，所以这里是为 6 个字节

---

2.2 CONCAT(s1,s2,…,sn), CONCAT_WS(x,s1,s2,…,sn) 字符串的拼接

在Mysql 中对于 SQL 语法中的字符串拼接的 运算符 || 并不支持，所以对于字符串的内容中的拼接我们使用以下两个函数：

CONCAT(s1,s2…,sn) 将 字符串 s1, 和字符串 s2 … 字符串 sn，拼接成一个字符串返回

CONCAT_WS(x,s1,s2,…sn) 同样也是将各个字符串，拼接成一个字符串，值得注意的是，每个字符串的拼接之间加上 x

SELECT CONCAT('hello','world','!!') AS 'demo', CONCAT_WS('-','hello','world','!!') AS 'demo1'
FROM DUAL;
1
2

---

2.3 INSERT(str, idx,len,replacestr) 替换字符串

INSERT(str, idx,len,replacestr) ： 将字符串 str 中第 idx 位置开始，len个字符长的子串替换为字符串replacestr, 注意 字符串的起始下标位置 是 1 ，没有 0

SELECT INSERT('hellowrold',1,3,'aaaaaa') AS 'demo', INSERT('hellowrold',0,3,'aaaaa') AS 'demo2'
FROM DUAL;
1
2

---

2.3 REPLACE(str, a, b) 字符串的替换

REPLACE(str,a,b) 将字符串 str 中的所有的字符串 a 替换为 字符串 b 返回，当字符串 str 中不存在 字符串 a 时，不发生替换

SELECT REPLACE('hello','l','A') AS 'demo', REPLACE('hello','ll','A') AS 'demo2', REPLACE('hello','kk','AA') AS 'dem03'
FROM DUAL;
1
2

---

2.4 字符串的大小写转换

UPPER(s) 或 UCASE(s) 将字符串s 中所有的英文字母转为 大写 的英文字母，注意 仅仅是英文字符，中文不支持

LOWER(s) 或LCASE(s) 将字符串s 中所有的英文字母转为 小写 的英文字母，注意 仅仅是英文字符，中文不支持

SELECT UPPER('hello 你好'), UPPER('heLLo 你好'),UCASE('hello 你好')
FROM DUAL;
1
2

---

SELECT LOWER('HELLO, 你好'), LCASE('HELLO,你好')
FROM DUAL;
1
2

---

2.5 返回左右字符

LEFT(str,n) 返回字符串str 从最左边开始的n 个字符

RIGHT(str,n) 返回字符串 str 从最右边开始的 n 个字符

SELECT LEFT('hello',2), RIGHT('hello',2)
FROM DUAL;
1
2

---

2.6 字符串填充

LPAD(str, len, pad) 用字符串pad对str最左边进行填充，直到str的长度为len个字符

RPAD(str ,len, pad) 用字符串pad对str最右边进行填充，直到str的长度为len个字符

SELECT LPAD('hello',10,'*'), RPAD('hello',10,'*')
FROM DUAL;
1
2

---

2.7 字符串之间的比较

STRCMP(s1,s2) 比较字符串s1,字符串s2 两者之间的 ASCll 码值的大小，当前者字符串s1 =(等于) 后者字符串s2，则返回 0，当前者 s1 > 后者s2, 返回 1，当前者 s1 < 后者 s2, 返回 -1

NULLIF(value1,value2) 比较这两个字符串内容是否相同，如果这两个字符串内容相同，返回 NULL, 否则不同，返回 前者 value1字符串

SELECT STRCMP('hello','hello'), STRCMP('abc','abd'), STRCMP('abe','abc')
FROM DUAL;
1
2

---

SELECT NULLIF('hello','hello'), NULLIF('hello','world')
FROM DUAL;
1
2

---

2.8 SUBSTR(s,index,len) 获取字符串中对应下标的字符

SUBSTR(s,index,len) ：获取字符串s 中下标为 index开始其 len 个字符串，注意 : 字符串的起始位置的下标是 1，不是 0

SELECT SUBSTR('helloworld',5,5), SUBSTR('helloworld',0,10)
FROM DUAL;
1
2

---

2.9 LOCATE(substr,str) 查找字符其位置

LOCATE(substr,str) ，返回 substr 子串在字符串 str首次出现的位置的下标位置，如果该字符串中没有该子串的存在，返回 0

SELECT LOCATE('o','helloworld'), LOCATE('l','hello'), LOCATE('A','hello')
FROM DUAL;
1
2

---

3.日期和时间函数

用来进行日期时间操作的函数

3.1 获取日期和时间的函数

---

3.1.1 CUDATE(), CURRENT_DATE() 获取当前日期

SELECT CURDATE(), CURRENT_DATE()
FROM DUAL;
1
2

---

3.1.2 CURTIME(), CURRENT_TIME() 获取当前时间

SELECT CURTIME(), CURRENT_TIME()
FROM DUAL;
1
2

---

3.1.3 NOW() / SYSDATE() 获取当前日期和时间

SELECT NOW(), SYSDATE(),LOCALTIME()
FROM DUAL;
1
2

---

3.1.4 世界标准时间

SELECT UTC_DATE(), UTC_TIME()
FROM DUAL;
1
2

---

3.2 日期和时间戳的转换

---

3.2.1 返回当前的时间以时间戳的形式

SELECT UNIX_TIMESTAMP()
FROM DUAL;
1
2

---

3.2.2 将时间以时间戳的形式返回

SELECT UNIX_TIMESTAMP('2020-01-01'), UNIX_TIMESTAMP(NOW())
FROM DUAL;
1
2

---

3.2.3 将时间戳转换为普通时间

SELECT FROM_UNIXTIME(1577808000), FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()))
FROM DUAL;
1
2

---

3.3 获取月份，星期，天数等函数

---

3.3.1 返回具体日期 ，返回具体时间

SELECT YEAR(CURDATE()), MONTH(NOW()), DAY(NOW())
/*     年                 月           天     */
FROM DUAL;
1
2
3

---

3.3.2 返回英文化的月份，星期

SELECT MONTHNAME(NOW()), DAYNAME(NOW())
FROM DUAL;
1
2

---

3.3.3 返回日期的周期，和季度

SELECT WEEKDAY(NOW()), QUARTER(NOW())
FROM DUAL;
1
2

---

3.3.4 返回一年中的第几周，第几天

SELECT WEEK(NOW()), DAYOFYEAR(NOW())
FROM DUAL;
1
2

---

3.3.5 日期的万能取值

EXTRACT(type FROM date)函数中type的取值与含义：

---

SELECT EXTRACT(MONTH FROM NOW()), EXTRACT(YEAR_MONTH FROM NOW())
FROM DUAL;
1
2

---

3.4 时间和秒钟转换的函数

---

SELECT TIME_TO_SEC(NOW()), SEC_TO_TIME(39375308), SEC_TO_TIME(TIME_TO_SEC(NOW()))
FROM DUAL;
1
2

---

3.5 计算日期和时间的函数

上述函数中type的取值：

---

**DATE_ADD(datetime, INTERVAL expr type) ** 可以根据需要增加日期时间上的 年，月，日，小时，分钟，

格式如下：

DATE_ADD(时间, INTERVAL 所需增加的数值 增加的类型(年,月,小时))
当增加的数值为 -负数时，就是减少了
1
2

---

SELECT NOW(), DATE_ADD(NOW(),INTERVAL 10 YEAR), /* 增加一年*/
              DATE_ADD(NOW(),INTERVAL -2 YEAR)  /*减去一年*/
FROM DUAL;
1
2
3

---

SELECT NOW(), DATE_ADD(NOW(),INTERVAL 10 MINUTE), /*增加10分钟*/
              DATE_ADD(NOW(),INTERVAL -10 MINUTE)
FROM DUAL;
1
2
3

---

DATE_SUB(date,INTERVAL expr type) 可以根据需要，减少日期时间上的年，月，日，小时

格式如下：

DATE_SUB(减少的日期时间对象, INTERVAL, 所减少的数值 减少的类型(年,小时))
当 所减的数值是 负数时，就是增加了
1
2

SELECT NOW(), DATE_SUB(NOW(),INTERVAL -1 YEAR), DATE_SUB(NOW(),INTERVAL 1 YEAR), DATE_SUB(NOW(), INTERVAL 10 MINUTE)
FROM DUAL;
1
2

---

---

**3.5.1 日期时间上的加减 **

SELECT NOW(),ADDTIME(NOW(),'2:2:2'), ADDTIME(NOW(),10), SUBTIME(NOW(), 30),SUBTIME(NOW(),'9:9:9')
FROM DUAL;
1
2

---

SELECT NOW(), DATEDIFF(NOW(),'2021-7-31'), TIMEDIFF(NOW(), '2021-7-31 22:10:10')
FROM DUAL;
1
2

---

3.5.2 计算0000年的关系

SELECT NOW(), FROM_DAYS(366), TO_DAYS(NOW())
FROM DUAL;
1
2

---

3.6 日期的格式化与解析

日期的格式化 ： 日期——> 字符串

日期的解析 ：字符串——> 日期

---

上述非GET_FORMAT函数中fmt参数常用的格式符：

我们如果直接使用字符串类型表示日期时间，会自动转换为 日期时间类型进行比较，这是一种隐式类型的转换，与 Java语言中的类型之间的隐式转换是一个道理，如下：

SELECT hire_date
FROM employees
WHERE hire_date = '1993-01-13';
1
2
3

---

下面是 将字符串类型根据一定的格式， 显式 转换为 日期时间类型的函数

SELECT DATE_FORMAT('2022-7-31','%Y-%M-%D'), DATE_FORMAT('2022-7-31','%Y-%m-%d'),
       TIME_FORMAT('16:21:13','%H:%i:%S'), TIME_FORMAT('16:21:13','%H-%i-%S')
FROM DUAL;
1
2
3

---

SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'), DATE_FORMAT(NOW(),'%Y-%m-%d'), 
       TIME_FORMAT(CURTIME(),'%H:%i:%S'),TIME_FORMAT(NOW(),'%H:%i:%S')
FROM DUAL;
1
2
3

---

SELECT STR_TO_DATE('2021-October-25th 11:34:42 ','%Y-%M-%D %h:%i:%S')
FROM DUAL;
1
2

---

GET_FORMAT(date_type,format_type) 返回日期字符串的显示格式

GET_FORMAT函数中date_type和format_type参数取值如下：

---

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;
1
2

---

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
1
2

---

4.流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。

MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

---

4.1 IF(value,value1,value2)

它的使用方式与 Java中的三目运算符 ？ 相似

当 value 为真true 时，返回 value1, 为假false 时, 返回 value2, 但需要注意 当 value 为NULL，返回的也是 NULL

SELECT last_name, salary, IF(salary >= 8000, '高工资','低工资') AS 'demo'
FROM employees;
1
2

---

4.2 IFNULL(value1,value2)

格式：

IFNULL(数据,当数据为NULL的代替值)
1

如果数据为NULL，把后者这个数值当作 NULL使用

IFNULL是为了避免 NULL 只要参与运算，最终的结果一定是NULL,为了避免这个现象，需要使用 IFNULL函数

补助为NULL的时候，因为有些情况下，我们会把为NULL的数据计算上，比如：计算一个人的年薪时，需要月薪+提成，而有时候提成是 NULL,

SELECT last_name, salary*12 + IFNULL(commission_pct,0) AS '年薪'
FROM employees;
1
2

---

其实IFNULL 也可以看做是 IF中的特殊情况：使用 IF 也可以到底和IFNULL 一样的效果：如下

使用 IFNULL

SELECT last_name, commission_pct,IFNULL(commission_pct,0)
FROM employees;
1
2

---

使用IF

SELECT last_name, commission_pct, IF(commission_pct IS NOT NULL, commission_pct, 0)
FROM employees;
1
2

---

4.3 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END

CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END 其效果相当于是 Java中的 if()…else if()… , 格式如下：

CASE WHEN 当满足条件 THEN 返回结果
     WHEN 当满足条件 THEN 返回结果
     ELSE 其他的情况下 返回结果 (可以省略)
     END 结束
注意在 CASE WHEN ...THEN WHEN... THEN 语句中不可以使用逗号分隔开，不然语法上无法通过的
      当存在其他情况下，而没有使用 ELSE 其结果返回的是 NULL
1
2
3
4
5
6

SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '精英'
                               WHEN salary >= 10000 THEN '潜力股'
                               WHEN salary >= 8000 THEN '努力股'
                               ELSE '草根'
                               END AS 'details', department_id
FROM employees;
1
2
3
4
5
6

---

不使用 else 的情况如下

SELECT last_name, salary, CASE WHEN salary >= 15000 THEN '精英'
                               WHEN salary >= 10000 THEN '潜力股'
                               WHEN salary >= 8000 THEN '努力股'
                               END AS 'details', department_id
FROM employees;
1
2
3
4
5

---

CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN

CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 其作用效果与 Java中的 switch …case 类似多分支选择语句，同样的函数主要用于，一个字段属性，多种数值可能，格式如下：

CASE 字段值 WHEN 该字段值的其中一种值 THEN 返回结果
           WHEN 该字段值的其中一种值 THEN 返回结果
           ELSE 其他情况 返回结果(可以省略)
           END 结束
同样在CASE... WHEN... THEN 中不要加逗号分隔，不然会报错，语法上无法通过
当存在其他情况下，而没有使用 ELSE 其结果返回的是 NULL
1
2
3
4
5
6

如实例：查询部门号为 10，20，30 的员工信息，若部门号为 10，则打印其工资的1.1倍，
20 号部门打印其工资 1.2 倍，30 号部门，打印其工资1.3倍，其他部门，打印其工资 1.4倍

SELECT employee_id, last_name, department_id, salary,
       CASE department_id WHEN 10 THEN salary * 1.1
                          WHEN 20 THEN salary * 1.2
                          WHEN 30 THEN salary * 1.3
                          ELSE salary * 1.4
                          END AS 'datails'
FROM employees;
1
2
3
4
5
6
7

---

不使用 else 的情况如下

SELECT employee_id, last_name, department_id, salary,
       CASE department_id WHEN 10 THEN salary * 1.1
                          WHEN 20 THEN salary * 1.2
                          WHEN 30 THEN salary * 1.3
                          END AS 'datails'
FROM employees;
                    
1
2
3
4
5
6
7

---

5.加密解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。

---

6.1 MD5(str)、SHA(str) 加密

无论是 **MD5(str)**加密，还是 SHA(str) 加密，同一个内容(相同的内容)进行的加密，多次执行的结果中，每次生成的暗码都是一样的，在密码登入中，用于匹配是否正确，当生成的暗码内容一样是表示密码正确，相同的内容生成的暗码不同，那么就永远无法匹配正确了，也就无法登入了。

SELECT MD5('mysql'), MD5('mysql')
FROM DUAL;
1
2

---

SELECT SHA('hello'), SHA('hello')
FROM DUAL;
1
2

---

6.2 ENCODE(value,password_seed),DECODE(value,password_seed)

SELECT ENCODE('hello','world'),
       DECODE(ENCODE('hello','world'),'world'),
       ENCODE(1,2),
       DECODE(ENCODE(1,2),2)
FROM DUAL;
1
2
3
4
5

---

6.其他函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

---

6.1

SELECT VERSION(), CONNECTION_ID(),DATABASE(),SCHEMA(),USER()
FROM DUAL;
1
2

---

SELECT CHARSET('hello'), CHARSET('你好'),
       COLLATION('hello'),COLLATION('你好')
FROM DUAL;
1
2
3

---

6.2 IP地址

SELECT INET_ATON('192.168.1.100'), INET_NTOA(3232235876)
FROM DUAL;
1
2

---

6.3 字符编码的修改

SELECT CHARSET('hello'), CONVERT('hello' USING 'utf8mb4'),CHARSET(CONVERT('hello' USING 'utf8mb4'))
FROM DUAL;
1
2

---

6.4 测试表达式的执行效率

我们可以使用函数 BENCHMARK(n,expr) 将表达式expr重复执行n次 。用于测试MySQL处理expr表达式所耗费的时间

执行 1000 次

SELECT BENCHMARK(1000,NOW())
FROM DUAL;
1
2

---

执行 10000000 次

SELECT BENCHMARK(10000000,NOW())
FROM DUAL;
1
2

---

7. 有关单行函数实例练习

由于涉及到的篇幅过多，大家可以一步到 🔜🔜🔜 MySQL ——单行处理函数实例练习_ChinaRainbowSea的博客-CSDN博客

---

8. 扩展

SELECT abc 
FROM employees;
/* 属性没有用 单引号括起来，这样肯定是不行的，因为会把 abc当做一个字段的名字，
去 employees 表中找 abc 显然是没有该字段的，报错*/
1
2
3
4

---

加上单引号使用变成为 字面值, 就不是表示为字段了，而如果是字面值的话，会根据表的结构的列数显示

SELECT 'abc'
FROM employees;
1
2

---

或者是数值 1000 同样是字面值，会根据表的结构的列数，显示

SELECT 1000 
FROM employees;
1
2

---

8.最后

限于自身水平，其中存在的错误，希望大家给予指教，韩信点兵——多多益善，谢谢大家，后会有期，江湖再见！