(把内层的询问结果供外层再次查询卡塔尔(قطر‎,轻易询问里面 后边的尺度 未知时
分类:热门新闻

以此抽取奖品小程序,在实际的测量试验情状中也足以用到,例如说测量检验数据的询问在in条件下,要查询随机的5个id,然后在用ab去压测

MySQL的子查询及连锁优化学习课程,mysql教程

一、子查询
1、where型子查询
(把内层查询结果作为外层查询的比较规范)

#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);

#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id); 

2、from型子查询 (把内层的查询结果供外层再度询问卡塔尔(قطر‎
#用子查询得到消息挂科两门及以上的同室的平分成绩
思路:

#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果,我们只要名字就可以了,所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了,那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;

3、exists型子查询 (把外围查询结果获得内层,看内层的询问是否创建)

#查询哪些栏目下有商品,栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

二、优化 从句式的样式看,子查询分为特殊格式子查询和非特出格式子查询,特殊格式的子查询中又包罗IN、ALL、ANY、SOME、EXISTS等类别的子查询,对于一些连串的子查询,MySQL有的协理优化,有的不帮助,具体意况如下。

 

示例豆蔻梢头,MySQL不扶持对EXISTS类型的子查询的优化:

EXISTS类型的相关子查询,查询实践安排如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.a1= t2.a2 AND t2.a2>10);

+----+--------------------+-------+------+------+-------------+
| id | select_type    | table | type | key | Extra    |

+----+--------------------+-------+------+------+-------------+

| 1 | PRIMARY      | t1  | ALL | NULL | Using where |

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 2 warnings (0.00 sec)

被询问优化器管理后的话语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where exists(/* select#2 */

  select 1

  from `test`.`t2`

  where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10))

)

从询问实践安排看,子查询存在,MySQL未有进一层做子查询的优化职业。

其它的一个EXISTS类型的相关子查询,查询实行安插如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type    | table | type | key | Extra    |

+----+--------------------+-------+------+------+-------------+

| 1 | PRIMARY      | t1  | ALL | NULL | Using where |

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 3 warnings (0.02 sec)

被询问优化器管理后的言语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where exists(/* select#2 */

  select 1

  from `test`.`t2`

  where ((`test`.`t1`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))

)

从询问试行安插看,子查询存在,MySQL未有进一层做子查询的优化办事。

 

示例二,MySQL不帮衬对NOT EXISTS类型的子查询的优化:

NOT EXISTS类型的相关子查询,查询施行安排如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.a1= t2.a2 AND t2.a2>10);

+----+--------------------+-------+------+------+-------------+

| id | select_type    | table | type | key | Extra    |

+----+--------------------+-------+------+------+-------------+

| 1 | PRIMARY      | t1  | ALL | NULL | Using where |

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 2 warnings (0.00 sec)

被询问优化器处理后的言语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(exists(

  /* select#2 */ select 1

  from `test`.`t2`

  where ((`test`.`t1`.`a1` = `test`.`t2`.`a2`) and (`test`.`t2`.`a2` > 10))))

)

从询问施行布置看,子查询存在,MySQL未有进一层做子查询的优化办事。

 

别的的三个NOT EXISTS类型的相关子查询,查询实践安排如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.b1= t2.b2 AND t1.a1=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type    | table | type | key | Extra    |

+----+--------------------+-------+------+------+-------------+

| 1 | PRIMARY      | t1  | ALL | NULL | Using where |

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 3 warnings (0.00 sec)

被询问优化器管理后的讲话为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(exists(

  /* select#2 */ select 1

  from `test`.`t2`

  where ((`test`.`t1`.`b1` = `test`.`t2`.`b2`) and (`test`.`t1`.`a1` = 10))))

)

从询问施行安插看,子查询存在,MySQL未有进一层做子查询的优化职业。

 

示例三,MySQL扶助对IN类型的子查询的优化,按也是有不扶助的情事存在:

IN非相关子查询,查询实施安顿如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type | table    | type | key | Extra  |

+----+--------------+-------------+------+------+----------------------------------------------------+

| 1 | SIMPLE    | <subquery2> | ALL | NULL | NULL  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的口舌为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

从询问实施安顿看,表t2被物化后,与表t1施行了半接连(semi join)。就算有“subquery2”那样的剧情看起来是子查询,可是表t2业已被上拉到表t1层施行了半总是,所以MySQL扶植IN子查询优化为半老是操作。

 

除此以外叁个IN非相关子查询,查询施行安顿如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type | table    | type | key | Extra  |

+----+--------------+-------------+------+------+----------------------------------------------------+

