MySQL-数据库的高级查询-聚合函数、分组查询、多表连接、UNION关键字和子查询-学习笔记06

0. 语法总结

SELECT (DISTINCT)
	字段1, 字段2(+1 AS "新字段名"), (GROUP_CONCAT(字段3)) ...  #如果有GROUP BY字段只能用出现在GROUP BY中的字段
														    #用GROUP_CONCAT函数，将字段串联起来
FROM 表名 
([LEFT/RIGHT] JOIN 表2 ON 条件) #没有LEFT或RIGHT为内连接，有为外连接
([LEFT/RIGHT] JOIN (SELECT * FROM * ...) ON 条件) #可以用子查询
(WHERE (condi1 [AND/OR] condi2) [AND/OR] condi3 ...) #条件查询，不能用聚合函数
(GROUP BY 字段1, 字段2 (HAVING expr)) #HAVING可以用聚合函数
(ORDER BY 
	字段1 [ASC/DESC], 字段2 [ASC/DESC], ...) #显示的排序方法，字段后面什么都不加默认ASC升序
(LIMIT 起始位置, 偏移量); #每次查询的起始位置和数量

1. 数据统计分析

1.1 聚合函数（AVG，SUM等）

• 聚合函数在数据的查询分析中，应用十分广泛。聚合函数可以对数据求和、求最大值和最小值、求平均值等等
• 基本用法：SELECT 聚合函数(字段) FROM 表;

序号	语句	功能	例子
1	AVG	求整个字段的均值，没有GROUP BY时返回一个平均数	SELECT AVG(sal+IFNULL(comm, 0)) AS avg FROM t_emp;
2	SUM	只能用于数字类型的求和 字符类型的统计结果为0 日期类型统计结果是毫秒数相加
3	MAX	求非空值的最大值
4	MIN	求非空值的最小值
5	LENGTH	求字符串长度	求最长的学校名字 SELECT MAX(LENGTH(school_name)) FROM t_school;
6	COUNT	返回整张表有的记录条数。 比如一个公司有15个员工，那条数就是15	SELECT COUNT(*) FROM 表;
		返回这个字段里有的数据条数，如果数据有null会被省略。 比如一个公司有15个员工，但只有10个员工有奖金，另外5个员工的奖金是nULL，那返回的条数位10。	SELECT COUNT(字段) FROM 表';
7	ROUND	四舍五入成整数
8	FLOOR	向下取整
9	CEIL	向上取整

1.2 一个容易犯错的地方：聚合函数不能出现在WHERE中

举个例子：当我们要查询1985年以后入职的员工，且底薪超过公司平均底薪的员工数量？
我们可能写出如下函数

SELECT COUNT(*) 
FROM t_emp 
WHERE salary>AVG(salary) AND hiredate>="1985-01-01";

会爆错如下错误

SELECT COUNT(*) FROM t_emp WHERE salary>AVG(salary) AND hiredate>="1985-01-01"
> 1111 - Invalid use of group function
> Time: 0.006 seconds

1.2 分组查询以及对SELECT子句的要求

1.2.1 基础用法（GROUP BY）

• 默认情况下，汇总函数是对全表范围内的数据做统计
• GROUP BY子句的作用是通过一定的规则将一个数据集划分为若干个小的区域，然后针对每个小区域分别进行数据汇总处理
• 用法：SELECT 字段 FROM 表 GROUP BY 字段
• 举例

  SELECT deptno, ROUND(AVG(sal)) FROM t_emp GROUP BY deptno ORDER BY deptno;
  

从下表我们可以看到有三个deptno被分组计算了sal均值，但其实我们一共有四个deptno，因为有一个deptno里没有员工，所以没有进行计算

1.2.2 按照数字进行分组

• 用法：SELECT [字段1/聚合函数(字段_)], [字段2/聚合函数(字段_)], ... GROUP BY 1, 2, ...;
• MySQL会给SELECT后的字段一个从1开始的index，选择第几个

SELECT deptno, AVG(sal), sal FROM t_emp
GROUP BY 1, 3

1.2.2 逐级分组

• 数据库支持多列分组条件，执行的时候逐级分组
• 举例：查询每个部门里。每种职位的人员数量和平均底薪
  这需要我们对部门进行分组，在部门里对职务进行分组，最后用SELECT语句中的聚合函数进行统计一下

  SELECT deptno, job, COUNT(*), AVG(sal) #COUNT在这里指每种职位的人数
  FROM t_emp 
  GROUP BY deptno, job 
  ORDER BY deptno; 
  

  

1.2.3 对SELECT子句的要求

• 如果含有GROUP BY子句，那么SELECT子句中只能出现聚合函数或者GROUP BY内包含的字段，其他字段都是非法的
• 例1：正确的用法

  SELECT deptno, job, COUNT(*), AVG(sal)
  FROM t_emp 
  GROUP BY deptno, job 
  ORDER BY deptno;