「这是我参与2022首次更文挑战的第16天，活动详情查看：2022首次更文挑战」
4.5谓词
谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
谓词主要有以下几个：
4.5.1LIKE，字符串部分一致性查询
对字符串的一部分进行一致性查询时使用。
_下划线匹配任意 1 个字符%是代表“零个或多个任意字符串”的特殊符号
根据%放的位置，分为前方一致，中间一致和后方一致3种类型。
-- DDL ：创建表
CREATE TABLE samplelike
( strcol VARCHAR(6) NOT NULL,
PRIMARY KEY (strcol)
samplelike);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
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');
COMMIT; -- 提交事务
SELECT * FROM samplelike;
+--------+
| strcol |
+--------+
| abcdd |
| abcddd |
| abddc |
| abdddc |
| ddabc |
| dddabc |
+--------+
6 rows in set (0.00 sec)
-- 前方一致：选取出“dddabc”
SELECT * FROM samplelike
WHERE strcol LIKE 'ddd%';
+--------+
| strcol |
+--------+
| dddabc |
+--------+
1 row in set (0.00 sec)
-- 中间一致：选取出“abcddd”“dddabc”“abdddc”
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
+--------+
| strcol |
+--------+
| abcddd |
| abdddc |
| dddabc |
+--------+
3 rows in set (0.00 sec)
-- 后方一致：选取出“abcddd“
SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
+--------+
| strcol |
+--------+
| abcddd |
+--------+
1 row in set (0.00 sec)
--_下划线匹配任意 1 个字符
SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';
+--------+
| strcol |
+--------+
| abcdd |
+--------+
1 row in set (0.00 sec)
4.5.2BETWEEN，用于范围查询
范围查询，需要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 rows in set (0.00 sec)
-- 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)
4.5.3IS NULL、IS NOT NULL， 判断是否为null
不能使用=来获得是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)
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)
4.5.4IN:or的简便用法
也有not in，但不能选出null。
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| T恤 | 500 |
| 打孔器 | 320 |
| 高压锅 | 5000 |
+--------------+----------------+
3 rows in set (0.00 sec)
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);
+--------------+----------------+
| product_name | purchase_price |
+--------------+----------------+
| 运动T恤 | 2800 |
| 菜刀 | 2800 |
| 擦菜板 | 790 |
+--------------+----------------+
3 rows in set (0.00 sec)
作为子查询的参数
-- DDL ：创建表
DROP TABLE IF EXISTS shopproduct;
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) -- 指定主键
);
-- DML ：插入数据
START TRANSACTION; -- 开始事务
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);
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 rows in set (0.00 sec)
-- 取出大阪在售商品的销售单价
-- step1：取出大阪门店的在售商品 `product_id`
SELECT product_id
FROM shopproduct
WHERE shop_id = '000C';
+------------+
| product_id |
+------------+
| 0003 |
| 0004 |
| 0006 |
| 0007 |
+------------+
4 rows in set (0.00 sec)
-- step2：取出大阪门店在售商品的销售单价 `sale_price`
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)
-- 子查询展开后的结果
SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
4 rows in set (0.00 sec)
-- 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 rows in set (0.00 sec)
4.5.5EXISTS：判断是否存在满足条件的记录
谓词的作用就是 “判断是否存在满足某种条件的记录” 。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替
EXIST（存在）谓词的主语是“记录”。
只有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);
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 运动T恤 | 4000 |
| 菜刀 | 3000 |
| 叉子 | 500 |
| 擦菜板 | 880 |
+--------------+------------+
4 rows in set (0.00 sec)
用not exist替换not in
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 rows in set (0.00 sec)
4.6case表达式
条件分支。
语法分为简单CASE表达式和搜索CASE表达式两种。
搜索CASE表达式包含简单CASE表达式的全部功能。
依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。
else可以省略，会认为是else null，但end不能省略。
行转列：
- 当待转换列为数字时，可以使用
SUM AVG MAX MIN等聚合函数； - 当待转换列为文本时，可以使用
MAX MIN等聚合函数
CASE WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
WHEN <求值表达式> THEN <表达式>
.
.
.
ELSE <表达式>
END
-- **应用场景1：根据不同分支得到不同列值**
SELECT product_name,
CASE WHEN product_type = '衣服' THEN CONCAT('A ： ',product_type)
WHEN product_type = '办公用品' THEN CONCAT('B ： ',product_type)
WHEN product_type = '厨房用具' THEN CONCAT('C ： ',product_type)
ELSE NULL
END AS abc_product_type
FROM product;
+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤 | A ： 衣服 |
| 打孔器 | B ： 办公用品 |
| 运动T恤 | A ： 衣服 |
| 菜刀 | C ： 厨房用具 |
| 高压锅 | C ： 厨房用具 |
| 叉子 | C ： 厨房用具 |
| 擦菜板 | C ： 厨房用具 |
| 圆珠笔 | B ： 办公用品 |
+--------------+------------------+
8 rows in set (0.00 sec)
-- **应用场景2：实现列方向上的聚合**
SELECT product_type,
SUM(sale_price) AS sum_price
FROM product
GROUP BY product_type;
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服 | 5000 |
| 办公用品 | 600 |
| 厨房用具 | 11180 |
+--------------+-----------+
3 rows in set (0.00 sec)
-- 在列的方向上展示不同种类额聚合值
sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
5000 | 11180 | 600
-- 对按照商品种类计算出的销售单价合计值进行行列转换
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 row in set (0.00 sec)
-- CASE WHEN 实现数字列 score 行转列
SELECT name,
SUM(CASE WHEN subject = '语文' THEN score ELSE null END) as chinese,
SUM(CASE WHEN subject = '数学' THEN score ELSE null END) as math,
SUM(CASE WHEN subject = '外语' THEN score ELSE null END) as english
FROM score
GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 93 | 88 | 91 |
| 李四 | 87 | 90 | 77 |
+------+---------+------+---------+
2 rows in set (0.00 sec)
-- CASE WHEN 实现文本列 subject 行转列
SELECT name,
MAX(CASE WHEN subject = '语文' THEN subject ELSE null END) as chinese,
MAX(CASE WHEN subject = '数学' THEN subject ELSE null END) as math,
MIN(CASE WHEN subject = '外语' THEN subject ELSE null END) as english
FROM score
GROUP BY name;
+------+---------+------+---------+
| name | chinese | math | english |
+------+---------+------+---------+
| 张三 | 语文 | 数学 | 外语 |
| 李四 | 语文 | 数学 | 外语 |
+------+---------+------+---------+
2 rows in set (0.00 sec
4.7练习题
4.7.1
运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）
4.7.2
对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？
-- 1
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000);
-- 2
SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500, 2800, 5000, NULL);
-1的输出结果：
2的输出结果：
4.7.3
按照销售单价（ sale_price）对练习 3.6 中的 product（商品）表中的商品进行如下分类。
- 低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、 圆珠笔）
- 中档商品：销售单价在1001日元以上3000日元以下（菜刀）
- 高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。
执行结果
low_price | mid_price | high_price
----------+-----------+------------
5 | 1 | 2
