失眠网 > 数据库学习——10-13-聚合函数+GROUP BY+HAVING学习

数据库学习——10-13-聚合函数+GROUP BY+HAVING学习

时间：2020-12-08 20:48:57

#字符串函数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 的使用

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：分页