三类MySQL_mysql 常用的三类函数

本篇文章主要介绍mysql 常用的三类函数，感兴趣的朋友参考下，希望对大家有所帮助。

一、字符串类。

注：mysql在处理字符串时，字符下标从1开始。

1、concat(string1, string2, ......); //连接字符串

mysql> select concat('leng', 'xue', 'gang') as name;

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

| name |

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

| lengxuegang |

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

1 row in set (0.00 sec)

2、instr(string, substring); //返回substring首次在string中出现的位置，不存在返回0

mysql> select instr('lengxuegang', 'xue');

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

| instr('lengxuegang', 'xue') |

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

| 5 |

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

1 row in set (0.00 sec)

mysql> select instr('lengxuegang', 'none');

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

| instr('lengxuegang', 'none') |

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

| 0 |

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

1 row in set (0.00 sec)

3、lcase(string); //转换为小写

mysql> select lcase('LengxueGang');

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

| lcase('LengxueGang') |

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

| lengxuegang |

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

1 row in set (0.00 sec)

4、left(string, length); //从string左边起取length个字符

mysql> select left('lengxuegang', 4);

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

| left('lengxuegang', 4) |

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

| leng |

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

1 row in set (0.01 sec)

5、length(string); //返回string的长度

mysql> select length('lengxuegang');

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

| length('lengxuegang') |

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

| 11 |

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

1 row in set (0.25 sec)

6、locate(substring, string, [start_position]); //从start_position出开始查找，返回substring在string中首次出现的位置。其功能与instr类似，不过注意string与substring的位置是不一样的。

mysql> select locate('leng', 'lengxueganglengxuegang', 4);

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

| locate('leng', 'lengxueganglengxuegang', 4) |

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

| 12 |

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

1 row in set (0.00 sec)

7、ltrim(string); //去除左边的空格

mysql> select ltrim(' leng');

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

| ltrim(' leng') |

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

| leng |

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

1 row in set (0.00 sec)

8、repeat(string, count); //重复string count次

mysql> select repeat('leng', 4);

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

| repeat('leng', 4) |

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

| lenglenglengleng |

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

1 row in set (0.00 sec)

9、replace(string, search_str, replace_str); //在string中将search_str替换为replace_str

mysql> select replace('lengxueganglengxuegang', 'leng', 'cheng');

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

| replace('lengxueganglengxuegang', 'leng', 'cheng') |

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

| chengxuegangchengxuegang |

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

1 row in set (0.05 sec)

10、rtrim(string); //去除右端空格

mysql> select rtrim('leng ');

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

| rtrim('leng ') |

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

| leng |

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

1 row in set (0.00 sec)

11、strcmp(string1， string2); //比较两个字符串大小，按大小关系分别返回1、0、-1

mysql> select strcmp('leng', 'cheng');

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

| strcmp('leng', 'cheng') |

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

| 1 |

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

1 row in set (0.04 sec)

mysql> select strcmp('cheng', 'leng');

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

| strcmp('cheng', 'leng') |

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

| -1 |

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

1 row in set (0.00 sec)

mysql> select strcmp('leng', 'leng');

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

| strcmp('leng', 'leng') |

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

| 0 |

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

1 row in set (0.00 sec)

12、substring(string, start_pos, length); //从string的start_pos开始，取length个字符

mysql> select substring('lengxuegang', 5, 3);

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

| substring('lengxuegang', 5, 3) |

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

| xue |

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

1 row in set (0.00 sec)

13、trim(); //去除字符串两端空格

mysql> select trim(' leng ');

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

| trim(' leng ') |

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

| leng |

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

1 row in set (0.00 sec)

14、ucase(string); //转换为大写

mysql> select ucase('lengxuegang');

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

| ucase('lengxuegang') |

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

| LENGXUEGANG |

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

1 row in set (0.00 sec)

15、right(string, length); //取string右边length个字符

mysql> select right('lengxuegang', 4);

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

| right('lengxuegang', 4) |

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

| gang |

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

1 row in set (0.00 sec)

16、space(count); //生成count个空格

mysql> select space(5);

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

| space(5) |

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

| |

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

1 row in set (0.00 sec)

17、lpad(string, length, pad); //在string的左端填充pad，直到其长度达到length

mysql> select lpad('leng', 10, 'dacb');

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

| lpad('leng', 10, 'dacb') |

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

