mySQL学习20221207

1.多表查询

1）如果使用别名，处处使用别名

2）多表查询，至少n-1个连接条件

2.多表查询的分类

角度1：等值连接 vs 非等值连接

角度2：自连接 vs 非自连接

角度3：内连接 vs外连接

等值、非自连接：

eg:select emp.last_name,dept.department_name from employees emp,departments dept

where emp.department_id=dept.department_id;

非等值：

eg:select last_name,salary,grade_level from employees emp,jobs job where emp.salary between job.lowest_sal and highest_sal;

自连接：

ps:把一张表比作不同的表使用，起两个不同的别名

eg:select emp.last_name,mgr.last_name from employees emp,employees mgr where emp.manager_id=mgr.manager_id;

内连接：

关键词：inner join..on

eg:select emp.last_name,dept.department_name from employees emp inner join departments dept on emp.department_id=dept.department_id;

外连接：

1）左外连接

关键词：left join...on

eg:select emp.last_name,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id;

2) 右外连接

关键词：right join...on

eg:select emp.last_name,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id;

3) 满外连接

Orcale支持：full join...on

mySQL：根据SQL99语法，union 或者 union all按照下图逻辑

 3.7种join 的实现

左上图：

eg:select emp.last_name,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id;

右上图：

eg:select emp.last_name,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id;

中图：

eg:select emp.last_name,dept.department_name from employees emp inner join departments dept on emp.department_id=dept.department_id;

左中图：

eg:select emp.last_name,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id where dept.department_id is null;

右中图：

eg:select emp.last_name,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id where emp.department_id is null;

左下图：满外连接实现方式：

1) 左上图+右中图

eg:

select emp.last_name,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id

union all

select emp.last_name,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id where emp.department_id is null;

2）右上图+左中图

eg:

select emp.last_name,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id;

union all

select emp.last_name,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id where dept.department_id is null;

右下图：左中图——右中图

eg:

select emp.employees_id,dept.department_name from employees emp left join departments dept on emp.department_id=dept.department_id where dept.department_id is null;

union all

select emp.employees_id,dept.department_name from employees emp right join departments dept on emp.department_id=dept.department_id where emp.department_id is null;