※食用指南:文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记,笔记整理比较粗糙,主要目的自存为主,记录完整的学习过程。(图片超级多,慎看!)
【中字】SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!
https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2
目录:
2、ALTERING OR DROPPING VIEWS——更改或删除视图
4、THE WITH OPTION CHECK CLAUSE——WITH OPTION CHECK子句
5、OTHER BENEFITS OF VIEWS——视图的其他优点
第七章:视图
1、CREATING VIEWS——创建视图
把查询或子查询存到视图里,直接使用无需重复编写,从而简化选择语句
未来可能有很多查询基于这段查询来写:最佳客户名单、销售额最低的客户
CREATE VIEW sales_by_client AS
因为是创建视图,所以这个语句不会像选择语句一样返回结果,而是创建了一个视图对象
刷新后即可查看视图
和表格一样,可以从这个视图中选择、使用数据
使用方法:
①进行排序
②进行筛选
③进行连接:和任何有client_id列的表
视图的作用就像一张虚拟表,但视图不储存数据,数据储存在了表中
只是把视图提供给了一张基础表去使用
练习:
balance:使用invoices表中的invoice_total-payment_total
我们对每个客户收取的费用与它们支付给我们的金额之差
2、ALTERING OR DROPPING VIEWS——更改或删除视图
发现查询有问题,需要修复视图的方法:
①删除并重建视图
删除后,修改CREATE VIEW并重新执行即可
②REPLACE关键字
比较推荐第二种,因为不需要删除
❗如果查询窗口没有了,无法拿到创建视图用到的查询
把视图保存在SQL文件里,并把文件放到源码控制
文件放入GIT储存库并和别人共享储存库是很常见的做法
别人也可以在他们的电脑上重建这个数据库
具体操作:
①把文件保存到【视图】的文件夹
新建文件夹,保存sales_by_client
②把文件放到码源控制中(共享)
如果团队不使用码源控制,用编辑模式打开
不属于我们输入的代码,是MySQL自己加上的
此处为了名称冲突
如果使用特定的SQL语言中有涵义的关键字,MySQL就会以表或者视图或者列名称处理它们,如下:
增加ORDER BY total_sales DESC后,点击Apply
再次点击Apply,完成
更新视图的最理想的方法:保存文件放到码源控制中
3、UPDATEABLE VIEWS——可更新视图
像表格一样使用视图
在ISELECT、NSERT、UPDATE、DELETE语句中皆可使用
但仅限于特定情况:视图中没有以下关键字
说明这个视图是可更新视图(可以在上面更新数据)
创建可更新视图(没有DISTINCT等用关键字)
依据:
invoices表中有invoice_total、payment_total,但是没有balance
所以每次需要使用balance时就要重新计算(invoice_total-payment_total)
方法:
①可以为Invoices表创建视图,视图里有每张发票的balance
②添加一个筛选:仅返回有balance的发票(balance>0)
❗不能直接使用balance这个别名,否则显示不存在
(MySQL先返回WHERE,再返回SELECT)
根据以上可更新视图
①在视图中删除记录:删除发票1
②在视图中更新日期:推迟发票2日期
③在视图中插入新发票
只有当视图有基础表中要用到的所有列,插入新发票才会生效
比如视图中没有invoice_date,就不能往视图里插入任何行,因为我们的表不允许插入的日期为空值(遵守Table里的规则)
大多数情况下我们在表中更新数据,但出于安全原因,有时可能没有某张表的直接权限,所以只能通过视图修改数据
在此情况下可以使用视图插入、更新或者删除数据(前提是可更新视图)
4、THE WITH OPTION CHECK CLAUSE——WITH OPTION CHECK子句
试求:更新视图里发票2的payment_total为invoice_total,balance会为0
结果:发票2消失
这是视图的默认行为,当你通过视图更新或者删除数据,一些行可能会消失
如果不希望UPDATE、DELETE语句将行从视图中删除
在选择语句中添加WITH CHECK OPTION,随后执行、重建
更新另一张发票,将发票3的payment_total改为invoice_total
Outup×:CHECK OPTION failed
添加WITH CHECK OPTION,当视图更新数据且结果可能导致行从视图里被删除,就会得到错误提示(阻止并报错行为)
5、OTHER BENEFITS OF VIEWS——视图的其他优点
①Simplify queries(简化查询)
把查询或子查询存到视图里,直接使用无需重复编写,从而简化选择语句
②Reduce the impact of changes(减少数据库设计改动的影响)
假设:invoices表中最上面写有10段查询,第二天觉得要给这些表做变动,可能想把表或者其中一列重命名,又或者想把表的某列移动到其他表
查询方式:必须回去修改所有涉及到这张表的查询
如果不能去修改所有涉及到这张表的任何查询,应该怎么处理?——视图
理解方法1:一个副本
如果在invoices表改动,为了使Query的查询不受影响
用视图invoices_with_balance再现invoices表来操作
(视图invoices_with_balance和invoices表,除了balance,其他一致)
理解方法2:传输介质
在写Query的查询时使用的是视图invoices_with_balance,而不是invoices表
那么当invoices表有变动的话
修改视图invoices_with_balance = 修改Query = 变动invoices表
例如:对invoices表做改动,只用修改视图查询,对变动列使用别名,以此来找回它们原来的名称
(把其中的某列移到了新的表,同理)
my_view选了table中的payment_date;
queries用了my_view的payment_date
🔺若想payment_date改名为payment_due
只需要my_view通过SELECT
payment_date AS payment_due
那么queries中关于payment_date的代码就不用修改
综上,如果我们的所有查询都是基于这个视图(invoices_with_balance),它不会受基础表(invoices表)改动的影响
③Restrict access to the data(限制基础表访问)
从视图中使用WHERE子句筛选记录
从基础表汇总删除一些列
如果用户没有基础表访问权限,只能通过视图更新数据,无法修改某列的值或者修改非视图返回的行(避免错误操作,弄乱基础表)
————TBC