本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；Task03/Task04

学习记录

3.1 视图

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

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

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

创建的表格设了一个联合主键shop_id和product_id

如果要删除：ALTER TABLE test DROP PRIMARY KEY;

新增主键：ALTER TABLE shop_product ADD PRIMARY KEY(shop_id,product_id);

创建视图

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;

修改视图结构

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

更新视图内容

因为视图是一个虚拟表，所以对视图的操作就是对底层基础表的操作，在修改时只有满足底层基本表的定义才能成功修改。

对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：

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

更新视图

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

修改视图的时候是设置product_type='办公用品'的商品的sale_price=5000，为什么原表的数据只有一条做了修改呢？

视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。

删除视图

DROP VIEW productSum;

3.2 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入。

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

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

子查询和视图的关系

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

嵌套子查询

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语句只能返回一个值，也就是要返回表中具体的某一行的某一列。

SELECT product_id, product_name, sale_price,

(SELECT AVG(sale_price) FROM product) AS avg_price

FROM product;

在能够使用常数或者列名的地方，无论是 SELECT 子句、GROUP BY 子句、HAVING 子句，还是 ORDER BY 子句，几乎所有的地方都可以使用。

关联子查询

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

SELECT product_type, product_name, sale_price

      FROM product ASp1

WHERE sale_price > (SELECT AVG(sale_price)

        FROM product ASp2

                  WHERE p1.product_type =p2.product_type

GROUP BY product_type);

首先来看看正常情况下SELECT的书写顺序和执行顺序

书写顺序：

SELECT》FROM 》WHERE》GROUP BY》HAVE》ORDER BY

执行顺序：

FROM 》WHERE》GROUP BY》HAVE》SELECT》ORDER BY

关联子查询执行逻辑如下：

（1）先从主查询的Product表中product _type列取出第一个值，进入子查询中，得到子查询结果，然后返回父查询，判断父查询的where子句条件，则返回整个语句的第1条结果。

（2）重复上述操作，直到所有主查询中的Product表中product _type列记录取完为止。得出整个语句的结果集，就是最后的答案。

练习题

3.1

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

3.2

18:12:53    INSERT INTO ViewPractice5_1 VALUES (' 刀子 ',300,'2009-11-02')    Error Code: 1423. Field of view 'test03.viewpractice5_1' underlying table doesn't have a default value    0.000 sec
3.3

CREATE VIEW AvgPriceByType
    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.3 各种各样的函数

USE shop;
DROP TABLE IF EXISTS samplemath;（清除前面创建的表）

3.3.1 算数函数

• ABS – 绝对值
• 语法：ABS( 数值 )

• ABS 函数用于计算一个数字的绝对值，表示一个数到原点的距离。

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

• MOD – 求余数

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

MOD 是计算除法余数（求余）的函数，是 modulo 的缩写。小数没有余数的概念，只能对整数列求余数。

注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
 

• ROUND – 四舍五入

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

ROUND 函数用来进行四舍五入操作。

注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。

3.3.2 字符串函数

• 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。

 

 

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

 

 

结果：www.mysql

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

结果：mysql.com

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

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

结果：mysql

3.3.3 日期函数

不同DBMS的日期函数语法各有不同，特定DBMS的日期函数查阅文档即可。

• CURRENT_DATE – 获取当前日期

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

转换函数

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

• CAST – 类型转换

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

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

3.4 谓词

谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。

谓词主要有以下几个：

• LIKE
• BETWEEN
• IS NULL、IS NOT NULL
• IN
• EXISTS

LIKE谓词 – 用于字符串的部分一致查询

当需要进行字符串的部分一致查询时需要使用该谓词。

部分一致大体可以分为前方一致、中间一致和后方一致三种类型。

• 前方一致：选取出“dddabc”

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

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

• _下划线匹配任意 1 个字符

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

BETWEEN谓词 – 用于范围查询

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

SELECT product_name, sale_price FROM product WHERE sale_price BETWEEN 100 AND 1000;

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

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。

IN谓词 – 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来实现。

需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。

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

IN 谓词（NOT IN 谓词）具有其他谓词所没有的用法，那就是可以使用子查询作为其参数。在 5-2 节中学习过了，子查询就是 SQL内部生成的表，因此也可以说“能够将表作为 IN 的参数”。同理，我们还可以说“能够将视图作为 IN 的参数”。

取出大阪门店在售商品的销售单价:

SELECT product_name, sale_price

FROM product

WHERE product_id IN (SELECT product_id

                                              FROM shopproduct

                                                     WHERE shop_id = '000C');

根据第5章学习的知识，子查询是从最内层开始执行的（由内而外），因此，上述语句的子查询执行之后，sql 展开成下面的语句:

SELECT product_name, sale_price FROM product WHERE product_id IN ('0003', '0004', '0006', '0007');

看到，子查询转换之后变为 in 谓词用法

3.4.7 EXIST 谓词

EXIST 谓词的用法理解起来有些难度。

① EXIST 的使用方法与之前的都不相同

② 语法理解起来比较困难

③ 实际上即使不使用 EXIST，基本上也都可以使用 IN（或者 NOT IN）来代替

这么说的话，还有学习 EXIST 谓词的必要吗？答案是肯定的，因为一旦能够熟练使用 EXIST 谓词，就能体会到它极大的便利性。

谓词的作用就是 “判断是否存在满足某种条件的记录”。

如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。

EXIST（存在）谓词的主语是“记录”。

EXIST 的左侧并没有任何参数

因为 EXIST 是只有 1 个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。

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

• 使用NOT EXIST替换NOT IN

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

3.5 CASE 表达式

CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能；

CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

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

根据不同分支得到不同列值

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;

实现列方向上的聚合

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;

实现行转列

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;

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

练习题

3.5   运算或者函数中含有 NULL 时，结果全都会变为NULL  对

3.6 第一种能查询到结果，第二种没有结果，因为NOT IN不能查询NULL

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