mysql 实用函数

mysql> create table sy(

    -> num int);

Query OK, 0 rows affected (0.03 sec)

 

mysql> alter table sy add column xiaoshu float;

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert sy (num,xiaoshu)values(1,2.0001);

Query OK, 1 row affected (0.00 sec)

//CEIL()    进一取整

mysql> select ceil(xiaoshu) from sy;

+---------------+

| ceil(xiaoshu) |

+---------------+

|             3 |

+---------------+

1 row in set (0.01 sec)

//DIV 整数除法

mysql> select xiaoshu div 2 from sy;

+---------------+

| xiaoshu div 2 |

+---------------+

|             1 |

+---------------+

//FLOOR() 舍一取整

mysql> select floor(xiaoshu) from sy;

+----------------+

| floor(xiaoshu) |

+----------------+

|              2 |

+----------------+

1 row in set (0.01 sec)

//MOD 取余数或取模

mysql> select xiaoshu MOD 2;

ERROR 1054 (42S22): Unknown column 'xiaoshu' in 'field list'

mysql> select xiaoshu MOD 2 from sy;

+------------------------+

| xiaoshu MOD 2          |

+------------------------+

| 0.00009989738464355469 |

+------------------------+

1 row in set (0.00 sec)

 

mysql> select 10 MOD 2;

+----------+

| 10 MOD 2 |

+----------+

|        0 |

+----------+

1 row in set (0.00 sec)

 

mysql> select MOD(10,2);

+-----------+

| MOD(10,2) |

+-----------+

|         0 |

+-----------+

1 row in set (0.00 sec)

//POWER() 幂运算

mysql> select power (num,3) from sy;

+---------------+

| power (num,3) |

+---------------+

|             1 |

+---------------+

1 row in set (0.01 sec)

 

mysql> select power (xiaoshu,3) from sy;

+-------------------+

| power (xiaoshu,3) |

+-------------------+

| 8.001198828493644 |

+-------------------+

1 row in set (0.00 sec)

//ROUND() 四舍五入

mysql> select round(xiaoshu) from sy;

+----------------+

| round(xiaoshu) |

+----------------+

|              2 |

+----------------+

1 row in set (0.00 sec)

 

mysql> insert sy(num,xiaoshu) values(2,2343.23425);

Query OK, 1 row affected (0.01 sec)

 

mysql> delete from sy where num=1;

Query OK, 1 row affected (0.00 sec)

//TRUNCATE    数字截取  (123.34,-2)---10

mysql> select truncate(xiaoshu,-3) from sy;

+----------------------+

| truncate(xiaoshu,-3) |

+----------------------+

|                 2000 |

+----------------------+

1 row in set (0.00 sec)

//NOW()       当前日期和时间

mysql> select now();

+---------------------+

| now()               |

+---------------------+

| 2017-05-10 20:40:25 |

+---------------------+

1 row in set (0.00 sec)

//CURDATE()   当前日期

mysql> select curdate();

+------------+

| curdate()  |

+------------+

| 2017-05-10 |

+------------+

1 row in set (0.01 sec)

//CURDATE()   当前时间

mysql> select curtime();

+-----------+

| curtime() |

+-----------+

| 20:41:17  |

+-----------+

1 row in set (0.00 sec)

//加date

mysql> select date_add('2014-10-10', interval 100 day );

+-------------------------------------------+

| date_add('2014-10-10', interval 100 day ) |

+-------------------------------------------+

| 2015-01-18                                |

+-------------------------------------------+

1 row in set (0.01 sec)

//日期差

mysql> select datediff('2017-3-4','2015-4-3');

+---------------------------------+

| datediff('2017-3-4','2015-4-3') |

+---------------------------------+

|                             701 |

+---------------------------------+

1 row in set (0.01 sec)

//转换格式

mysql> select date_format('2017-10-10','%m/%d/%y');

+--------------------------------------+

| date_format('2017-10-10','%m/%d/%y') |

+--------------------------------------+

| 10/10/17                             |

+--------------------------------------+

1 row in set (0.00 sec)

 

mysql> select date_format('2017-10-10','%m/%d/%Y');

+--------------------------------------+

| date_format('2017-10-10','%m/%d/%Y') |

+--------------------------------------+

| 10/10/2017                           |

+--------------------------------------+

1 row in set (0.00 sec)

//md5 转换

mysql> select md5(xiaoshu) from sy;

+----------------------------------+

| md5(xiaoshu)                     |

+----------------------------------+

| e93b820d3439cdae3a9e977f42f63b48 |

+----------------------------------+

1 row in set (0.01 sec)

//设置新密码

mysql> set password = password('xinmima')