SQL复杂一点的查询

最新推荐文章于 2022-10-19 22:50:34 发布

Mayumi_yumi

最新推荐文章于 2022-10-19 22:50:34 发布

阅读量191

点赞数

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

本文链接：https://blog.csdn.net/lc960928/article/details/111461217

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

复杂一点的查询

1. 视图
2. 子查询
3. 各种各样的函数
4. 谓词
CASE 表达式
- 5.1 什么是 CASE 表达式？
- CASE表达式的使⽤⽅法

1. 视图

SELECT stu_name FROM view_students_info;

1.2 什么是视图

视图是⼀个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下⾯具体介绍），所以操作视图时会根据创建视图的SELECT语句⽣成⼀张虚拟表，然后在这张虚拟表上做SQL操作。

1.3 视图与表有什么区别

区别在于“是否保存了实际的数据”。

所以视图并不是数据库真实存储的数据表，它可以看作是⼀个窗⼝，通过这个窗⼝我们可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的⼀张虚拟的表，其数据来源均建⽴在真实表的基础上。
在这里插入图片描述

“视图不是表，视图是虚表，视图依赖于表”。

1.4 为什么会存在视图

通过定义视图可以将频繁使⽤的SELECT语句保存以提⾼效率。
通过定义视图可以使⽤户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

1.5 如何创建视图

创建视图的基本语法如下：

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

SELECT 语句需要书写在 AS 关键字之后。
SELECT 语句中列的排列顺序和视图中列的排列顺序相同
，SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。
⽽且视图的列名是在视图名称之后的列表中定义的。

需要注意的是视图名在数据库中需要是唯⼀的，不能与其他视图和表重名。
视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。
在这里插入图片描述

虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL的性能。

注意事项：
需要注意的是在⼀般的DBMS中定义视图时不能使⽤ORDER BY语句。下⾯这样定义视图是错误的：
在这里插入图片描述
为什么不能使⽤ ORDER BY ⼦句呢？这是因为视图和表⼀样，数据⾏都是没有顺序的。

在 MySQL中视图的定义是允许使⽤ ORDER BY 语句的，但是若从特定视图进⾏选择，⽽该视图使⽤了⾃⼰的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。（这句着实看不懂）

1.5.1 基于单表的视图

我们在product表的基础上创建⼀个视图，如下：

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

在这里插入图片描述

1.5.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; -- 这个没法运行，没有shop_product表。

应该输出为这个：
在这里插入图片描述
我们可以在这个视图的基础上进⾏查询

1.6 如何修改视图结构

修改视图结构的基本语法如下：

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

其中视图名在数据库中需要是唯⼀的，不能与其他视图和表重名。
当然也可以通过将当前视图删除然后重新创建的⽅式达到修改的效果。（对于数据库底
层是不是也是这样操作的呢，你可以⾃⼰探索⼀下。）

修改视图

我们修改上⽅的productSum视图为

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

此时productSum视图内容如下图所示
在这里插入图片描述

1.7 如何更新视图内容

因为视图是⼀个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只
有满⾜底层基本表的定义才能成功修改。
对于⼀个视图来说，如果包含以下结构的任意⼀种都是不可以被更新的：

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
DISTINCT 关键字。
GROUP BY ⼦句。
HAVING ⼦句。
UNION 或 UNION ALL 运算符。
FROM ⼦句中包含多个表。

视图归根结底还是从表派⽣出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发⽣了改变，⽽原表没有进⾏相应更新的话，就⽆法保证数据的⼀致性了。

更新视图

因为我们刚刚修改的productSum视图不包括以上的限制条件，我们来尝试更新⼀下视图：
在这里插入图片描述

不知道⼤家看到这个结果会不会有疑问，刚才修改视图的时候是设置product_type='办公⽤品’的商品sale_price=5000，为什么原表的数据只有⼀条做了修改呢？还是因为视图的定义，视图只是原表的⼀个窗⼝，所以它修改也只能修改透过窗⼝能看到的内容。

注意：

