Mysql-七-mysql函数

Mysql-7-mysql函数

1.数学函数

  用来处理数值数据方面的运算,主要的数学函数有:绝对值函数,三角函数,对数函数,随机函数。使用数学函数过程中,如果有错误产生,该函数会返回null值。

数学函数

功能介绍

组合键

abs(x)

返回x的绝对值

整数本身

pi()

返回圆周率

返回pa的值,默认显示6

sqrtx

返回非负数x的二次方根

如为负数,返回null

mod(x,y)

返回x/y的模,即相除余数

 

ceil(x)ceiling(x)

先这两个函数功能相同,返回不小于x的最小整数值

注意正负数及小数和整数的区别

floorx

返回不大于x的最大整数值

 

rand()

返回一个随机浮点值v,0<=v<=1

 

rand(x)

返回一个随机浮点值v,0<=v<=1,参数x为整数,被用作种子值,用来产生重复序列

 

roundx

返回最接近参数x的整数,对x进行四舍五入

此函数返回值为整数

round(x,y)

返回最接近参数x的值,此值保留到小数点后面y

如果参数取负值,则将小数点保留左边y

truncate(x,y)

返回截去小数点后y位的数值x

 

signx

返回参数x的符号

x值为负,返回-1;0 0 ;正,返回1.

powx,y

返回xy次乘方的结果值

 

expx

返回ex次方后的值

 

logx

返回x的自然对数,x相对于基数e的对数

 

log10(x

返回x的基础为10的对数

 

radians

返回参数x由角转化为弧度的值

 

degreesx

返回参数x由弧度转化为角度的值

 

sinx

返回参数x的正玄值

 

asinx

返回参数x的反正玄

 

cosx

返回参数x的余弦值

 

acos

 

 

tanx

 

 

atanx

 

 

cotx

返回参数x的余切值

 
例:mysql> select mod(25,6),mod(9,3);
+-----------+----------+
| mod(25,6) | mod(9,3) |
+-----------+----------+
|         1 |        0 |
+-----------+----------+
1 row in set (0.00 sec)

 

2.字符串函数

  处理字符串数据,字符串函数主要有:计算字符串长度函数、字符串合并函数、字符串转换函数、字符串比较函数、查找指定字符串位置函数。

ASCII(char)        返回字符的ASCII码值

BIT_LENGTH(str)      返回字符串的比特长度

CONCAT(s1,s2,sn)      将s1,s2,sn连接成字符串

CONCAT_WS(sep,s1,s2,sn)  将s1,s2,sn连接成字符串,并用sep字符间隔

INSERT(str,x,y,instr)    将字符串str从第x位置开始,y个字符长的子串替换为字符串instr,返回结果

FIND_IN_SET(str,list)   分析逗号分隔的list列表,如果发现str,返回strlist中的位置

LCASE(str)LOWER(str)   返回将字符串str中所有字符改变为小写后的结果

LEFT(str,x)        返回字符串str中最左边的x个字符

LENGTH(s)         返回字符串str中的字符数

LTRIM(str)         从字符串str中切掉开头的空格

POSITION(substr,str)    返回子串substr在字符串str中第一次出现的位置

QUOTE(str)         用反斜杠转义str中的单引号

REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果

REVERSE(str)        返回颠倒字符串str的结果

RIGHT(str,x)        返回字符串str中最右边的x个字符

RTRIM(str)         返回字符串str尾部的空格

STRCMP(s1,s2)       比较字符串s1s2

TRIM(str)         去除字符串首部和尾部的所有空格

UCASE(str)UPPER(str)   返回将字符串str中所有字符转变为大写后的结果

例:mysql> select length('test'),length('测试');
+----------------+------------------+
| length('test') | length('测试') |
+----------------+------------------+
|              4 |                6 |
+----------------+------------------+

 

3.日期和时间函数

  主要处理日期和时间值,一般的日期函数除使用date类型的参数外,也可以使用datetime或timestamp类型的参数。

(1)日期时间函数

时间转化秒函数:time_to_sec
MySQL> select time_to_sec('01:01:01');
+-------------------------+
| time_to_sec('01:01:01') |
+-------------------------+
| 3661 | 
+-------------------------+
1 row in set (0.00 sec)
秒转化时间函数:sec_to_time
mysql> select sec_to_time(3661);
+-------------------+
| sec_to_time(3661) |
+-------------------+
| 01:01:01 | 
+-------------------+
1 row in set (0.00 sec)
日期转为天数函数:to_days
mysql> select to_days('0000-00-00');
+-----------------------+
| to_days('0000-00-00') |
+-----------------------+
| NULL | 
+-----------------------+
1 row in set, 1 warning (0.01 sec)
mysql> select to_days('0001-01-01');
+-----------------------+
| to_days('0001-01-01') |
+-----------------------+
| 366 | 
+-----------------------+
1 row in set (0.00 sec)
天数转化日期函数:from_days
mysql> select from_days(0);
+--------------+
| from_days(0) |
+--------------+
| 0000-00-00 | 
+--------------+
1 row in set (0.00 sec)
mysql> select from_days(366);
+----------------+
| from_days(366) |
+----------------+
| 0001-01-01 | 
+----------------+
1 row in set (0.00 sec)
字符串转换为日期函数:str_to_date
mysql> select str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s');
+--------------------------------------------------------+
| str_to_date('2013-01-01 01:21:01','%Y-%m-%d %H:%i:%s') |
+--------------------------------------------------------+
| 2013-01-01 01:21:01 | 
+--------------------------------------------------------+
1 row in set (0.00 sec)
日期转换为字符串函数:date_format
mysql> select date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s');
+----------------------------------------------------+
| date_format('2013-01-01 01:21:01','%Y%m%d %H%i%s') |
+----------------------------------------------------+
| 20130101 012101 | 
+----------------------------------------------------+
1 row in set (0.00 sec)
时间转换为字符串函数:time_format
mysql> select time_format('01:21:01','%H%i%s');
+----------------------------------+
| time_format('01:21:01','%H%i%s') |
+----------------------------------+
| 012101 | 
+----------------------------------+
1 row in set (0.00 sec)

说明:

日期时间格式参数如下:

%M 月名字(January……December)

%W 星期名字(Sunday……Saturday)

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。)

