1. 数学函数
ABS(x);
ACOS(x);
ASIN(x);
ATAN(x);
CEILING(x);
COS(x);
COT(x);
DEGREES(x);
EXP(x);
FLOOR(x);
GREATEST(x1,x2,x3...);
LEAST(x1,x2,x3,,,);
LN(x);
LOG(x,y);
MOD(x,y);
PI();
POW(x,y)或者POWER(x,y);
RAND();
RADIANS(x);
ROUND(x,y);
SIGN(x);
SQRT(x);
SIN(x);
TAN(x);返回x(弧度)的正切值
TRUNCATE(x,y);
2. 聚合函数
MySQL有一组函数是特意为求和或者对表中的数据进行集中概括而设计的,这些函数经常用在包含group by从句的select查询中,当然,它们也可以用于无group的查询
AVG(col);
COUNT(col);
MIN(col);
MAX(col);
SUM(col);
STD(col)或STDDEV(col);
VARIANCE(col);
GROUP_CONCAT(col);
3. 字符串函数
length();
trim();
concat();
4. 日期时间函数
now();
current_timestamp() localtime() localtimestamp()
sysdate();
curdate();
curtime();
utc_date(), utc_time(), utc_timestamp();
mysql> select utc_timestamp(), utc_date(), utc_time(), now()我国位于东八时区,所以本地时间 = UTC 时间 + 8 小时。
unix_timestamp();
unix_timestamp(date);
from_unixtime(unix_timestamp),from_unixtime(unix_timestamp,format)
timestamp(dt,time);
timestampadd(unit,interval,datetime_expr) --
timestampdiff(unit,datetime_expr1,datetime_expr2) --
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
select microsecond(@dt); -- 123456
set @dt = '2008-09-10 07:15:30.123456';
select extract(year from @dt); -- 2008
select extract(quarter from @dt); -- 3
select extract(month from @dt); -- 9
select extract(week from @dt); -- 36
...
select dayofweek(@dt); -- 6
select dayofmonth(@dt); -- 8
set @dt = '2008-08-08';
select week(@dt); -- 31
select week(@dt,3); -- 32
select weekofyear(@dt); -- 32
set @dt = '2008-08-08';
select dayname(@dt); -- Friday
select monthname(@dt); -- August
select last_day('2008-02-01'); -- 2008-02-29
select last_day('2008-08-08'); -- 2008-08-31
select date_add(@dt, interval 1 day); -- add 1 day
select date_add(@dt, interval 1 hour); -- add 1 hour
date_sub()
MySQL datediff(date1,date2):两个日期相减 date1 - date2,返回天数。
select datediff('2008-08-08', '2008-08-01'); -- 7
select datediff('2008-08-01', '2008-08-08'); -- -7
MySQL timediff(time1,time2):两个日期相减 time1 - time2,返回 time 差值。
select timediff('2008-08-08 08:08:08', '2008-08-08 00:00:00'); -- 08:08:08
select timediff('08:08:08', '00:00:00'); -- 08:08:08
1. 数学函数
ABS(x);
ACOS(x);
ASIN(x);
ATAN(x);
CEILING(x);
COS(x);
COT(x);
DEGREES(x);
EXP(x);
FLOOR(x);
GREATEST(x1,x2,x3...);
LEAST(x1,x2,x3,,,);
LN(x);
LOG(x,y);
MOD(x,y);
PI();
POW(x,y)或者POWER(x,y);
RAND();
RADIANS(x);
ROUND(x,y);
SIGN(x);
SQRT(x);
SIN(x);
TAN(x);返回x(弧度)的正切值
TRUNCATE(x,y);
5. 加密函数
AES_ENCRYPT(str,key);
AES_DECRYPT(str,key);
DECODE(str,key);
ENCRYPT(str,salt);
ENCODE(str,key);
MD5();
PASSWORD(str);
SHA();
6. 控制流函数
case where[test1] then[result1] ... else[defalut] end;
case[test] where[val1] then[result1]...else[default] end;
if(test,t,f);
ifnull(arg1,arg2);
nullif(arg1,arg2);
7. 格式化函数
date_format(date,fmt);
format(x,y);
inet_aton(ip);
inet_ntoa(num);
time_format(time,fmt);
8. 数据转换函数
select 1+'99';
select 1+cast('99' as signed);
9. 系统信息函数
database();
benchmark(count,expr);
connection_id();
found_rows();
get_lock(str,dur);
is_free_lock(str);
last_insert_id();
master_pos_wait(log,pos,dur);
release_lock(str);
user()/system_user();
version();