从 01 开始
2023-11-28
目录

谓词

# 170.谓词

通俗来讲谓词就是函数中的一种。对通常的函数来说，返回值有可能是数字、字符串或者日期等，但是谓词的返回值全都是真值（TRUE/FALSE/UNKNOWN ），这也是谓词和函数的最大区别。

‍ ‍

本节将会介绍以下谓词：

  • LIKE
  • BETWEEN
  • IS NULL 、IS NOT NULL
  • IN
  • EXISTS ‍

# LIKE 谓词：字符串的部分一致查询

之前我们使用字符串作为查询条件时，用的符号都是等于号 =，只有在字符串完全一致时才认为是相等，

然而，有时候我们需要的是部分的一致，例如找出“姓张的人”，也就是名字这个字符串中，以“张”开头的字符串，也就是第一个字符一致，后面几个字符无所谓。

部分一致可以大致分为三种：前方一致，中间一致，后方一致。

如果读者学过一点正则，可以理解为 like 就是一个正则匹配。 ‍ 接下来我们演示下，首先来创建一张测试用的表：

-- DDL ：创建表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
  PRIMARY KEY (strcol)
);

-- DML ：插入数据
INSERT INTO SampleLike (strcol) VALUES ('abcddd');
INSERT INTO SampleLike (strcol) VALUES ('dddabc');
INSERT INTO SampleLike (strcol) VALUES ('abdddc');
INSERT INTO SampleLike (strcol) VALUES ('abcdd');
INSERT INTO SampleLike (strcol) VALUES ('ddabc');
INSERT INTO SampleLike (strcol) VALUES ('abddc');
1
2
3
4
5
6
7
8
9
10
11
12
13

‍ 假设我们要找出包含字符串“ddd ”的记录。 ‍

# 前方一致查询

前方一致：就是选取出作为查询条件的字符串，与查询对象字符串起始部分相同的记录的查询方法。这里选取出“dddabc ”

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'ddd%';

-- 结果：

+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (3.30 sec)
1
2
3
4
5
6
7
8
9
10
11
12

说明： % 是代表“0 字符以上的任意字符串”的特殊符号，本例中代表“以 ddd 开头的所有字符串”。

这样我们就可以使用 LIKE 和模式匹配来进行查询了。 ‍

# 中间一致查询

中间一致：所谓中间一致 ，就是选取出查询对象字符串中，含有作为查询条件的字符串。无论该字符串是出现在前方，还是后方，中间。

这里的结果是选取出“abcddd ”“dddabc ”“abdddc ”。

SELECT *
  FROM SampleLike
 WHERE strcol LIKE '%ddd%';

-- 结果：
+--------+
| strcol |
+--------+
| abcddd |
| abdddc |
| dddabc |
+--------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

在查询字符串的起始和结束位置加上 % ，就能取出“包含 ddd 的字符串”了。 ‍

# 后方一致查询

后方一致：与前方一致相反，会选取出作为查询条件的字符串，与查询对象字符串的末尾部分相同的记录的查询方法。

这里选取出“abcddd ”：

SELECT *
  FROM SampleLike
 WHERE strcol LIKE '%ddd';

-- 结果：
+--------+
| strcol |
+--------+
| abcddd |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

大家可以看到上述结果与前方一致正好相反。

# 下划线_

此外，我们还可以使用 _ （下划线）来代替 % ，与 % 不同的是，它代表了“任意 1 个字符”。

例如，要找出 strcol 列的值为“abc + 任意 2 个字符”的记录：

SELECT *
  FROM SampleLike
 WHERE strcol LIKE 'abc__';

-- 结果：
+--------+
| strcol |
+--------+
| abcdd  |
+--------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11

虽然“abcddd ”也是以“abc ”开头的字符串，但是其中“ddd ”是 3 个字符，所以不满足 __ 所指定的 2 个字符的条件，因此该字符串并不在查询结果之中。 ‍

# BETWEEN 谓词——范围查询

使用 BETWEEN 可以进行范围查询 。

该谓词与其他谓词或者函数的不同之处在于它使用了 3 个参数。例如，从 product 表中读取出 sale_price 为 100 日元到 1000 日元之间的商品：

SELECT product_name, sale_price
  FROM Product
 WHERE sale_price BETWEEN 100 AND 1000;

-- 结果：
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| T恤          |       1000 |
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
| 圆珠笔       |        100 |
+--------------+------------+
5 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

‍ BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值。如果不想让结果中包含临界值，那就必须使用 < 和 > ：

SELECT product_name, sale_price
  FROM Product
 WHERE sale_price > 100
   AND sale_price < 1000;

-- 结果：
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 打孔器       |        500 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

执行结果中不再包含 1000 日元和 100 日元的记录。 ‍

# IS NULL 、IS NOT NULL：判断是否为 NULL

为了选取出某些值为 NULL 的列的数据，不能使用 = ，而只能使用特定的谓词 IS NULL ：

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;

-- 结果：
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 叉子         |           NULL |
| 圆珠笔       |           NULL |
+--------------+----------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13

‍ 与此相反，想要选取 NULL 以外的数据时，需要使用 IS NOT NULL ：

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NOT NULL;


