一、函数的概述

??从函数定义的角度出发，我们可以将函数分为 内置函数 和 自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的；MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。

二、单行函数

单行函数只对一行进行变换，每行返回一个结果，它的参数可以是一列或一个值。单行函数可以嵌套。

2.1、数值函数

2.1.1、基本函数

ABS(x)              -- 返回x的绝对值
SIGN(X)             -- 返回X的符号。正数返回1，负数返回-1，0返回0 
PI()                --  返回圆周率的值
CEIL(x)             -- 返回大于或等于某个值的最小整数
CEILING(x)          -- 返回大于或等于某个值的最小整数
FLOOR(x)            -- 返回小于或等于某个值的最大整数
LEAST(e1,e2,e3…)    -- 返回列表中的最小值 
GREATEST(e1,e2,e3…) -- 返回列表中的最大值
MOD(x,y)            -- 返回X除以Y后的余数
RAND()              -- 返回0~1的随机值
RAND(x)             -- 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数
ROUND(x)            -- 返回一个对x的值进行四舍五入后，最接近于X的整数
ROUND(x,y)          -- 返回一个对x的值进行四舍五入后最接近X的值，并保留到小数点后面Y位
TRUNCATE(x,y)       -- 返回数字x截断为y位小数的结果
SQRT(x)             -- 返回x的平方根。当X的值为负数时，返回NULL

-- 绝对值函数
SELECT ABS(-123),ABS(32) FROM DUAL;
-- 符号函数
SELECT SIGN(-23),SIGN(43) FROM DUAL;
-- 圆周率
SELECT PI() FROM DUAL;
-- 向上取整函数、向下取整函数
SELECT CEIL(32.32),CEILING(-42.23),FLOOR(32.32),FLOOR(-43.23) FROM DUAL;
-- 取余函数
SELECT MOD(12,5) FROM DUAL;
-- 取最小值函数、取最大值函数
SELECT LEAST(13,10,43,53),GREATEST(13,10,43,53) FROM DUAL;
-- 随机函数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1) FROM DUAL;
-- 四舍五入函数
SELECT ROUND(123.456),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,-1),ROUND(123.456,-2) FROM DUAL;
-- 截断函数
SELECT TRUNCATE(123.4556,0),TRUNCATE(123.456,1),TRUNCATE(123.456,-1) FROM DUAL;
-- 开方函数
SELECT SQRT(4),SQRT(3),SQRT(6.25),SQRT(3.3),SQRT(-9) FROM DUAL;

2.1.2、角度与弧度互换函数

RADIANS(x) -- 将角度转化为弧度，其中，参数x为角度值
DEGREES(x) -- 将弧度转化为角度，其中，参数x为弧度值

-- 角度换成弧度
SELECT RADIANS(30),RADIANS(45),RADIANS(60) FROM DUAL;
-- 弧度换成角度
SELECT DEGREES(2*PI()),DEGREES(RADIANS(60)) FROM DUAL;

2.1.3、三角函数

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为弧度值

-- 正弦函数、反正弦函数
SELECT SIN(PI()/6),DEGREES(ASIN(1/2)) FROM DUAL;
-- 余弦函数、反余弦函数
SELECT SQRT(3)/2,COS(PI()/6),DEGREES(ACOS(SQRT(3)/2)) FROM DUAL;
-- 正切函数、反正切函数
SELECT TAN(RADIANS(45)),DEGREES(ATAN(1)) FROM DUAL;
-- 计算两个参数的反正切函数
SELECT ATAN2(3-1,6-5) FROM DUAL;
-- 余切函数
SELECT COT(RADIANS(45)) FROM DUAL;

2.1.4、指数与对数

POW(x,y)     -- 返回x的y次方
POWER(X,Y)   -- 返回x的y次方
EXP(X)       -- 返回e的X次方，其中e是一个常数，2.718281828459045
LN(X)        -- 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG(X)       -- 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG10(X)     -- 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL
LOG2(X)      -- 返回以2为底的X的对数，当X <= 0 时，返回NULL

SELECT POW(2,5),POWER(2,6),EXP(2) FROM DUAL;
SELECT LN(EXP(2)),LOG(7.389),LOG10(100),LOG2(8) FROM DUAL;

2.1.5、进制间的转换

BIN(x)        -- 返回x的二进制编码
HEX(x)        -- 返回x的十六进制编码
OCT(x)        -- 返回x的八进制编码
CONV(x,f1,f2) -- 返回f1进制数变成f2进制数

