谓词
# 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');
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)
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)
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)
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)
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)
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)
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)
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)
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)
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);
2
3
反之,希望选取出“进货单价不是 320 日元、500 日元、5000 日元”的商品时,可以使用否定形式 NOT IN 来实现:
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);
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);
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)
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');
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)
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');
2
3
4
这样就转换成了之前我们学习过的 IN 的使用方法。
由于我们结合了 in 和子查询,即使数据发生了变更,SQL 也不用修改。这类 SQL 几乎不用修改,易于维护
同理,我们也可以结合 NOT IN 和子查询。
# EXISTS 谓词
最后要介绍的是 EXISTS(存在) 谓词 。将它放到最后进行学习的原因有以下 3 点:
- EXISTS 的使用方法与之前的都不相同
- 语法理解起来比较困难
- 实际上即使不使用 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)
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)
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);
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)
);
2
3
4
5
6
7
8
9
10
这样如果数据库中存在该表,则不会创建;不存在则创建。
创建数据库同理:
create database if not exists db_test; --当db_test不存在时,执行创建
删除也可以结合 EXISTS 来使用:
drop databse if exists db_test; -- 当db_test存在时,执行删除
以上用法通常用来初始化,例如想要重新创建一个数据库,不要之前的旧数据了:
drop databse if exists db_test;
create db_test;
use db_test;
--.....
2
3
4
(完)