| dacbdaleng |

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

1 row in set (0.00 sec)

18、rpad(); //在string的右端填充pad，直到其长度达到length

mysql> select rpad('leng', 10, 'dacb');

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

| rpad('leng', 10, 'dacb') |

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

| lengdacbda |

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

1 row in set (0.00 sec)

19、coalesce(value1, value2, ...) 返回第一个非null值，如果全为null，则返回null

mysql> select coalesce(null, 1, 2);

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

| coalesce(null, 1, 2) |

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

| 1 |

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

1 row in set (0.03 sec)

二、数学类

1、abs(num); //返回绝对值

mysql> select abs(-3.5);

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

| abs(-3.5) |

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

| 3.5 |

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

1 row in set (0.03 sec)

2、bin(decimal_num); //十进制转二进制

mysql> select bin(12);

+---------+

| bin(12) |

+---------+

| 1100 |

+---------+

1 row in set (0.05 sec)

3、ceiling(num); //向上取整

mysql> select ceiling(3.4);

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

| ceiling(3.4) |

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

| 4 |

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

1 row in set (0.00 sec)

mysql> select ceiling(-3.4);

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

| ceiling(-3.4) |

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

| -3 |

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

1 row in set (0.00 sec)

4、conv(num, from_base, to_base); //进制转换

mysql> select conv(10, 10, 2);

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

| conv(10, 10, 2) |

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

| 1010 |

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

1 row in set (0.00 sec)

5、floor(num); //向下取整

mysql> select floor(3.6);

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

| floor(3.6) |

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

| 3 |

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

1 row in set (0.00 sec)

mysql> select floor(-3.6);

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

| floor(-3.6) |

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

| -4 |

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

1 row in set (0.00 sec)

6、least(num1, num2, num3, ......); //取最小值

mysql> select least(10, 4, -4, 0);

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

| least(10, 4, -4, 0) |

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

| -4 |

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

1 row in set (0.10 sec)

7、mod(); //取余

mysql> select mod(10, 3);

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

| mod(10, 3) |

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

| 1 |

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

1 row in set (0.00 sec)

8、power(num, power); //幂运算

mysql> select power(3, 3);

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

| power(3, 3) |

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

| 27 |

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

1 row in set (0.08 sec)

9、rand([seed]); //随机数

mysql> select rand();

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

| rand() |

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

| 0.10342728263086 |

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

1 row in set (0.00 sec)

mysql> select rand();

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

| rand() |

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

| 0.98467650821868 |

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

1 row in set (0.00 sec)

10、round(number, [decimals]); //四舍五入，decimals为小数位数

mysql> select round(1.2345);

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

| round(1.2345) |

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

| 1 |

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

1 row in set (0.00 sec)

mysql> select round(1.2345, 3);

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

| round(1.2345, 3) |

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

| 1.235 |

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

1 row in set (0.00 sec)

11、sign(number); //返回符号，正负或0

mysql> select sign(0);

+---------+

| sign(0) |

+---------+

| 0 |

+---------+

1 row in set (0.00 sec)

mysql> select sign(2);

+---------+

| sign(2) |

+---------+

| 1 |

+---------+

1 row in set (0.00 sec)

mysql> select sign(-2);

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

| sign(-2) |

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

| -1 |

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

1 row in set (0.00 sec)

12、sqrt(num); //开平方

mysql> select sqrt(3);

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

| sqrt(3) |

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

| 1.7320508075689 |

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

1 row in set (0.00 sec)

13、greatest(value1, value2, ...); //取最大值

mysql> select greatest(2, 3, 10);

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

| greatest(2, 3, 10) |

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

| 10 |

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

1 row in set (0.00 sec)

三、日期时间类

1、current_date(); //返回当前日期

mysql> select current_date();

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

| current_date() |

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

| 2012-07-01 |

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

1 row in set (0.04 sec)

2、current_time(); //返回当前时间

mysql> select current_time();

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

| current_time() |

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

| 02:05:41 |

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

1 row in set (0.00 sec)

3、current_timestamp(); //返回当前时间戳

mysql> select current_timestamp();

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

| current_timestamp() |

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

| 2012-07-01 02:06:12 |

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

1 row in set (0.04 sec)

4、now(); //返回当前时间

mysql> select now();

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

| now() |

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

| 2012-07-01 02:06:57 |

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

1 row in set (0.00 sec)

相关推荐：