这⾥虽然修改成功了，但是并不推荐这种使⽤⽅式。⽽且我们在创建视图时也尽量使⽤限制不允许通过视图来修改表

1.8 如何删除视图

删除视图的基本语法如下：

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

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

删除视图

我们删除刚才创建的productSum视图

DROP VIEW productSum;

如果我们继续操作这个视图的话就会提示当前操作的内容不存在。

2. 子查询

我们先来看⼀个语句：

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

这个语句看起来很好理解，其中使⽤括号括起来的sql语句⾸先执⾏，执⾏成功后再执⾏外⾯的sql语句。但是我们上⼀节提到的视图也是根据SELECT语句创建视图然后在这个基础上再进⾏查询。那么什么是⼦查询呢？⼦查询和视图⼜有什么关系呢？

2.1 什么是子查询

子查询就是将⽤来定义视图的 SELECT 语句直接⽤于 FROM ⼦句当中。其中AS studentSum可以看作是⼦查询的名称，⽽且由于⼦查询是⼀次性的，所以⼦查询不会像视图那样保存在存储介质中，⽽是在 SELECT 语句执⾏之后就消失了。

2.3 嵌套子查询

与在视图上再定义视图类似，⼦查询也没有具体的限制，例如我们可以这样

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两列

虽然嵌套⼦查询可以查询出结果，但是随着⼦查询嵌套的层数的叠加，SQL语句不仅会难以理解⽽且执⾏效率也会很差，所以要尽量避免这样的使⽤

2.4 标量⼦查询

标量就是单⼀的意思，那么标量⼦查询也就是单⼀的⼦查询，那为什么叫做单⼀的⼦查询呢？

所谓单⼀就是要求我们执⾏的SQL语句只能返回⼀个值，也就是要返回表中具体的某⼀⾏的某⼀列。例如我们有下⾯这样⼀张表：
在这里插入图片描述
那么我们执⾏⼀次标量⼦查询后是要返回类似于，“0004”，“菜⼑”这样的结果。

2.5 标量子查询有什么用

我们现在已经知道标量⼦查询可以返回⼀个值了，那么它有什么作⽤呢？直接这样想可能会有些困难，让我们看⼏个具体的需求：

查询出销售单价⾼于平均销售单价的商品
查询出注册⽇期最晚的那个商品

让我们看如何通过标量⼦查询语句查询出销售单价⾼于平均销售单价的商品

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

在这里插入图片描述
上⾯的这条语句⾸先后半部分查询出product表中的平均售价，前⾯的sql语句在根据WHERE条件挑选出合适的商品。

由于标量⼦查询的特性，导致标量⼦查询不仅仅局限于 WHERE ⼦句中，通常任何可以使⽤单⼀值的位置都可以使⽤。也就是说，能够使⽤常数或者列名的地⽅，⽆论是SELECT ⼦句、GROUP BY ⼦句、HAVING ⼦句，还是 ORDER BY ⼦句，⼏乎所有的地⽅都可以使⽤。

我们还可以这样使⽤标量⼦查询：

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

在这里插入图片描述

2.6 关联子查询

关联⼦查询既然包含关联两个字那么⼀定意味着查询与⼦查询之间存在着联系。这种联系是如何建⽴起来的呢？

我们先看⼀个例⼦：

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

在这里插入图片描述
通过上⾯的例⼦我们⼤概可以猜到吗，关联⼦查询就是通过⼀些标志将内外两层的查询连接起来起到过滤数据的⽬的，接下来我们就⼀起看⼀下关联⼦查询的具体内容吧。

2.6.1关联子查询与子查询的联系

还记得我们之前的那个例⼦么查询出销售单价高于平均销售单价的商品，这个例⼦的SQL语句如下：

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

我们再来看⼀下这个需求选取出各商品种类中高于该商品种类的平均销售单价的商品。SQL语句如下：

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

可以看出上⾯这两个语句的区别吗？（一个是输出商品，第二个是输出商品和种类，所以要把种类连接）
在第⼆条SQL语句也就是关联⼦查询中我们将外⾯的product表标记为p1，将内部的product设置为p2，⽽且通过WHERE语句连接了两个查询。

