首页 > 编程笔记 > MySQL笔记 阅读:111

MySQL子查询详解(附带实例)

子查询指一个查询语句嵌套在另一个查询语句内部的查询,这个特性从 MySQL 4.1 开始引入。

在 SELECT 子句中先计算子查询,子查询结果作为外层另一个查询的过滤条件,查询可以基于一张表或者多张表。

子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中,而且可以进行多层嵌套。子查询中也可以使用比较运算符,如“<”、“<=”、“>”、“>=”和“!=”等。

本节将介绍如何在 SELECT 语句中嵌套子查询。

为了演示的需要,需要创建以下几张表:
1) 创建数据表 fruits,SQL 语句如下:
CREATE TABLE fruits
(
f_id    char(10)      NOT NULL,
s_id    INT           NOT NULL,
f_name  char(255)     NOT NULL,
f_price decimal(8,2)  NOT NULL,
PRIMARY KEY(f_id)
);
插入如下数据:
mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price)
       VALUES('a1', 101,'apple',5.2),
       ('b1',101,'blackberry', 10.2),
       ('bs1',102,'orange', 11.2),
       ('bs2',105,'melon',8.2),
       ('t1',102,'banana', 10.3),
       ('t2',102,'grape', 5.3),
       ('o2',103,'coconut', 9.2),
       ('c0',101,'cherry', 3.2),
       ('a2',103, 'apricot',2.2),
       ('l2',104,'lemon', 6.4),
       ('b2',104,'berry', 7.6),
       ('m1',106,'mango', 15.7),
       ('m2',105,'xbabay', 2.6),
       ('t4',107,'xbababa', 3.6),
       ('m3',105,'xxtt', 11.6),
       ('b5',107,'xxxx', 3.6);

2) 创建数据表 suppliers,SQL 语句如下:
CREATE TABLE suppliers
(
s_id      int      NOT NULL AUTO_INCREMENT,
s_name    char(50) NOT NULL,
s_city    char(50) NULL,
s_zip     char(10) NULL,
s_call    CHAR(50) NOT NULL,
PRIMARY KEY (s_id)
) ;
插入需要演示的数据,SQL 语句如下:
INSERT INTO suppliers(s_id, s_name,s_city,  s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

3) 创建表 orders,SQL 语句如下:
CREATE TABLE orders
(
o_num  int      NOT NULL AUTO_INCREMENT,
o_date datetime NOT NULL,
c_id   int      NOT NULL,
PRIMARY KEY (o_num)
) ;
插入数据,SQL 语句如下:
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

4) 创建数据表 orderitems,SQL 语句如下:
CREATE TABLE orderitems
(
o_num      int          NOT NULL,
o_item     int          NOT NULL,
f_id       char(10)     NOT NULL,
quantity   int          NOT NULL,
item_price decimal(8,2) NOT NULL,
PRIMARY KEY (o_num,o_item)
) ;
然后插入演示数据,SQL 语句如下:
INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)
VALUES(30001, 1, 'a1', 10, 5.2),
(30001, 2, 'b2', 3, 7.6),
(30001, 3, 'bs1', 5, 11.2),
(30001, 4, 'bs2', 15, 9.2),
(30002, 1, 'b3', 2, 20.0),
(30003, 1, 'c0', 100, 10),
(30004, 1, 'o2', 50, 2.50),
(30005, 1, 'c0', 5, 10),
(30005, 2, 'b1', 10, 8.99),
(30005, 3, 'a2', 10, 2.2),
(30005, 4, 'm1', 5, 14.99);

带ANY、SOME关键字的子查询

ANY 和 SOME 是同义词,表示满足其中任意一个条件。它们允许创建一个表达式对子查询的返回值列表进行比较,只要满足内层子查询中的任何一个比较条件,就返回一个结果作为外层查询的条件。

下面定义两张表 tbl1 和 tbl2:
CREATE table tbl1 ( num1 INT NOT NULL);
CREATE table tbl2 ( num2 INT NOT NULL);

分别向两张表中插入数据:
INSERT INTO tbl1 values(1), (5), (13), (27);
INSERT INTO tbl2 values(6), (14), (11), (20);

ANY 关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较的结果为 TRUE,则返回 TRUE。

【实例 1】返回表 tbl2 中的 num2 列的所有值,然后将表 tbl1 中的 num1 列的值与 num2 列的值进行比较,只要 num1 列中的值大于 num2 列中的任何一个值,即为符合查询条件的结果。
mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
+------+
| num1 |
+------+
|  13  |
|  27  |
+------+
在子查询中,返回的是表 tbl2 的 num2 列的值,即(6,14,11,20),然后将表 tbl1 中的 num1 列的值与(6,14,11,20)进行比较,只要 num1 列中的值大于(6,14,11,20)中的任意一个,即为符合条件的结果,因此返回的结果为(13,27)。

