MySQL——（6）连接查询

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

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

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

分类：1、按照年代分类：SQL92标准（仅支持内连接）、SQL99标准【推荐使用（支持内连接+外连接（左外+右外）+交叉连接）

           2、按照功能分类：内连接、外连接、交叉连接

                 内连接：等值连接、非等值连接、自连接

                 外连接：左外连接、右外连接、全外连接

一、SQL92标准

1、等值连接

多表等值连接的结果为多表的交集部分

n表连接，至少需要n-1个连接条件

多表的顺序没有要求

一般需要为表起别名

可以搭配前面介绍的所有子句使用，例如：排序、分组、筛选

#案例1：查询女神名和对应的男神名
use girls;
select name, boyName from boys, beauty where beauty.boyfriend_id = boys.id;
#案例2：查询员工名和对应的部门名
use myemployees;
select last_name, department_name from employees, departments where employees.department_id = departments.department_id;
#（1）为表起别名(起的别名比较短：提高简洁度，区分多个重名的字段)
#注意：起了别名，就全部用别名，因为是先执行from，表的名字已经变成新的了，原来的表名不认识了
#案例1：查询员工名、工种号、工种民
select e.last_name, e.job_id, job_title from employees as e, jobs as j where e.job_id = j.job_id;
#（2）两个表的顺序可以互换的，效果不变
select e.last_name, e.job_id, job_title from jobs as j, employees as e where e.job_id = j.job_id;
#（3）可以加筛选条件吗？
#案例2：查询有奖金的员工名、部门名
select last_name, department_name from employees e, departments d where e.department_id = d.department_id and e.commission_pct is not null;
#案例3：查询城市名中第二个字符为o的对应的部门名和城市名
select department_name, city from departments d, locations l where d.location_id = l.location_id and city like '_o%';
#（4）可以加分组吗？
#案例4：查询每个城市的部门个数
select count(*) 个数, city from departments d, locations l where d.location_id = l.location_id group by city;
#案例5：查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
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;
#（5）可以加排序吗？
#案例6：查询每个工种名和员工个数，并且按员工个数降序
select job_title, count(*) from employees e, jobs j where e.job_id = j.job_id group by job_title order by count(*) desc;
#（6）可以三表连接吗？
#案例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;
#案例8：查询员工名、部门名和所在城市，且所在城市以s开头
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%';

2、非等值连接

#案例1：查询员工的工资和工资级别
select salary, grade_level from employees e, job_grades g where salary between g.lowest_sal and g.highest_sal;
#案例2：查询员工的工资和工资级别=A的
select salary, grade_level from employees e, job_grades g where salary between g.lowest_sal and g.highest_sal and g.grade_level = 'A';

3、自连接

#案例1：查询员工名和他领导的名称
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;

二、SQL99标准

语句：select 查询列表 from 表1 别名 【连接类型】join 表2 别名 on 连接条件 【where 筛选条件】【group by 分组】【having 筛选条件】 【order by 排序列表】

分类：内连接：inner

          外连接：左外：left 【outer】

                        右外：right 【outer】

                        全外：full 【outer】

          交叉连接：cross

1、内连接

语法：select 查询列表 from 表1 别名 inner join 表2 别名 on 连接条件

分类：等值连接、非等值连接、自连接

特点：可以添加排序、分组、筛选；

           inner可以省略；

           筛选条件放在where后面，提高分离性，便于阅读；

           inner join连接和SQL92语法中的等值连接效果是一样的

（1）等值连接

#案例1：查询员工名、部门名（调换位置不影响）
select last_name, department_name from employees e inner join departments d on e.department_id = d.department_id;
select last_name, department_name from departments d inner join employees e on e.department_id = d.department_id;
#案例2：查询名字中包含e的员工名和工种名（添加筛选条件）
select last_name, job_title from employees e inner join jobs j on e.job_id = j.job_id where e.last_name like '%e%';
#案例3：查询部门个数大于3的城市名和部门个数（添加分组和筛选条件）
select city, count(*) 部门个数 from departments d inner join locations l on d.location_id = l.location_id group by city having count(*)>3;
#案例4：查询哪个部门的员工个数大于3的部门名和员工个数，并按照员工个数降序排列（添加排序）
select count(*), department_name from employees e inner join departments d on e.department_id = d.department_id group by department_name having count(*)>3 order by count(*) desc;
#案例5：查询员工名、部门名、工种名，并按部门名降序（三表连接）
select last_name, department_name, job_title from employees e inner join departments d on e.department_id = d.department_id inner join jobs j on e.job_id = j.job_id order by department_name desc;

（2）非等值连接

#案例1：查询员工的工资级别
select salary, grade_level from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal;
#案例2：查询工资级别个数大于20的个数，并且按工资级别降序
select count(*), grade_level from employees e inner join job_grades g on e.salary between g.lowest_sal and g.highest_sal group by grade_level having count(*)>20 order by grade_level desc;

（3）自连接

#案例1：查询员工名和他上级的名字
select e.last_name, m.last_name from employees e join employees m on e.manager_id = m.employee_id;
#案例2：查询员工名包含k的员工名和他上级的名字
select e.last_name, m.last_name from employees e join employees m on e.manager_id = m.employee_id where e.last_name like '%k%';

2、外连接

应用场景：用于查询一个表里有，另一个表里没有的记录

特点：1、外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的则显示匹配的值，如                  果没有和它匹配的，则显示null，外连接查询结果=内连接结果+主表中有而从表中没有                    的记录

           2、左外连接：left join左边是主表

                右外连接：right join右边是主表

           3、左外和右外交换两个表的顺序，可以实现同样的效果

           4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1中没有的

#案例1：查询男朋友不在男神表的女生名
use girls;
#左外连接：
select b.name from beauty b left outer join boys bo on b.boyfriend_id = bo.id where bo.id is null;
#右外连接：
select b.name from boys bo right outer join beauty b on b.boyfriend_id = bo.id where bo.id is null;
#案例2：查询哪个部门没有员工
use myemployees;
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;
select d.*, e.employee_id from employees e right outer join departments d on d.department_id = e.department_id where e.employee_id is null;

3、交叉连接

use girls;
select  b.*, bo.* from beauty b cross join boys bo;

SQL92与SQL99 PK:

功能上：SQL99支持的较多

可读性：SQL99实现连接条件和筛选条件的分离，可读性较高