MySQL学习（四）分组函数&&连接查询

分组查询

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

#引入:查询每个部门的平均工资
SELECT AVG(salary) FROM employees;
#简单的分组查询
#案例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

#① 查询每个部门的员工个数
SELECT COUNT(*) ,department_id
FROM employees
GROUP BY department_id;

#②根据以上的结果进行筛选，查询哪个部门的员工个数>2
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);
#②根据①结果筛选，最高工资>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) len_name
FROM employees
GROUP BY LENGTH(last_name);
#②添加筛选条件
SELECT COUNT(*),LENGTH(last_name) len_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;

连接查询

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

笛卡尔积乘积现象：表1 有m行， 表2有n行，结果=m*n行

发生原因：没有有效的连接条件
如何避免：添加有效的连接条件

分类：
    按年代分类：
    sql92标准:仅仅支持内连接
    sql99标准【推荐】:mysql中支持内连接+外连接（左外和右外）+ 交叉连接
    
    按功能分类：
        内连接：
            等值连接
            非等值连接
            自连接
        外连接：
            左外连接
            右外连接
            全外连接
        交叉连接

SELECT *FROM beauty;
SELECT *FROM boys;

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

#一、sql92标准
#1、等值连接
/*
①：多表等值连接的结果为多表的交集部分
②：n表连接，至少需要n-1个连接条件
③：多表的顺序没有要求
④：一般需要为表起别名
⑤：可以搭配前面介绍的所有子句使用，比如排序、分组、筛选
*/

#案例1：查询女神所对应的男神名
SELECT NAME ,boyName 
FROM beauty , boys 
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,e.`job_id`,job_title
FROM jobs j,employees e
WHERE e.`job_id`=j.`job_id`;

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

#案例3:查询城市名中第二个字符为0的部门名和城市名
SELECT department_name ,city
FROM departments d,locations l 
WHERE d.`location_id`=l.`location_id`
AND city LIKE '_o%'; 

#5、可以加分组？
#案例1：查询每个城市的部门个数
SELECT COUNT(*) 个数,city
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city ;

#案例2：查询有奖金的每个部门名和部门的领导编号和该部门的最低工资
SELECT department_name,MIN(salary)
FROM departments d,employees e
WHERE d.`department_id`= e.`department_id`
AND e.`commission_pct` IS NOT NULL
GROUP BY department_name;

#6、可以加排序
#案例：查询每个工资的工种名和员工的个数，并且按员工个数降序
SELECT job_title,COUNT(*)
FROM employees e ,jobs j
WHERE e.`job_id`=j.`job_id`
GROUP BY j.`job_title`
ORDER BY COUNT(*) DESC;

#7、可以实现三表连接？
#案例：查询员工名、部门名和所在的城市
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`
AND  city LIKE 's%'
ORDER BY department_name DESC;

#2、非等值连接

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

#3、自连接

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

#二、外连接
/*
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
	如果从表中有和他匹配的，则显示匹配的值
	如果从表中没有和他匹配的，则显示null
	外连接查询结果=内连接结果+主表中有而从表中没有的记录
2、左外连接，left join左边的是主表
   右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的
*/
#引入:查询男朋友不在男神表的女神名

#左外连接
SELECT b.name FROM beauty b
LEFT OUTER JOIN boys bo 
ON b.`boyfriend_id`=bo.`id`
WHERE bo.`id` IS NULL;

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

#交叉连接 (用99语法的标准实现笛卡尔乘积)

SELECT b.*,bo.*
FROM beauty b
CROSS JOIN boys bo;

连接查询图解