学习SQL【8】-谓词和CASE表达式

96
爱吃西瓜的番茄酱
2017.12.09 20:11 字数 1632
学习.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 行记录)

今天的学习到此结束。加油!

每天学习一点点,每天进步一点点。

学习Python
Web note ad 1