但是如果刚接触的话⼀定会⽐较疑惑关联查询的执⾏过程，这⾥有⼀个博客讲的⽐较清楚。在这⾥我们简要的概括为：

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

在⼦查询中像标量⼦查询，嵌套⼦查询或者关联⼦查询可以看作是⼦查询的⼀种操作⽅式即可。

⼩结

视图和⼦查询是数据库操作中较为基础的内容，对于⼀些复杂的查询需要使⽤⼦查询加⼀些条件语句组合才能得到正确的结果。但是⽆论如何对于⼀个SQL语句来说都不应该设计的层数⾮常深且特别复杂，不仅可读性差⽽且执⾏效率也难以保证，所以尽量有简洁的语句来完成需要的功能。

练习题-第⼀部分

3.1
创建出满⾜下述三个条件的视图（视图名称为 ViewPractice5_1）。使⽤ product（商品）表作为参照表，假设表中包含初始状态的 8 ⾏数据。
 条件 1：销售单价⼤于等于 1000 ⽇元。
 条件 2：登记⽇期是 2009 年 9 ⽉ 20 ⽇。
 条件 3：包含商品名称、销售单价和登记⽇期三列。
对该视图执⾏ SELECT 语句的结果如下所示。

SELECT * FROM ViewPractice5_1;

在这里插入图片描述

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

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

3.3
请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
在这里插入图片描述

3.4
请根据习题⼀中的条件编写⼀条 SQL 语句，创建⼀幅包含如下数据的视图（名称为AvgPriceByType）。
在这里插入图片描述
提示：其中的关键是 avg_sale_price

List item

列。与习题三不同，这⾥需要计算出的是各商品种类的平均销售单价。这与使⽤关联⼦查询所得到的结果相同。也就是说，该列可以使⽤关联⼦查询进⾏创建。问题就是应该在什么地⽅使⽤这个关联⼦查询。

3. 各种各样的函数

sql⾃带了各种各样的函数，极⼤提⾼了sql语⾔的便利性。
所谓函数，类似⼀个⿊盒⼦，你给它⼀个输⼊值，它便按照预设的程序定义给出返回值，输⼊
值称为参数。
函数⼤致分为如下⼏类：

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

函数总个数超过200个，不需要完全记住，常⽤函数有 30~50 个，其他不常⽤的函数使⽤时查阅⽂档即可。

3.1 算数函数

- / 四则运算在之前的章节介绍过，此处不再赘述。为了演示其他的⼏个算数函数，在此构造 samplemath 表

-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m float(10,3),
n INT,
p INT);
-- DML ：插入数据
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 函数⽤于计算⼀个数字的绝对值，表示⼀个数到原点的距离。
当 ABS 函数的参数为 NULL 时，返回值也是 NULL 。

MOD – 求余数

语法： MOD( 被除数，除数 )
MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。⼩数没有余数的概念，只能对整
数列求余数。
注意：主流的 DBMS 都⽀持 MOD 函数，只有SQL Server 不⽀持该函数，其使⽤ % 符号
来计算余数。

ROUND – 四舍五⼊

语法： ROUND( 对象数值，保留小数的位数 )
ROUND 函数⽤来进⾏四舍五⼊操作。
注意：当参数 保留⼩数的位数 为变量时，可能会遇到错误，请谨慎使⽤变量。

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

在这里插入图片描述

3.2 字符串函数

字符串函数也经常被使⽤，为了学习字符串函数，在此我们构造 samplestr 表。

-- DDL ：创建表
USE shop;
DROP TABLE IF EXISTS samplestr;
CREATE TABLE samplestr
 (str1 VARCHAR (40),
str2 VARCHAR (40),
str3 VARCHAR (40)
);
-- DML：插入数据
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;

在这里插入图片描述

CONCAT – 拼接

语法： CONCAT(str1, str2, str3)
MySQL中使⽤ CONCAT 函数进⾏拼接。