SELECT BIN(10),HEX(10),COT(10),CONV(10,2,16) FROM DUAL;

2.2、字符串函数

ASCII(S)                          -- 返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH(s)                    -- 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
LENGTH(s)                         -- 返回字符串s的字节数，和字符集有关
CONCAT(s1,s2,......,sn)           -- 连接s1,s2,......,sn为一个字符串
CONCAT_WS(x, s1,s2,......,sn)     -- 同CONCAT(s1,s2,...)函数，但是每个字符串之间要加上x
INSERT(str, idx, len, replacestr) -- 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE(str, a, b)                -- 用字符串b替换字符串str中所有出现的字符串a 
UPPER(s)                          -- 将字符串s的所有字母转成大写字母
UCASE(s)                          -- 将字符串s的所有字母转成大写字母 
LOWER(s)                          -- 将字符串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个字符
LTRIM(s)                          -- 去掉字符串s左侧的空格
RTRIM(s)                          -- 去掉字符串s右侧的空格
TRIM(s)                           -- 去掉字符串s开始与结尾的空格
TRIM(s1 FROM s)                   -- 去掉字符串s开始与结尾的s1
TRIM(LEADING s1 FROM s)           -- 去掉字符串s开始处的s1
TRIM(TRAILING s1 FROM s)          -- 去掉字符串s结尾处的s1
REPEAT(str, n)                    -- 返回str重复n次的结果
SPACE(n)                          -- 返回n个空格
STRCMP(s1,s2)                     -- 比较字符串s1,s2的ASCII码值的大小
SUBSTR(s,index,len)               -- 返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同 
LOCATE(substr,str)                -- 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
ELT(m,s1,s2,…,sn)                 -- 返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
FIELD(s,s1,s2,…,sn)               -- 返回字符串s在字符串列表中第一次出现的位置
FIND_IN_SET(s1,s2)                -- 返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
REVERSE(s)                        -- 返回s反转后的字符串
NULLIF(value1,value2)             -- 比较两个字符串，如果value1与value2相等，则返回NULL，否则返回value1

-- 返回字符串第一个字符ASCII函数
SELECT ASCII('abc') FROM DUAL;
-- 返回字符串的字符数函数、返回字符串的字节数函数
SELECT CHAR_LENGTH('hello'),CHAR_LENGTH('华夏'),LENGTH('hello'),LENGTH('华夏') FROM DUAL;
-- 字符串连接函数
SELECT CONCAT('hello',' ','world'),CONCAT_WS('-','hello','world') FROM DUAL;
-- 字符串替换函数，在SQL中字符串的索引从1开始
SELECT INSERT('hello world',2,3,'aaaaaa'),REPLACE('hello','ll','mmmm'),REPLACE('hello','lol','aaa') FROM DUAL;
-- 字符串转化大写函数、字符串转化小写函数
SELECT UPPER('aBcd'),UCASE('aBcd'),LOWER('aBcd'),LCASE('aBcd') FROM DUAL;
-- 返回字符串最左边n个字符函数、返回字符串最右边n个字符函数
SELECT LEFT('hello',3),RIGHT('hello',3) FROM DUAL;
-- 字符串左边填充函数、字符串右边甜筒函数
SELECT LPAD('hello',10,'*'),RPAD('hello',10,'*') FROM DUAL;
-- 去除左边空格函数、去除右边空格函数、去除前后空格
SELECT LTRIM(' hello world '),RTRIM(' hello world '),TRIM(' hello world ') FROM DUAL;
-- 去除左边指定的字符串、去除右边指定的字符串、去除前后指定的字符串
SELECT TRIM(LEADING 'aa' FROM 'aababc aa cbabaa'),TRIM(TRAILING 'aa' FROM 'aababc aa cbabaa'),TRIM('aa' FROM 'aababc aa cbabaa') FROM DUAL;
-- 返回字符串重复个数的函数
SELECT REPEAT('hello',4) FROM DUAL;
-- 返回n个空格函数
SELECT LENGTH(SPACE(5)) FROM DUAL;
-- 比较两字符串ANSII函数
SELECT STRCMP('abc','abd') FROM DUAL;
-- 返回指定的子字符串函数
SELECT SUBSTR('hello',2,2) FROM DUAL;
-- 返回子字符串第一次出现的位置
SELECT LOCATE('ll','hello'),LOCATE('lll','hello') FROM DUAL;
-- 返回指定位置的字符串
SELECT ELT(2,'a','b','c') FROM DUAL;
-- 返回指定字符串在字符串列表中首次出现的位置
SELECT FIELD('mm','gg','dd','jj','mm','bb','mm') FROM DUAL;
-- 返回字符串s1在字符串s2中出现的位置
SELECT FIND_IN_SET('mm','gg,dd,jj,mm,bb,mm') FROM DUAL;
-- 反转字符串函数
SELECT REVERSE('abc') FROM DUAL;
-- 比较两个字符串
SELECT NULLIF('abc','abc'),NULLIF('abc','abe') FROM DUAL;

