欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql中的join,left join,right join的用法超级详解!!!_MySQL

程序员文章站 2022-04-19 20:09:42
...
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
相关标签: mysql