兄弟连 Mysql 内置系统函数
查询语句或插入语句的时候，往往能会用的 mysql 系统内置的函数，
丰富的函数使工作事半功倍，帮助我们做很多事情，比如字符串的处理，数值的运算，日期的运算等等，帮助开发人员编写简单快捷的 sql 语句
什么是内置函数？
这些函数可以用在 SELECT 语句，及它的子句 where、order by、having 等等
也可以用在 UPDATE、DELETE 这些语句以及它们的子句中
其中 Mysql 中常用的内置函数有
1. 字符串函数、2. 数值函数、3. 日期函数、还有之前的统计函数...
一、字符串函数
基本上 PHP 中用的字符串函数，大部分在 Mysql 中也提供了，
可以打开 Mysql 手册，左侧栏找到“函数和操作符”，可以看到字符串函数、数值函数、日期函数...
常用的一些字符串函数
| 函数 | 作用 |
| concat(str1, str2...strn) | 把传入的参数链接成一个字符串 |
| insert(str, x, y, insert) | 将字符串 str，从 x 的位置开始，y 个字符串长度替换为 字符串" insert" |
| lower(str) | 将传入的字符串转成小写 |
| upper(str) | 将传入的字符串转成大写 |
| left(str, x) | 返回字符串最左边的 x 字符， 返回字符串最右边的 x 字符， 如果第二个参数传 null，则返回 null |
| right(str, x) | |
| lpad(str, n, pad) l 是 left 的缩写 | 用字符串 pad，对字符串 str 最左边或最右边进行填充，直到长度为 n 个字符长度为止 |
| rpad(str, n, pad) r 是 rigth 的缩写 | |
| trim(str) | 去掉字符串两边空格 |
| ltrim(str) | 去左边空格 |
| rtrim(str) | 去掉右边的空格 |
| replace(str, a, b); | 用字符串 b 替换字符串 str 中所有出现的字符串 a |
| strcmp(str1, str2) | 比较函数是按 ACSII 码字节比较的，比较 str1 和 str2 的 ACSII 码值的大小 如果 str1 比 str2 小返回 -1 如果 str1 比str2 大返回 1 如果 str1 等于 str2 小返回 0 |
| substr(str, x, y) | 返回字符串 str 中第 x 的位置开始，y 个字符串长度 |
concat() 字符串链接
将字符串 'abc' 链接 'cfg'
select concat('abc', 'efg');+----------------------+
| concat('abc', 'efg') |
+----------------------+
| abcefg | 两个字符串连接了
+----------------------+
在链接第三个字符串 "kil"
select concat('abc', 'efg', "kil");+-----------------------------+
| concat('abc', 'efg', "kil") |
+-----------------------------+
| abcefgkil |
+-----------------------------+
在链接第四个 "hello"
select concat('abc', 'efg', "kil", "hello");+--------------------------------------+
| concat('abc', 'efg', "kil", "hello") |
+--------------------------------------+
| abcefgkilhello | 在 select 语句中用 concat() 函数里，把所有的参数链接在一起
+--------------------------------------+
也可以在 concat() 函数里面直接用变量，对于表来说“列”就是变量，
比如创建一个 user 用户表，并插入两条数据
create table users( id int, name char(30), age tinyint ); insert into users values(1, '张三', 40),(2, '李四', 39); select * from users;
+------+------+------+
| id | name| age |
+------+------+------+
| 1 | 张三 | 40 |
| 2 | 李四 | 39 |
+------+------+------+
上面 concat() 函数里面写的是字符串，
如果直接里写的是字段名 concat(name, age) 那么变量的值就是这个列对应的记录
select concat(name, "年龄是：", age) from users;
+-------------------------------+
| concat(name, "年龄是：", age) |
+-------------------------------+
| 张三年龄是：40 |
| 李四年龄是：39 |
+-------------------------------+
对于数据表字段就是变量（variables）
select concat(name, '的年龄是', age) from users;
函数中可以将列（字段）作为变量来用，变量的值就是这个列对应的每一行记录
也可以在 where 字句中和 order by 字句中用等
insert( str, x, y, insert ) 替换
作用将字符串 str，从 x 开始的位置，y 个字符串长度的字符替换为 insert
select insert("abcdefg", 2, 3, 'hellow');从第 2 个 b 开始（从 1 开始计数，1 是 a），
往后 3 个长度的字符 bcd
换成 "hellow"
+-----------------------------------+
| insert("abcdefg", 2, 3, 'hellow') |
+-----------------------------------+
| ahellowefg |
+-----------------------------------+
修改一下表的内容
update users set name = '张三大兄弟' where id = 1; update users set name = '李四大兄弟' where id = 2; select * from users;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1 | 张三大兄弟 | 40 |
| 2 | 李四大兄弟 | 39 |
+------+------------+------+
中文字符也是可以替换，
将名字的第 3 个字符后面的 3 个字符换成“是中国人”
select insert(name, 3, 3, "是中国人") from users;
"张三大兄弟" 这三个字符换了
第3个字符是大字，网红3个是大兄弟，然后替换为“是中国人”
+--------------------------------+
| insert(name, 3, 3, "是中国人") |
+--------------------------------+
| 张三是中国人 |
| 李四是中国人 |
+--------------------------------+
对比原来没有换的名字
select insert(name, 3, 3, "是中国人"), name from users;
+--------------------------------+------------+
| insert(name, 3, 3, "是中国人") | name |
+--------------------------------+------------+
| 张三是中国人 | 张三大兄弟 |
| 李四是中国人 | 李四大兄弟 |
+--------------------------------+------------+
lower() 将字符串转成小写
upper() 将字符串转成大写
select lower("HELLO"), upper('hello');+----------------+----------------+
| lower("HELLO") | upper('hello') |
+----------------+----------------+
| hello | HELLO |
+----------------+----------------+
插入 name 字段小写英文 monica
insert into users values(3, 'maria', 37); select * from users;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1 | 张三大兄弟 | 40 |
| 2 | 李四大兄弟 | 39 |
| 3 | maria | 37 |
+------+------------+------+
查询 name 字段等于 maria 的，将名字转成大写的 MARIA 在等于大写的 MARIA
select * from users where upper(name) = 'MARIA';
不管 maria 大写还是小写，查询时候都换成大写的就查询出来了
+------+--------+------+
| id | name | age |
+------+--------+------+
| 3 | maria | 37 |
+------+--------+------+
left( str, x )
right( str, x )
截取
返回字符串最左边和最右边的 x 个字符，如果第二个参数为 null 空，则不返回任何字符
select left('abcdefg', 3), right('abcdefg', 3);+--------------------+---------------------+
| left('abcdefg', 3) | right('abcdefg', 3) |
+--------------------+---------------------+
| abc | efg |
+--------------------+---------------------+
如果第二个参数 x 传的是空 null，则返回的就是 null
select left('abcdefg', 3), right('abcdefg', 3), left('abcdefg', null);+--------------------+---------------------+-----------------------+
| left('abcdefg', 3) | right('abcdefg', 3) | left('abcdefg', null) |
+--------------------+---------------------+-----------------------+
| abc | efg | NULL |
+--------------------+---------------------+-----------------------+
lpad( str, n, pad ) l 是 left 的缩写
rpad( str, n, pad ) r 是 rigth 的缩写
用字符串 pad 对 str 最左边和最右边进行填充，直到个长度为 n 个字符长度为止
查看 users 表
select name from users;
+------------+
| name |
+------------+
| 张三大兄弟 |
| 李四大兄弟 |
| maria |
+------------+
比如把 name 字段都补足 10 个字符的长度
左边用 # 号补充
右边用 @ 号补充
select lpad(name, 10, '*'), rpad(name, 10, '@') from users;
+---------------------+---------------------+
| lpad(name, 10, '*') | rpad(name, 10, '@') |
+---------------------+---------------------+
| *****张三大兄弟 | 张三大兄弟@@@@@ |
| *****李四大兄弟 | 李四大兄弟@@@@@ |
| *****maria | maria@@@@@ |
+---------------------+---------------------+
trim(str) 去掉字符串两边空格
ltrim(str) 去左边空格
rtrim(str) 去掉右边的空格
select trim(" abc "), ltrim(" abc "), rtrim(" abc ");这样有点看不出来
+-------------------+--------------------+--------------------+
| trim(" abc ") | ltrim(" abc ") | rtrim(" abc ") |
+-------------------+--------------------+--------------------+
| abc | abc | abc |
+-------------------+--------------------+--------------------+
用函数 concat() 在两边链接 # 在查询，
使用 \G 立起来查看，不然字段名称太长了
select concat("#", trim(" abc "), "#"), concat("#", ltrim(" abc "), "#"), concat("#", rtrim(" abc "), "#") \G*************************** 1. row ***************************
concat("#", trim(" abc "), "#"): #abc#
concat("#", ltrim(" abc "), "#"): #abc #
concat("#", rtrim(" abc "), "#"): # abc#
给字段起一个别名，看的更清晰
select concat("#", trim(" abc "), "#") one, concat("#", ltrim(" abc "), "#") two, concat("#", rtrim(" abc "), "#") three;+-------+----------+----------+
| one | two | three |
+-------+----------+----------+
| #abc# | #abc # | # abc# |
+-------+----------+----------+
replace( str, a, b )
将字符串 str 中所有 "a" 都替换成 "-"
select replace("希望a世界更好a所有的不如意a都消散", "a", " - ");+----------------------------------------------------------+
| replace("希望a世界更好a所有的不如意a都消散", "a", " - ") |
+----------------------------------------------------------+
| 希望 - 世界更好 - 所有的不如意 - 都消散 |
+----------------------------------------------------------+
strcmp(str1, str2)
字符串比较函数，是按 ACSII 码字节比较的
比较的是字符串 str1 和 str2 它们 ACSII 码的大小
如果 str1 比 str2 小，则返回 -1
如果 str1 比 str2 大，则返回 1
如果 str1 等于 str2 ，则返回 0
select strcmp("a", "b"), strcmp("b", "a"), strcmp("a", "a");+------------------+------------------+------------------+
| strcmp("a", "b") | strcmp("b", "a") | strcmp("a", "a") |
+------------------+------------------+------------------+
| -1 | 1 | 0 |
+------------------+------------------+------------------+
substr(str, 3, 9)
截取字符串，从第 3 个字符截取，截取 9 个字符
select substr("学习Mysql，学习了两三部教程后，感觉入门了", 3, 9);+-----------------------------------------------------------+
| substr("学习Mysql，学习了两三部教程后，感觉入门了", 3, 9) |
+-----------------------------------------------------------+
| Mysql，学习了 |
+-----------------------------------------------------------+
二、数值函数
mysql 数值函数能处理很多数值方面的运算问题
| 函数 | 作用 |
| abs(x) | 返回 x 的绝对值 |
| ceil(x) | 返回大于 x 的最小整数， 比如 2.1、2.5、2.9 返回的都是 3，进 1 取整法 |
| floor(x) | 返回小于 x 的最大整数， 比如 2.1、2.5、2.9 返回的都是 2，割舍法 |
| mod(x, y) | 返回 x / y 的模（就是余数） |
| rand() | 返回 0~1 之间的随机数 |
| round(x, y) | 四舍五入函数， 返回参数 x 的四舍五入，有 y 位小数的值 |
| truncate(x, y) | 截断的函数， 返回数字 x，截断为 y 位小数的结果 |
abs()
返回绝对值
select abs(10), abs(-10);
正数 10 的绝对值本身就是 10
负数 10 返回的是直接去掉负号的绝对值
+---------+----------+
| abs(10) | abs(-10) |
+---------+----------+
| 10 | 10 |
+---------+----------+
比如年龄取一个正数（当然年龄没有负数）
select abs(age) from users;
+----------+
| abs(age) |
+----------+
| 40 |
| 39 |
| 37 |
+----------+
ceil() 向上取整
floor() 向下取整
对比一下
ceil 返回的是进 1 取整法
floor 是割舍取整法
select ceil(2.1), ceil(2.5), ceil(2.9), floor(2.1), floor(2.5), floor(2.9);
+-----------+-----------+-----------+------------+------------+------------+
| ceil(2.1) | ceil(2.5) | ceil(2.9) | floor(2.1) | floor(2.5) | floor(2.9) |
+-----------+-----------+-----------+------------+------------+------------+
| 3 | 3 | 3 | 2 | 2 | 2 |
+-----------+-----------+-----------+------------+------------+------------+
mod()
求模函数
10 % 4 模的结果是 2
select mod(10, 4);
+------------+
| mod(10, 4) |
+------------+
| 2 |
+------------+
10 % 5 模的结果是 0
select mod(10, 5);
+------------+
| mod(10, 5)|
+------------+
| 0 |
+------------+
如果前面的数小
1 % 11 模的结果是 1
select mod(1, 11);
+------------+
| mod(1, 11)|
+------------+
| 1 |
+------------+
如果前面是 null，返回结果也是空
select mod(null, 11);
+---------------+
| mod(null, 11) |
+---------------+
| NULL |
+---------------+
rand()
随机数函数
返回 0 ~ 1 之间的随机数，是浮动数的随机，每次的值都是不一样的
select rand();
+--------------------+
| rand() |
+--------------------+
| 0.9642467442566964 |
+--------------------+
想要 1 ~ 100 之间的随机数怎么办呢？
1. rend() 乘以 100
2. 在用 ceil 或 floor 取整
select ceil(rand()*100);
+------------------+
| ceil(rand()*100) |
+------------------+
| 33 |
+------------------+
round(x, y)
四舍五入函数，返回参数 x 的四舍五入，有 y 位小数的值
不加第二个参数 y，浮点数 1.1 四舍五入返回 1
select round(1.1);
+------------+
| round(1.1) |
+------------+
| 1 |
+------------+
1.5 四舍五入返回 2
select round(1.5);
+------------+
| round(1.5) |
+------------+
| 2 |
+------------+
1.4 四舍五入返回 1
select round(1.4);
+------------+
| round(1.4) |
+------------+
| 1 |
+------------+
把第二个参数加上
浮点数 1.489 第二个参数是 2，返回 1.49 小数点后是 2 位的
select round(1.489, 2);
+-----------------+
| round(1.489, 2) |
+-----------------+
| 1.49 |
+-----------------+
truncate(x, y)
截断函数，返回数字 x，截断为 y 位小数的结果
参数数一 12.35
参数数二 2
truncate() 函数和 round() 四舍五入函数的对比
select truncate(1.235, 2), round(1.235, 2);
truncate() 小数超过两位直接截断返回 1.23
round() 也是截断，如果后面的数超过 5 或是 5 就进一位返回 1.24
+--------------------+-----------------+
| truncate(1.235, 2) | round(1.235, 2) |
+--------------------+-----------------+
| 1.23 | 1.24 |
+--------------------+-----------------+
三、日期函数
有时候会遇到这样的需求
当前时间是多少？
下个月的今天是星期几？
统计截止到当前日期前三天的收入总和等等……
这些需求都需要时间日期函数来实现，
当然我们可以用 PHP 的时间戳来完成，时间戳是秒数，比如三天前怎么办呢，用当前时间戳减去 3 天的秒数就是三天前
mysql 数据库的时间日期函数
| 函数 | 作用 |
| curdate() | 返回当前的日期 |
| curtime() | 返回当前的时间 |
| now() | 返回当前日期时间 |
| unix_timestamp(now()) | 返回unix时间戳 |
| from_unixtime(1632130879) | 返回unix时间戳日期的值 |
| week(now()) | 通过时间返回当前的周，一年的第几周 |
| year(now()) | 通过时间返回的的年 |
| hour(now()) | 通过时间返回当前的小时 |
| minute(now()) | |
| date_format(now(), "%Y-%m-%d %H:%i:%s") | 格式化日期时间 |
curdate()
返回当前的日期
select curdate();
+------------+
| curdate() |
+------------+
| 2021-09-20|
+------------+
curtime()
返回当前的时间
select curtime();
+-----------+
| curtime() |
+-----------+
| 17:16:47 |
+-----------+
now()
现在的时间，返回既有日期又有时间
select now();
+---------------------+
| now() |
+---------------------+
| 2021-09-20 17:39:50 |
+---------------------+
unix_timestamp()
返回 unix 时间戳，比如根据当前的时间返回 unix 时间戳
select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1632130879 | 这是从 1971年 1 月 1 日 0 时 0 分 0 秒 到当前的秒数，也就是计算机元年到现在的秒数
+-----------------------+
from_unixtime()
返回 unix 时间戳对应的日期时间
select from_unixtime(1632130879);
+---------------------------+
| from_unixtime(1632130879) |
+---------------------------+
| 2021-09-20 17:41:19 | 返回的是时间戳 1632130879 对应的日期时间
+---------------------------+
week()
year()
hour()
从当前时间戳里面返回周、年、小时（从当前时间 now() 里返回）
select week(now()), year(now()), hour(now());
+-------------+-------------+-------------+
| week(now()) | year(now()) | hour(now()) |
+-------------+-------------+-------------+
| 38 | 2021 | 18 |
+-------------+-------------+-------------+
hour()
minute()
second()
上面的 curtime() 是当前时间，从当前时间里面获取当前的小时、分钟、秒
select hour(curtime()) '小时', minute(curtime()) '分钟', second(curtime()) '秒';
+------+------+------+
| 小时 | 分钟 | 秒 |
+------+------+------+
| 15 | 23 | 25 |
+------+------+------+
monthname()
从现在日期 now() 中返回当前英文的月份
select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| September |
+------------------+
date_format()
自定义的日期格式，将当前的日期 now() 格式化
select date_format(now(), "%Y-%m-%d %H:%i:%s");
+-----------------------------------------+
| date_format(now(), "%Y-%m-%d %H:%i:%s") |
+-----------------------------------------+
| 2021-09-20 18:12:02 |
+-----------------------------------------+
四、流程控制函数
流程控制函数也是很常用到的一类函数，
用户可以使用这类函数，在一个 sql 语句中实现条件选择，这样就能做到提高 sql 语句的效率
先创建一个薪水的表
create table salary( id int not null, salary decimal(9, 2) ); insert into salary values(1, 1000),(2, 2000),(3, 3000),(4, 4000),(5, 5000),(6, null); select id, salary from salary;
+----+---------+
| id | salary |
+----+---------+
| 1 | 1000.00 |
| 2 | 2000.00 |
| 3 | 3000.00 |
| 4 | 4000.00 |
| 5 | 5000.00 |
| 6 | NULL |
+----+---------+
一些流程控制函数（流程控制函数还有很多）
| 函数名 | 作用 |
| if(value, t, f) | 如果 value 值是真返回 t，如果是假返回 f |
| ifnull(value1, value2) | 如果 value1 不为空就返回 value1，否则返回 value2 |
| case when [value1] then [result1]...else[default] end | 跟程序里的 switch case 一样， 意思是如果 value1 是真返回 result1，否则返回缺省值 default |
if(value, t, f)
月薪在 3000 元以上的用 hight 表示，2000 以下的用 low 表示
select id, salary, if(salary > 3000, 'hight', 'low') from salary;
+----+---------+-----------------------------------+
| id | salary | if(salary > 3000, 'hight', 'low') |
+----+---------+-----------------------------------+
| 1 | 1000.00 | low |
| 2 | 2000.00 | low |
| 3 | 3000.00 | low |
| 4 | 4000.00 | hight |
| 5 | 5000.00 | hight |
| 6 | NULL | low |
+----+---------+-----------------------------------+
ifnull( value1, value2 )
如果薪水不为空 null 就返回薪水，如果薪水为空就返回 0
select id, salary, ifnull(salary, 0) from salary;
+----+---------+-------------------+
| id | salary | ifnull(salary, 0) |
+----+---------+-------------------+
| 1 | 1000.00 | 1000.00 |
| 2 | 2000.00 | 2000.00 |
| 3 | 3000.00 | 3000.00 |
| 4 | 4000.00 | 4000.00 |
| 5 | 5000.00 | 5000.00 |
| 6 | NULL | 0.00 |
+----+---------+-------------------+
这个函数是替换空 null 值的，
因为 null 值是不能参与数值运算的，所以可以通过这个函数把 null 值转为 0 来代替
case when [value1] then [result1]...else[default] end
select case when salary<=3000 then '薪水小于等于三千' else '高薪水' end from salary;
+------------------------------------------------------------------+
| case when salary<=3000 then '薪水小于等于三千' else '高薪水' end |
+------------------------------------------------------------------+
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 高薪水 |
| 高薪水 |
| 高薪水 |
+------------------------------------------------------------------+
五、其它函数
其它的一些函数
| 函数名 | 用途 |
| database() | 当前的数据库名 |
| version() | 当前 Mysql 的版本 |
| user() | 当前的数据库用户 |
| inet_aton(ip) | 返回 ip 的数字表示 |
| inet_ntoa() | |
| md5() |
查看当前的数据库名（其实 \s 看状态也可以）
select database();
+------------+
| database() |
+------------+
| dbshop |
+------------+
查看当前的数据库版本
select version();
+------------------+
| version() |
+------------------+
| 5.7.18-cynos-log|
+------------------+
查看当前登录的用户
select user();
+------------------+
| user() |
+------------------+
| root@localhost | root 用户在本地登录的
+------------------+
返回 ip 地址的网络字节顺序
select inet_aton("192.168.1.1");+--------------------------+
| inet_aton("192.168.1.1")|
+--------------------------+
| 3232235777 |
+--------------------------+
返回网络字节序列 3232235777 代表的 ip 地址
select inet_ntoa(3232235777);
+-----------------------+
| inet_ntoa(3232235777)|
+-----------------------+
| 192.168.1.1 |
+-----------------------+
inet_ntoa() 与 inet_aton() 是互逆的，
inet_aton() 主要用途是将字符串的 ip 地址，转为数字表示网络字节顺序，这样更方便的进行 ip 地址或者网络端的比较
password(str) 将字符串加密
select password('123456');+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
大概是 41 位长的字符串
其实 password() 函数是设置 Mysql 系统里面的用户密码，不要对应用的数据加密，
password() 函数是给 Mysql 本身系统用户用的，比如给 Mysql 用户设置密码就要用 password() 加密
如果应用方面加密要使用 md5() 函数，比如网站的用户密码加密
select md5('123456');+----------------------------------+
| md5('123456') |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
查看 Mysql 本事用户表的加密方式是 passwork() 加密
select * from mysql.user; select * from mysql.user \G