MySQL常用函数

一、数学函数

abs（x）　　返回x的绝对值
bin（x）　　返回x的二进制（oct返回八进制，hex返回十六进制）
ceiling（x）　　返回大于x的最小整数值(向上取整)
exp（x）　　返回e（自然对数的底）的x次方
floor（x）　　返回小于x的最大整数值(向下取整)
greatest（x1，x2,...,xn）　　返回集合中最大的值
least（x1，x2,...,xn）　　返回集合中最小的值
ln（x）　　返回x的自然对数
log（x，y）　　返回x的以y为底的对数
mod（x，y）　　返回x/y的模（余数）
pi（）　　返回pi的值（圆周率）
rand（）　　返回0到1内的随机数值，可以通过提供一个参数（种子）使rand（）随机数生成器生成一个指定的值
round（x，y）　　返回参数x的四舍五入的有y位小数的值
sign（x）　　返回代表数值x的符号的值
sqrt（x）　　返回一个数的平方根
truncate（x，y）　　返回数字x截短为y位小数的结果

二、聚合函数

常用于group by从句的select查询中

avg（col）　　返回指定列的平均值
count（col）　　返回指定列中非null值的个数
min（col）　　返回指定列的最小值
max（col）　　返回指定列的最大值
sum（col）　　返回指定列的所有值之和
group_concat（col）　　返回由属于一组的列值连接组合而成的结果
--取出栏目4下面的goods_id,并把goods_id拼接起来，默认用逗号拼接
select group_concat(goods_id,'') from goods where cat_id = 4 group by cat_id; 

三、字符串函数

ascii（char）　　返回字符的ascii码值
bit_length（str）　　返回字符串的比特长度
concat（s1，s2...,sn）　　将s1，s2...，sn连接成字符串
concat_ws（sep，s1，s2...,sn）　　将s1，s2...，sn连接成字符串，并用sep字符间隔
--将字符串str从第x位置开始，y个字符长的子串替换为insert，返回结果
insert（str，x，y，insert）
+----------------------------------+
| insert('helloworld',6,5,'nihao') |
+----------------------------------+
| hellonihao                       |
+----------------------------------+
find_in_set（str，list）    分析逗号分隔的list列表，如果发现str，返回str在list中的位置
lcase（str）或lower（str）    返回将字符串str中所有字符改变为小写后的结果
left（str，x）    返回字符串str中最左的x个字符
length（s）    返回字符串str中的字节数char_length（s） 返回字符串str中的字符数
ltrim（str）    从字符串str中切掉开头的空格
position（substr in 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）    比较字符串s1和s2
trim（str）    去除字符串首部和尾部的所有空格
ucase（str）或upper（str）    返回将字符串str中所有字符转变为大写后的结果 

案例：某网站有email字段，存邮件地址，调查163,126,qq,gmail邮箱的比例

--调查邮箱后缀，把后缀想办法查出来
right (email，（length - position（'@' in email））) 

四、日期和时间函数

curdate（）或current_date（）    返回当前的日期
curtime（）或current_time（）    返回当前的时间
--返回日期date加上间隔时间int的结果（int必须按照关键字进行格式化）
--如：select date_add（current_date,interval 6 month）;
date_add（date，interval int keyword）  
date_format(date,fmt)      依照指定的fmt格式格式化日期date值
--返回日期date减去间隔时间int的结果（int必须按照关键字进行格式化）
--如：select date_sub(current_date,interval 6 month);
date_sub(date,intval int keyword)
dayofweek(date)    返回date所代表的一星期中的第几天（1~7）
dayofmouth（date）    返回date是一个月的第几天（1~31）
dayofyear（date）    返回date是一年的第几天（1~366）
dayname（date）    返回date的星期名，如：select dayname(current_date);
from_unixtime(ts,fmt)    根据指定的fmt格式，格式化unix时间戳ts
hour（time）    返回time小时值（0~23）
minute（time）    返回time的分钟值（0~59）
month（date）    返回date的月份值（1~12）
monthname（date）    返回date的月份名，如：select monthname（current_date）;
now（）    返回当前的日期和时间
quarter（date）    返回date在一年中的季度（1~4），如select quarter（current_date）
week（date）    返回日期date为一年中的第一周（0~53）
year（date）    返回日期date的年份（1000~9999） 
--按星期统计 加班的时间
select sum(num),week(dt) as week from tab1 group by week; 

