mysql join
来源:互联网 发布:纳尔逊勋爵 知乎 编辑:程序博客网 时间:2024/06/05 18:56
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
来自:http://kuchaguangjie.iteye.com/blog/749901
------
- mysql join
- mysql join
- mysql join
- mysql join
- mysql join
- MySQL join
- MySQL-Join
- MySQL join
- join mysql
- mysql left join right join inner join
- mysql left join,right join,inner join
- Mysql-left join,right join,inner join
- mysql left join,right join,inner join
- MySQL join left join right join 区别
- MySQL Left Join,Right Join
- MySQL Left Join,Right Join
- mysql left join right join
- Mysql Inner Join , Outer Join
- OV9650摄像头驱动分析 (2.6.35)
- g++: error trying to exec 'cc1plus': execvp: 没有那个文件或目录
- Bugfree使用手册
- opencv提取视频每一帧
- 童牧晨玄 # 丰富多彩的危害 阅读笔记
- mysql join
- OV9650驱动程序跟踪分析
- sql中的group by 和 having 用法解析
- VPN的实现原理
- GDB详解
- Java Swing编程:JTable表格
- mysql编码问题
- Xcode 4.1~4.6 + iOS 5、iOS 6免证书(iDP)开发+真机调试+生成IPA全攻略
- java WebService jws 入门学习笔记