目录
1、charset(str)、concat(string2 [, ... ])、instr(string,substring)
2、ucase(string2)、lcase(string2)
3、left(string2, length)、right(string2, length)
6、显示 exam 表中的信息，格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
一、日期函数
（一）使用方法
| 函数名称 | 描述 |
| 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)