-- 结果：
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 运动T恤      |           2800 |
| 菜刀         |           2800 |
| 高压锅       |           5000 |
| 擦菜板       |            790 |
+--------------+----------------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

# IN 谓词——OR 的简便用法

接下来让我们思考一下如何选取出 purchase_price 分别为 320 日元、500 日元、5000 日元的商品。这里使用之前学过的 OR 的 SQL 语句：

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price =  320
    OR purchase_price =  500
    OR purchase_price = 5000;

-- 结果：
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤          |            500 |
| 打孔器       |            320 |
| 高压锅       |           5000 |
+--------------+----------------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

‍ 虽然上述方法没有问题，但还是存在一点不足之处，那就是随着希望选取的对象越来越多，SQL 语句也会越来越长，阅读起来也会越来越困难。这时，我们就可以使用代码清单 6-32 中的 IN 谓词 “IN( 值,……) ”来替换上述 SQL 语句：

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IN (320, 500, 5000);
1
2
3

反之，希望选取出“进货单价不是 320 日元、500 日元、5000 日元”的商品时，可以使用否定形式 NOT IN 来实现：

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price NOT IN (320, 500, 5000);
1
2
3

但需要注意的是，在使用 IN 和 NOT IN 时是无法选取出 NULL 数据的 。NULL 终究还是需要使用 IS NULL 和 IS NOT NULL 来进行判断。

# IN 和子查询

IN 谓词具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。之前我们学过了子查询，子查询就是 SQL 内部生成的表，因此也可以说“能够将表作为 IN 的参数”。视图也可以看成表，因此也能将视图作为 IN 的参数。

为了掌握详细的使用方法，再添加一张新表 hopProduct （商店商品），显示出哪些商店销售哪些商品：

shop_id shop_name product_id quantity
（商店） （商店名称） （商品编号） （数量）
000A 东京 0001 30
000A 东京 0002 50
000A 东京 0003 15
000B 名古屋 0002 30
000B 名古屋 0003 120
000B 名古屋 0004 20
000B 名古屋 0006 10
000B 名古屋 0007 40
000C 大阪 0003 20
000C 大阪 0004 50
000C 大阪 0006 90
000C 大阪 0007 70
000D 福冈 0001 100

‍ 相关 SQL：

CREATE TABLE ShopProduct
(shop_id    CHAR(4)      NOT NULL,
 shop_name  VARCHAR(200) NOT NULL,
 product_id CHAR(4)      NOT NULL,
 quantity   INTEGER      NOT NULL,
 PRIMARY KEY (shop_id, product_id));

INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO ShopProduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

‍ 这里用了两列作为主键，这样就能确定每一行的数据。接下来，我们要读取“大阪店（000C ）在售商品（product_id ）的销售单价（sale_price ）”。

如果是之前，我们的先找出大阪店在售的商品编号：

SELECT product_id
  FROM ShopProduct
 WHERE shop_id = '000C';

-- 结果：
+------------+
| product_id |
+------------+
| 0003       |
| 0004       |
| 0006       |
| 0007       |
+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

再根据这些商品编号，去 Product 表读取单价：

SELECT product_name, sale_price
  FROM Product
 WHERE product_id IN ('0003', '0004', '0006', '0007');
1
2
3

但这样写有缺点：一个商店贩卖的商品不是一成不变的，如果使用固定的 where 条件 product_id IN ('0003', '0004', '0006', '0007');​，那么每当商品有变化（例如加多了一个商品，减少了一个商品），就得修改 where 条件，那么修改工作就没完没了。 ‍ 如果结合 in 和子查询，就直接一步到位：

-- 取得“在大阪店销售的商品的销售单价”
SELECT product_name, sale_price
  FROM Product
 WHERE product_id IN (SELECT product_id
                         FROM ShopProduct
                        WHERE shop_id = '000C');

-- 结果：
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

‍ 之前说过，子查询是从内层开始执行的。因此，该 SELECT 语句也是从内层的子查询开始执行，然后像下面这样展开：

-- 子查询展开后的结果
SELECT product_name, sale_price
  FROM Product
 WHERE product_id IN ('0003', '0004', '0006', '0007');
1
2
3
4

这样就转换成了之前我们学习过的 IN 的使用方法。

由于我们结合了 in 和子查询，即使数据发生了变更，SQL 也不用修改。这类 SQL 几乎不用修改，易于维护

同理，我们也可以结合 NOT IN 和子查询。 ‍

# EXISTS 谓词

最后要介绍的是 EXISTS（存在） 谓词 。将它放到最后进行学习的原因有以下 3 点：

  1. EXISTS 的使用方法与之前的都不相同
  2. 语法理解起来比较困难
  3. 实际上即使不使用 EXISTS ，基本上也都可以使用 IN （或者 NOT IN ）来代替

理由 ① 和 ② 都说明 EXISTS 是使用方法特殊而难以理解的谓词。特别是使用否定形式 NOT EXISTS 的 SELECT 语句，即使是 DB 工程师也常常无法迅速理解（可读性较差）。

