mysql多表连接查询

连接查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询
笛卡尔成绩现象：表1 有m行，表2有n行，结果m*n行
发生原因：没有有效的连接条件
如何避免：添加有效的额连接条件
分类：

• 按年代分类：

  sql192标准：仅仅支持内连接
  sql199标准【推荐】：支持内连接+外连接（左外连接和右外连接）+交叉连接

• 按功能分类：

1. 内连接

等值连接
非等值连接
自连接

2. 外连接

左外连接
右外连接
全外连接

3. 交叉连接

一、sql192标准

1.等值连接

案例1.查询女神名和对应的男神名

select name,boyname
from boys,beauty
where beauty.boyfriend_id = boys.id;

案例2.查询员工名和对应的部门名

select last_name,department_name
from employees,departments
where employess.department_id=departments.department_id

1.1 为表起别名

提高语句的简洁度
区分多个充名字段
注意：如果为表起了别名，则查询的字段就不能使用原来的表名去限定

案例3.查询员工名,工种号，工种名

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

1.2 两个表的顺序是否可以调换：可以调换

1.3 可以加筛选

案例1：查询有奖金的员工名、部门名

select last_name,department_name,commission_pct
from employees e,departments d
where e.department_id = d.department.id
and e.`commission_pct` is not null;

案例2：查询城市名中第二个字符为o的部门名和城市名

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

1.4 可以加分组

案例1：查询每个城市的部门个数

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

案例2：查询有奖金的每个部门的部门名称和部门领导编号和该部门的最低工资

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

1.5 可以实现三表连接

案例3：查询员工名、部门名和所在城市

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

1.6等值连接特点总结：

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

2. 非等值连接

案例：查询员工的工资和工资级别

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;

练习：已知表student里面有id(学号)，name,gradeId(年级编号)。已知表grade,里面有id年级编号，name(年级名称)。已知表result，里面有id,score,studentNo（学号）要求查询姓名，年级名，成绩

select s.name,g.name,r.score
from student s,grade g,result r
where s.gradeId=g.id 
and s.id = r.stuentNo