注意：MySQL中，字符串的位置是从1开始的。

2.3、日期和时间函数

2.3.1、获取日期、时间

CURDATE()           -- 返回当前日期，只包含年、月、日
CURRENT_DATE()      -- 返回当前日期，只包含年、月、日
CURTIME()           -- 返回当前时间，只包含时、分、秒
CURRENT_TIME()      -- 返回当前时间，只包含时、分、秒
NOW()               -- 返回当前系统日期和时间
SYSDATE()           -- 返回当前系统日期和时间
CURRENT_TIMESTAMP() -- 返回当前系统日期和时间
LOCALTIME()         -- 返回当前系统日期和时间
LOCALTIMESTAMP()    -- 返回当前系统日期和时间
UTC_DATE()          -- 返回UTC（世界标准时间）日期
UTC_TIME()          -- 返回UTC（世界标准时间）时间

-- 返回当前日期，只包含年、月、日
SELECT CURDATE(),CURRENT_DATE() FROM DUAL;
-- 返回当前时间，只包含时、分、秒
SELECT CURTIME(),CURRENT_TIME() FROM DUAL;
-- 返回当前系统日期和时间
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;
-- 返回UTC（世界标准时间）日期函数、返回UTC（世界标准时间）时间函数
SELECT UTC_DATE(),UTC_TIME() FROM DUAL;

2.3.2、日期与时间戳的转换

UNIX_TIMESTAMP()         -- 以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP(date)     -- 将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(timestamp) -- 将UNIX时间戳的时间转换为普通格式的时间

-- 以UNIX时间戳的形式返回当前时间
SELECT UNIX_TIMESTAMP() FROM DUAL;
-- 将时间date以UNIX时间戳的形式返回      
SELECT UNIX_TIMESTAMP('2022-01-24 11:30:50') FROM DUAL;
-- 将UNIX时间戳的时间转换为普通格式的时间
SELECT FROM_UNIXTIME(1642995050) FROM DUAL;

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

YEAR(date)          -- 返回具体的日期值
MONTH(date)         -- 返回具体的日期值
DAY(date)           -- 返回具体的日期值
HOUR(time)          -- 返回具体的时间值
MINUTE(time)        -- 返回具体的时间值  
SECOND(time)        -- 返回具体的时间值  
MONTHNAME(date)     -- 返回月份：January，...
DAYNAME(date)       -- 返回星期几：MONDAY，TUESDAY.....SUNDAY
WEEKDAY(date)       -- 返回周几，注意，周1是0，周2是1，。。。周日是6
DAYOFWEEK(date)     -- 返回周几，注意：周日是1，周一是2，。。。周六是7
QUARTER(date)       -- 返回日期对应的季度，范围为1～4
WEEK(date)          -- 返回一年中的第几周
WEEKOFYEAR(date)    -- 返回一年中的第几周
DAYOFYEAR(date)     -- 返回日期是一年中的第几天
DAYOFMONTH(date)    -- 返回日期位于所在月份的第几天

-- 返回具体的日期值
SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()) FROM DUAL;
-- 返回具体的时间值
SELECT HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()) FROM DUAL;
-- 返回月份：January，...
SELECT MONTHNAME(NOW()) FROM DUAL;    
-- 返回星期几：MONDAY，TUESDAY.....SUNDAY / 周1是0，周2是1，... 周日是6 / 周日是1，周一是2，... 周六是7
SELECT DAYNAME(NOW()),WEEKDAY(NOW()),DAYOFWEEK(NOW()) FROM DUAL;
-- 返回日期对应的季度，范围为1～4
SELECT QUARTER(NOW()) FROM DUAL;    
-- 返回一年中的第几周、返回一年中的第几周
SELECT WEEK(NOW()),WEEKOFYEAR(NOW()) FROM DUAL;
-- 返回日期是一年中的第几天
SELECT DAYOFYEAR(NOW()) FROM DUAL;
-- 返回日期位于所在月份的第几天
SELECT DAYOFMONTH(NOW()) FROM DUAL;

