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;