免费手机网站制作方法,网络推广和网络运营的区别,湛蓝 网站开发,产品展示类网站模板聚合函数 1.什么是聚合函数1.1AVG和SUM函数1.2MIN和MAX函数1.3COUNT函数 2.GROUP BY2.1基本使用2.2使用多个列分组2.3GROUP BY中使用WITH ROLLUP 3.HAVING3.1基本使用3.2WHERE和HAVING的区别 4.SELECT的执行过程4.1查询的结构4.2SELECT执行顺序4.3SQL执行原理 1.什么是聚合函数… 聚合函数 1.什么是聚合函数1.1AVG和SUM函数1.2MIN和MAX函数1.3COUNT函数 2.GROUP BY2.1基本使用2.2使用多个列分组2.3GROUP BY中使用WITH ROLLUP 3.HAVING3.1基本使用3.2WHERE和HAVING的区别 4.SELECT的执行过程4.1查询的结构4.2SELECT执行顺序4.3SQL执行原理 1.什么是聚合函数
聚合函数作用于一组数据并对一组数据返回一个值。 聚合函数类型 AVG()SUM()MAX()MIN()COUNT() 聚合函数语法 聚合函数不能嵌套调用。比如不能出现类似AVG(SUM(字段名称))形式的调用。
1.1AVG和SUM函数
可以对数值型数据使用AVG和SUM函数
SELECT AVG(salary), MAX(salary),MIN(salary), SUM(salary)
FROM employees
WHERE job_id LIKE %REP%;1.2MIN和MAX函数
可以对任意数据类型的数据使用MIN和MAX函数
SELECT MIN(hire_date), MAX(hire_date)
FROM employees;1.3COUNT函数
COUNT(*)返回表中记录总数适用于任意数据类型
SELECT COUNT(*)
FROM employees
WHERE department_id 50;COUNT(expr)返回expr不为空的记录总数
SELECT COUNT(commission_pct)
FROM employees
WHERE department_id 50;问题用count(*)count(1)count(列名)谁好呢 其实对于MyISAM引擎的表是没有区别的。这种引擎内部有一计数器在维护着行数。 Innodb引擎的表用count(*),count(1)直接读行数复杂度是O(n)因为innodb真的要去数一遍。但好于具体的count(列名)。问题能不能使用count(列名)替换count(*) 不要使用 count(列名)来替代 count() count() 是 SQL92 定义的标准统计行数的语法跟数据库无关跟 NULL 和非 NULL 无关。 说明count(*)会统计值为 NULL 的行而 count(列名)不会统计此列为 NULL 值的行。
2.GROUP BY
2.1基本使用 可以使用GROUP BY子句将表中的数据分成若干组
SELECT column, group_function(column)
FROM table
[WHERE condition]
[GROUP BY group_by_expression]
[ORDER BY column];WHERE一定放在FROM的后面 在SELECT列表中所有未包含在组函数中的列都应该包含在GROUP BY子句中
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id ;包含在GROUP BY子句中的列不必包含在SELECT列表中
SELECT AVG(salary)
FROM employees
GROUP BY department_id ;2.2使用多个列分组 SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id ;2.3GROUP BY中使用WITH ROLLUP
使用WITH GROUP关键字之后在所有查询出的分组记录之后增加一条记录该记录计算查询出的所有记录的综合即统计记录数量。
SELECT department_id,AVG(salary)
FROM employees
WHERE department_id 80
GROUP BY department_id WITH ROLLUP;注意当使用ROLLUP时不能同时使用ORDER BY子句进行结果排序即ROLLUP和ORDER BY是相互排斥的。
3.HAVING
3.1基本使用 过滤分组HAVING子句
行已经被分组使用了聚合函数满足HAVING子句中条件的分组将被显示HAVING不能单独使用必须要跟GROUP BY一起使用。
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)10000 ;非法使用聚合函数不能在WHERE子句中使用聚合函数
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) 8000
GROUP BY department_id;3.2WHERE和HAVING的区别
区别1WHERE可以直接使用表中的字段作为筛选但不能使用分组中的计算函数作为筛选条件HAVING必须要与GROUP BY配合使用可以把分组计算的函数和分组字段作为筛选条件。 这决定了在需要对数据进行分组统计的时候HAVING 可以完成 WHERE 不能完成的任务。这是因为在查询语法结构中WHERE 在 GROUP BY 之前所以无法对分组结果进行筛选。HAVING 在 GROUP BY 之后可以使用分组字段和分组中的计算函数对分组的结果集进行筛选这个功能是 WHERE 无法完成的。另外WHERE排除的记录不再包括在分组中。 区别2如果需要通过连接从关联表中获取需要的数据WHERE是先筛选后连接的而HAVING是线连接后筛选的。这一点就决定了在关联查询中WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选用一个筛选后的较小数据集和关联表进行连接这样占用的资源比较少执行效率也比较高。HAVING 则需要先把结果集准备好也就是用未被筛选的数据集进行关联然后对这个大的数据集进行筛选这样占用的资源就比较多执行效率也较低。 小结如下
优点缺点WHERE先筛选数据在关联执行效率高不能使用分组中的计算函数进行筛选HAVING可以使用分组中的计算函数在最后的结果中进行筛选执行效率低
4.SELECT的执行过程
4.1查询的结构
#方式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 ...,...
#其中
#1from从哪些表中筛选
#2on关联多表查询时去除笛卡尔积
#3where从表中筛选的条件
#4group by分组依据
#5having在统计结果中再次筛选
#6order by排序
#7limit分页4.2SELECT执行顺序
你需要记住 SELECT 查询时的两个顺序 1.关键字的舒徐是不能颠倒的
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... LIMIT...2.SELECT语句执行顺序MySQL和Oracle中SELECT执行顺序基本相同
FROM - WHERE - GROUP BY - HAVING - SELECT 的字段 - DISTINCT - ORDER BY - LIMIT比如你写了一个 SQL 语句那么它的关键字顺序和执行顺序是下面这样的
SELECT DISTINCT player_id, player_name, count(*) as num # 顺序 5
FROM player JOIN team ON player.team_id team.team_id # 顺序 1
WHERE height 1.80 # 顺序 2
GROUP BY player.team_id # 顺序 3
HAVING num 2 # 顺序 4
ORDER BY num DESC # 顺序 6
LIMIT 2 # 顺序 7在 SELECT 语句执行这些步骤的时候每个步骤都会产生一个 虚拟表 然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是这些步骤隐含在 SQL 的执行过程中对于我们来说是不可见的。
4.3SQL执行原理
SELECT 是先执行 FROM 这一步的。在这个阶段如果是多张表联查还会经历下面的几个步骤
首先先通过 CROSS JOIN 求笛卡尔积相当于得到虚拟表 vtvirtual table1-1通过 ON 进行筛选在虚拟表 vt1-1 的基础上进行筛选得到虚拟表 vt1-2添加外部行。如果我们使用的是左连接、右链接或者全连接就会涉及到外部行也就是在虚拟表 vt1-2 的基础上增加外部行得到虚拟表 vt1-3。 当然如果我们操作的是两张以上的表还会重复上面的步骤直到所有表都被处理完为止。这个过程得到是我们的原始数据。
当我们拿到了查询数据表的原始数据也就是最终的虚拟表 vt1就可以在此基础上再进行 WHERE 阶段。在这个阶段中会根据 vt1 表的结果进行筛选过滤得到虚拟表 vt2。
然后进入第三步和第四步也就是 GROUP 和 HAVING阶段。在这个阶段中实际上是在虚拟表 vt2 的基础上进行分组和分组过滤得到中间的虚拟表 vt3 和 vt4。
当我们完成了条件筛选部分之后就可以筛选表中提取的字段也就是进入到 SELECT 和 DISTINCT阶段。
首先在 SELECT 阶段会提取想要的字段然后在 DISTINCT 阶段过滤掉重复的行分别得到中间的虚拟表 vt5-1 和 vt5-2。
当我们提取了想要的字段数据之后就可以按照指定的字段进行排序也就是 ORDER BY 阶段得到虚拟表 vt6。
最后在 vt6 的基础上取出指定行的记录也就是 LIMIT 阶段得到最终的结果对应的是虚拟表 vt7。
当然我们在写 SELECT 语句的时候不一定存在所有的关键字相应的阶段就会省略。
同时因为 SQL 是一门类似英语的结构化查询语言所以我们在写 SELECT 语句的时候还要注意相应的关键字顺序所谓底层运行的原理就是我们刚才讲到的执行顺序。 练习
where子句可否使用组函数进行过滤?查询公司员工工资的最大值最小值平均值总和查询各job_id的员工工资的最大值最小值平均值总和选择具有各个job_id的员工人数查询员工最高工资和最低工资的差距DIFFERENCE查询各个管理者手下员工的最低工资其中最低工资不能低于6000没有管理者的员工不计算在内查询所有部门的名字location_id员工数量和平均工资并按平均工资降序查询每个工种、每个部门的部门名、工种名和最低工资
1.where子句可否使用组函数进行过滤?
NO
2.查询公司员工工资的最大值最小值平均值总和
SELECT MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees;
3.查询各job_id的员工工资的最大值最小值平均值总和
SELECT job_id, MAX(salary), MIN(salary), AVG(salary), SUM(salary)
FROM employees
GROUP BY job_id;
4.选择具有各个job_id的员工人数
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
5.查询员工最高工资和最低工资的差距DIFFERENCE
SELECT MAX(salary), MIN(salary), MAX(salary) - MIN(salary) DIFFERENCE
FROM employees;
6.查询各个管理者手下员工的最低工资其中最低工资不能低于6000没有管理者的员工不计算在内
SELECT manager_id, MIN(salary)
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary) 6000;
7.查询所有部门的名字location_id员工数量和平均工资并按平均工资降序
SELECT department_name, location_id, COUNT(employee_id), AVG(salary) avg_sal
FROM employees e RIGHT JOIN departments d
ON e.department_id d.department_id
GROUP BY department_name, location_id
ORDER BY avg_sal DESC;
8.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_id,MIN(salary)
FROM departments d LEFT JOIN employees e
ON e.department_id d.department_id
GROUP BY department_name,job_id
