MySQL小白入门03 - 复杂一点的查询

最新推荐文章于 2024-08-25 22:15:08 发布

落木~

最新推荐文章于 2024-08-25 22:15:08 发布

阅读量201

点赞数

分类专栏： MySQL 文章标签： mysql sql

本文链接：https://blog.csdn.net/a786150017/article/details/111464931

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

1. 视图

从SQL的角度来说操作视图与操作表看起来是完全相同的，那么为什么需要视图呢？

什么是视图？

视图是虚拟的表，是依据SELECT语句创建的
操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在虚拟表上做SQL操作

视图与表的区别 （是否保存了实际的数据？）

视图是基于真实表的一张虚拟表，数据来源建立在真实表的基础上
视图不是数据库真实存储的数据表，可以看作是一个窗口，通过该窗口能看到数据库表中真实存在的数据

=> 视图不是表，视图是虚表，视图依赖于表

为什么需要视图？

可保存频繁使用的SELECT语句，提高效率
让用户看到的数据更清晰
可以不对外公开数据表全部字段，增强数据的保密性
可以降低数据的冗余

视图不仅可基于真实表，也可以在视图的基础上继续创建视图
（但建议尽量避免该操作，因为多重视图会降低 SQL 的性能 - 对多数 DBMS 来说）

1.1 创建视图 CREATE

视图名称：在数据库中唯一，不能与其他视图和表重名
视图列名：在视图名称之后的列表中定义；和SELECT语句中列的排列顺序相同

CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>

在一般的DBMS中定义视图时不能使用ORDER BY语句，因为视图和表一样，数据行都是没有顺序的。
在 MySQL中视图的定义是允许使用ORDER BY语句的

1）基于单表的视图
在product表的基础上创建一个视图

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, count(*)
FROM product
GROUP BY product_type;

在这里插入图片描述
2）基于多表的视图
在product表和shop_product表的基础上创建视图

CREATE VIEW view_shop_product (product_type, sale_price, shop_name)
AS
SELECT product_type, sale_price, shop_name
FROM product,
	shop_product
WHERE product.product_id = shop_product.product_id;

在这里插入图片描述
3）在视图的基础查询

SELECT sale_price, shop_name
FROM view_shop_product
WHERE product_type = "衣服";

在这里插入图片描述

1.2 修改视图的结构 ALTER

ALTER VIEW <视图名> AS <SELECT语句>

举个例子

ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
        FROM Product
         WHERE regist_date > '2009-09-11';

1.3 更新视图的内容 UPDATE

视图是虚拟表，所以对视图的操作就是对底层基础表的操作
因为若视图发生了改变，而原表没有进行相应更新，就无法保证数据的一致性了

若视图包含以下任一结构，则不能被更新：
聚合函数 SUM()、MIN()、MAX()、COUNT() 等；DISTINCT 关键字；GROUP BY 子句；HAVING 子句；UNION 或 UNION ALL 运算符；FROM 子句中包含多个表

上述productSum视图不包括以上限制条件，所以以此为例：

UPDATE productsum
   SET sale_price = '5000'
 WHERE product_type = '办公用品';

再查看productSum视图，可以发现数据已更新
观察原表也会发现数据也被更新了

但是原表的数据只有一条做了修改？

因为视图只是原表的一个窗口，所以它的修改只能修改透过窗口能看到的内容。
（虽然修改成功了，但是并不推荐该方式 - 尽量使用限制不允许通过视图来修改表）

1.4 删除视图 DROP

需要有相应的权限才能成功删除

DROP VIEW <视图名1> [ , <视图名2> …]

2. 子查询

SELECT stu_name
FROM (
         SELECT stu_name, COUNT(*) AS stu_cnt
          FROM students_info
          GROUP BY stu_age) AS studentSum;

什么是子查询？

子查询指一个查询语句嵌套在另一个查询语句内部的查询

SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件
查询可以基于一个表或者多个表

