mysql 常用函数
本文共 13361 字，大约阅读时间需要 44 分钟。
字符串函数
mysql> select concat('a','b','c'),concat('a',null);+---------------------+------------------+| concat('a','b','c') | concat('a',null) |+---------------------+------------------+| abc | NULL |+---------------------+------------------+1 row in set (0.00 sec) mysql> select insert('beijing2008you',12,3,'me');+------------------------------------+| insert('beijing2008you',12,3,'me') |+------------------------------------+| beijing2008me |+------------------------------------+1 row in set (0.01 sec) mysql> select lower('BEIJING'),upper('beijing');+------------------+------------------+| lower('BEIJING') | upper('beijing') |+------------------+------------------+| beijing | BEIJING |+------------------+------------------+1 row in set (0.00 sec) mysql> select left('beijing2008',7),left('beijing2008',null),right('beijing2008',4);+-----------------------+--------------------------+------------------------+| left('beijing2008',7) | left('beijing2008',null) | right('beijing2008',4) |+-----------------------+--------------------------+------------------------+| beijing | NULL | 2008 |+-----------------------+--------------------------+------------------------+1 row in set (0.00 sec) mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');+---------------------------+---------------------------+| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |+---------------------------+---------------------------+| beijingbeijingbe2008 | beijing2008200820082 |+---------------------------+---------------------------+1 row in set (0.00 sec) mysql> select ltrim(' |beijing'),rtrim('beijing| ');+--------------------+---------------------+| ltrim(' |beijing') | rtrim('beijing| ') |+--------------------+---------------------+| |beijing | beijing| |+--------------------+---------------------+1 row in set (0.00 sec) mysql> select repeat('mysql ',3);+--------------------+| repeat('mysql ',3) |+--------------------+| mysql mysql mysql |+--------------------+1 row in set (0.00 sec) mysql> select replace('beijing_2010','_2010','2008');+----------------------------------------+| replace('beijing_2010','_2010','2008') |+----------------------------------------+| beijing2008 |+----------------------------------------+1 row in set (0.00 sec) mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');+-----------------+-----------------+-----------------+| strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |+-----------------+-----------------+-----------------+| -1 | 0 | 1 |+-----------------+-----------------+-----------------+1 row in set (0.00 sec) mysql> select trim(' $ beijing $ ');+-----------------------+| trim(' $ beijing $ ') |+-----------------------+| $ beijing $ |+-----------------------+1 row in set (0.00 sec) mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);+------------------------------+------------------------------+| substring('beijing2008',8,4) | substring('beijing2008',1,7) |+------------------------------+------------------------------+| 2008 | beijing |+------------------------------+------------------------------+1 row in set (0.00 sec) 数值函数
mysql> select abs(-0.8),abs(0.8);+-----------+----------+| abs(-0.8) | abs(0.8) |+-----------+----------+| 0.8 | 0.8 |+-----------+----------+1 row in set (0.01 sec)mysql> select ceil(-0.8),ceil(0.8);+------------+-----------+| ceil(-0.8) | ceil(0.8) |+------------+-----------+| 0 | 1 |+------------+-----------+1 row in set (0.02 sec)mysql> select floor(-0.8),floor(0.8);+-------------+------------+| floor(-0.8) | floor(0.8) |+-------------+------------+| -1 | 0 |+-------------+------------+1 row in set (0.00 sec)mysql> select mod(15,10),mod(1,11),mod(null,10);+------------+-----------+--------------+| mod(15,10) | mod(1,11) | mod(null,10) |+------------+-----------+--------------+| 5 | 1 | NULL |+------------+-----------+--------------+1 row in set (0.01 sec)mysql> select rand(),rand();+---------------------+---------------------+| rand() | rand() |+---------------------+---------------------+| 0.14735915059909147 | 0.09535266654133114 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> select ceil(100*rand()),ceil(100*rand());+------------------+------------------+| ceil(100*rand()) | ceil(100*rand()) |+------------------+------------------+| 4 | 89 |+------------------+------------------+1 row in set (0.01 sec)mysql> select round(1.1),round(1.1,2),round(1,2);+------------+--------------+------------+| round(1.1) | round(1.1,2) | round(1,2) |+------------+--------------+------------+| 1 | 1.10 | 1 |+------------+--------------+------------+1 row in set (0.01 sec)mysql> select round(1.235,2),truncate(1.235,2);+----------------+-------------------+| round(1.235,2) | truncate(1.235,2) |+----------------+-------------------+| 1.24 | 1.23 |+----------------+-------------------+1 row in set (0.00 sec)
日期和时间函数
mysql> select curdate();+------------+| curdate() |+------------+| 2016-09-21 |+------------+1 row in set (0.00 sec)mysql> select curtime();+-----------+| curtime() |+-----------+| 10:22:39 |+-----------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now() |+---------------------+| 2016-09-21 10:22:48 |+---------------------+1 row in set (0.00 sec)mysql> select unix_timestamp(now());+-----------------------+| unix_timestamp(now()) |+-----------------------+| 1474424612 |+-----------------------+1 row in set (0.00 sec)mysql> select from_unixtime(1474424612);+---------------------------+| from_unixtime(1474424612) |+---------------------------+| 2016-09-21 10:23:32 |+---------------------------+1 row in set (0.00 sec)mysql> select from_unixtime(1);+---------------------+| from_unixtime(1) |+---------------------+| 1970-01-01 08:00:01 |+---------------------+1 row in set (0.00 sec)mysql> select week(now()),year(now());+-------------+-------------+| week(now()) | year(now()) |+-------------+-------------+| 38 | 2016 |+-------------+-------------+1 row in set (0.00 sec)mysql> select hour(now()),minute(now());+-------------+---------------+| hour(now()) | minute(now()) |+-------------+---------------+| 10 | 27 |+-------------+---------------+1 row in set (0.00 sec)mysql> select monthname(now());+------------------+| monthname(now()) |+------------------+| September |+------------------+1 row in set (0.02 sec)
mysql日期和时间格式
mysql> select date_format(now(),'%M,%D,%Y');+-------------------------------+| date_format(now(),'%M,%D,%Y') |+-------------------------------+| September,21st,2016 |+-------------------------------+1 row in set (0.01 sec)
mysql日期间隔类型
mysql> select now() current, date_add(now(),interval 31 day) after31days;+---------------------+---------------------+| current | after31days |+---------------------+---------------------+| 2016-09-21 11:08:33 | 2016-10-22 11:08:33 |+---------------------+---------------------+1 row in set (0.00 sec)mysql> select now() current, date_add(now(),interval -31 day) before31days,date_add(now(),interval '-1_-2' year_month) before_oneyear_twomonth;+---------------------+---------------------+-------------------------+| current | before31days | before_oneyear_twomonth |+---------------------+---------------------+-------------------------+| 2016-09-21 11:10:34 | 2016-08-21 11:10:34 | 2015-07-21 11:10:34 |+---------------------+---------------------+-------------------------+1 row in set (0.00 sec)
mysql> select datediff('2015-07-06',now());+------------------------------+| datediff('2015-07-06',now()) |+------------------------------+| -443 |+------------------------------+1 row in set (0.00 sec) 流程函数
测试数据
mysql> create table salary(userid int,salary decimal(9,2));Query OK, 0 rows affected (0.01 sec)mysql> insert into salary values (1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);Query OK, 6 rows affected (0.01 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from salary;+--------+---------+| userid | salary |+--------+---------+| 1 | 1000.00 || 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 || 1 | NULL |+--------+---------+6 rows in set (0.00 sec)
mysql> select userid, if(salary>2000,'high','low') from salary;+--------+------------------------------+| userid | if(salary>2000,'high','low') |+--------+------------------------------+| 1 | low || 2 | low || 3 | high || 4 | high || 5 | high || 1 | low |+--------+------------------------------+6 rows in set (0.00 sec)mysql> select userid,ifnull(salary,0) from salary;+--------+------------------+| userid | ifnull(salary,0) |+--------+------------------+| 1 | 1000.00 || 2 | 2000.00 || 3 | 3000.00 || 4 | 4000.00 || 5 | 5000.00 || 1 | 0.00 |+--------+------------------+6 rows in set (0.00 sec)
mysql> select userid, case when salary<=2000 then 'low' else 'high' end from salary;+--------+---------------------------------------------------+| userid | case when salary<=2000 then 'low' else 'high' end |+--------+---------------------------------------------------+| 1 | low || 2 | low || 3 | high || 4 | high || 5 | high || 1 | high |+--------+---------------------------------------------------+6 rows in set (0.00 sec)mysql> select userid,case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end from salary;+--------+-----------------------------------------------------------------------+| userid | case salary when 1000 then 'low' when 2000 then 'mid' else 'high' end |+--------+-----------------------------------------------------------------------+| 1 | low || 2 | mid || 3 | high || 4 | high || 5 | high || 1 | high |+--------+-----------------------------------------------------------------------+6 rows in set (0.00 sec)
其他函数
mysql> select database();+------------+| database() |+------------+| test |+------------+1 row in set (0.00 sec)mysql> select version();+------------+| version() |+------------+| 5.6.31-log |+------------+1 row in set (0.00 sec)mysql> select user();+----------------+| user() |+----------------+| root@localhost |+----------------+1 row in set (0.00 sec)mysql> select inet_aton('192.168.0.7');+--------------------------+| inet_aton('192.168.0.7') |+--------------------------+| 3232235527 |+--------------------------+1 row in set (0.00 sec)mysql> select inet_ntoa(3232235527);+-----------------------+| inet_ntoa(3232235527) |+-----------------------+| 192.168.0.7 |+-----------------------+1 row in set (0.00 sec) 使用inet_aton查看192.168.1.3和192.168.1.20之间有多少个ip地址
mysql> create table ipaddr (ip varchar(15));Query OK, 0 rows affected (0.00 sec)mysql> insert into ipaddr values ('192.168.1.1'),('192.168.1.3'),('192.168.1.6'),('192.168.1.10'),('192.168.1.20'),('192.168.1.30');Query OK, 6 rows affected (0.00 sec)Records: 6 Duplicates: 0 Warnings: 0mysql> select * from ipaddr;+--------------+| ip |+--------------+| 192.168.1.1 || 192.168.1.3 || 192.168.1.6 || 192.168.1.10 || 192.168.1.20 || 192.168.1.30 |+--------------+6 rows in set (0.00 sec)mysql> select * from ipaddr where inet_aton(ip)>=inet_aton('192.168.1.3') and inet_aton(ip)<=inet_aton('192.168.1.20');+--------------+| ip |+--------------+| 192.168.1.3 || 192.168.1.6 || 192.168.1.10 || 192.168.1.20 |+--------------+4 rows in set (0.00 sec) password用户系统用户，md5用于应用
mysql> select password('123456');+-------------------------------------------+| password('123456') |+-------------------------------------------+| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |+-------------------------------------------+1 row in set (0.00 sec)mysql> select md5('123456');+----------------------------------+| md5('123456') |+----------------------------------+| e10adc3949ba59abbe56e057f20f883e |+----------------------------------+1 row in set (0.00 sec)
你可能感兴趣的文章
Ubuntu 源代码阅读和函数、变量的定位--之一
查看>>
Java - Keywords 基本数据类型 Identifier
查看>>
我的友情链接
查看>>
Core Linux 操作文档（一）
查看>>
hadoop安装过程中ubuntu系统ssh免密码登陆设置
查看>>
input按钮的background-image属性兼容性问题
查看>>
java.lang.*不用我们导入，编译器会自动给我们导入的，，，这个包是默认导入的。...
查看>>
shell 小脚本
查看>>
IE8、IE9下访问博客报不安全『博客帮助』文档
查看>>
HDU 5162
查看>>
Python 获取本机ip地址
查看>>
NO.1 关于禅道
查看>>
win-codeblocks-16.01
查看>>
资本主义系统的基本结构
查看>>
财务企业网银故障快速攻克
查看>>
谭浩强C语言下载
查看>>
我的友情链接
查看>>
weblogic 启动脚本
查看>>
lamp-配置防盗链、访问控制Directory（针对目录）、访问控制（针对单文件）
查看>>
Cacti中文版在Centos上的安装(1)
查看>>