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 setmysql> select date_add('2015-6-1',interval -365 day);+----------------------------------------+| date_add('2015-6-1',interval -365 day) |+----------------------------------------+| 2014-06-01 |+----------------------------------------+1 row in setmysql> 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() |+----------------+| [email protected] |+----------------+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 setmysql> insert into dept(deptName) values('研发部');Query OK, 1 row affectedmysql> 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 setmysql> select ceil(12.01);+-------------+| ceil(12.01) |+-------------+| 13 |+-------------+1 row in setmysql> 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 setmysql> 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 setmysql> 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 setmysql> select truncate(23.3333,3);+---------------------+| truncate(23.3333,3) |+---------------------+| 23.333 |+---------------------+1 row in set
?
?