天池SQL训练营学习笔记03

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

3.1视图

视图与表的区别---“是否保存了实际的数据”。视图并不是数据库真实存储的数据表，可以看作是一个窗口，通过这个窗口可以看到数据库表中真实存在的数据。所以我们要区别视图和数据表的本质，即视图是基于真实表的一张虚拟的表，其数据来源均建立在真实表的基础上。

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

3.1.2 为什么会存在视图

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

3.1.3 如何创建视图

create view <视图名称>(<列名1>,<列名2>,...) as <select语句>

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

 3.1.4 修改视图结构

ALTER VIEW <视图名> as <select语句>

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

3.1.5如何更新视图内容

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

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

视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。不推荐通过视图来修改表。

3.1.6删除视图

drop view <视图名1>[,<视图名2>...]

需要相应的权限才能成功删除。

3.2 子查询

3.2.1 什么是子查询

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

3.2.2 子查询和视图的关系

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

3.2.3 嵌套子查询

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

3.2.4 标量子查询

标量指单一，标量子查询也就是单一的子查询，返回表中具体的某一行的某一列。

3.2.5 关联子查询

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语句：

where p1.product_type=p2.product_type

执行顺序：

1.先执行主查询：

SELECT product_type, product_name, sale_price
  FROM product AS p1

2.从主查询的product_type先取第一个值，然后通过关联子查询语句，传入子查询，子查询为：

SELECT AVG(sale_price)
   FROM product ASp2
                      WHERE p2.product_type=<关联子查询的传入语句>
   GROUP BY product_type;

3.从子查询得到的结果AVG(sale_price)，返回主查询：

SELECT product_type, product_name, sale_price
  FROM product AS p1
 WHERE sale_price >  <传出的AVG(sale_price)>
  and product_type =<关联子查询的语句要求>;

4.product _type取第二个值，得到整个语句的第二结果，依次类推，把product _type全取值一遍，就得到了整个语句的结果集。

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

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

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

练习题第一部分

第一题

create view ViewPractice5_1 as
select product_name, sale_price, regist_date from product
where sale_price>1000 and
regist_date = '2009-09-20';

第二题

插入时会报错，因为视图和原表会同时插入数据，但是新插入的数据不满足原表的非空要求。

第三题

select product_id, product_name, product_type, sale_price,
(select avg(sale_price) from product) as sale_price_all
from product;

第四题

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)
from product as p1;

3.3 各种各样的函数

函数大致分为如下几类：

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

3.3.1 算术函数

绝对值函数：ABS(数值)

