今天要分享的内容是mysql的select子查询
一直记得凯哥,半年前说过的一句话,原来你不知道,子查询啊,其实,我知道是又这么一回事
但是,具体又说不上来,大概就是那种,一看就知道,但是你有不完全知道的类型吧
现在在写hive语法的时候又碰到了,那么就好好总结一次吧。
子查询定义
在一个表表达中可以调用另一个表表达式,这个被调用的表表达式叫做子查询(subquery),
那么也称作子选择(subselect)或内嵌选择(inner select)。
子查询的结果传递给调用它的表达式继续处理。
说人话就是,一个sql语句,出现了2个以上的select关键词,这个清晰了吧?
子查询分类1
1*1 标量子查询:返回的结果集是一个标量集合,一行一列,也就是一个标量值。可以指定一个标量表达式的任何地方,都可以用一个标量子查询。
1*n 行子查询:返回的结果集是一个列的集合,一行N列(N>=1)。行子查询可以用于福查询的FROM子句和WHERE子句中。
n*1 列子查询:返回的结果集是一个行的集合,N行一列(N>=1)。
n*n 表子查询:返回的结果集是一个行的集合,N行N列(N>=1)。表子查询经常用于父查询的FROM子句中。
就这么四类
从定义上讲,
每个标量子查询也是一个行子查询和一个列子查询,反之则不是;
每个行子查询和列子查询也是一个表子查询,反之也不是。
子查询分类2
where型子查询:(把内层查询结果当作外层查询的比较条件)
定义:where型的子查询就是把内层查询的结果当作外层查询的条件。
from型子查询:(把内层的查询结果供外层再次查询)
定义:from子查询就是把子查询的结果(内存里的一张表)当作一张临时表,然后再对它进行处理。
exists型子查询:(把外层查询结果拿到内层,看内层的查询是否成立)
定义:exists子查询就是对外层表进行循环,再对内表进行内层查询。和in ()差不多,但是它们还是有区别的。主要是看两个张表大小差的程度。若子查询表大则用exists(内层索引),子查询表小则用in(外层索引);
其实,光这么说,很难明白的,因为你都没写过,根本理解不了,尤其是exisit子查询
使用子查询原则
1.一个子查询必须放在圆括号中。
2.将子查询放在比较条件的右边以增加可读性。
子查询不包含 ORDER BY 子句。对一个 SELECT 语句只能用一个 ORDER BY 子句,并且如果指定了它就必须放在主 SELECT 语句的最后。
3.在子查询中可以使用两种比较条件:单行运算符(>, =, >=, <, <>, <=) 和多行运算符(IN, ANY, ALL)。
where型子查询
select * from a where a.xx = (select xx from b where b.xx=”xx”),这种感觉
from型子查询
select * from (select * from a) as aa,这种感觉
exists型子查询
因为上面那两种,子查询,很明了简单,就不谈了,主要说下面的
尽量表达清楚
EXISTS关键字表示存在。使用EXISTS关键字时,内层查询语句不返回查询的记录,
而是返回一个真假值,如果内层查询语句查询到满足条件的记录,
只要子查询中至少返回一个值,则EXISTS语句的值就为True。
就返回true,否则返回false。当返回的值为true时,外层查询语句将进行查询,
否则不进行查询。NOT EXISTS刚好与之相反。exists的用法和in ()差不多,
但是它们还是有区别的。主要是看两个张表大小差的程度。
若子查询表大则用exists(内层索引),子查询表小则用in(外层索引);
看上面这段话的,解释,保证,你看了,也不知道在说啥,其实说的稍微有点问题
正确的解释,应该是,上面的当然也没错
exists对外表用loop逐条查询,每次查询都会查看exists的条件语句,当 exists里的条件语句能够返回记录行时(无论记录行是的多少,只要能返回),条件就为真,返回当前loop到的这条记录,反之如果exists里的条 件语句不能返回记录行,则当前loop到的这条记录被丢弃,exists的条件就像一个bool条件,当能返回结果集则为true,不能返回结果集则为 false
就是循环外层的查询结果,然后带进去,查看条件是否成立,不成立丢弃,成立留下
select * from user where exists (select 1);
这句话,就会输出,user表中全部的人,因为后面的条件,对于每个人都成立
select * from user where userId in (1, 2, 3);
这样的in查询,等价于
select * from user where userId = 1 or userId = 2 or userId = 3;
因此,要求in后面的表是小表,才不至于,很大的表,循环多次
记住一句话,谁的表大就用谁的索引
下面来考虑exists和in的性能
考虑如下SQL语句
1: select * from A where exists (select * from B where B.id = A.id);
2: select * from A where A.id in (select id from B);
查询1.可以转化以下伪代码,便于理解
for ($i = 0; $i < count(A); $i++) {
$a = get_record(A, $i); #从A表逐条获取记录
if (B.id = $a[id]) #如果子条件成立
$result[] = $a;
}
return $result;
大概就是这么个意思,其实可以看到,查询1主要是用到了B表的索引,A表如何对查询的效率影响应该不大
也就是说,表B越大就越能利用到索引的优势
而假设B表是小表的所有id为1,2,3,查询2可以转换为
select * from A where A.id = 1 or A.id = 2 or A.id = 3;
这个好理解了,这里主要是用到了A的索引,B表如何对查询影响不大
也就是说,表A越大就越能利用到索引的优势
下面再看not exists 和 not in
1. select * from A where not exists (select * from B where B.id = A.id);
2. select * from A where A.id not in (select id from B);
看查询1,还是和上面一样,用了B的索引
而对于查询2,可以转化成如下语句
select * from A where A.id != 1 and A.id != 2 and A.id != 3;
可以知道not in是个范围查询,这种!=的范围查询无法使用任何索引,等于说A表的每条记录,都要在B表里遍历一次,查看B表里是否存在这条记录
not in 和not exists如果查询语句
使用了not in 那么内外表都进行全表扫描,没有用到索引;而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
总结
先大概说一下,这个子查询吧,其实,mysql还有很多的细节地方要注意,有时间再说说,自己的理解吧
补充一句,昨天和妹子出去吃饭,今天居然就感冒生病了,心疼啊/(ㄒoㄒ)/~~,怪我了