Day3-SQL视图、函数(Datawhale)

目录

一、视图

1. 什么是视图

2. 视图和表的区别

3. 为什么存在视图

4.  如何创建视图

4.1 基于单表的视图

4.2 基于多表的视图

5. 修改视图结构

6. 更新视图内容

7. 删除视图

二、子查询

1. 什么是子查询

2. 子查询与视图的关系

3. 嵌套子查询

4. 标量子查询

5. 关联子查询

练习1

三、函数

1. 算术函数

2. 字符串函数

3. 日期函数

4. 转换函数

四、谓语

五、CASE表达式

1. 不同分支得到不同列值

2. 实现列方向上的聚合

3. 实现行转列

练习2

总结

---

一、视图

从SQL角度操作视图和操作表看起完全相同

1. 什么是视图

视图是虚拟的表，视图是依据SELECT语句来创建的，操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在虚拟表上做SQL操作

2. 视图和表的区别

视图不保存实际的数据，作为一个窗口，通过窗口看到数据表中实际的数据，视图是基于真实表的一张虚拟表，其数据来源均建立在真实表的基础上。

3. 为什么存在视图

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

4.  如何创建视图

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

• SELECT语句中列的排列顺序和视图的排列顺序相同，主要是顺序的一致性，名称可以不一致。
• 视图名在数据库中需要唯一，不能与其他视图和表重名
• 视图可以基于表和视图创建(避免多重视图，会降低SQL性能)
• 视图不能使用ORDER BY

4.1 基于单表的视图

CREATE VIEW productsum (product_type, cnt_product)
AS
SELECT product_type, COUNT(*)
FROM product
GROUP BY product_type;

4.2 基于多表的视图

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;

在视图的基础上查询

语句与在表上的操作一致

SELECT * 
FROM view_shop_product
WHERE product_type = '衣服';

5. 修改视图结构

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

修改视图的结构，可以新增查询条件，也可以新增字段，新增字段时一定要保证视图字段和查询字段一致。

ALTER 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
AND regist_date>'2009-09-11';

6. 更新视图内容

对视图的操作基于底层表的操作，修改时只有满足底层基本表的定义才能修改成功。包含以下任意一种，都不能被更新：

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

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

通过视图的修改只能透过窗口能看到内容

注意：不推荐透过视图去改表内容

7. 删除视图

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

需要有相应的权限

DROP VIEW productsum;

 

二、子查询

1. 什么是子查询

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

 

2. 子查询与视图的关系

子查询可以将用来定义视图的SELECT语句直接用于FROM子句中。子查询是一次性的，不会被保存在存储介质中，视图是会被保存在存储介质中

 

3. 嵌套子查询

SELECT *
FROM 
(SELECT product_name, shop_name
	FROM  product,
     shop_product
	WHERE product.product_id = shop_product.product_id			
) AS shop;

多个SELECT嵌套查询，不建议使用过多的嵌套查询，会影响SQL的性能。

4. 标量子查询

单一的子查询。需要返回表中具体的某一行的某一列

1. 查询出product表中sale_price大于平均销售单价的产品

SELECT * 
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

也可以将平均销售单价显示出来

SELECT * ,
    (SELECT AVG(sale_price) FROM product)	AS avg_price	
FROM product
WHERE sale_price > (SELECT AVG(sale_price) FROM product);

 2. 查询register_date最晚的商品

SELECT * 
FROM product
WHERE regist_date = (SELECT max(regist_date) FROM product);

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

根据结果我们会发现，查询到的是大于每一个product_type的平均值，相当于分组之后求平均值；之前查询到的是大于总的平均销售，没有按照每一个product_type去求平均数

关联子查询：

• 首先执行不带WHERE的主查询
• 根据查询结果匹配product_type，获得子查询结果
• 将子查询结果和主查询结合执行完整的SQL

练习1

1. 依据product表创建视图view_practice，满足一下三条件

• 销售单价大于等于1000
• 登记日期是2009-09-20
• 包含商品名称、销售单价、登记日期三列