子查询和视图的关系？

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句中
（子查询是一次性的，所以在 SELECT 语句执行后就消失了）

2.1 嵌套子查询

随着子查询嵌套的层数的叠加，SQL语句会难以理解且执行效率也会很差，所以要尽量避免这样的使用

SELECT product_type, cnt_product
FROM (SELECT *
        FROM (SELECT product_type, 
                      COUNT(*) AS cnt_product
                FROM product 
               GROUP BY product_type) AS productsum
       WHERE cnt_product = 4) AS productsum2;

最内层的子查询将其命名为productSum，该语句根据product_type分组并查询个数
第二层查询中将个数为4的商品查询出来
最外层查询product_type和cnt_product两列

2.2 标量子查询（单一的子查询）

单一要求要执行的SQL语句只能返回一个值，即要返回表中具体的某一行的某一列

举个例子

product_id | product_name | sale_price 
------------+-------------+----------
0003       | 运动T恤       | 4000 
0004       | 菜刀          | 3000 
0005       | 高压锅        | 6800

那么执行一次标量子查询后是要返回类似于，“0004”，“菜刀”这样的结果

标量子查询的应用

查询出销售单价高于平均销售单价的商品

SELECT product_id, product_name, sale_price
  FROM product
 WHERE sale_price > (SELECT AVG(sale_price) FROM product);

查询包括sale_price的均价

SELECT product_id,
       product_name,
       sale_price,
       (SELECT AVG(sale_price)
          FROM product) AS avg_price
  FROM product;

2.3 关联子查询

通过一些标志将内外两层的查询连接起来起到过滤数据的目的

例子：选取出各商品种类中高于该商品种类的平均销售单价的商品

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price > (SELECT AVG(sale_price)
  FROM product AS p2
  WHERE p1.product_type = p2.product_type
  GROUP BY product_type);

关联子查询的执行过程

首先执行不带WHERE的主查询
根据主查询讯结果匹配product_type，获取子查询结果
将子查询结果再与主查询结合执行完整的SQL语句

练习题（第一部分）

3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。
对该视图执行 SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

执行结果

product_name | sale_price | regist_date
--------------+------------+------------
T恤衫         | 　 1000    | 2009-09-20
菜刀          |    3000    | 2009-09-20

答：

CREATE VIEW ViewPractice5_1 (product_name, sale_price, regist_date)
AS
SELECT product_name, sale_price, regist_date
FROM product
WHERE sale_price >= 1000 and regist_date = "2009-09-20";

3.2 向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？

INSERT INTO ViewPractice5_1 VALUES (' 刀子 ', 300, '2009-11-02');

答：
报错 - 因为本行会尝试向原始数据表插入数据，数据列不完整
Error Code: 1423. Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value

3.3 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       | 2097.5000000000000000
0002       | 打孔器        | 办公用品      | 500        | 2097.5000000000000000
0003       | 运动T恤       | 衣服          | 4000      | 2097.5000000000000000
0004       | 菜刀          | 厨房用具      | 3000       | 2097.5000000000000000
0005       | 高压锅        | 厨房用具      | 6800       | 2097.5000000000000000
0006       | 叉子          | 厨房用具      | 500        | 2097.5000000000000000
0007       | 擦菜板        | 厨房用具       | 880       | 2097.5000000000000000
0008       | 圆珠笔        | 办公用品       | 100       | 2097.5000000000000000

答：

SELECT  product_id, 
		product_name, 
		product_type, 
		sale_price, 
		(SELECT AVG(sale_price) FROM product) AS sale_price_all
FROM product;

3.4 请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）