带ALL关键字的子查询

ALL 关键字与 ANY 和 SOME 不同,使用 ALL 时需要同时满足所有内层查询的条件。

ALL 关键字接在一个比较操作符的后面,表示与子查询返回的所有值进行比较,若结果为 TRUE,则返回 TRUE。

下面修改【实例 1】,用 ALL 关键字替换 ANY。

【实例 2】返回表 tbl1 中比表 tbl2 的 num2 列所有值都大的值,SQL 语句如下:
mysql> SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);
+------+
| num1 |
+------+
|  27  |
+------+
在子查询中,返回的是表 tbl2 的值,即 num2 列的所有(6,14,11,20),然后将表 tbl1 中的 num1 列的值与之进行比较,大于所有 num2 列值的 num1 值只有 27,因此返回结果为 27。

带EXISTS关键字的子查询

EXISTS 关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么 EXISTS 返回的结果为 TRUE,此时外层查询语句将进行查询;如果子查询没有返回任何行,那么 EXISTS 返回的结果是 FALSE,此时外层查询语句将不进行查询。

【实例 3】查询表 suppliers 中是否存在 s_id=107 的水果供应商,如果存在,则查询表 fruits 中的记录,SQL 语句如下:
mysql> SELECT * FROM fruits WHERE EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
+--------+--------+------------+--------+
| f_id   | s_id   | f_name     | f_price|
+--------+--------+------------+--------+
| a1     | 101    | apple      | 5.20   |
| a2     | 103    | apricot    | 2.20   |
| b1     | 101    | blackberry | 10.20  |
| b2     | 104    | berry      | 7.60   |
| b5     | 107    | xxxx       | 3.60   |
| bs1    | 102    | orange     | 11.20  |
| bs2    | 105    | melon      | 8.20   |
| c0     | 101    | cherry     | 3.20   |
| l2     | 104    | lemon      | 6.40   |
| m1     | 106    | mango      | 15.70  |
| m2     | 105    | xbabay     | 2.60   |
| m3     | 105    | xxtt       | 11.60  |
| o2     | 103    | coconut    | 9.20   |
| t1     | 102    | banana     | 10.30  |
| t2     | 102    | grape      | 5.30   |
| t4     | 107    | xbababa    | 3.60   |
+--------+--------+------------+--------+
由结果可以看到,内层查询结果表明表 suppliers 中存在 s_id=107 的记录,因此 EXISTS 表达式返回 TRUE;外层查询语句接收 TRUE 之后对表 fruits 进行查询,返回所有的记录。

EXISTS 关键字可以和条件表达式一起使用。

【实例 4】查询表 suppliers 中是否存在 s_id=107 的水果供应商,如果存在,则查询表 fruits 中的 f_price>10.20 的记录,SQL 语句如下:
mysql> SELECT * FROM fruits WHERE f_price>10.20 AND EXISTS (SELECT s_name FROM suppliers WHERE s_id = 107);
+--------+--------+--------+--------+
| f_id   | s_id   | f_name | f_price|
+--------+--------+--------+--------+
| bs1    | 102    | orange | 11.20  |
| m1     | 106    | mango  | 15.70  |
| m3     | 105    | xxtt   | 11.60  |
| t1     | 102    | banana | 10.30  |
+--------+--------+--------+--------+
由结果可以看到,内层查询结果表明表 suppliers 中存在 s_id=107 的记录,因此 EXISTS 表达式返回 TRUE;外层查询语句接收 TRUE 之后根据查询条件 f_price > 10.20 对表 fruits 进行查询,返回结果为 4 条 f_price>10.20 的记录。

NOT EXISTS 与 EXISTS 的使用方法相同,返回的结果相反。子查询如果至少返回一行,那么 NOT EXISTS 返回的结果为 FALSE,此时外层查询语句将不进行查询;如果子查询没有返回任何行,那么 NOT EXISTS 返回的结果是 TRUE,此时外层查询语句将进行查询。

【实例 5】查询表 suppliers 中是否存在 s_id=107 的水果供应商,如果不存在,则查询表 fruits 中的记录,SQL 语句如下:
mysql> SELECT * FROM fruits WHERE NOT EXISTS  (SELECT s_name FROM suppliers WHERE s_id = 107);
Empty set (0.00 sec)
查询语句 SELECT s_name FROM suppliers WHERE s_id = 107 对表 suppliers 进行查询并返回了一条记录,因此 NOT EXISTS 表达式返回 FALSE,外层表达式接收 FALSE,将不再查询表 fruits 中的记录。

EXISTS 和 NOT EXISTS 的结果只取决于是否会返回行,而不取决于这些行的内容,所以这个子查询输入列表通常是无关紧要的。

带IN关键字的子查询

IN 关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作。