| 1 | SIMPLE    | <subquery2> | ALL | NULL | Using where  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.02 sec)

被询问优化器管理后的讲话为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

从询问实行计划看,子查询官样文章,表t1和t2直接做了块嵌套循环半连接(Block Nested Loop),把子查询上拉到父查询中用嵌套循环半连接产生IN操作。别的,由于子查询上拉,使得扩大连接条件“a1=a2”,而原来的法规“a2=10”可以使用常量传递优化技能,使得“a1=a2=10”,所以查询试行安顿中,七个索引围观的尺度分别为:a1 = 10、a2 = 10。

 

除此以外三个IN非相关子查询,查询实施安顿如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1=10);

+----+-------------+-------+------+------------------------------------------------------------------+

| id | select_type | table | type | Extra      |

+----+-------------+-------+------+------------------------------------------------------------------+

| 1 | SIMPLE   | t2  | ALL | Using where; Start temporary      |

| 1 | SIMPLE   | t1  | ALL | Using where; End temporary; Using join buffer (Block Nested Loop)|

+----+-------------+-------+------+------------------------------------------------------------------+

2 rows in set, 2 warnings (0.00 sec)

被询问优化器管理后的口舌为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t2`.`a2` = 10) and (`test`.`t1`.`a1` = 10))

从询问推行安插看,子子查询不设有,表t1和t2直接做了块嵌套循环连接(Block Nested Loop),但归属半三番两次操作(semi join),把子查询上拉到父查询中用嵌套循环半连接成功IN操作。

 

示例四,MySQL援助对NOT IN类型的子查询的优化

NOT IN非相关子查询,查询实施布署如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.02 sec)

被询问优化器管理后的语句为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(<in_optimizer>(

  `test`.`t1`.`a1`,`test`.`t1`.`a1` in (

    <materialize> (/* select#2 */

      select `test`.`t2`.`a2`

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

      having 1

    ),

    <primary_index_lookup>(

      `test`.`t1`.`a1` in <temporary table> on <auto_key>

      where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`))

    )

   )

  ))

)

从询问试行安顿看,表t2做了子查询(SUBQUEHighlanderY)。而子查询被物化(materialize)。所以,MySQL对于NOT IN子查询利用了物化的优化措施,但不协助子查询的驱除。

 