2.3.4、日期的操作函数

EXTRACT(type FROM date) -- 返回指定日期中特定的部分，type指定返回的值

EXTRACT(type FROM date)函数中type的取值与含义：

SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(DAY FROM NOW()) FROM DUAL;
SELECT EXTRACT(HOUR FROM NOW()),EXTRACT(MINUTE FROM NOW()),EXTRACT(SECOND FROM NOW()),EXTRACT(MICROSECOND FROM NOW()) FROM DUAL; 
SELECT EXTRACT(QUARTER FROM NOW()) FROM DUAL;

2.3.5、时间和秒钟转换的函数

TIME_TO_SEC(time)    -- 将 time 转化为秒并返回结果值。转化的公式为：`小时*3600+分钟*60+秒`
SEC_TO_TIME(seconds) -- 将 seconds 描述转化为包含小时、分钟和秒的时间

SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(42234) FROM DUAL;

2.3.6、计算日期和时间的函数

DATE_ADD(datetime, INTERVAL  expr type)  -- 返回与给定日期时间相差INTERVAL时间段的日期时间
ADDDATE(date,INTERVAL expr type)         -- 返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_SUB(date,INTERVAL expr type)        -- 返回与date相差INTERVAL时间间隔的日期
SUBDATE(date,INTERVAL expr type)         -- 返回与date相差INTERVAL时间间隔的日期
ADDTIME(time1,time2)                     -- 返回time1加上time2的时间。当time2为一个数字时，代表的是`秒`，可以为负数
SUBTIME(time1,time2)                     -- 返回time1减去time2后的时间。当time2为一个数字时，代表的是`秒`，可以为负数
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后的时间

SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR) FROM DUAL;
SELECT NOW(),DATE_SUB(NOW(),INTERVAL 1 YEAR),DATE_ADD(NOW(),INTERVAL -1 YEAR)
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_ADD(NOW(),INTERVAL 1 SECOND) FROM DUAL;
SELECT NOW(),DATE_ADD(NOW(),INTERVAL '1_1' MINUTE_SECOND) FROM DUAL;
SELECT NOW(),DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH) FROM DUAL;
SELECT NOW(),ADDTIME(NOW(),10),SUBTIME(NOW(),10),ADDTIME(NOW(),'1:1:1'),SUBTIME(NOW(),'1:1:1') FROM DUAL;
SELECT DATEDIFF(NOW(),'2022-01-10 22:10:12'),TIMEDIFF(NOW(),'2022-01-10 22:10:12') FROM DUAL;
SELECT FROM_DAYS(366),TO_DAYS('0001-01-01') FROM DUAL;
SELECT LAST_DAY(NOW()) FROM DUAL;
SELECT MAKEDATE(YEAR(NOW()),32) FROM DUAL;
SELECT MAKETIME(10,21,13) FROM DUAL;
SELECT PERIOD_ADD(20220101010101,10) FROM DUAL;

2.3.7、日期的格式化与解析

• 格式化：日期 --> 字符串
• 解析：字符串 --> 日期

DATE_FORMAT(date,fmt)              -- 按照字符串fmt格式化日期date值
TIME_FORMAT(time,fmt)              -- 按照字符串fmt格式化时间time值
GET_FORMAT(date_type,format_type)  -- 返回日期字符串的显示格式
STR_TO_DATE(str, fmt)              -- 按照字符串fmt对str进行解析，解析为一个日期

上述非GET_FORMAT函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

-- 格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%Y-%m-%d') FROM DUAL;
SELECT TIME_FORMAT(CURTIME(),'%H:%i:%S'),TIME_FORMAT(CURTIME(),'%h:%i:%S') FROM DUAL;
SELECT DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r') FROM DUAL;

