兄弟连 Mysql 内置系统函数
丰富的函数往往能使工作事半功倍，函数能帮助我们做很多事情，
比如字符串的处理，数值的运算，日期的运算等等
在这方面 Mysql 提供了多种内置函数，帮助开发人员编写简单快捷的 sql 语句
其中Mysql中常用的内置函数有三类
1. 字符串函数
2. 数值函数
3. 日期函数
这些函数可以用在 SELECT 语句，及它的子句 where、order by、having 等等
也可以用在 UPDATE、DELETE 这些语句以及他们的子句中
一、什么是内置函数？
函数可以直接在 select 语句里面用，
比如字符串的链接函数 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(name, age) 函数里面写字段名，整列都作为变量
select concat(name, "年龄是：", age) from users;
+-------------------------------+
| concat(name, "年龄是：", age) |
+-------------------------------+
| 张三年龄是：40 |
| 李四年龄是：39 |
+-------------------------------+
函数中可以将“字段名”作为“变量”来用，变量的值就是这个列对应的每一行记录
函数也可以在 where 字句，ordre by 字句中都可以用
基本上 PHP 中用的函数，Mysql 中大部分也提供了
Mysql手册 -> 函数和操作符 -> 字符串函数/数值函数/日期函数
二、字符串函数
字符串函数是最常用的一种函数
| 函数 | 作用 |
| concat(str1, str2...strn) | 把传入的参数链接成一个字符串 |
| insert(str, x, y, insert) | 将字符串str，从x的位置开始，y个字符串长度的字符串，替换为"字符串insert" |
| lower(str) | 将传入的字符串转成小写 |
| upper(str) | 将传入的字符串转成大写 |
| left(str, x) | 返回字符串最左边的x字符 |
| right(str, x) | 返回字符串最右边的x字符 |
| 如果第二个参数传null，则返回null | |
| lpad(str, n, pad) | l 是 left 的缩写 |
| rpad(str, n, pad) | r 是 rigth 的缩写 |
| 用字符串pad，对字符串st对左边或最右边进行填充，直到长度为n个字符长度为止 | |
| 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个字符串长度的字符串 |
insert( str, x, y, insert )
作用将字符串 str，从 x 开始的位置，y 个字符串长度，替换为字符串 insert
select insert("abcdefg", 2, 3, 'hellow');从第 2 个b 开始（默认从 1 开始），
往后三个字符的长度，换成 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;
大兄弟 这三个字符换了
+--------------------------------+
| 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, 'monica', 37); select * from users;
+------+------------+------+
| id | name | age |
+------+------------+------+
| 1 | 张三大兄弟 | 40 |
| 2 | 李四大兄弟 | 39 |
| 3 | monica | 37 |
+------+------------+------+
查询大写的 MONICA，将 name 字段转成大写的
select * from users where upper(name) = 'MONICA';
换成大写的查询出来了
+------+--------+------+
| id | name | age |
+------+--------+------+
| 3 | monica | 37 |
+------+--------+------+
对于数据表字段就是变量（variables）
select concat(name, '的年龄是', age) from users;
函数中可以将列（字段名）作为变量来用，变量的值就是这个列对应的每一行记录
也可以在where字句中和order by字句中用等
left(str, x)
right(str, x)
返回字符串最左边或最右边的x字符，如果第二个参数为null，则什么都不返回
select left('abcdefg', 3), right('abcdefg', 3);+--------------------+---------------------+
| left('abcdefg', 3) | right('abcdefg', 3) |
+--------------------+---------------------+
| abc | efg |
+--------------------+---------------------+
如果最后一个参数是null，以left()为例第二个参数传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 |
+------------+
| 张三大兄弟 |
| 李四大兄弟 |
| monica |
+------------+
把name字段都补足10个字符长度，左边用"#"号补充，然后右边用"@"号补充
select lpad(name, 10, '*'), rpad(name, 10, '@') from users;
+---------------------+---------------------+
| lpad(name, 10, '*') | rpad(name, 10, '@') |
+---------------------+---------------------+
| *****张三大兄弟 | 张三大兄弟@@@@@ |
| *****李四大兄弟 | 李四大兄弟@@@@@ |
| ****monica | monica@@@@ |
+---------------------+---------------------+
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，学习了 |
+-----------------------------------------------------------+
三、数值函数
| 1 | abs(x) | 返回x的绝对值 |
| 2 | ceil(x) |
返回大于x的最小整数， 比如 2.1, 2.5, 2.9返回的都是3， 进一取整法 |
| floor(x) | 返回小于x的最大整数， 比如 2.1, 2.5, 2.9返回的都是2， 割舍法 | |
| 3 | mod(x, y) | 返回x/y的模 |
| 4 | rand() | 返回0~1之间的随机数 |
| 5 | round(x, y) | 四舍五入函数，返回参数x的四舍五入的，有y位小数的值 |
| 6 | truncate(x, y) | 截断的函数，返回数字x，截断为y位小数的结果 |
abs() 返回绝对值函数
select abs(10), abs(-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模的结果是
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位小数的值
不加第二个参数，浮点数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) 截断函数
第一个参数：12.35
第二个参数：2
truncate()函数和round()四舍五入函数对比
select truncate(1.235, 2), round(1.235, 2);
round()截断函数，小数超过两位直接截断，返回1.23
truncate()四舍五入函数，如果超过5或是5就进一位，返回1.24
+--------------------+-----------------+
| truncate(1.235, 2) | round(1.235, 2) |
+--------------------+-----------------+
| 1.23 | 1.24 |
+--------------------+-----------------+
四、日期函数
有时候会遇到这样的需求：
当前时间是多少？
下个月的今天是星期几？
统计截止到当前日期前三天的收入总和等等……
这些需求都需要时间日期函数来实现
当然我们可以用PHP的时间戳来完成，比如三天前，用当前时间戳减去3天的秒数
数据库里面的时间日期函数
| 1 | curdate() | 返回当前的日期 |
| 2 | curtime() | 返回当前的时间 |
| 3 | now() | 返回当前日期时间 |
| 4 | unix_timestamp(now()) | 返回unix时间戳 |
| 5 | from_unixtime(1632130879) | 返回unix时间戳日期的值 |
| 6 | week(now()) | 通过时间返回当前的周，一年的第几周 |
| 7 | year(now()) | 通过时间返回的的年 |
| 8 | hour(now()) | 通过时间返回当前的小时 |
| 9 | minute(now()) | |
| 10 | date_format(now(), "%Y-%m-%d %H:%i:%s") | 格式化日期时间 |
返回当前的日期
select curdate();
+------------+
| curdate() |
+------------+
| 2021-09-20|
+------------+
返回当前的时间
select curtime();
+-----------+
| curtime() |
+-----------+
| 17:16:47 |
+-----------+
返回既有日期又有时间
select now();
+---------------------+
| now() |
+---------------------+
| 2021-09-20 17:39:50 |
+---------------------+
返回unix时间戳
select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1632130879 |
+-----------------------+
返回unix时间戳中，对应的日期时间
select from_unixtime(1632130879);
返回1632130879对应的日期时间
+---------------------------+
| from_unixtime(1632130879) |
+---------------------------+
| 2021-09-20 17:41:19 |
+---------------------------+
从当前时间戳里面返回周，年，小时，是从当前时间now()里返回
select week(now()), year(now()), hour(now());
+-------------+-------------+-------------+
| week(now()) | year(now()) | hour(now()) |
+-------------+-------------+-------------+
| 38 | 2021 | 18 |
+-------------+-------------+-------------+
从时间curtime()里面获取当前的小时，分钟，秒
select hour(curtime()) '小时', minute(curtime()) '分钟', second(curtime()) '秒';
+------+------+------+
| 小时 | 分钟 | 秒 |
+------+------+------+
| 15 | 23 | 25 |
+------+------+------+
monthname()返回当前英文的月份
select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| September |
+------------------+
自定义日期格式，将当前的日期格式化
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语句中实现条件选择，这样就能做到提高语句的效率
先创建一个薪水表
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 |
+----+---------+
流程控制函数：
| 1 | if(value, t, f) | 如果value值是真返回t，如果是假返回f |
| 2 | ifnull(value1, value2) | 如果value1不为空就返回value1，否则返回value2 |
| 3 | 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 |
+------------------------------------------------------------------+
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 薪水小于等于三千 |
| 高薪水 |
| 高薪水 |
| 高薪水 |
+------------------------------------------------------------------+
六、其它函数
| 1 | database() | 当前的数据库名 |
| 2 | version() | 当前Mysql的版本 |
| 3 | user() | 当前的数据库用户 |
| 4 | inet_aton(ip) | 返回ip的数字表示 |
| 5 | inet_ntoa() | |
| 6 | md5() |
查看当前的数据库名
select database();
+------------+
| database() |
+------------+
| dbshop |
+------------+
其实 \s 看状态也可以
查看当前的数据库版本
select version();
+------------------+
| version() |
+------------------+
| 5.7.18-cynos-log|
+------------------+
查看当前的用户
select user();
root用户在本地登录的
+------------------+
| user() |
+------------------+
| root@localhost |
+------------------+
返回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()是互逆的，
主要用途是将字符串的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