Task03：复杂⼀点的查询

开始之前，出现了MySQL Workbench出现 Could not connect,server may not be running，原来服务里面设置成了手动，参考启动方式。

视图

什么是视图
视图是⼀个虚拟的表，不同于直接操作数据表，视图是依据SELECT语句来创建的，所以操作视图时会根据创建视图的SELECT语句⽣成⼀张虚拟表，然后在这张虚拟表上做SQL操作。
区别视图和数据表的本质，即视图是基于真实表的⼀张虚拟的表，其数据来源均建⽴在真实表的基础上。

视图的优点

1. 通过定义视图可以将频繁使⽤的SELECT语句保存以提⾼效率。
2. 通过定义视图可以使⽤户看到的数据更加清晰。
3. 通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
4. 通过定义视图可以降低数据的冗余。

创建视图
CREATE VIEW <视图名称>(<列名1>,<列名1 2>,…) AS <SELECT语句>
其中SELECT 语句需要书写在 AS 关键字之后。 SELECT 语句中列的排列顺序和视图中列的排列顺序相同， SELECT 语句中的第 1 列就是视图中的第 1 列， SELECT 语句中的第 2 列就是视图中的第 2 列，以此类推。⽽且视图的列名是在视图名称之后的列表中定义的。
attention！
1.需要注意的是视图名在数据库中需要是唯⼀的，不能与其他视图和表重名。
2.视图不仅可以基于真实表，我们也可以在视图的基础上继续创建视图。虽然在视图上继续创建视图的语法没有错误，但是我们还是应该尽量避免这种操作。这是因为对多数 DBMS 来说， 多重视图会降低 SQL 的性能。
3.需要注意的是在⼀般的DBMS中定义视图时不能使⽤ORDER BY语句。这是因为视图和表⼀样， 数据⾏都是没有顺序的。
在 MySQL中视图的定义是允许使⽤ ORDER BY 语句的，但是若从特定视图进⾏选择，⽽该视图使⽤了⾃⼰的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。【特例】

视图结构修改、更新、删除
ALTER VIEW <视图名> AS <SELECT语句>
———————————————————————————————
因为视图是⼀个虚拟表，所以对视图的操作就是对底层基础表的操作，所以在修改时只有满⾜底层基本表的定义才能成功修改。对于⼀个视图来说，如果包含以下结构的任意⼀种都是不可以被更新的：
 聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
 DISTINCT 关键字。
 GROUP BY ⼦句。
 HAVING ⼦句。
 UNION 或 UNION ALL 运算符。
 FROM ⼦句中包含多个表。
视图归根结底还是从表派⽣出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发⽣了改变，⽽原表没有进⾏相应更新的话，就⽆法保证数据的⼀致性了。
<UPDATE productsum
SET sale_price = ‘5000’
WHERE product_type = ‘办公用品’;>
但是视图的更新只能对视图所看到的数据进行更新，并不能更改原表所有的数据。所以要更新数据还是建议对原表进行修改。并且在创建视图的时候要限制视图的更新操作。
————————————————————————————
DROP VIEW 1 <视图名1> [ , <视图名2> …]删除视图需要一定的权限。

⼦查询

⼦查询指⼀个查询语句嵌套在另⼀个查询语句内部的查询，这个特性从 MySQL 4.1 开始引⼊，在 SELECT ⼦句中先计算⼦查询，⼦查询结果作为外层另⼀个查询的过滤条件，查询可以基于⼀个表或者多个表。
⼦查询就是将⽤来定义视图的 SELECT 语句直接⽤于 FROM ⼦句当中。其中AS studentSum可以看作是⼦查询的名称，⽽且由于⼦查询是⼀次性的，所以⼦查询不会像视图那样保存在存储介质中， ⽽是在 SELECT 语句执⾏之后就消失了。
可以嵌套子查询，但是效率不高，不建议使用。

标量子查询：标量就是单⼀的意思，那么标量⼦查询也就是单⼀的⼦查询。所谓单⼀就是要求我们执⾏的SQL语句只能返回⼀个值，也就是要返回表中具体的某⼀⾏的某⼀列。

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

关联⼦查询：关联二字意味着查询与⼦查询之间存在着联系。难点

关联子查询的难点参考
关联子查询小结：

1. ⾸先执⾏不带WHERE的主查询
2. 根据主查询讯结果匹配product_type，获取⼦查询结果
3. 将⼦查询结果再与主查询结合执⾏完整的SQL语句

练习
3.1

create view viewpractice (product_name, sale_price, regist_date) as 
select product_name, sale_price, regist_date
from product1
where sale_price >=1000 and regist_date=2009-09-20;
SELECT * FROM  ViewPractice;

虽然但是这个两个条件都加上后，并没有数据返回？？？？
3.2插入失败。
3.3
select product_id, product_name, product_type,sale_price, (select avg(sale_price) from product1 )as sale_price_all from product1

3.4 这个有点不会先略过…

select product_type,product_id,product_name,sale_price,avg(sale_price)
 from product1 as p1
 where (SELECT AVG(sale_price)
                       FROM product1 AS p2
                      WHERE p1.product_type = p2.product_type
                      GROUP BY product_type);

各种各样的函数

 算术函数 （⽤来进⾏数值计算的函数）+ - * /
ABS – 绝对值；MOD – 求余数；ROUND – 四舍五⼊

 字符串函数 （⽤来进⾏字符串操作的函数）
CONCAT – 拼接；LENGT H – 字符串⻓度；LOWER – ⼩写转换；REPLACE – 字符串的替换；SUBST RING – 字符串的截取；）SUBST RING_INDEX – 字符串按索引截取

 ⽇期函数 （⽤来进⾏⽇期操作的函数）
CURRENT _DAT E – 获取当前⽇期；CURRENT _T IME – 当前时间；CURRENT _T IMEST AMP – 当前⽇期和时间；EXT RACT – 截取⽇期元素

 转换函数 （⽤来转换数据类型和值的函数）在 SQL 中主要有两层意思：⼀是数据类型的转换，简称为类型转换，在英语中称为 cast ；另⼀层意思是值的转换。
CAST – 类型转换；COALESCE – 将NULL转换为其他值
 聚合函数 （⽤来进⾏数据聚合的函数）

谓词

谓词就是返回值为真值的函数。包括 TRUE / FALSE / UNKNOWN 。
谓词主要有以下⼏个：
 LIKE：% 是代表“零个或多个任意字符串”的特殊符号；_ 下划线匹配任意 1 个字符。
 BET WEEN
 IS NULL、 IS NOT NULL：为了选取出某些值为 NULL 的列的数据，不能使⽤ =，⽽只能使⽤特定的谓词 IS NULL。
 IN
 EXIST ：EXIST 的左侧并没有任何参数。因为 EXIST 是只有 1个参数的谓词。 所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是⼀个⼦查询。
⼦查询中的SELECT *
使⽤NOT EXIST 替换NOT IN

CASE 表达式

CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。

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

ELSE ⼦句也可以省略不写，这时会被默认为 ELSE NULL。但为了防⽌有⼈漏读，还是建议显示地写出 ELSE ⼦句。
此外， CASE 表达式最后的“END”是不能省略的

 当待转换列为数字时，可以使⽤ SUM AVG MAX MIN 等聚合函数；
 当待转换列为⽂本时，可以使⽤ MAX MIN 等聚合函数

练习

3.5 错
3.6

空值
3.7有问题再订正。
SELECT count(case when sale_price<1000 then count(product_id) end) as low_price, count(case when 3000>sale_price>1000 then count(product_id) end) as median_price, count(case when sale_price>3000 then count(product_id) end) as high_price FROM product1;