-- 解析
SELECT STR_TO_DATE('2022-January-17th 07:52:32 Monday 1','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL;

SELECT GET_FORMAT(DATE,'USA') FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA')) FROM DUAL;

2.4、流程控制函数

IF(value,value1,value2)                                                  -- 如果value的值为TRUE，返回value1，否则返回value2
IFNULL(value1, value2)                                                   -- 如果value1不为NULL，返回value1，否则返回value2
CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 .... [ELSE resultn] END   -- 相当于Java的if...else if...else...
CASE  expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 .... [ELSE 值n] END -- 相当于Java的switch...case... 

-- IF()
SELECT last_name,salary,IF(salary>=6000,'高工资','低工资') AS "details"  FROM employees;
-- IFNULL()
SELECT last_name,commission_pct,IFNULL(commission_pct,0) AS "details" FROM employees;
-- CASE WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
SELECT last_name,salary,
CASE WHEN salary>=15000 THEN '继续努力'	
     WHEN salary>=1000 THEN '仍需加油'
     WHEN salary>=8000 THEN '努力奋斗'
     ELSE '加油干吧' END AS "details"
FROM employees;

-- CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
SELECT employee_id,last_name,department_id,salary,
CASE department_id WHEN 10 THEN salary * 1.1
		   WHEN 20 THEN salary * 1.2
		   WHEN 30 THEN salary * 1.3
		   ELSE salary *  1.4 END AS "details"
FROM employees;

2.5、加密与解密函数

PASSWORD(str)               -- 返回字符串str的加密版本，41位长的字符串。加密结果`不可逆`，MySQL8.0已弃用
MD5(str)                    -- 返回字符串str的md5加密后的值若参数为NULL，则会返回NULL
SHA(str)                    -- 从原明文密码str计算并返回加密后的密码字符串，当参数为NULL时，返回NULL
ENCODE(value,password_seed) -- 返回使用password_seed作为加密密码加密value，MySQL8.0已弃用
DECODE(value,password_seed) -- 返回使用password_seed作为加密密码解密value，MySQL8.0已弃用

-- PASSWORD在MySQL8.0弃用
SELECT PASSWORD('mysql') FROM DUAL;
-- MD5()、SHA()加密不可逆
SELECT MD5('mysql'),SHA('mysql') FROM DUAL;
-- ENCODE、DECODE()在MySQL8.0弃用
SELECT ENCODE('sakura','kinomoto'),DECODE(ENCODE('sakura','kinomoto'),'kinomoto') FROM DUAL;

2.6、MySQL信息函数

VERSION()              -- 返回当前MySQL的版本号
CONNECTION_ID()        -- 返回当前MySQL服务器的连接数
DATABASE()，SCHEMA()   -- 返回MySQL命令行当前所在的数据库
USER()                 -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CURRENT_USER()         -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
SYSTEM_USER()          -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
SESSION_USER()         -- 返回当前连接MySQL的用户名，返回结果格式为“主机名@用户名”
CHARSET(value)         -- 返回字符串value自变量的字符集
COLLATION(value)       -- 返回字符串value的比较规则

SELECT VERSION() FROM DUAL;
SELECT CONNECTION_ID() FROM DUAL;
SELECT DATABASE(),SCHEMA() FROM DUAL;
SELECT USER(),CURRENT_USER(),SYSTEM_USER(),SESSION_USER() FROM DUAL;
SELECT CHARSET('sakura') FROM DUAL;
SELECT COLLATION('sakura') FROM DUAL;

2.7、其他函数

FORMAT(value,n)                -- 返回对数字value进行格式化后的结果数据。n表示`四舍五入`后保留到小数点后n位
CONV(value,from,to)            -- 将value的值进行不同进制之间的转换
INET_ATON(ipvalue)             -- 将以点分隔的IP地址转化为一个数字
INET_NTOA(value)               -- 将数字形式的IP地址转化为以点分隔的IP地址
BENCHMARK(n,expr)              -- 将表达式expr重复执行n次。用于测试MySQL处理expr表达式所耗费的时间
CONVERT(value USING char_code) -- 将value所使用的字符编码修改为char_code

SELECT FORMAT(123.125,2),FORMAT(123.15,0),FORMAT(123.15,-2) FROM DUAL;
SELECT CONV(16,10,2),CONV(8888,10,16),CONV(NULL,10,2) FROM DUAL;
-- 计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876) FROM DUAL;
SELECT BENCHMARK(100000,MD5('sakura')) FROM DUAL;
SELECT CHARSET('sakura'),CHARSET(CONVERT('sakura' USING 'gbk')) FROM DUAL;

2.8、练习题