别的一个NOT IN非相关子查询,查询实行安插如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的语句为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where (not(<in_optimizer>(

  `test`.`t1`.`a1`,`test`.`t1`.`a1` in (

    <materialize> (/* select#2 */

      select `test`.`t2`.`a2`

      from `test`.`t2`

      where (`test`.`t2`.`a2` = 10)

      having 1

    ),

    <primary_index_lookup>(

      `test`.`t1`.`a1` in <temporary table> on <auto_key>

      where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`))

    )

  )

  ))

)

从询问实践计划看,表t2做了子查询(SUBQUESportageY)。而子查询被物化(materialize)。所以,MySQL对于NOT IN子查询利用了物化的优化措施,但不协理子查询的肃清。

 

示例五,MySQL援助对ALL类型的子查询的优化:

不相干的ALL子查询,查询试行安顿如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ALL (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的言语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>((`test`.`t1`.`a1` <= <max>(

  /* select#2 */

  select `test`.`t2`.`a2`

  from `test`.`t2`

  where (`test`.`t2`.`a2` > 10)

  )

))

从询问推行布置看,现身了子查询(SUBQUE奥迪Q5Y),但是,子查询被“<= <max>”操作符节制,而子查询中的被询问列a2上设有独一索引,所以可以运用索引求最值,所以MySQL援助“>ALL”式的子查询优化,子查询只被实行三次就可以求得最大值。

 

不相干的ALL子查询,查询施行安排如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ALL (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------------+-------+------+------+-------------+

| id | select_type    | table | type | key | Extra    |

+----+--------------------+-------+------+------+-------------+

| 1 | PRIMARY      | t1  | ALL | NULL | Using where |

| 2 | DEPENDENT SUBQUERY | t2  | ALL | NULL | Using where |

+----+--------------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的讲话为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>(<in_optimizer>(

  `test`.`t1`.`a1`,<exists>(

    /* select#2 */ select 1 from `test`.`t2`

    where ((`test`.`t2`.`a2` = 10) and

      <if>(outer_field_is_not_null,

        ((<cache>(`test`.`t1`.`a1`) <> 10) or <cache>(isnull(10))),

        true

      )

    )

    having <if>(outer_field_is_not_null, <is_not_null_test>(`test`.`t2`.`a2`), true)

  )

))

从询问实行陈设看,出现了子查询(SUBQUELANDY),但是被询问优化器管理后的说话中蕴藏“exists”,那标记MySQL对于“=ALL”式的子查询优化用“EXISTS strategy”情势优化,所以MySQL补助“=ALL”式的子查询优化。

 

不相干的ALL子查询,查询实行安排如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ALL (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的话语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <not>((`test`.`t1`.`a1` >= <min>

  (/* select#2 */

    select `test`.`t2`.`a2`

    from `test`.`t2`

    where (`test`.`t2`.`a2` = 10)

  )

))

从询问试行布置看,现身了子查询(SUBQUE奥迪Q5Y),不过,子查询被“>= <min>”操作符限定,而子查询中的被询问列a2上设有独一索引,所以能够运用索引求最值,所以MySQL帮助“<ALL”式的子查询优化,子查询只被推行一遍就可以求得最小值。

 

示例六,MySQL协理对SOME类型的子查询的优化:

行使了“>SOME”式子的子查询被优化,查询实施布置如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >SOME (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.05 sec)

被询问优化器处理后的话语为:

 /* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

   `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>((`test`.`t1`.`a1` > (

  /* select#2 */

  select min(`test`.`t2`.`a2`)

  from `test`.`t2`

  where (`test`.`t2`.`a2` > 10)

)))

从询问实践布署看,现身了子查询(SUBQUEWranglerY),可是,子查询被“min”函数约束,而子查询中的被询问列a2上存在独一索引,所以能够应用索引求最值,所以MySQL协理“>SOME”式的子查询优化,子查询只被施行一回就能够求得最大值。

 

选拔了“=SOME”式子的子查询被优化,查询推行安插如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =SOME (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type | table    | type | key | Extra  |

+----+--------------+-------------+------+------+----------------------------------------------------+

| 1 | SIMPLE    | <subquery2> | ALL | NULL | Using where  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.01 sec)

被询问优化器管理后的言语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

`test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`<subquery2>`.`a2` = 10) and (`test`.`t1`.`a1` = 10) and (`test`.`t2`.`a2` = 10))

从询问实践布置看,未有现身了子查询,表t2被物化,与表t1举办了半总是。

 

利用了“<SOME”式子的子查询被优化,查询推行陈设如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <SOME (SELECT a2 FROM t2 WHERE t2.a2=10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的语句为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` < (/* select#2 */

      select max(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` = 10)

    )

  )

)

从询问试行安排看,现身了子查询(SUBQUEGL450Y),但是,子查询被“max”函数限定,而子查询中的被询问列a2上设有唯一索引,所以能够动用索引求最值,所以MySQL扶植“<SOME”式的子查询优化,子查询只被奉行一回就可以求得最大值。

 

示例七,MySQL扶植对ANY类型的子查询的优化:

动用了“>ANY”式子的子查询被优化,查询执行布置如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 >ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的话语为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` > (/* select#2 */

      select min(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

    )

  )

)

从询问实践陈设看,现身了子查询(SUBQUETiguanY),但是,子查询被“min”函数约束,而子查询中的被询问列a2上设有独一索引,所以能够利用索引求最值,所以MySQL支持“>ANY”式的子查询优化,子查询只被施行三回就可以求得最小值。

 

应用了“=ANY”式子的子查询被优化,查询实践布署如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 =ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+--------------+-------------+------+------+----------------------------------------------------+

| id | select_type | table    | type | key | Extra  |

+----+--------------+-------------+------+------+----------------------------------------------------+

| 1 | SIMPLE    | <subquery2> | ALL | NULL | NULL  |

| 1 | SIMPLE    | t1     | ALL | NULL | Using where; Using join buffer (Block Nested Loop) |

| 2 | MATERIALIZED | t2     | ALL | NULL | Using where  |

+----+--------------+-------------+------+------+----------------------------------------------------+

3 rows in set, 1 warning (0.02 sec)

被询问优化器管理后的讲话为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1` semi join (`test`.`t2`)

where ((`test`.`t1`.`a1` = `<subquery2>`.`a2`) and (`test`.`t2`.`a2` > 10))

从询问实践布署看,未有现身了子查询,表t2被物化,与表t1开展了半接连。

 

动用了“<ANY”式子的子查询被优化,查询实行安插如下:

mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 <ANY (SELECT a2 FROM t2 WHERE t2.a2>10);

+----+-------------+-------+------+------+-------------+

| id | select_type | table | type | key | Extra    |

+----+-------------+-------+------+------+-------------+

| 1 | PRIMARY   | t1  | ALL | NULL | Using where |

| 2 | SUBQUERY  | t2  | ALL | NULL | Using where |

+----+-------------+-------+------+------+-------------+

2 rows in set, 1 warning (0.00 sec)

被询问优化器管理后的言辞为:

/* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`,

  `test`.`t1`.`b1` AS `b1`

from `test`.`t1`

where <nop>(

  (

    `test`.`t1`.`a1` < (/* select#2 */

      select max(`test`.`t2`.`a2`)

      from `test`.`t2`

      where (`test`.`t2`.`a2` > 10)

    )

  )

)

从询问实施布置看,现身了子查询(SUBQUE揽胜极光Y),然则,子查询被“max”函数限定,而子查询中的被询问列a2上设有独一索引,所以能够应用索引求最值,所以MySQL支持“<ANY”式的子查询优化,子查询只被实行三遍就能够求得最大值。

生机勃勃、子查询 1、where型子查询 (把内层查询结果作为外层查询的可比基准) #不用order by 来查询...

在南部内容中    car  和  nation   都代表 表名

<?php 
 /** 
  * “抽奖”函数 
  * 
  * @param integer $first    起始编号 
  * @param integer $last     结束编号 
  * @param integer $total    获奖人数 
  * 
  * @return string 
  * 
 */ 
 function isWinner($first, $last, $total) 
 { 
     $winner = array(); 
     for ($i=0;;$i++) 
     { 
         $number = mt_rand($first, $last); 
         if (!in_array($number, $winner)) 
             $winner[] = $number;    // 如果数组中没有该数,将其加入到数组 
         if (count($winner) == $total)   break; 
     } 
     return implode(' ', $winner); 
 } 
 // for test 
 echo isWinner(1, 100, 5); 
 ?>

 

1.无论 高端查询依然轻便询问   都用  select.. from..语句   from  后边加表名  能够使一张表也得以是多张表   表和表之间用逗号隔离

2. 简便询问和高端查询 不是 独立的   高等查询里面 相通能够用到 轻松询问  

3.简易询问与复杂查询的牵连: 简单询问里面 前面包车型大巴规格 未知时 须要用另一个 查询来代表  那样就改为了高等查询

4.链接查询  和  联合查询的界别:1. 链接查询 连接两张以上的表  输出时 突显在一张表里 ; 联合查询 以另一张表为条件  只输出 一张表的内容 

                                               联合查询时 注意 相关询问  : 子查询  和父查询 的关系  父查询  的规格 时子查询  子查询的规格相仿引用子查询

                                                2.生机勃勃致点 关联在合作期表示必需树立外键关系;

 

黄金时代.链接查询

1.链接查询 对结果集列的扩展   

select*from  表名

查询多张表 查询结果 在一张表中呈现

select * from info,nation  #多变笛Carl积   缺点 查询速度慢(爆发大批量冗余数据)

select * from 表1名, 表1名where 表1名.列名=表2名.列名

select * from info,nation where info.code=nation.code

 

 

select info.code, info. name, birthday  from info,nation where info.code=nation.code

因为 birthday 在两张表里  未有重新  所以能够平昔写  (比如简单询问里面)

 

select * from info join nation on info.nation=nation.code

 

 

二.一同查询   union

select 列名列名 from 表1名

union

select 列名列名 from 表2名

三.  子查询(查询功能高 首要)

父查询 : 外层查询  

子查询: 里层查询

子查询 查询出的结果作为父查询的标准

 

  1. 毫无干系子查询  

子查询和父查询未有涉嫌  子查询能够独立执行

父查询:select *from info where nation=()

子查询: select code from nation where name=’汉族’

select *from info where nation=(select code from nation where name=’汉族’)

②.询问种类名字为‘Camry’的装有小车消息
select * from car where brand=(select brand_code from brand where brand_name='宝马5系')

 

  1. 相关子查询

子查询在进行的时候 和父查询有关联   子查询不能够独立试行

1.询问汽车表中 等速油耗小于平均等速油耗的具备小车音信

父查询: 小车的新闻: select * from car where oil<平均道路循环油耗

子查询: 平均百公里油耗;  select avg(oilState of Qatar from car where brand =该连串

 select * from car as a where oil<(select avg(oil) from car as b where b.brand=a.brand)

 

本文由威尼斯在线注册平台发布于热门新闻,转载请注明出处:(把内层的询问结果供外层再次查询卡塔尔(قطر‎,轻易询问里面 后边的尺度 未知时

上一篇:威尼斯登录首页还记得上初中的时候经常下午放学就躲在路边扎金花来赌,简单洗牌算法 下一篇:没有了
猜你喜欢
热门排行
精彩图文