SQL学习（3）——表的复杂查询与函数操作

北海虽赊，扶摇可接

已于 2022-10-18 10:18:25 修改

阅读量355

点赞数

分类专栏： SQL基础教程_DataWhale 文章标签： sql 学习数据库

于 2022-06-20 00:29:14 首次发布

本文链接：https://blog.csdn.net/weixin_46336048/article/details/125363616

版权

SQL基础教程_DataWhale 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

SQL学习（3）——表的复杂查询

1、视图
2、子查询
3、函数
4、谓词
5、CASE 表达式
6. 练习题

新建样例列表product&shop_product：

CREATE DATABASE shop;
 CREATE TABLE product
(product_id CHAR(4) NOT NULL,
 product_name VARCHAR(100) NOT NULL,
 product_type VARCHAR(32) NOT NULL,
 sale_price INTEGER ,
 purchase_price INTEGER ,
 regist_date DATE ,
 PRIMARY KEY (product_id));
 
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
INSERT INTO product VALUES('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO product VALUES('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO product VALUES('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
INSERT INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');

CREATE TABLE shop_product
(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));

INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0001',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0002',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A',	'东京',		'0003',	15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0002',	30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0003',	120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0004',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0006',	10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B',	'名古屋',	'0007',	40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0003',	20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0004',	50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0006',	90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C',	'大阪',		'0007',	70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D',	'福冈',		'0001',	100);

在这里插入图片描述

1、视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作。
《sql基础教程第2版》用一句话非常凝练的概括了视图与表的区别—“是否保存了实际的数据”。所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据。
在这里插入图片描述
视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。
个人感觉，有点类似EXCEL的筛选功能。

1.1、创建视图

-- 基本语法
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
-- demo
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type ;

其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

1.1.1、基于单表的视图

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

在这里插入图片描述

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

在这里插入图片描述

1.2、查询视图

与表查询相同：

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

在这里插入图片描述

1.3、修改视图

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

-- 语法
ALTER VIEW <视图名> AS <SELECT语句>`
-- Demo
ALTER VIEW productSum
    AS
        SELECT product_type, sale_price
          FROM Product
         WHERE regist_date > '2009-09-11';

在这里插入图片描述

1.4、更新视图

更新视图与修改视图区别是：修改视图改变视图的列，更新视图只改变数据值。
因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

-- 设置安全模式
SET SQL_SAFE_UPDATES = 0;
UPDATE productsum
   SET sale_price = '1000'
 WHERE product_type = '办公用品';

在这里插入图片描述
而且，原表也完成了更新：

注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

1.5、删除视图

-- 删除视图的基本语法如下：
DROP VIEW <视图名1> [ , <视图名2> …]
--注意：需要有相应的权限才能成功删除。

-- 删除视图
DROP VIEW productSum;

2、子查询

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

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

子查询是将用来定义视图的 SELECT 语句直接用于 FROM 子句当中进行查询。
视图是根据SELECT语句创建视图然后在这个基础上再进行查询。

其中AS XXXX可以看作是子查询的名称，而且由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 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;

其中最内层的子查询将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

2.2、标量子查询

标量就是单一的意思，标量子查询也就是单一的子查询。所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列，即一个确定的单一数据。
ex1：通过标量子查询语句查询出销售单价高于平均销售单价的商品：

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

标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用。也就是说，能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。
ex2：通过标量子查询语句查询出商品及商品平均售价

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

2.3、关联子查询

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

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

在这里插入图片描述
关联子查询中将外面的product表标记为p1，将内部的product设置为p2，而且通过WHERE语句连接了两个查询。
执行过程：

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

参考博客：https://zhuanlan.zhihu.com/p/41844742

1、关联子查询的执行逻辑完全不同于正常的SELECT语句。
2、关联子查询执行逻辑如下：
（1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。
（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。

3、函数

SQL函数大致分为如下几类：

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

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

在这里插入图片描述

ABS – 绝对值

语法：ABS( 数值 )

ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。
当 ABS 函数的参数为NULL时，返回值也是NULL。

MOD – 求余数

语法：MOD( 被除数，除数 )

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

ROUND – 圆整（四舍五入）

语法：ROUND( 对象数值，保留小数的位数 )

ROUND 函数用来进行四舍五入操作。
注意：当参数保留小数的位数为变量时，可能会遇到错误，请谨慎使用变量。
ex4:

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表如下：

-- 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。
ex5:

select str1,str2,str3,
				CONCAT(str1,str2,str3) as str_concat,
				length(str1) as len_str,
				lower(str1) as low_str,
				upper(str1) as up_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                                | 
+------------------------------------------+
1 row in set (0.00 sec)

SELECT SUBSTRING_INDEX('www.mysql.com', '.', -2);
+-------------------------------------------+
| SUBSTRING_INDEX('www.mysql.com', '.', -2) | 
+-------------------------------------------+
| mysql.com                                 | 
+-------------------------------------------+
1 row in set (0.00 sec)

获取第1个元素比较容易，获取第2个元素/第n个元素可以采用二次拆分的写法。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1);
+--------------------------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX('www.mysql.com', '.', 2), '.', -1) |
+--------------------------------------------------------------------+
| mysql                                                              |
+--------------------------------------------------------------------+
1 row in set (0.00 sec)

REPEAT – 字符串按需重复多次

语法：REPEAT(string, number)

该函数用来对特定字符实现按需重复。

SELECT REPEAT('加油！',3);
+-----------------------------+
| REPEAT('加油！',3)          |
+-----------------------------+
| 加油！加油！加油！          |
+-----------------------------+
1 row in set (0.00 sec)

3.3、日期函数

CURRENT_DATE – 获取当前日期
CURRENT_TIMESTAMP – 当前日期和时间
EXTRACT – 截取日期元素

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

使用 EXTRACT 函数可以截取出日期数据中的一部分，例如“年”，“月”，或者“小时”“秒”等。该函数的返回值并不是日期类型而是数值类型
在这里插入图片描述

3.4、转换函数

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

CAST – 类型转换

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

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

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;
+-------+-------------+------------+
| 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谓词 – 用于字符串的部分一致查询

当需要进行字符串的部分一致查询时需要使用该谓词。
部分一致大体可以分为前方一致、中间一致和后方一致三种类型。
案例表格创建：

-- DDL ：创建表
CREATE TABLE samplelike
	( strcol VARCHAR(6) NOT NULL,
	  PRIMARY KEY (strcol)
	);
-- 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';

在这里插入图片描述

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

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

在这里插入图片描述

4.4、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来实现。
--WHERE purchase_price NOT IN (320, 500, 5000);

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

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

4.5.1、IN和子查询

IN 谓词（NOT IN 谓词）可以使用子查询作为其参数。子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

在此，我们创建一张新表shopproduct显示出哪些商店销售哪些商品，如下：

-- 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）或者商品编号（product_id）不能区分表中每一行数据，因此指定了 2 列作为主键（primary key）对商店和商品进行组合，用来唯一确定每一行数据。

假设需要取出大阪在售商品的销售单价，该如何实现呢？

第一步，取出大阪门店的在售商品 product_id ;
第二步，取出大阪门店在售商品的销售单价 sale_price

-- step1：取出大阪门店的在售商品 `product_id`
SELECT product_id FROM shopproduct
WHERE shop_id = '000C';
-- step2：取出大阪门店在售商品的销售单价 `sale_price`
SELECT product_name, sale_price FROM product
WHERE product_id IN (SELECT product_id FROM shopproduct
                       WHERE shop_id = '000C');

在这里插入图片描述
子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，SQL展开成下面的语句

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

可以看到，子查询转换之后变为 in 谓词用法。既然 in 谓词也能实现，那为什么还要使用子查询呢？这里给出两点原因：

①：实际生活中，某个门店的在售商品是不断变化的，使用 in 谓词就需要经常更新 sql 语句，降低了效率，提高了维护成本；
②：实际上，某个门店的在售商品可能有成百上千个，手工维护在售商品编号真是个大工程。

使用子查询即可保持 sql 语句不变，极大提高了程序的可维护性，这是系统开发中需要重点考虑的内容。

4.5.2、NOT IN和子查询

NOT IN 同样支持子查询作为参数，用法和 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 谓词

EXIST谓词的使用方法

谓词的作用就是 “判断是否存在满足某种条件的记录”。
如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
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的参数

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

(SELECT * FROM shopproduct AS sp
  WHERE sp.shop_id = '000C' AND sp.product_id = p.product_id)

上面这样的子查询就是唯一的参数。确切地说，由于通过条件“sp.product_id = p.product_id”将 product 表和 shopproduct表进行了联接，因此作为参数的是关联子查询。 EXIST 通常会使用关联子查询作为参数。

子查询中的SELECT

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询中 WHERE 子句指定的条件“商店编号（shop_id）为 ‘000C’，商品（product）表和商店商品（shopproduct）表中商品编号（product_id）相同”的记录，只有存在这样的记录时才返回真（TRUE）。
因此，使用下面的查询语句，查询结果也不会发生变化。

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 的一种习惯。

使用NOT EXIST替换NOT IN

就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。

下面的代码示例取出，不在东京门店销售的商品的销售单价。

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

在这里插入图片描述
NOT EXIST 与 EXIST 相反，当“不存在”满足子查询中指定条件的记录时返回真（TRUE）。

5、CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能。
CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。

语法：

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

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

假设现在要实现如下结果：

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

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

5.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。但为了防止有人漏读，还是希望大家能够显式地写出 ELSE 子句。此外， CASE 表达式最后的“END”是不能省略的。

5.2、实现列方向上的聚合

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

SELECT product_type, SUM(sale_price) AS sum_price
      	FROM product
 		GROUP BY product_type;  
+--------------+-----------+
| product_type | sum_price |
+--------------+-----------+
| 衣服         |      5000 |
| 办公用品      |       600 |
| 厨房用具      |     11180 |
+--------------+-----------+

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

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;

在这里插入图片描述

5.3、实现行转列

假设有如下图表的结构
在这里插入图片描述
计划得到如下的图表结构

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

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

上述代码实现了数字列 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 |
+------+---------+------+---------+
| 张三 | 语文    | 数学 | 外语    |
| 李四 | 语文    | 数学 | 外语    |
+------+---------+------+---------+
2 rows in set (0.00 sec

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

6. 练习题

6.1 创建视图

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

条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

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

SELECT * FROM ViewPractice5_1;

在这里插入图片描述

6.2 插入数据

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

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

出现报错：1423 - Field of view ‘shop.viewpractice5_1’ underlying table doesn’t have a default value。

因为修改视图，其实是在底层表中进行修改，但是底层表，除了视图中的数据，还有一些别的数据，没有初始值，所以会报错。

6.3 编写标量子查询

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

-- 3.3 标量子查询
SELECT product_id, product_name, product_type, sale_price , 
(SELECT AVG(sale_price) FROM product) as sale_price_avg 
FROM product

在这里插入图片描述

6.4 编写关联子查询

请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
在这里插入图片描述
提示：其中的关键是 sale_price_avg_type 列。与习题三不同，这里需要计算出的是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。

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 sale_price_avg_type
FROM product AS p1

在这里插入图片描述

6.5 判断题

四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL ？
YES

SELECT m,n,m+n,m-n,m*n,m*n
FROM samplemath;

在这里插入图片描述

6.6 语句验证

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

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

product表中purchase_price价格不属于(500, 2800, 5000)同时not null 的项。
在这里插入图片描述

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

NOT IN的参数含有NULL，返回结果为空；
在这里插入图片描述

6.7 商品分类查询

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

低档商品：销售单价在1000日元以下（T恤衫、办公用品、叉子、擦菜板、圆珠笔）
中档商品：销售单价在1001日元以上3000日元以下（菜刀）
高档商品：销售单价在3001日元以上（运动T恤、高压锅）
请编写出统计上述商品种类中所包含的商品数量的 SELECT 语句，结果如下所示。

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

SELECT  
SUM(CASE WHEN sale_price < 1000 THEN 1 ELSE 0 END) AS low_price,
SUM(CASE WHEN sale_price BETWEEN 1000 AND 3000  THEN 1 ELSE 0 END) AS mid_price,
SUM(CASE WHEN sale_price > 3000 THEN 1 ELSE 0 END) AS high_price
FROM  product;