LENGTH – 字符串⻓度

语法： LENGTH( 字符串 )

LOWER – ⼩写转换

LOWER 函数只能针对英⽂字⺟使⽤，它会将参数中的字符串全都转换为⼩写。该函数不适⽤
于英⽂字⺟以外的场合，不影响原本就是⼩写的字符。
类似的， UPPER 函数⽤于⼤写转换。

REPLACE – 字符串的替换

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

SUBSTRING – 字符串的截取

语法： SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数）
使⽤ SUBSTRING 函数可以截取出字符串中的⼀部分字符串。截取的起始位置从字符串最左
侧开始计算，索引值起始为1。

在这里插入图片描述

（扩展内容）SUBSTRING_INDEX – 字符串按索引截取

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

3.3 日期函数

不同DBMS的⽇期函数语法各有不同，本课程介绍⼀些被标准 SQL 承认的可以应⽤于绝⼤多数 DBMS 的函数。特定DBMS的⽇期函数查阅⽂档即可。

CURRENT_DATE – 获取当前⽇期

SELECT CURRENT_DATE;

在这里插入图片描述

CURRENT_TIME – 当前时间

SELECT CURRENT_TIME;

在这里插入图片描述

CURRENT_TIMESTAMP – 当前⽇期和时间

SELECT CURRENT_TIMESTAMP;

在这里插入图片描述
EXTRACT – 截取⽇期元素

语法： EXTRACT(日期元素 FROM 日期)

使⽤ EXTRACT 函数可以截取出⽇期数据中的⼀部分，例如“年”
“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型。

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;

在这里插入图片描述

3.4 转换函数

“转换”这个词的含义⾮常⼴泛，在 SQL 中主要有两层意思：⼀是数据类型的转换，简称为类
型转换，在英语中称为 cast ；另⼀层意思是值的转换。

CAST – 类型转换

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

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;

在这里插入图片描述

COALESCE – 将NULL转换为其他值

语法： COALESCE(数据1，数据2，数据3……)
COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 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;

在这里插入图片描述

4. 谓词

4.1 什么是谓词

谓词就是返回值为真值的函数。包括 TRUE / FALSE / UNKNOWN 。
谓词主要有以下⼏个：

LIKE
BETWEEN
IS NULL、 IS NOT NULL
IN
EXISTS

4.2 LIKE谓词 – ⽤于字符串的部分⼀致查询

当需要进⾏字符串的部分⼀致查询时需要使⽤该谓词。
部分⼀致⼤体可以分为前⽅⼀致、中间⼀致和后⽅⼀致三种类型。
⾸先我们来创建⼀张表：

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

在这里插入图片描述

前⽅⼀致：选取出“dddabc”

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

SELECT *
FROM samplelike
WHERE strcol LIKE 'ddd%';

其中的 % 是代表“零个或多个任意字符串”的特殊符号，本例中代表“以 ddd 开头的所有
字符串”。
 中间⼀致：选取出“abcddd”“dddabc”“abdddc”
中间⼀致即查询对象字符串中含有作为查询条件的字符串，⽆论该字符串出现在对象字
符串的最后还是中间都没有关系。

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd%';

后⽅⼀致：选取出“abcddd“

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

SELECT *
FROM samplelike
WHERE strcol LIKE '%ddd';

综合如上三种类型的查询可以看出，查询条件最宽松，也就是能够取得最多记录的是中间一
致。这是因为它同时包含前⽅⼀致和后⽅⼀致的查询结果。
 _ 下划线匹配任意 1 个字符
使⽤ _（下划线）来代替 %，与 % 不同的是，它代表了“任意 1 个字符”。

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

BETWEEN谓词 – ⽤于范围查询

使⽤ BETWEEN 可以进⾏范围查询。该谓词与其他谓词或者函数的不同之处在于它使⽤了 3个参数

-- 选取销售单价为100～ 1000元的商品
SELECT product_name, sale_price
FROM product
WHERE sale_price BETWEEN 100 AND 1000;

BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想
让结果中包含临界值，那就必须使⽤ < 和 >。

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

IS NULL、 IS NOT NULL – ⽤于判断是否为NULL

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

SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

在这里插入图片描述
与此相反，想要选取 NULL 以外的数据时，需要使⽤IS NOT NULL。

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

IN谓词 – OR的简便⽤法

多个查询条件取并集时可以选择使⽤ or 语句

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

虽然上述⽅法没有问题，但还是存在⼀点不⾜之处，那就是随着希望选取的对象越来越多，
SQL 语句也会越来越⻓，阅读起来也会越来越困难。这时，我们就可以使⽤IN 谓词
`IN(值1, 值2, 值3, …) 来替换上述 SQL 语句。

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

上述语句简洁了很多，可读性⼤幅提⾼。
反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使⽤否定形式
NOT IN 来实现

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

需要注意的是，在使⽤IN 和 NOT IN 时是⽆法选取出 NULL 数据的。
实际结果也是如此，上述两组结果中都不包含进货单价为 NULL 的叉⼦和圆珠笔。 NULL 只
能使⽤ IS NULL 和 IS NOT NULL 来进⾏判断。

使⽤⼦查询作为IN谓词的参数

 IN和⼦查询
IN 谓词（NOT IN 谓词）具有其他谓词所没有的⽤法，那就是可以使⽤⼦查询作为其参数。我
们已经在 5-2 节中学习过了，⼦查询就是 SQL内部⽣成的表，因此也可以说“能够将表作为
IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。
在此，我们创建⼀张新表 shopproduct 显示出哪些商店销售哪些商品

在这里插入图片描述

由于单独使⽤商店编号（shop_id）或者商品编号（product_id）不能区分表中每⼀⾏数据
，因此指定了 2 列作为主键（primary key）对商店和商品进⾏组合，⽤来唯⼀确定每⼀⾏数
据。
假设我么需要取出⼤阪在售商品的销售单价，该如何实现呢？
第⼀步，取出⼤阪⻔店的在售商品 product_id ; 第⼆步，取出⼤阪⻔店在售商品的销售单价sale_price

在这里插入图片描述
上述语句取出了⼤阪⻔店的在售商品编号，接下来，我么可以使⽤上述语句作为第⼆步的查询
条件来使⽤了。

在这里插入图片描述
根据第5章学习的知识，⼦查询是从最内层开始执⾏的（由内⽽外），因此，上述语句的⼦查询执⾏之后，sql 展开成下⾯的语句

可以看到，⼦查询转换之后变为 in 谓词⽤法，你理解了吗？
或者，你会疑惑既然 in 谓词也能实现，那为什么还要使⽤⼦查询呢？这⾥给出两点原因：
①：实际⽣活中，某个⻔店的在售商品是不断变化的，使⽤ in 谓词就需要经常更新 sql 语
句，降低了效率，提⾼了维护成本；
②：实际上，某个⻔店的在售商品可能有成百上千个，⼿⼯维护在售商品编号真是个⼤⼯程。
使⽤⼦查询即可保持 sql 语句不变，极⼤提⾼了程序的可维护性，这是系统开发中需要重点考
虑的内容。
 NOT IN和⼦查询
NOT IN 同样⽀持⼦查询作为参数，⽤法和 in 完全⼀样。

在这里插入图片描述

EXIST 谓词

EXIST 谓词的⽤法理解起来有些难度。
① EXIST 的使⽤⽅法与之前的都不相同
② 语法理解起来⽐较困难
③ 实际上即使不使⽤ EXIST，基本上也都可以使⽤ IN（或者 NOT IN）来代替
这么说的话，还有学习 EXIST 谓词的必要吗？答案是肯定的，因为⼀旦能够熟练使⽤ EXIST
谓词，就能体会到它极⼤的便利性。
不过，你不⽤过于担⼼，本课程介绍⼀些基本⽤法，⽇后学习时可以多多留意 EXIST 谓词的
⽤法，以期能够在达到 SQL 中级⽔平时掌握此⽤法。
 EXIST谓词的使⽤⽅法
谓词的作⽤就是 “判断是否存在满⾜某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
EXIST（存在）谓词的主语是“记录”。
我们继续以 IN和⼦查询中的示例，使⽤ EXIST 选取出⼤阪⻔店在售商品的销售单价。

在这里插入图片描述

 EXIST的参数
之前我们学过的谓词，基本上都是像“列 LIKE 字符串”或者“ 列 BETWEEN 值 1 AND 值
2”这样需要指定 2 个以上的参数，⽽ EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1
个参数的谓词。所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是⼀个⼦查询。

在这里插入图片描述
上⾯这样的⼦查询就是唯⼀的参数。确切地说，由于通过条件“SP.product_id =
P.product_id”将 product 表和 shopproduct表进⾏了联接，因此作为参数的是关联⼦查询。
EXIST 通常会使⽤关联⼦查询作为参数。
 ⼦查询中的SELECT *
由于 EXIST 只关⼼记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满
⾜⼦查询中 WHERE ⼦句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表
和商店
商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才
返回真（TRUE）。
因此，使⽤下⾯的查询语句，查询结果也不会发⽣变化。

在这里插入图片描述
 使⽤NOT EXIST替换NOT IN
就像 EXIST 可以⽤来替换 IN ⼀样， NOT IN 也可以⽤ NOT EXIST来替换。
下⾯的代码示例取出，不在⼤阪⻔店销售的商品的销售单价

NOT EXIST 与 EXIST 相反，当“不存在”满⾜⼦查询中指定条件的记录时返回真（TRUE）。

CASE 表达式

5.1 什么是 CASE 表达式？

CASE 表达式是函数的⼀种。是 SQL 中数⼀数⼆的重要功能，有必要好好学习⼀下。
CASE 表达式是在区分情况时使⽤的，这种情况的区分在编程中通常称为（条件）分⽀。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包
含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。
语法：
在这里插入图片描述
上述语句执⾏时，依次判断 when 表达式是否为真值，是则执⾏ THEN 后的语句，如果所有
的 when 表达式均为假，则执⾏ ELSE 后的语句。
⽆论多么庞⼤的 CASE 表达式，最后也只会返回⼀个值

CASE表达式的使⽤⽅法

假设现在要实现如下结果：
在这里插入图片描述
因为表中的记录并不包含“A ： ”或者“B ： ”这样的字符串，所以需要在 SQL 中进⾏添加。
并将“A ： ”“B ： ”“C ： ”与记录结合起来。

应⽤场景1：根据不同分⽀得到不同列值

ELSE ⼦句也可以省略不写，这时会被默认为 ELSE NULL。但为了防⽌有⼈漏读，还是希望
⼤家能够显示地写出 ELSE ⼦句。
此外， CASE 表达式最后的“END”是不能省略的，请⼤家特别注意不要遗漏。忘记书写 END
会发⽣语法错误，这也是初学时最容易犯的错误。
 应⽤场景2：实现列⽅向上的聚合
通常我们使⽤如下代码实现⾏的⽅向上不同种类的聚合（这⾥是 sum）
在这里插入图片描述

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

 （扩展内容）应⽤场景3：实现⾏转列
假设有如下图表的结构

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

在这里插入图片描述

总结：
 当待转换列为数字时，可以使⽤ SUM AVG MAX MIN 等聚合函数；
 当待转换列为⽂本时，可以使⽤ MAX MIN 等聚合函数

Mayumi_yumi

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
SQL复杂一点的查询

复杂一点的查询1. 视图1.2 什么是视图1.3 视图与表有什么区别1.4 为什么会存在视图1.5 如何创建视图1. 视图1.2 什么是视图视图是⼀个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的（会在下⾯具体介绍），所以操作视图时会根据创建视图的SELECT语句⽣成⼀张虚拟表，然后在这张虚拟表上做SQL操作。1.3 视图与表有什么区别区别在于“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是⼀个窗⼝，通过这个窗⼝我们可以看到数据库表中真实存在的
复制链接

扫一扫