product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001       | T恤衫         | 衣服         | 1000       |2500.0000000000000000
0002       | 打孔器         | 办公用品     | 500        | 300.0000000000000000
0003       | 运动T恤        | 衣服        | 4000        |2500.0000000000000000
0004       | 菜刀          | 厨房用具      | 3000        |2795.0000000000000000
0005       | 高压锅         | 厨房用具     | 6800        |2795.0000000000000000
0006       | 叉子          | 厨房用具      | 500         |2795.0000000000000000
0007       | 擦菜板         | 厨房用具     | 880         |2795.0000000000000000
0008       | 圆珠笔         | 办公用品     | 100         | 300.0000000000000000

提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询询进行创建。问题就是应该在什么地方使用这个关联子查询。

答：

CREATE VIEW AvgPriceByType
(
	product_id,
	product_name,
	product_type,
	sale_price,
	avg_sale_price
)
AS
SELECT
	product_id,
	product_name,
	product_type,
	sale_price,
	(SELECT AVG(sale_price) FROM product AS p2
	WHERE p1.product_type = p2.product_type
	GROUP BY product_type) AS avg_sale_price
FROM product AS p1;

3. 各种各样的函数

函数总个数超过200个，不需要完全记住，查阅文档即可；常用函数有 30~50 个

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）
日期函数（用来进行日期操作的函数）
转换函数（用来转换数据类型和值的函数）
聚合函数（用来进行数据聚合的函数）

3.1 算数函数

+ - * /四则运算

举个例子 - 构造samplemath表

-- 创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(
    m FLOAT(10, 3),
    n INT,
    p INT
);