五、加密函数

--返回用秘钥key对字符串str利用高级加密标准算法加密后的结果，调用aes_encrypt的结果
--是一个二进制字符串，以blob类型存储
aes_encrypt(str,key)
aes_decrypt(str,key)    返回用秘钥key对字符串str利用高级加密标准算法加密后的结果
decode（str，key）    使用key作为秘钥解密加密字符串str
--使用unixcrypt（）函数，用关键字salt（一个可以唯一确定口令的字符串）加密字符串str
encrypt（str，salt）
--使用key作为秘钥加密字符串str，调用encode()的结果是一个二进制字符串，以blob类型存储
encode（str，key）    
md5 ( )    计算字符串str的MD5校验和
password（str）    返回字符串str的加密版本，这个加密过程是不可逆转的，和unix密码加密过程使用不同的算法
sha（）    计算字符串str的安全散列算法（sha）校验和 

六、格式化函数

date_format(date,fmt)    依照字符串fmt格式化日期date值
format(x,y)    把x格式化为以逗号隔开的数字序列，y是结果的小数位数
inet_aton(ip)    返回ip地址的数字表示
inet_ntoa(num)    返回数字所代表的ip地址
time_format(time,fmt)    依照字符串fmt格式化时间time值
--其中最简单的是format（）函数，它可以把大的数值格式化为以逗号间隔的易读的序列
--example
mysql> select format(32423.3243432423,5);
+----------------------------+
| format(32423.3243432423,5) |
+----------------------------+
| 32,423.32434               |
+----------------------------+
mysql> select date_format(now(),'%w,%d,%m %y %r');
+-------------------------------------+
| date_format(now(),'%w,%d,%m %y %r') |
+-------------------------------------+
| 5,20,05 16 04:39:11 PM              |
+-------------------------------------+
mysql> select date_format(19900330,'%y-%m-%d');
+----------------------------------+
| date_format(19900330,'%y-%m-%d') |
+----------------------------------+
| 90-03-30                         |
+----------------------------------+
mysql> select inet_aton('127.0.0.1');
+------------------------+
| inet_aton('127.0.0.1') |
+------------------------+
|             2130706433 |
+------------------------+
mysql> select inet_ntoa(2130706433);
+-----------------------+
| inet_ntoa(2130706433) |
+-----------------------+
| 127.0.0.1             |
+-----------------------+ 

七、类型转换函数

为了进行类型转化，mysql提供了cast( )函数，它可以把一个值转化为指定的数据类型。类型有：binary，char，date，time，datetime，signed，unsigned

--example：
select cast(now() as signed integer),curdate()+0;
+-------------------------------+-------------+
| cast(now() as signed integer) | curdate()+0 |
+-------------------------------+-------------+
|                20160520162850 |    20160520 |
+-------------------------------+-------------+
select 'f' = binary 'f','f' = cast('f' as binary); 

八、系统信息函数

database（）　　返回当前数据库名
benchmark（count，expr）　　将表达式expr重复运行count次
connection_id（）　　返回当前客户的连接id
found_rows（）　　返回最后一个select查询进行检索的总行数
user（）或 system_user（）　　返回当前登录用户名
version（）　　返回mysql服务器的版本 
mysql> select benchmark( 500000, md5( 'test' ) );
+------------------------------------+
| benchmark( 500000, md5( 'test' ) ) |
+------------------------------------+
|                                  0 |
+------------------------------------+
mysql> select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

流程控制

--mysql有4个函数是用来进行条件操作的，这些函数可以实现sql的条件逻辑，允许开发
--者将一些应用程序逻辑转换到数据库后台
--mysql控制流函数：
case when[test1] then [result1]...else [default] end 如果testn是真，则返回resultn，否则返回default
case [test] when[val1] then [result]...else [default]end 如果test和valn相等，则返回resultn，否则返回default
if(test,t,f)    如果test是真，返回t，否则返回f
ifnull（arg1，arg2）    如果arg1不是空，返回arg1，否则返回arg2
nullif（arg1，arg2）    如果arg1=arg2返回null；否则返回arg1
select sname
case gender
when 1
then '男'
when 0
then '女'
else '保密'
end as sex
from tab1;