阿里天池龙珠计划SQL训练营Task03笔记

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为AI训练营SQL-阿里云天池

什么是视图

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

视图与表有什么区别

是否保存了实际的数据

所以视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口我们可以看到数据库表中真实存在的数据

已经有数据表了为什么还需要视图

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

创建视图

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

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

而且视图的列名是在视图名称之后的列表中定义的。

需要注意的是视图名在数据库中需要是唯一的，不能与其他视图和表重名。

视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。

注：虽然在视图的基础上再创建视图语法上没有错误但是尽量避免，对多数DBMS来说，多重视图会降低SQL性能

在一般的DBMS中定义视图时不能使用ORDER BY语句，在 MySQL中视图的定义是允许使用 ORDER BY 语句

查询视图

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

修改视图结构

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

更新视图内容

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

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

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

如果原表可以更新，那么 视图中的数据也可以更新

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

不推荐通过修改视图来修改表

删除视图

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

子查询

例：

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

定义

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

子查询和视图的关系

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

注：虽然子查询可嵌套，但是不仅难以理解而且效率差，不推荐

标量子查询

所谓标量就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列

通过标量子查询语句查询出销售单价高于平均销售单价的商品

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

 标量子查询不仅仅局限于 WHERE 子句中，通常任何可以使用单一值的位置都可以使用

关联子查询

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 ASp1
WHERE sale_price > (SELECT AVG(sale_price)
                    FROM product ASp2
                    WHERE p1.product_type =p2.product_type
                    GROUP BY product_type);

关联查询的执行过程：

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

对于一些复杂的查询需要使用子查询加一些条件语句组合才能得到正确的结果。但是无论如何对于一个SQL语句来说都不应该设计的层数非常深且特别复杂，不仅可读性差而且执行效率也难以保证，所以尽量有简洁的语句来完成需要的功能。 

函数

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

 算术函数

• ABS – 绝对值

语法：ABS( 数值 )

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

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

• MOD – 求余数

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

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

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

• ROUND – 四舍五入

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

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

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

字符串函数

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

 日期函数

• CURRENT_DATE – 获取当前日期
• CURRENT_TIME – 当前时间
• CURRENT_TIMESTAMP – 当前日期和时间

转换函数 

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

• CAST – 类型转换

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

• COALESCE – 将NULL转换为其他值

语法：COALESCE(数据1，数据2，数据3……)

COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。

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

谓词

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

谓词主要有以下几个：

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

like——用于字符串的部分一致查询

--查询姓名为张鑫鑫的人的性别
SELECT sex FROM info WHERE name like '张鑫鑫'
--查询名为鑫鑫的人的性别
SELECT sex FROM info WHERE name like '%鑫鑫'
--查询姓为张的人的性别
SELECT sex FROM info WHERE name like '张%'
--查询姓名中含有'鑫'的人的性别
SELECT sex FROM info WHERE name like '%鑫%'

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

BETWEEN——范围查询

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 的列的数据，不能使用 =
SELECT product_name, purchase_price
FROM product
WHERE purchase_price IS NULL;

IN——OR的简便用法

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

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

例：“进货单价不是 320 元、 500 元、 5000 元”的商品时

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

注：使用IN 和 NOT IN 时是无法选取出NULL数据的

子查询作为IN参数

SELECT product_name, sale_price
FROM product
WHERE product_id IN (SELECT product_id
                     FROM shopproduct
                     WHERE shop_id = '000C');

EXIST

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

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

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 个参数，该参数通常都会是一个子查询

由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系

注：把在 EXIST 的子查询中书写 SELECT * 当作 SQL 的一种习惯

CASE

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

无论多么庞大的 CASE 表达式，最后也只会返回一个值

ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL

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;

+--------------+------------------+
| product_name | abc_product_type |
+--------------+------------------+
| T恤          | A ： 衣服        |
| 打孔器       | B ： 办公用品    |
| 运动T恤      | A ： 衣服        |
| 菜刀         | C ： 厨房用具    |
| 高压锅       | C ： 厨房用具    |
| 叉子         | C ： 厨房用具    |
| 擦菜板       | C ： 厨房用具    |
| 圆珠笔       | B ： 办公用品    |
+--------------+------------------+

CASE 表达式最后的“END”是不能省略的

例：列的方向上展示不同种类额聚合值

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