【实例 6】在表 orderitems 中查询 f_id 为 c0 的订单号,并根据订单号查询客户 c_id,SQL 语句如下:
mysql> SELECT c_id FROM orders WHERE o_num IN  (SELECT o_num  FROM orderitems WHERE f_id = 'c0');
+-------+
| c_id  |
+-------+
| 10004 |
| 10001 |
+-------+
查询结果中的 c_id 有两个值,分别为 10001 和 10004。

上述查询过程可以分步执行,首先内层子查询查出表 orderitems 中符合条件的订单号,单独执行内查询,查询结果如下:
mysql> SELECT o_num  FROM orderitems WHERE f_id = 'c0';
+-------+
| o_num |
+-------+
| 30003 |
| 30005 |
+-------+
然后执行外层查询,在表 orders 查询订单号等于 30003 或 30005 的客户 c_id。嵌套子查询语句还可以写为如下形式,实现相同的效果:
mysql> SELECT c_id FROM orders WHERE o_num IN (30003, 30005);
+-------+
| c_id  |
+-------+
| 10004 |
| 10001 |
+-------+
这个例子说明在处理 SELECT 语句的时候,MySQL 实际上执行了两个操作过程,即先执行内层子查询,再执行外层查询,内层子查询的结果作为外层查询的比较条件。

SELECT 语句中可以使用 NOT IN 关键字,其作用与IN正好相反。

【实例 7】:与【实例 6】类似,但是在 SELECT 语句中使用 NOT IN 关键字,SQL 语句如下:
mysql> SELECT c_id FROM orders WHERE o_num NOT IN (SELECT o_num  FROM orderitems WHERE f_id = 'c0');
+-------+
| c_id  |
+-------+
| 10001 |
| 10003 |
| 10005 |
+-------+
这里返回的结果中有 3 条记录。由【实例 6】可知,子查询返回的订单值有两个,即 30003 和 30005,但为什么这里还有值为 10001 的 c_id 呢?这是因为 c_id 等于 10001 的客户的订单不只一个,可以查看订单表 orders 中的记录。
mysql> SELECT * FROM orders;
+-------+---------------------+-------+
| o_num | o_date              | c_id  |
+-------+---------------------+-------+
| 30001 | 2008-09-01 00:00:00 | 10001 |
| 30002 | 2008-09-12 00:00:00 | 10003 |
| 30003 | 2008-09-30 00:00:00 | 10004 |
| 30004 | 2008-10-03 00:00:00 | 10005 |
| 30005 | 2008-10-08 00:00:00 | 10001 |
+-------+---------------------+-------+
可以看到,虽然排除了订单号为 30003 和 30005 的客户 c_id,但是 o_num 为 30001 和 30005 的订单都是 c_id 为 10001 的客户的订单。因此,结果中只是排除了订单号,但是仍然有可能选择同一个客户。

子查询的功能也可以通过连接查询完成,但是子查询使得 MySQL 代码更容易阅读和编写。

带比较运算符的子查询

在前面介绍带 ANY、ALL 关键字的子查询时使用了“>”比较运算符,子查询时还可以使用其他的比较运算符,如“<”“<=”“=”“>=”和“!=”等。

【实例 8】在表 suppliers 中查询 s_city 等于“Tianjin”的水果供应商 s_id,然后在表 fruits 中查询该供应商提供的所有的水果种类,SQL 语句如下:
SELECT s_id, f_name FROM fruits WHERE s_id =(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
该嵌套查询首先在表 suppliers 中查找 s_city 等于 Tianjin 的水果供应商的 s_id,单独执行子查询查看 s_id 的值:
mysql> SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin';
+------+
| s_id |
+------+
| 101  |
+------+

然后在外层查询时,在表 fruits 中查找 s_id 等于 101 的水果供应商提供的水果的种类,查询结果如下:
mysql> SELECT s_id, f_name FROM fruits WHERE s_id = (SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
+------+------------+
结果表明,“Tianjin”地区的水果供应商提供的水果种类有 3 种,分别为 apple、blackberry 与 cherry。

【实例 9】在表 suppliers 中查询 s_city 等于“Tianjin”的水果供应商的 s_id,然后在表 fruits 中查询所有非该供应商提供的水果的种类,SQL 语句如下:
mysql> SELECT s_id, f_name FROM fruits WHERE s_id <> (SELECT s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');
+--------+----------+
| s_id   | f_name   |
+--------+----------+
| 103    | apricot  |
| 104    | berry    |
| 107    | xxxx     |
| 102    | orange   |
| 105    | melon    |
| 104    | lemon    |
| 106    | mango    |
| 105    | xbabay   |
| 105    | xxtt     |
| 103    | coconut  |
| 102    | banana   |
| 102    | grape    |
| 107    | xbababa  |
+--------+----------+
该嵌套查询执行过程与【实例 8】相同,但这里使用了“<>”运算符,因此返回的结果和【实例 8】的正好相反。

相关文章