SQL常用函数总结（三）

三、单行函数

1.从函数定义角度，分成内置函数、自定义函数

2.从实现功能分为：数值函数、字符串函数、日期和时间函数、流程控制函数等等

3.分为单行函数、聚合函数（分组函数）

单行函数特点：操作数据对象。接受参数返回的一个结果，只对一行进行变换，每行返回一个结果可以嵌套，参数可以是一列或一个值。

1.数值函数 

ABS（x) 返回绝对值；   

SIGN（x) 返回x的符号

PI（）圆周率的值；       

CEIL（x），CEILING（x）返回大于或等于某个值的最小整数

FLOOR（x）小于或等于某个值的最大整数；

LEAST(x1，x2，x3…)返回列表中的最小值；GREATEST（x1，x2，x3…）返回列表中的最大值

MOD(x,y) 返回x除以y后的余数

RAND（） 返回0~1的随机值

RAND（x） 返回0~1随机值，x的值作为种子值，相同的x值产生相同的随机数

ROUND（x，c）四舍五入，截断操作 c为保留小数位数，为0时可省略不写

TRUNCATE（x，c）将数字x截断为c位小数，没有四舍五入直接截断

SQRT（x）返回x的平方根；x为负值时，返回null

2.三角函数

SIN（x）返回x的正弦值，x为弧度值

ASIN（x）返回x的反正弦值：正弦为x的值，若x不属于-1到1，返回null

COS（x）返回x的余弦值，x为弧度值

ACOS（x）返回x的反余弦值：余弦为x的值，若x不属于-1到1，返回null

TAN（x）返回x的正切值，x为弧度值

ATAN（x）返回x的反正切值：正切为x的值

ATAN2（m,n）返回两个参数的反正切值

COT（x） x的余切值，x为弧度值

角度于与弧度互换函数

RADIANS（x） 角度——>弧度，x为角度

DEGREES（x）弧度——>角度，x为弧度

3.指数和对数函数

POW（x，c），POWER（x，c）表示x的c次幂

EXP（c） 表示e的c次幂

LN（a）表示ln a

LOGa（N）表示logaN

4.进制间的转换

BIN（）转二进制

HEX（）转十六进制

OCT（）转八进制

CONV（x，y，z）将x从y进制转到z进制

4.字符串函数

ASCII（s）返回字符串s的第一个字符的ASCII码值

CHAR_LENGTH（s）返回字符串s的字符数

LENGTH（s）返回字符串s的字节数，与字符集有关，一个汉字占3个字节，为一个字符

CONCAT（s1，s2，…，sn）连接s1，s2，…，sn为一个字符串

CONCAT_WS（x,s1，s2，…，sn）同上，但每个字符串之间要加上x

INSERT（str，idx，len，replacestr）将str从第idx位置开始，len个字符长的子串替换为replacestr

REPLACE（str，a，b）用b替换str中所有出现的a

UPPER（s）或者UCASE（s） 将s所有字母转成大写字母

LOWER（s）或者LCASE（s）将s所有字母转成小写字母

LEFT（str，n）返回str最左边的n个字符

RIGHT（str，n）返回str最右边的n个字符

LPAD（str，len，pad）用pad对str最左边进行填充，直到str的长度为len个字符

RPAD（str，len，pad）用pad对str最右边进行填充，直到str的长度为len个字符

REPEAT（x，c）重复x，4次写成一串

STRCMP（s1，s2）比较s1和s2的ASCII码哪个大

SUBSTR（x，a，b）返回x字符串的第a个起b个字符

