MySql 连接查询

一、连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔乘积现象：表1 有 m 行，表2 有 n 行，结果 = m * n 行
发生原因：没有有效的连接条件
解决办法：添加有效的连接条件
按功能分类：

1. 内连接（包括：等值连接、非等值连接、自连接）
2. 外连接（包括：左外连接、右外连接、全外连接（mysql不支持））
3. 交叉连接

1. 等值连接

语法：

select 查询列表
from 表1 别名,表2 别名
where 表1.key = 表2.key
【and 筛选条件】
【group by 分组字段】
【having 分组后的筛选】
【order by 排序字段】

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

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" = department."department_id";

3. 查询员工名、工种号，工种名**（为表起别名）**
   提高语句的简洁渡，区分多个重名的字段
   注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

select last_name,e.job_id,job_title from employees as e,jobs as j where e."job_id" = j."job_id";

4. 两个表的顺序是可以调换的

//查询员工名、工种号，工种名
select last_name,e.job_id,job_title from jobs as j,employees as e where e."job_id" = j."job_id";

5. 查询有奖金的员工名、部门名（带筛选条件查询）

select last_name,department_name from employees as e,departments as d where e."department_id" = d."department_id" and e."commission_pct" is not bull;

6. 查询城市名中第二个字符为O 的部门名和城市名（带筛选条件查询）

select department_name,city from departments d,locations l where d."location_id" = l."location_id" and city like "_O%";

7. 查询每个城市的部门个数（带分组查询）

select count(*) 个数,city from departments d,locations l where d."location_id" = l."location_id" group by city;

8. 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资(带分组查询)

select department_name,d.manager_id,min(salary) from departments d,employees e where d."departments_id" = e."departments_id" and commission_pct is not null group by department_name,d.manager_id;

9. 查询每个工种的工种名和员工的个数，并且按员工个数排序(带排序查询)

select job_title,count(*) from employees e,jobs j where e."job_id" = j."job_id" group by job_title order by count(*) desc;

10. 查询员工名，部门名和所在的城市(多表连接)

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 g where salary between g."lowest_sal" and g."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";

二、sql99语法

语法：

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

内连接：inner
外连接：(左外连接：left【outer】; 右外连接 ：right 【outer】; 全外连接：full 【outer】)
交叉连接：cross

1、sql99内连接

语法：

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

1.1 等值连接

特点：
① 添加排序、分组、筛选
② inner 可以省略
③ 筛选条件放在where后面，连接条件放在 on 后面，提高分离性，便于阅读

1. 查询员工名、部门名

select last_name,department_name from employees e inner join departments d 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."departments_id" = d."department_id" group by department_name;
② 在①的结果上筛选员工个数 > 3的记录 ,并排序
select count(*),department_name from employees e inner join departments d on e."departments_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;

1.2 非等值连接

1. 查询员工的工资级别

select salary,grade_level from employees e join job_grades g on e."salary" between g."lowest_sal" and  g."highest_sal";

2. 查询工资级别的个数>2的个数，并且按工资级别降序

select count(*),grade_level from employees e 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;

1.3 自连接

1. 查询员工的名字、上级的名字

select e.last_name,m.last_name from employees join employees on e."manager_id" = m."employee_id";

2. 查询姓名中包含字符k的员工的名字，上级的名字

select e.last_name,m.last_name from employees join employees on e."manager_id" = m."employee_id" where e.last_name like "%k%";

2、sql99外连接

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

1. 外连接的查询结果为主表中的所有记录，如果从表中有和它匹配的，则显示匹配的值；如果从表中没有和它匹配的，则显示为null.
2. 外连接查询结果 = 内连接的结果 + 主表中有而从表中没有的记录
3. 左外连接，left join左边的是主表 ；右外连接，right join右边的是主表
4. 左外和右外交换两个表的顺序，可以实现同样的效果
5. 全外连接 = 内连接的结果 + 表1中有但表2中么有的 + 表2中有的但表1中没有的

2.1 左（右）外连接

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;

1. 查询哪个部门没有员工(右外连接)

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;

2.2 全外连接（mysql不支持）

select b.*,bo.* from beauty b full outer join boys bo on b."boyfriend_id" = bo."id";

2.3 交叉连接(笛卡尔乘积)

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