MySQL函数

刘超 12天前 ⋅ 4224 阅读   编辑

说明

  hive、phoenix、mysql、oracle、sparksql、flinksql函数

目录

  1、数学函数
  2、字符串函数
  3、日期和时间函数
  4、条件判断函数
  5、行列转换函数
  6、系统信息函数
  7、加密函数
  8、格式化函数

 
一、数学函数

数学函数主要用于处理数字,包括整型、浮点数等
mysql版本 函数 作用 示例 说明
abs(x)  返回x的绝对值 select abs(-1) -- 返回1
ceil(x),ceiling(x)  返回大于或等于x的最小整数  select ceil(1.5) -- 返回2
floor(x) 返回小于或等于x的最大整数 select floor(1.5) -- 返回1
rand() 返回0->1的随机数 select rand() --0.93099315644334
rand(x) 返回0->1的随机数,x值相同时返回的随机数相同 select rand(2) --1.5865798029924
sign(x) 返回x的符号,x是负数、0、正数分别返回-1、0和1 select sign(-10) -- (-1)
pi() 返回圆周率(3.141593) select pi() --3.141593
truncate(x,y) 返回数值x保留到小数点后y位的值(与round最大的区别是不会进行四舍五入) select truncate(1.23456,3) -- 1.234
round(x) 返回离x最近的整数 select round(1.23456) --1
round(x,y) 保留x小数点后y位的值,但截断时要进行四舍五入 select round(1.23456,3) -- 1.235
pow(x,y).power(x,y) 返回x的y次方 select pow(2,3) -- 8
sqrt(x) 返回x的平方根 select sqrt(25) -- 5
exp(x) 返回e的x次方 select exp(3) -- 20.085536923188
mod(x,y)  返回x除以y以后的余数 select mod(5,2) -- 1
log(x) 返回自然对数(以e为底的对数) select log(20.085536923188) -- 3
log10(x) 返回以10为底的对数 select log10(100) -- 2
radians(x) 将角度转换为弧度  select radians(180) -- 3.1415926535898
degrees(x) 将弧度转换为角度 select degrees(3.1415926535898) -- 180
sin(x) 求正弦值(参数是弧度) select sin(radians(30)) -- 0.5
asin(x)  求反正弦值(参数是弧度)
cos(x)  求余弦值(参数是弧度)
acos(x)  求反余弦值(参数是弧度)
tan(x)  求正切值(参数是弧度)
atan(x) atan2(x)  求反正切值(参数是弧度)
cot(x)  求余切值(参数是弧度)

二、字符串函数

