mysql join子查詢_MySQL子查詢join連接配接union

where型子查詢

指把内層查詢的結果作為外層查詢的比較條件，典型題：查詢id最大,最貴商品

如果where 列 =(内層sql)，則内層sql傳回的必須是單行單列，單個值；

如果where 列 in(内層sql)，則内層sql隻傳回單列，可以多行。

--查出本網站最新的(goods_id最大)的一條商品--按goods_id desc排序，再取第一行

select goods_id,goods_name fromgoodsorder by goods_id desc limit 0,1;--查出本網站最新的(goods_id最大)一條商品，要求：不用排序--其實很簡單，實質就是下面的語句(得到最大的goods_id)--select goods_id,goods_name from goods where goods_id = 32;--查出最大的goods_id -> select max(goods_id) from goods;

select goods_id,goods_name fromgoodswheregoods_id= (select max(goods_id) from goods);

用where型子查詢，查詢"每個欄目下goods_id最大的商品"？

--先查出所有欄目下最大的商品id，然後根據上步的id在商品表中取出相應id的商品

select goods_id,goods_name,cat_id fromgoodswhere goods_id in(select max(goods_id) from goods group by cat_id)

from型子查詢

把内層的查詢結果當成臨時表，供外層sql再次查詢，典型題：查詢id最大,最貴商品

--查出本網站最新的(goods_id最大)的一條商品

select goods_id,cat_id,goods_name fromgoodsorder by goods_id DESC,cat_id ASC;--如果存在上述查詢結果的這張表，表名為tmp，則隻需select * from tmp group by cat_id;就可以得到結果--因為group by取每個分組下第一次出現的行

select * from (selectgoods_id,cat_id,goods_namefromgoodsorder by goods_id DESC,cat_id ASC) astmpgroup by cat_id order by goods_id;

exists型子查詢

把外層sql的結果，拿到内層sql去測試，如果内層sql成立，則該行取出

--要求：查出有商品的欄目->取欄目表，且隻取有商品的欄目--假設欄目cat_id為N，則select * from goods where cat_Id = N -->能取出資料，則說明該欄目有商品

select cat_id,cat_name fromcategorywhere exists(select * from goods where goods.cat_id=category.cat_id);

連接配接join

集合知識

集合的特點:無序性、唯一性

集合的運算：求交集，并集，笛卡爾積(相乘)

笛卡爾積：即集合的元素，做兩兩的組合

例：集合a：2，3，5 集合b：4，7集合a*b？

得到一個新集合(2,4)(2,7)(3,4)(3,7)(5,4)(5,7)

表與集合的關系

一個集合就是一張表，表中的一條記錄就是集合中的一個元素

兩張表做笛卡爾積SELECT * FROMT1,T2;

從行的角度看：就是2表每一行，兩兩組合。

從列的角度看：結果集中的列，是兩表的 列名的相加(列可以重複)

如果在多表聯查時，某一列名，在2張或2張以上表都有，則需要在列名前，指定表名，即表名.列名--利用2表全相乘來查詢，生成大量資料，占用大量記憶體，效率低下

SELECTgoods_id,goods_name,cat_nameFROMgoods,categoryWHERE goods.cat_id = category.cat_id;

1.左連接配接

假設A表在左，不動，B表在A表的右邊滑動，A表與B表通過一個關系來篩選B表的行。

A LEFT JOIN B ON條件 #當條件為真，則B表對應的行取出

ALEFT JOIN B ON --這一塊形成的也是一個結果集，可以看成一張表，設為C--C表的可以查詢的列是A，B的列--即如此，可以對C表做查詢，自然where,group by,having,order by,limit--改進上面的笛卡爾積查詢，利用左連接配接

SELECTgoods_id,goods.cat_id,goods_name,cat_nameFROMgoodsLEFT JOIN category ON goods.cat_id = category.cat_id;

2.右連接配接

--左右連接配接是可以互換的

A LEFT JOIN B ==> B RIGHT JOINA--上面的查詢用右連接配接

SELECTgoods_id,goods.cat_id,goods_name,cat_nameFROMcategoryRIGHT JOIN goods ON goods.cat_id =category.cat_id;--既然左右連接配接可以互換，盡量用左連接配接，出于移植時相容性方面的考慮。

3.内連接配接

如果從集合的角度看，A inner join B 和 B inner join A，取交集，内連接配接是左右連接配接的交集。

外連接配接：結果是左右連接配接的并集，但mysql中不支援外連接配接。

面試題

--賽程表

+-----+-----+-----+------+------------+

| mid | hid | gid | mres | mtime |

+-----+-----+-----+------+------------+

