学习.png
谓词
什么是谓词
谓词就是返回值为真值的函数。对于通常的函数来说，返回值有可能是数字、字符串和日期等，但是谓词的返回值全部是真值。这也是谓词和函数的最大区别。
谓词主要有以下几种：
-
LIKE
-
BETWEEN
-
IS NULL、IS NOT NULL
-
IN
-
EXISTS
LIKE谓词—字符串的部分一致查询
截止目前，我们使用字符串作为查询条件的例子使用的都是=。这里的=只有在字符串完全一致时才为真。与之相反，LIKE谓词更加模糊一些，当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。接下来就让我们看一看具体示例吧。
首先，我们先创建一张用作示例的表：
--创建SampleLike表
CREATE TABLE SampleLike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY(strcol));
向表中插入数据
--插入数据
BEGIN TRANSACTION;BEGIN
INSERT INTO SampleLike VALUES ('abcddd');INSERT 0 1
INSERT INTO SampleLike VALUES ('dddabc');INSERT 0 1
INSERT INTO SampleLike VALUES ('abdddc');INSERT 0 1
INSERT INTO SampleLike VALUES ('abcdd');INSERT 0 1
INSERT INTO SampleLike VALUES ('ddabc');INSERT 0 1
INSERT INTO SampleLike VALUES ('abddc');INSERT 0 1
COMMIT;COMMIT
确认一下我们创建的表的内容：
SELECT * FROM SampleLike;
执行结果：
strcol--------
abcddd
dddabc
abdddc
abcdd
ddabc
abddc
(6 行记录)
前方一致查询
使用Like进行前方一致查询
SELECT * FROM SampleLike
WHERE strcol LIKE 'ddd%';
执行结果：
strcol--------
dddabc
(1 行记录)
其中的%代表“0字符以上的任意字符”的特殊符号，上例表示“以ddd开头的所有字符”。
中间一致查询
使用LIKE进行中间一致查询
SELECT * FROM SampleLike
WHERE strcol LIKE '%ddd%';
执行结果：
strcol--------
abcddd
dddabc
abdddc
(3 行记录)
在字符串的起始和结束位置加上%，就能取出“包含ddd的字符串”。
后方一致查询
使用LIKE进行后方一致查询
SELECT * FROM SampleLike
WHERE strcol LIKE '%ddd';
执行结果：
strcol--------
abcddd
(1 行记录)
此外，我们还可以使用_（下划线）来代替%，与%不同的是，它代表“任意一个字符”，下面我们就来尝试一下：
--使用LIKE和_（下划线）进行后方一致查询
SELECT * FROM SampleLike
WHERE strcol LIKE 'abc__';
执行结果：
strcol--------
abcdd
(1 行记录)
再举个例子：
--查询'abc+任意3个字符'的字符串
SELECT * FROM SampleLike
WHERE strcol LIKE 'abc___';
执行结果：
strcol--------
abcddd
(1 行记录)
BETWEEN谓词—范围查询
使用BETWEEN可以进行范围查询。该谓词与其他谓词或者函数不同的是它使用了3个参数。
--获取销售单价为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 行记录)
BETWEEN的特点就是结果会包含100和1000这两个临界值。如果不想让结果包含临界值，那就必须使用<和>。
--选取出销售单价为101~999元的商品
SELECT product_name, sale_price
FROM Product WHERE sale_price > 100
AND sale_price < 1000;
执行结果：
product_name | sale_price--------------+------------
打孔器 | 500
叉子 | 500
擦菜板 | 880
(3 行记录)
IS NULL、IS NOT NULL—判断是否为NULL
为了选取某些值为NULL的列的数据，不能使用=，而只能使用特定的谓词IS NULL。
--选取出进货单价为NULL的商品
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IS NULL;
执行结果：
product_name | purchase_price--------------+----------------
叉子 |
圆珠笔 |
(2 行记录)
与之相反，如果选取NULL以外的数据，需要使用谓词IS NOT NULL。
--选取出进货单价不为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 行记录)
IN谓词—OR的简便用法
通过OR指定多个进货单价进行查询：
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 行记录)
我们使用IN 谓词来替换上述SQL语句：
--通过IN来指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price IN (320, 500, 5000);
执行结果：
product_name | purchase_price--------------+----------------
T衫 | 500
打孔器 | 320
高压锅 | 5000
(3 行记录)
反之，否定形式可以使用NOT IN来实现：
--使用NOT IN 进行查询时指多个排除的进货单价进行查询
SELECT product_name, purchase_price
FROM Product
WHERE purchase_price NOT IN (320, 500, 5000);
执行结果：
product_name | purchase_price--------------+----------------
运动T衫 | 2800
菜刀 | 2800
擦菜板 | 790
(3 行记录)
注释：
使用IN 和NOT IN 时是无法取出NULL数据的，NULL终究是需要使用IS NULL和IS NOT NULL来进行判断。
使用子查询作为IN谓词的参数
IN谓词和子查询
IN谓词（NOT IN谓词）具有其他谓词所没有的用法，那就是可以使用子查询来作为其参数。子查询在之前已经学过，就是SQL内部生成的表。
为了掌握更详尽的使用方法，我们再创建一张新表：
--创建ShopProduct(商店商品)表的CREATE TABLE语句
CREATE TABLE ShopProduct
( shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantit INTEGER NOT NULL,
PRIMARY KEY(shop_id, product_id));
向表ShopProduct中插入数据
--向表ShopProduct中插入数据的INSERT语句
BEGIN TRANSACTION;BEGIN
INSERT INTO ShopProduct VALUES('000A', '成华区', '0001', 30);INSERT 0 1
INSERT INTO ShopProduct VALUES('000A', '成华区', '0002', 50);INSERT 0 1
INSERT INTO ShopProduct VALUES('000A', '成华区', '0003', 15);INSERT 0 1
INSERT INTO ShopProduct VALUES('000B', '金牛区', '0002', 30);INSERT 0 1
INSERT INTO ShopProduct VALUES('000B', '金牛区', '0003', 120);INSERT 0 1
INSERT INTO ShopProduct VALUES('000B', '金牛区', '0004', 20);INSERT 0 1
INSERT INTO ShopProduct VALUES('000B', '金牛区', '0006', 10);INSERT 0 1
INSERT INTO ShopProduct VALUES('000B', '金牛区', '0007', 40);INSERT 0 1
INSERT INTO ShopProduct VALUES('000C', '武侯区', '0003', 20);INSERT 0 1
INSERT INTO ShopProduct VALUES('000C', '武侯区', '0004', 50);INSERT 0 1
INSERT INTO ShopProduct VALUES('000C', '武侯区', '0006', 90);INSERT 0 1
INSERT INTO ShopProduct VALUES('000C', '武侯区', '0007', 70);INSERT 0 1
INSERT INTO ShopProduct VALUES('000D', '锦江区', '0001', 100);INSERT 0 1
COMMIT;COMMIT
确认创建的表的内容：
SELECT * FROM ShopProduct;
执行结果：
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
(13 行记录)
使用子查询作为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 行记录)
如果在SELECT语句中使用了子查询，那么即使数据发生了变更，还可以继续使用同样的SELECT语句。像这样能够应对数据变更的程序，称为‘易维护程序’。
NOT IN 和子查询
使用子查询作为NOT IN 的参数：
SELECT product_name, sale_price
FROM Product
WHERE product_id NOT IN (SELECT product_id
FROM ShopProduct
WHERE shop_id = '000A');
执行结果：
product_name | sale_price--------------+------------
菜刀 | 3000
高压锅 | 6800
叉子 | 500
擦菜板 | 880
圆珠笔 | 100
(5 行记录)
EXISTS谓词
EXISTS谓词的使用方法
一言以蔽之，谓词的作用就是“判断是否存在某种满足条件的记录”。如果存在这样的记录就返回真（TRUE），如果不存在这样的记录就返回假（FALSE）。EXISTS（存在）谓词的主语是“记录”。
--使用EXISTS选取出“武侯区在售商品的销售单价”
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 行记录)
注释：
-
通常指定关联子查询作为EXISTS的参数。
-
作为EXISTS参数的子查询中通常使用SELECT *。
使用NOT EXISTS替换NOT IN
就像EXISTS可以替换IN一样，NOT IN 也可以用NOT EXISTS 来替换。
--使用NOT EXISTS 读取出“成华区店在售之外的商品的销售单价”
SELECT product_name, sale_price
FROM Product AS P
WHERE NOT EXISTS (SELECT *
FROM ShopProduct AS SP
WHERE SP.shop_id = '000A'
AND SP.product_id = p.product_id);
执行结果：
product_name | sale_price--------------+------------
菜刀 | 3000
高压锅 | 6800
叉子 | 500
擦菜板 | 880
圆珠笔 | 100
(5 行记录)
CASE表达式
什么是CASE表达式
CASE表达式是一种进行运算的功能，它是SQL中最重要的功能之一。CASE表达式是在区分情况下使用，这种情况的区分在编程中通常叫做条件（分支）。类似于C语言中的if……else….语句。
CASE表达式的语法
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。但是搜索CASE表达式包含了简单CASE表达式的全部功能，所以我们学习搜索CASE表达式的语法就可以了。
--搜索CASE表达式
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.....
ELSE <表达式>
END
CASE表达式会从最初的WHEN子句中的“ <求值表达式> ”进行求值运算。所谓求值，就是要调查该表达式的真值是什么，如果结果为真（TRUE），那么就返回THEN子句中的表达式，CASE表达式的执行到此为止。如果结果不为真，那么就跳转到下一条的WHEN子句的求值之中。如果知道最后的WHEN子句为止返回结果都不为真，那么就会返回ELSE中的表达式，执行结束。
CASE表达式的使用方法
咱们用一个例子说明：
--通过CASE表达式将A~C的字符串加入到商品种类中
SELECT product_name, CASE WHEN product_type = '衣服'
THEN 'A:' || product_type
WHEN product_type = '办公用品'
THEN 'B:' || product_type
WHEN product_type = '厨房用具'
THEN 'C:' || product_type
ELSE NULL
END AS abs_product_type
FROM Product;
执行结果：
product_name | abs_product_type--------------+------------------
T衫 | A:衣服
打孔器 | B:办公用品
运动T衫 | A:衣服
菜刀 | C:厨房用具
高压锅 | C:厨房用具
叉子 | C:厨房用具
擦菜板 | C:厨房用具
圆珠笔 | B:办公用品
(8 行记录)
注释：
-
虽然CASE表达式中的ELSE子句可以省略，但是最好不要省略。
-
CASE表达式中的END不能省略。
CASE表达式可以实现行列互换
使用GRUOP BY无法实现行列转换：
SELECT product_type, SUM(sale_price) AS sum_price
FROM Product
GROUP BY product_type;
执行结果：
product_type | sum_price--------------+-----------
衣服 | 5000
办公用品 | 600
厨房用具 | 11180
(3 行记录)
但是使用CASE表达式可以实现行列转换
--对照商品种类计算出的销售单价合计值进行行列转换
SELECT SUM(CASE WHEN product_type = '衣服'
THEN sale_price ELSE 0 END) AS sum_price_clothes, SUM(CASE WHEN product_type = '厨房用具'
THEN sale_price ELSE 0 END) AS sum_price_kitchen, SUM(CASE WHEN product_type = '办公用品'
THEN sale_price ELSE 0 END) AS sum_price_office
FROM Product;
执行结果：
sum_price_clothes | sum_price_kitchen | sum_price_office-------------------+-------------------+------------------
5000 | 11180 | 600
(1 行记录)
今天的学习到此结束。加油！
每天学习一点点，每天进步一点点。
网友评论