#字符串函数SELECT ASCII('abcdf'),CHAR_LENGTH('hello') ,CHAR_LENGTH('诗栋'),LENGTH('helllo'),LENGTH('我嗯')from DUAL;SELECT CONCAT(name,'-work for-',id) "details"FROM students;#日期和时间函数#获取日期,时间SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),#-09-24 --09-24- 09:42:57 --09-24 09:42:57- -09-24 09:42:57 UTC_DATE(),UTC_TIME()#--09-24-01:42:57FROM DUAL;#日期与时间的转换SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1663984252),UNIX_TIMESTAMP('-09-24 09:50:52'),FROM_UNIXTIME(1663984252)FROM DUAL;SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),WEEKDAY('-09-25'),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())FROM DUAL;#日期的操作函数----》》extract(type FROM data)type指定返回的值SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR FROM NOW()),EXTRACT(MINUTE FROM NOW()),EXTRACT(SECOND FROM NOW()),EXTRACT(QUARTER FROM NOW())FROM DUAL;#时间和秒钟的转换函数 TIME_TO_SEC(time)将time转化为秒返回结果值;#SEC_TO_TIME(seconds)TIME(second) 将second描述转化为包含小时、分钟、秒的时间SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(9000)FROM DUAL;#计算日期和时间的函数 # DATE_ADD(date,INTERVAL expr unit)或ADDDATE(date,INTERVAL expr unit)返回与给定时间相差INTERVAL时间段的日期时间#DATE_SUB(date,INTERVAL expr unit)返回与data相差INTERVAL时间间隔的日期SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1 YEAR),#-09-26 00:48:01;-09-26 00:48:01DATE_SUB(NOW(),INTERVAL 1 YEAR),#-09-26 00:53:59DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH)#-10-26 00:58:14;加1年1个月FROM DUAL;#日期的格式化与解析,格式化:日期---》字符串,解析:字符串————》日期#DATE_FORMAT(date,format)按照字符串format格式转化日期date值SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%d'),#-September-26DATE_FORMAT(NOW(),'%Y-%M-%d'),#-September-26TIME_FORMAT(CURDATE(),'%H:%i:%s'),#00:00:00DATE_FORMAT(CURDATE(),'%Y-%M-%d %h:%i:%s %W %T %r')#-September-26 12:00:00 Monday 00:00:00 12:00:00 AMFROM DUAL;#解析:格式化的逆过程·SELECT STR_TO_DATE('-October-26 12:30:20 Monday 1','%Y-%M-%d %h:%i:%s %W')#-10-26 00:30:20FROM DUAL;SELECT GET_FORMAT(DATE, 'USA')#%m.%d.%YSELECT GET_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA'))FROM DUAL;#流程控制函数#IF(VALUE,VALUE1,VALUE2)SELECT name,salary,IF(salary>=6000,'高工资','低工资')FROM employees;#IFNULL(expr1,expr2)如果expr1是null则输出expr2否则输出本身#CASE#相当于java的if....elseSELECT last_name,CASE WHEN salary>=2000 THEN '孙悟空'WHEN salary>=5000 THEN '孙悟净'WHEN salary>=9000 THEN '孙悟能'ELT '曹耿'END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3ELSE salary*1.4 END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3END "details"FROM employees;WHERE department_id IN(10,20,30);#5-加密与解密函数#PASSWORD()在mysql8.0被弃用#加密SELECT MD5('mysql'),SHA('mysql') #81c3b080dad537de7e10e0987a4bf52eFROM DUAL;#f460c882a18c1304d88854e902e11b85d71e7e1b#加密:#ENCODE(str,,)/DECODE(crypt_str,pass_str):返回pass_str作为加密密码加密value在mysql8.0被弃用#解密:#DECODE(crypt_str,pass_str):返回pass_str作为解密密码解密value 在mysql8.0被弃用SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')FROM DUAL;#在mysql8.0被弃用,5.7可以#[SQL]SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')#FROM DUAL;#[Err] 1305 - FUNCTION dbtest.ENCODE does not exist#mysql信息函数SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER(),CHARSET('shenlidong'),COLLATION('shenlidong')FROM DUAL;#其他函数SELECT FORMAT(123.123,2),FORMAT(123.125,0),FORMAT(123.125,2)FROM DUAL;SELECT CONV(16,10,2),CONV(8888,10,16),CONV(NULL,10,2)FROM DUAL;#BENCHMARK(count,expr):用于测试表达式的执行效率(时间)SELECT INET_ATON('192.168.10.1')from dual;SELECT INET_ATON('192.168.10.1'),CHARSET(CONVERT('shenlidong' USING 'utf8')}from dual;#3232238081/*SELECT employeesINSERT INTO employees VALUES(1002,'wangwu',1500);INSERT INTO employees VALUES(1003,'likui',2300);INSERT INTO employees VALUES(1004,'shimin',3000);INSERT INTO employees VALUES(1005,'liyuan',3500);FROM employees;*/#8-聚合函数#8-1AVG() SUM() SELECT id,name,salary,AVG(salary),SUM(salary),SUM(id)FROM employees;#8-2 MAx() MIN() SELECT MAX(salary),MIN(salary)FROM employees;SELECT MAX(name),MIN(NAME)#wangwu lisiFROM employees;#8-3 COUNT()#1.计算指定字段在查询结构中出现的个数(不包含有Null值的)SELECT COUNT(id),COUNT(salary),COUNT(salary*3),COUNT(1),COUNT(*)#4 4 4 4 4FROM employees;#如果计算表中有多少条记录,如何实现?#方式一:COUNT(*)#方式二:COUNT(1)‘#方式三:COUNT(具体字段):不一定对!#2-注意:计算指定字段出现的个数时,是不计算有null值的。#3-SELECT AVG(salary),SUM(salary)/COUNT(salary) aver #2550.0000 2550.0000FROM employees;#。。。。。。。。。。。。。。。。。SELECT AVG(IFNULL(salary,1))aver1,SUM(salary)/COUNT(IFNULL(salary,1)) aver2 #2550.0000 2550.0000FROM employees;#SELECT*FROM employees;#方差 标准差 中位数等
#字符串函数SELECT ASCII('abcdf'),CHAR_LENGTH('hello') ,CHAR_LENGTH('诗栋'),LENGTH('helllo'),LENGTH('我嗯')from DUAL;SELECT CONCAT(name,'-work for-',id) "details"FROM students;#日期和时间函数#获取日期,时间SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),#-09-24 --09-24- 09:42:57 --09-24 09:42:57- -09-24 09:42:57 UTC_DATE(),UTC_TIME()#--09-24-01:42:57FROM DUAL;#日期与时间的转换SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1663984252),UNIX_TIMESTAMP('-09-24 09:50:52'),FROM_UNIXTIME(1663984252)FROM DUAL;SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),WEEKDAY('-09-25'),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())FROM DUAL;#日期的操作函数----》》extract(type FROM data)type指定返回的值SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR FROM NOW()),EXTRACT(MINUTE FROM NOW()),EXTRACT(SECOND FROM NOW()),EXTRACT(QUARTER FROM NOW())FROM DUAL;#时间和秒钟的转换函数 TIME_TO_SEC(time)将time转化为秒返回结果值;#SEC_TO_TIME(seconds)TIME(second) 将second描述转化为包含小时、分钟、秒的时间SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(9000)FROM DUAL;#计算日期和时间的函数 # DATE_ADD(date,INTERVAL expr unit)或ADDDATE(date,INTERVAL expr unit)返回与给定时间相差INTERVAL时间段的日期时间#DATE_SUB(date,INTERVAL expr unit)返回与data相差INTERVAL时间间隔的日期SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1 YEAR),#-09-26 00:48:01;-09-26 00:48:01DATE_SUB(NOW(),INTERVAL 1 YEAR),#-09-26 00:53:59DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH)#-10-26 00:58:14;加1年1个月FROM DUAL;#日期的格式化与解析,格式化:日期---》字符串,解析:字符串————》日期#DATE_FORMAT(date,format)按照字符串format格式转化日期date值SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%d'),#-September-26DATE_FORMAT(NOW(),'%Y-%M-%d'),#-September-26TIME_FORMAT(CURDATE(),'%H:%i:%s'),#00:00:00DATE_FORMAT(CURDATE(),'%Y-%M-%d %h:%i:%s %W %T %r')#-September-26 12:00:00 Monday 00:00:00 12:00:00 AMFROM DUAL;#解析:格式化的逆过程·SELECT STR_TO_DATE('-October-26 12:30:20 Monday 1','%Y-%M-%d %h:%i:%s %W')#-10-26 00:30:20FROM DUAL;SELECT GET_FORMAT(DATE, 'USA')#%m.%d.%YSELECT GET_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA'))FROM DUAL;#流程控制函数#IF(VALUE,VALUE1,VALUE2)SELECT name,salary,IF(salary>=6000,'高工资','低工资')FROM employees;#IFNULL(expr1,expr2)如果expr1是null则输出expr2否则输出本身#CASE#相当于java的if....elseSELECT last_name,CASE WHEN salary>=2000 THEN '孙悟空'WHEN salary>=5000 THEN '孙悟净'WHEN salary>=9000 THEN '孙悟能'ELT '曹耿'END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3ELSE salary*1.4 END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3END "details"FROM employees;WHERE department_id IN(10,20,30);#5-加密与解密函数#PASSWORD()在mysql8.0被弃用#加密SELECT MD5('mysql'),SHA('mysql') #81c3b080dad537de7e10e0987a4bf52eFROM DUAL;#f460c882a18c1304d88854e902e11b85d71e7e1b#加密:#ENCODE(str,,)/DECODE(crypt_str,pass_str):返回pass_str作为加密密码加密value在mysql8.0被弃用#解密:#DECODE(crypt_str,pass_str):返回pass_str作为解密密码解密value 在mysql8.0被弃用SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')FROM DUAL;#在mysql8.0被弃用,5.7可以#[SQL]SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')#FROM DUAL;#[Err] 1305 - FUNCTION dbtest.ENCODE does not exist#mysql信息函数SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER(),CHARSET('shenlidong'),COLLATION('shenlidong')FROM DUAL;#其他函数SELECT FORMAT(123.123,2),FORMAT(123.125,0),FORMAT(123.125,2)FROM DUAL;SELECT CONV(16,10,2),CONV(8888,10,16),CONV(NULL,10,2)FROM DUAL;#BENCHMARK(count,expr):用于测试表达式的执行效率(时间)SELECT INET_ATON('192.168.10.1')from dual;SELECT INET_ATON('192.168.10.1'),CHARSET(CONVERT('shenlidong' USING 'utf8')}from dual;#3232238081/*SELECT employeesINSERT INTO employees VALUES(1002,'wangwu',1500);INSERT INTO employees VALUES(1003,'likui',2300);INSERT INTO employees VALUES(1004,'shimin',3000);INSERT INTO employees VALUES(1005,'liyuan',3500);FROM employees;*/#8-1聚合函数#8-1-1AVG() SUM() SELECT id,name,salary,AVG(salary),SUM(salary),SUM(id)FROM employees;#8-1-2 MAx() MIN() SELECT MAX(salary),MIN(salary)FROM employees;SELECT MAX(name),MIN(NAME)#wangwu lisiFROM employees;#8-1-3 COUNT()#1.计算指定字段在查询结构中出现的个数(不包含有Null值的)SELECT COUNT(id),COUNT(salary),COUNT(salary*3),COUNT(1),COUNT(*)#4 4 4 4 4FROM employees;#如果计算表中有多少条记录,如何实现?#方式一:COUNT(*)#方式二:COUNT(1)‘#方式三:COUNT(具体字段):不一定对!#2-注意:计算指定字段出现的个数时,是不计算有null值的。#3-SELECT AVG(salary),SUM(salary)/COUNT(salary) aver #2550.0000 2550.0000FROM employees;#。。。。。。。。。。。。。。。。。SELECT AVG(IFNULL(salary,1))aver1,SUM(salary)/COUNT(IFNULL(salary,1)) aver2 #2550.0000 2550.0000FROM employees;#SELECT*FROM employees;#方差 标准差 中位数等#8-2 GROUP BY 的使用#需求:查询各个部门的平均工资、最高工资SELECT id,AVG(salary),SUM(salary)FROM employeesGROUP BY id#结论1:select中出现的非组函数的字段必须声明在group BY中#反之,group BY中声明的字段可以不出现在select中。#结论2:GROUP BY 声明在from后面、where后面,Order BY前面、limit前面#结论3:MySql中GROUP BY使用with ROLLUPSELECT id,AVG(salary),SUM(salary)FROM employeesGROUP BY id WITH ROLLUP #计算整体的平均 : AVG(salary): 2550.0000 SUM(salary):10200#需求:查询各个部门的平均工资,按照低到高排列SELECT id,AVG(salary) aver_salFROM employeesGROUP BY idORDER BY aver_sal ASC;SELECT id,AVG(salary) aver_salFROM employeesGROUP BY id WITH ROLLUPORDER BY aver_sal;#8-3 HAVING 的使用
#字符串函数SELECT ASCII('abcdf'),CHAR_LENGTH('hello') ,CHAR_LENGTH('诗栋'),LENGTH('helllo'),LENGTH('我嗯')from DUAL;SELECT CONCAT(name,'-work for-',id) "details"FROM students;#日期和时间函数#获取日期,时间SELECT CURDATE(),CURRENT_DATE(),CURTIME(),NOW(),SYSDATE(),#-09-24 --09-24- 09:42:57 --09-24 09:42:57- -09-24 09:42:57 UTC_DATE(),UTC_TIME()#--09-24-01:42:57FROM DUAL;#日期与时间的转换SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1663984252),UNIX_TIMESTAMP('-09-24 09:50:52'),FROM_UNIXTIME(1663984252)FROM DUAL;SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),WEEKDAY('-09-25'),HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())FROM DUAL;#日期的操作函数----》》extract(type FROM data)type指定返回的值SELECT EXTRACT(YEAR FROM NOW()),EXTRACT(MONTH FROM NOW()),EXTRACT(DAY FROM NOW()),EXTRACT(HOUR FROM NOW()),EXTRACT(MINUTE FROM NOW()),EXTRACT(SECOND FROM NOW()),EXTRACT(QUARTER FROM NOW())FROM DUAL;#时间和秒钟的转换函数 TIME_TO_SEC(time)将time转化为秒返回结果值;#SEC_TO_TIME(seconds)TIME(second) 将second描述转化为包含小时、分钟、秒的时间SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(9000)FROM DUAL;#计算日期和时间的函数 # DATE_ADD(date,INTERVAL expr unit)或ADDDATE(date,INTERVAL expr unit)返回与给定时间相差INTERVAL时间段的日期时间#DATE_SUB(date,INTERVAL expr unit)返回与data相差INTERVAL时间间隔的日期SELECT NOW(), DATE_ADD(NOW(),INTERVAL 1 YEAR),#-09-26 00:48:01;-09-26 00:48:01DATE_SUB(NOW(),INTERVAL 1 YEAR),#-09-26 00:53:59DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH)#-10-26 00:58:14;加1年1个月FROM DUAL;#日期的格式化与解析,格式化:日期---》字符串,解析:字符串————》日期#DATE_FORMAT(date,format)按照字符串format格式转化日期date值SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%d'),#-September-26DATE_FORMAT(NOW(),'%Y-%M-%d'),#-September-26TIME_FORMAT(CURDATE(),'%H:%i:%s'),#00:00:00DATE_FORMAT(CURDATE(),'%Y-%M-%d %h:%i:%s %W %T %r')#-September-26 12:00:00 Monday 00:00:00 12:00:00 AMFROM DUAL;#解析:格式化的逆过程·SELECT STR_TO_DATE('-October-26 12:30:20 Monday 1','%Y-%M-%d %h:%i:%s %W')#-10-26 00:30:20FROM DUAL;SELECT GET_FORMAT(DATE, 'USA')#%m.%d.%YSELECT GET_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA'))FROM DUAL;#流程控制函数#IF(VALUE,VALUE1,VALUE2)SELECT name,salary,IF(salary>=6000,'高工资','低工资')FROM employees;#IFNULL(expr1,expr2)如果expr1是null则输出expr2否则输出本身#CASE#相当于java的if....elseSELECT last_name,CASE WHEN salary>=2000 THEN '孙悟空'WHEN salary>=5000 THEN '孙悟净'WHEN salary>=9000 THEN '孙悟能'ELT '曹耿'END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3ELSE salary*1.4 END "details"FROM employees;#相当于java的switch#CASE....WHEN.......THEN........WHEN....THEN...ELSE....endSELECT employee_id,last_name,department_id,salary,CASE department-id WHEN 10 THEN salary *1.1WHEN 20 THEN salary*1.2WHEN 20 THEN salary*1.3END "details"FROM employees;WHERE department_id IN(10,20,30);#5-加密与解密函数#PASSWORD()在mysql8.0被弃用#加密SELECT MD5('mysql'),SHA('mysql') #81c3b080dad537de7e10e0987a4bf52eFROM DUAL;#f460c882a18c1304d88854e902e11b85d71e7e1b#加密:#ENCODE(str,,)/DECODE(crypt_str,pass_str):返回pass_str作为加密密码加密value在mysql8.0被弃用#解密:#DECODE(crypt_str,pass_str):返回pass_str作为解密密码解密value 在mysql8.0被弃用SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')FROM DUAL;#在mysql8.0被弃用,5.7可以#[SQL]SELECT ENCODE('shenlidong','mysql'),ENCODE('shenlidong','mysql','mysql')#FROM DUAL;#[Err] 1305 - FUNCTION dbtest.ENCODE does not exist#mysql信息函数SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),USER(),CURRENT_USER(),CHARSET('shenlidong'),COLLATION('shenlidong')FROM DUAL;#其他函数SELECT FORMAT(123.123,2),FORMAT(123.125,0),FORMAT(123.125,2)FROM DUAL;SELECT CONV(16,10,2),CONV(8888,10,16),CONV(NULL,10,2)FROM DUAL;#BENCHMARK(count,expr):用于测试表达式的执行效率(时间)SELECT INET_ATON('192.168.10.1')from dual;SELECT INET_ATON('192.168.10.1'),CHARSET(CONVERT('shenlidong' USING 'utf8')}from dual;#3232238081/*SELECT employeesINSERT INTO employees VALUES(1002,'wangwu',1500);INSERT INTO employees VALUES(1003,'likui',2300);INSERT INTO employees VALUES(1004,'shimin',3000);INSERT INTO employees VALUES(1005,'liyuan',3500);FROM employees;*/#8-1聚合函数#8-1-1AVG() SUM() SELECT id,name,salary,AVG(salary),SUM(salary),SUM(id)FROM employees;#8-1-2 MAx() MIN() SELECT MAX(salary),MIN(salary)FROM employees;SELECT MAX(name),MIN(NAME)#wangwu lisiFROM employees;#8-1-3 COUNT()#1.计算指定字段在查询结构中出现的个数(不包含有Null值的)SELECT COUNT(id),COUNT(salary),COUNT(salary*3),COUNT(1),COUNT(*)#4 4 4 4 4FROM employees;#如果计算表中有多少条记录,如何实现?#方式一:COUNT(*)#方式二:COUNT(1)‘#方式三:COUNT(具体字段):不一定对!#2-注意:计算指定字段出现的个数时,是不计算有null值的。#3-SELECT AVG(salary),SUM(salary)/COUNT(salary) aver #2550.0000 2550.0000FROM employees;#。。。。。。。。。。。。。。。。。SELECT AVG(IFNULL(salary,1))aver1,SUM(salary)/COUNT(IFNULL(salary,1)) aver2 #2550.0000 2550.0000FROM employees;#SELECT*FROM employees;#方差 标准差 中位数等#8-2 GROUP BY 的使用#需求:查询各个部门的平均工资、最高工资SELECT id,AVG(salary),SUM(salary)FROM employeesGROUP BY id#结论1:select中出现的非组函数的字段必须声明在group BY中#反之,group BY中声明的字段可以不出现在select中。#结论2:GROUP BY 声明在from后面、where后面,Order BY前面、limit前面#结论3:MySql中GROUP BY使用with ROLLUPSELECT id,AVG(salary),SUM(salary)FROM employeesGROUP BY id WITH ROLLUP #计算整体的平均 : AVG(salary): 2550.0000 SUM(salary):10200#需求:查询各个部门的平均工资,按照低到高排列SELECT id,AVG(salary) aver_salFROM employeesGROUP BY idORDER BY aver_sal ASC;SELECT id,AVG(salary) aver_salFROM employeesGROUP BY id WITH ROLLUPORDER BY aver_sal;#8-3 HAVING 的使用(作用:用来过滤数据的)SELECT id,MAX(salary)FROM employees#WHERE MAX(salary)>1000 #错误方式过滤GROUP BY id;#要求1:如果过滤条件中使用了聚合函数,则必须使用HAVING来替换where。否则,报错#要求2:HAVING必须声明在GROUP BY 后面SELECT id,MAX(salary)FROM employeesGROUP BY idHAVING MAX(salary)>2000;#开发中,我们使用HAVING的前提是SQL中使用了GROUP BY#需求:查employees中id为1001,1002,1004中的比2000的最高工资#方式一:(推荐使用,执行效率高于方式二)SELECT id,MAX(salary)FROM employeesWHERE id IN(1001,1002,1004)GROUP BY idHAVING MAX(salary)>2000;#方式二:SELECT id,MAX(salary)FROM employeesWHERE id IN(1001,1002,1004)GROUP BY idHAVING MAX(salary)>2000 AND id IN(1001,1002,1004);#结论:当过滤条件中有聚合函数时,则此过滤条件必须声明在HAVING# 当过滤条件中没有聚合函数时,则此过滤条件声明在WHERE或HAVING中都可以。但是,建议声明在WHERE中/*WHERE和HAVING的对比:1-从适用范围上:HAVING适用范围更广HAVING 可以完成 WHERE 不能完成的任务。这是因为,在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成的。另外,WHERE排除的记录不再包括在分组中。2-如果过滤条件中没有聚合函数:这种情况下,WHERE的执行效率要高于HAVING如果需要通过连接从关联表中获取需要的数据,WHERE 是先筛选后连接,而 HAVING 是先连接后筛选。*/#4. SQL底层执行原理#SELECT语句的完整结构/*sql92语法:SELECT ...,...,...(存在聚合函数)FROM ...,...,...,...WHERE 多表连接条件 AND 不包含组(聚合函数)函数的过滤条件GROUP BY 包含组函数的过滤条件ORDER BY ... (ASC/DESC)LIMIT ...,...(分页操作)#sql99语法:SELECT ...,...,...(存在聚合函数)FROM ...(LEFT/RIGHT)JOIN...ON 多表连接条件...(LEFT/RIGHT)JOIN...ONWHERE 不包含组(聚合函数)函数的过滤条件GROUP BY 包含组函数的过滤条件ORDER BY ... (ASC/DESC)LIMIT ...,...(分页操作)*/#4.SQL语句的执行过程:#FROM...,...-->ON-->(LEFT/RIGHT JOIN)-->WHERE-->GROUP BY-->HAVING-->SELECT-->DISTINCT(去重)-->ORDER BY-->LIMIT
3.2 WHERE和HAVING的对比区别1:WHERE可以直接使用表中的字段作为筛选条件,但不能使用分组中的计算函数作为筛选条件;HAVING必须要与GROUP BY配合使用,可以把分组计算的函数和分组字段作为筛选条件。这决定了,在需要对数据进行分组统计的时候,HAVING 可以完成 WHERE 不能完成的任务。这是因为, 在查询语法结构中,WHERE 在 GROUP BY 之前,所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之 后,可以使用分组字段和分组中的计算函数,对分组的结果集进行筛选,这个功能是 WHERE 无法完成 的。另外,WHERE排除的记录不再包括在分组中。区别2:如果需要通过连接从关联表中获取需要的数据,WHERE是先筛选后连接,而HAVING是先连接后筛选。这一点,就决定了在关联查询中,WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选,用一 个筛选后的较小数据集和关联表进行连接,这样占用的资源比较少,执行效率也比较高。HAVING 则需要 先把结果集准备好,也就是用未被筛选的数据集进行关联,然后对这个大的数据集进行筛选,这样占用 的资源就比较多,执行效率也较低。
开发中的选择:WHERE 和 HAVING 也不是互相排斥的,我们可以在一个查询里面同时使用 WHERE 和 HAVING。包含分组 统计函数的条件用 HAVING,普通条件用 WHERE。这样,我们就既利用了 WHERE 条件的高效快速,又发 挥了 HAVING 可以使用包含分组统计函数的查询条件的优点。当数据量特别大的时候,运行效率会有很 大的差别。
#方式1: SELECT ...,....,... FROM ...,...,.... WHERE 多表的连接条件 AND 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #方式2: SELECT ...,....,... FROM ... JOIN ... ON 多表的连接条件 JOIN ... ON ... WHERE 不包含组函数的过滤条件 AND/OR 不包含组函数的过滤条件 GROUP BY ...,... HAVING 包含组函数的过滤条件 ORDER BY ... ASC/DESC LIMIT ...,... #其中: #(1)from:从哪些表中筛选 #(2)on:关联多表查询时,去除笛卡尔积 #(3)where:从表中筛选的条件 #(4)group by:分组依据 #(5)having:在统计结果中再次筛选 #(6)order by:排序 #(7)limit:分页
1.关键字的顺序是不能颠倒的:
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...
2.SELECT语句的执行顺序
#.SQL语句的执行过程:
#FROM...,...-->ON-->(LEFT/RIGHT JOIN)-->WHERE-->GROUP BY-->HAVING-->SELECT-->DISTINCT(去重)-->ORDER BY-->LIMIT
如果觉得《数据库学习——10-13-聚合函数+GROUP BY+HAVING学习》对你有帮助,请点赞、收藏,并留下你的观点哦!