MySql复习--（3）分组查询

大致跟着课程复习了分组查询的相关内容，随手记了一点笔记，等有空再回来整理吧
#分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：
sum 、 avg 、max 、 min 、count

参数支持类型：
1.均忽略null
2.可与distinct搭配使用
3.count详细介绍
count()统计行数= count(常数) 一般用前者
效率：
myisam存储引擎，count()的效率高
innodb存储引擎，count（*）与count（1）效率差不多，比字段效率高

和分组函数一起查询的字段有限制
一般要求是group by后的字段
datediff
分组查询
group by 把表中数据分成若干组
select 分组函数,列（要求出现在group by 后面）
from 表
wherer
group by 分组的列表 where接的条件一定是原有的字段
添加分组后的筛选用having 即刻
特点：
1、分组查询的筛选条件分为两类
数据源 位置
分组前筛选 原始表 groupby子句芊
分组后筛选 分组后的结果集 groupby子句后
1.分组函数做条件一定放在having子句中
、 2.优先考虑使用分组芊筛选
2、group by 可单个可多个字段分组，多个字段之间通过逗号隔开
可添加排序，放在分组查询的最后

按多个字段分组、添加排序
*/
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM employees;

SELECT SUM(salary) FROM employees;

SELECT AVG(salary) FROM employees;

SELECT COUNT(salary) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT SUM(salary) 总和,ROUND(AVG(salary),2) 平均,MAX(salary) 最高
FROM employees;

SELECT SUM(DISTINCT salary),SUM(salary) FROM employees;

SELECT COUNT(*) FROM employees;

SELECT COUNT(1) FROM employees;

SELECT AVG(salary) FROM employees WHERE
;

SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

SELECT AVG(salary),department_id
FROM employees WHERE email LIKE ("%a%")
GROUP BY department_id;

SELECT MAX(salary),last_name,manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

SELECT COUNT() ,department_id FROM employees
GROUP BY department_id
HAVING COUNT()>2;

SELECT job_id,MAX(salary)
FROM employees
WHERE
commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

SELECT manager_id,MIN(salary)
FROM employees
WHERE manager_id>102
GROUP BY manager_id
HAVING MIN(salary)>5000;

SELECT COUNT(),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT()>5;

SELECT AVG(salary),last_name,department_id,job_id
FROM employees
GROUP BY department_id,job_id;

SELECT AVG(salary),department_id,job_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

SELECT MIN(salary),manager_id,last_name
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000
;

SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;

连接查询

/*
含义：又称夺标查询，用到多个表

*/