-- 1.显示系统时间(注：日期+时间) 
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() FROM DUAL;

-- 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary） 
SELECT last_name,salary,salary*(1+0.2) AS "new salary" FROM employees;

-- 3.将员工的姓名按首字母排序，并写出姓名的长度（length） 
SELECT last_name,LENGTH(last_name) FROM employees ORDER BY last_name ASC;

-- 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT 
SELECT CONCAT(employee_id,',',last_name,',',salary) AS "OUT_PUT" FROM employees;

-- 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序 
SELECT employee_id,last_name,hire_date,
FLOOR(DATEDIFF(CURDATE(),hire_date)/365) AS "worked_years",DATEDIFF(CURDATE(),hire_date) AS "worked_days" 
FROM employees;

-- 6.查询员工姓名，hire_date , department_id，
-- 满足以下条件：雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空 
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110) AND commission_pct IS NOT NULL
-- and hire_date >= '1997-01-01';			-- 格式化：日期 --> 字符串，隐式转化
-- and date_format(hire_date,'%Y-%m-%d')>'1997-01-01';	-- 格式化：日期 --> 字符串，显示转化
-- and date_format(hire_date,'%Y')>='1997'		-- 格式化：日期 --> 字符串，显示转化
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');	-- 解析：字符串 --> 日期

-- 7.查询公司中入职超过10000天的员工姓名、入职时间 
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;

-- 8.做一个查询，产生下面的结果 
--  earns  monthly but wants 
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0),' monthly but wants ',TRUNCATE(salary*3,0)) AS "Dream Salary" 
FROM employees;

-- 9.使用case-when，按照下面的条件： 
-- job         grade 
-- AD_PRES     A 
-- ST_MAN      B 
-- IT_PROG     C 
-- SA_REP      D 
-- ST_CLERK    E
SELECT last_name AS "Last_name",job_id AS "Job_id",
CASE job_id WHEN 'AD_PRES' THEN 'A'
	    WHEN 'ST_MAN' THEN 'B'
	    WHEN 'IT_PROG' THEN 'C'
            WHEN 'SA_REP' THEN 'D'
	    WHEN 'ST_CLERK' THEN 'E' 
	    ELSE 'undefined' END AS "Grade"
FROM employees;

三、聚合函数

聚合函数是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值。聚合函数不能嵌套调用。

3.1、常见的聚合函数类型

AVG()        -- 平均数函数
SUM()        -- 求和函数
MAX()        -- 最大值函数
MIN()        -- 最小值函数
COUNT()      -- 求总数函数

• AVG() 和 SUM() 只适用于数值类型的字段
• MAX() 和 MIN() 适用于数值类型、字符串类型、时间类型
• COUNT(*)、COUNT(1)返回表中记录总数，适用于任意数据类型
• COUNT(expr) 返回expr不为空的记录总数
• AVG() = SUM()/COUNT()
• 聚合函数忽略NULL

-- AVG()、SUM()只适用于数据类型
SELECT AVG(salary),SUM(salary) FROM employees;

-- MAX()、MIN()适用于数值类型、字符串类型、时间类型
SELECT MAX(salary),MIN(salary) FROM employees;
SELECT MAX(last_name),MIN(last_name) FROM employees;
SELECT MAX(hire_date),MIN(hire_date) FROM employees;

SELECT COUNT(employee_id) FROM employees;
SELECT COUNT(commission_pct) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

SELECT AVG(salary),SUM(salary)/COUNT(salary) FROM employees;
SELECT AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct),SUM(commission_pct)/COUNT(*) FROM employees;

使用 COUNT(*)、COUNT(1)、COUNT(具体字段) 统计表中的记录数，哪个效率更高？

• 如果使用的是MyISAM存储引擎，则三者效率相同，都是O(1)；
• 如果使用是InnoDB存储引擎，则三者效率：COUNT(*) = COUNT(1) > COUNT(具体字段)

3.2、GROUP BY的使用

SELECT 字段名1, 分组函数(字段名2)
FROM 表名
GROUP BY 字段名1;

• 可以使用GROUP BY子句将表中的数据分成若干组；
• 在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中，反之，GROUP BY中声明的字段可以不出现在SELECT中；
• GROUP BY 声明在 FROM 后面，WHERE 后面，ORDER BY 前面，LIMIT 前面
• 使用 WITH ROLLUP 关键字之后，在所有查询出的分组记录之后增加一条记录，该记录计算查询出的所有记录的总和，即统计记录数量。
• 当使用 ROLLUP 时，不能同时使用 ORDER BY 子句进行结果排序，即 ROLLUP 和 ORDER BY 是互相排斥的。

