Mysql连接查询

每个实体一个表。
多张表应该在一起使用，将多个表的记录连接起来。

create table teacher(
id int primary key auto_increment,
name varchar(10),
gender enum('male','female','secret')
)engine innodb character set utf8;

insert into teacher values(null,'奥巴马','male');
insert into teacher values(null,'杨幂','female');
insert into teacher values(null,'rose','secret');
insert into teacher values(null,'阿黛尔','female');

create table grade(
id int primary key auto_increment,
name varchar(10),
room varchar(3)
)engine innodb character set utf8;

insert into grade values(null,'bigdata','203');
insert into grade values(null,'english','207');
insert into grade values(null,'maths','308');
insert into grade values(null,'PE','228');

create table teacher_grade(
id int primary key auto_increment,
t_id int,
c_id int,
day tinyint,
begin_date date,
end_date date
)engine innodb character set utf8;

insert into teacher_grade values(null,1,1,15,'2015-01-15','2015-01-30');
insert into teacher_grade values(null,1,2,18,'2015-02-10','2015-02-28');
insert into teacher_grade values(null,1,3,22,'2015-03-01','2015-03-23');
insert into teacher_grade values(null,2,1,20,'2015-03-05','2015-03-25');
insert into teacher_grade values(null,2,2,22,'2015-04-08','2015-04-30');
insert into teacher_grade values(null,3,1,15,'2015-05-01','2015-05-16');
insert into teacher_grade values(null,1,1,15,'2015-05-05','2015-05-20');
insert into teacher_grade values(null,3,3,15,'2015-07-01','2015-07-16');
insert into teacher_grade values(null,2,1,5,'2015-06-17','2015-06-23');

总体思路：
将所有的数据，按照某种条件，连接起来，在进行筛选处理。

连接的分类：
根据连接条件的不同，分为如下：
内连接
外连接
自然连接

内连接：

连接的多个数据必须同时存在才能进行连接。
tbl_left inner join tbl_right on 连接条件

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade on 
teacher.id = teacher_grade.t_id;

内连接的处理：

无条件的内连接

内连接在连接时，可以省略条件。这就意味着左表的数据都要与右表的记录做一个连接，共存在M*N个连接，这种连接称之为交叉连接，或者笛卡尔积。

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade;

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher cross join teacher_grade;

注意：
mysql中cross join与inner join相同，但在数据库的定义上，交叉连接就是笛卡尔积，是没有条件的inner join。
Mysql的inner join 是默认的连接方案，可以省略inner。

有条件的内连接：

会在连接时过滤掉非法的连接。
where写法：
在理解上，数据完全交叉连接，连接完成后，再做数据过滤。

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade where 
teacher.id = teacher_grade.t_id;

on写法：
在连接时，就对数据进行判断。

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade on 
teacher.id = teacher_grade.t_id;

using的写法：
要求负责连接的两个实体之间的名字一致。

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade using(id);

同一业务，以下三种不同的写法：

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade on 
teacher.id = teacher_grade.t_id and day>=20;

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade where 
teacher.id = teacher_grade.t_id and day>=20;

建议使用的语法：

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher inner join teacher_grade on 
teacher.id = teacher_grade.t_id where day>=20;

建议：
在有相同的字段时，使用using，
在通用条件时，使用on，
在数据过滤时(不是指的连接过滤)使用where。

注意：
内连接的查询条件与外连接通用，但是外连接不能使用where作为连接条件。
无论是连接条件，还是连接查询多字段列表，都没有必要一定写
表名.字段 的语法。是否写取决于是否发生冲突，如果冲突，则需要写；如果不冲突，无所谓。如果可以的话，尽量写上，保证代码的可读性。

表别名：

表应该取别名，保证简介和清晰。

select t.name,tg.day,tg.begin_date 
from teacher as t inner join teacher_grade as tg on 
t.id = tg.t_id;

列别名：

select t.id as teacher_id ,tg.id as teacher_grade_id,t.name,tg.day,tg.begin_date 
from teacher as t inner join teacher_grade as tg on t.id = tg.t_id;

外连接：

如果负责连接的一个或多个数据不真实存在，则称之为外连接。

外连接：分为左外连接，右外连接，全外连接（MySQL暂不支持）。

左连接

left [outer] join
在连接时，如果出现左边表数据连接不到右边表的情况，
则左表的数据在最终的结果内保留。
而如果出现右边的表的数据连接不到左表的情况，右表的数据被丢弃。
反之亦然。

select teacher.name,teacher_grade.day,teacher_grade.begin_date 
from teacher left outer join teacher_grade on 
teacher.id = teacher_grade.t_id;

select t.id as teacher_id ,tg.id as teacher_grade_id,t.name,tg.day,tg.begin_date 
from teacher as t left outer join teacher_grade as tg on t.id = tg.t_id;

右外连接

select t.id as teacher_id ,tg.id as teacher_grade_id,t.name,tg.day,tg.begin_date 
from teacher as t right outer join teacher_grade as tg on t.id = tg.t_id;

全外连接

mysql暂不支持全外连接，如果非要写，可以通过union模拟。

(select t.id as teacher_id ,tg.id as teacher_grade_id,t.name,tg.day,tg.begin_date
from teacher as t left outer join teacher_grade as tg on t.id = tg.t_id)
union
(select t.id as teacher_id ,tg.id as teacher_grade_id,t.name,tg.day,tg.begin_date
from teacher as t right outer join teacher_grade as tg on t.id = tg.t_id);

using：

使用using会去掉结果中的重复字段，并放在列前。

注意：
不能使用没有条件的外连接。

自然连接

自然连接：通过mysql自己的判断完成的连接过程。不需要指定连接条件，mysql会使用多表内的，相同的字段，作为连接条件。

自然连接也有内连接，外连接之分。
内连接（natural join）
外连接：左外连接（natural left join），右外连接（natural right join）

select * from one natural join two;
select * from one inner join two using(public_field);

select * from one natural left join two;
select * from one left join two using(public_field);

select * from one natural right join two;
select * from one right join two using(public_field);

多表连接

连接时支持多表连接。
以下三张表：

info_class:id,class_name
info_student:id,student_name,class_id
info_student_info:id,student_info

select s.*,si.* from info_class as c 
left join info_student as s on c.id=s.class_id 
left join info_student_info as si on s.id = si.id 
where c.class_name='001';

match表和class表
一个表可以连接多次，通过别名区分。

match left join class on match.host_id=class.id left join class on match.guest_id=class.id