mysql子查询的实例总结

发布时间:2019-11-02编辑:脚本学堂
本文介绍下,在mysql中实现子查询的一些方法,分享一些mysql子查询的例子,供大家学习参考下。

本节内容:
mysql子查询.html target=_blank class=infotextkey>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 子查询效率慢的优化方法详解