DataWhale学习-SQL编程3：复杂一点的查询_dataworks的表和视图怎么区分-优快云博客

本文链接：https://blog.youkuaiyun.com/m0_38090787/article/details/121447058

本文详细介绍了SQL编程中的视图概念，包括视图与表的区别、创建、修改和删除视图。此外，还探讨了子查询的三种类型及其与视图的关系，并讲解了各种函数的用法，如算数、字符串和日期函数。最后，文章通过实例展示了谓语LIKE、BETWEEN、IN和EXISTS的使用，以及CASE表达式的应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 视图

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

1.1 视图与表的区别：

是否保存了实际的数据

视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

1.2 视图存在的原因：

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

1.3 视图的创建：

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

SELECT 语句需要书写在 AS 关键字之后
SELECT 语句中列的排列顺序和视图中列的排列顺序相同
视图名在数据库中需要是唯一的，不能与其他视图和表重名
可以在视图的基础上继续创建视图
一般的DBMS中定义视图时不能使用ORDER BY语句（因为视图和表一样，数据行都是没有顺序的）

在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。

创建视图示例：

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

在Navicat上操作：

查询->新建查询->运行：

创建的视图：

新建的shop_product：：

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

通过“product.product_id = shop_product.product_id”进行了连接。

查看创建的视图：

1.4 修改视图

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

示例：修改上方productSum视图：

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

注意比较date类型的regist_date数据段的方法。

Navicat上操作依然是“查询 -> 新建查询”：

结果如下：

1.5 更新视图内容

对视图的操作就是对底层基础表的操作，所以修改时需要满足底层基本表的定义。

包含以下结构不可修改：

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

视图是从表派生出来的，如果原表可以更新，那么视图中的数据也可以更新，反之亦然。

示例：

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

修改前：

修改后：

原表中只修改被选入视图的数据，视图只是原表的一个窗口，所以它只能修改透过窗口能看到的内容。

1.6 删除视图

删除视图需要相应权限。

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

示例：删除productSum视图：

DROP VIEW productSum;

productSum视图已经被删除，

再对其进行操作，显示不存在：

2 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，在 SELECT 子句中先计算子查询，结果作为外层查询的过滤条件，查询可以基于一个表或者多个表。
子查询与视图的关系：

子查询就是将用来定义视图的 SELECT 语句直接用于 FROM 子句中。
AS ...可看作子查询的名称。
由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 SELECT 语句执行之后就消失了。

2.1 嵌套子查询

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;

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

2.2 标量子查询

即单一子查询，要求执行的SQL语句只返回一个值，即表中具体某一位置的数据。

示例：

表product:

执行以下标量子查询：

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

执行结果如下：

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

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

关联子查询执行顺序：

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

练习题

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
3000

| 2009-09-20
| 2009-09-20

操作：

INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('T恤衫', 1500, '2009-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('菜刀', 3000, '2009-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('老人与海', 150, '2008-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('风扇', 100, '2007-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('卫生纸', 30, '2006-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('笔记本', 40, '2005-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('电饭煲', 500, '2004-09-20');
INSERT INTO product_1 (product_name, sale_price, regist_date) VALUES ('行李箱', 600, '2003-09-20');

CREATE VIEW ViewPractice5_1
AS
SELECT product_name,
sale_price,
regist_date
FROM product
WHERE sale_price > 1000
AND regist_date = ‘2009-09-20’;

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

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

product中添加数据成功，ViewPractice5_1视图中没有数据添加，因为不满足条件regist_date = ‘2009-09-20’。

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
创建表product_3：

CREATE TABLE product_3
(product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER NOT NULL
);

向表中插入数据：

INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0001', 'T恤衫', '衣服', 1000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0002', '打孔器', '办公用品', 500);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0003','运行T恤', '衣服', 4000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0004','菜刀', '厨房用具', 3000);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0005','高压锅', '厨房用具', 6800);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0006','叉子', '厨房用具', 500);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0007','擦菜板', '厨房用具', 880);
INSERT INTO product_3 (product_id, product_name, product_type, sale_price) VALUES ('0008','圆珠笔', '办公用品', 100);

选择数据：

SELECT *,
(SELECT AVG(sale_price) FROM ViewPractice5_3) AS sale_price_all
FROM ViewPractice5_3;

