15、MySQL函数功能

1、定义字符串全部大写、小写

mysql> select vend_name, upper(vend_name) as vend_name_upper from vendors;

mysql> select vend_name, lower(vend_name) as vend_name_lower from vendors;

2、去掉左右空格

mysql> select vend_name, ltrim(rtrim(vend_name)) from vendors;

3、trim（）和upper（）连用

mysql> select vend_name, ltrim(upper(vend_name)) from vendors;

4、字符串截位处理

mysql> select vend_name, substr(vend_name, 1, 5) from vendors;

都是从左到右顺序

mysql> select vend_name, substr(vend_name, -1, 5) from vendors;

mysql> select vend_name, substr(vend_name, -5, 5) from vendors;

5、soundex功能，英文使用

mysql> select * from customers where 
soundex(cust_contact) = soundex('Y Li');

6、日期和时间的处理

正规用法 date（）

mysql> select * from orders where date(order_date) = '2005-09-01';

between用法

mysql> select * from orders where date(order_date) between '2005-09-01' and '2005-09-30';

year month day hour minute second

mysql> select * from orders where year(order_date) = 2005 and month(order_date) = 9;

常用的时间处理函数：
current_date(),当前日期, current_time()

mysql> select vend_name, current_date() from vendors;

用到concat()函数

mysql> select vend_name, concat(current_date(),' ', current_time()) as time from vendors;

使用 ADDDATE() 函数执行日期的加操作,与DATE_ADD(date,INTERVAL expr type)功能完全相同

mysql> select vend_name, adddate(concat(current_date(),' ', current_time()), interval 30 minute) from vendors;

mysql> select vend_name, adddate(concat(current_date(),' ', current_time()), interval 30 hour) from vendors;

mysql> select vend_name, adddate(concat(current_date(),' ', current_time()), interval '1:1' minute_second) from vendors;

ADDTIME()将 expr2添加至expr 然后返回结果。 expr 是一个时间或时间日期表达式，而expr2 是一个时间表达式

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
+---------------------------------------------------------+
| ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002') |
+---------------------------------------------------------+
| 2008-01-02 01:01:01.000001                              |
+---------------------------------------------------------+

mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');

+-----------------------------------------------+
| ADDTIME('01:00:00.999999', '02:00:00.999998') |
+-----------------------------------------------+
| 03:00:01.999997                               |
+-----------------------------------------------+

datediff（）时间差函数

mysql> select datediff('2015-01-01', '2016-01-01');
+--------------------------------------+
| datediff('2015-01-01', '2016-01-01') |
+--------------------------------------+
|                                 -365 |
+--------------------------------------+

数值函数的使用，mod（）取余 Python中是 5%2

mysql> select mod(5, 2), 5 / 2;
+-----------+--------+
| mod(5, 2) | 5 / 2  |
+-----------+--------+
|         1 | 2.5000 |
+-----------+--------+

取整函数：
1、ROUND()
ROUND(X) – 表示将值 X 四舍五入为整数，无小数位
ROUND(X,D) – 表示将值 X 四舍五入为小数点后 D 位的数值，D为小数点后小数位数。若要保留 X 值小数点左边的 D 位，可将 D 设为负值。

mysql> select round(19.876);
+---------------+
| round(19.876) |
+---------------+
| 20            |
+---------------+

2、FLOOR(X)表示向下取整，只返回值X的整数部分，小数部分舍弃。 Python中的取余 5//2

mysql> select floor(2.887);
+--------------+
| floor(2.887) |
+--------------+
|            2 |
+--------------+

3、CEILING(X) 表示向上取整，只返回值X的整数部分，小数部分舍弃。

mysql> select ceiling(5.889);
+----------------+
| ceiling(5.889) |
+----------------+
|              6 |
+----------------+

7、随机数rand（）

mysql> select rand(), rand()*100;
+---------------------+-------------------+
| rand()              | rand()*100        |
+---------------------+-------------------+
| 0.26614594111791434 | 86.53402196851934 |
+---------------------+-------------------+

随机种子 rand（x） x是种子

mysql> select rand(1), rand(1)*100;
+---------------------+-------------------+
| rand(1)             | rand(1)*100       |
+---------------------+-------------------+
| 0.40540353712197724 | 40.54035371219772 |
+---------------------+-------------------+

8、开方pow（2,3）

mysql> select pow(2, 3);
+-----------+
| pow(2, 3) |
+-----------+
|         8 |
+-----------+

9、