MySQL03

分组查询

#进阶5：分组查询
/*
语法：
	select 分组函数，列（要求出现在group by后面）
	from 表
	【where 筛选条件】
	group by 分组的列表
	【order by子句】
注意：
	查询列表必须特殊，要求是分组函数和group by后出现的字段
特点：
	1.分组查询中的筛选条件分为两类
				数据源				位置					关键字
	分组前筛选	原始表		     group by子句的前面		   where
	分组后筛选   分组后的结果集	  group by子句的后面		 	having
	
	分组函数做条件肯定是放在having子句中
	
	2.能用分组前筛选的就优先使用分组前筛选
*/

#案例1：查询每个工种的最高工资
SELECT MAX(salary),job_id
FROM employees
GROUP BY job_id;

#案例2：查询每个位置上的部门个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;

分组前查询

#案例1：查询邮箱中包含a字符的，每个部门的平均工资
SELECT AVG(salary),department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#案例2：查询有奖金的每个领导手下员工的最高工资
SELECT MAX(salary),manager_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

分组后查询

#案例1：查询哪个部门的员工个数>2

#1.查询每个部门的员工个数
#2.根据1的结果进行筛选
SELECT COUNT(*),department_id
FROM employees
GROUP BY department_id
HAVING COUNT(*)>2;

#案例2：查询每个工种有奖金的员工的最高工资>12000的工种编号和其最高工资
SELECT MAX(salary),job_id
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING MAX(salary)>12000;

#案例3：查询领导编号>102的每个领导手下的最低工资>5000的领导是哪个，以及其最低工资
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id >102
GROUP BY manager_id
HAVING MIN(salary)>5000;

按表达式或函数分组

#按表达式或函数分组

#案例：按员工姓名的长度分组，查询每一组的员工个数，筛选员工个数>5的
SELECT COUNT(*),LENGTH(last_name)
FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*)>5;

按多个字段分组

#案例：查询每个部门每个工种的员工的平均工资
SELECT AVG(salary),department_id,job_id
FROM employees
GROUP BY department_id,job_id
ORDER BY AVG(salary) DESC;

连接查询

#进阶6：连接查询
/*
含义：又称为多表查询：当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：没有有效的连接条件

分类：
	功能：内连接
			等值连接
			非等值连接
			自连接
	      外连接
			左外连接
			右外连接
			全外连接
	      交叉连接
*/

sql92标准

等值连接

#1.等值连接
/*
1.多表连接的结果为多表的交集部分
2.n表连接，至少需要n-1个连接条件
3.多表的顺序没有要求
4.一般需要为表起别名
*/

#案例1：查询女神名和对应的男神名
SELECT NAME,boyname FROM boys,beauty
WHERE beauty.boyfriend_id=boys.id;

#案例2：查询员工名和对应的部门名
SELECT last_name,department_name
FROM employees,departments
WHERE employees.department_id=departments.department_id;

#2.为表起别名

#查询员工名、工种号、工种名
SELECT last_name,e.job_id,job_title
FROM employees e,jobs j
WHERE e.`job_id`=j.`job_id`;

#3.加筛选条件

#案例：查询有奖金的员工名、部门名
SELECT last_name,department_name,commission_pct
FROM employees e,departments d
WHERE e.`department_id`=d.`department_id`
AND commission_pct IS NOT NULL;

#4.加分组条件

#案例：查询每个城市的部门个数
SELECT COUNT(*),city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

非等值连接

#2.非等值连接

#案例.查询员工的工资和工资级别
SELECT salary,grade_level
FROM employees e,job_grades j
WHERE salary BETWEEN j.`lowest_sal`AND j.`highest_sal`;

自连接

#3.自连接

#案例：查询员工名和上级的名称
SELECT e.last_name,m.last_name
FROM employees e,employees m
WHERE e.manager_id=m.employee_id;

sql99标准

/*
语法：
	select 查询列表
	from 表1 别名 【连接类型】
	join 表2 别名 
	on 连接条件
	【where 筛选条件】
分类：
	内连接：inner
	外连接：
	       左外连接 left
	       右外连接 right
	       全外连接 full
	交叉连接：cross
*/

内连接

#1.等值连接

#案例1：查询名字中包含e的员工名、部门名
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
WHERE e.last_name LIKE '%e%';

#案例2：查询部门个数>3的城市名和部门个数
SELECT COUNT(*) 部门个数,city
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

#2.非等值连接

#查询员工的工资级别
SELECT salary,grade_level
FROM employees e
INNER JOIN job_grades j
ON e.`salary` BETWEEN j.`lowest_sal`AND j.`highest_sal`;

#3.自连接

#查询员工名、上级名
SELECT e.last_name,m.last_name
FROM employees e
INNER JOIN employees m
ON e.`manager_id`=m.`employee_id`;

外连接

/*
用于查询一个表中有，另一个表没有的记录

特点：
1.外连接的查询结果为主表中的所有记录，
	如果从表中有和它匹配的，则显示匹配的值
	如果从表中没有和它匹配的，则显示null
2.左外连接，left join左边的是主表
  右外连接，right join右边的是主表
3.左外和右外交换两个表的顺序，可以实现相同的效果
*/

#案例1：查询哪个部门没有员工
SELECT d.*,e.employee_id
FROM departments d
LEFT JOIN employees e
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`IS NULL;

SELECT d.*,e.employee_id
FROM employees e
RIGHT JOIN departments d
ON d.`department_id`=e.`department_id`
WHERE e.`employee_id`IS NULL;

#全外
SELECT b.*,bo.*
FROM beauty b
FULL JOIN boys bo
ON b.boyfriend_id=bo.id;

交叉连接

#交叉连接——笛卡尔乘积
SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;