%Y , 数字, 4

%y , 数字, 2

%a 缩写的星期名字(Sun……Sat)

%d 月份中的天数, 数字(00……31)

%e 月份中的天数, 数字(0……31)

%m , 数字(01……12)

%c , 数字(1……12)

%b 缩写的月份名字(Jan……Dec)

%j 一年中的天数(001……366)

%H 小时(00……23)

%k 小时(0……23)

%h 小时(01……12)

%I 小时(01……12)

%l 小时(1……12)

%i 分钟, 数字(00……59)

%r 时间,12 小时(hh:mm:ss [AP]M)

%T 时间,24 小时(hh:mm:ss)

%S (00……59)

%s (00……59)

%p AMPM

%w 一个星期中的天数(0=Sunday ……6=Saturday

%U 星期(0……52), 这里星期天是星期的第一天

%u 星期(0……52), 这里星期一是星期的第一天

%% 一个文字“%”。

 

提取表达式的日期部分
mysql> select date(now());
+-------------+
| date(now()) |
+-------------+
| 2013-05-16 | 
+-------------+
1 row in set (0.00 sec)
返回表达式的星期索引(0=星期一,1=星期二, ……6= 星期天)。
mysql> select weekday(now());
+----------------+
| weekday(now()) |
+----------------+
| 3 | 
+----------------+
1 row in set (0.00 sec)
返回表达式是一年的第几周
mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
| 19 | 
+-------------+
1 row in set (0.00 sec)
WEEK()允许指定星期是否开始于星期天或星期一。如果第二个参数是0,星期从星期天开始,如果第二个参数是1, 从星期一开始,如下所示:
mysql> select week(now(),0);
+---------------+
| week(now(),0) |
+---------------+
| 19 | 
+---------------+
1 row in set (0.00 sec)
mysql> select week(now(),1);
+---------------+
| week(now(),1) |
+---------------+
| 20 | 
+---------------+
1 row in set (0.00 sec)
返回表达式一年中季度
mysql> select quarter(now());
+----------------+
| quarter(now()) |
+----------------+
| 2 | 
+----------------+
1 row in set (0.00 sec)
返回表达式一周的第一天
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 5 | 
+------------------+
1 row in set (0.00 sec)
返回表达式一个月的第几天
mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 16 | 
+-------------------+
1 row in set (0.00 sec)
返回表达式一年的第几天
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 136 | 
+------------------+
1 row in set (0.00 sec)
返回表达式的星期名字
mysql> select dayname(now());
+----------------+
| dayname(now()) |
+----------------+
| Thursday | 
+----------------+
1 row in set (0.00 sec)
返回表达式月份的名字
mysql> select monthname(now());
+------------------+
| monthname(now()) |
+------------------+
| May | 
+------------------+
1 row in set (0.00 sec)
mysql>
提取表达式的年份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
| 2013 | 
+-------------+
1 row in set (0.00 sec)
提取表达式的月份
mysql> select month(now());
+--------------+
| month(now()) |
+--------------+
| 5 | 
+--------------+
1 row in set (0.01 sec)
提取表达式的天数
mysql> select day(now());
+------------+
| day(now()) |
+------------+
| 16 | 
+------------+
1 row in set (0.00 sec)
提取表达式的小时
mysql> select hour(now());
+-------------+
| hour(now()) |
+-------------+
| 16 | 
+-------------+
1 row in set (0.00 sec)
提取表达式的分钟
mysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 31 | 
+---------------+
1 row in set (0.00 sec)
提取表达式的秒数
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 34 | 
+---------------+
1 row in set (0.00 sec)

 

将当前日期按照'YYYY-MM-DD' 或YYYYMMDD 格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2013-05-16 | 
+------------+
1 row in set (0.00 sec)
mysql> select curdate()+1;
+-------------+
| curdate()+1 |
+-------------+
| 20130517 | 
+-------------+
1 row in set (0.00 sec)
将当前时间以'HH:MM:SS'或 HHMMSS的格式返回,具体格式根据函数用在字符串或是数字语境中而定。 
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:43:10 | 
+-----------+
1 row in set (0.00 sec)
mysql> select curtime()+1;
+---------------+
| curtime()+1 |
+---------------+
| 164420.000000 | 
+---------------+
1 row in set (0.00 sec)
获取当前日期时间:sysdate(),now()
mysql> select sysdate(),sleep(2),sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(2) | sysdate() |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:04 | 0 | 2013-05-16 17:16:06 | 
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
mysql> select now(),sleep(2),now();
+---------------------+----------+---------------------+
| now() | sleep(2) | now() |
+---------------------+----------+---------------------+
| 2013-05-16 17:16:18 | 0 | 2013-05-16 17:16:18 | 
+---------------------+----------+---------------------+
1 row in set (2.00 sec)
从上面可以看到sysdate和now的区别,now表示语句开始的时间,而sysdate实时的获取时间

将当前日期按照'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式的值返回,具体格式根据函数用在字符串或是数字语境中而定。
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-05-16 17:19:51 | 
+---------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp+1;
+-----------------------+
| current_timestamp+1 |
+-----------------------+
| 20130516172008.000000 | 
+-----------------------+
1 row in set (0.00 sec)
unix_timestamp(),unix_timestamp(date)
如果没有参数调用,返回一个Unix时间戳记(从'1970-01-01 00:00:00'GMT开始的秒数)。如果UNIX_TIMESTAMP()用一 
个date参数被调用,它返回从'1970-01-01 00:00:00' GMT开始的秒数值。date可以是一个DATE字符串、一个DATETIME 
字符串、一个TIMESTAMP或以YYMMDD或YYYYMMDD格式的本地时间的一个数字。 
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1368696216 | 
+------------------+
1 row in set (0.00 sec)
mysql> select unix_timestamp('2013-05-16 01:01:01');
+---------------------------------------+
| unix_timestamp('2013-05-16 01:01:01') |
+---------------------------------------+
| 1368637261 | 
+---------------------------------------+
1 row in set (0.00 sec)
mysql>
FROM_UNIXTIME(unix_timestamp) 
以'YYYY-MM-DD HH:MM:SS'或YYYYMMDDHHMMSS格式返回unix_timestamp参数所表示的值,具体格式根据函数用在字符串或是数字语境中而定
mysql> select from_unixtime(1368637261);
+---------------------------+
| from_unixtime(1368637261) |
+---------------------------+
| 2013-05-16 01:01:01 | 
+---------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1368637261)+1;
+-----------------------------+
| from_unixtime(1368637261)+1 |
+-----------------------------+
| 20130516010102.000000 | 
+-----------------------------+
1 row in set (0.00 sec)
mysql> select from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s');
+-----------------------------------------------+
| from_unixtime(1368637261,'%Y-%m-%d %h:%i:%s') |
+-----------------------------------------------+
| 2013-05-16 01:01:01 | 
+-----------------------------------------------+
1 row in set (0.00 sec)
返回表达式所在月的最后一天
mysql> select last_day(now());
+-----------------+
| last_day(now()) |
+-----------------+
| 2013-05-31 | 
+-----------------+
1 row in set (0.00 sec)
日期加减运算
DATE_ADD(date,INTERVAL expr type) --加法
DATE_SUB(date,INTERVAL expr type) --减法