mysql版本 字符串函数是mysql中最常用的一类函数,字符串函数主要用于处理表中的字符串
函数 作用 示例 说明
char_length(s)  返回字符串s的字符数 select char_length('你好123') -- 5
length(s) 返回字符串s的长度 select length('你好123') -- 9
concat(s1,s2,...)  将字符串s1,s2等多个字符串合并为一个字符串 select concat('12','34') -- 1234
concat_ws(x,s1,s2,...)  同concat(s1,s2,...)函数,但是每个字符串直接要加上x select concat_ws('@','12','34') -- 12@34
insert(s1,x,len,s2) 将字符串s2替换s1的x位置开始长度为len的字符串 select insert('12345',1,3,'abc') -- abc45
upper(s),ucaase(s)  将字符串s的所有字母变成大写字母 select upper('abc') -- abc
lower(s),lcase(s)  将字符串s的所有字母变成小写字母 select lower('abc') -- abc
left(s,n) 返回字符串s的前n个字符 select left('abcde',2) -- ab
right(s,n) 返回字符串s的后n个字符 select right('abcde',2) -- de
lpad(s1,len,s2) 字符串s2来填充s1的开始处,使字符串长度达到len select lpad('abc',5,'xx') -- xxabc
rpad(s1,len,s2) 字符串s2来填充s1的结尾处,使字符串的长度达到len select rpad('abc',5,'xx') -- abcxx
ltrim(s)  去掉字符串s开始处的空格
rtrim(s)  去掉字符串s结尾处的空格
trim(s)  去掉字符串s开始和结尾处的空格
trim(s1 from s) 去掉字符串s中开始处和结尾处的字符串s1 select trim('@' from '@@abc@@') -- abc
repeat(s,n) 将字符串s重复n次 select repeat('ab',3) -- ababab
space(n)  返回n个空格
replace(s,s1,s2) 将字符串s2替代字符串s中的字符串s1 select replace('abc','a','x') --xbc
strcmp(s1,s2) 比较字符串s1和s2
substring(s,n,len) 获取从字符串s中的第n个位置开始长度为len的字符串
mid(s,n,len)  同substring(s,n,len)
locate(s1,s),position(s1 in s)  从字符串s中获取s1的开始位置 select locate('b', 'abc') -- 2
instr(s,s1) 从字符串s中获取s1的开始位置 select instr('abc','b') -- 2
reverse(s) 将字符串s的顺序反过来 select reverse('abc') -- cba
elt(n,s1,s2,...) 返回第n个字符串 select elt(2,'a','b','c') -- b
export_set(x,s1,s2) 返回一个字符串,在这里对于在“bits”中设定每一位,你得到一个“on”字符串,并且对于每个复位(reset)的位,你得到一个 “off”字符串。每个字符串用“separator”分隔(缺省“,”),并且只有“bits”的“number_of_bits” (缺省64)位被使用 select export_set(5,'y','n',',',4) -- y,n,y,n
field(s,s1,s2...)  返回第一个与字符串s匹配的字符串位置 select field('c','a','b','c') -- 3
find_in_set(s1,s2) 返回在字符串s2中与s1匹配的字符串的位置
make_set(x,s1,s2) 返回一个集合 (包含由“,”
字符分隔的子串组成的一个 字符串),由相应的位在bits集合中的的字符串组成。str1对应于位0,str2对 应位1,等等
select make_set(1|4,'a','b','c'); -- a,c
substring_index

返回从字符串str的第count个出现的分隔符delim之后的子串。
1、如果count是正数,返回第count个字符左边的字符串。
2、如果count是负数,返回第(count的绝对值(从右边数))个字符右边的字符串。

select substring_index('a*b','*',1) -- a
select substring_index('a*b','*',-1) -- b
select substring_index(substring_index('a*b*c*d*e','*',3),'*',-1) -- c
load_file(file_name)

读入文件并且作为一个字符串返回文件内容。文件必须在服务器上,你必须指定到文件的完整路径名,而且你必须有file权 限。文件必须所有内容都是可读的并且小于max_allowed_packet。 如果文件不存在或由于上面原因之一不能被读出,函数返回null

三、日期时间函数

mysql版本 mysql的日期和时间函数主要用于处理日期时间
函数 作用 示例 说明
curdate(),current_date()  返回当前日期 select curdate()
->2014-12-17
curtime(),current_time 返回当前时间 select curtime()
->15:59:02

now(),current_timestamp(),localtime(),

sysdate(),localtimestamp()

返回当前日期和时间 select now()
->2014-12-17 15:59:02
unix_timestamp() 以unix时间戳的形式返回当前时间 select unix_timestamp()
->1418803177
unix_timestamp(d) 将时间d以unix时间戳的形式返回 select unix_timestamp('2011-11-11 11:11:11')
->1320981071
from_unixtime(d) 将unix时间戳的时间转换为普通格式的时间 select from_unixtime(1320981071)
->2011-11-11 11:11:11
utc_date() 返回utc日期 select utc_date()
->2014-12-17
utc_time() 返回utc时间 select utc_time()
->08:01:45 (慢了8小时)
month(d) 返回日期d中的月份值,1->12 select month('2011-11-11 11:11:11')
->11
monthname(d) 返回日期当中的月份名称,如janyary select monthname('2011-11-11 11:11:11')
->november
dayname(d) 返回日期d是星期几,如monday,tuesday select dayname('2011-11-11 11:11:11')
->friday
dayofweek(d) 日期d今天是星期几,1星期日,2星期一 select dayofweek('2011-11-11 11:11:11')
->6
weekday(d)

