【MySQL】内置函数

发布于:2025-05-20 ⋅ 阅读:(15) ⋅ 点赞:(0)

目录

一、日期函数

(一)使用方法

1、获得年月日

2、获得时分秒

3、获得时间戳

4、在日期的基础上加日期

5、在日期的基础上减去日期

6、计算两个日期之间相差多少天

(二)创建一张生日表

(三)创建一个留言表

二、字符串函数

1、charset(str)、concat(string2 [, ... ])、instr(string,substring)

2、ucase(string2)、lcase(string2)

3、left(string2, length)、right(string2, length)

4、length(string)

5、获取msg表的 content 列的字符集

6、显示 exam 表中的信息,格式:“XXX的语文是XXX分,数学XXX分,英语XXX分”

7、求学生表中学生姓名各自占用的字节数

8、将学生表中所有名字的'孙'替换为'王'

9、截取学生表中 name 字段的第二个到第三个字符

10、以首字母小写的方式显示所有员工的姓名

11、删除左右空格

三、数学函数

1、进制转换

2、向上取整和向下取整

3、浮点数相关

四、其他函数

1、显示正在使用的数据库

2、查询当前用户

3、md5(str)哈希加密

4、password()函数

5、ifnull(val1, val2)


一、日期函数

(一)使用方法

函数名称 描述
current_date()

当前日期

current_time() 当前时间
current_timestamp() 当前时间戳
date(datetime) 返回 datetime 参数的日期部分
date_add(date, interval d_value_type)

当 date 中添加日期或时间

interval 后的数值单位可以是:year minuete second day

date_sub(date, interval d_value_type)

在 date 中减去日期或时间

interval 后的数值单位可以是:year minute second day

datediff(date1, date2) 两个日期的差,单位是天
now() 当前日期时间

1、获得年月日

mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2025-05-17     |
+----------------+
1 row in set (0.00 sec)

2、获得时分秒

mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 15:58:49       |
+----------------+
1 row in set (0.00 sec)

3、获得时间戳

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2025-05-17 15:59:07 |
+---------------------+
1 row in set (0.00 sec)

        在MySQL中,时间戳会以以上的格式进行显示。

4、在日期的基础上加日期

mysql> select date_add('1949-10-1', interval 100 day);
+-----------------------------------------+
| date_add('1949-10-1', interval 100 day) |
+-----------------------------------------+
| 1950-01-09                              |
+-----------------------------------------+
1 row in set (0.00 sec)

5、在日期的基础上减去日期

mysql> select date_sub('1949-10-1', interval 100 day);
+-----------------------------------------+
| date_sub('1949-10-1', interval 100 day) |
+-----------------------------------------+
| 1949-06-23                              |
+-----------------------------------------+
1 row in set (0.00 sec)

6、计算两个日期之间相差多少天

mysql> select datediff(current_date(), '1949-10-1');
+---------------------------------------+
| datediff(current_date(), '1949-10-1') |
+---------------------------------------+
|                                 27622 |
+---------------------------------------+
1 row in set (0.00 sec)

(二)创建一张生日表