mysql> select date_add('2013-05-16 01:01:01',interval 1 second);
+---------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 second) |
+---------------------------------------------------+
| 2013-05-16 01:01:02 | 
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2013-05-16 01:01:01',interval 1 day);
+------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 day) |
+------------------------------------------------+
| 2013-05-17 01:01:01 | 
+------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2013-05-16 01:01:01',interval 1 minute);
+---------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 minute) |
+---------------------------------------------------+
| 2013-05-16 01:02:01 | 
+---------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2013-05-16 01:01:01',interval 1 hour);
+-------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval 1 hour) |
+-------------------------------------------------+
| 2013-05-16 02:01:01 | 
+-------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2013-05-16 01:01:01',interval '1:1' minute_second);
+--------------------------------------------------------------+
| date_add('2013-05-16 01:01:01',interval '1:1' minute_second) |
+--------------------------------------------------------------+
| 2013-05-16 01:02:02 | 
+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> select date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second);
+----------------------------------------------------------------+
| date_add('2013-05-16 01:01:01', interval '1 1:1:1' day_second) |
+----------------------------------------------------------------+
| 2013-05-17 02:02:02 | 
+----------------------------------------------------------------+
1 row in set (0.00 sec)

type值格式:

SECOND SECONDS

MINUTE 分钟 MINUTES