此外，如理由 ③ 所述，使用 IN 作为替代的情况非常多（尽管不能完全替代让人有些伤脑筋），很多读者虽然记住了使用方法但还是不能实际运用。

但是一旦能够熟练使用 EXISTS 谓词，就能体会到它极大的便利性。因此，非常希望大家能够在达到 SQL 中级水平时掌握此工具。本书只简单介绍其基本使用方法 。 ‍

# EXIST 谓词的使用方法

一言以蔽之，谓词的作用就是“判断是否存在满足某种条件的记录”。如果存在这样的记录就返回真（TRUE ），如果不存在就返回假（FALSE ）。

我们继续使用前一节“IN 和子查询”中的示例，使用 EXISTS 选取出“大阪店（000C ）在售商品（product_id ）的销售单价（sale_price ）”：

SELECT product_name, sale_price
  FROM Product AS P -----------------------①
 WHERE EXISTS (SELECT *
                  FROM ShopProduct AS SP --②
                 WHERE SP.shop_id = '000C'
                   AND SP.product_id = P.product_id);


-- 结果：
-- 结果：
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤      |       4000 |
| 菜刀         |       3000 |
| 叉子         |        500 |
| 擦菜板       |        880 |
+--------------+------------+
4 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

‍ 如果子查询能查询到结果，那么就说明“存在”记录，因此会返回 true，然后 where 条件就会成立，然后再通过 select 来查询数据。

强调的是：是否返回结果集，而不需要知道返回什么 ‍

# EXISTS 的参数

之前学过的谓词，比如 like 和 between ，需要指定 2 个及以上的参数；

EXIST 是只有 1 个参数的谓词，该参数通常都会是一个子查询：

(SELECT *
   FROM ShopProduct AS SP
  WHERE SP.shop_id = '000C'
    AND SP.product_id = P.product_id)
1
2
3
4

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“SP.product_id = P.product_id ”将 Product 表和 ShopProduct 表进行了联接，因此作为参数的是关联子查询。

EXISTS 通常都会使用关联子查询作为参数 。虽然严格来说语法上也可以使用非关联子查询作为参数，但实际应用中几乎没有这样的情况。 ‍

# EXISTS 的执行过程

  • 先在外层查询中取 Product 表的第一行记录，将该记录传给子查询，然后若子程序内有结果，也就是“EXISTS”结果，因此子查询返回 true；
  • 然后外层查询得到结果后（为 true），那么 where 条件就是成立了，因此第一行记录是符合条件的，是要表内 selete 的，这条记录会被放入结果表中。
  • 然后再取 Product 表的下一行记录；重复上述过程直到外层表的记录全部遍历一次为止。

有的博客 (opens new window)是这样说的：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则 EXISTS 子句返回 TRUE，这一行行可作为外查询的结果行，否则不能作为结果。

因此，IN 适合于外表大而内表小的情况；EXISTS 适合于外表小而内表大的情况。

# 子查询中的 SELECT *

由于 EXISTS 只关心记录是否存在，返回哪些列都没有关系，子查询内写成 SELECT *​，SELECT 1 ​都是可以的：

SELECT product_name, sale_price
  FROM Product AS P 
 WHERE EXISTS (SELECT 1 -- 这里可以书写适当的常数
                  FROM ShopProduct AS SP 
                 WHERE SP.shop_id = '000C'
                   AND SP.product_id = P.product_id);
1
2
3
4
5
6

不过，一般习惯是写成 SELECT *​。 ‍ EXISTS 也可以和 not 谓词 结合使用

# EXISTS 与 CREATE、DROP

在表的创建里我们学过，一个数据库中不允许同名表，假设数据库中已经有了 Product 表，那么再次创建会报错。

我们可以结合会学习 EXIST 与 CREATE，在创建前先判断是否有同名表：

CREATE TABLE IF NOT EXISTS Product
(
    product_id      CHAR(4)      NOT NULL,
    product_name    VARCHAR(100) NOT NULL,
    product_type    VARCHAR(32)  NOT NULL,
    sale_price      INTEGER,
    purchase_price  INTEGER,
    regist_date     DATE,
    PRIMARY KEY (product_id)
);
1
2
3
4
5
6
7
8
9
10

‍ 这样如果数据库中存在该表，则不会创建；不存在则创建。

创建数据库同理：

create database if not exists db_test; --当db_test不存在时，执行创建
1

删除也可以结合 EXISTS 来使用：

drop databse if exists db_test; -- 当db_test存在时，执行删除
1

‍ 以上用法通常用来初始化，例如想要重新创建一个数据库，不要之前的旧数据了：

drop databse if exists db_test;
create db_test;
use db_test;
--.....
1
2
3
4

‍ （完） ‍

上次更新: 2025/5/5 17:15:09
函数
CASE 表达式

← CASE 表达式→

最近更新
01
语雀文档一键下载至本地教程
07-04
02
要成功，就不要低估环境对你的影响
07-03
03
血泪教训：电子设备要定期开机
07-02
更多文章>
Theme by Vdoing | Copyright © 2022-2025 | 粤 ICP 备 2022067627 号 -1 | 粤公网安备 44011302003646 号 | 点击查看十年之约