【Datawhale202208】SQL编程-Task 03

• ch03: 复杂一点的查询

1. 视图表【虚拟的表；操作视图时会根据创建视图的SELECT语句生成一张虚拟表，然后在这张虚拟表上做SQL操作；视图不是表，视图是虚表，视图依赖于表】
2. 的基本语法【CREATE VIEW <视图名称>(<列名1>,<列名2>,...) AS <SELECT语句>】
3. 注意事项【SELECT 语句需要书写在 AS 关键字之后；SELECT 语句中列的排列顺序和视图中列的排列顺序相同；视图名在数据库中需要是唯一的；*可以在视图的基础上继续创建视图，但多重视图可能会降低sql的性能；在一般的DBMS中定义视图时不能使用ORDER BY语句，在 MySQL中视图的定义是允许使用 ORDER BY 语句的，但是若从特定视图进行选择，而该视图使用了自己的 ORDER BY 语句，则视图定义中的 ORDER BY 将被忽略。】
4. 修改视图结构【ALTER VIEW <视图名> AS <SELECT语句>】
5. 无法更新的视图类型【聚合函数 SUM()、MIN()、MAX()、COUNT() 等；DISTINCT 关键字；GROUP BY 子句；HAVING 子句；UNION 或 UNION ALL 运算符；FROM 子句中包含多个表。修改时只有满足底层基本表的定义才能成功修改。在创建视图时也尽量使用限制不允许通过视图来修改表。】
6. 删除视图【DROP VIEW <视图名1> [ , <视图名2> …]】
7. 子查询【一个查询语句嵌套在另一个查询语句内部的查询，从 MySQL 4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。】
8. 子查询不会像视图那样保存在存储介质中， 而是在 SELECT 语句执行之后就消失了。
9. 嵌套子查询
10. 标量子查询【单一的子查询，所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列。】
11. 关联子查询【通过一些标志将内外两层的查询连接起来起到过滤数据的目的】
12. sql 自带函数【算术函数 （用来进行数值计算的函数）；字符串函数 （用来进行字符串操作的函数）；日期函数 （用来进行日期操作的函数）；转换函数 （用来转换数据类型和值的函数）；聚合函数 （用来进行数据聚合的函数）】

13. 函数	类别	表达式	效果
    ABS	算术函数	ABS( 数值 )	取绝对值
    MOD	算术函数	MOD( 被除数，除数 )	求余数，SQL Sever不支持，只能用%
    ROUND	算术函数	ROUND( 对象数值，保留小数的位数 )	四舍五入

14. 函数	类别	表达式	效果
    CURRENT_DATE	日期函数	CURRENT_DATE;	获取当前日期
    CURRENT_TIME	日期函数	CURRENT_TIME;	获取当前时间
    URRENT_TIMESTAMP	日期函数	URRENT_TIMESTAMP;	获取当前日期和时间
    EXTRACT	日期函数	EXTRACT(日期元素 FROM 日期)	截取日期元素，返回值为数值类型

15.  

     

16. 
17. 谓词【返回值为真值的函数。LIKE / BETWEEN / IS NULL、IS NOT NULL / IN / EXISTS】
18. CASE表达式【在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。有简单CASE表达式和搜索CASE表达式两种。】
19. 搜索CASE表达式【】

练习题

3.1

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

select * from ViewPractice5_1;

3.2

报错

Field of view 'shop.viewpractice5_1' underlying table doesn't have a default value

可能是因为视图表不能直接插入数据？视图表不是真正意义上的表

3.3

select product_id , product_name , product_type , sale_price , (select avg(sale_price) from product) as sale_price_avg

from product;

3.4

create view AvgPriceByType(product_id, product_name, product_type, sale_price,sale_price_avg)

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 product_type) as sale_price_avg

from product as p1;

select * from AvgPriceByType;

3.5

NULL 作为一个值，进行四则运算时， NULL+1、NULL*2诸如之类，得到最终结果都为 UNKNOW。

3.6

运行第一条语句，除了滤掉了 purchase_price 为 500 or 2800 or 5000的数据，也滤掉了值为null的数据。最终得到打孔器和擦菜板及各自的价格。

运行第二条语句，没有得到任何数据。

使用IN 和 NOT IN 时无法选取出NULL数据。只能通过IS NULL 和 IS NOT NULL 来进行判断。

3.7

select sum(case when sale_price <= 1000 then 1 else 0 end) as low_price,

  sum(case when sale_price >=1001 and sale_price <= 3000 then 1 else 0 end) as mid_price,

  sum(case when sale_price >=3001 then 1 else 0 end) as high_price

from product;