Task03：复杂一点的查询_利用第一题创建的视图完成以下查询-优快云博客

本文链接：https://blog.youkuaiyun.com/DMax13/article/details/111461252

学习目标：

学习内容：

1、视图
2、子查询
3、函数
4、谓词
5、 CASE 表达式

学习产出：

视图

一、视图与数据表的区别

视图	数据表
虚拟	真实

在这里插入图片描述
二、视图的优点

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

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

SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。

虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说，多重视图会降低 SQL 的性能。

需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句。

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
  FROM product
 GROUP BY product_type
 --下面语句错误！！！
 ORDER BY product_type;

为什么不能使用 ORDER BY 子句呢？这是因为视图和表一样，数据行都是没有顺序的。

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

(2)修改视图结构

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

(3)更新视图内容
我们在创建视图时也尽量使用限制不允许通过视图来修改表

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

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

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

UPDATE <视图名>
	SET <列表名>+条件
WHERE <列表名>+条件

e.g.

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

这样的话原表的数据也会随之更改

(4)删除视图

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

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

子查询

嵌套子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询。将用来定义视图的 SELECT 语句直接用于 FROM 子句当中。由于子查询是一次性的，所以子查询不会像视图那样保存在存储介质中，而是在 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;

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

标量子查询

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

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

关联子查询
关联子查询就是通过一些标志将内外两层的查询连接起来起到过滤数据的目的。

关于子查询的东西还是不太清楚qwq

函数

算术函数（用来进行数值计算的函数）
字符串函数（用来进行字符串操作的函数）

函数名	作用
CONCAT	拼接
LENGTH	字符串长度
LOWER	小写转换
REPLACE	字符串的替换
SUBSTRING	字符串的截取

日期函数（用来进行日期操作的函数）

函数名	作用
CURRENT_DATE	获取当前日期
CURRENT_TIME	当前时间
CURRENT_TIMESTAMP	当前日期和时间
EXTRACT	截取日期元素

转换函数（用来转换数据类型和值的函数）

函数名	作用
CAST	类型转换
COALESCE	将NULL转换为其他值

聚合函数（用来进行数据聚合的函数）

谓词

LIKE（用于字符串的部分一致查询）

% 是代表“零个或多个任意字符串”的特殊符号
如ddd%表示ddd开头字符，%ddd%中间有ddd，%ddd末尾一致

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

BETWEEN(用于范围查询)

BETWEEN AND 是闭区间查询，如果开区间要用<>

IS NULL、IS NOT NULL(用于判断是否为NULL)
IN(OR的简便用法)
EXISTS

Case表达式

条件分支函数

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

练习题

创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
条件 1：销售单价大于等于 1000 日元。
条件 2：登记日期是 2009 年 9 月 20 日。
条件 3：包含商品名称、销售单价和登记日期三列。

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

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

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

error！！！
3. 3. 请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。

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

CREATE VIEW AvgPriceByType(product_id,product_name,product_type,sale_price,avg_sale_price)
  AS
  SELECT product_id,product_name,product_type,sale_price,(SELECT AVG(sale_price)
  FROM product AS p1
  WHERE p1.product_type=p2.product_type
  GROUP BY product_type ) AS avg_sale_price
  FROM product AS p2;

False

SELECT
	count(
		CASE
		WHEN sale_price <= 1000 THEN
			product_name
		ELSE
			NULL
		END
	) AS low_price,
	count(
		CASE
		WHEN sale_price BETWEEN 1001 AND 3000 THEN
			product_name
		ELSE
			NULL
		END
	) AS mid_price,
	count(
		CASE
		WHEN sale_price >= 3001 THEN
			product_name
		ELSE
			NULL
		END
	) AS high_price
FROM
	product;