CREATE VIEW view_practice (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';

SELECT * FROM view_practice;

思路：首先需要通过product查找到相应的字段并限制相应的条件，之后按照视图创建的语句将SQL语句整理好

2. 向view_practice插入一条数据

INSERT INTO view_practice VALUES ('菜刀',300,'2009-11-02');

CREATE VIEW view_practice_t (product_name, sale_price, regist_date)
AS 
SELECT product_name, sale_price, regist_date
FROM product ;

INSERT INTO view_practice_t VALUES ('菜刀',300,'2009-11-02');

思路：在插入的过程中，结果显示无法插入，考虑到我做了限制条件，我将单价改为1000，日期改2009-09-20，发现依旧无法插入数据，然后我建立了一张全新的不做限制的表单，再执行插入依旧无法插入。(我在百度上显示如果按照条件mysql的视图是可以插入的，但是我测试的结果是不可以，这是我疑惑的点)

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;

4. 编写SQL语句，包含按product_type分类的平均销售额

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

思路：再原来的product的基础上增加一列，首先我们需要查询相互按product_type分类的平均单价，之后再将两张表通过product_type关联起来（也可以使用join 将两张表拼接起来达到一样的效果）

三、函数

1. 算术函数

2. 字符串函数

3. 日期函数

4. 转换函数

数据类型的转换和值的转换

四、谓语

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

五、CASE表达式

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

1. 不同分支得到不同列值

SELECT product_name,
       product_type,
      CASE WHEN product_type = '衣服' THEN CONCAT('A', '-', product_name)
		WHEN product_type = '厨房用具' THEN CONCAT('B-', product_name)
		ELSE NULL
		END new_product_type
FROM product;

2. 实现列方向上的聚合

SELECT SUM(CASE WHEN product_type = '衣服' THEN sale_price ELSE 0 END) sum_price_cloth
FROM product;

3. 实现行转列

CREATE TABLE score 
( name VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  score INT 
);

INSERT INTO score VALUES('张三','语文',93),
('张三','数学',88),
('张三','英语',91),
('李四','语文',87),
('李四','数学',90),
('李四','英语',77);

SELECT     name,
	   SUM(CASE WHEN subject='语文' THEN score ELSE 0 END) chinese,
	   SUM(CASE WHEN subject='数学' THEN score ELSE 0 END) math,
	   SUM(CASE WHEN subject='英语' THEN score ELSE 0 END) english
FROM score
GROUP BY name;

总结：

• 待转列为数字时，使用SUM/AVG/MAX/MIN聚合函数
• 待转列为文本时，使用MAX/MIN聚合函数

练习2

1. 运算或函数中含有NULL时，结果全都变为NULL？(是的)

运算中含有NULL值，结果会变为NULL值，运算前可以使用IFNULL(字段,0)将值转换为0参与运算。使用聚合函数时，含有NULL值的记录也不会参与聚合

 

2. 对product表执行语句，观察结果

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500,2800,5000);

SELECT product_name, purchase_price
FROM product
WHERE purchase_price NOT IN (500,2800,5000,NULL);

含有NOT IN 条件中含有NULL值，结果也变为了NULL

 

3. 按照以下条件进行分类

• 低档商品：销售单价在1000以下
• 中档商品：销售单价在1001-3000
• 高档商品：销售单价在3001以上

计算这三种分类的所含商品数量

SELECT 
	SUM(CASE WHEN sale_price<=1000 THEN 1 ELSE 0 END ) low_price,
	SUM(CASE WHEN sale_price BETWEEN 1001 AND 3000 THEN 1 ELSE 0 END) mid_price,
	SUM(CASE WHEN sale_price >= 3001 THEN 1 ELSE 0 END ) high_price
FROM product;

因为我们使用的product表的产品是唯一的，因此可以使用 1 和 0 来进行计数，最后求和得到商品数量，但是不是普遍适用的方法，个人觉得下面的思路是普遍的方法：先找到满足条件的产品，之后对产品进行去重统计

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

 

 

---

总结

1. 着重注意运算和函数对于NULL的使用，如果存在NULL值在计算前转换为0

2. 注意COALESCE函数的使用

3. CASE WHEN行转列的使用