-- 插入数据
START TRANSACTION;
INSERT INTO samplemath(m, n, p) VALUES (500, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (-180, 0, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, NULL, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 7, 3);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 5, 2);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 4, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8, NULL, 3);
INSERT INTO samplemath(m, n, p) VALUES (2.27, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (5.555,2, NULL);
INSERT INTO samplemath(m, n, p) VALUES (NULL, 1, NULL);
INSERT INTO samplemath(m, n, p) VALUES (8.76, NULL, NULL);
-- 提交事务
COMMIT;

-- 查询
SELECT * FROM samplemath;

ABS – 绝对值

语法：ABS( 数值 )
当 ABS 函数的参数为NULL时，返回值也是NULL。

MOD – 求余数

语法：MOD( 被除数，除数 )
小数没有余数的概念，只能对整数列求余数。
主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。

ROUND – 四舍五入

语法：ROUND( 对象数值，保留小数的位数 )-
-参数保留小数的位数为变量时，可能会遇到错误，请谨慎使用变量

SELECT
	m,
	ABS(m) AS abs_col,
	n, p,
	MOD(n, p) AS mod_col,
	ROUND(m, 1) AS round_col
FROM samplemath;

3.2 字符串函数

举个例子 - 构造samplestr表

-- D创建表
USE shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
(
	str1 VARCHAR (40),
	str2 VARCHAR (40),
	str3 VARCHAR (40)
);

-- 插入数据
START TRANSACTION;
INSERT INTO samplestr (str1, str2, str3) VALUES ('opx',	'rt', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc', 'def', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('太阳',	'月亮', '火星');
INSERT INTO samplestr (str1, str2, str3) VALUES ('aaa',	NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES (NULL, 'xyz', NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('@!#$%', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('ABC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('aBC', NULL, NULL);
INSERT INTO samplestr (str1, str2, str3) VALUES ('abc哈哈',  'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('abcdefabc', 'abc', 'ABC');
INSERT INTO samplestr (str1, str2, str3) VALUES ('micmic', 'i', 'I');
COMMIT;

-- 确认表中的内容
SELECT * FROM samplestr;

CONCAT – 拼接

语法：CONCAT(str1, str2, str3)

LENGTH – 字符串长度

语法：LENGTH( 字符串 )

LOWER – 小写转换

只能针对英文字母使用，不适用于英文字母以外的场合
UPPER 函数用于大写转换

REPLACE – 字符串的替换

语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )

SUBSTRING – 字符串的截取

语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
截取的起始位置从字符串最左侧开始计算，索引值起始为1。

SELECT
	str1,
	str2,
	str3,
    CONCAT(str1, str2, str3) AS str_concat,
	LENGTH(str1) AS len_str,
	LOWER(str1) AS low_str,
	REPLACE(str1, str2, str3) AS rep_str,
	SUBSTRING(str1 FROM 3 FOR 2) AS sub_str
FROM samplestr;

扩展内容

SUBSTRING_INDEX – 字符串按索引截取

语法：SUBSTRING_INDEX (原始字符串，分隔符，n)
获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支持正向和反向索引，索引起始值分别为 1 和 -1。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2);
	+------------------------------------------+
	| SUBSTRING_INDEX('www.mysql.com', '.', 2) |
	+------------------------------------------+
	| www.mysql                                |
	+------------------------------------------+

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
	+-------------------------------------------+
	| SUBSTRING_INDEX('www.mysql.com', '.', -2) |
	+-------------------------------------------+
	| mysql.com                                 |
	+-------------------------------------------+

获取第2个元素/第n个元素可以采用二次拆分的写法。

SELECT SUBSTRING_INDEX('www.mysql.com', '.', 1);
	+------------------------------------------+
	| SUBSTRING_INDEX('www.mysql.com', '.', 1) |
	+------------------------------------------+
	| www                                      |
	+------------------------------------------+
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
	+--------------------------------------------------------------------+
	| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
	+--------------------------------------------------------------------+
	| mysql                                                              |
	+--------------------------------------------------------------------+

REPEAT – 字符串按需重复多次

语法：REPEAT(string, number)
对特定字符实现按需重复

SELECT REPEAT('加油！',3);
	+-----------------------------+
	| REPEAT('加油！',3)          |
	+-----------------------------+
	| 加油！加油！加油！          |
	+-----------------------------+

3.3 日期函数

不同DBMS的日期函数语法各有不同，这里介绍一些被标准 SQL 承认的可以应用于绝大多数 DBMS 的函数。特定DBMS的日期函数查阅文档即可。

CURRENT_DATE – 获取当前日期

SELECT CURRENT_DATE;
	+--------------+
	| CURRENT_DATE |
	+--------------+
	| 2020-08-08   |
	+--------------+

CURRENT_TIME – 当前时间

SELECT CURRENT_TIME;
	+--------------+
	| CURRENT_TIME |
	+--------------+
	| 17:26:09     |
	+--------------+

CURRENT_TIMESTAMP – 当前日期和时间

SELECT CURRENT_TIMESTAMP;
	+---------------------+
	| CURRENT_TIMESTAMP   |
	+---------------------+
	| 2020-08-08 17:27:07 |
	+---------------------+

EXTRACT – 截取日期元素

语法：EXTRACT(日期元素 FROM 日期)
截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等
该函数的返回值不是日期类型，而是数值类型

SELECT CURRENT_TIMESTAMP as now,
EXTRACT(YEAR   FROM CURRENT_TIMESTAMP) AS year,
EXTRACT(MONTH  FROM CURRENT_TIMESTAMP) AS month,
EXTRACT(DAY    FROM CURRENT_TIMESTAMP) AS day,
EXTRACT(HOUR   FROM CURRENT_TIMESTAMP) AS hour,
EXTRACT(MINUTE FROM CURRENT_TIMESTAMP) AS MINute,
EXTRACT(SECOND FROM CURRENT_TIMESTAMP) AS second;
	+---------------------+------+-------+------+------+--------+--------+
	| now                 | year | month | day  | hour | MINute | second |
	+---------------------+------+-------+------+------+--------+--------+
	| 2020-08-08 17:34:38 | 2020 |     8 |    8 |   17 |     34 |     38 |
	+---------------------+------+-------+------+------+--------+--------+

3.4 转换函数

类型转换和值转换
CAST – 类型转换

语法：CAST（转换前的值 AS 想要转换的数据类型）

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
	+---------+
	| int_col |
	+---------+
	|       1 |
	+---------+
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;
	+------------+
	| date_col   |
	+------------+
	| 2009-12-14 |
	+------------+

COALESCE – 将NULL转换为其他值

语法：COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数，会返回可变参数中左侧开始第 1个不是NULL的值
参数个数可变，因此可根据需要无限增加

在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。

SELECT COALESCE(NULL, 11) AS col_1,
COALESCE(NULL, 'hello world', NULL) AS col_2,
COALESCE(NULL, NULL, '2020-11-01') AS col_3;
	+-------+-------------+------------+
	| col_1 | col_2       | col_3      |
	+-------+-------------+------------+
	|    11 | hello world | 2020-11-01 |
	+-------+-------------+------------+

4. 谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN
主要有以下几个：LIKE、BETWEEN、IS NULL、IS NOT NULL、IN、EXISTS

4.1 LIKE谓词（字符串的部分一致查询）

部分一致大体可以分为前方一致、中间一致和后方一致三种类型
首先我们来创建一张表

-- 创建表
CREATE TABLE samplelike
(
	strcol VARCHAR(6) NOT NULL,
	PRIMARY KEY (strcol)
);

-- 插入数据
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 |
	+--------+

1）前方一致：选取出“dddabc”

前方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串起始部分相同。

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';
	+--------+
	| strcol |
	+--------+
	| dddabc |
	+--------+

%代表“零个或多个任意字符串”的特殊符号

2）中间一致：选取出“abcddd”“dddabc”“abdddc”

中间一致即查询对象字符串中含有作为查询条件的字符串，无论该字符串出现在对象字符串的最后还是中间

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';
	+--------+
	| strcol |
	+--------+
	| abcddd |
	| abdddc |
	| dddabc |
	+--------+

3）后方一致：选取出“abcddd“

后方一致即作为查询条件的字符串（这里是“ddd”）与查询对象字符串的末尾部分相同。

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';
	+--------+
	| strcol |
	+--------+
	| abcddd |
	+--------+

查询条件最宽松，也就是能够取得最多记录的是中间一致，因为它同时包含前方一致和后方一致的查询结果。

_下划线匹配任意 1 个字符
_（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';
	+--------+
	| strcol |
	+--------+
	| abcdd  |
	+--------+

4.2 BETWEEN谓词（范围查询）

BETWEEN使用了 3 个参数

BETWEEN 的结果中会包含临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >

-- 选取销售单价为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 |
	+--------------+------------+

SELECT product_name, sale_price
FROM product
WHERE sale_price > 100
AND sale_price < 1000;

4.3 IS NULL、 IS NOT NULL

选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;
	+--------------+----------------+
	| product_name | purchase_price |
	+--------------+----------------+
	| 叉子         |           NULL |
	| 圆珠笔       |           NULL |
	+--------------+----------------+

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NOT NULL;

4.4 IN谓词（OR的简便用法）

多个查询条件取并集时可以选择使用or语句
避免SQL 语句也会越来越长，用IN 谓词IN(值1, 值2, 值3, ......)替换OR语句
OR语句

使用IN 和 NOT IN 时是无法选取出NULL数据的，NULL 只能使用 IS NULL 和 IS NOT NULL

-- 通过OR指定多个进货单价进行查询
SELECT product_name, purchase_price
FROM product
WHERE purchase_price = 320
OR purchase_price = 500
OR purchase_price = 5000;

IN谓词语句

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)

NOT IN

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (320, 500, 5000);

4.5 使用子查询作为IN谓词的参数

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，它可以使用子查询作为其参数 - 能够将表、视图作为 IN 的参数

举个例子 - 创建一张新表shopproduct显示出哪些商店销售哪些商品

-- 创建表
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)
);

-- 插入数据
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;

1. 取出大阪在售商品的销售单价

取出大阪门店的在售商品 `product_id ;
取出大阪门店在售商品的销售单价 `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 |
	+--------------+------------+

子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句

-- 子查询展开后的结果
SELECT product_name, sale_price
FROM product
WHERE product_id IN ('0003', '0004', '0006', '0007');

=> 子查询转换之后变为 in 谓词用法

使用子查询即可保持 sql 语句不变，提高了程序的可维护性 - 系统开发

2. 例子 - 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');

4.6 EXIST 谓词

判断是否存在满足某种条件的记录

可以使用 IN（或者 NOT IN）来代替
EXIST（存在）谓词的主语是“记录”。
EXIST 是只有 1 个参数的谓词

1）使用 EXIST 选取出大阪门店在售商品的销售单价

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);

使用下面的查询语句，查询结果也不会发生变化

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);