-- SELECT 中出现的非分组函数的字段必须声明在 GROUP BY 中。
-- GROUP BY 中声明的字段可以不出现在SELECT中
SELECT department_id,AVG(salary),SUM(salary),MAX(salary) FROM employees GROUP BY department_id;
SELECT department_id,job_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id,job_id;
SELECT job_id,department_id,AVG(salary),SUM(salary) FROM employees GROUP BY job_id,department_id;

SELECT department_id,AVG(salary) FROM employees GROUP BY department_id WITH ROLLUP;

3.3、HAVING的使用

SELECT 字段名1, 分组函数1(字段名2)
FROM 表名
GROUP BY 字段名1;
HAVING 带分组函数的过滤条件;

• HAVING 必须声明在 GROUP BY 后面。
• 如果过滤条件中使用了聚合函数，则必须使用 HAVING 来替换 WHERE。否则，报错。
• 当过滤条件没有聚合函数时，则此过滤条件中声明在 WHERE 中或 HAVING 中都可以，建议声明在 WHERE 中。
• 开发中，我们使用 HAVING 的前提是 SQL 中使用了 GROUP BY。

SELECT department_id,MAX(salary) 
FROM employees 
GROUP BY department_id
HAVING MAX(salary) > 10000;

SELECT department_id,MAX(salary)
FROM employees 
WHERE department_id IN (10,20,30)
GROUP BY department_id
HAVING MAX(salary) > 10000;

3.4、SELECT的执行原理

3.4.1、SELECT的书写顺序

SELECT DISTINCT 字段名1,分组函数(字段名2)  AS "别名"
FROM 表名1,表名2
WHERE 多表的连接条件 AND 不包含分组函数的过滤条件
GROUP BY 字段名1
HAVING 包含分组函数的过滤条件
ORDER BY 字段名 ASC/DESC
LIMIT 偏移量,条目数

SQL99语法

SELECT DISTINCT 字段名1,分组函数(字段名2) AS "别名"
FROM 表名1
[LEFT/RIGHT] JOIN 表名2 ON 连接条件
WHERE 不包含分组函数的过滤条件
GROUP BY 字段名1
HAVING 包含分组函数的过滤条件
ORDER BY 字段名 ASC/DESC
LIMIT 偏移量,条目数

3.4.2、SELECT语句的执行过程

1. FROM 表名
2. ON 连接条件
3. [LEFT/RIGHT] JOIN
4. WHERE 不含分组函数的过滤条件
5. GROUP BY 分组的字段
6. HAVING 带分组函数的过滤条件
7. SELECT 查询的字段名 AS "别名"
8. DISTINCT 去重
9. ORDER BY 按指定字段排序 ASC/DESC
10. LIMIT 偏移量,条目数

3.5、练习题

-- 1.where子句可否使用组函数进行过滤? 
-- 不可以

-- 2.查询公司员工工资的最大值，最小值，平均值，总和 
SELECT MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees;

-- 3.查询各job_id的员工工资的最大值，最小值，平均值，总和 
SELECT job_id,MAX(salary),MIN(salary),AVG(salary),SUM(salary) FROM employees GROUP BY job_id;

-- 4.选择具有各个job_id的员工人数 
SELECT job_id,COUNT(employee_id) FROM employees GROUP BY job_id;

-- 5.查询员工最高工资和最低工资的差距（DIFFERENCE） 
SELECT MAX(salary)-MIN(salary) AS "DIFFERENCE" FROM employees;

-- 6.查询各个管理者手下员工的最低工资，其中最低工资不能低于6000，没有管理者的员工不计算在内 
SELECT manager_id,MIN(salary) 
FROM employees 
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) >= 6000;

-- 7.查询所有部门的名字，location_id，员工数量和平均工资，并按平均工资降序 
SELECT department_name,location_id,COUNT(employee_id),AVG(salary)
FROM employees e RIGHT JOIN departments d 
ON e.department_id = d.department_id 
GROUP BY department_name,location_id
-- having avg(salary) is not null
ORDER BY AVG(salary) DESC;

-- 8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.department_id = d.department_id 
GROUP BY department_name,job_id;