mysql as join_mysql join

mysql 的 join

------

基本格式：

select ... from A

join B1 on ...

join B2 on ...

...

where ...

------

join 类型

join内连接

left join左连接，左表所有数据查出，右表跟左表有关联的数据才查出

right join右连接，右表所有数据查出，左表跟右表有关联的数据才查出

inner join内连接，同 join，

全连接mysql 不支持全连接，可以通过 union 2个 left join 或 right join 实现全连接，

=如果不用 join 语句，直接用 = 号关联，则相当于 内连接，

------

例子：

* 建表：

-- create table boy & gril

create table boy(

id int(10) primary key auto_increment,

name varchar(50) not null,

girl_id int null,

cdate timestamp default current_timestamp

)

engine=innodb default charset=utf8;

create table girl(

id int(10) primary key auto_increment,

name varchar(50) not null,

boy_id int null,

cdate timestamp default current_timestamp

)

engine=innodb default charset=utf8;

* 插入测试数据：

-- clear table

truncate table boy;

truncate table girl;

-- init data

insert into boy(name,girl_id) values

('boy_01',2),

('boy_02',5),

('boy_03',1),

('boy_04',4),

('boy_05',3),

('boy_06',100);

insert into girl(name,boy_id) values

('girl_1',3),

('girl_2',1),

('girl_3',5),

('girl_4',4),

('girl_5',2),

('girl_6',100);

* 各种 join 的 sql 语句：

-- 内连接

select b.id as boy_id, g.id as girl_id from boy b join girl g on b.girl_id = g.id order by b.id asc;

-- 用 = 关联，相当于 内连接

select b.id as boy_id, g.id as girl_id from boy b, girl g where b.girl_id = g.id order by b.id asc;

-- 左连接

select b.id as boy_id, g.id as girl_id from boy b left join girl g on b.girl_id = g.id order by b.id asc;

-- 右连接

select b.id as boy_id, g.id as girl_id from boy b right join girl g on b.girl_id = g.id order by b.id asc;

-- 2个 左连接，通过 union 合并，相当于 全连接

select b.id as boy_id, g.id as girl_id from boy b left join girl g on b.girl_id = g.id

union

select b.id as boy_id, g.id as girl_id from girl g left join boy b on g.boy_id = b.id

------

分享到：

2010-08-28 23:04

浏览 1628

分类:数据库

评论