可以把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯。

2）取出不在大阪门店销售的商品的销售单价

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);

5. CASE 表达式

包括简单CASE表达式和搜索CASE表达式
搜索CASE表达式包含简单CASE表达式的全部功能，这里主要介绍搜索CASE

表达式

CASE WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     WHEN <求值表达式> THEN <表达式>
     .
     .
     .
ELSE <表达式>
END

依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。
无论多么庞大的 CASE 表达式，最后也只会返回一个值。

5.1 使用方法

假设现在要实现：

A ：衣服
B ：办公用品
C ：厨房用具

因为表中的记录并不包含“A ： ”或者“B ： ”这样的字符串，所以需要在 SQL 中进行添加。并将“A ： ”“B ： ”“C ： ”与记录结合起来

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;

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL
此外， CASE 表达式最后的“END”不能省略

2. 实现列方向上的聚合

实现行的方向上不同种类的聚合（这里是 sum）

SELECT product_type,
       SUM(sale_price) AS sum_price
  FROM product
 GROUP BY product_type;

假如要在列的方向上展示不同种类额聚合值

sum_price_clothes | sum_price_kitchen | sum_price_office
------------------+-------------------+-----------------
             5000 |             11180 |              600

聚合函数 + CASE WHEN 表达式即可实现该效果

