mysql 函数(二 )

 

日期函数

 

1 now 当前时间

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2015-07-15 23:15:12 |
+---------------------+
1 row in set

 2 curDate() 当前日期

 

mysql> select curDate();
+------------+
| curDate()  |
+------------+
| 2015-07-15 |
+------------+
1 row in set

 3 curTime() 当前时间

 

mysql> select curTime();
+-----------+
| curTime() |
+-----------+
| 23:15:21  |
+-----------+
1 row in set

 4 时间添加函数 date_add()

 

  

mysql> select date_add('2015-6-1',interval 365 day);
+---------------------------------------+
| date_add('2015-6-1',interval 365 day) |
+---------------------------------------+
| 2016-05-31                            |
+---------------------------------------+
1 row in set

mysql> select date_add('2015-6-1',interval -365 day);
+----------------------------------------+
| date_add('2015-6-1',interval -365 day) |
+----------------------------------------+
| 2014-06-01                             |
+----------------------------------------+
1 row in set
mysql>  select date_add('2015-6-1',interval 3 week);
+--------------------------------------+
| date_add('2015-6-1',interval 3 week) |
+--------------------------------------+
| 2015-06-22                           |
+--------------------------------------+
1 row in set

 5 datediff() 时间差值

mysql> select datediff('2015-7-15','2015-7-1');
+----------------------------------+
| datediff('2015-7-15','2015-7-1') |
+----------------------------------+
|                               14 |
+----------------------------------+
1 row in set

 6 date_format 日期格式化

mysql> select date_format('2015-07-15','%m月%d日%Y年');
+------------------------------------------+
| date_format('2015-07-15','%m月%d日%Y年') |
+------------------------------------------+
| 07月15日2015年                           |
+------------------------------------------+
1 row in set

 

信息函数

1 connection_id() 当前连接的线程ID

 

mysql> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
|              47 |
+-----------------+
1 row in set

 2  user() 当前用户

mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set

 3 version() 当前数据库版本

mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.22-community-nt |
+---------------------+
1 row in set

 4 last_insert_id() 最后一次插入的ID

mysql> select * from dept;
+----+----------+
| id | deptName |
+----+----------+
|  2 | 安全部   |
+----+----------+
1 row in set
mysql> insert into dept(deptName) values('研发部');
Query OK, 1 row affected
mysql> select last_insert_id();
+------------------+
| last_insert_id() |
+------------------+
|                3 |
+------------------+
1 row in set

 5 database() 当前数据库

mysql> select  database();
+------------+
| database() |
+------------+
| t1         |
+------------+
1 row                                                                                                

数学函数

1 ceil() 向上取整,也叫进一取整

 

mysql> select ceil(12.33);
+-------------+
| ceil(12.33) |
+-------------+
|          13 |
+-------------+
1 row in set
mysql> select ceil(12.01);
+-------------+
| ceil(12.01) |
+-------------+
|          13 |
+-------------+
1 row in set

mysql> select ceil(12.89);
+-------------+
| ceil(12.89) |
+-------------+
|          13 |
+-------------+
1 row in set

 2 floor() 向下取整，也叫舍一取整

mysql> select floor(12.98);
+--------------+
| floor(12.98) |
+--------------+
|           12 |
+--------------+
1 row in set

 3 mod 取莫 类似 %

mysql> select 3 mod 4;
+---------+
| 3 mod 4 |
+---------+
|       3 |
+---------+
1 row in set

 4 div 除 类似/

mysql> select 3 div 5;
+---------+
| 3 div 5 |
+---------+
|       0 |
+---------+
1 row in set
mysql> select 3 div 1;
+---------+
| 3 div 1 |
+---------+
|       3 |

5 power(m,n) 幂运

mysql> select power(2,4);
+------------+
| power(2,4) |
+------------+
|         16 |
+------------+
1 row in set

 算 m的n次方

6 round() 四舍五入

mysql> select round(2.44);
+-------------+
| round(2.44) |
+-------------+
| 2           |
+-------------+
1 row in set

mysql> select round(2.456,2);
+----------------+
| round(2.456,2) |
+----------------+
| 2.46           |
+----------------+
1 row in set

 7 truncate() 截取 字段 后面的参数是小数点的位数

mysql> select truncate(12.33,1);
+-------------------+
| truncate(12.33,1) |
+-------------------+
| 12.3              |
+-------------------+
1 row in set

mysql> select truncate(23.3333,3);
+---------------------+
| truncate(23.3333,3) |
+---------------------+
| 23.333              |
+---------------------+
1 row in set