Fraction+mysql_mysql 经常使用函数

floor(x) 返回小于x的最大整数值mysql

mysql> select * from t13;

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

| id   | name   | fraction |

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

|    1 | 李四   | 65.5     |

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

1 row in set (0.00 sec)

mysql> select fraction  from t13;

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

| fraction |

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

| 65.5     |

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

1 row in set (0.00 sec)

#floor截取fraction值的整数部分.sql

mysql> select floor(fraction)  from t13;

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

| floor(fraction) |

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

|              65 |

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

1 row in set (0.00 sec)

rand()：返回0到1之间的随机数.ide

mysql> select rand();

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

| rand()            |

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

| 0.874798723379894 |

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

1 row in set (0.00 sec)

返回5-10之间的随机数.函数

mysql> select rand()*5+5;

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

| rand()*5+5        |

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

| 6.166499062596354 |

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

1 row in set (0.00 sec)

mysql> select rand()*5+5;

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

| rand()*5+5        |

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

| 7.710514615951585 |

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

1 row in set (0.00 sec)

配合floor能够获得5-10之间的随机整数.spa

mysql> select floor(rand()*5+5);

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

| floor(rand()*5+5) |

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

|                 5 |

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

1 row in set (0.00 sec)

mysql> select floor(rand()*5+5);

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

| floor(rand()*5+5) |

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

|                 7 |

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

1 row in set (0.00 sec)

position获取指定字符串所在列的位置,从1开始计数.orm

mysql> create table t18 (

-> email char(20)

-> );

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t18 values ("abc@163.com"),("12356@163.com");

Query OK, 2 rows affected (0.02 sec)

Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t18;

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

| email         |

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

| abc@163.com   |

| 12356@163.com |

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

2 rows in set (0.00 sec)

position使用的语法:字符串

mysql> select position('@' in email) from t18;

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

| position('@' in email) |

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

|                      4 |

|                      6 |

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

2 rows in set (0.00 sec)

left语法截取:it

mysql> select left(email,position('@' in email)) from t18;

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

| left(email,position('@' in email)) |

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

| abc@                               |

| 12356@                             |

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

2 rows in set (0.01 sec)

mysql> select left(email,position('@' in email)-1) from t18;

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

| left(email,position('@' in email)-1) |

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

| abc                                  |

| 12356                                |

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

2 rows in set (0.00 sec)

date_format:时间戳函数.io

mysql> select now();

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

| now()               |

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

| 2019-02-15 23:09:31 |

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

1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y%m');

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

| date_format(now(),'%Y%m') |

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

| 201902                    |

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

1 row in set (0.00 sec)

mysql> select date_format(now(),'%Y%m%d');

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

| date_format(now(),'%Y%m%d') |

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

| 20190215                    |

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

1 row in set (0.00 sec)