MySQL子查询join连接union

where型子查询

指把内层查询的结果作为外层查询的比较条件，典型题：查询id最大,最贵商品

如果where 列 =（内层sql），则内层sql返回的必须是单行单列，单个值；

如果where 列 in（内层sql），则内层sql只返回单列，可以多行。

--查出本网站最新的（goods_id最大）的一条商品
--按goods_id desc排序，再取第一行
select goods_id,goods_name from goods
order 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 from goods 
where 
goods_id = (select max(goods_id) from goods);

用where型子查询，查询"每个栏目下goods_id最大的商品"？

--先查出所有栏目下最大的商品id，然后根据上步的id在商品表中取出相应id的商品
select goods_id,goods_name,cat_id from goods
where 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 from goods
order by goods_id DESC,cat_id ASC;
--如果存在上述查询结果的这张表，表名为tmp，则只需select * from tmp group by cat_id;就可以得到结果
--因为group by取每个分组下第一次出现的行
select * from (select goods_id,cat_id,goods_name
from goods 
order by goods_id DESC,cat_id ASC) as tmp
group 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 from category
where 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 * FROM T1,T2;
从行的角度看：就是2表每一行，两两组合。
从列的角度看：结果集中的列，是两表的 列名的相加（列可以重复）
如果在多表联查时，某一列名，在2张或2张以上表都有，则需要在列名前，指定表名，即表名.列名 
--利用2表全相乘来查询，生成大量数据，占用大量内存，效率低下
SELECT goods_id,goods_name,cat_name
FROM goods,category
WHERE goods.cat_id = category.cat_id; 

1.左连接

假设A表在左，不动，B表在A表的右边滑动，A表与B表通过一个关系来筛选B表的行。

A LEFT JOIN B ON 条件 #当条件为真，则B表对应的行取出 
A LEFT JOIN B ON     --这一块形成的也是一个结果集，可以看成一张表，设为C
--C表的可以查询的列是A，B的列
--即如此，可以对C表做查询，自然where,group by,having,order by,limit 
--改进上面的笛卡尔积查询，利用左连接
SELECT goods_id,goods.cat_id,goods_name,cat_name
FROM
goods LEFT JOIN category ON goods.cat_id = category.cat_id; 

2.右连接

--左右连接是可以互换的
A LEFT JOIN B ==> B RIGHT JOIN A 
--上面的查询用右连接
SELECT goods_id,goods.cat_id,goods_name,cat_name
FROM
category RIGHT 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 from m 
left join t as t1 on m.hid = t1.tid 
left join t as t2 on m.gid = t2.tid 
where mtime between '2006-06-01' and '2006-07-01';

union 联合

合并2条或多条语句的结果,union合并的是结果集，不区分来自于哪张表。

--语法：
sql1 union sql2 
--要求查出价格低于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 from goods 
where cat_id = 3 order by shop_price DESC limit 3)
UNION
(SELECT goods_id,cat_id,goods_name,shop_price from goods 
where 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 * FROM a
UNION
SELECT * FROM b
--再sum一下
select id,sum(num) from (
SELECT * FROM a
UNION
SELECT * FROM b
) as tmp group by id;
--不需要去重复 只需将union改成union all
select id,sum(num) from (
SELECT * FROM a
UNION ALL
SELECT * FROM b
) as tmp group by id;