MySQL基础（三）

1、分组查询

特点：1、分组查询中的筛选条件分为两类
		     			       数据源                       位置				关键字
  			分组前筛选		原始表（库中真实存在的表）	group by 字句的前面		WHERE
			分组后筛选		分组后的结果集			group by 字句的后面		HAVING
 

分组函数作为条件，必须放在 HAVING 字句中；
能用分组前筛选的，最好使用分组前筛选；

2、group by 字句支持单个字段分组，多个字段分组，且多个字段之间用逗号隔开，没有顺序要求；
3、可以添加排序（排序放在整个分组查询之后）

1、GROUP BY
语法：

SELECT	
		column,   //列
		group_function(column)//分组函数
FROM
		table
[WHERE	condition]
[GROUP	BY		group_by_expression(分组的列表)]
[ORDER	BY		column];

注：查询列表必须特殊，要求是分组函数和group by后出现的字段
案例1：查询每个工种的最高工资

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

案例2、查询每个位置上的部门个数

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

案例3、查询邮箱中包含字符a字符的，每个部门的平均工资

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

案例4、查询有奖金的每个领导手下员工的最高工资

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

案例5、查询那个部门的员工个数>2

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

案例6、查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资
1、查询每个工种有奖金的员工的最高工资
2、根据1 查询出 最高工资>12000

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

案例7：查询领导编号>102的每个领导手下的最低工资>5000的领导编号是哪一个，以及其最低工资
1、查询领导编号>102的每个领导手下的最低工资，（领导编号+最低工资）
2、最低工资>5000

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

案例1、按照员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的有哪些；
1、查询每个长度的员工个数；
2、添加筛选；

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

按照多个字段进行分组：
案例2、查询每个部门每个工种的员工的平均工资

SELECT
		AVG(salary),department_id,job_id
FROM
		employees
GROUP	BY
		department_id,job_id;   //前后效果相同

案例2、查询每个部门每个工种的员工的平均工资，并且按照平均工资由高到低排序显示；

SELECT
		AVG(salary),department_id,job_id
FROM
		employees
GROUP	BY
		department_id,job_id;   //前后效果相同
ORDER	BY
		AVG(salary) DESC;

2、连接查询

含义：又称之为多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积，表一有m行，表2有n行，结果出现result = m*n行；
发生原因：，没有添加有效的连接条件；
如何避免：，添加有效的连接条件；

SELECT
		 NAME,boyName
FROM
		boys,beauty
WHERE
		beauty.boyfriend_id = boys.id;

> sql99标准：支持内连接 + 外连接（左外 + 右外）+ 交叉连接 
> 按照功能分类： 		
> 内连接：
> 					等值连接
> 					非等值连接
> 					自连接 		
> 外连接：
> 					左外连接
> 					右外连接
> 					全外连接 		
> 交叉连接；

1、等值连接

特点：1、多表多表等值连接的结果为多表的交集部分
2、 n表连接，至少需要 n-1 个连接
3、 多表的顺序没有要求
4、 一般要给表取别名
5、 可以搭配所有的字句使用，例 排序，筛选等

案例1、查询员工名和对应的部门名

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

案例2、查询员工名，工种号，工种名 、、可以为表取别名；

SELECT 
		last_name,employeees.job_id,job_title
FROM
		employees,jobs
WHERE
		employees.job_id = jobs.job_id;

案例3、两个表的顺序可以替换，查询员工名，工种号，工种名

SELECT 
		last_name,employeees.job_id,job_title
FROM
		jobs,employees
WHERE
		employees.job_id = jobs.job_id;

案例四、添加筛选，查询有奖金的员工名，部门名

SELECT
		last_name,department_name,commission_pct
FROM
		employees e, department d
WHERE 	
		e.department_id = d.department_id
AND		e.commisssion_pct IS NOT NULL;	

案例5、查询出城市名中第二个字符为o的部门名和城市名

SELECT
		department_name,city
FROM
		departments d,location l
WHERE
		d.location_id = l.location_id
AND 
		city	LIKE	'_o%';

案例6、查询每个城市的部门个数

SELECT
		COUNT(*)   个数,city
FROM
		departments d,location l
WHERE 
		d.location_id = l.location_id
GROUP	BY	city;

案例7、查询出有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

SELECT	
		department_name,d.manager_id,MIN(salary)
FROM
		departments d,employees e
WHERE
		d.department_id = e.department_id
AND		commission_pct IS NOT NULL
GROUP BY	department_name,d.manager_id;	

案例8、实现三表连接，查询员工名，部门名，和所在的城市

SELECT
		last_name,department_name,city
FROM
		employees e,department d,location l
WHERE
		e.department_id = d.department_id
AND		d.location_id = l.location_id
AND		city	LIKE	's%'
ORDER BY
		department_name	DESC;