-- 对按照商品种类计算出的销售单价合计值进行行列转换
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 |
	+-------------------+-------------------+------------------+

3. 实现行转列

假设有如下图表的结构

计划得到如下的图表结构

聚合函数 + CASE WHEN 表达式即可实现该转换

当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数；
当待转换列为文本时，可以使用MAX MIN等聚合函数

实现数字列 score 的行转列

-- 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;

实现文本列 subject 的行转列

-- 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 |
	+------+---------+------+---------+
	| 张三 | 语文    | 数学 | 外语    |
	| 李四 | 语文    | 数学 | 外语    |
	+------+---------+------+---------+

练习题（第二部分）

5. 运算或者函数中含有 NULL 时，结果全都会变为NULL ？（判断题）

答：错。比如：COALESCE – 会将NULL转换为其他值

6. 对本章中使用的 product（商品）表执行如下 2 条 SELECT 语句，能够得到什么样的结果呢？

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000);

SELECT product_name, purchase_price
  FROM product
 WHERE purchase_price NOT IN (500, 2800, 5000, NULL);

答：
都无法选取出NULL数据，因为NULL只能用IS NULL / IS NOT NULL
？？第二条语句结果为空，用 IN NULL也是返回结果为空

7. 按照销售单价（ sale_price）对练习 3.6 中的 product（商品）表中的商品进行如下分类。

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）

请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

执行结果

low_price | mid_price | high_price
----------+-----------+------------
        5 |         1 |         2

答：

SELECT  COUNT(CASE WHEN sale_price <= 1000 THEN 1 ELSE NULL END) AS low_price,
		COUNT(CASE WHEN sale_price > 1001 and sale_price <= 3000 THEN 1 ELSE NULL END) AS mid_price,
		COUNT(CASE WHEN sale_price > 3001 THEN 1 ELSE NULL END) AS high_price
FROM product;