| 1 | 1 | 2 | 2:0 | 2006-05-21 |

| 2 | 2 | 3 | 1:2 | 2006-06-21 |

| 3 | 3 | 1 | 2:5 | 2006-06-25 |

| 4 | 2 | 1 | 3:2 | 2006-07-25 |

+-----+-----+-----+------+------------+--參賽隊伍表

+-----+----------+

| tid | tname |

+-----+----------+

| 1 | 國安 |

| 2 | 申花 |

| 3 | 公益聯隊 |

+-----+----------+--Match的hID與gID都與Team中的tID關聯--查出 2006-6-1 到2006-7-1之間舉行的所有比賽，并且用以下形式列出：--拜仁 2：0 不來梅 2006-6-21

select t1.tname as hname,m.mres,t2.tname as tname,m.mtime frommleft join t as t1 on m.hid =t1.tidleft join t as t2 on m.gid =t2.tidwhere mtime between '2006-06-01' and '2006-07-01';

union 聯合

合并2條或多條語句的結果,union合并的是結果集，不區分來自于哪張表。

--文法：

sql1 unionsql2--要求查出價格低于30元和價格高于4000元的商品，不能用or

SELECT goods_id,goods_name,shop_price FROM goods WHERE shop_price <30

UNION

SELECT goods_id,goods_name,shop_price FROM goods WHERE shop_price >4000;--合并2張不同表的資料

SELECT user_name,msg_content,msg_time FROM feedback WHERE msg_status = 1

UNION

SELECT user_name,content as msg_content,add_time as msg_time FROM comment WHERE STATUS = 1;

如果取出的結果集，列名稱不一樣，同樣可以union，并且取出的最終列名以第1條sql為準。

--改結果集的列名以第一條sql為準，即user_name,msg_content,msg_time

SELECT user_name,msg_content,msg_time FROM feedback WHERE msg_status = 1

UNION

SELECT user_name,content ,add_time FROM comment WHERE STATUS = 1;

隻要結果集中的列數一緻就可以用union進行聯合操作。即使列的類型不一緻也可以，但這樣沒什麼意義。

union後的資料可以進行排序(order by)操作 sql1 union sql2 order by 字段。内層的order by語句單獨使用，不會影響結果集，僅排序，在執行期間，就被mysql的代碼分析器給優化掉了；内層的order by必須能夠影響結果集時才有意義，比如配合limit使用。

--用union取出第4個欄目的欄目，和第5個欄目的商品，并按價格升序排列--内層的ORDER BY shop_price DESC不會起作用，無意義，被查詢優化器優化掉了

SELECT goods_id,cat_id,goods_name,shop_price from goods where cat_id=4 ORDER BY shop_price DESC

UNION

SELECT goods_id,cat_id,goods_name,shop_price from goods where cat_id =5 ORDER BY shop_price DESC

ORDER BY shop_price ASC;--第3個欄目下，價格前3高的商品，和第4個欄目下，價格前2高的商品，用union來完成--因為有limit，order by将影響傳回值，有意義，不會被查詢優化器優化掉。

(SELECT goods_id,cat_id,goods_name,shop_price fromgoodswhere cat_id = 3 order by shop_price DESC limit 3)UNION(SELECT goods_id,cat_id,goods_name,shop_price fromgoodswhere cat_id = 4 order by shop_price DESC limit 2)

如果union後的結果有重複(即某2行或N行，所有的列值都相同)預設會去重，如果不想去重，須用union all。

--面試題

A表+----+-----+

| id | num |

+----+-----+

| a | 5 |

| b | 10 |

| c | 15 |

| d | 10 |

+----+-----+

B表+----+-----+

| id | num |

+----+-----+

| b | 5 |

| c | 10 |

| d | 20 |

| e | 99 |

+----+-----+

須得到如下結果+------+----------+

| id | sum(num) |

+------+----------+

| a | 5 |

| b | 15 |

| c | 25 |

| d | 30 |

| e | 99 |

+------+----------+--可用左連接配接來做

select a.*,b.* from a left join b on a.id =b.id;--再把上面看成一張臨時表，再次from型子查詢，計算a.num+b.num的和，ifnull函數--而且少了e，隻好左聯union右聯，再子查詢。很複雜--另一思路，先把2表資料union到一塊，再sum函數來相加

SELECT * FROMaUNION

SELECT * FROMb--再sum一下

select id,sum(num) from(SELECT * FROMaUNION

SELECT * FROMb

)as tmp group byid;--不需要去重複 隻需将union改成union all

select id,sum(num) from(SELECT * FROMaUNION ALL

SELECT * FROMb

)as tmp group by id;