谓词就是返回值为真值的函数。对于通常的函数来说,返回值有可能是数字、字符串和日期等,但是谓词的返回值全部是真值。这也是谓词和函数的最大区别。
谓词主要有以下几种:
截止目前,我们使用字符串作为查询条件的例子使用的都是=。这里的=只有在字符串完全一致时才为真。与之相反,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可以进行范围查询。该谓词与其他谓词或者函数不同的是它使用了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 行记录)
为了选取某些值为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 行记录)
通过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 W HERE 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谓词(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 的参数:
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 行记录)
一言以蔽之,谓词的作用就是“判断是否存在某种满足条件的记录”。如果存在这样的记录就返回真(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可以替换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表达式是一种进行运算的功能,它是SQL中最重要的功能之一。CASE表达式是在区分情况下使用,这种情况的区分在编程中通常叫做条件(分支)。类似于C语言中的if……else….语句。
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表达式将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 行记录)
注释:
使用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 行记录)
今天的学习到此结束。加油!