日期d今天是星期几,0表示星期一,1表示星期二

week(d),weekofyear(d) 计算日期d是本年的第几个星期,范围是0->53 select week('2011-11-11 11:11:11')
->45
dayofyear(d) 计算日期d是本年的第几天 select dayofyear('2011-11-11 11:11:11')
->315
dayofmonth(d) 计算日期d是本月的第几天 select dayofmonth('2011-11-11 11:11:11')
->11
quarter(d) 返回日期d是第几季节,返回1->4 select quarter('2011-11-11 11:11:11')
->4
hour(t) 返回t中的小时值 select hour('1:2:3')
->1
minute(t) 返回t中的分钟值 select minute('1:2:3')
->2
second(t) 返回t中的秒钟值 select second('1:2:3')
->3
extract(type from d) 从日期d中获取指定的值,type指定返回的值,type可取值为microsecond、second...,更多见extract函数 select extract(minute from '2011-11-11 11:11:11')
->11
extract函数使用示例
time_to_sec(t) 将时间t转换为秒 select time_to_sec('1:12:00')
->4320
sec_to_time(s) 将以秒为单位的时间s转换为时分秒的格式 select sec_to_time(4320)
->01:12:00
to_days(d) 计算日期d距离0000年1月1日的天数 select to_days('0001-01-01 01:01:01')
->366
from_days(n) 计算从0000年1月1日开始n天后的日期 select from_days(1111)
->0003-01-16
datediff(d1,d2) 计算日期d1->d2之间相隔的天数 select datediff('2001-01-01','2001-02-02')
->-32
adddate(d,n) 计算其实日期d加上n天的日期
adddate(d,interval expr type) 计算起始日期d加上一个时间段后的日期

select adddate('2011-11-11 11:11:11',1)
->2011-11-12 11:11:11 (默认是天)

select adddate('2011-11-11 11:11:11', interval 5 minute)
->2011-11-11 11:16:11 (type的取值与上面那个列出来的函数类似)

