DataWhale 2021 8月SQL | 第三章 复杂一点的查询

DataWhale 2021 8月SQL | 第三章 复杂一点的查询

3.1 视图

什么是视图

视图是一个虚拟的表，不同于直接操作数据表，视图是依据SELECT 语句创建的

视图和表有什么区别

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

如何创建视图

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

视图不能重名，虚拟的窗口

修改视图

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

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 子句中包含多个表。

删除视图

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

删除刚才我们创建的视图；
DROP VIEW productSum;

子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL 4.1 开始引入，在 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;

标量子查询

标量子查询可以返回一个值

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

关联子查询

关联子查询既然包含关联两个字那么一定意味着查询与子查询之间存在着联系。
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的

练习题：

解答：


解答：
会报错

解答：

3.4

各种各样的函数

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

算术函数

ABS – 绝对值
MOD – 求余数
ROUND – 四舍五入

字符串函数

CONCAT – 拼接
LENGTH – 字符串长度
LOWER – 小写转换
REPLACE – 字符串的替换
SUBSTRING – 字符串的截取

日期函数

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

转换函数

CAST – 类型转换
COALESCE – 将NULL转换为其他值

谓词

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

谓词主要有以下几个：

LIKE
BETWEEN
IS NULL、IS NOT NULL
IN
EXISTS

case 表达示例

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;

根据运算的性质和函数的具体计算来决定结果是不是NULL

需要从product中找出当单价不是500 2800 5000 的商品

2)没有返回
当 not in 参数中包含 null 时，查询结果会为空

SELECT
count(case when sale_price <= 1000 then sale_price end) AS low_price,
count(case when sale_price BETWEEN 1001 AND 3000 then sale_price end) AS mid_price,
count(case when sale_price >= 3001 then sale_price end) AS high_price
FROM product;