1.视图
定义：视图是虚拟的表，依靠select语句创建
创建视图的原因：
- 通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
- 通过定义视图可以使用户看到的数据更加清晰。
- 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
- 通过定义视图可以降低数据的冗余。
1.1创建视图的语法
CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>
其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。而且视图的列名是在视图名称之后的列表中定义的。
视图名在数据库中需要是唯一的，不能与其他视图和表重名。
定义视图时不可使用ORDER BY语句
创建视图：
基于单表的视图
CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type ;基于多表的视图
--首先创建一张新表
CREATE TABLE shop_product
(shop_id CHAR(4) NOT NULL,
shop_name VARCHAR(200) NOT NULL,
product_id CHAR(4) NOT NULL,
quantity INTEGER NOT NULL,
PRIMARY KEY (shop_id, product_id));
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0001', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0002', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000A', '东京', '0003', 15);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0002', 30);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0003', 120);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0004', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0006', 10);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000B', '名古屋', '0007', 40);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0003', 20);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0004', 50);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0006', 90);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000C', '大阪', '0007', 70);
INSERT INTO shop_product (shop_id, shop_name, product_id, quantity) VALUES ('000D', '福冈', '0001', 100);--在productsum shop_product的基础上创建一个视图
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;1.2修改视图结构的语法
ALTER VIEW <视图名> AS <SELECT语句>
--修改视图
ALTER VIEW productSum
AS
SELECT product_type, sale_price
FROM Product
WHERE regist_date > '2009-09-11';1.3更新视图
视图为虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满足底层基本表的定义才能成功修改。
即对视图的更新修改，本质上是对基础表的修改。
对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
- 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
- DISTINCT 关键字。
- GROUP BY 子句。
- HAVING 子句。
- UNION 或 UNION ALL 运算符。
- FROM 子句中包含多个表。
--更新视图的语句，使用UPDATE SET
UPDATE productsum
SET sale_price = '5000'
WHERE product_type = '办公用品';1.4删除视图
DROP VIEW <视图名1> [ , <视图名2> …]
DROP VIEW productsum;2.子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
子查询是一次性的，不会如视图一样保存。
2.1嵌套子查询
--嵌套子查询
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;
--最内层的子查询我们将其命名为productSum，这条语句根据product_type分组并查询个数，第二层查询中将个数为4的商品查询出来，最外层查询product_type和cnt_product两列。
--为简洁与效率，应尽量减少SELECT子查询语句的反复套娃2.2 标量子查询（单一子查询）
单一就是要求执行的SQL语句
--查询销售单价高于平均单价的商品
SELECT product_id, product_name, sale_price
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);只能返回一个值，也就是要返回表中具体的某一行的某一列
SELECT product_id,
product_name,
sale_price,
(SELECT AVG(sale_price)
FROM product) AS avg_price
FROM product;?
?2.3 关联子查询
关联子查询即将内外两层的查询连接起来起到过滤数据的目的
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);
--内部的表为P2，外部的表为P1，使用WHERE将两个表连接在一起
?关联查询的执行过程：
- 首先执行不带WHERE的主查询
- 根据主查询讯结果匹配product_type，获取子查询结果
- 将子查询结果再与主查询结合执行完整的SQL语句
练习题1
创建出满足下述三个条件的视图（视图名称为 ViewPractice5_1）。使用 product（商品）表作为参照表，假设表中包含初始状态的 8 行数据。
- 条件 1：销售单价大于等于 1000 日元。
- 条件 2：登记日期是 2009 年 9 月 20 日。
- 条件 3：包含商品名称、销售单价和登记日期三列。
CREATE VIEW ViewPractice5_1
AS
(SELECT product_name,sale_price,regist_date
FROM product
WHERE sale_price >= 1000 AND regist_date="2009-09-20" );
?向习题一中创建的视图 ViewPractice5_1 中插入如下数据，会得到什么样的结果呢？
SQL出错了，不知道啥结果
--请根据如下结果编写 SELECT 语句，其中 sale_price_all 列为全部商品的平均销售单价。
/*product_id | product_name | product_type | sale_price | sale_price_all
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 | 2097.5000000000000000
0002 | 打孔器 | 办公用品 | 500 | 2097.5000000000000000
0003 | 运动T恤 | 衣服 | 4000 | 2097.5000000000000000
0004 | 菜刀 | 厨房用具 | 3000 | 2097.5000000000000000
0005 | 高压锅 | 厨房用具 | 6800 | 2097.5000000000000000
0006 | 叉子 | 厨房用具 | 500 | 2097.5000000000000000
0007 | 擦菜板 | 厨房用具 | 880 | 2097.5000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 2097.5000000000000000*/
SELECT product_id,product_name,product_type,sale_price,
(SELECT AVG(sale_price)
FROM product) AS sale_price_all
FROM product
ORDER BY product_id;
请根据习题一中的条件编写一条 SQL 语句，创建一幅包含如下数据的视图（名称为AvgPriceByType）。
product_id | product_name | product_type | sale_price | avg_sale_price
------------+-------------+--------------+------------+---------------------
0001 | T恤衫 | 衣服 | 1000 |2500.0000000000000000
0002 | 打孔器 | 办公用品 | 500 | 300.0000000000000000
0003 | 运动T恤 | 衣服 | 4000 |2500.0000000000000000
0004 | 菜刀 | 厨房用具 | 3000 |2795.0000000000000000
0005 | 高压锅 | 厨房用具 | 6800 |2795.0000000000000000
0006 | 叉子 | 厨房用具 | 500 |2795.0000000000000000
0007 | 擦菜板 | 厨房用具 | 880 |2795.0000000000000000
0008 | 圆珠笔 | 办公用品 | 100 | 300.0000000000000000
/*提示：其中的关键是 avg_sale_price 列。与习题三不同，这里需要计算出的 是各商品种类的平均销售单价。这与使用关联子查询所得到的结果相同。 也就是说，该列可以使用关联子查询进行创建。问题就是应该在什么地方使用这个关联子查询。*/
CREATE VIEW AvgPriceByType
AS (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 p1.product_type) AS avg_sale_price
FROM product AS p1);