Mysql連接查詢

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;

<喎?http://www.Bkjia.com/kf/ware/vc/" target="_blank" class="keylink">vcD4NCjxwcmUgY2xhc3M9"brush:sql;"> 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