3 各种函数

函数大致分类：

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

3.1 算数函数

构造 samplemath 表：

-- DDL ：创建表
USE shop; -- 选择数据库
DROP TABLE IF EXISTS samplemath;
CREATE TABLE samplemath
(m NUMERIC(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;
+----------+------+------+
| m | n | p |
+----------+------+------+
| 500.000 | 0 | NULL |
| -180.000 | 0 | NULL |
| NULL | NULL | NULL |
| NULL | 7 | 3 |
| NULL | 5 | 2 |
| NULL | 4 | NULL |
| 8.000 | NULL | 3 |
| 2.270 | 1 | NULL |
| 5.555 | 2 | NULL |
| NULL | 1 | NULL |
| 8.760 | NULL | NULL |
+----------+------+------+
11 rows in set (0.00 sec)

ABS(数值) 绝对值

当 ABS 函数的参数为 NULL 时，返回值也是 NULL：

MOD(被除数，除数) 求余数

注：

被除数或者除数有NULL则计算结果为NULL
只对整数，小数没有余数的概念
SQL Server 不支持该函数，使用 %

ROUND(对象数值，保留小数的位数) 四舍五入

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(str1, str2, str3) 拼接字符串
LENGTH(字符串) 字符串长度
LOWER(字符串) 小写转换
REPLACE(对象字符串，替换前的字符串，替换后的字符串) 字符串替换
SUBSTRING(对象字符串 FROM 截取的起始位置 FOR 截取的字符数)索引值起始为1
SUBSTRING_INDEX (原始字符串，分隔符，n) SUBSTRING_INDEX (原始字符串，分隔符，n)，获取原始字符串按照分隔符分割后，第 n 个分隔符之前（或之后）的子字符串，支
持正向和反向索引，索引起始值分别为 1 和 -1 。

正向：

反向

3.3 日期函数

CURRENT_DATE -- 获取当前日期（重要）
CURRENT_TIME -- 获取当前时间
CURRENT_TIMESTAMP -- 当前日期和时间
EXTRACT -- 截取日期元素

3.4 转换函数

CAST（转换前的值 AS 想要转换的数据类型）--类型转换

一是数据类型的转换，简称为类型转换，在英语中称为 cast
另一层意思是值的转换

COALESCE(数据1，数据2，数据3……) -- 将NULL转换为其他值

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是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 |
+-------+-------------+------------+
1 row in set (0.00 sec)

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)
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; -- 提交事务

结果：

前方一致：LIKE 'ddd%'

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

中间一致：LIKE '%ddd%'

后方一致：LIKE '%ddd'
_ 下划线匹配任意 1 个字符：

SELECT *
FROM samplelike
WHERE strcol LIKE 'abc__';

4.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;
+--------------+------------+
| product_name | sale_price |
+--------------+------------+
| 打孔器 | 500 |
| 叉子 | 500 |
| 擦菜板 | 880 |

4.5 IN谓词 -- OR的简便用法

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

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

反之，希望选取出“进货单价不是 320 元、 500 元、 5000 元”的商品时，可以使用否定形式
NOT IN来实现。

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

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

-- 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',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002'
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003'
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004'
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006'
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007'
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007',
INSERT INTO shopproduct (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001',
COMMIT; -- 提交事务
SELECT * FROM shopproduct;

取出大阪在售商品的销售单价，如何实现呢？
第一步，取出大阪门店的在售商品 product_id ;
第二步，取出大阪门店在售商品的销售单价 sale_price。

4.7 EXIST谓词

判断是否存在满足某种条件的记录
示例：以 IN和子查询中的示例，使用 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);

EXIST 只需在右侧书写 1 个参数，该参数通常都会是一个子查询。

5 CASE 表达式

应用场景1：根据不同分支得到不同列值

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

假设现在要实现如下结果：
A ：衣服
B ：办公用品
C ：厨房用具

应用场景2：实现列方向上的聚合

通常我们使用如下代码实现行的方向上不同种类的聚合（这里是 sum）

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)

聚合函数 + 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 实现数字列 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      |

上述代码实现了数字列 score 的行转列，也可以实现文本列 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 |
+------+---------+------+---------+
| 张三 | 语文     | 数学 | 外语    |
| 李四 | 语文     | 数学 | 外语    |