开发常用sql高级函数

SQL 高级函数大全

一、数学函数

序号	函数	作用
1	ABS(x)	返回x的绝对值
2	BIN(x)	返回x的二进制（OCT返回八进制，HEX返回十六进制）
3	CEILING(x)	返回大于x的最小整数值
4	EXP(x)	返回值e（自然对数的底）的x次方
5	FLOOR(x)	返回集合中最大的值
6	GREATEST(x1,x2,…,xn)	返回集合中最小的值
7	LEAST(x1,x2,…,xn)	返回x的自然对数
8	LN(x)	返回大于x的最小整数值
9	LOG(x,y)	返回x的以y为底的对数
10	MOD(x,y)	返回x/y的模（余数）
11	PI()	返回pi的值（圆周率）
12	RAND()	返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
13	ROUND(x,y)	返回参数x的四舍五入的有y位小数的值
14	SIGN(x)	返回代表数字x的符号的值
15	SQRT(x)	返回一个数的平方根
16	TRUNCATE(x,y)	返回数字x截短为y位小数的结果

二、聚合函数

常用于GROUP BY从句的SELECT查询中

序号	函数	作用
1	AVG(col)	返回指定列的平均值
2	COUNT(col)	返回指定列中非NULL值的个数
3	MIN(col)	返回指定列的最小值
4	MAX(col)	返回指定列的最大值
5	SUM(col)	返回指定列的所有值之和
6	GROUP_CONCAT(col)	返回由属于一组的列值连接组合而成的结果
7	ABS(x)	返回x的绝对值
8	ABS(x)	返回x的绝对值
9	ABS(x)	返回x的绝对值

三、字符串函数

序号	函数	作用
1	ASCII(char)	返回字符的ASCII码值
2	BIT_LENGTH(str)	返回字符串的比特长度
3	CONCAT(s1,s2…,sn)	将s1,s2…,sn连接成字符串
4	CONCAT_WS(sep,s1,s2…,sn)	将s1,s2…,sn连接成字符串，并用sep字符间隔
5	INSERT(str,x,y,instr)	将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
6	FIND_IN_SET(str,list)	分析逗号分隔的list列表，如果发现str，返回str在list中的位置
7	LCASE(str)或LOWER(str)	返回将字符串str中所有字符改变为小写后的结果
8	LEFT(str,x)	返回字符串str中最左边的x个字符
9	LENGTH(s)	返回字符串str中的字符数
10	POSITION(substr,str)	返回子串substr在字符串str中第一次出现的位置
11	LTRIM(str)	从字符串str中切掉开头的空格
12	QUOTE(str)	用反斜杠转义str中的单引号
13	REPEAT(str,srchstr,rplcstr)	返回字符串str重复x次的结果
14	REVERSE(str)	返回颠倒字符串str的结果
15	RIGHT(str,x)	返回字符串str中最右边的x个字符
16	RTRIM(str)	返回字符串str尾部的空格
17	STRCMP(s1,s2)	比较字符串s1和s2
18	TRIM(str)	去除字符串首部和尾部的所有空格
19	UCASE(str)或UPPER(str)	返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数

序号	函数	作用
1	CURDATE()或CURRENT_DATE()	返回当前的日期
2	CURTIME()或CURRENT_TIME()	返回当前的时间
3	DATE_ADD(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如： SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH)
4	DATE_FORMAT(date,fmt)	依照指定的fmt格式格式化日期date值
5	DATE_SUB(date,INTERVAL int keyword)	返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如： SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
6	DAYOFWEEK(date)	返回date所代表的一星期中的第几天(1~7)
7	DAYOFMONTH(date)	返回date是一个月的第几天(1~31)
8	DAYOFYEAR(date)	返回date是一年的第几天(1~366)
9	DAYNAME(date)	返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE)
10	FROM_UNIXTIME(ts,fmt)	根据指定的fmt格式，格式化UNIX时间戳ts
11	HOUR(time)	返回time的小时值(0~23)
12	MINUTE(time)	返回time的分钟值(0~59)
13	MONTH(date)	返回date的月份值(1~12)
14	MONTHNAME(date)	返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE)
15	NOW()	返回当前的日期和时间
16	QUARTER(date)	返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE)
17	WEEK(date)	返回日期date为一年中第几周(0~53)
18	YEAR(date)	返回日期date的年份(1000~9999)

