一、数学函数
函数 | 说明 |
---|---|
abs(v) | 返回v的绝对值 |
floor(v) | 返回不大于v的最大整数(去掉小数) |
ceil(v) | 返回不小于v的最小整数(有效数进1) |
truncate(v,n) | 对v保留n位小数精度 |
round(v) | 保留整数,四舍五入 |
round(v,n) | 保留v的n位小数,四舍五入 |
rand() | 返回0~1的随机数 |
sign(v) | 正数返回1,负数返回-1,0返回0 |
pi() | 返回圆周率的值,保留7位小数 |
pow(x,y) | 返回x的y次乘方值 |
sqrt(x) | 返回非负数x的二次方根 |
exp(n) | 返回e的n次方 |
mod(n,m) | 返回n除m的余数 |
log(v) | 返回v的自然对数(v相对于基数2的对数) |
log10(v) | 返回v的基数为10的对数 |
redians(v) | 角度转弧度 |
degrees(v) | 弧度转角度 |
二、字符串函数
函数 | 说明 |
---|---|
char_length(v) | 返回字符串的字符长度 |
length(v) | 返回字符串的字节长度(一个汉字三个字节) |
concat(s1,s2,…) | 字符串拼接(任意一个为NULL则返回值为NULL) |
concat_ws(x,s1,s2,…) | 间隔符x拼接字符串 |
group_concat(FIELD_NAME) | 行转列(将字段名对应列的值以逗号拼接成一个字符串对象) |
insert(s1,x,len,s2) | 从s1的x位置开始,将长度为len的字符串替换成s2(删除/插入/替换) |
lower(v) | 字符串中字母全部转换为小写形式 |
upper(v) | 字符串中字母全部转换为大写形式 |
left(v,len) | 从v的左边开始截取长度为len的字符串 |
right(v,len) | 从v的右边开始截取长度为len的字符串 |
mid(v,x,len) | 从v的x位置开始截取长度为len的字符串 |
lpad(s1,len,s2) | 从s1的左侧用s2填充至len字符长度(若s1长度大于len则返回值缩短至len字符) |
rpad(s1,len,s2) | 从s1的右侧用s2填充至len字符长度(若s1长度大于len则返回值缩短至len字符) |
ltrim(v) | 清空字符串v左侧所有空格 |
rtrim(v) | 清空字符串v右侧所有空格 |
trim(v) | 清空字符串v两端所有空格 |
trim(s from v) | 清空字符串v两端所有字符串s |
repeat(v,n) | 返回将v重复n次组成的字符串 |
space(n) | 返回一个由n个空格组成的字符串 |
repalce(v,s1,s2) | 将s中所有的s1替换为s2 |
strcmp(s1,s2) | s1<s2返回-1,s1=s2返回0,s1>s2返回1 |
substring(s,x,len) | 等同于mid(s,x,len) |
substring_index(v,x,n) | 以x作为分隔符,从v左侧截取第n个分隔符之前的字符串 |
locate(s1,s2) | 返回s1在s2中的开始位置 |
reverse(v) | 返回将v翻转之后的字符串 |
elt(n,s1,s2,…) | 返回第n个字符串 |
field(v,s1,s2,…) | 返回第一个与v匹配的字符串的位置 |
find_in_set(v,‘s1,s2,…’) | 返回包含多个字符串的对象中与v匹配的字符串位置 |
make_set(x,s1,s2,…) | 按x的二进制数从s1,s2,…中选取字符串 |
三、日期函数
函数 | 说明 |
---|---|
curdate() | 返回当前日期的年月日(yyyy-MM-dd) |
curtime() | 返回当前日期的时分秒(HH:mm:ss) |
now() | 返回当前日期的年月日时分秒(yyyy-MM-dd HH:mm:ss) |
unix_timestamp() | 获取当前时间的毫秒数 |
unix_timestamp(date) | 获取指定日期的毫秒数 |
from_unix(date) | 将毫秒数转化为时间格式 |
utc_date() | 当前UTC(世界标准时间)的日期值(格林尼治时间,中国地区为UTC+8) |
utc_time() | 当前UTC(世界标准时间)的时间值(格林尼治时间,中国地区为UTC+8) |
year(date) | 返回日期的年份值 |
month(date) | 返回日期的月份值 |
day(date) | 返回日期的日值 |
hour(date) | 返回日期的小时值 |
minute(date) | 返回日期的分钟值 |
second(date) | 返回日期的秒钟值 |
milliseconds(date) | 返回日期的毫秒值 |
weekofyear(date) | 本年第几周(1~53) |
weekday(date) | 本周第几天(周日为第一天) |
dayofweek(date) | 本周第几天(周一为第一天) |
dayofyear(date) | 本年第几天 |
quarter(date) | 本年第几季度(1~4) |
datediff(d1,d2) | d1至d2之间的天数 |
adddate(d,n) subdate(d,n) | 日期d加上n天的日期 日期d减去n天的日期 |
adddate(date,INTERVAL expr TYPE) subdate(date,INTERVAL expr TYPE) | 对日期增加指定类型的指定时间间隔并返回新的日期 对日期减少指定类型的指定时间间隔并返回新的日期 |
date_format(date,format) | 按照format的指定格式显示日期 |
四、系统级函数
函数 | 说明 |
---|---|
if(expr,v1,v2) | 如果表达式expr成立(true),则返回v1,否则返回v2 |
ifnull(v1,v2) | 如果v1不为空,则返回v1,否则返回v2 |
case when expr1 then v1 [when expr2 then v2][else vn] end | case表示函数开始,end表示函数结束,如果表达式expr1成立,则返回v1, 如果表达式expr2成立,则返回v2,以此类推,最后else返回vn |
case expr when e1 then v1 [when e2 then v2][else vn] end | case表示函数开始,end表示函数结束,如果表达式expr取值e1,则返回v1, 如果表达式expr取值e2,则返回v2,以此类推,最后else返回vn |
database() | 获取当前数据库名 |
password(str) | 对字符串str进行不可逆加密 |
md5(str) | 对字符串str进行md5加密 |
encode(src_str,pass_str) | 用字符串pass_str加密字符串src_str,加密后的结果是一个二进制数 |
decode(crpyt_str,pass_str) | 用字符串pass_str解密字符串crpyt_str |
conv(N,from_base,to_base) | 将数值N由from_base进制转换为to_base进制,返回字符串类型 |
五、聚合函数
函数 | 说明 |
---|---|
min(FIELD_NAME) | 取字段名对应列中的最小值 |
max(FIELD_NAME) | 取字段名对应列中的最大值 |
sum(FIELD_NAME) | 对字段名对应列的数据求和 |
avg(FIELD_NAME) | 对字段名对应列的数据求平均值 |
count(1/[distinct] FIELD_NAME) | 对字段名对应列的数据进行[去重]计数 |
六、个别函数解析
1.group_concat
group_concat([distinct] FIELD_NAME1 [order by FIELD_NAME2 ASC/DESC][separator 'sep'])
下面表中一个老师对应多个科目,导致老师名字出现多次,这时可以使用group_concat将老师名字去重,同时还能显示每个老师对应的教学科目
代码如下:
mysql> select
-> C.cid,T.tname,C.cname
-> from course C
-> inner join teacher T on T.tid=C.teacher_id
-> order by C.cid;
+ -------- + -------- +
| tname | cname |
+ -------- + -------- +
| 张三 | 生物 |
| 张三 | 体育 |
| 李四 | 物理 |
| 王五 | 化学 |
| 李杰 | 美术 |
| 李四 | 音乐 |
| 王五 | 语文 |
| 李杰 | 数学 |
| 李四 | 地理 |
| 张三 | 研究 |
+ -------- + -------- +
mysql> select
-> distinct T.tname,group_concat(C.cname)
-> from course C
-> inner join teacher T on T.tid=C.teacher_id
-> group by T.tname;
+ -------- + -------------------------------- +
| tname | group_concat(C.cname) |
+ -------- + -------------------------------- +
| 张三 | 生物,体育,研究 |
| 李四 | 物理,音乐,地理 |
| 李杰 | 美术,数学 |
| 王五 | 化学,语文 |
+ -------- + -------------------------------- +
2.substring_index
substring_index(VERCHAR,SEPARATOR,NUM)
select substring_index('aa,bb,cc,dd',',',2);
+ -------- +
| aa,bb |
+ -------- +