HOUR 时间 HOURS

DAY DAYS

MONTH MONTHS

YEAR YEARS

MINUTE_SECOND 分钟和秒 "MINUTES:SECONDS"

HOUR_MINUTE 小时和分钟 "HOURS:MINUTES"

DAY_HOUR 天和小时 "DAYS HOURS"

YEAR_MONTH 年和月 "YEARS-MONTHS"

HOUR_SECOND 小时, 分钟, "HOURS:MINUTES:SECONDS"

DAY_MINUTE , 小时, 分钟 "DAYS HOURS:MINUTES"

DAY_SECOND , 小时, 分钟, "DAYS HOURS:MINUTES:SECONDS"

DATEDIFF(expr, expr2)

返回起始时间 expr和结束时间expr2之间的天数。Exprexpr2为日期或 date-and-time 表达式。计算中只用到这些值的日期部分。

 

mysql> select datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01');
+--------------------------------------------------------+
| datediff('2013-05-16 06:01:01', '2013-05-17 01:01:01') |
+--------------------------------------------------------+
| -1 | 
+--------------------------------------------------------+
1 row in set (0.00 sec)
表示日期时间的数据类型:
date
time
year
datetime
timestamp
在使用日期时间数据比较时常用如下
mysql> select * from tab ;
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
| bbbb | 2013-04-14 17:20:36 |
| bbbb | 2013-04-13 17:20:36 |
| bbbb | 2013-04-15 17:20:36 |
+------+---------------------+
4 rows in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2013-05-14 17:10:26 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from tab where createtime > now();
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select current_timestamp;
+---------------------+
| current_timestamp |
+---------------------+
| 2013-05-14 17:10:49 |
+---------------------+
1 row in set (0.00 sec)
mysql> select * from tab where createtime > current_timestamp;
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from tab where createtime> str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s');;
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from tab where createtime between str_to_date('2013-05-14 00:00:00','%Y-%m-%d %H:%i:%s') and str_to_date('2013-05-15 00:00:00','%Y-%m-%d %H:%i:%s')
-> ;
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)
mysql> select * from tab where createtime between '2013-05-14 00:00:00' and '2013-05-15 00:00:00';
+------+---------------------+
| name | createtime |
+------+---------------------+
| aaaa | 2013-05-14 17:20:19 |
+------+---------------------+
1 row in set (0.00 sec)
mysql>

 

(2)数值函数

