MySQL之内置函数(日期函数、字符串函数、数学函数、加密函数等)

1.日期函数

函数名称	描述
current_date()	当前日期
current_time()	当前时间
current_timestamp	当前时间戳
date(datetime)	返回datetime参数的日期部分
date_add(date,interval d_value_type)	在date中添加日期或时间，interval后的数值单位可以是：year, minute,second,day
date_sub(date,interval d_value_type)	在date中减去日期或时间，interval后的数值单位可以是：year, minute,second,day
datediff(date1,date2)	两个日期的差，单位是天
now()	当前日期时间

范例：
1.1返回系统当前时间

mysql> select now(),curdate(),current_time();
+---------------------+------------+----------------+
| now()               | curdate()  | current_time() |
+---------------------+------------+----------------+
| 2019-07-25 00:22:35 | 2019-07-25 | 00:22:35       |
+---------------------+------------+----------------+

mysql> select current_date(),current_time(),current_timestamp();
+----------------+----------------+---------------------+
| current_date() | current_time() | current_timestamp() |
+----------------+----------------+---------------------+
| 2019-07-25     | 00:23:52       | 2019-07-25 00:23:52 |
+----------------+----------------+---------------------+

1.2在日期的基础上加日期：

mysql> select date_add('2019-07-25',interval 10 day);
+----------------------------------------+
| date_add('2019-07-25',interval 10 day) |
+----------------------------------------+
| 2019-08-04                             |
+----------------------------------------+

1.3在日期的基础上减去时间

mysql> select date_sub('2019-07-25',interval 10 day);
+----------------------------------------+
| date_sub('2019-07-25',interval 10 day) |
+----------------------------------------+
| 2019-07-15                             |
+----------------------------------------+

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

mysql> select datediff('2019-07-25','2018-06-12');
+-------------------------------------+
| datediff('2019-07-25','2018-06-12') |
+-------------------------------------+
|                                 408 |
+-------------------------------------+

1.5创建一张表，用来记录生日

mysql> create table bir_table(
    -> id int primary key auto_increment,
    -> birthday date);

//添加当前日期
mysql> insert into bir_table(birthday) value(current_date());

mysql> select * from bir_table;
+----+------------+
| id | birthday   |
+----+------------+
|  1 | 2019-07-25 |
+----+------------+

1.6创建一个留言表

mysql> create table msg(
    -> id int primary key auto_increment,
    -> content varchar(30) not null,
    -> sendtime datetime
    -> );

//用户添加留言
mysql> insert into msg(content,sendtime) values('晚上好', now());
mysql> insert into msg(content,sendtime) values('晚安，好梦', now());

//查看所有留言记录
mysql> select * from msg;
+----+-----------------+---------------------+
| id | content         | sendtime            |
+----+-----------------+---------------------+
|  1 | 晚上好          | 2019-07-25 00:35:01 |
|  2 | 晚安，好梦      | 2019-07-25 00:35:23 |
+----+-----------------+---------------------+

显示所有留言信息，发布日期只显示日期，不显示时间

mysql> select content,date(sendtime) from msg;
+-----------------+----------------+
| content         | date(sendtime) |
+-----------------+----------------+
| 晚上好          | 2019-07-25     |
| 晚安，好梦      | 2019-07-25     |
+-----------------+----------------+

查询在10分钟内发布的留言

mysql> select * from msg where date_add(sendtime,interval 10 minute)>now();
+----+-----------------+---------------------+
| id | content         | sendtime            |
+----+-----------------+---------------------+
|  1 | 晚上好          | 2019-07-25 00:35:01 |
|  2 | 晚安，好梦      | 2019-07-25 00:35:23 |
+----+-----------------+---------------------+

2.字符串函数

案例：

• 获取emp表的ename列的字符集

select charset(ename) from emp;

• 要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分”

select concat(name, '的语文是',yuwen,'分，数学是',shuxue,'分') as '分数' from exam_result;

• 将EMP表中所有名字中含有s的替换成’上海’

select replace(ename, 'S', '上海') ,ename from EMP;

• 截取EMP表中ename字段的第二个到第三个字符

select substring(ename, 2, 2), ename from EMP;

3.数学函数

• 绝对值

mysql> select abs(-100.2);
+-------------+
| abs(-100.2) |
+-------------+
|       100.2 |
+-------------+

• 向上取整

mysql> select ceiling(23.04);
+----------------+
| ceiling(23.04) |
+----------------+
|             24 |
+----------------+

• 保留2位小数位数(小数四舍五入)

mysql> select floor(23.7);
+-------------+
| floor(23.7) |
+-------------+
|          23 |
+-------------+

• 产生随机数

mysql> select rand();
+------------------+
| rand()           |
+------------------+
| 0.75725203357381 |
+------------------+

• 十进制转换二进制

mysql> select bin(10);
+---------+
| bin(10) |
+---------+
| 1010    |
+---------+

4.其它函数

• user()查询当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

• md5(str)对一个字符串进行md5摘要，摘要后得到一个32位字符串

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+

• database()显示当前正在使用的数据库

mysql> select database();
+------------+
| database() |
+------------+
| test1      |
+------------+

• password()函数，MySQL数据库使用该函数对用户加密

mysql> select password('root');
+-------------------------------------------+
| password('root')                          |
+-------------------------------------------+
| *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B |
+-------------------------------------------+

• 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                |
+--------------------+