【MySQL】（6）常用函数

日期函数

函数	说明
current_date()	当前日期
current_time()	当前时间
current_timestamp()	当前时间戳
date(datetime)	返回 datetime 参数的日期部分
date_add(date, interval d_value_type)	在 date 中添加日期或时间 interval 后的数值单位可以是：year minute second day
date_sub(date, interval d_value_type)	在 date 中减去日期或时间 interval 后的数值单位可以是：year minute second day
datediff(date1, date2)	两个日期的差，单位为天
now()	当前日期时间

获取日期

例：

MariaDB [(none)]> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-04-18     |
+----------------+
1 row in set (0.00 sec)MariaDB [(none)]> select current_time();
+----------------+
| current_time() |
+----------------+
| 22:47:01       |
+----------------+
1 row in set (0.00 sec)MariaDB [(none)]> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-04-18 22:55:14 |
+---------------------+
1 row in set (0.00 sec)

创建一个生日表

CREATE TABLE birthday (birth_date DATE,create_time TIMESTAMP
);MariaDB [test_db]> desc birthday;
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field       | Type      | Null | Key | Default           | Extra                       |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| birth_date  | date      | YES  |     | NULL              |                             |
| create_time | timestamp | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.00 sec)

插入数据

-- 手动插入一个指定的日期
MariaDB [test_db]> insert into birthday (birth_date) values ('1919-08-10');
Query OK, 1 row affected (0.00 sec)-- 使用函数插入当前日期
MariaDB [test_db]> insert into birthday (birth_date) values (current_date); -- current_date可不加圆括号
Query OK, 1 row affected (0.00 sec)-- 查看结果
MariaDB [test_db]> select * from birthday;
+------------+---------------------+
| birth_date | create_time         |
+------------+---------------------+
| 1919-08-10 | 2023-04-18 23:04:12 |
| 2023-04-18 | 2023-04-18 23:06:07 |
+------------+---------------------+
2 rows in set (0.00 sec)

date() 获取 datetime 类型的日期部分

MariaDB [test_db]> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2023-04-18  |
+-------------+
1 row in set (0.00 sec)

日期计算

日期+时间

MariaDB [test_db]> select date_add('1919-08-10', interval 10 day);
+-----------------------------------------+
| date_add('1919-08-10', interval 10 day) |
+-----------------------------------------+
| 1919-08-20                              |
+-----------------------------------------+
1 row in set (0.00 sec)

日期-时间

MariaDB [test_db]> select date_sub('1919-08-10', interval 10 day);
+-----------------------------------------+
| date_sub('1919-08-10', interval 10 day) |
+-----------------------------------------+
| 1919-07-31                              |
+-----------------------------------------+
1 row in set (0.00 sec)

日期-日期

MariaDB [test_db]> select datediff(now(), '1919-08-10');
+-------------------------------+
| datediff(now(), '1919-08-10') |
+-------------------------------+
|                         37872 |
+-------------------------------+
1 row in set (0.00 sec)

例：

create table msg (id int unsigned primary key auto_increment,content varchar(100) not null,sendtime datetime
);

字符串函数

函数	说明
charset(str)	返回字符串字符集
concat(str1, str2, ...)	连接字符串
instr(str, substr)	返回 substr 在 str 中出现的位置，没有则返回0
ucase(str)(MySQL 特有) upper(str)(标准 SQL 定义)	转换转换为大写
lcase(str)(MySQL 特有) lower(str) (标准 SQL 定义)	转换转换为小写
left(str, len); right(str, len)	从 str 左起取 len 个字符; 从 str 右起取 len 个字符
length(str)	str 的长度
replace(str, search_str, replace_str)	在 str 中用 replace_str 替换 search_str
strcmp(str1, str2)	比较两个字符串
substring(str, pos[, len])	从 str 的 pos 位置（下标从 1 开始）开始，取 len 个字符
ltrim(str) rtrim(str) trim(str)	去除前空格或后空格

例：

charset