求余数：MOD(被除数，除数)(是modulo的缩写，小数没有余数的概念，只针对整数列求余数）

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

3.3.2 字符串函数

字符串拼接：CONCAT(str1, str2, str3)

字符串长度：LENGTH(字符串)

小写转换：LOWER(字符串)，只针对英文字母，全部转为小写。

大写转换：UPPER(字符串)，只针对英文字母，全部转为大写。

字符串替换：REPLACE(操作字符串对象，替换前的字符串，替换后的字符串)

字符串截取：SUBSTRING(对象字符串 from 截取的起始位置 for 截取的字符串数)，索引起始为1

字符串按索引截取：SUBSTRING_INDEX(原始字符串，分隔符，n) ，获取原始字符串按照分隔符分割后，第n个分隔符之前(或之后)的子字符串，支持正向和反向索引，索引起始值为1(或-1)。

www.mysql.com

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

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

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

substring_index(substring_index('www.mysql.com','.',2),'.',-1)
mysql

3.3.3 日期函数

获取当前日期：CURRENT_DATE

获取当前时间：CURRENT_TIME

获取当前日期和时间：CURRENT_TIMESTAMP

SELECT CURRENT_DATE;
SELECT CURRENT_TIME;
SELECT CURRENT_TIMESTAMP;

截取日期元素：EXTRACT(日期元素 from 日期)

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.3.4 转换函数

类型转换：CAST(转换前的值 AS 想要转换的数据类型)

-- 将字符串类型转换为数值类型
SELECT CAST('0001' AS SIGNED INTEGER) AS int_col;
-- 将字符串类型转换为日期类型
SELECT CAST('2009-12-14' AS DATE) AS date_col;

将NULL转换为其他值：COALESCE(数据1，数据2，数据3)，是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 |
+-------+-------------+------------+

3.4 谓词

3.4.1 谓词定义

谓词：返回值为真值的函数，包括 TRUE / FALSE / UNKNOWN.

谓词主要有以下几个：

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

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

部分一致：      前方一致('所查找字符串%')、

                        中间一致('%所查找字符串%')、

                        后方一致('%所查找字符串')。

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

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

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

拓展SQL的四种匹配模式：

1、%表示任意0个或多个字符。%三%：表示模糊查询。

2、_表示任意单个字符。Select * FROM user Where name LIKE '_三_'；只找出name为三个字且中间一个字是“三”的。

3、[ ]表示括号内所列字符中的一个（类似与正则表达式）。Select * FROM user Where name LIKE '[张李王]三'; 将找出“张三”、“李三”、“王三”（而不是“张李王三”）； 如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e“。Select * FROM user Where name LIKE '老[1-9]'；将找出“老1”、“老2”、……、“老9”；

4、[^ ] 表示不在括号所列之内的单个字符。语句：Select * FROM user Where name LIKE '[^张李王]三'；将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等；Select * FROM user Where name LIKE '老[^1-4]'; 将排除“老1”到“老4”寻找“老5”、“老6”、……、“老9”。

5、* 表示查找的是所有信息

 3.4.3 BETWEEN谓词—用于范围查询

BETWEEN a AND b：[a, b]包含临界值的闭区间。如果不想要临界值，需要使用<和>。

select product_name from product
where sale_price between 100 and 1000;

select product_name from product
where sale_price<1000 and sale_price>100;

3.4.4 IS NULL、IS NOT NULL—用于判断是否为NULL

为了选取出某些值(不)为NULL的列的数据，不能使用‘=’，而只能使用特定的谓词IS (NOT) NULL.

3.4.5 IN谓词—OR的简便用法

多个查询条件取并集时可以选择使用OR语句，但是随着选取的对象增加，SQL语句会越长，可以使用'IN(值1，值2，值3，...)来简化SQL语句。否定时用NOT IN(值1，值2，值3，...)。

注意：值为NULL时用IN和NOT IN无法取出。

select product_name from pruduct
where purchase_price=320,
or purchase_price=500,
or purchase_price=5000;

select product_name from product
where purchase_price in (320, 500, 5000);

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

IN谓词可以使用子查询，视图等作为参数。

select product_name, sale_price from product
where product_id in (
    select product_id from shopproduct
    where shop_id='000A');

3.4.7 EXIST谓词

EXIST：判断是否存在满足某种条件的记录。很多时候基本上可以使用 IN 或 NOT IN 来代替EXIST或NOT EXIST。

EXIST 通常会使用关联子查询作为参数。

select product_name, sale_price from product as p
where exists (
    select * from shopproduct as sp
    where sp.shop_id='000A'
    and sp.product_id=p.product_id);

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

3.5 CASE表达式

3.5.1 什么是CASE表达式

CASE表达式是函数的一种，在区分情况时使用，在编程中通常成为（条件）分支。 分为简单CASE表达式和搜索CASE表达式两种（后者包含前者）。

语法执行时，依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。 无论多么大的 CASE 表达式，最后也只会返回一个值。ELSE 子句也可以省略不写，这时会被默认为 ELSE NULL。END不可以省略。

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

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

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

--行方向上的聚合
select product_type,
    sum(sale_price) as sum_price
from product
group by product_type;

--case实现列聚合
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;

应用3：实现行转列

转为

利用聚合函数+CASE WHEN表达式实现行转列

select name,
    sum(case when subject='语文' then score else 0 end) as 'chinese',
    sum(case when subject='数学' then score else 0 end) as 'math',
    sum(case when subject='英语' then score else 0 end) as 'english'
from score
group by name;

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

练习题第二部分

3.5 会

3.6 （1）选出售卖价格不为500，2800，5000的商品名和售卖价格；

      （2）结果为空（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>3000 then 1 else 0 end) as high_price
from product;