mysql中的join，left join，right join的用法超级详解！！！_MySQL

bitsCN.com

  今天在看brophp框架的时候，顺手把mysql中的联合查询复习了一下。（以前我就会最简单的“select * from .....”），其他的与其说不屑练习倒不如说不敢用。我现在就是把以前的那些“盲点”扫除一下。
      接下来我就说一下简单的联合查询，当然我们得首先建立几个数据库表，我平有限，暂时先用两个表来演示：
创建bro_cats表：
 
create table bro_cats( 
id int(11) not null auto_increment, 
name varchar(128) not null default '', 
desn varchar(128) not null default '',
primary key() 
);
创建bro_articles表：
 
create table bro_articles( 
id int not null auto_increment, 
cid int not null, 
name varchar(128) not null, 
content test not null, 
primary key(id) 
);
接下来我们向里边插入数据
 
INSERT INTO bro_cats(name, desn) values(‘php’,’php demo’); 
 
INSERT INTO bro_cats(name, desn) values(‘jsp’,’jsp demo’); 
 
INSERT INTO bro_cats(name, desn) values(‘asp’,’asp demo’); 
INSERT INTO bro_articles(cid, name, content)  //php 类中 cid=1
 
values(1,’this article of php1’, ‘php content1’); 
 
INSERT INTO bro_articles(cid, name, content)  //php 类中 cid=1
 
values(1,’this article of php2’, ‘php content2’); 
 
INSERT INTO bro_articles(cid, name, content)  //jsp 类中 cid=2
 
values(2,’this article of jsp’, ‘jsp content’); 
 
INSERT INTO bro_articles(cid, name, content)  //asp 类中 cid=3
 
values(3,’this article of asp1’, ‘asp content1’); 
 
INSERT INTO bro_articles(cid, name, content)  //asp 类中 cid=3
 
values(3,’this article of asp2’, ‘asp content2’); 
下一步我们就用join语句测试一下
 
左联接：left join 
select bro_cats.name as catsname,bro_cats.desn as description,bro_articles.name as articlesname,bro_articles.content as articlecontent from bro_cats left join bro_articles on bro_cats.id = bro_articles.cid; 
右联接：right join 
select bro_cats.name as catsname,bro_cats.desn as description,bro_articles.name as articlesname,bro_articles.content as articlecontent from bro_cats right join bro_articles on bro_cats.id = bro_articles.cid; 
联接：join 
select bro_cats.name as catsname,bro_cats.desn as description,bro_articles.name as articlesname,bro_articles.content as articlecontent from bro_cats join bro_articles on bro_cats.id = bro_articles.cid;
具体的这三个是什么效果，我还是建议大家试一下比较一下自己得出结论。
以后会扩展多个表查询，以及如何优化多表查询，欢迎大家关注我的博客！！记得看了回复额，希望有不对的地方大家指正，毕竟是菜鸟，有好多得向老鸟们学习！！！
作者“You_Can”

bitsCN.com