MariaDB [(none)]> select charset('abc');
+----------------+
| charset('abc') |
+----------------+
| utf8           |
+----------------+
1 row in set (0.03 sec)MariaDB [scott]> select charset(ename) from emp;
+----------------+
| charset(ename) |
+----------------+
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
| utf8           |
+----------------+
14 rows in set (0.04 sec)

concat

MariaDB [scott]> select concat('Hello', ' ', 'world', 123); -- 也可以传入数字，会被函数看作字符串
+------------------------------------+
| concat('Hello', ' ', 'world', 123) |
+------------------------------------+
| Hello world123                     |
+------------------------------------+
1 row in set (0.00 sec)MariaDB [scott]> select concat('My name is ', ename) from emp;
+------------------------------+
| concat('My name is ', ename) |
+------------------------------+
| My name is SMITH             |
| My name is ALLEN             |
| My name is WARD              |
| My name is JONES             |
| My name is MARTIN            |
| My name is BLAKE             |
| My name is CLARK             |
| My name is SCOTT             |
| My name is KING              |
| My name is TURNER            |
| My name is ADAMS             |
| My name is JAMES             |
| My name is FORD              |
| My name is MILLER            |
+------------------------------+
14 rows in set (0.00 sec)

length

MariaDB [scott]> select length(ename) from emp;
+---------------+
| length(ename) |
+---------------+
|             5 |
|             5 |
|             4 |
|             5 |
|             6 |
|             5 |
|             5 |
|             5 |
|             4 |
|             6 |
|             5 |
|             5 |
|             4 |
|             6 |
+---------------+
14 rows in set (0.00 sec)

注意 length 返回的长度是按字节为单位的

MariaDB [scott]> select length('你好');
+------------------+
| length('你好')   |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

substring

substring(str, pos[, len])

str 是要提取子字符串的原始字符串，pos 是子字符串的起始位置，len 是要提取的子字符串的长度。

如果省略 len 参数，则将返回从 pos 位置开始到原始字符串的末尾的所有字符。如果 pos 参数为负数，则 SUBSTRING 函数将从字符串的末尾开始计数。

MariaDB [(none)]> select substring('123456', 2);
+------------------------+
| substring('123456', 2) |
+------------------------+
| 23456                  |
+------------------------+
1 row in set (0.00 sec)MariaDB [(none)]> select substring('123456', 2, 2);
+---------------------------+
| substring('123456', 2, 2) |
+---------------------------+
| 23                        |
+---------------------------+
1 row in set (0.00 sec)

replace

MariaDB [(none)]> select replace('abcxyz1234', 'xyz', 'ddd');
+-------------------------------------+
| replace('abcxyz1234', 'xyz', 'ddd') |
+-------------------------------------+
| abcddd1234                          |
+-------------------------------------+
1 row in set (0.00 sec)

把所有人名首字母转小写

MariaDB [scott]> select concat(lower(left(ename, 1)), substring(ename, 2)) result from emp;
+--------+
| result |
+--------+
| sMITH  |
| aLLEN  |
| wARD   |
| jONES  |
| mARTIN |
| bLAKE  |
| cLARK  |
| sCOTT  |
| kING   |
| tURNER |
| aDAMS  |
| jAMES  |
| fORD   |
| mILLER |
+--------+
14 rows in set (0.00 sec)

instr

注：instr 查找忽略大小写的

MariaDB [scott]> select instr('abcxyz1234', 'xyz');
+----------------------------+
| instr('abcxyz1234', 'xyz') |
+----------------------------+
|                          4 |
+----------------------------+
1 row in set (0.00 sec)-- 查询名字里有 th 的人
MariaDB [scott]> select ename from emp where instr(ename, 'th');
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)-- 此方法等价于使用 like
MariaDB [scott]> select ename from emp where ename like '%th%';
+-------+
| ename |
+-------+
| SMITH |
+-------+
1 row in set (0.00 sec)

注意：

聚合函数不可以在where字句中使用，而上述的日期函数，字符串函数可以。

strcmp

MariaDB [scott]> select strcmp('abcd', 'abcd'), strcmp('aab', 'abcd'), strcmp('abcd', 'aab');
+------------------------+-----------------------+-----------------------+
| strcmp('abcd', 'abcd') | strcmp('aab', 'abcd') | strcmp('abcd', 'aab') |
+------------------------+-----------------------+-----------------------+
|                      0 |                    -1 |                     1 |
+------------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

