【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 ) [0.0, 1.0) [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。