ABS(X) :返回表达式X的绝对值
mysql> select abs(-2);
+---------+
| abs(-2) |
+---------+
| 2 | 
+---------+
1 row in set (0.00 sec)
FLOOR(X) :返回不大于X的最大整数值
mysql> select floor(-2.45);
+--------------+
| floor(-2.45) |
+--------------+
| -3 | 
+--------------+
1 row in set (0.00 sec)
MOD(N,M):模操作,返回N被M除后的余数。
mysql> select mod(3,2);
+----------+
| mod(3,2) |
+----------+
| 1 | 
+----------+
1 row in set (0.00 sec)
RAND()/RAND(N) :返回一个随机浮点值数a,范围在 0 到1 之间 (即, 其范围为 0 ≤ a ≤ 1.0)。若已指定一个整数参数 N,则它被用作种子值,用来产生重复序列。
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.294932589209576 | 
+-------------------+
1 row in set (0.00 sec)
mysql> select rand(2);
+-------------------+
| rand(2) |
+-------------------+
| 0.655586646549019 | 
+-------------------+
1 row in set (0.00 sec)
ROUND(X)/ROUND(X,D) :返回参数X, 其值接近于最近似的整数。在有两个参数的情况下,返回 X ,其值保留到小数点后D位,而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D位,可将D设为负值。
mysql> select round(2.4 );
+-------------+
| round(2.4 ) |
+-------------+
| 2 | 
+-------------+
1 row in set (0.00 sec)
mysql> select round(2.432,2 );
+-----------------+
| round(2.432,2 ) |
+-----------------+
| 2.43 | 
+-----------------+
1 row in set (0.00 sec)
mysql> select round(12.432,-1 );
+-------------------+
| round(12.432,-1 ) |
+-------------------+
| 10 | 
+-------------------+
1 row in set (0.00 sec)

 

3.控制流函数(又称条件判断函数)

控制流函数

功能介绍

使用说明

if(expr,v1,v2)

返回表达式expr得到不同运算结果时对应的值。若exprtrueexpr<>0 and expr<>null),则if()返回值为v1,否则返回v2

if()的返回值为数值型或字符串型,具体情况视其所在语境而定。

ifnull(v1,v2)

返回参数v1v2的值。假如v1不为null,则返回值为v1,否则返回值为v2

返回值为数值型或字符串型,具体情况视其所在语境而定。

case

 

 

 

 

 
例:mysql> select if(3<2,1,0),if(3>2,1,0);
+-------------+-------------+
| if(3<2,1,0) | if(3>2,1,0) |
+-------------+-------------+
|           0 |           1 |
+-------------+-------------+
1 row in set (0.00 sec)

 

4.系统信息函数:包括系统库的版本号、当前用户和链接数、系统字符集、最后一个自动生成的值等。

系统信息函数

功能介绍

使用的说明

version()

返回当前版本号的字符串

 

connection_id()

返回服务器当前用户的链接次数

每个连接都有各自唯一的id,登录次数不同,返回的值也不同

processlist

show processlist输出结果显示正在运行的线程,不仅可以查看当前所有的链接数,还可以查看连接状态。

show processlist只能列出100条连接,如果查看全部show full processlist

database()

显示目前正在使用的数据库名称

 

user(),current_user(),

system_user(),session_

user

获取当前用户名的函数

 

charsetstr

获取字符串的字符集函数,返回参数字符串str使用的字符集

 

collationstr

返回参数字符串str的排列方式

 

last_insert_id()

获取最后一个自动生成的id值得函数。

 

 

5.加密函数:对数据进行加密和解密处理,保证数据表中某些重要数据不被别人窃取,保证安全。

加密函数

功能介绍

使用说明

password(str)

加密函数。原明文密码str,并返回加密后的密码字符串

当参数为null时,返回null

md5(str

为参数字符串计算出一个md5 128比特检验和,该值以32位十六制数字的二进制字符串形式返回

strnull,返回null

encode(str,pswd_str)

使用参数pswd_str作为秘钥,加密参数str

要使用decode()进行解密

decode(crypt_str,pswd_str)

解密函数。使用参数pswd_str作为秘钥,解密参数加密字符串crypt_str

 
例:mysql> select password('cairui');
+-------------------------------------------+
| password('cairui')                        |
+-------------------------------------------+
| *5E4CC0E31193A3717D105C9B98F79988347A025D |
+-------------------------------------------+
1 row in set (0.00 sec)

相关内容推荐