一些示例：

• 获取当前系统时间：

  SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
  SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
  SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
  SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
  1234
  

• 返回两个日期值之间的差值(月数)：

  SELECT PERIOD_DIFF(200302,199802);
  1
  

• 在Mysql中计算年龄：

  SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),'%Y')+0 AS age FROM employee;
  # 这样，如果Brithday是未来的年月日的话，计算结果为0。
  12
  

• 下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。

  SELECT DATE_FORMAT(NOW(), '%Y') - DATE_FORMAT(birthday, '%Y') -(DATE_FORMAT(NOW(), '00-%m-%d') <DATE_FORMAT(birthday, '00-%m-%d')) AS age from employee
  1
  

五、加密函数

序号	函数	作用
1	AES_ENCRYPT(str,key)	返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
2	AES_DECRYPT(str,key)	返回用密钥key对字符串str利用高级加密标准算法解密后的结果
3	DECODE(str,key)	使用key作为密钥解密加密字符串str
4	ENCRYPT(str,salt)	使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
5	ENCODE(str,key)	使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
6	MD5()	计算字符串str的MD5校验和
7	PASSWORD(str)	返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法
8	SHA()	计算字符串str的安全散列算法(SHA)校验
9	DAYNAME(date)	返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE)
10	FROM_UNIXTIME(ts,fmt)	根据指定的fmt格式，格式化UNIX时间戳ts

示例：

SELECT ENCRYPT('root','salt');
SELECT ENCODE('xufeng','key');
SELECT DECODE(ENCODE('xufeng','key'),'key');#加解密放在一起
SELECT AES_ENCRYPT('root','key');
SELECT AES_DECRYPT(AES_ENCRYPT('root','key'),'key');
SELECT MD5('123456');
SELECT SHA('123456');
1234567

六、控制流函数

MySQL这些函数可以实现SQL的条件逻辑

序号	函数	作用
1	CASE WHEN[test1] THEN [result1]…ELSE [default] END	如果testN是真，则返回resultN，否则返回default
2	CASE [test] WHEN[val1] THEN [result]…ELSE [default]END	如果test和valN相等，则返回resultN，否则返回default
3	IF(test,t,f)	如果test是真，返回t；否则返回f
4	IFNULL(arg1,arg2)	如果arg1不是空，返回arg1，否则返回arg2
5	NULLIF(arg1,arg2)	如果arg1=arg2返回NULL；否则返回arg1

IF函数

# 判断是否为三角形
select *,if(x+y>z and x+z>y and y+z>x,'Yes','No') triangle  from Triangle

CASE函数的第一种写法：

CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END
12345678

CASE函数的第二种写法：

CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
12345

七、格式化函数

序号	函数	作用
1	DATE_FORMAT(date,fmt)	依照字符串fmt格式化日期date值
2	FORMAT(x,y)	把x格式化为以逗号隔开的数字序列，y是结果的小数位数
3	INET_ATON(ip)	返回IP地址的数字表示
4	INET_NTOA(num)	返回数字所代表的IP地址
5	TIME_FORMAT(time,fmt)	依照字符串fmt格式化时间time值

FORMAT()函数，把大的数值格式化为以逗号间隔的易读的序列。

SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),'%W,%D %M %Y %r');
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
SELECT DATE_FORMAT(19990330,'%Y-%m-%d');
SELECT DATE_FORMAT(NOW(),'%h:%i %p');
SELECT INET_ATON('10.122.89.47');
SELECT INET_NTOA(175790383);

八、类型转化函数

CAST()函数进行数据类型转化，把一个值转化为指定的数据类型。
类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED

 SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;

九、系统信息函数

序号	函数	作用
1	DATABASE()	返回当前数据库名
2	BENCHMARK(count,expr)	将表达式expr重复运行count次
3	CONNECTION_ID()	返回当前客户的连接ID
4	FOUND_ROWS()	返回最后一个SELECT查询进行检索的总行数
5	USER()或SYSTEM_USER()	返回当前登陆用户名
6	VERSION()	返回MySQL服务器的版本

示例：

SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));

十、窗口函数

窗口函数