MySQL学习笔记4 （分组函数与查询、连接查询）

一、分组函数

   分组函数，又称为统计函数或聚合函数

   length('john')  //传入一个值，返回一个值，一对一，像这种函数，就不是分组函数，
   叫做单行函数，分组函数表示这个函数给出一组值,最后返回一个值
    max(salary) // 分组函数，给它一组值，比如说给它一列值
    分组函数的分类
		max():最大值
		min():最小值
		sum():和
		avg():平均值
		count():计算非空的个数
	这些是通用的，sqlserver、oracle、mysql 都是一样的
 特点：
	1、sum、avg只支持数值型，count、max、min支持任何类型
	2、这几个分组函数，都忽略null值
	3、count()分组函数，支持以下写法
		count(*)      表示 查询结果集的行数，建议使用
		count(1)      表示 查询结果集的行数

#一、查询分组函数
SELECT MAX(salary) 最大值 FROM employees;
SELECT MIN(salary) 最小值 FROM employees;
SELECT SUM(salary) 和 FROM employees;
SELECT AVG(salary) 平均值 FROM employees;
SELECT COUNT(salary) 个数 FROM employees;
SELECT MAX(salary) 最大值,SUM(salary) 和 FROM employees;

#二、关于sum 和avg
1.只支持数值型
2.sum和avg都忽略null值

SELECT AVG(last_name) FROM employees;
SELECT SUM(last_name) FROM employees;

SELECT * FROM employees;
SELECT SUM(commission_pct)/107,AVG(commission_pct) FROM employees;

#三、关于max和min
1.支持任何类型
2.max和min都忽略null值

SELECT MAX(last_name),MIN(last_name) FROM employees; //判断类型
SELECT MAX(commission_pct),MIN(commission_pct) FROM employees; //判断是否忽略null

#四、关于count
SELECT COUNT(commission_pct) FROM employees; //计算非空的值，忽略null、
//查询员工表占了几个部门
SELECT COUNT(DISTINCT department_id) FROM employees;

#查询结果集中的行数
count(1)的性能没有count（）高
因为count()，sql服务器对其进行了优化，所以效率高

SELECT COUNT() FROM employees; //查看有多少条数据
//计算满足条件的行数
SELECT COUNT() FROM employees WHERE department_id>30;
SELECT COUNT(1) FROM employees; //和count(*)的效果是一样的，但是效率不一样

二、分组查询

分组查询：
	和分组函数一起搭配使用的查询，我们叫做分组查询，换句话说，分组查询一般都会用到分组函数
语法：
	select 分组函数 别名,分组后的字段
	from 表
	【where 分组前筛选】
	group by 分组的字段
	【having 分组后筛选】
	【order by 排序的字段】

从上到下依次顺序，不能颠倒顺序

分组查询的特点：
1、按多个字段分组，字段间和分组函数一起查询的字用 逗号 隔开，没有顺序要求
2、可以段，只能是分组后的字段，不能是任意字段
3、分组筛选

				针对的表	           位置		        使用的关键字
 分组前筛选	     原始表		    group by的前面	      where
 分组后筛选	    分组后的结果集	group by的后面        having

顺序：where——group by——having
4、having和group by支持别名
*/

#一、普通的分组查询
#01 案例：查询各部门的最高工资和部门号
SELECT MAX(salary),department_id FROM employees GROUP BY department_id;

#二、按多个字段分组
#案例：查询每个工种、每个部门的平均工资
SELECT AVG(salary),job_id,department_id
FROM employees
GROUP BY job_id,department_id;

#三、分组查询+筛选having

筛选条件分两类：
分组前筛选：筛选的条件针对的是原始表
分组后筛选：筛选的条件针对的是分组后的结果集
针对的表 位置 使用的关键字
分组前筛选 原始表 group by的前面 where
分组后筛选 分组后的结果集 group by的后面 having
where——group by——having

#案例1：查询有奖金的，每个部门的最高奖金率
SELECT MAX(commission_pct) 最高奖金率,department_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY department_id;

#案例2：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
SELECT department_id,MIN(salary) 最低工资
FROM employees
WHERE last_name LIKE ‘%a%’
GROUP BY department_id
HAVING MIN(salary)>3000;

#案例3：查询电话以“110”开头的，工种号包含字符’T’的,每个工种的平均工资>5000的工种号和平均工资
SELECT job_id,AVG(salary)
FROM employees
WHERE phone_number LIKE ‘110%’ AND job_id LIKE ‘%T%’
GROUP BY job_id
HAVING AVG(salary)>5000;

#案例4：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT() 个数,location_id
FROM departments
GROUP BY location_id
HAVING COUNT()>2;

#四、having子句支持别名
#案例：查询每个地区的部门个数，求个数大于2的部门个数和地区号
SELECT COUNT(*) 个数,location_id
FROM departments
GROUP BY location_id
HAVING 个数>2;

#五、排序 order by 支持别名
#案例：查询员工姓名中包含字符a，每个部门的最低工资高于3000的部门编号
,按照最低工资降序排序
SELECT department_id,MIN(salary) 最低工资
FROM employees
WHERE last_name LIKE ‘%a%’
GROUP BY department_id
HAVING MIN(salary)>3000
ORDER BY 最低工资 DESC;

三、连接查询

连接查询概念：查询的字段来自于多个表
语法：
	select 字段1，字段2
	from 表1，表2
	【where 连接条件】
分类：

1、传统模式的多表连接

等值连接(where)——非等值连接

2、sql99推出的标准，使用join关键字实现连接

内连接——外连接

3、自连接

#一、传统模式的多表连接，也叫等值连接
特点：
	①表的顺序没有要求
	②n表连接，至少需要n-1个连接条件
	③一般需要为表起别名，这样可以提高语句简洁度，并且防止字段有歧义
	④可以添加分组、排序、筛选，一起混合使用

#①案例1：查询员工名、部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#②为表起别名
一般需要为表起别名，好处：
a、提高语句简洁度
b、防止字段有歧义，
c、提高效率
如果已经为表起别名，则使用字段时，只能用别名限定而不能用表名限定

SELECT e.last_name,d.department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id;

#③添加筛选条件
#案例：查询 工资>5000的工种名和员工名、工资
SELECT job_title,last_name,salary
FROM employees e,jobs j
WHERE e.job_id=j.job_id
AND salary>5000;

#④添加分组和筛选
#01案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
GROUP BY e.department_id
HAVING 个数>5;

#⑤排序
#01案例：查询每个部门的员工个数和部门名
SELECT COUNT(*) 个数,department_name
FROM employees e,departments d
WHERE e.department_id=d.department_id
GROUP BY e.department_id
HAVING 个数>5
ORDER BY 个数 DESC;

#⑥ 三表连接
#案例：查询员工名、部门名和所在城市
SELECT last_name,department_name,city
FROM employees e,departments d,locations l
WHERE e.department_id=d.department_id
AND d.location_id=l.location_id;