ni## 一、日期函数
1.current_date()：获取年月日
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2022-06-07 |
+----------------+
1 row in set (0.00 sec)
2.current_time()：获取时分秒
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 18:14:40 |
+----------------+
1 row in set (0.00 sec)
3.current_timestamp()：获取时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2022-06-07 18:16:15 |
+---------------------+
1 row in set (0.00 sec)
4.date_add():可以添加日期或时间
mysql> select date_add('2022-6-7',interval 10 day);
+--------------------------------------+
| date_add('2022-6-7',interval 10 day) |
+--------------------------------------+
| 2022-06-17 |
+--------------------------------------+
1 row in set (0.00 sec)
5.date_sub():减去日期或时间
mysql> select date_sub('2022-6-7',interval 10 day);
+--------------------------------------+
| date_sub('2022-6-7',interval 10 day) |
+--------------------------------------+
| 2022-05-28 |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> select date_sub('2022-6-7',interval -10 day);
+---------------------------------------+
| date_sub('2022-6-7',interval -10 day) |
+---------------------------------------+
| 2022-06-17 |
+---------------------------------------+
1 row in set (0.00 sec)
6.date_diff（）:统计时间差
mysql> select datediff('2022-1-1','2022-1-10');
+----------------------------------+
| datediff('2022-1-1','2022-1-10') |
+----------------------------------+
| -9 |
+----------------------------------+
1 row in set (0.00 sec)
7.now()：当前日期时间（和时间戳获得一样结果）
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-06-07 18:26:22 |
+---------------------+
1 row in set (0.00 sec)
8.实例
建立的表
mysql> desc msg;
+-----------+-------------+------+-----+--------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+--------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| nick_name | varchar(20) | NO | | NULL | |
| content | varchar(30) | YES | | 没有任何留言 | |
| sendtime | datetime | YES | | NULL | |
+-----------+-------------+------+-----+--------------------+----------------+
4 rows in set (0.00 sec)
（1）留言的时间：当前时间，只显示日期，只显示时间
mysql> select *from msg;
+----+--------------+--------------------------+---------------------+
| id | nick_name | content | sendtime |
+----+--------------+--------------------------+---------------------+
| 1 | 会飞的猪 | 这个小孩好可爱 | 2022-06-08 12:46:42 |
| 2 | 爬树的狗 | 这个小孩好可爱呀 | 2022-06-08 12:47:39 |
+----+--------------+--------------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select content,sendtime from msg;
+--------------------------+---------------------+
| content | sendtime |
+--------------------------+---------------------+
| 这个小孩好可爱 | 2022-06-08 12:46:42 |
| 这个小孩好可爱呀 | 2022-06-08 12:47:39 |
+--------------------------+---------------------+
2 rows in set (0.00 sec)
mysql> select content,date(sendtime) from msg;
+--------------------------+----------------+
| content | date(sendtime) |
+--------------------------+----------------+
| 这个小孩好可爱 | 2022-06-08 |
| 这个小孩好可爱呀 | 2022-06-08 |
+--------------------------+----------------+
2 rows in set (0.00 sec)
mysql> select content,time(sendtime) from msg;
+--------------------------+----------------+
| content | time(sendtime) |
+--------------------------+----------------+
| 这个小孩好可爱 | 12:46:42 |
| 这个小孩好可爱呀 | 12:47:39 |
+--------------------------+----------------+
2 rows in set (0.00 sec)
（2）显示两分钟以前发布的贴子（即：now() <= sendtime + 2min）
mysql> select * from msg where date_add(sendtime,interval 2 minute) < now();
+----+--------------+--------------------------+---------------------+
| id | nick_name | content | sendtime |
+----+--------------+--------------------------+---------------------+
| 1 | 会飞的猪 | 这个小孩好可爱 | 2022-06-08 12:46:42 |
| 2 | 爬树的狗 | 这个小孩好可爱呀 | 2022-06-08 12:47:39 |
| 3 | 小猪佩奇 | 世上无难事 | 2022-06-08 12:56:46 |
| 4 | 小黄鸭 | 只怕有心人 | 2022-06-08 12:57:07 |
+----+--------------+--------------------------+---------------------+
4 rows in set (0.00 sec)
（3）两分钟以内发布的贴子（即：now() <= sendtime + 2min）
mysql> select * from msg where date_add(sendtime,interval 2 minute) >= now();
+----+-----------+---------+---------------------+
| id | nick_name | content | sendtime |
+----+-----------+---------+---------------------+
| 5 | 小黄鸭 | 加油 | 2022-06-08 13:02:26 |
| 6 | 小黄鸭 | 冲呀 | 2022-06-08 13:02:37 |
+----+-----------+---------+---------------------+
2 rows in set (0.01 sec)
二、字符串函数
1.charset(str):查询某一列的字符串字符集
mysql> select charset(nick_name) from msg;
+--------------------+
| charset(nick_name) |
+--------------------+
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
| utf8 |
+--------------------+
6 rows in set (0.00 sec)
2.concat():连接字符串
如：要求显示student表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”
mysql> select *from exam_result;
+----+-----------+---------+------+---------+
| id | name | chinese | math | english |
+----+-----------+---------+------+---------+
| 3 | 猪悟能 | 176 | 98 | 90 |
| 4 | 曹孟德 | 140 | 90 | 67 |
| 6 | 孙权 | 140 | 73 | 78 |
| 8 | 孙悟空 | 87 | 98 | 95 |
+----+-----------+---------+------+---------+
4 rows in set (0.00 sec)
mysql> select concat(name,'的语文成绩是',chinese,'分','数学成绩是',math,'分','英语成绩是',english,'分') 成绩概览 from exam_result;
+---------------------------------------------------------------------------+
| 成绩概览 |
+---------------------------------------------------------------------------+
| 猪悟能的语文成绩是176分数学成绩是98分英语成绩是90分 |
| 曹孟德的语文成绩是140分数学成绩是90分英语成绩是67分 |
| 孙权的语文成绩是140分数学成绩是73分英语成绩是78分 |
| 孙悟空的语文成绩是87分数学成绩是98分英语成绩是95分 |
+---------------------------------------------------------------------------+
4 rows in set (0.00 sec)
3.length(str)：求字符串所占的字节数
一个汉字3字节
mysql> select name,length(name) from exam_result;
+-----------+--------------+
| name | length(name) |
+-----------+--------------+
| 猪悟能 | 9 |
| 曹孟德 | 9 |
| 孙权 | 6 |
| 孙悟空 | 9 |
+-----------+--------------+
4 rows in set (0.00 sec)
4.replace(str,s1,s2):str中的s1换成s2
如：将表中所有名字中有S的替换成’上海’
mysql> select name,replace(name,'S','上海') from exam_result;
+-----------+----------------------------+
| name | replace(name,'S','上海') |
+-----------+----------------------------+
| 猪悟能 | 猪悟能 |
| 曹孟德 | 曹孟德 |
| 孙权 | 孙权 |
| 孙悟空 | 孙悟空 |
| S关 | 上海关 |
| 可爱S | 可爱上海 |
+-----------+----------------------------+
6 rows in set (0.00 sec)
mysql> select replace('sxl','l','玲');
+--------------------------+
| replace('sxl','l','玲') |
+--------------------------+
| sx玲 |
+--------------------------+
1 row in set (0.00 sec)
5.substring(str,position,[长度])：截取字符串
mysql> select substring('abcdefg',3);
+------------------------+
| substring('abcdefg',3) |
+------------------------+
| cdefg |
+------------------------+
1 row in set (0.00 sec)
mysql> select substring('abcdefg',3,2);
+--------------------------+
| substring('abcdefg',3,2) |
+--------------------------+
| cd |
+--------------------------+
1 row in set (0.00 sec)
mysql> select substring(name,2,1),name from exam_result;
+---------------------+-----------+
| substring(name,2,1) | name |
+---------------------+-----------+
| 悟 | 猪悟能 |
| 孟 | 曹孟德 |
| 权 | 孙权 |
| 悟 | 孙悟空 |
| 关 | S关 |
| 爱 | 可爱S |
+---------------------+-----------+
6 rows in set (0.00 sec)
6.ucase(str)：转换成大写字母
mysql> select ucase('abcd');
+---------------+
| ucase('abcd') |
+---------------+
| ABCD |
+---------------+
1 row in set (0.00 sec)
7，lcase(str):转换成小写字母
mysql> select lcase('ABCD');
+---------------+
| lcase('ABCD') |
+---------------+
| abcd |
+---------------+
1 row in set (0.00 sec)
把名字的首字母转换成小写
8.综合运用
把名字首字母改为小写字母
步骤：（1）先把首字母和后面字符串分离
（2）把首字母变为小写字母
（3）连接字符串
mysql> select substring(name,1,1),substring(name,2),name from exam_result;
+---------------------+-------------------+-----------+
| substring(name,1,1) | substring(name,2) | name |
+---------------------+-------------------+-----------+
| 猪 | 悟能 | 猪悟能 |
| 曹 | 孟德 | 曹孟德 |
| 孙 | 权 | 孙权 |
| 孙 | 悟空 | 孙悟空 |
| S | 关 | S关 |
| 可 | 爱S | 可爱S |
| M | ARY | MARY |
| B | OB | BOB |
+---------------------+-------------------+-----------+
8 rows in set (0.00 sec)
mysql> select lcase(substring(name,1,1)),substring(name,2),name from exam_result;
+----------------------------+-------------------+-----------+
| lcase(substring(name,1,1)) | substring(name,2) | name |
+----------------------------+-------------------+-----------+
| 猪 | 悟能 | 猪悟能 |
| 曹 | 孟德 | 曹孟德 |
| 孙 | 权 | 孙权 |
| 孙 | 悟空 | 孙悟空 |
| s | 关 | S关 |
| 可 | 爱S | 可爱S |
| m | ARY | MARY |
| b | OB | BOB |
+----------------------------+-------------------+-----------+
8 rows in set (0.00 sec)
mysql> select concat(lcase(substring(name,1,1)),substring(name,2)),name from exam_result;
+------------------------------------------------------+-----------+
| concat(lcase(substring(name,1,1)),substring(name,2)) | name |
+------------------------------------------------------+-----------+
| 猪悟能 | 猪悟能 |
| 曹孟德 | 曹孟德 |
| 孙权 | 孙权 |
| 孙悟空 | 孙悟空 |
| s关 | S关 |
| 可爱S | 可爱S |
| mARY | MARY |
| bOB | BOB |
+------------------------------------------------------+-----------+
8 rows in set (0.00 sec)
把名字首字母变为大写，其余小写
步骤：（1）先分离首字母与后面的字符串
（2）把首字母变为大写，后面字符变为小写
（3）连接字符串
mysql> select substring(name,1,1),substring(name,2),name from exam_result;
+---------------------+-------------------+-----------+
| substring(name,1,1) | substring(name,2) | name |
+---------------------+-------------------+-----------+
| 猪 | 悟能 | 猪悟能 |
| 曹 | 孟德 | 曹孟德 |
| 孙 | 权 | 孙权 |
| 孙 | 悟空 | 孙悟空 |
| S | 关 | S关 |
| 可 | 爱S | 可爱S |
| M | ARY | MARY |
| B | OB | BOB |
| l | ily | lily |
| l | ucy | lucy |
+---------------------+-------------------+-----------+
10 rows in set (0.00 sec)
mysql> select ucase(substring(name,1,1)),substring(name,2),name from exam_result;
+----------------------------+-------------------+-----------+
| ucase(substring(name,1,1)) | substring(name,2) | name |
+----------------------------+-------------------+-----------+
| 猪 | 悟能 | 猪悟能 |
| 曹 | 孟德 | 曹孟德 |
| 孙 | 权 | 孙权 |
| 孙 | 悟空 | 孙悟空 |
| S | 关 | S关 |
| 可 | 爱S | 可爱S |
| M | ARY | MARY |
| B | OB | BOB |
| L | ily | lily |
| L | ucy | lucy |
+----------------------------+-------------------+-----------+
10 rows in set (0.00 sec)
mysql> select concat(ucase(substring(name,1,1)),substring(name,2)),name from exam_result;
+------------------------------------------------------+-----------+
| concat(ucase(substring(name,1,1)),substring(name,2)) | name |
+------------------------------------------------------+-----------+
| 猪悟能 | 猪悟能 |
| 曹孟德 | 曹孟德 |
| 孙权 | 孙权 |
| 孙悟空 | 孙悟空 |
| S关 | S关 |
| 可爱S | 可爱S |
| MARY | MARY |
| BOB | BOB |
| Lily | lily |
| Lucy | lucy |
+------------------------------------------------------+-----------+
10 rows in set (0.00 sec)
mysql> select concat(ucase(substring(name,1,1)),lcase(substring(name,2))),name from exam_rresult;
+-------------------------------------------------------------+-----------+
| concat(ucase(substring(name,1,1)),lcase(substring(name,2))) | name |
+-------------------------------------------------------------+-----------+
| 猪悟能 | 猪悟能 |
| 曹孟德 | 曹孟德 |
| 孙权 | 孙权 |
| 孙悟空 | 孙悟空 |
| S关 | S关 |
| 可爱s | 可爱S |
| Mary | MARY |
| Bob | BOB |
| Lily | lily |
| Lucy | lucy |
+-------------------------------------------------------------+-----------+
10 rows in set (0.00 sec)
9.instr(str,substr)：substr在str中首次出现的位置
mysql> select instr('sxlyiding','ly');
+-------------------------+
| instr('sxlyiding','ly') |
+-------------------------+
| 3 |
+-------------------------+
1 row in set (0.00 sec)
10，left(str,length):str从左边起取length个字符
mysql> select left(name,2),name from exam_result;
+--------------+-----------+
| left(name,2) | name |
+--------------+-----------+
| 猪悟 | 猪悟能 |
| 曹孟 | 曹孟德 |
| 孙权 | 孙权 |
| 孙悟 | 孙悟空 |
| S关 | S关 |
| 可爱 | 可爱S |
| MA | MARY |
| BO | BOB |
| li | lily |
| lu | lucy |
+--------------+-----------+
10 rows in set (0.00 sec)
11.strcmp(str1,str2)：逐字符比较大小
str1 > str2 : 返回1
str1 < str2：返回-1
str1 == str2：返回0（不分大小写）
mysql> select strcmp('ccc','bcc');
+---------------------+
| strcmp('ccc','bcc') |
+---------------------+
| 1 |
+---------------------+
1 row in set (0.00 sec)
mysql> select strcmp('acc','bcc');
+---------------------+
| strcmp('acc','bcc') |
+---------------------+
| -1 |
+---------------------+
1 row in set (0.00 sec)
mysql> select strcmp('acc','Acc');
+---------------------+
| strcmp('acc','Acc') |
+---------------------+
| 0 |
+---------------------+
1 row in set (0.00 sec)
12.trim(str)：去除前空格或后空格
ltrim(str)：去除左空格
rtrim(str):去除右空格
mysql> select trim(' sxl ');
+-----------------+
| trim(' sxl ') |
+-----------------+
| sxl |
+-----------------+
1 row in set (0.00 sec)
mysql> select rtrim(' a ') ret;
+-------+
| ret |
+-------+
| a |
+-------+
1 row in set (0.00 sec)
mysql> select ltrim(' a ') ret;
+------+
| ret |
+------+
| a |
+------+
1 row in set (0.00 sec)
mysql> select trim(' a ') ret;
+------+
| ret |
+------+
| a |
+------+
1 row in set (0.00 sec)
三、数学函数
1.abs(num)：绝对值
mysql> select abs(-123);
+-----------+
| abs(-123) |
+-----------+
| 123 |
+-----------+
1 row in set (0.00 sec)
2.ceiling(num)：向上取整
floor(num)：向下取整
mysql> select ceiling(12.3);
+---------------+
| ceiling(12.3) |
+---------------+
| 13 |
+---------------+
1 row in set (0.00 sec)
mysql> select floor(12.3);
+-------------+
| floor(12.3) |
+-------------+
| 12 |
+-------------+
1 row in set (0.00 sec)
3.：format（num,n）：保留小数位数，会四舍五入
mysql> select format(2.14,1);
+----------------+
| format(2.14,1) |
+----------------+
| 2.1 |
+----------------+
1 row in set (0.00 sec)
mysql> select format(2.15,1);
+----------------+
| format(2.15,1) |
+----------------+
| 2.2 |
+----------------+
1 row in set (0.00 sec)
mysql> select format(2.5,0);
+---------------+
| format(2.5,0) |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> select format(-3.6,0);
+----------------+
| format(-3.6,0) |
+----------------+
| -4 |
+----------------+
1 row in set (0.00 sec)
4.bin(num):十进制转二进制
mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010 |
+---------+
1 row in set (0.00 sec)
5.hex(num)：转换成十六进制
mysql> select hex(15);
+---------+
| hex(15) |
+---------+
| F |
+---------+
1 row in set (0.00 sec)
6.mod(num1,num2)：取模
mysql> select mod(10,3);
+-----------+
| mod(10,3) |
+-----------+
| 1 |
+-----------+
1 row in set (0.00 sec)
四、其它函数
1.user():查询当前用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
2.md5(str)：对字符串进行摘要，得到一个32位的定长字符串（匿文，应用：密码的展现）
mysql> select md5('abc');
+----------------------------------+
| md5('abc') |
+----------------------------------+
| 900150983cd24fb0d6963f7d28e17f72 |
+----------------------------------+
1 row in set (0.00 sec)
3.database()：查看当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| db1 |
+------------+
1 row in set (0.00 sec)
4.password():MySQL数据库使用该函数对用户加密
mysql> select password('123');
+-------------------------------------------+
| password('123') |
+-------------------------------------------+
| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
+-------------------------------------------+
1 row in set (0.00 sec)
5.ifnull(val1,val2):如果val1为null，返回val2，否则返回val1的值
mysql> select ifnull('abc','123');
+---------------------+
| ifnull('abc','123') |
+---------------------+
| abc |
+---------------------+
1 row in set (0.00 sec)
mysql> select ifnull(null,'123');
+--------------------+
| ifnull(null,'123') |
+--------------------+
| 123 |
+--------------------+
1 row in set (0.00 sec)