目录
IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
CASE WHEN ... THEN ...WHEN ... THEN ... 【ELSE】 ... END 可选
CASE ... WHEN ... THEN ... WHEN ... THEN ... 【ELSE】 ... END 可选
函数的理解
MySQL的内置函数及分类
MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。
两种SQL函数
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
函数 用法 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(VALUE,VALUE1,VALUE2)
SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details" FROM employees;
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details", salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal" FROM employees;
IFNULL(VALUE1,VALUE2):看做是IF(VALUE,VALUE1,VALUE2)的特殊情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details" FROM employees;
CASE WHEN ... THEN ...WHEN ... THEN ... 【ELSE】 ... END 可选
类似于java的if ... else if ... else if ... else
语句1
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' ELSE '草根' END "details",department_id FROM employees;结果
语句2 else 可选
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精' WHEN salary >= 10000 THEN '潜力股' WHEN salary >= 8000 THEN '小屌丝' END "details" FROM employees;
CASE ... WHEN ... THEN ... WHEN ... THEN ... 【ELSE】 ... END 可选
类似于java的swich ... case...
练习1：
查询员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数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 "details" FROM employees;
加密与解密函数
PASSWORD(str) \ ENCODE() \ DECODE() 在 mysql8.0中弃用
函数 用法 PASSWORD(str) 返回字符串str的加密版本,41位长的字符串。加密结果 不可逆 ,常用于用户的密码加密 MD5(str) 返回字符串str的md5加密后的值,也是一种加密方式。若参数为 NULL,则会返回NULL SHA(str) 从原明文密码str计算并返回加密后的密码字符串,当参数为 NULL时,返回NULL。 SHA加密算法比MD5更加安全 。 ENCODE(value,password_seed) 返回使用password_seed作为加密密码加密value DECODE(value,password_seed) 返回使用password_seed作为加密密码解密value # PASSWORD()在mysql8.0中弃用。 SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql')) FROM DUAL; SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql') FROM DUAL;
MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
函数 用法 VERSION() 返回当前MySQL的版本号 CONNECTION_ID() 返回当前MySQL服务器的连接数 DATABASE(),SCHEMA() 返回MySQL命令行当前所在的数据库 USER(),CURRENT_USER()、SYSTEM_USER(),SESSION_USER() 返回当前连接MySQL的用户名,返回结果格式为“主机名@用户名” CHARSET(value) 返回字符串value自变量的字符集 COLLATION(value) 返回字符串value的比较规则 SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(), USER(),CURRENT_USER(),CHARSET('张三'),COLLATION('张三') FROM DUAL;
其他函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的
函数 用法 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 # 1、如果n的值小于或者等于0，则只保留整数部分 SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2) FROM DUAL; # 2、进制转换 SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2) FROM DUAL; # 3、以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。 SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876) FROM DUAL; # 4、BENCHMARK()用于测试表达式的执行效率 SELECT BENCHMARK(100000,MD5('mysql')) FROM DUAL; # 5、CONVERT():可以实现字符集的转换 SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk')) FROM DUAL;
数值函数
单行函数可以嵌套
基本函数
函数
用法 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
基本操作
绝对值、取符号、圆周率
# 返回x 的绝对值 SELECT ABS(-124),ABS(1234); # 返回 x 的符号 SELECT SIGN(-123),SIGN(124),SIGN(0); # 返回圆周率的值 SELECT PI();
天花板，地板，平方根
# 天花板、地板、平方根 --> 返回x的平方根。当X的值为负数时，返回NULL SELECT CEIL(32.32) AS "天花板",CEILING(-43.23)AS "天花板", FLOOR(32.32) AS "地板",FLOOR(-43.23) AS "地板", SQRT(4) AS "平方根",SQRT(9) AS "平方根",SQRT(-5) AS "平方根";
最大值、最小值、余数
# LEAST() 列表最小值、GREATEST() 列表最大值、MOD() 余数 SELECT LEAST(21,25,54,1,-14),GREATEST(21,25,54,1,-14), MOD(25,3),MOD(25,-3),MOD(-24,3),MOD(-24,-3);
随机数
# RAND() 返回0~1的随机值 # RAND(x) 返回0~1的随机值，其中x的值用作种子值，相同的X值会产生相同的随机数 # 获取一个 10以内的随机数 SELECT RAND() * 10,RAND(2),RAND(2);
四舍五入，截断操作
# ROUND(x) 四舍五入,取整 # ROUND(x,y) 四舍五入,保留几位小数 # TRUNCATE(x,y) 返回数字x截断为y位小数的结果 SELECT ROUND(123.556),ROUND(135.5),ROUND(135.6),ROUND(123.456,0),ROUND(123.456,1),ROUND(123.456,2), ROUND(123.456,-1),ROUND(153.456,-2) FROM DUAL; SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1),TRUNCATE(129.45,-2),TRUNCATE(129.45,-3) FROM DUAL;
角度与弧度互换函数
函数 用法 RADIANS(x) 将角度转化为弧度，其中，参数x为角度值 DEGREES(x) 将弧度转化为角度，其中，参数x为弧度值 #角度与弧度的互换 # 1/12π、1/8π、1/6π、1/4π、2π、360 60度转化为弧度，在转化为度数 SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90), DEGREES(2*PI()),DEGREES(RADIANS(60)) FROM DUAL;
三角函数
函数 用法 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(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)) FROM DUAL;
指数与对数
函数 用法 POW(x,y),POWER(X,Y) 返回x的y次方 EXP(X) 返回e的X次方，其中e是一个常数，2.718281828459045 LN(X)，LOG(X) 返回以e为底的X的对数，当X <= 0 时，返回的结果为NULL LOG10(X) 返回以10为底的X的对数，当X <= 0 时，返回的结果为NULL LOG2(X) 返回以2为底的X的对数，当X <= 0 时，返回NULL
进制间的转换
函数 用法 BIN(x) 返回x的二进制编码 HEX(x) 返回x的十六进制编码 OCT(x) 返回x的八进制编码 CONV(x,f1,f2) 返回f1进制数变成f2进制数 SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8) FROM DUAL;
注意：MySQL中，字符串的位置是从1开始的。
字符串函数
函数 函数 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) 或 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个字符 LTRIM(s) 去掉字符串s左侧的空格 RTRIM(s) 去掉字符串s右侧的空格 TRIM(s) 去掉字符串s开始与结尾的空格 TRIM(s FROM s1) 去掉字符串s开始与结尾的s1 TRIM(LEADING s FROMs2) 去掉字符串s开始处的s1 TRIM(TRAILING s FROMs2) 去掉字符串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在字符串列表中第一次出现的位置 IND_IN_SET(s1,s2) 返回字符串s1在字符串s2中出现的位置。
其中,字符串s2是一个以逗号分 隔的字符串
REVERSE(s) 返回s反转后的字符串 NULLIF(value1,value2) 比较两个字符串,如果value1与value2相等,
则返回NULL,否则返回 value1
常用的字符串
返回ASCII码值、字符数、字节数
# ASCII(S) 返回字符串S中的第一个字符的ASCII码值 # CHAR_LENGTH(s) 返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同 # LENGTH(s) 返回字符串s的字节数,和字符集有关 SELECT ASCII('a'),ASCII('A'); SELECT CHAR_LENGTH('string'),CHAR_LENGTH('中国'); SELECT LENGTH('string'),LENGTH('中国');
连接
CONCAT(s1,s2,......,sn)
连接s1,s2,......,sn为一个字符串
CONCAT_WS(x,s1,s2,......,sn)
同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x
# CONCAT(s1,s2,......,sn) 连接s1,s2,......,sn为一个字符串 # CONCAT_WS(x,s1,s2,......,sn) 同CONCAT(s1,s2,...)函数,但是每个字符串之间要加上x # 单表 SELECT CONCAT_WS ('-','我叫',last_name,first_name) AS "姓名" FROM `employees` # 多表 SELECT CONCAT(emp.last_name,' worked for ',mgr.last_name) "details" FROM employees emp JOIN employees mgr WHERE emp.`manager_id` = mgr.employee_id;
替换、索引区间替换全部替换
# INSERT(str, idx, len,replacestr) 将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr # REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a SELECT INSERT('my name is zhangsan',12,18,'lisi'),REPLACE('my name is zhangsan','a','3');
转换成大写/小写
MySQL 不严谨 不区分大小写
# UPPER(s) 或 UCASE(s) 将字符串s的所有字母转成大写字母 # LOWER(s) 或LCASE(s) 将字符串s的所有字母转成小写字母 SELECT UPPER('HelLo MY name WANGWU'),LOWER('HelLoMYname lilei') FROM DUAL;
返回（截取）字符串、拼接字符串
# 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个字符 SELECT LEFT('my name is zhangsan',2), RIGHT('my name is zhangsan',8), LPAD('my name is zhangsan',25,'aaaaa-'), RPAD('my name is zhangsan',25,'-aaaaa');
去除空格（开头、结尾、开头结尾）去除字符（开头、结尾、开头结尾）
# LTRIM(s) 去掉字符串s左侧的空格 # RTRIM(s) 去掉字符串s右侧的空格 # TRIM(s) 去掉字符串s开始与结尾的空格 # TRIM(s FROM s1) 去掉字符串s开始与结尾的s1 # TRIM(LEADING s FROMs2) 去掉字符串s开始处的s1 # TRIM(TRAILING s FROMs2) 去掉字符串s结尾处的s1 SELECT LTRIM(' my name is zhangsan') AS '王五1', # 去掉字符串s左侧的空格 RTRIM('my name is zhangsan ') AS '王五2', # 去掉字符串s右侧的空格 TRIM(' my name is zhangsan ') AS '王五3', # 去掉字符串s开始与结尾的空格 TRIM('assa' FROM 'assa my name is zhangsan assa') AS '王五4', # 去掉字符串s开始与结尾的s1 TRIM(LEADING 'assa' FROM 'assa my name is zhangsan assa') AS '王五5', # 去掉字符串s开始处的s1 TRIM(TRAILING 'assa' FROM 'assa my name is zhangsan assa') AS '王五6'; # 去掉字符串s结尾处的s1
返回字符串重复n次后的结果、比较两个字符串的ASCII值、返回n次空格
# REPEAT(str, n) 返回str重复n次后的结果 # SPACE(n) 返回n个空格 # STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小 SELECT REPEAT('hello',4),LENGTH(SPACE(5)), STRCMP('abc','abe'),STRCMP('abe','abc'),STRCMP('abe','abe') FROM DUAL;
返回字符串首次出现的位置 未找到返回0
# SUBSTR(s,index,len) 返回 字符串 从 索引 len 位后的字符，作用SUBSTRING(s,n,len)、MID(s,n,len)相同 # LOCATE(substr,str) 返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substrIN str)、INSTR(str,substr)相同。未找到，返回0 # 首次出现的位置 未找到返回0 SELECT SUBSTR('hello',2,4),SUBSTRING('hello',2,3),MID('hello',2,4) FROM DUAL; SELECT LOCATE('l','hello'),INSTR('hello','l'),POSITION('l' IN 'hello') FROM DUAL;
返回指定字符串第一次出现的位置
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是一个以逗号分隔的字符串
# ELT 返回指定的字符串 FIELD 返回第一次出现的次数 SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg') FROM DUAL;
反转字符串、比较两个字符串、相等返回value1，不相等返回null
#反转字符串 SELECT REVERSE('nasgnahz si eman'); # 比较两个字符 相等 null 否则返回 value1 前面字符 返回 null SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare" FROM employees;
日期和时间函数
获取日期、时间
函数 用法 CURDATE() ,CURRENT_DATE() 返回当前日期,只包含年、月、日 CURTIME() , CURRENT_TIME() 返回当前时间,只包含时、分、秒 NOW() / SYSDATE() / CURRENT_TIMESTAMP() /
LOCALTIME() /LOCALTIMESTAMP()
返回当前系统日期和时间 UTC_DATE() 返回UTC（世界标准时间）日期 UTC_TIME() 返回UTC（世界标准时间）时间 #获取日期、时间 SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(), UTC_DATE(),UTC_TIME() FROM DUAL;
拓展：把日期或者时间转换成数值
# 把日期或者时间转换成数值 SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0 FROM DUAL;
日期与时间戳的转换
函数 用法 UNIX_TIMESTAMP() 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 UNIX_TIMESTAMP(date) 将时间date以UNIX时间戳的形式返回。 FROM_UNIXTIME(timestamp) 将UNIX时间戳的时间转换为普通格式的时间 SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-04-14 00:13:57'),FROM_UNIXTIME(1649866390)
获取月份、星期、星期数、天数等函数
函数 用法 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 QUARTER(date) 返回日期对应的季度,范围为1～4 WEEK(date) , WEEKOFYEAR(date) 返回一年中的第几周 DAYOFYEAR(date) 返回日期是一年中的第几天 DAYOFMONTH(date) 返回日期位于所在月份的第几天 DAYOFWEEK(date) 这一周的第几天
注意：周日是1，周一是2，。。。周六是7
#获取月份、星期、星期数、天数等函数 SELECT YEAR(CURDATE()) "年",MONTH(CURDATE()) "月",DAY(CURDATE()) "天", HOUR(CURTIME()) "小时",MINUTE(NOW()) "分钟",SECOND(SYSDATE()) "秒" FROM DUAL;
SELECT MONTHNAME('2022-4-14') "月份",DAYNAME('2022-4-14') "星期几",WEEKDAY('2022-4-14') "周几", QUARTER(CURDATE()) "四季",WEEK(CURDATE()) "第几周",DAYOFYEAR(NOW()) "一年第几天", DAYOFMONTH(NOW()) "月份的第几天",DAYOFWEEK(NOW()) "这一周的第几天" FROM DUAL;
日期的操作函数
函数 用法 EXTRACT(type FROM date) 返回指定日期中特定的部分,type指定返回的值 EXTRACT(type FROM date)函数中type的取值与含义：
SELECT EXTRACT(SECOND FROM NOW()) "秒数",EXTRACT(DAY FROM NOW()) "天数", EXTRACT(HOUR_MINUTE FROM NOW()) "小时",EXTRACT(QUARTER FROM '2022-05-12')"季度" FROM DUAL;
时间和秒钟转换的函数
函数 用法 TIME_TO_SEC(time) 将 time 转化为秒并返回结果值。转化的公式为： 小时*3600+分钟*60+秒 SEC_TO_TIME(seconds) 将 seconds 描述转化为包含小时、分钟和秒的时间 SELECT NOW(),TIME_TO_SEC(CURTIME()), SEC_TO_TIME(2494) FROM DUAL;
计算日期和时间的函数
第一组
技巧：
只要记住一组即可，整数 加 负数 减
函数 用法 DATE_ADD(datetime, INTERVAL expr type),
ADDDATE(date,INTERVAL expr type)
返回与给定日期时间相差INTERVAL时 间段的日期时间 DATE_SUB(date,INTERVAL expr type),
SUBDATE(date,INTERVAL expr type)
返回与date相差INTERVAL时间间隔的 日期 上述函数中type的取值：
SELECT NOW(),DATE_ADD(NOW(),INTERVAL 1 YEAR), # 在 现在时间上加 一年 DATE_ADD(NOW(),INTERVAL -1 YEAR),# 在 现在时间上减 一年 DATE_SUB(NOW(),INTERVAL 1 YEAR) FROM DUAL;结果
语句2
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) "现在时间加上一天", DATE_ADD(NOW(),INTERVAL 1 SECOND) AS "现在时间加上一秒", ADDDATE(NOW(),INTERVAL 1 SECOND) "现在时间加上一秒，另一张写法", DATE_ADD(NOW(),INTERVAL '1_1' HOUR_MINUTE) AS "现在时间加上一小时和一分钟", DATE_ADD(NOW(), INTERVAL -1 YEAR) "现在时间减去一年", #可以是负数 DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) "现在时间加上一年和一个月" #需要单引号 # 加一年 同时 一个月 FROM DUAL;
&ensp；
函数 用法 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 ADDTIME(NOW(),20) "+20s", SUBTIME(NOW(),30) "-30s", SUBTIME(NOW(),'1:1:3') "-1小时1分钟3秒" FROM DUAL; SELECT # 差值 两个日期 DATEDIFF(NOW(),'2023-04-14') "返回现在时间 与 此时间的隔离天数", TIMEDIFF(NOW(),'2022-05-14 00:47:54') "返回现在时间 与 此时间的 时间间隔", FROM_DAYS(366) "返回从0000年1月1日起，366天以后的日期", TO_DAYS('0000-12-25') "返回日期date距离0000年1月1日的天数"; SELECT LAST_DAY(NOW()) "所在月份的最后一天", MAKEDATE(YEAR(NOW()),32) "返回现在的年份 2022 在 加上 数值【32】 为 2022-02-01", MAKETIME(10,21,23) "给定的时分秒组合", PERIOD_ADD(20200101010101,10) # 在此时间上加 10 MySQL 5.7 不好使 FROM DUAL;
日期的格式化与解析
格式化：日期 ---> 字符串
解析： 字符串 ----> 日期
函数 用法 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参数常用的格式符：
格式化：
#格式化： SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'), DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'), DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r') FROM DUAL;
解析：
#解析：格式化的逆过程 SELECT STR_TO_DATE('2022-April-14th 01:41:42 Thursday 4','%Y-%M-%D %h:%i:%S %W %w') FROM DUAL;
GET_FORMAT函数中date_type和format_type参数取值如下：
使用格式化类型
SELECT GET_FORMAT(DATE,'USA'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA')) FROM DUAL;
SELECT DATE_FORMAT(CURDATE(),'%m.%d.%Y'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'));
聚合函数介绍
什么是聚合函数
- 聚合函数作用于一组数据，并对一组数据返回一个值。
聚合函数类型
- AVG()
- SUM()
- MAX()
- MIN()
- COUNT()
聚合函数语法
聚合函数不能嵌套调用。
比如不能出现类似“AVG(SUM(字段名称))”形式的调用。
AVG / SUM ：只适用于数值类型的字段（或变量）
语句
SELECT AVG(salary),SUM(salary),AVG(salary) * 107 FROM employees;结果
拓展：字符串求总和：MySQL中 0 Order 直接报无效的数字
#如下的操作没有意义 SELECT SUM(last_name),AVG(last_name),SUM(hire_date) FROM employees;
MAX / MIN :适用于数值类型、字符串类型、日期时间类型的字段（或变量）
SELECT MAX(salary),MIN(salary) FROM employees;
字符串可以比较 大小
SELECT MAX(last_name),MIN(last_name),MAX(hire_date),MIN(hire_date) FROM employees;
COUNT：
- ① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）
- ② 注意：计算指定字段出现的个数时，是不计算NULL值的。
- ③ 公式：AVG = SUM / COUNT AVG SUM 默认也不计算 null
① 作用：计算指定字段在查询结构中出现的个数（不包含NULL值的）
SELECT COUNT(employee_id),COUNT(salary),COUNT(2 * salary),COUNT(1),COUNT(2),COUNT(*) FROM employees ;结果
② 注意：计算指定字段出现的个数时，是不计算NULL值的。
计算表中有多少条记录，如何实现？
- 方式1：COUNT(*)
- 方式2：COUNT(1) 常数
- 方式3：COUNT(具体字段) : 不一定对！
SELECT COUNT(commission_pct) FROM employees; SELECT commission_pct FROM employees WHERE commission_pct IS NOT NULL;
③ 公式：AVG = SUM / COUNT AVG SUM 默认也不计算 null
SELECT AVG(salary),SUM(salary)/COUNT(salary), AVG(commission_pct),SUM(commission_pct)/COUNT(commission_pct), SUM(commission_pct) / 107 FROM employees; -------------------------------- #需求：查询公司中平均奖金率 #错误的！ SELECT AVG(commission_pct) FROM employees; -------------------------------- #正确的： SELECT SUM(commission_pct) / COUNT(IFNULL(commission_pct,0)), AVG(IFNULL(commission_pct,0)) FROM employees; --------------------------------
如何需要统计表中的记录数，使用COUNT(*)、COUNT(1)、COUNT(具体字段) 哪个效率更高呢？
- 如果使用的是MyISAM 存储引擎，则三者效率相同，都是O(1)
- 如果使用的是InnoDB 存储引擎，则三者效率：COUNT(*) = COUNT(1)> COUNT(字段)
其他聚合函数：
如：方差、标准差、中位数
GROUP BY 的使用
单列分组
实例1：查询各个部门的平均工资，最高工资
#需求：查询各个部门的平均工资，最高工资 SELECT department_id,AVG(salary),SUM(salary) FROM employees GROUP BY department_id
实例2：查询各个job_id的平均工资
#需求：查询各个job_id的平均工资 SELECT job_id,AVG(salary) FROM employees GROUP BY job_id;结果
实例3：查询各个department_id,job_id的平均工资
#需求：查询各个department_id,job_id的平均工资 #方式1： SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id,job_id; #方式2： SELECT job_id,department_id,AVG(salary) FROM employees GROUP BY job_id,department_id;结果
注意：错误写法
MySQL出现数据，但是数据不对 ORDER 报不是GROUP BY 表达式
#错误的！ MySQL出现数据，但是数据不对 ORDER 不是GROUP BY 表达式 SELECT department_id,job_id,AVG(salary) FROM employees GROUP BY department_id;结论：
- 1：SELECT 中出现的 非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
- 2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
- 3：MySQL中 GROUP BY 中 使用 WITH ROLLUP
- 把所有的组分完之后，在把所有组当成一个组，在进行一次分组
SELECT department_id,AVG(salary) FROM employees # 把所有的组分完之后，在把所有组当成一个组，在进行一次分组 GROUP BY department_id WITH ROLLUP;
使用多个列分组
案例：查询各个部门的平均工资，按照平均工资升序排列
#需求：查询各个部门的平均工资，按照平均工资升序排列 SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id ORDER BY avg_sal ASC;
说明：
当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。 （最后一组数据无法进行排序）
#错误的： SELECT department_id,AVG(salary) avg_sal FROM employees GROUP BY department_id WITH ROLLUP ORDER BY avg_sal ASC;
HAVING
过滤分组：HAVING子句
1. 行已经被分组。
2. 使用了聚合函数。
3. 满足HAVING 子句中条件的分组将被显示。
4. HAVING 不能单独使用，必须要跟 GROUP BY 一起使用。
案例：查询 个个部门的最大工资，并且最大工资大于 1000的 部门
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000;
非法使用聚合函数 ： 不能在 WHERE 子句中使用聚合函数：
SELECT employee_id emp_id,last_name AS lname,department_id "部门id",salary * 12 AS "annual sal" FROM employees;
要求：
1、如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
2、HAVING 必须声明在 GROUP BY 的后面。
3、开发中，我们使用HAVING的前提是SQL中使用了GROUP BY。
案例：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息
#练习：查询部门id为10,20,30,40这4个部门中最高工资比10000高的部门信息 #方式1：推荐，执行效率高于方式2. SELECT department_id,MAX(salary) FROM employees WHERE department_id IN (10,20,30,40) GROUP BY department_id HAVING MAX(salary) > 10000; #方式2： SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) > 10000 AND department_id IN (10,20,30,40);
- 当过滤条件中有聚合函数时，则此过滤条件必须声明在HAVING中。
- 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中都可以。但是，建议大家声明在WHERE中。
WHERE 与 HAVING 的对比
1. 从适用范围上来讲，HAVING的适用范围更广。
2. 如果过滤条件中没有聚合函数：这种情况下，WHERE的执行效率要高于HAVING
优点 缺点 WHERE 先筛选数据再关联，执行效率高 不能使用分组中的计算函数进行筛选 HAVING 可以使用分组中的计算函数 在最后的结果集中进行筛选，执行效率较低
引出问题：为什么where 的效率要比 having的效率高？ 看SQL的执行原理
SQL底层执行原理
查询的结构
#sql92语法：
SELECT ....,....,....(存在聚合函数)
FROM ...,....,....
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件、不可以使用别名
GROUP BY ...,....
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....#sql99语法：
SELECT ....,....,....(存在聚合函数)
FROM ... (LEFT / RIGHT)JOIN ....ON 多表的连接条件
(LEFT / RIGHT)JOIN ... ON ....
WHERE 不包含聚合函数的过滤条件、不可以使用别名
GROUP BY ...,....AND/OR 不包含聚合函数的过滤条件
HAVING 包含聚合函数的过滤条件
ORDER BY ....,...(ASC / DESC )
LIMIT ...,....#其中：
（1）from：从哪些表中筛选
（2）on：关联多表查询时，去除笛卡尔积
（3）where：从表中筛选的条件
（4）group by：分组依据
（5）having：在统计结果中再次筛选
（6）order by：排序
（7）limit：分页
SQL语句的执行过程：
FROM ...,...-> ON -> (LEFT/RIGNT JOIN) -> WHERE -> GROUP BY ->HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT 
WHERE 为什么不包含聚合函数的过滤条件？
从执行过程来看 可以先前引用，但是没有办法向下引用
为什么where 的效率要比 having的效率高？
由SQL的执行顺序可知，where的执行在 having的前面，现在where 中过滤一些条件，分组时效率高很多，提升很大，如果此时 过滤条件写在 having 中 先分组后进行条件筛选，很多数据都是不需要的数据，无用功
477
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
