MySQL Common function description

#mySql的数学函数
Select ABS (-5); #绝对值
Select Ceiling (-5.8); #取大整数
Select Floor (-5.8); #取小整数
Select LEAST (10,3,23,40,0); #取最小数
Select Greatest (1,23,4,6,9,12,9); #取最大数
Select MOD (10,3); #除余
Select PI (); #π的值
Select RAND (2); #随机数
Select ROUND (10.4367,3); #四舍五入 take the back three decimal places
Select TRUNCATE (10.12321,2); #后两位截断函数
Select sign (3.4), #判断正负数 negative return-1, positive return 1 0 return 0
Select COS (1); #余弦函数
Select DEGREES (1); #弧度转换为角度
Select Power (2,3); #二的三次方 Pow Shorthand
Select SQRT (4); #开方

#聚合函数
Select AVG (a.sex) from ' user ' A; #平均值
Select MAX (a.sex) from ' user ' A; #最大值
Select MIN (a.sex) from ' user ' A; #最小值
Select SUM (a.sex) from ' user ' A; #求和
Select COUNT (a.sex) from ' user ' A; #统计个数
Select STD (a.sex) from ' user ' A; #标准偏差

#字符串处理函数
Select LENGTH (' Hello '); #取长度
Select LCASE (' Hello '); #取小写
Select UCASE (' Hello '); #取大写
Select STRCMP (' Hello ', ' yes '), #比较两个字符串的大小 (the order of the beginning letters) hello>yes:1 hello<yes:-1 equals: 0
Select position (' Yes ' in ' Hyesman '); #字符串查找定位
Select replace (' Yes ', ' y ', ' hello '); #替换字符串
Select Insert (' Yes man! ', 2,3, ' hello '), #插入函数2, insert between 3 bits
Select concat (' Hello ', ' world '); #合并函数
Select Concat_ws (': ', ' world ', ' hello '); #加上间隔符号合并
Select Left (' HelloWorld ', 3); #取左边的前三个字符
Select right (' Hello ', 3); #取最后面三个字符
Select LTrim (' Hello '); #去出左边空格
Select RTrim (' Hello '); #去除右边空格
Select Trim (' Hello '); #去除佐佑两边空格
Select substring (' macket ', 2,3); #从第二位开始往后取三位字符串

#日期函数
Select Now (); #取现在的是时间
Select Curtime (); #取当前时间
Select Curdate (); #去现在的时间
Select year (' 20160609 '); #取年份
Select month (' 2016-06-09 '); #取月份
Select MonthName (' 2016-06-09 '); #取月份的英文名称
Select DayOfYear (' 2016-06-09 '); #这一年中的第几天
Select DayOfWeek (' 2016-06-09 '); #在一个星期中的第几天
Select Dayname (' 2016-06-09 '); #Thursday take the Week
Select Hour (' 14:56 '); # take the Hour
Select minute (' 14:56 '); # take minutes
Select second (' 14:56:23 '); #取秒
Select Date_add (now (), Interval 3); #当前时间往后推三年
Select Date_add (now (), Interval 3 month); #当前时间往后推三个月
Select Date_add (now (), Interval 3 day); #当前时间往后推三天
Select Date_sub (now (), Interval 3 day); #当前时间往前推三天
Select Date_sub (now (), Interval 3 month); #当前时间往前推三个月
Select Date_sub (now (), Interval 3); #当前时间往前推三年

#对日期格式化函数
#W: Days of the week D: that day M: Month Y: Year R: Current time
Select Date_format (now (), '%W%d%M%Y%r ');
Select Date_format (now (), '%w%d%m%y%r ');
#时间, formatted 100个小时21分, 12 seconds
Select Time_format (' 100:21:12 ', '%h:%i%p ');
#数字转换IP地址 & IP Address converted to a number
Select Inet_aton (' 192.168.11.133 ');
Select Inet_ntoa (' 3232238469 ');

#类型转换函数
Select 1 + ' 99 ';
Select 1 + CAST (' as signed ')

#加了 binary is case-sensitive
Select ' F ' =binary ' f ', ' F ' =cast (' F ' as binary); #转二进制
Select CONVERT (' signed '), #数值, convert data type


#mySql的数据加密函数
Select Password (' secret '), password (' secret '); #加密过程不可逆, same string encryption same
Select Encrypt (' Secret ', ' abc ');
Select Encode (' Tanmujiang ', ' key '), #互相可逆加密效果, key for decryption effect
Select decode (Encode (' Tanmujiang ', ' key '), ' key '); #解密
Select Aes_encrypt (' Secret ', ' key '); #aes加密
Select Aes_decrypt (Aes_encrypt (' secret ', ' key '), ' key '); #解密
Select MD5 (' secret '); #MD5加密
Select SHA (' secret '); #SHA加密


#mySql控制流函数
Select if (1<10,2,3); #相当于三元表达式
Select Ifnull; #如果第一个字符为空, returns itself 1, otherwise returns two
Select Ifnull (null,2); #返回2
Select Nullif; #如果第一个参数和第二个参数相等返回1, equal return null
Select Nullif (n); #返回null

#如果前面条件成立, returns a specified value
SELECT
Case 4
When 0 Then
First Prize
When 0 Then
Second Prize
When 3 Then
Third Prize
ELSE
' Lucky Prize '
END;

SELECT
Case ' green '
When the ' red ' then
100
When "green" then
200
ELSE
300
END;


#最后记录一个逼格高点清空表数据方法
TRUNCATE TABLE visit;

MySQL Common function description