文章目录
- 前言
- 一、日期函数
- 案例1:
- 案例2
- 二、字符串函数
- 三、数学函数
- 四、其它函数
- 总结
前言
正文开始!!!
一、日期函数
函数名称 | 描述 |
---|---|
current_date() | 当前日期 |
current_time() | 当前时间 |
current_timestamp() | 当前时间戳 |
date(datetime) | 返回datetime参数的日期部分 |
date_add(date,interval d_value_type) | 在date中添加日期或者时间,interval后的数值单位可以使:year/day/minute/second |
date_sub(date,interval d_value_type) | 在date中减去日期或者时间,interval后的数值单位可以使:year/day/minute/second |
datediff(date1,date2) | 两个日期的差,单位是天 |
now() | 当前日期时间 |
- 获得年月日:
mysql> select current_date();
+----------------+
| current_date() |
+----------------+
| 2023-04-21 |
+----------------+
1 row in set (0.00 sec)
- 获得时分秒:
mysql> select current_time();
+----------------+
| current_time() |
+----------------+
| 12:52:40 |
+----------------+
1 row in set (0.00 sec)
- 获得时间戳
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2023-04-21 12:53:03 |
+---------------------+
1 row in set (0.00 sec)mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-04-21 14:25:06 |
+---------------------+
1 row in set (0.00 sec)
- 在日期的基础上加日期:
mysql> select date_add('1949-10-01',interval 10 day);
+----------------------------------------+
| date_add('1949-10-01',interval 10 day) |
+----------------------------------------+
| 1949-10-11 |
+----------------------------------------+
1 row in set (0.00 sec)mysql> select date_add('1949-10-01',interval 10 month);
+------------------------------------------+
| date_add('1949-10-01',interval 10 month) |
+------------------------------------------+
| 1950-08-01 |
+------------------------------------------+
1 row in set (0.00 sec)
- 在日期的基础上减去时间:
mysql> select current_time(),date_sub(current_time(),interval 10 year);
+----------------+-------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 year) |
+----------------+-------------------------------------------+
| 14:21:13 | NULL |
+----------------+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select current_time(),date_sub(current_time(),interval 10 month);
+----------------+--------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 month) |
+----------------+--------------------------------------------+
| 14:21:18 | NULL |
+----------------+--------------------------------------------+
1 row in set, 1 warning (0.00 sec)mysql> select current_time(),date_sub(current_time(),interval 10 second);
+----------------+---------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 second) |
+----------------+---------------------------------------------+
| 14:21:25 | 14:21:15 |
+----------------+---------------------------------------------+
1 row in set (0.00 sec)mysql> select current_time(),date_sub(current_time(),interval 10 minute);
+----------------+---------------------------------------------+
| current_time() | date_sub(current_time(),interval 10 minute) |
+----------------+---------------------------------------------+
| 14:21:30 | 14:11:30 |
+----------------+---------------------------------------------+
1 row in set (0.00 sec)
- 计算两个日期之间相差多少天:
mysql> select datediff('1949-10-01',current_time());
+---------------------------------------+
| datediff('1949-10-01',current_time()) |
+---------------------------------------+
| -26865 |
+---------------------------------------+
1 row in set (0.00 sec)mysql> select datediff(current_time(),'1949-10-01');
+---------------------------------------+
| datediff(current_time(),'1949-10-01') |
+---------------------------------------+
| 26865 |
+---------------------------------------+
1 row in set (0.00 sec)
案例1:
- 创建一张表,记录生日
Database changed
mysql> create table if not exists birth(-> d date not null,-> t timestamp-> );
Query OK, 0 rows affected (0.01 sec)mysql> desc birth;
+-------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------+
| d | date | NO | | NULL | |
| t | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------+
2 rows in set (0.01 sec)mysql> insert into birth (d) values ('1949/10/01');
Query OK, 1 row affected (0.00 sec)mysql> insert into birth (d) values (current_date());
Query OK, 1 row affected (0.00 sec)mysql> select * from birth;
+------------+---------------------+
| d | t |
+------------+---------------------+
| 1949-10-01 | 2023-04-21 14:13:16 |
| 2023-04-21 | 2023-04-21 14:13:27 |
+------------+---------------------+
2 rows in set (0.01 sec)mysql> alter table birth change t t datetime;
Query OK, 2 rows affected (0.03 sec)
Records: 2 Duplicates: 0 Warnings: 0mysql> desc birth;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | NO | | NULL | |
| t | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.01 sec)mysql> insert into birth (d,t) values (current_date(),current_timestamp());
Query OK, 1 row affected (0.00 sec)mysql> select * from birth;
+------------+---------------------+
| d | t |
+------------+---------------------+
| 1949-10-01 | 2023-04-21 14:13:16 |
| 2023-04-21 | 2023-04-21 14:13:27 |
| 2023-04-21 | 2023-04-21 14:15:50 |
+------------+---------------------+
3 rows in set (0.00 sec)mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2023-04-21 14:16:12 |
+---------------------+
1 row in set (0.00 sec)
案例2
- 创建一个留言表
mysql> create table if not exists msg(-> id int primary key auto_increment,-> content varchar(30) 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> insert into msg (content,sendtime) values ('顶楼上',now());
Query OK, 1 row affected (0.01 sec)mysql> insert into msg (content,sendtime) values ('一定可以!!!',now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg (content,sendtime) values ('one pieces',now());
Query OK, 1 row affected (0.00 sec)# 显示所有留言信息,发布日期显示日期
mysql> select * from msg;
+----+--------------------------------------+---------------------+
| id | content | sendtime |
+----+--------------------------------------+---------------------+
| 1 | 我是要成为海贼王的男人!!! | 2023-04-21 14:28:11 |
| 2 | 顶楼上 | 2023-04-21 14:28:33 |
| 3 | 一定可以!!! | 2023-04-21 14:28:40 |
| 4 | one pieces | 2023-04-21 14:28:48 |
+----+--------------------------------------+---------------------+
4 rows in set (0.00 sec)# 请查询在2分钟内发布的帖子
mysql> insert into msg (content,sendtime) values ('顶楼上',now());
Query OK, 1 row affected (0.00 sec)mysql> insert into msg (content,sendtime) values ('顶楼上111',now());
Query OK, 1 row affected (0.00 sec)# 评论的时间+2min>now()
mysql> select * from msg where date_add(sendtime,interval 2 minute)>now();
+----+--------------+---------------------+
| id | content | sendtime |
+----+--------------+---------------------+
| 5 | 顶楼上 | 2023-04-21 14:31:58 |
| 6 | 顶楼上111 | 2023-04-21 14:32:06 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)# now()-2min<评论的时间
mysql> select * from msg where date_sub(now(),interval 2 minute)<sendtime;
+----+--------------+---------------------+
| id | content | sendtime |
+----+--------------+---------------------+
| 5 | 顶楼上 | 2023-04-21 14:31:58 |
| 6 | 顶楼上111 | 2023-04-21 14:32:06 |
+----+--------------+---------------------+
2 rows in set (0.00 sec)
二、字符串函数
函数名称 | 描述 |
---|---|
charset(str) | 返回字符串字符集 |
concat(string [,…]) | 连接字符串 |
instr(string,substring) | 返回substring在string中出现的位置,没有返回0 |
ucase(string) | 转换成大写 |
lcase(string) | 转换成小写 |
left(string,length) | 从string中的左边起取length个字符 |
right(string,length) | 从string中的右边起取length个字符 |
length(string) | string的长度(单位是字节) |
replace(string,search_string,replace_str) | 在str中用replace_str替换search_str |
strcmp(string1,string2) | 逐字符比较两字符串的大小 |
substring(string,position [,length]) | 从string的position开始,取length个字符,如果不带length默认提取到字符串结尾 |
ltrim(string) rtrim(string) trim(string) | 去除前空格或后空格 |
案例:
- 获取emp表的ename列的字符集
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 charset(deptno) from emp;
+-----------------+
| charset(deptno) |
+-----------------+
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
| binary |
+-----------------+
14 rows in set (0.00 sec)
- 要求显示exam_result表中的信息,显示格式:“XXX的语文是XXX分,数学XXX分,英语XXX分”
mysql> select concat(name,'的语文成绩是:',chinese,'分,数学成绩是:',math,'分,英语成绩是:',english,'分') 分数 from exam_reesult;
+-------------------------------------------------------------------------------+
| 分数 |
+-------------------------------------------------------------------------------+
| 赖益烨的语文成绩是:67分,数学成绩是:98分,英语成绩是:56分 |
| 郭雨妍的语文成绩是:87分,数学成绩是:78分,英语成绩是:77分 |
| 马师傅的语文成绩是:88分,数学成绩是:98分,英语成绩是:90分 |
| 洋妈的语文成绩是:82分,数学成绩是:84分,英语成绩是:67分 |
| 李观洋的语文成绩是:55分,数学成绩是:85分,英语成绩是:45分 |
| 郭昊的语文成绩是:70分,数学成绩是:73分,英语成绩是:78分 |
| 张照洋的语文成绩是:75分,数学成绩是:65分,英语成绩是:30分 |
+-------------------------------------------------------------------------------+
7 rows in set (0.01 sec)
- 求学生表中学生姓名占用的字节数
mysql> select name,length(name) from exam_result;
+-----------+--------------+
| name | length(name) |
+-----------+--------------+
| 赖益烨 | 9 |
| 郭雨妍 | 9 |
| 马师傅 | 9 |
| 洋妈 | 6 |
| 李观洋 | 9 |
| 郭昊 | 6 |
| 张照洋 | 9 |
+-----------+--------------+
7 rows in set (0.00 sec)mysql> select length('abcdef');
+------------------+
| length('abcdef') |
+------------------+
| 6 |
+------------------+
1 row in set (0.00 sec)
注意:length函数返回字符串长度,以字节为单位.如果是多字节字符则计算多个字节数;如果是单字节字符则算作一个字节.比如:字母,数字算作一个字节,中文表示多个字节数(与字符集编码有关).
- 将EMP表中所有名字中有S的替换成’上海’
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| FORD |
| MILLER |
+--------+
14 rows 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.01 sec)
- 截取EMP表中ename字段的第二个到第三个字符
mysql> select ename from emp;
+--------+
| ename |
+--------+
| SMITH |
| ALLEN |
| WARD |
| JONES |
| MARTIN |
| BLAKE |
| CLARK |
| SCOTT |
| KING |
| TURNER |
| ADAMS |
| JAMES |
| 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)) ename from emp;
+--------+
| ename |
+--------+
| sMITH |
| aLLEN |
| wARD |
| jONES |
| mARTIN |
| bLAKE |
| cLARK |
| sCOTT |
| kING |
| tURNER |
| aDAMS |
| jAMES |
| fORD |
| mILLER |
+--------+
14 rows in set (0.00 sec)
三、数学函数
函数名称 | 描述 |
---|---|
abs(num) | 绝对值函数 |
bin(decimal_num) | 十进制转化二进制 |
hex(decimal_num) | 转换成十六进制 |
conv(num,from_base,to_base) | 进制转换 |
ceiling(num) | 向上取整 |
floor(num) | 向下取整 |
format(number,decimal_places) | 格式化,保留小数位数 |
rand() | 返回随机浮点数,范围[0.0,1.0) |
mod(num,denominator) | 取模,求余 |
- 绝对值
mysql> select abs(-1000.1);
+--------------+
| abs(-1000.1) |
+--------------+
| 1000.1 |
+--------------+
1 row in set (0.00 sec)mysql> select abs(-1000.123);
+----------------+
| abs(-1000.123) |
+----------------+
| 1000.123 |
+----------------+
1 row in set (0.00 sec)mysql> select abs(-0.123);
+-------------+
| abs(-0.123) |
+-------------+
| 0.123 |
+-------------+
1 row in set (0.00 sec)mysql> select abs(1.2);
+----------+
| abs(1.2) |
+----------+
| 1.2 |
+----------+
1 row in set (0.00 sec)
- 向上取整
mysql> select ceiling(8.1);
+--------------+
| ceiling(8.1) |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)mysql> select ceiling(8.9);
+--------------+
| ceiling(8.9) |
+--------------+
| 9 |
+--------------+
1 row in set (0.00 sec)mysql> select ceiling(-3.1);
+---------------+
| ceiling(-3.1) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)mysql> select ceiling(-3.9);
+---------------+
| ceiling(-3.9) |
+---------------+
| -3 |
+---------------+
1 row in set (0.00 sec)
- 向下取整
mysql> select floor(1.9);
+------------+
| floor(1.9) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)mysql> select floor(1.1);
+------------+
| floor(1.1) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)mysql> select floor(-3.1);
+-------------+
| floor(-3.1) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)mysql> select floor(-3.9);
+-------------+
| floor(-3.9) |
+-------------+
| -4 |
+-------------+
1 row in set (0.00 sec)
- 保留2位小数位数(小数四舍五入)
mysql> select format(12.236,2);
+------------------+
| format(12.236,2) |
+------------------+
| 12.24 |
+------------------+
1 row in set (0.00 sec)mysql> select format(12.231,2);
+------------------+
| format(12.231,2) |
+------------------+
| 12.23 |
+------------------+
1 row in set (0.00 sec)mysql> select format(-12.231,2);
+-------------------+
| format(-12.231,2) |
+-------------------+
| -12.23 |
+-------------------+
1 row in set (0.00 sec)mysql> select format(-12.236,2);
+-------------------+
| format(-12.236,2) |
+-------------------+
| -12.24 |
+-------------------+
1 row in set (0.00 sec)
- 产生随机数
mysql> select format(rand(),2);
+------------------+
| format(rand(),2) |
+------------------+
| 0.61 |
+------------------+
1 row in set (0.00 sec)mysql> select format(rand()*100,0);
+----------------------+
| format(rand()*100,0) |
+----------------------+
| 83 |
+----------------------+
1 row in set (0.00 sec)
四、其它函数
- user() 查询当前用户
mysql> select user();
+--------+
| user() |
+--------+
| root@ |
+--------+
1 row in set (0.00 sec)
- md5(str)对一个字符串进行md5摘要,摘要后得到一个32位字符串
mysql> select md5('1');
+----------------------------------+
| md5('1') |
+----------------------------------+
| c4ca4238a0b923820dcc509a6f75849b |
+----------------------------------+
1 row in set (0.00 sec)mysql> select md5('hulu');
+----------------------------------+
| md5('hulu') |
+----------------------------------+
| a016e7e3817eec54180fba6a64ae6616 |
+----------------------------------+
1 row in set (0.00 sec)
- database()显示当前使用的数据库
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
- password()函数,MySQL数据库使用该函数对用户加密
mysql> select password('123456');
+-------------------------------------------+
| password('123456') |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
- ifnull(val1, val2) 如果val1为null,返回val2,否则返回val1的值
mysql> select ifnull(1,2);
+-------------+
| ifnull(1,2) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)mysql> select ifnull(null,2);
+----------------+
| ifnull(null,2) |
+----------------+
| 2 |
+----------------+
1 row in set (0.00 sec)mysql> select ifnull(null,null);
+-------------------+
| ifnull(null,null) |
+-------------------+
| NULL |
+-------------------+
1 row in set (0.00 sec)
总结
(本章完!)