mysql:view

view:一张虚拟表： 可以从中取出数据

view是一张虚拟表：并非仅仅存在于 内存中， 而是存在于 磁盘上，
说view是虚拟的， 是因为 view 是对 底层table 的代理、过滤器，

通过view可以限制特定用户对特定table数据的访问，
用户只能访问 the query defined the view：
the SELECT statement that defines the view

也就是 select 指定范围内的数据，

view存在于磁盘上， 当view 依赖的 the underlying tables 不存在了，
You can use the CHECK TABLE statement to check whether the view is valid or not.
你也可以删除 drop view

database objects： 数据库对象

包括 views, tables

参考、

http://www.mysqltutorial.org/mysql-views-tutorial.aspx

什么是view:

A database view is a virtual table or logical table which is defined as a SQL SELECT query with joins.

MySQL, allow you to update data in the underlying tables through the database view with some prerequisites.

mysql> show create view saleperorder\G;
*************************** 1. row ***************************
                View: saleperorder
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `saleperorder` AS select `orderdetails`.`orderNumber` AS `orderNumber`,sum((`orderdetails`.`quantityOrdered` * `orderdetails`.`priceEach`)) AS `total` from `orderdetails` group by `orderdetails`.`orderNumber` order by `total` desc
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

1. 可以通过view更新数据， 底层的 tables 的数据也会变化。
   也就是能通过view来修改底层 tables的数据，

A database view is dynamic because it is not related to the physical schema. The database system stores views as a SQL SELECT statement with joins. When the data of the tables changes, the view reflects that changes as well.

2. 不可以修改的 view

view的好处：

A database view allows you to simplify complex queries
A database view helps limit data access to specific users
A database view provides extra security layer
A database view enables computed columns
A database view enables backward compatibility

1. 简化复杂的查询
2. 针对特定的用户限制其对特定tables的访问
3. 提供了额外的安全层
4. 支持 computed columns
5. 支持向后兼容

view的坏处：

Performance:
Tables dependency：

查询view数据相对来说是比较慢的， 尤其是 view建立在其他的view基础上。
view 依赖于 the underlying tables ， 当 the underlying tables 结构被改变， view 也要被改变。

mysql中views的实现

the query defined the view：
the SELECT statement that defines the view

MySQL supports versioning system for views

MySQL allows you to create a view based on other views

You cannot create an index on a view

You can use the CHECK TABLE statement to check whether the view is valid or not.

A simple view can be updatable.

select statement: the SELECT statement that defines the view

• The SELECT statement can contain a subquery in WHERE clause but not in the FROM clause.

支持 select ... from table_name where col_name in subqueries

不支持 select ... from subqueries

create an updatable view:

update data in the underlying table through the view.
通过view 更新底层 table 数据

It means that you can use the INSERTor UPDATE statement to insert or update rows of the base table through the updatable view. In addition, you can use DELETE statement to remove rows of the underlying table through the view.

However, to create an updatable view, the SELECT statement that defines the view must not contain any of the following elements:

• Aggregate functions such as MIN, MAX, SUM, AVG, and COUNT.
• DISTINCT
• GROUP BY clause.
• HAVING clause.
• UNION or UNION ALL clause.
• Left join or outer join.
• Subquery in the SELECT clause or in the WHERE clause that refers to the table appeared in the FROM clause.
• Reference to non-updatable view in the FROM clause.
• Reference only to literal values.
• Multiple references to any column of the base table.

with check option clause

prevents you from updating or inserting rows that are not visible through the view.

阻止 undatable views （可修改的view）修改 view 看不到的 rows

也就是说， view 只能修改 the SELECT statement that defines the view
范围内的rows，
例如： select ... where a >20
则 view 不能修改 insert .... where a = 30

whenever you update or insert a row of the base table through a view, MySQL ensures that the insert or update operation is conformed with the definition of the view.

with cascaded check option & with local check option

Because MySQL allows a view to be created based on another view, it also checks the rules in the dependent views for consistency.

To determine the scope of check, MySQL provides two options: LOCAL and CASCADED. If you don’t specify the keyword explicitly in the WITH CHECK OPTION clause, MySQL uses CASCADED by default.

When a view uses a WITH CASCADED CHECK OPTION, MySQL checks the rules of the view and also the rules of the underlying views recursively.

with cascaded check option ： mysql 递归 检查 新数据 是否符合 view的定义规则，不管 依赖的 view 有没有指定 with check option ， 都会严格执行 view 只能修改 看得到的rows

而针对 with local check option: mysql 递归检查 新数据 是否符合 view的定义规则： 但是 如果u 依赖的 view 没有指定 with check option， 则 跳过 检查， 相对于 cascaded 来说， 要求更低了

managing views

MySQL provides the SHOW CREATE VIEW statement that displays the view’s definition.

展示 view 的定义；

2. modifying views

MySQL provides two statements that allow you to modify an existing view: ALTER VIEW and CREATE OR REPLACE VIEW

Once a view is created, you can modify it using the ALTER VIEW statement.

REPLACE VIEW statement to either create or replace an existing view. If a view already exists, MySQL simply modifies the view. In case the view does not exist, MySQL creates a new view.