mysql 实用函数

来源：互联网发布：淘宝炒作多久会消失编辑：程序博客网时间：2024/06/06 05:00

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')

0 0