mysql> create table tmp(
    -> id int primary key auto_increment,
    -> birthday date
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into tmp(birthday) values (current_date());
Query OK, 1 row affected (0.00 sec)

mysql> insert into tmp(birthday) values (current_time());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into tmp(birthday) values (current_timestamp());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from tmp;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2025-05-17 |
|  2 | 2025-05-17 |
|  3 | 2025-05-17 |
+----+------------+
3 rows in set (0.00 sec)

        虽然tmp表中字段 birthday 为 date ,但在插入数据时可以插入 date 、time 或者 timestamp类型数据,因为插入时间或时间戳时,​MySQL会隐式截断时间部分,仅保留日期。

(三)创建一个留言表

mysql> create table msg(
    -> id int primary key auto_increment,
    -> content varchar(100) not null,
    -> sendtime datetime
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into msg(content, sendtime) values('天道酬勤',now());
Query OK, 1 row affected (0.00 sec)

mysql> select * from msg;
+----+--------------+---------------------+
| id | content      | sendtime            |
+----+--------------+---------------------+
|  1 | 天道酬勤     | 2025-05-17 16:47:03 |
+----+--------------+---------------------+
1 row in set (0.00 sec)

        查询在两分钟内发布的贴子:

mysql> insert into msg(content, sendtime) values('越努力越幸运',now());
Query OK, 1 row affected (0.01 sec)

mysql> select content, sendtime from msg where sendtime>date_sub(now(), interval 2 minute);
+--------------------+---------------------+
| content            | sendtime            |
+--------------------+---------------------+
| 越努力越幸运       | 2025-05-17 16:50:37 |
+--------------------+---------------------+
1 row in set (0.00 sec)

mysql> select content, sendtime from msg;
+--------------------+---------------------+
| content            | sendtime            |
+--------------------+---------------------+
| 天道酬勤           | 2025-05-17 16:47:03 |
| 越努力越幸运       | 2025-05-17 16:50:37 |
+--------------------+---------------------+
2 rows in set (0.00 sec)

二、字符串函数

函数 说明
charset(str) 返回字符串字符集
concat(string2,[...]) 连接字符串
insrt(sting, substring) 返回 substring 在 string 中出现的位置,没有返回0
ucase(string2) 转换成大写
lcase(string2) 转化成小写
left(string2, length) 从 string2 中的左边起取 length 个字符
length(string) string 的长度
replace(str, search_str, replace_str) 在 str 中用 replace_str 替换 search_str
strcmp(string1, string2) 逐字符比较两字符串大小
substring(str, postion, [length]) 从 str 的 postion 开始,取 length 个字符
ltrim(string)/rtrim(string)/trim(string) 去除前空格或后空格

1、charset(str)、concat(string2 [, ... ])、instr(string,substring)

//查看字符串字符集
mysql> select charset('abc');
+----------------+
| charset('abc') |
+----------------+
| utf8           |
+----------------+
1 row in set (0.00 sec)
//拼接字符串
mysql> select concat('a', 'b', 'c');
+-----------------------+
| concat('a', 'b', 'c') |
+-----------------------+
| abc                   |
+-----------------------+
1 row in set (0.00 sec)
//返回字符串b在字符串abc中的位置 不存在则返回0
mysql> select instr('abc','b');
+------------------+
| instr('abc','b') |
+------------------+
|                2 |
+------------------+
1 row in set (0.00 sec)

2、ucase(string2)、lcase(string2)

//小写转大写
mysql> select ucase('abcABC123');
+--------------------+
| ucase('abcABC123') |
+--------------------+
| ABCABC123          |
+--------------------+
1 row in set (0.00 sec)
//大写转小写
mysql> select lcase('abcABC123');
+--------------------+
| lcase('abcABC123') |
+--------------------+
| abcabc123          |
+--------------------+
1 row in set (0.00 sec)

3、left(string2, length)、right(string2, length)

mysql> select left('aabbcc', 3);
+-------------------+
| left('aabbcc', 3) |
+-------------------+
| aab               |
+-------------------+
1 row in set (0.00 sec)

mysql> select right('aabbcc', 3);
+--------------------+
| right('aabbcc', 3) |
+--------------------+
| bcc                |
+--------------------+
1 row in set (0.00 sec)

4、length(string)

mysql> select length('aabbcc');
+------------------+
| length('aabbcc') |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

5、获取msg表的 content 列的字符集

mysql> select charset(content) from msg;
+------------------+
| charset(content) |
+------------------+
| utf8             |
| utf8             |
+------------------+
2 rows in set (0.00 sec)

6、显示 exam 表中的信息,格式:“XXX的语文是XXX分,数学XXX分,英语XXX分”

mysql> select concat('姓名:', name, ',语文成绩:', chinese, ',数学成绩:',math,',英语成绩:', english) as 考试成绩 from exam;
+------------------------------------------------------------------+
| 考试成绩                                                         |
+------------------------------------------------------------------+
| 姓名:刘备,语文成绩:90,数学成绩:90,英语成绩:76                    |
| 姓名:诸葛亮,语文成绩:99,数学成绩:98,英语成绩:96                  |
| 姓名:关羽,语文成绩:96,数学成绩:79,英语成绩:86                    |
| 姓名:张飞,语文成绩:80,数学成绩:82,英语成绩:60                    |
| 姓名:曹操,语文成绩:83,数学成绩:91,英语成绩:87                    |
| 姓名:孙策,语文成绩:80,数学成绩:82,英语成绩:58                    |
| 姓名:孙权,语文成绩:85,数学成绩:75,英语成绩:66                    |
+------------------------------------------------------------------+
7 rows in set (0.00 sec)

7、求学生表中学生姓名各自占用的字节数

//utf8中一个中文字符占3字节
mysql> select name, length(name) from exam;
+-----------+--------------+
| name      | length(name) |
+-----------+--------------+
| 刘备      |            6 |
| 诸葛亮    |            9 |
| 关羽      |            6 |
| 张飞      |            6 |
| 曹操      |            6 |
| 孙策      |            6 |
| 孙权      |            6 |
+-----------+--------------+
7 rows in set (0.00 sec)

8、将学生表中所有名字的'孙'替换为'王'

//替换只会影响当前查询结果 不会影响原表数据
mysql> select replace(name, '孙','王') from exam;
+----------------------------+
| replace(name, '孙','王')   |
+----------------------------+
| 刘备                       |
| 诸葛亮                     |
| 关羽                       |
| 张飞                       |
| 曹操                       |
| 王策                       |
| 王权                       |
+----------------------------+
7 rows in set (0.00 sec)
mysql> select name from exam;
+-----------+
| name      |
+-----------+
| 刘备      |
| 诸葛亮    |
| 关羽      |
| 张飞      |
| 曹操      |
| 孙策      |
| 孙权      |
+-----------+
7 rows in set (0.00 sec)

9、截取学生表中 name 字段的第二个到第三个字符

mysql> select substring(name, 2, 2) from exam;
+-----------------------+
| substring(name, 2, 2) |
+-----------------------+
| 备                    |
| 葛亮                  |
| 羽                    |
| 飞                    |
| 操                    |
| 策                    |
| 权                    |
+-----------------------+
7 rows in set (0.00 sec)

10、以首字母小写的方式显示所有员工的姓名

mysql> select concat(lcase(substring(ename,1,1)), substring(ename, 2)) as name from emp;
+--------+
| name   |
+--------+
| sMITH  |
| aLLEN  |
| wARD   |
| jONES  |
| mARTIN |
| bLAKE  |
| cLARK  |
| sCOTT  |
| kING   |
| tURNER |
| aDAMS  |
| jAMES  |
| fORD   |
| mILLER |
+--------+
14 rows in set (0.00 sec)

11、删除左右空格

mysql> select '    hello    ' as test;
+---------------+
| test          |
+---------------+
|     hello     |
+---------------+
1 row in set (0.00 sec)
//清除左空格
mysql> select ltrim('    hello    ') as test;
+-----------+
| test      |
+-----------+
| hello     |
+-----------+
1 row in set (0.00 sec)
//清除右空格
mysql> select rtrim('    hello    ') as test;
+-----------+
| test      |
+-----------+
|     hello |
+-----------+
1 row in set (0.00 sec)
//清除左右空格
mysql> select trim('    hello    ') as test;
+-------+
| test  |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)
//中间空格不受影响
mysql> select trim('    a b c d e    ') as test;
+-----------+
| test      |
+-----------+
| a b c d e |
+-----------+
1 row in set (0.00 sec)

三、数学函数

函数 说明
abs(num) 绝对值函数
bin(decimal_num) 十进制转换二进制
hex(decimal_num) 转换成十六进制
conv(num, from_base, to_base) 进制转换
ceiling(num) 向上取整
floor(num) 向下取整
format(num, decimal_places) 格式化,保留小数位数
rand() 返回随机浮点数,范围[0.0, 1.0)
mod(num, denominator) 取模,求余

1、进制转换

mysql> select abs(-1), abs(0), abs(1);
+---------+--------+--------+
| abs(-1) | abs(0) | abs(1) |
+---------+--------+--------+
|       1 |      0 |      1 |
+---------+--------+--------+
1 row in set (0.00 sec)
mysql> select bin(4),bin(6);
+--------+--------+
| bin(4) | bin(6) |
+--------+--------+
| 100    | 110    |
+--------+--------+
1 row in set (0.00 sec)

mysql> select hex(16), hex(20);
+---------+---------+
| hex(16) | hex(20) |
+---------+---------+
| 10      | 14      |
+---------+---------+
1 row in set (0.00 sec)
//将十进制数字7转换为四进制
mysql> select conv(7, 10, 4);
+----------------+
| conv(7, 10, 4) |
+----------------+
| 13             |
+----------------+
1 row in set (0.00 sec)

2、向上取整和向下取整

mysql> select ceiling(0.5), ceiling(0), ceiling(-0.5);
+--------------+------------+---------------+
| ceiling(0.5) | ceiling(0) | ceiling(-0.5) |
+--------------+------------+---------------+
|            1 |          0 |             0 |
+--------------+------------+---------------+
1 row in set (0.00 sec)

mysql> select floor(0.5), floor(0), floor(-0.5);
+------------+----------+-------------+
| floor(0.5) | floor(0) | floor(-0.5) |
+------------+----------+-------------+
|          0 |        0 |          -1 |
+------------+----------+-------------+
1 row in set (0.00 sec)

3、浮点数相关

mysql> select format(3.1415926, 2), format(3.1415926, 5), format(3.1415926, 10);
+----------------------+----------------------+-----------------------+
| format(3.1415926, 2) | format(3.1415926, 5) | format(3.1415926, 10) |
+----------------------+----------------------+-----------------------+
| 3.14                 | 3.14159              | 3.1415926000          |
+----------------------+----------------------+-----------------------+
1 row in set (0.00 sec)

mysql> select rand(), rand()*100;
+--------------------+--------------------+
| rand()             | rand()*100         |
+--------------------+--------------------+
| 0.8977837291525526 | 19.039931817948755 |
+--------------------+--------------------+
1 row in set (0.00 sec)

mysql> select mod(10,3), mod(10, -3), mod(-10, 3), mod(-10, -3);
+-----------+-------------+-------------+--------------+
| mod(10,3) | mod(10, -3) | mod(-10, 3) | mod(-10, -3) |
+-----------+-------------+-------------+--------------+
|         1 |           1 |          -1 |           -1 |
+-----------+-------------+-------------+--------------+
1 row in set (0.00 sec)

四、其他函数

1、显示正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test       |
+------------+
1 row in set (0.00 sec)

2、查询当前用户

mysql> select user();
+--------+
| user() |
+--------+
| root@  |
+--------+
1 row in set (0.00 sec)

3、md5(str)哈希加密

        在一些场景下,某些数据不能在表中以明文的形式出现,因此可以通过 md5() 函数进行加密。

mysql> create table test(
    -> id int primary key auto_increment,
    -> name varchar(20) not null,
    -> password char(32) not null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test(name, password) values('张三','abc123');
Query OK, 1 row affected (0.01 sec)
//密码明文保存
mysql> select * from test;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 张三   | abc123   |
+----+--------+----------+
1 row in set (0.00 sec)
//使用md5()加密
mysql> insert into test(name, password) values ('李四',md5('abc'));
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+--------+----------------------------------+
| id | name   | password                         |
+----+--------+----------------------------------+
|  1 | 张三   | abc123                           |
|  2 | 李四   | 900150983cd24fb0d6963f7d28e17f72 |
+----+--------+----------------------------------+
2 rows in set (0.00 sec)
mysql> select name from test where password=md5('abc');
+--------+
| name   |
+--------+
| 李四   |
+--------+
1 row in set (0.00 sec)

4、password()函数

        password()函数是专门用于对用户数据(密码)加密

mysql> select password('abc'), password('123');
+-------------------------------------------+-------------------------------------------+
| password('abc')                           | password('123')                           |
+-------------------------------------------+-------------------------------------------+
| *0D3CED9BEC10A777AEC23CCC353A8C08A633045E | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+-------------------------------------------+
1 row in set, 2 warnings (0.00 sec)

5、ifnull(val1, val2)

        类似于C语言中的三目运算符。若 val1 为 null,否则返回 val1 的值。

mysql> select ifnull('abc','123'), ifnull(null, '123'), ifnull('abc', null);
+---------------------+---------------------+---------------------+
| ifnull('abc','123') | ifnull(null, '123') | ifnull('abc', null) |
+---------------------+---------------------+---------------------+
| abc                 | 123                 | abc                 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)


网站公告

今日签到

点亮在社区的每一天
去签到