date_add(d,interval expr type)
subdate(d,n) 日期d减去n天后的日期 select subdate('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (默认是天)
subdate(d,interval expr type) 日期d减去一个时间段后的日期 select subdate('2011-11-11 11:11:11', interval 5 minute)
->2011-11-11 11:06:11 (type的取值与上面那个列出来的函数类似)
addtime(t,n) 时间t加上n秒的时间 select addtime('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:16 (秒)
subtime(t,n) 时间t减去n秒的时间 select subtime('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (秒)
date_format(d,f) 按表达式f的要求显示日期d select date_format('2011-11-11 11:11:11','%y-%m-%d %r')
->2011-11-11 11:11:11 am
time_format(t,f) 按表达式f的要求显示时间t select time_format('11:11:11','%r')
11:11:11 am
get_format(type,s) 获得国家地区时间格式函数 select get_format(date,'usa')
->%m.%d.%y (注意返回的就是这个奇怪的字符串(format字符串))

四、条件判断函数

mysql版本 mysql的日期和时间函数主要用于处理日期时间
函数 作用 示例 说明
if(expr,v1,v2) 如果表达式expr成立,返回结果v1;否则,返回结果v2 select if(1 > 0,'正确','错误')    
->正确
ifnull(v1,v2) 如果v1的值不为null,则返回v1,否则返回v2 select ifnull(null,'hello word')
->hello word
case 
  when e1 then v1
  when e2 then e2
  ...
  else vn
end
case表示函数开始,end表示函数结束。如果e1成立,则返回v1,如果e2成立,则返回v2,当全部不成立则返回vn,而当有一个成立之后,后面的就不执行了 select case 
  when 1 > 0 then '1 > 0'
  when 2 > 0 then '2 > 0'
  else '3 > 0' 
 end
->1 > 0

五、行列转换函数

mysql版本
函数 作用 示例 说明
group_concat 实现行转列 group_concat使用示例

六、系统信息函数

mysql版本 系统信息函数用来查询mysql数据库的系统信息
函数 作用 示例 说明
version() 返回数据库的版本号 select version()
->5.0.67-community-nt
connection_id() 返回服务器的连接数
database()、schema 返回当前数据库名

user()、system_user()、session_user()、

current_user()、current_user

返回当前用户
charset(str) 返回字符串str的字符集
collation(str) 返回字符串str的字符排列方式
last_insert_id() 返回最近生成的auto_increment值

七、加密函数

mysql版本 加密函数是mysql用来对数据进行加密的函数
函数 作用 示例 说明
password(str) 该函数可以对字符串str进行加密,一般情况下,password(str)用于给用户的密码加密 select password('123')
->*23ae809ddacaf96af0fd78ed04b6a265e05aa257
md5(str) 可以对字符串str进行散列,可以用于一些普通的不需要解密的数据加密 select md5('123')
->202cb962ac59075b964b07152d234b70
encode(str,pswd_str)与decode(crypt_str,pswd_str) encode函数可以使用加密密码pswd_str来加密字符串str,加密结果是二进制数,需要使用blob类型的字段保存。该函数与decode是一对,需要同样的密码才能够解密 select encode('123','xxoo')
->;vx
select decode(';vx','xxoo')
->123

八、其他函数

mysql版本
函数 作用 示例 说明
format(x,n) format(x,n)函数可以将数字x进行格式化,将x保留到小数点后n位 select format(3.1415926,3)
->3.142
不同进制的数字进行转换
ascii(s) 返回字符串s的第一个字符的ascii码
bin(x) 返回x的二进制编码
hex(x) 返回x的十六进制编码
oct(x) 返回x的八进制编码
conv(x,f1,f2) 返回f1进制数变成f2进制数
ip地址与数字相互转换的函数
inet_aton(ip) 可以将ip地址转换为数字表示;ip值需要加上引号
inet_ntoa(n) 可以将数字n转换成ip形式 select inet_aton('192.168.0.1')
->3232235521
select inet_ntoa(3232235521)
->192.168.0.1
加锁函数和解锁函数
get_lock(name,time) 定义一个名称为nam、持续时间长度为time秒的锁。如果锁定成功,则返回1;如果尝试超时,则返回0;如果遇到错误,返回null select get_lock('mysql',10)
->1    (持续10秒)
select is_free_lock('mysql')
->1    
select release_lock('mysql')
->1
release_lock(name) 解除名称为name的锁。如果解锁成功,则返回1;如果尝试超时,返回0了如果解锁失败,返回null
is_free_lock(name) 判断是否已使用名为name的锁定。如果使用,返回0,否则,返回1
重复执行指定操作的函数
benchmark(count.expr) 将表达式expr重复执行count此,然后返回执行时间。该函数可以用来判断mysql处理表达式的速度 select benchmark(10000,now())
->0    返回系统时间1万
改变字符集的函数
convert(s using cs) 将字符串s的字符集变成cs

select charset('abc')
->utf-8    

select charset(convert('abc' using gbk))
->gbk

转换数据类型
cast(x as type) 这两个函数只对binary、char、date、datetime、time、signed integer、unsigned integer

select cast('123' as unsigned integer) + 1
->124

select '123' + 1
->124 其实mysql能默认转换

select cast(now() as date)
->2014-12-18

convert(x,type)


注意:本文归作者所有,未经作者允许,不得转载

全部评论: 0

    我有话说: