• 售前

  • 售后

热门帖子
入门百科

MySQL内里的子查询的基本利用

[复制链接]
米老鼠和蓝精鼠v 显示全部楼层 发表于 2021-8-14 14:40:33 |阅读模式 打印 上一主题 下一主题
目次


  • 一、子查询定义
  • 二、子查询分类

    • 1. 标量子查询:
    • 2. MySQL 列子查询:
    • 3. MySQL 行子查询:
    • 4. MySQL 表子查询:

  • 三、字查询例举

    • 1. ANY举行子查询
    • 2. 利用IN举行子查询
    • 3. 利用SOME举行子查询
    • 4. 利用ALL举行子查询
    • 5.标量子查询
    • 6. 多值子查询
    • 7. 独立子查询
    • 8.相干子查询
    • 9.EXISTS谓词
    • 10. 派生表

  • 四、子查询优化

一、子查询定义


  定义:
  子查询允许把一个查询嵌套在另一个查询当中。
  子查询,又叫内部查询,相对于内部查询,包罗内部查询的就称为外部查询。
  子查询可以包罗普通select可以包罗的任何子句,比如:distinct、 group by、order by、limit、join和union等;但是对应的外部查询必须是以下语句之一:select、insert、update、delete、set或 者do。
  子查询的位置:select 中、from 后、where 中.group by 和order by 中无实用意义。

二、子查询分类


子查询分为如下几类:
    1. 标量子查询:返回单一值的标量,最简单的情势。
    2. 列子查询:返回的效果集是 N 行一列。
    3. 行子查询:返回的效果集是一行 N 列。
    4. 表子查询:返回的效果集是 N 行 N 列。

  可以利用的操纵符:= > < >= <= <> ANY IN SOME ALL EXISTS
  一个子查询会返回一个标量(就一个值)、一个行、一个列或一个表,这些子查询称之为标量、行、列和表子查询。
  如果子查询返回一个标量值(就一个值),那么外部查询就可以利用:=、>、<、>=、<=和<>符号举行比较判定;如果子查询返回的不是一个标量值,而外部查询利用了比较符和子查询的效果举行了比较,那么就会抛出非常。

1. 标量子查询:

是指子查询返回的是单一值的标量,如一个数字或一个字符串,也是子查询中最简单的返回情势。 可以利用 = > < >= <= <> 这些操纵符对子查询的标量效果举行比较,通常子查询的位置在比较式的右侧

示例:
  1. SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
  2. SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
  3. SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)
复制代码
2. MySQL 列子查询:

    指子查询返回的效果集是 N 行一列,该效果通常来自对表的某个字段查询返回。
    可以利用 = > < >= <= <> 这些操纵符对子查询的标量效果举行比较,通常子查询的位置在比较式的右侧
    可以利用 IN、ANY、SOME 和 ALL 操纵符,不能直接利用 = > < >= <= <> 这些比较标量效果的操纵符。
    示例:
  1. SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
  2. SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
  3. SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)
复制代码
NOT IN 是 <> ALL 的别名,二者雷同。

特别情况
       
  • 如果 table2 为空表,则 ALL 后的效果为 TRUE;   
  • 如果子查询返回如 (0,NULL,1) 这种只管 s1 比返回效果都大,但有空行的效果,则 ALL 后的效果为 UNKNOWN 。
注意:对于 table2 空表的情况,下面的语句均返回 NULL:
  1. SELECT s1 FROM table1 WHERE s1 > (SELECT s2 FROM table2)
  2. SELECT s1 FROM table1 WHERE s1 > ALL (SELECT MAX(s1) FROM table2)
复制代码
3. MySQL 行子查询:

指子查询返回的效果集是一行 N 列,该子查询的效果通常是对表的某行数据举行查询而返回的效果集。

例子:
  1. SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
  2. 注:(1,2) 等同于 row(1,2)
  3. SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)
复制代码
4. MySQL 表子查询:

指子查询返回的效果集是 N 行 N 列的一个表数据。

例子:
  1. SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
复制代码
三、字查询例举



1. ANY举行子查询

  any关键词的意思是“对于子查询返回的列中的任何一个数值,如果比较效果为TRUE,就返回TRUE”。
  好比“10 >any(11, 20, 2, 30)”,由于10>2,以是,该该判定会返回TRUE;只要10与集合中的恣意一个举行比较,得到TRUE时,就会返回TRUE。 
  1. select table1.customer_id,city,count(order_id)
  2. from table1join table2
  3. on table1.customer_id=table2.customer_id
  4. where table1.customer_id<>'tx' and table1.customer_id<>'9you'
  5. group by customer_id
  6. having count(order_id) >
  7. any (
  8. select count(order_id)
  9. from table2
  10. where customer_id='tx' or customer_id='9you'
  11. group by customer_id);
复制代码
  any的意思比较好明确,直译就是恣意一个,只要条件满意恣意的一个,就返回TRUE。

2. 利用IN举行子查询

  利用in举行子查询,这个我们在日常写sql的时间是常常碰到的。in的意思就是指定的一个值是否在这个集合中,如安在就返回TRUE;否则就返回FALSE了。
  in是“=any”的别名,在利用“=any”的地方,我们都可以利用“in”来举行更换。
  有了in,肯定就有了not in;not in并不是和<>any是同样的意思,not in和<>all是一个意思。

3. 利用SOME举行子查询

  some是any的别名,用的比较少。  

4. 利用ALL举行子查询

  all必须与比较操纵符一起利用。all的意思是“对于子查询返回的列中的全部值,如果比较效果为TRUE,则返回TRUE”。
  好比“10 >all(2, 4, 5, 1)”,由于10大于集合中的全部值,以是这条判定就返回TRUE;而如果为“10 >all(20, 3, 2, 1, 4)”,如许的话,由于10小于20,以是该判定就会返回FALSE。
  <>all的同义词是not in,表现不即是集合中的全部值,这个很容易和<>any搞混,平时多留点心就好了。

5.标量子查询

  根据子查询返回值的数量,将子查询可以分为标量子查询和多值子查询。在利用比较符举行子查询时,就要求必须是标量子查询;如果是多值子查询时,利用比较符,就会抛出非常。

6. 多值子查询

  与标量子查询对应的就是多值子查询了,多值子查询会返回一列、一行或者一个表,它们构成一个集合。我们一样寻常利用的any、in、all和some等词,将外部查询与子查询的效果举行判定。如果将any、in、all和some等词与标量子查询,就会得到空的效果。


7. 独立子查询

  独立子查询是不依赖外部查询而运行的子查询。什么叫依赖外部查询?先看下面两个sql语句。
  sql语句1:获得全部hangzhou顾客的订单号。 
  1. select order_id
  2. from table2
  3. where customer_idin
  4.           (select customer_id
  5.           from table1
  6.           where city='hangzhou');
  7.   sql语句2:获得城市为hangzhou,并且存在订单的用户。
  8. select *
  9. from table1
  10. where city='hangzhou' and exists
  11.                 (select *
  12.                 from table2
  13.                 where table1.customer_id=table2.customer_id);
复制代码
        上面的两条sql语句,虽然例子举的有点不是很恰当,但是足以阐明这里的问题了。
    对于sql语句1,我们将子查询单独复制出来,也是可以单独执行的,就是子查询与外部查询没有任何关系。
    对于sql语句2,我们将子查询单独复制出来,就无法单独执行了,由于sql语句2的子查询依赖外部查询的某些字段,这就导致子查询就依赖外部查询,就产生了相干性。
  对于子查询,很多时间都会思量到效率的问题。当我们执行一个select语句时,可以加上explain关键字,用来查察查询范例,查询时利用的索引以及别的等等信息。比如这么用:
  1. explainselect order_id
  2.   from table2
  3.   where customer_idin
  4.             (select customer_id
  5.             from table1
  6.             where city='hangzhou');
复制代码
  利用独立子查询,如果子查询部分对集合的最大遍历次数为n,外部查询的最大遍历次数为m时,我们可以记为:O(m+n)。而如果利用相干子查询,它的遍历 次数可能会到达O(m+m*n)。可以看到,效率就会成倍的降落;以是,大伙在利用子查询时,肯定要思量到子查询的相干性。

8.相干子查询

  相干子查询是指引用了外部查询列的子查询,即子查询会对外部查询的每行举行一次盘算。但是在MySQL的内部,会举行动态优化,会随着情况的差异会 有所差异。利用相干子查询是最容易出现性能的地方。而关于sql语句的优化,这又是一个非常大的话题了,只能通过现实的经验积聚,才气更好的去明白如何进 行优化。


