mysql 实例子_mysql子查询的实例总结

本节内容：

mysql子查询

一、有 = <> > < >= <= 的子查询

格式：

SELECT col1 , col2 , col3

FROM tb

WHERE col = [ANY | SOME | ALL](

SELECT coln FROM tb WHERE ...

)

例子：

复制代码 代码示例:

SELECT `ID`, `classname`

FROM `ydfzx_software_class`

WHERE `fatherid` = ANY(

SELECT `id`

FROM `ydfzx_software_class`

WHERE `fatherid`=13

)

子查询返回一个离散值(一列一行)，如果有ANY或ALL的修饰，可以返回一个离散值列表(一列多行) ,其实 col = ANY 就相当于col IN。

二、IN 子查询

格式：

SELECT col1 , col2 , col3

FROM tb

WHERE col [NOT] IN(

SELECT coln FROM tb WHERE ...

)

在有IN谓词的这个子查询中，返回一个离散值列表(一列多行)。

三、SELECT ROW 字查询，注意返回值是0或1

格式：

SELECT ROW(value1,value2,value3 ...) = [ANY | SOME] SELECT col1,col2,col3

如果第2条查询必须返回一组离散值(多列单行)，如果有ANY和SOME谓词的修饰，返回一组离散值列表

例子：

复制代码 代码示例:

SELECT ROW(1,'操作系统'，1) = ANY (SELECT `ID`,`caption`,`status` FROM `ydfzx_software_class`)

在`ydfzx_software_class`里查找有没有`ID`=1，`caption`='操作系统',`status`=1的记录，有就返回1，没有就返回NULL 或 0。

四、EXISTS 子查询

格式:

SELECT col1,col2 FROM a WHERE EXISTS(SELECT ...)

解释:

主查询先查询出数据,再逐条通过EXISTS子查询验证,只有子查询返回的记录数不为0时,主查询中的记录有效。

这个查询开销比较大。

例子：

users表

┏━━━┯━━━┯━━━━┓

┃ uid │ name │address ┃

┃ 1 │张荣 │中光 ┃

┃ 2 │晨曦 │fuyang ┃

┃ 3 │川湘 │中光 ┃

┃ 4 │张荣 │浙江 ┃

┗━━━┷━━━┷━━━━┛

xl表

┏━━━┯━━━┯━━━━┓

┃ uid │ xl │year  ┃

┃ 1 │大专 │2007  ┃

┃ 1 │大本 │2008  ┃

┃ 1 │硕士 │2009  ┃

┃ 2 │小学 │2012  ┃

┃ 2 │初中 │2018  ┃

┃ 2 │高中 │2021  ┃

┃ 2 │大本 │2025  ┃

┃ 4 │大本 │2025  ┃

┃ 5 │大本 │2025  ┃

┃ 3 │大专 │1995  ┃

┃ 3 │大本 │2001  ┃

┗━━━┷━━━┷━━━━┛

例子：找出users表中那些在xl表中有xl=小学的记录，他们的共同键为id

复制代码 代码示例:

SELECT *

FROM `users`

WHERE EXISTS (

SELECT *

FROM `xl`

WHERE `xl`.`uid` = `users`.`uid` AND xl = '小学'

)

等效于:

复制代码 代码示例:

SELECT `users`. *

FROM `users` , `xl`

WHERE `xl`.`xl` = '小学' AND `users`.`uid` = `xl`.`uid`

结果为:

┏━━━┯━━━┯━━━━┓

┃ uid │name │address ┃

┃ 2 │晨曦 │fuyang ┃

┗━━━┷━━━┷━━━━┛

五、衍生数据表子查询

格式： SELECT ... FROM (SELECT ...) AS name WHERE 。

首先执行子查询生成临时表，外层查询再到此临时表中获取数据。

例子：

复制代码 代码示例:

SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount

FROM `users` , `xl`

WHERE `users`.`uid` = `xl`.`uid`

GROUP BY `users`.`uid`

ORDER BY `lcount` DESC

等效于：

复制代码 代码示例:

SELECT *

FROM(

SELECT `users`.`uid` , `users`.`name` , count( `xl`.`uid` ) AS lcount

FROM `users` , `xl`

WHERE `users`.`uid` = `xl`.`uid`

GROUP BY `users`.`uid`

) AS `temp`

ORDER BY `lcount` DESC

实用例子：

有一存储树形目录的表tb, 共有3个字段，ID，classname,fatherid,每个节点一条记录.ID字段为非重复字段，classname为此节点的名称,每个子节点的fatherid等于其父节点的ID，如果fatherid为0，表示它为顶层节点，如何能一次就查询出各顶层节点有没有子节点呢，经过调试，以下语句执行成功。

复制代码 代码示例:

SELECT `ID` , `classname` , `fatherid` ,

IF(

(SELECT COUNT(`ID` )

FROM `tb`

WHERE `fatherid` = `main`.`ID`

) >0, 1, 0) AS `haveson`

FROM `tb` AS `main`

WHERE `fatherid` =0

2、一次性查询出某节点的兄弟节点

复制代码 代码示例:

SELECT *

FROM `tb`

WHERE `fatherid`=(SELECT `fatherid` FROM `tb` WHERE `ID`=6)

3、一次性查询出某节点的父辈节点

复制代码 代码示例:

SELECT *

FROM `tb`

WHERE `fatherid`=(

SELECT `fatherid`

FROM `tb`

WHERE `ID`=(SELECT `fatherid` FROM `tb` WHERE `ID`=13)

)

您可能感兴趣的文章：

mysql多表子查询与左连接的代码

mysql子查询使用EXISTS命令的例子

mysql子查询中EXISTS用法的例子

mysql子查询的五种方式

mysql 子查询与join性能对比

mysql子查询的用法举例

mysql update实现子查询的实例分享

mysql in 子查询效率慢的优化方法详解