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

亦凉 2024-04-03 12:17 85阅读 0赞

#字符串函数
    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(),#2022-09-24 -2022-09-24- 09:42:57 -2022-09-24 09:42:57- 2022-09-24 09:42:57 
    UTC_DATE(),UTC_TIME()#-2022-09-24-01:42:57
    FROM DUAL;
    
    #日期与时间的转换
    SELECT UNIX_TIMESTAMP(),FROM_UNIXTIME(1663984252),UNIX_TIMESTAMP('2022-09-24 09:50:52'),
    FROM_UNIXTIME(1663984252)
    FROM DUAL;
    
    SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),WEEKDAY('2022-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),#2022-09-26 00:48:01;2023-09-26 00:48:01
    DATE_SUB(NOW(),INTERVAL 1 YEAR),#2021-09-26 00:53:59
    DATE_ADD(NOW(),INTERVAL '1_1' YEAR_MONTH)#2023-10-26 00:58:14;加1年1个月
    FROM DUAL;
    
    
    #日期的格式化与解析,格式化：日期---》字符串，解析：字符串————》日期
    #DATE_FORMAT(date,format)按照字符串format格式转化日期date值
    SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%d'),#2022-September-26
    DATE_FORMAT(NOW(),'%Y-%M-%d'),#2022-September-26
    TIME_FORMAT(CURDATE(),'%H:%i:%s'),#00:00:00
    DATE_FORMAT(CURDATE(),'%Y-%M-%d %h:%i:%s %W %T %r')#2022-September-26 12:00:00 Monday 00:00:00 12:00:00 AM
    FROM DUAL;
    #解析：格式化的逆过程·
    SELECT STR_TO_DATE('2022-October-26 12:30:20 Monday 1','%Y-%M-%d %h:%i:%s %W')#2022-10-26 00:30:20
    FROM DUAL;
    
    SELECT GET_FORMAT(DATE, 'USA')#%m.%d.%Y
    SELECT 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....else
    SELECT 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....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 20 THEN salary*1.3
    																												
    																																		 ELSE salary*1.4 END "details"
    
    FROM employees;
    
    
    #相当于java的switch
    #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 20 THEN salary*1.3																												
    																																
    
    																																		 END "details"
    
    FROM employees;
    
    WHERE department_id IN(10,20,30);
    
    
    
    
    #5-加密与解密函数
    #PASSWORD()在mysql8.0被弃用
    #加密
    SELECT MD5('mysql'),SHA('mysql')    #81c3b080dad537de7e10e0987a4bf52e
    FROM 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 employees
    INSERT 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  lisi
    FROM employees;
    
    #8-1-3 COUNT()
    #1.计算指定字段在查询结构中出现的个数（不包含有Null值的）
    SELECT COUNT(id),COUNT(salary),COUNT(salary*3),COUNT(1),COUNT(*)#4 4 4 4 4
    FROM employees;
    
    #如果计算表中有多少条记录，如何实现？
    #方式一：COUNT(*)
    #方式二：COUNT(1)‘
    #方式三：COUNT(具体字段)：不一定对！
    #2-注意：计算指定字段出现的个数时，是不计算有null值的。
    #3-
    SELECT AVG(salary),SUM(salary)/COUNT(salary) aver #2550.0000  2550.0000
    FROM employees;
    #。。。。。。。。。。。。。。。。。
    SELECT AVG(IFNULL(salary,1))aver1,SUM(salary)/COUNT(IFNULL(salary,1)) aver2 #2550.0000  2550.0000
    FROM employees;
    
    #
    SELECT*
    FROM employees;
    #方差  标准差  中位数等
    
    #8-2  GROUP BY 的使用
    #需求：查询各个部门的平均工资、最高工资
    SELECT id,AVG(salary),SUM(salary)
    FROM employees
    GROUP BY id
    #结论1：select中出现的非组函数的字段必须声明在group BY中
    #反之，group BY中声明的字段可以不出现在select中。
    #结论2：GROUP BY 声明在from后面、where后面，Order BY前面、limit前面
    #结论3：MySql中GROUP BY使用with ROLLUP
    SELECT id,AVG(salary),SUM(salary)
    FROM employees
    GROUP BY id WITH ROLLUP  #计算整体的平均 : AVG(salary): 2550.0000 SUM(salary):10200
    
    #需求：查询各个部门的平均工资,按照低到高排列
    SELECT id,AVG(salary) aver_sal
    FROM employees
    GROUP BY id
    ORDER BY aver_sal ASC;
    
    
    SELECT id,AVG(salary) aver_sal
    FROM employees
    GROUP BY id WITH ROLLUP
    ORDER 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 ：分页

> **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