网站接入商排名,宣传网站制作方案,传奇网页链接,一流的镇江网站建设上一级目录#xff1a;Mosh_完全掌握SQL课程_学习笔记 其它相关#xff1a;数据概要【第八章】视图Views (时长18分钟)1. 创建视图Creating Views (5:36)小结就是创建虚拟表#xff0c;自动化一些重复性的查询模块#xff0c;简化各种复杂操作#xff08;包括复杂的子查询…上一级目录Mosh_完全掌握SQL课程_学习笔记 其它相关数据概要【第八章】视图Views (时长18分钟)1. 创建视图Creating Views (5:36)小结就是创建虚拟表自动化一些重复性的查询模块简化各种复杂操作包括复杂的子查询和连接等注意视图虽然可以像一张表一样进行各种操作但并没有真正储存数据数据仍然储存在原始表中视图只是储存起来的模块化的查询结果是为了方便和简化后续进一步操作而储存起来的虚拟表。案例创建 sales_by_client 视图USE sql_invoicing;CREATE VIEW sales_by_client ASSELECT client_id,name,SUM(invoice_total) AS total_salesFROM clients cJOIN invoices i USING(client_id)GROUP BY client_id, name;-- 虽然实际上这里加不加上name都一样若要删掉该视图用 DROP VIEW sales_by_client 或右键创建视图后可就当作 sql_invoicing 库下一张表一样进行各种操作USE sql_invoicing;SELECT s.name,s.total_sales,phone
FROM sales_by_client s
JOIN clients c USING(client_id)
WHERE s.total_sales 500练习创建一个客户差额表视图可以看到客户的id名字以及差额发票总额-支付总额USE sql_invoicing;CREATE VIEW clients_balance ASSELECT client_id,c.name,SUM(invoice_total - payment_total) AS balanceFROM clients cJOIN invoices USING(client_id)GROUP BY client_id2. 更新或删除视图Altering or Dropping Views (2:52)小结修改视图可以先DROP在CREATE但最好是用CREATE OR REPLACE视图的查询语句可以在编辑模式下查看和修改但最好是保存为sql文件并放在源码控制妥善管理案例想在上一节的顾客差额视图的查询语句最后加上按差额降序排列法1. 先删除再重建USE sql_invoicing;DROP VIEW clients_balance;
-- 若不存在这个视图用DROP会报错CREATE VIEW clients_balance AS ……ORDER BY balance DESC法2. 用REPLACE关键字即用 CREATE OR REPLACE VIEW clients_balance AS, 这个比较通用不管现在这个视图现在是否已经存在都不会出问题推荐使用种这种方式。USE sql_invoicing;CREATE OR REPLACE VIEW clients_balance AS……ORDER BY balance DESC方法如何保存视图的原始查询语句法1. 推荐方法 将原始查询语句保存为 views 文件夹下的和与视图同名的 clients_balance.sql 文件然后将这个文件夹放在源码控制下put these files under source control, 通常放在 git repository仓库里与其它人共享团队其他人因此能在自己的电脑上重建这个数据库法2. 若丢失了原始查询语句要修改的话可点击视图的扳手按钮打开编辑模式可看到如下被MySQL处理了的查询语句MySQL在前面加了些莫名其妙的东西并且在所有库名表名字段名外套上反引号防止名称冲突当对象名和MySQL里的关键字相同时确保被当作对象名而不是关键字但这都不影响直接做我们需要的修改如加上ORDER BY balance DESC 然后点apply就行了CREATE ALGORITHM UNDEFINED DEFINER rootlocalhost SQL SECURITY DEFINER
VIEW clients_balance ASSELECT c.client_id AS client_id,c.name AS name,SUM((invoices.invoice_total - invoices.payment_total)) AS balanceFROM(clients cJOIN invoices ON ((c.client_id invoices.client_id)))GROUP BY c.client_idORDER BY balance DESC法2是没有办法的办法当然最好还是将 views 保存为 sql 文件并放入源码控制3. 可更新视图Updatable Views (5:12)小结视图除了可用在查询语句SELECT中也可以用在增删改INSERT DELETE UPDATE语句中但后者有一定的前提条件。如果一个视图的原始查询语句中没有如下元素 1. DISTINCT 去重 2. 聚合函数/GROUP BY/HAVING (聚合函数和HAVING筛选通常是伴随GROUP BY分组出现的) 3. UNION 纵向连接则该视图是可更新视图Updatable Views可以增删改否则只能查。另外增INSERT还要满足附加条件视图必须包含底层原表的所有必须字段总之一般通过原表修改数据但当出于安全考虑或其他原因没有某表的直接权限时可以通过视图来修改底层数据前提是视图是可更新的。之后会将关于安全和权限的内容案例创建视图新虚拟表invoices_with_balance带差额的发票记录表USE sql_invoicing;CREATE OR REPLACE VIEW invoices_with_balance AS
SELECT /* 这里有个小技巧要插入表中的多列列名时可从左侧栏中连选并拖入相关列 */invoice_id, number, client_id, invoice_total, payment_total, invoice_date,invoice_total - payment_total AS balance,-- 新增列due_date, payment_date
FROM invoices
WHERE (invoice_total - payment_total) 0
/* 这里不能用列别名balance会报错说不存在
必须用原列名的表达式
之前讲WHERE和HAVING作为事前筛选和事后筛选的区别时提到过 */该视图满足条件是可更新视图故可以增删改删删掉id为1的发票记录DELETE FROM invoices_with_balance
WHERE invoice_id 1改将2号发票记录的期限延后两天UPDATE invoices_with_balance
SET due_date DATE_ADD(due_date, INTERVAL 2 DAY)
WHERE invoice_id 2增在视图中用INSERT新增记录的话还有另一个前提即视图必须包含其底层所有原始表的所有必须字段 例如若这个 invoices_with_balance 视图里没有 invoice_date 字段invoices 中的必须字段那就无法通过该视图向 invoices 表新增记录因为 invoices 表不会接受 invoice_date 字段为空的记录4. WITH CHECK OPTION 子句THE WITH CHECK OPTION Clause (2:18)小结在视图的原始查询语句最后加上 WITH CHECK OPTION 可以防止执行那些会让视图中某些行记录消失的修改语句。案例接前面的 invoices_with_balance 视图的例子该视图与原始的 orders 表相比增加了balance(invouce_total - payment_total) 列且只显示 balance 大于0的行记录若将某记录如2号订单的 payment_total 改为和 invouce_total 相等则 balance 为0该记录会从视图中消失UPDATE invoices_with_balance
SET payment_total invoice_total
WHERE invoice_id 2更新后会发现invoices_with_balance视图里2号订单消失。但在视图原始查询语句最后加入 WITH CHECK OPTION 后对3号订单执行类似上面的语句后会报错UPDATE invoices_with_balance
SET payment_total invoice_total
WHERE invoice_id 3-- Error Code: 1369. CHECK OPTION failed sql_invoicing.invoices_with_balance5. 视图的其他优点Other Benefits of Views (2:37)小结三大优点简化查询、增加抽象层和减少变化的影响、数据安全性具体来讲 首要优点简化查询 simplify queries 增加抽象层减少变化的影响 Reduce the impact of changes视图给表增加了一个抽象层模块化这样如果数据库设计改变了如一个字段从一个表转移到了另一个表只需修改视图的查询语句使其能保持原有查询结果即可不需要修改使用这个视图的那几十个查询。相反如果没有视图这一层的话所有查询将直接使用指向原表的原始查询语句这样一旦更改原表设计就要相应地更改所有的这些查询。 限制对原数据的访问权限 Restrict access to the data在视图中可以对原表的行和列进行筛选这样如果你禁止了对原始表的访问权限用户只能通过视图来修改数据他们就无法修改视图中未返回的那些字段和记录。但注意这通常并不简单需要良好的规划否则最后可能搞得一团乱。了解这些优点但不要盲目将他们运用在所有的情形中。导航下一章将讲解储存过程和函数上一级目录Mosh_完全掌握SQL课程_学习笔记 其它相关数据概要