LOCATE(a，x）返回a首次在x中出现的位置

ELT（m，s1，s2，…，sn）返回指定位置字符串，例如m=1则返回s1

FIELD（s，s1，s2，…，sn）返回s在列表中第一次出现的位置

FIND_IN_SET(s1,s2)返回s1在s2首先出现的位置

REVERSE（s）返回s反转后的字符串

NULLIF（value1，value2）比较两个字符串，若相等，返回null，否则返回value1

4.日期和时间函数

4.1获取日期和时间

CURDATE（）、CURRENT_DATE（）返回当前日期

CURTIME（）返回当前时间

NOW（）返回当前日期+时间

UTC_DATE()、UTC_TIME() utc世界标准，相差8小时，是我们的时间-8hour

4.2日期与时间戳的转换

UNIX_TIMESTAMP（）日期转为时间戳

FROM_UNIXTIME（）时间戳转为日期

4.3获取月份、星期、星期数、天数等函数

YEAR(date) 返回date对应的年

MONTH(date)返回date对应的月

DAY(date)返回date对应的日

HOUR（time）返回time对应的小时

MINUTE（time）返回time对应的分钟

SECOND（time）返回time对应的秒数

MONTHNAME(date)返回对应的月份：January,…

DAYNAME(date)返回星期几：MONDAY，…

WEEKDAY(date)返回周几，0是周一，…6是周日

QUARTER(date)返回日期对应的季度1~4

WEEK(date)返回日期是一年中的第几周

WEEKOFYEAR(date)返回日期是一年中的第几周

DAYOFYEAR(date)返回日期是一年中的第几天

DAYOFMONTH(date)返回日期是所在月份中的第几天

DAYOFWEEK(date)返回周几，周日是1，周一是2

4.4日期的操作函数

EXTRACT（type FROM  date） 返回指定日期中的特定部分，type为返回的值

type可以是SECOND、MINUTE、HOUR、DAY、WEEK、MONTH、QUARTER、YEAR、MINUTE_SECOND

4.5时间和秒钟转换的函数

TIME_TO_SEC（）时间转换为秒数

SEC_TO_TIME（）秒数转换为时间

4.6计算日期和时间的函数

DATE_ADD(datetime，INTERVAL，expr type)加上一段时间的日期

此函数情况下，expr为正数时候则为加，为负数时则为减，type为year，minute，second之类的

DATE_SUB(datetime，INTERVAL，expr type)减去一段时间的日期

ADDTIME(time1，time2)在time1上加上time2的时间，time为一个数字时表示秒，可为负值，相当于减去

SUBTIME(time1，time2)在time1上减去time2的时间，time为一个数字时表示秒，可为负值，相当于加上

DATEDIFF(date1，date2)返回date1，date2日期间隔天数

TIMEDIFF(time1，time2）返回time1，time2时间间隔

FROM_DAYS(N)返回从0000年1月1日起，N天以后的日期

TO_DAYS（date）返回日期date距离0000年1月1日的天数

LAST_DAY(date)返回date所在月份的最后一天的日期

MAKEDATE(year，n)针对给定年份和所在年份中的天数返回一个日期

MAKETIME（hour，minute，second）将给定的小时、分钟、秒组合成时间返回

PERIOD_ADD(time,n)返回time加上n后的时间

4.7日期的格式化与解析

格式化：日期——>字符串

解析：字符串——>日期

！此时说的是日期的显示格式化和解析，之前说的都是隐式格式化或解析

DATE_FORMAT（日期，'%Y-%M-%D'）后者还可以是其他格式

STR_TO_DATE(字符串，'%Y-%M-%D %h-%m-%s %W %w')后者格式可自行选定

GET_FORMAT（date，'USA')获得date的USA日期格式，USA可换成想要知道的其他国家格式

5.流程控制函数

IF（VALUE,VALUE1,VALUE2）如果满足条件VALUE，则值为VALUE1，否则为VALUE2

IFNULL（VALUE1，VALUE1）如果VALUE1为null，则用VALUE2替代

CASE WHEN…条件1 THEN…value1

           WHEN…条件2 THEN…value2

            ELSE…value3  END

若满足条件1则为值value1，满足条件2，则为value2，其他情况则为value3

CASE…value  WHEN…THEN…

                        WHEN…THEN…

                         ELSE…END

若value等于值1则…，等于值2则…，其他情况则…结束

FORMAT（value，n）保留value的n位小数，若n小于等于0，则只保留整数部分，有四舍五入。