首页 > mysql教程 > 正文

mysql 常用的三类函数

原创 2018-05-16 15:53:13 0 134
本篇文章主要介绍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)

相关推荐:

推荐MySQL常用函数+福利

PHP中的MYSQL常用函数php下操作数据库必备

PHP中的MYSQL常用函数(php下操作数据库必备)_PHP教程

以上就是mysql 常用的三类函数的详细内容,更多请关注php中文网其它相关文章!

  • 相关标签:MYSQL 函数 用的
  • 本文原创发布php中文网 ,转载请注明出处,感谢您的尊重!
  • 相关文章


  • Mysql的底层封装
  • MySQL 主键与索引的联系与区别分析_Mysql
  • mysql5.5数据库data目录迁移方法详解_MySQL
  • 理解Mysql prepare预处理语句_MySQL
  • mysql 常用的三类函数
  • 网友评论

    文明上网理性发言,请遵守 新闻评论服务协议

    我要评论
    独孤九贱(4)_PHP视频教程

    独孤九贱(4)_PHP视频教程

    江湖传言:PHP是世界上最好的编程语言。真的是这样吗?这个梗究竟是从哪来的?学会本课程,你就会明白了。 PHP中文网出品的PHP入门系统教学视频,完全从初学者的角度出发,绝不玩虚的,一切以实用、有用...

    • PeterZhu
    • 2017-03-20 22:47:17
    • 点击数(110601)

    独孤九贱(5)_ThinkPHP5视频教程

    独孤九贱(5)_ThinkPHP5视频教程

    ThinkPHP是国内最流行的中文PHP开发框架,也是您Web项目的最佳选择。《php.cn独孤九贱(5)-ThinkPHP5视频教程》课程以ThinkPHP5最新版本为例,从最基本的框架常识开始,将...

    • PeterZhu
    • 2017-05-16 12:03:57
    • 点击数(108468)

    独孤九贱(1)_HTML5视频教程

    独孤九贱(1)_HTML5视频教程

    《php.cn原创html5视频教程》课程特色:php中文网原创幽默段子系列课程,以恶搞,段子为主题风格的php视频教程!轻松的教学风格,简短的教学模式,让同学们在不知不觉中,学会了HTML知识。 ...

    • PeterZhu
    • 2017-03-13 10:15:11
    • 点击数(83305)

    ThinkPHP5实战之[教学管理系统]

    ThinkPHP5实战之[教学管理系统]

    本套教程,以一个真实的学校教学管理系统为案例,手把手教会您如何在一张白纸上,从零开始,一步一步的用ThinkPHP5框架快速开发出一个商业项目。

    • PeterZhu
    • 2017-07-24 16:48:56
    • 点击数(82474)

    PHP入门视频教程之一周学会PHP

    PHP入门视频教程之一周学会PHP

    所有计算机语言的学习都要从基础开始,《PHP入门视频教程之一周学会PHP》不仅是PHP的基础部分更主要的是PHP语言的核心技术,是学习PHP必须掌握的内容,任何PHP项目的实现都离不开这部分的内容,通...

    • 墨辰丷

      全栈工程师

    • 认证0级讲师
    • 2058篇
      文章总数
    • 134
      文章总浏览数

    相关视频教程

  • javascript初级视频教程 javascript初级视频教程
  • jquery 基础视频教程 jquery 基础视频教程
  • javascript三级联动视频教程 javascript三级联动视频教程
  • 独孤九贱(3)_JavaScript视频教程 独孤九贱(3)_JavaScript视频教程
  • 独孤九贱(6)_jQuery视频教程 独孤九贱(6)_jQuery视频教程
  • 相关视频章节