MySQL编程：核心函数深度剖析——日期、字符串、数学等全面解读
目录
- 函数
1.1 日期函数
1.2 字符串函数
1.3 数学函数
1.4 其他函数
1. 函数
1.1 日期函数
- 获取当前日期：
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2024-09-17 |
+----------------+
1 row in set (0.00 sec)
- 获取当前时间：
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 21:37:52 |
+----------------+
1 row in set (0.00 sec)
- 获取当前时间戳（包含日期和时间）：
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2024-09-17 21:39:02 |
+---------------------+
1 row in set (0.00 sec)
- 获取当前日期和时间：
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2024-09-17 21:44:10 |
+---------------------+
1 row in set (0.00 sec)
- 在日期基础上添加时间：
# 添加10天
mysql> select date_add('2022-01-1',interval 10 day);
+---------------------------------------+
| date_add('2022-01-1',interval 10 day) |
+---------------------------------------+
| 2022-01-11 |
+---------------------------------------+
1 row in set (0.00 sec)
# 添加10分钟
mysql> select date_add('2022-01-1',interval 10 minute);
+------------------------------------------+
| date_add('2022-01-1',interval 10 minute) |
+------------------------------------------+
| 2022-01-01 00:10:00 |
+------------------------------------------+
1 row in set (0.00 sec)
# 添加10秒
mysql> select date_add('2022-01-1',interval 10 second);
+------------------------------------------+
| date_add('2022-01-1',interval 10 second) |
+------------------------------------------+
| 2022-01-01 00:00:10 |
+------------------------------------------+
1 row in set (0.00 sec)
- 在日期基础上减去时间：
# 减去15天
mysql> select date_sub('2023-10-12',interval 15 day);
+----------------------------------------+
| date_sub('2023-10-12',interval 15 day) |
+----------------------------------------+
| 2023-09-27 |
+----------------------------------------+
1 row in set (0.00 sec)
# 减去15分钟
mysql> select date_sub('2023-10-12',interval 15 minute);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 minute) |
+-------------------------------------------+
| 2023-10-11 23:45:00 |
+-------------------------------------------+
1 row in set (0.00 sec)
# 减去15秒
mysql> select date_sub('2023-10-12',interval 15 second);
+-------------------------------------------+
| date_sub('2023-10-12',interval 15 second) |
+-------------------------------------------+
| 2023-10-11 23:59:45 |
+-------------------------------------------+
1 row in set (0.00 sec)
- 计算两个日期之间的天数差（前者减后者）：
mysql> select datediff('2023-10-15','2023-9-8');
+-----------------------------------+
| datediff('2023-10-15','2023-9-8') |
+-----------------------------------+
| 37 |
+-----------------------------------+
1 row in set (0.00 sec)
mysql> select datediff('2023-9-8','2023-10-15');
+-----------------------------------+
| datediff('2023-9-8','2023-10-15') |
+-----------------------------------+
| -37 |
+-----------------------------------+
1 row in set (0.00 sec)
案例-1：
- 创建记录生日的表：
mysql> create table tmp(
-> id int primary key auto_increment,
-> birthday date);
Query OK, 0 rows affected (0.03 sec)
mysql> desc tmp;
+----------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| birthday | date | YES | | NULL | |
+----------+---------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
- 插入当前日期：
mysql> insert into tmp (birthday) values(current_date());
Query OK, 1 row affected (0.01 sec)
mysql> select * from tmp;
+----+------------+
| id | birthday |
+----+------------+
| 1 | 2024-09-17 |
+----+------------+
1 row in set (0.00 sec)
案例-2：
- 创建留言表：
mysql> create table msg(
-> id int primary key auto_increment,
-> content varchar(30) not null,
-> sendtime datetime);
Query OK, 0 rows affected (0.02 sec)
mysql> desc msg;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| content | varchar(30) | NO | | NULL | |
| sendtime | datetime | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
- 插入数据：
mysql> insert into msg (content,sendtime) values('hello mysql',now());
Query OK, 1 row affected (0.01 sec)
mysql> insert into msg (content,sendtime) values('hello friend',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from msg;
+----+--------------+---------------------+
| id | content | sendtime |
+----+--------------+---------------------+
| 1 | hello mysql | 2024-09-17 21:50:49 |
| 2 | hello friend | 2024-09-17 21:50:56 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)
- 显示留言信息，仅显示日期部分：
mysql> select content,date(sendtime) from msg;
+--------------+----------------+
| content | date(sendtime) |
+--------------+----------------+
| hello mysql | 2024-09-17 |
| hello friend | 2024-09-17 |
+--------------+----------------+
2 rows in set (0.00 sec)
- 查询两分钟内发布的帖子：
# 直接查询两分钟内发布的帖子（已过两分钟无结果）
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
Empty set (0.00 sec)
# 插入新数据后查询
mysql> insert into msg (content,sendtime) values('excuse me',now());
Query OK, 1 row affected (0.01 sec)
mysql> select * from msg where date_add(sendtime,interval 2 minute) > now();
+----+-----------+---------------------+
| id | content | sendtime |
+----+-----------+---------------------+
| 3 | excuse me | 2024-09-17 21:55:52 |
+----+-----------+---------------------+
1 row in set (0.00 sec)
1.2 字符串函数
- 获取字符串字符集：
mysql> select charset('aaa');
+----------------+
| charset('aaa') |
+----------------+
| utf8 |
+----------------+
1 row in set (0.00 sec)
- 获取表中某列的字符集：
mysql> 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.00 sec)
- 连接字符串：
mysql> select concat('abc','123',456);
+-------------------------+
| concat('abc','123',456) |
+-------------------------+
| abc123456 |
+-------------------------+
1 row in set (0.00 sec)
- 格式化表中信息显示：
mysql> select concat(name,'的语文是',chinese,'分，数学是',math,'分') from exam_result;
+------------------------------------------------------------------+
| concat(name,'的语文是',chinese,'分，数学是',math,'分') |
+------------------------------------------------------------------+
| 唐三藏的语文是134分，数学是98分 |
| 猪悟能的语文是176分，数学是98分 |
| 曹孟德的语文是140分，数学是90分 |
| 刘玄德的语文是110分，数学是115分 |
| 孙权的语文是140分，数学是73分 |
| 宋公明的语文是150分，数学是95分 |
+------------------------------------------------------------------+
6 rows in set (0.00 sec)
- 获取字符串占用字节数：
# 中文utf8编码占3字节，英文数字占1字节
mysql> select name,length(name) from student;
+--------+--------------+
| name | length(name) |
+--------+--------------+
| 张三 | 6 |
| 李四 | 6 |
| 王五 | 6 |
| 孙权 | 6 |
| 妲己 | 6 |
+--------+--------------+
5 rows in set (0.00 sec)
mysql> select length('abcd中');
+-------------------+
| length('abcd中') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
- 判断子串是否在主串中：
mysql> select instr('abcdef123','123a') bool;
+------+
| bool |
+------+
| 0 |
+------+
1 row in set (0.00 sec)
mysql> select instr('abcdef123','123') bool;
+------+
| bool |
+------+
| 7 |
+------+
1 row in set (0.00 sec)
- 转换字符串大小写：
# 转大写
mysql> select ucase('abcdef') res;
+--------+
| res |
+--------+
| ABCDEF |
+--------+
1 row in set (0.00 sec)
# 转小写
mysql> select lcase('ABCDEF') res;
+--------+
| res |
+--------+
| abcdef |
+--------+
1 row in set (0.00 sec)
- 截取字符串：
# 从左边取3个字符
mysql> select left('abcdef',3);
+------------------+
| left('abcdef',3) |
+------------------+
| abc |
+------------------+
1 row in set (0.00 sec)
# 从右边取3个字符
mysql> select right('abcdef',3);
+-------------------+
| right('abcdef',3) |
+-------------------+
| def |
+-------------------+
1 row in set (0.00 sec)
- 替换字符串中的特定内容：
mysql> select replace(ename,'S','上海') from emp;
+-----------------------------+
| replace(ename,'S','上海') |
+-----------------------------+
| 上海MITH |
| ALLEN |
| WARD |
| JONE上海 |
| MARTIN |
| BLAKE |
| CLARK |
| 上海COTT |
| KING |
| TURNER |
| ADAM上海 |
| JAME上海 |
| FORD |
| MILLER |
+-----------------------------+
14 rows in set (0.00 sec)
- 截取指定范围的字符串：
mysql> select substring(ename,2,2) from emp;
+----------------------+
| substring(ename,2,2) |
+----------------------+
| MI |
| LL |
| AR |
| ON |
| AR |
| LA |
| LA |
| CO |
| IN |
| UR |
| DA |
| AM |
| OR |
| IL |
+----------------------+
14 rows in set (0.00 sec)
- 首字母小写显示姓名：
mysql> select concat(lcase(substring(ename,1,1)),substring(ename,2)) from emp;
+--------------------------------------------------------+
| concat(lcase(substring(ename,1,1)),substring(ename,2)) |
+--------------------------------------------------------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+--------------------------------------------------------+
14 rows in set (0.00 sec)
- 去除字符串前后空格：
# 去除前空格
mysql> select ltrim(' nihao ') res;
+------------------+
| res |
+------------------+
| nihao |
+------------------+
1 row in set (0.00 sec)
# 去除后空格
mysql> select rtrim(' nihao ') res;
+----------+
| res |
+----------+
| nihao |
+----------+
1 row in set (0.00 sec)
# 去除前后空格
mysql> select trim(' nihao ') res;
+-------+
| res |
+-------+
| nihao |
+-------+
1 row in set (0.00 sec)
1.3 数学函数
- 绝对值：
文章整理自互联网，只做测试使用。发布者：Lomu，转转请注明出处：https://www.it1024doc.com/12961.html
