【DW组队学习—Wonderful SQL】复杂一点的查询

参考：DATAWHALE - 一个热爱学习的社区

目录

Ch3.复杂一点的查询

一、视图

二、子查询

2.1 嵌套子查询

2.2 标量子查询

2.3 关联子查询

三、函数

四、谓词

 练习题

3.1 创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。

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

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

3.4 请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。

3.5 四则运算中含有 NULL 时（不进行特殊处理的情况下），运算结果是否必然会变为NULL？

 3.6 对本章中使用的 product（商品）表执行如下2条SELECT语句，能够得到什么样的结果呢？

3.7 按照销售单价( sale_price )对练习3.6中的 product（商品）表中的商品进行如下分类。

---

Ch3.复杂一点的查询

一、视图

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

·视图与表的区别

视图并不是数据库真实存储的数据表，它可以看作是一个窗口，通过这个窗口可以看到数据库表中真实存在的数据。所以视图是基于真实表的一张虚拟表，其数据来源均建立在真实表的基础上。视图不是表，视图是虚表，视图依赖于表。

·使用视图的优点

①通过定义视图可以将频繁使用的SELECT语句保存以提高效率。

②通过定义视图可以使用户看到的数据更加清晰。

③通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。

④通过定义视图可以降低数据的冗余。

视图操作

操作	命令	举例
视图创建	CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>	--基于单表的视图 CREATE VIEW productsum (product_type, cnt_product) AS SELECT product_type, COUNT(*)   FROM product  GROUP BY product_type ; --基于多表的视图 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 <视图名> AS <SELECT语句>	ALTER VIEW productSum AS SELECT product_type, sale_price FROM Product WHERE regist_date > '2009-09-11';
视图更新	UPDATE <视图名> SET <列名>=<表达式>[,<列名2>=<表达式2>...] WHERE <条件表达式>	UPDATE productsum SET sale_price = '5000' WHERE product_type = '办公用品'; 【注】更新视图的同时，原始图的对应数据也会发生改变，但是只能修改通过视图看到的部分。 虽然可以修改成功，但是并不推荐这种方式。在创建视图时也尽量使用限制不允许通过视图来修改表。
视图删除	DROP VIEW <视图名1> [ , <视图名2> …]	DROP VIEW productSum; 【注】需要有相应的权限才能成功删除。

【注】

①SELECT 语句中列的排列顺序和视图中列的排列顺序相同。

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

③可以在视图的基础上继续创建视图。应该尽量避免这种操作。这是因为对多数DBMS来说，多重视图会降低SQL的性能。

 ④在一般的DBMS中定义视图时不能使用ORDER BY语句，因为视图和表一样，数据行都是没有顺序的。在MySQL中视图的定义是允许使用ORDER BY语句的，但是若从特定视图进行选择，而该视图使用了自己的ORDER BY语句，则视图定义中的ORDER BY将被忽略。

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

聚合函数 SUM()、MIN()、MAX()、COUNT() 等。

DISTINCT关键字。

GROUP BY子句。

HAVING子句。

UNION或UNION ALL运算符。

FROM子句中包含多个表。

二、子查询

SELECT stu_name

FROM (

         SELECT stu_name, COUNT(*) AS stu_cnt //子查询

          FROM students_info

          GROUP BY stu_age) AS studentSum;

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

子查询是一次性的，不会像视图那样保存在存储介质中，而是在SELECT语句执行之后就消失了。