前言
环境:
- Window11
- MySQL-8.0.35
1.字符串函数
最常用的一种函数。
注意:在 MySQL 中,字符串的下标(或称为索引)是从 1 开始的,而不是从 0 开始。
函数 | 功能 |
---|---|
CONCAT(s1, s2, …, sn) | 连接s1, s2, …, sn 为一个字符串 |
INSERT(str, x, y, instr) | 将字符串str从第x位置开始,y个字符长的子串替换为字符串instr |
LOWER(str) | 将字符串str中的所有字符转换为小写 |
UPPER(str) | 将字符串str中的所有字符转换为大写 |
LEFT(str, x) | 返回字符串str最左边的x个字符 |
RIGHT(str, x) | 返回字符串str最右边的x个字符 |
LPAD(str, n, pad) | 用字符串pad对str最左边进行填充,直到长度为n个字符长度 |
RPAD(str, n, pad) | 用字符串pad对str最右边进行填充,直到长度为n个字符长度 |
LTRIM(str) | 去掉字符串str左侧的空格 |
RTRIM(str) | 去掉字符串str右侧的空格 |
REPEAT(str, x) | 返回str重复x次的结果 |
REPLACE(str, a, b) | 用字符串b替换字符串str中所有出现的字符串a |
STRCMP(s1, s2) | 比较字符串s1和s2 |
TRIM(str) | 去掉字符串行尾和行头的空格 |
SUBSTRING(str, x, y) | 返回从字符串str的x位置起y个字符长度的字符串 |
- CONCAT(s1, s2, …, sn)函数,把传入的字符串连接成为一个字符串。但是如果其中有NULL,结果就是NULL,因为任何字符串和NULL进行连接的结果都是NULL。
mysql> select concat('aa','bb','cc'), concat('aa','bb', null);
+------------------------+-------------------------+
| concat('aa','bb','cc') | concat('aa','bb', null) |
+------------------------+-------------------------+
| aabbcc | NULL |
+------------------------+-------------------------+
1 row in set (0.01 sec)
- INSERT(str, x, y, instr)函数,将字符串str从第x位置开始,y个字符长的字串替换为字符串instr。
// 第6个位置也就是 H 开始,5个字符的长度 Hello 替换为 MySQL
mysql> select insert('12345Hello', 6, 5, 'MySQL');
+-------------------------------------+
| insert('12345Hello', 6, 5, 'MySQL') |
+-------------------------------------+
| 12345MySQL |
+-------------------------------------+
1 row in set (0.01 sec)
- LOWER(str) 和 UPPER(str)函数,把字符串转换为小写或者大写。
mysql> select lower('我是大写转小写ABC'), upper('我是小写转大写abc');
+----------------------------+----------------------------+
| lower('我是大写转小写ABC') | upper('我是小写转大写abc') |
+----------------------------+----------------------------+
| 我是大写转小写abc | 我是小写转大写ABC |
+----------------------------+----------------------------+
1 row in set (0.00 sec)
- LEFT(str, x) 和 RIGTH(str, x)函数,分别是返回字符串最左边的x字符和最右边的x个字符,如果第二个参数为NULL,那么将不返回任何字符串,返回NULL。
mysql> select left('abcdef', 5), right('abcdef', 5), left('abcdef', null);
+-------------------+--------------------+----------------------+
| left('abcdef', 5) | right('abcdef', 5) | left('abcdef', null) |
+-------------------+--------------------+----------------------+
| abcde | bcdef | NULL |
+-------------------+--------------------+----------------------+
1 row in set (0.01 sec)
- LPAD(str, n, pad) 和 RPAD(str, n, pad)函数,分别用字符串pad 对 str 最左边和最右边进行填充,直到长度为n个字符长度。
mysql> select lpad('abc', 5, 00), lpad('abc', 5, 'hellomysql'), rpad('abc', 5, 0), rpad('abc', 5, 'hellomysql');
+--------------------+------------------------------+-------------------+------------------------------+
| lpad('abc', 5, 00) | lpad('abc', 5, 'hellomysql') | rpad('abc', 5, 0) | rpad('abc', 5, 'hellomysql') |
+--------------------+------------------------------+-------------------+------------------------------+
| 00abc | heabc | abc00 | abche |
+--------------------+------------------------------+-------------------+------------------------------+
1 row in set (0.01 sec)
- LTRIM(str) 和 RTRIM(str)函数,分别去掉字符串str左侧和右侧的空格。
mysql> select ltrim(' |abc| '), rtrim(' |abc| ');
+----------------------+----------------------+
| ltrim(' |abc| ') | rtrim(' |abc| ') |
+----------------------+----------------------+
| |abc| | |abc| |
+----------------------+----------------------+
1 row in set (0.00 sec)
- REPEAT(str, x)函数,返回str重复x次的结果。
mysql> select repeat('HelloMySQL==', 5);
+--------------------------------------------------------------+
| repeat('HelloMySQL==', 5) |
+--------------------------------------------------------------+
| HelloMySQL==HelloMySQL==HelloMySQL==HelloMySQL==HelloMySQL== |
+--------------------------------------------------------------+
1 row in set (0.00 sec)
- REPLACE(str, a, b)函数,用字符串b替换字符串str中所有出现的字符串a。
mysql> select replace('abbaaccaaadd', 'a', '==');
+------------------------------------+
| replace('abbaaccaaadd', 'a', '==') |
+------------------------------------+
| ==bb====cc======dd |
+------------------------------------+
1 row in set (0.00 sec)
- STRCMP(s1, s2)函数,比较字符串s1和s2的ASCII码值的大小,如果s1<s2,返回-1。如果s1=s2,返回0。如果s1>s2,返回1.
mysql> select strcmp('a','b'), strcmp('a','a'),strcmp('b','a');
+-----------------+-----------------+-----------------+
| strcmp('a','b') | strcmp('a','a') | strcmp('b','a') |
+-----------------+-----------------+-----------------+
| -1 | 0 | 1 |
+-----------------+-----------------+-----------------+
1 row in set (0.01 sec)
- SUBSTRING(str, x, y)函数,返回从字符串str中的第x位置起y个字符长度的字符串。
mysql> select substring('abcdefg', '2', 5), substring('你好我是张三', 3, 4);
+------------------------------+---------------------------------+
| substring('abcdefg', '2', 5) | substring('你好我是张三', 3, 4) |
+------------------------------+---------------------------------+
| bcdef | 我是张三 |
+------------------------------+---------------------------------+
1 row in set (0.00 sec)
2.数值函数
处理数值方面的运算。
函数 | 功能 |
---|---|
ABS(x) | 返回x的绝对值 |
CEIL(x) | 返回大于x的最小整数值 |
FLOOR(x) | 返回小于x的最大整数值 |
MOD(x, y) | 返回x/y的模, 等价于前面运算符章节中的% |
RAND() | 返回0~1内的随机值 |
ROUND(x, y) | 返回参数x的四舍五入的有y位小数的值 |
TRUNCATE(x, y) | 返回数字x截断为y位小数的结果 |
- ABS(x)函数,返回x的绝对值。
mysql> select abs(0.8), abs(-0.8);
+----------+-----------+
| abs(0.8) | abs(-0.8) |
+----------+-----------+
| 0.8 | 0.8 |
+----------+-----------+
1 row in set (0.00 sec)
- CEIL(x)函数,返回大于x的最小整数值。
mysql> select ceil(-0.8), ceil(-1.5), ceil(0.8), ceil(1.5);
+------------+------------+-----------+-----------+
| ceil(-0.8) | ceil(-1.5) | ceil(0.8) | ceil(1.5) |
+------------+------------+-----------+-----------+
| 0 | -1 | 1 | 2 |
+------------+------------+-----------+-----------+
1 row in set (0.01 sec)
- FLOOR(x)函数,返回小于x的最大整数值。和上面的CEIL正好相反。
mysql> select floor(-0.8), floor(-1.5), floor(0.8), floor(1.5);
+-------------+-------------+------------+------------+
| floor(-0.8) | floor(-1.5) | floor(0.8) | floor(1.5) |
+-------------+-------------+------------+------------+
| -1 | -2 | 0 | 1 |
+-------------+-------------+------------+------------+
1 row in set (0.00 sec)
- MOD(x, y)函数,返回 x/y 的模,等价于前面符号章节的%操作。x和y的值任何一个为NULL,结果就是NULL。
mysql> select 10%3, 10%null, 10 mod 3, mod(10,3), mod(null, 3);
+------+---------+----------+-----------+--------------+
| 10%3 | 10%null | 10 mod 3 | mod(10,3) | mod(null, 3) |
+------+---------+----------+-----------+--------------+
| 1 | NULL | 1 | 1 | NULL |
+------+---------+----------+-----------+--------------+
1 row in set (0.00 sec)
- RAND()函数,返回0~1的随机值。
mysql> select rand(), rand(), rand();
+--------------------+--------------------+--------------------+
| rand() | rand() | rand() |
+--------------------+--------------------+--------------------+
| 0.5018647653068088 | 0.8444691345509786 | 0.7167514075681115 |
+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
RAND函数可以取任意指定范围内的随机数,例如需要0~100之间的任意随机整数,可以如下操作:
mysql> select ceil(rand()*100), ceil(rand()*100), ceil(rand()*100);
+------------------+------------------+------------------+
| ceil(rand()*100) | ceil(rand()*100) | ceil(rand()*100) |
+------------------+------------------+------------------+
| 6 | 11 | 36 |
+------------------+------------------+------------------+
1 row in set (0.00 sec)
- ROUND(x, y)函数,返回参数x的四舍五入的有y位小数的值。如果是整数,将会保留y位数量的0。如果不写y,则默认y就是0,也就是将四舍五入后取整。
mysql> select round(1.456, 2), round(1.456), round(1.456, 4), round(1), round(1.456, 0), round(1,3);
+-----------------+--------------+-----------------+----------+-----------------+------------+
| round(1.456, 2) | round(1.456) | round(1.456, 4) | round(1) | round(1.456, 0) | round(1,3) |
+-----------------+--------------+-----------------+----------+-----------------+------------+
| 1.46 | 1 | 1.456 | 1 | 1 | 1 |
+-----------------+--------------+-----------------+----------+-----------------+------------+
1 row in set (0.00 sec)
- TRUNCATE(x, y)函数,返回数字x截断为y位小数的结果。注意和上面ROUND的区别,这里是截断不进行四舍五入操作。
mysql> select truncate(1.456, 2), truncate(1.456, 4), truncate(1, 2);
+--------------------+--------------------+----------------+
| truncate(1.456, 2) | truncate(1.456, 4) | truncate(1, 2) |
+--------------------+--------------------+----------------+
| 1.45 | 1.456 | 1 |
+--------------------+--------------------+----------------+
1 row in set (0.00 sec)
3.日期和时间函数
常用的日期函数如下:
函数 | 功能 |
---|---|
CURDATE() | 返回当前日期 |
CURTIME() | 返回当前时间 |
NOW() | 返回当前日期和时间 |
UNIX_TIMESTAMP(date) | 返回日期date的UNIX时间戳 |
FROM_UNIXTIME | 返回UNIX时间戳的日期值 |
WEEK(date) | 返回日期date为一年中的第几周 |
YEAR(date) | 返回日期date的年份 |
HOUR(time) | 返回时间time的小时值 |
MINUTE(time) | 返回时间time的分钟值 |
MONTH(date) | 返回日期date为一年中的第几个月 |
MONTHNAME(date) | 返回日期date的月份名 |
DATE_FORMAT(date, fmt) | 返回按照字符串fmt格式化日期date后的值 |
DATE_ADD(date, INTERVAL expr type) | 返回一个日期或时间值加上一个时间间隔的时间值 |
DATEDIFF(expr, expr2) | 返回起始时间expr和结束日期expr2之间的天数 |
- CURDATE()函数,返回当前日期,只包含 年、月、日。
- CURTIME()函数,返回当前时间,只包含 时、分、秒。
- NOW()函数,返回当前日期和时间,年、月、日、时、分、秒都包含。
mysql> select curdate(), curtime(), now();
+------------+-----------+---------------------+
| curdate() | curtime() | now() |
+------------+-----------+---------------------+
| 2024-04-24 | 16:00:26 | 2024-04-24 16:00:26 |
+------------+-----------+---------------------+
1 row in set (0.01 sec)
- UNIX_TIMESTAMP(date)函数,返回日期date的UNIX时间戳。
mysql> select unix_timestamp(now());
+-----------------------+
| unix_timestamp(now()) |
+-----------------------+
| 1713946688 |
+-----------------------+
1 row in set (0.01 sec)
- FROM_UNIXTIME(unixtime)函数,返回UNIXTIME时间戳的日期值,和上面的UNIX_TIMESTAMP(date)函数互为逆操作。
mysql> select unix_timestamp(now()), now();
+-----------------------+---------------------+
| unix_timestamp(now()) | now() |
+-----------------------+---------------------+
| 1713946819 | 2024-04-24 16:20:19 |
+-----------------------+---------------------+
1 row in set (0.00 sec)
// 对比上面unix_timestamp的值,互为逆操作
mysql> select from_unixtime(1713946819);
+---------------------------+
| from_unixtime(1713946819) |
+---------------------------+
| 2024-04-24 16:20:19 |
+---------------------------+
1 row in set (0.00 sec)
- WEEK(date) 和 YEAR(date)函数,分别返回参数date是一年中的第几周 和 哪一年。
mysql> select week(now()), year(now());
+-------------+-------------+
| week(now()) | year(now()) |
+-------------+-------------+
| 16 | 2024 |
+-------------+-------------+
1 row in set (0.00 sec)
- HOUR(time) 和 MINUTE(time)函数,分别返回参数time的小时 和 分钟。
mysql> select now(), hour(now()), minute(now());
+---------------------+-------------+---------------+
| now() | hour(now()) | minute(now()) |
+---------------------+-------------+---------------+
| 2024-04-24 16:23:27 | 16 | 23 |
+---------------------+-------------+---------------+
1 row in set (0.01 sec)
- MONTHNAME(date)函数,返回参数date的英文月份名称。
- MONTH(date)函数,返回参数date为一年中的第几个月。
mysql> select now(), monthname(now()), month(now());
+---------------------+------------------+--------------+
| now() | monthname(now()) | month(now()) |
+---------------------+------------------+--------------+
| 2024-04-24 16:25:18 | April | 4 |
+---------------------+------------------+--------------+
1 row in set (0.00 sec)
- DATE_FORMAT(date, fmt)函数,按照字符串fmt格式化日期date,fmt的格式符如下:
- %Y:四位数的年份
- %y:两位数的年份
- %m:两位数的月份(01-12)
- %c:月份(1-12)
- %d:两位数的日(01-31)
- %e:日(1-31)
- %H:两位数的小时(00-23)
- %h 或 %I:两位数的小时(01-12)
- %i:两位数的分钟(00-59)
- %s:两位数的秒(00-59)
- %p:AM 或 PM
- %r:时间,12 小时制(hh:mm:ss AM 或 PM)
- %T 或 %X:时间,24 小时制(hh:mm:ss)
- %k:小时(0-23)
- %l:小时(1-12)
- %W:星期几的完整名称(例如:Sunday)
- %a:星期几的缩写(例如:Sun)
- %j:一年的第几天(001-366)
- %U:周数(星期天为一周的开始,00-53)
- %u:周数(星期一为一周的开始,00-53)
- %V:周数(星期天为一周的开始,但第一周至少要有 4 天在这一年中,1-53)
- %v:周数(星期一为一周的开始,但第一周至少要有 4 天在这一年中,1-53)
- %D:美国格式的日期(月/日/年)
- %F:年-月-日格式的日期(YYYY-MM-DD)
mysql> select date_format(now(), '%Y-%m-%d %H:%i:%s'),date_format(now(), '%Y/%m/%d %H-%i-%s');
+-----------------------------------------+-----------------------------------------+
| date_format(now(), '%Y-%m-%d %H:%i:%s') | date_format(now(), '%Y/%m/%d %H-%i-%s') |
+-----------------------------------------+-----------------------------------------+
| 2024-04-24 16:31:36 | 2024/04/24 16-31-36 |
+-----------------------------------------+-----------------------------------------+
1 row in set (0.00 sec)
- DATE_ADD(date, INTERVAL expr type)函数,用于向日期值添加指定的时间间隔,INTERVAL是间隔类型的关键字,expr是表达式,要添加的时间间隔的数量或值,type表示间隔类型,如下:
expr间隔类型 | 描述 | 格式 |
---|---|---|
HOUR | 小时 | hh |
MINUTE | 分 | mm |
SECOND | 秒 | ss |
YEAR | 年 | YY |
MONTH | 月 | MM |
DAY | 日 | DD |
YEAR_MONTH | 年和月 | YY-MM |
DAY_HOUR | 日和小时 | DD hh |
DAY_MINUTE | 日和分钟 | DD hh:mm |
DAY_SECOND | 日和秒 | DD hh:mm:ss |
HOUR_MINUTE | 小时和分 | hh:mm |
HOUR_SECOND | 小时和秒 | hh:ss |
MINUTE_SECOND | 分钟和秒 | mm:ss |
// -1 day 可以用来表示减去日给定的日期
mysql> select now(), date_add(now(), interval 30 day), date_add(now(), interval 1 month), date_add(now(), interval -1 day);
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
| now() | date_add(now(), interval 30 day) | date_add(now(), interval 1 month) | date_add(now(), interval -1 day) |
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
| 2024-04-24 16:42:58 | 2024-05-24 16:42:58 | 2024-05-24 16:42:58 | 2024-04-23 16:42:58 |
+---------------------+----------------------------------+-----------------------------------+----------------------------------+
1 row in set (0.01 sec)
- DATEDIFF(date1, date2)函数,用来计算两个日期之间相差的天数。date1 - date2的,所以可能出现负数的差值,如下。
mysql> select datediff('2024-04-20',now()), now(), datediff(now(), '2024-04-20');
+------------------------------+---------------------+-------------------------------+
| datediff('2024-04-20',now()) | now() | datediff(now(), '2024-04-20') |
+------------------------------+---------------------+-------------------------------+
| -4 | 2024-04-24 16:45:34 | 4 |
+------------------------------+---------------------+-------------------------------+
1 row in set (0.00 sec)
4.流程函数
这类函数可以在SQL中实现条件选择,如下:
函数 | 功能 |
---|---|
IF(value, t, f) | 如果value是真,返回t,否则返回f |
IFNULL(value1, value2) | 如果value1不为空,返回value1,否则返回value2 |
CASE WHEN [expr] THEN [result] … ELSE [default] END | 如果expr表达式是真,返回result,否则返回default |
CASE [expr] WHEN [value] THEN [result] … ELSE [default] END | 如果expr等于value1,返回result,否则返回default |
这里测试示例我们模拟人员表的年龄进行分类,先创建一个person表,并插入一些数据,如下:
// 主键自增
mysql> create table person (
-> id int primary key auto_increment,
-> age int
-> );
Query OK, 0 rows affected (0.02 sec)
// 插入年龄数据
mysql> insert into person (age) values (10),(18),(20),(30),(50),(70),(90),(100),(null);
Query OK, 9 rows affected (0.01 sec)
Records: 9 Duplicates: 0 Warnings: 0
- IF(value, t, f)函数,这里认为年龄在60以上的属于高龄,用 “老人” 表示,而60以下的属于年轻人, 用”年轻人“表示。
mysql> select id, age, if(age>60, '老人', '年轻人') as info from person;
+----+------+--------+
| id | age | info |
+----+------+--------+
| 1 | 10 | 年轻人 |
| 2 | 18 | 年轻人 |
| 3 | 20 | 年轻人 |
| 4 | 30 | 年轻人 |
| 5 | 50 | 年轻人 |
| 6 | 70 | 老人 |
| 7 | 90 | 老人 |
| 8 | 100 | 老人 |
| 9 | NULL | 年轻人 |
+----+------+--------+
9 rows in set (0.00 sec)
- IFNULL(value1, value2)函数,这个函数一般用来替换NULL值,NULL值是不能参与数值运算的,举例替换NULL值为0,如下
// id = 9的那条数据,ifnull替换为了0
mysql> select id, age, ifnull(age, 0) from person;
+----+------+----------------+
| id | age | ifnull(age, 0) |
+----+------+----------------+
| 1 | 10 | 10 |
| 2 | 18 | 18 |
| 3 | 20 | 20 |
| 4 | 30 | 30 |
| 5 | 50 | 50 |
| 6 | 70 | 70 |
| 7 | 90 | 90 |
| 8 | 100 | 100 |
| 9 | NULL | 0 |
+----+------+----------------+
9 rows in set (0.00 sec)
- CASE WHEN [expr] THEN [result] … ELSE [default] END 函数,允许根据一个或多个条件返回不同的值,它的基本完整语法如下:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
...
ELSE resultN
END
- condition1, condition2, ... 是要评估的条件。
- result1, result2, ... 是当对应条件为真时返回的结果。
- ELSE 子句是可选的,用于当所有条件都不满足时返回的结果。
- END 关键字标志着 CASE 语句的结束。
继续使用 年龄在60以上的属于高龄,用 “老人” 表示,而60以下的属于年轻人, 用”年轻人“表示 的这个列子,如下:
// 注意 else 的使用,如果没有前面符合条件的,都是用 else 来表示
mysql> select id, age, case when age>60 then '老人' when age< 60 then '年轻人' else '=====' end from person;
+----+------+--------------------------------------------------------------------------+
| id | age | case when age>60 then '老人' when age< 60 then '年轻人' else '=====' end |
+----+------+--------------------------------------------------------------------------+
| 1 | 10 | 年轻人 |
| 2 | 18 | 年轻人 |
| 3 | 20 | 年轻人 |
| 4 | 30 | 年轻人 |
| 5 | 50 | 年轻人 |
| 6 | 70 | 老人 |
| 7 | 90 | 老人 |
| 8 | 100 | 老人 |
| 9 | NULL | ===== |
+----+------+--------------------------------------------------------------------------+
9 rows in set (0.00 sec)
- CASE [expr] WHEN [value] THEN [result] … ELSE [default] END 函数,这其实是case的一个简单函数,case后面跟着列名或者列的表达式,when后面跟表达式(case后面的)所有可能的值(不是值得范围,是具体等于的值),如果相等,就返回then后面的值,如下:
mysql> select id, age, (case age when 10 then '孩子' when '20' then '年轻人' when 100 then '老者' else '===' end) as info from person;
+----+------+--------+
| id | age | info |
+----+------+--------+
| 1 | 10 | 孩子 |
| 2 | 18 | === |
| 3 | 20 | 年轻人 |
| 4 | 30 | === |
| 5 | 50 | === |
| 6 | 70 | === |
| 7 | 90 | === |
| 8 | 100 | 老者 |
| 9 | NULL | === |
+----+------+--------+
9 rows in set (0.00 sec)
END
未完,持续更新中… …