ltrim, rtrim, trim

ltrim: 去掉前缀空格

rtrim: 去掉后缀空格

trim: 去掉前后缀空格。

例：

MariaDB [scott]> select ltrim('    Hello world    ') res;
+-----------------+
| res             |
+-----------------+
| Hello world     |
+-----------------+
1 row in set (0.00 sec)MariaDB [scott]> select rtrim('    Hello world    ') res;
+-----------------+
| res             |
+-----------------+
|     Hello world |
+-----------------+
1 row in set (0.00 sec)MariaDB [scott]> select trim('    Hello world    ') res;
+-------------+
| res         |
+-------------+
| Hello world |
+-------------+
1 row in set (0.00 sec)

数学函数

函数	说明
abs(N)	绝对值
bin(N)	十进制转二进制
hex(N)	十进制转十六进制
conv(N, from_base, to_base)	进制转换
ceiling(N)	向上取整
floor(N)	向下取整
format(N, D)	格式化为带有千位分隔符的字符串，保留 D 位小数
rand()	返回随机浮点数，范围 $[0.0,1.0)$
mod(N, M)	N 除以 M 的余数

abs

MariaDB [scott]> select abs(-10);
+----------+
| abs(-10) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

bin, hex

MariaDB [scott]> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+
1 row in set (0.01 sec)MariaDB [scott]> select hex(10);
+---------+
| hex(10) |
+---------+
| A       |
+---------+
1 row in set (0.00 sec)

conv

conv(N, from_base, to_base)

N 是要进行进制转换的数字

from_base 是原数值的进制

to_base 是目标数值的进制。

MariaDB [scott]> select conv('1A', 16, 10);
+--------------------+
| conv('1A', 16, 10) |
+--------------------+
| 26                 |
+--------------------+
1 row in set (0.00 sec)MariaDB [scott]> select conv(10, 3, 10);
+-----------------+
| conv(10, 3, 10) |
+-----------------+
| 3               |
+-----------------+
1 row in set (0.00 sec)MariaDB [scott]> select conv(10, 3, 2);
+----------------+
| conv(10, 3, 2) |
+----------------+
| 11             |
+----------------+
1 row in set (0.01 sec)

ceiling, floor

MariaDB [scott]> select ceiling(3.1);
+--------------+
| ceiling(3.1) |
+--------------+
|            4 |
+--------------+
1 row in set (0.00 sec)MariaDB [scott]> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
|            -3 |
+---------------+
1 row in set (0.00 sec)MariaDB [scott]> select floor(3.1);
+------------+
| floor(3.1) |
+------------+
|          3 |
+------------+
1 row in set (0.00 sec)MariaDB [scott]> select floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
|          -4 |
+-------------+
1 row in set (0.00 sec)

rand

MariaDB [scott]> select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.3416242546789574 |
+--------------------+
1 row in set (0.00 sec)

format

MariaDB [scott]> select format(rand()*10000, 1);
+-------------------------+
| format(rand()*10000, 1) |
+-------------------------+
| 2,683.2                 |
+-------------------------+
1 row in set (0.00 sec)

mod

MariaDB [scott]> select mod(10, 3);
+------------+
| mod(10, 3) |
+------------+
|          1 |
+------------+
1 row in set (0.00 sec)

其他函数

user() 查询当前用户

MariaDB [scott]> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

密码加密

md5()

MD5 是一种哈希函数，用于将任意长度的字符串转换为固定长度的散列值。它可以用于安全地存储和比较密码等敏感数据，因为通过计算哈希值，即使在数据库中存储的是哈希值，也可以在需要验证密码时进行比较，而无需直接存储密码本身。

MariaDB [scott]> select md5('hello world');
+----------------------------------+
| md5('hello world')               |
+----------------------------------+
| 5eb63bbbe01eeed093cb22bb8f5acdc3 |
+----------------------------------+
1 row in set (0.00 sec)

例：