9.EXISTS谓词

  EXISTS是一个非常牛叉的谓词,它允许数据库高效地查抄指定查询是否产生某些行。根据子查询是否返回行,该谓词返回TRUE或FALSE。与其 它谓词和逻辑表达式差异的是,无论输入子查询是否返回行,EXISTS都不会返回UNKNOWN,对于EXISTS来说,UNKNOWN就是FALSE。 还是上面的语句,获得都会为hangzhou,而且存在订单的用户。
  1. select *
  2. from table1
  3. where city='hangzhou' and exists
  4.                 (select *
  5.                 from table2
  6.                 where table1.customer_id=table2.customer_id);
复制代码
  关于IN和EXISTS的重要区别在于三值逻辑的判定上。EXISTS总是返回TRUE或FALSE,而对于IN,除了TRUE、FALSE值外, 尚有可能对NULL值返回UNKNOWN。但是在过滤器中,UNKNOWN的处置惩罚方式与FALSE雷同,因此利用IN与利用EXISTS一样,SQL优化 器会选择雷同的执行筹划。
  说到了IN和EXISTS几乎是一样的,但是,就不得不说到NOT IN和NOT EXISTS,对于输入列表中包罗NULL值时,NOT EXISTS和NOT IN之间的差异就表现的非常大了。输入列表包罗NULL值时,IN总是返回TRUE和UNKNOWN,因此NOT IN就会得到NOT TRUE和NOT UNKNOWN,即FALSE和UNKNOWN。

10. 派生表

  上面也说到了,在子查询返回的值中,也可能返回一个表,如果将子查询返回的虚拟表再次作为FROM子句的输入时,这就子查询的虚拟表就成为了一个派生表。语法布局如下:
  1. FROM (subquery expression) AS derived_table_alias
复制代码
  由于派生表是完全的虚拟表,并没有也不可能被物理地具体化。

四、子查询优化


  很多查询中必要利用子查询。利用子查询可以一次性的完成很多逻辑上必要多个步骤才气完成的SQL操纵,同时也可以制止变乱或者表锁死。子查询可以使查询语 句很机动,但子查询的执行效率不高。
  子查询时,MySQL必要为内层查询语句的查询效果创建一个临时表。然后外层查询语句再临时表中查询记录。查询完毕 后,MySQL必要取消这些临时表。因此,子查询的速率会受到肯定的影响。如果查询的数据量比较大,这种影响就会随之增大。
  在MySQL中可以利用连接查 询来替换子查询。连接查询不必要创建临时表,其速率比子查询要快。
       利用连接(JOIN)来代替子查询

       如:   
例子1:
  1. SELECT * FROM t1
  2. WHERE t1.a1 NOTin (SELECT a2 FROM t2 )
  3. 优化后:
  4. SELECT * FROM t1
  5. LEFT JOIN t2 ON t1.a1=t2.a2
  6. WHERE t2.a2 IS NULL  
复制代码
例子2:
  1. SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
  2. 优化后:
  3. SELECT * FROM article
  4. innerjoin blog
  5. on (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)
复制代码
  不能优化的子查询:
  1、mysql不支持子查询合并和聚合函数子查询优化,mariadb对聚合函数子查询举行物化优化;
  2、mysql不支持from子句子查询优化,mariadb对from子句子查询举行子查询上拉优化;
  3、mysql和mariadb对子查询睁开提供有限的支持,如对主键的操纵才气举行上拉子查询优化;
  4、mysql不支持exists子查询优化,mariadb对exists关联子查询举行半连接优化,对exists非关联子查询没有进一步举行优化;
  5、mysql和mariadb不支持not exists子查询优化;
  6、mysql和mariadb对in子查询,对满意半连接语义的查询举行半连接优化,再基于代价评估举行优化,两者对半连接的代价评估选择方式有差异;
  7、mysql不支持not in子查询优化,mariadb对非关联not in子查询利用物化优化,对关联not in子查询不做优化;
  8、mysql和mariadb对>all非关联子查询利用max函数,<all非关联子查询利用min函数,对=all和非关联子查询利用exists优化;
  9、对>some和>any非关联子查询利用min函数,对<some和<any非关联子查询利用max函数,=any 和=some子查询利用半连接举行优化,对>some和>any关联子查询以及<some和<any关联子查询只有exists 优化。
到此这篇关于MySQL里面的子查询的基本利用的文章就介绍到这了,更多相干MySQL 子查询内容请搜索草根技术分享以前的文章或继承浏览下面的相干文章希望各人以后多多支持草根技术分享!

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作