今天在看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”