在数据库中，像密码这样的敏感数据不可以明文存储，否则一旦数据库泄漏，用户的账户和密码就都泄漏了。所以数据库中的密码往往是加密过的。

创建一个包含用户名和密码字段的表，并向其中插入6条数据，每次插入数据时，都要使用 MD5 函数对密码进行哈希加密，以确保其安全性：

CREATE TABLE users (id INT NOT NULL AUTO_INCREMENT,username VARCHAR(50) NOT NULL,password VARCHAR(32) NOT NULL,PRIMARY KEY (id)
);INSERT INTO users (username, password) VALUES ('张小凡', MD5('b3UyDnT7'));
INSERT INTO users (username, password) VALUES ('王小磊', MD5('RvSgFpK4'));
INSERT INTO users (username, password) VALUES ('李婷婷', MD5('XcNjZmH9'));
INSERT INTO users (username, password) VALUES ('赵丽丽', MD5('qLwEeT2G'));
INSERT INTO users (username, password) VALUES ('刘伟东', MD5('a6fVdP8S'));
INSERT INTO users (username, password) VALUES ('陈冬梅', MD5('t4JkMxY1'));

MariaDB [test_db]> select * from users;
+----+-----------+----------------------------------+
| id | username  | password                         |
+----+-----------+----------------------------------+
|  7 | 张小凡    | 1329ebf6352bb67d4e2f6e2024d2f72c |
|  8 | 王小磊    | 6e49be674cee12dc2f333296c675db49 |
|  9 | 李婷婷    | 135abaf36b2e5803c97a6db4c1fe83c6 |
| 10 | 赵丽丽    | 45791c398b4dab808a8c1ae2bf8caaac |
| 11 | 刘伟东    | a0aada9b4933edd0f5da12c3194b6d5f |
| 12 | 陈冬梅    | b410ad83ff45a3f9ec91bb8ddbdb4096 |
+----+-----------+----------------------------------+
6 rows in set (0.00 sec)

查询李婷婷和她的密码 XcNjZmH9 是否匹配

MariaDB [test_db]> select * from users where username='李婷婷' and password=md5('XcNjZmH9');
+----+-----------+----------------------------------+
| id | username  | password                         |
+----+-----------+----------------------------------+
|  9 | 李婷婷    | 135abaf36b2e5803c97a6db4c1fe83c6 |
+----+-----------+----------------------------------+
1 row in set (0.00 sec)

这样即使数据库泄漏，攻击者也无法轻易地解密密码。

注意：

由于 MD5 散列算法已经被证明存在一些安全漏洞，不再建议将其用于新的安全应用程序中。如果你需要更高的安全性，建议使用 SHA-256 或 SHA-512 等更强大的哈希算法。

password()

在 MySQL 中，PASSWORD 通常用于加密用户的密码。这个函数使用 MySQL 自己的哈希算法，可以将一个字符串转换成一个不可逆的字符串，通常用于存储用户密码的安全散列值。

MariaDB [test_db]> select password('hello world');
+-------------------------------------------+
| password('hello world')                   |
+-------------------------------------------+
| *67BECF85308ACF0261750DA1075681EE5C412F05 |
+-------------------------------------------+
1 row in set (0.00 sec)

password() 的安全性比 md5() 略高一些。

database() 查看当前数据库

查看当前正在使用的数据库名称。

如果当前没有连接到任何数据库，调用该函数将返回 NULL 值。

MariaDB [test_db]> select database();
+------------+
| database() |
+------------+
| test_db    |
+------------+
1 row in set (0.00 sec)

在这种情况下没什么用，因为我的命令行提示符已经显示了当前正在使用的数据库名称

ifnull()

语法：

IFNULL(expr1, expr2)

expr1 是要判断的表达式，expr2 是默认值。如果 expr1 不为 NULL，则返回 expr1，否则返回 expr2。

例：

假设你有一个包含商品价格的表，但某些商品的价格尚未确定，因此为 NULL，你可以使用 IFNULL 函数来将这些商品的价格设置为默认值，如下所示：

SELECT product_name, IFNULL(price, 0) AS price
FROM products;

在上面的查询中，如果 price 字段不为 NULL，则返回 price 字段的值，否则返回 0。