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;