Functions of mysql view and mysql View

Functions of mysql view and mysql View

A view is a virtual table that is exported from one or more tables in the database. A view can also be defined based on an existing view. In the database, only view definitions are stored, and no data in views is stored in the original table. The VIEW does not support the input record function to create a VIEW. Format: create [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW name [(attribute list)] as select statement [WITH [CASCADED | LOCAL] check option]; Note: ALGORITHM is an optional parameter that indicates the ALGORITHM selected by the view. The attribute list is an optional parameter that specifies the nouns of each attribute in the view, by default, it is the same as the query attribute in the select statement. with check option is an optional parameter, which indicates that the view must be updated within the permission range of the view. In ALGORITHM, the undefined option indicates that mysql will automatically select the ALGORITHM to be used. The merge option indicates that the statement using the view is merged with the view definition, so that a part of the view definition replaces the corresponding part of the statement; the temptable option stores the view results to a temporary table, and then runs the statement in the temporary table. CASCADED indicates that all the related views and tables must be met when updating a view. This parameter is the default value. local indicates that the view must meet the defined conditions of the view. If no attribute list is specified, the attribute name of the view is the same as the attribute name queried by the select statement. Note 1: when creating a view, it is best to add the with check option parameter and the CASCADED parameter. In this way, after the new view is derived from the view, the constraints of its parent view must be considered when the new view is updated. This method is strict to ensure data security. NOTE 2: You must have the create view permission and select permission to create a view. You can use the select statement to query the permission information stored in the user table under the mysql database. Select Select_priv, Create_view_priv from mysql. user where user = 'username'; 1. view the View: describe statement, show create view statement, show table status statement, views table 2 in the information _ schema database, modify view 1, create or replace statement to modify view create or replace [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW name [(attribute list)] as select statement [WITH [CASCADED | LOCAL] check option]; Note: The create or replace statement can not only modify existing views, but also create new views. 2. alter statement: alter [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW name [(attribute list)] as select statement [WITH [CASCADED | LOCAL] check option]; Note: alter can only modify existing views. 3. update a view update view refers to insert, update, and delete data in a view. View updates are converted to basic tables for update. Update a view can only update data within the permitted range. If the view is out of range, it cannot be updated. For example, the update view name is set name = 'A'. After the update, the basic table content is also updated. The view cannot be updated in the following situations: 1. The view contains sum (), count (), max (), min () and other functions 2, union, union all, distinct, group by, having and other keywords. 3. Constant View 4. select in the view contains subquery 5. View exported from non-updatable views 6. When creating a view, ALGORITHM is of the TEMPTABLE type, this type of temporary table cannot be updated. 7. There are columns with no default values on the table corresponding to the view, and the column is not included in the view. Because, when updating a view, this record without default values will be inserted with no values or null values. The database system does not allow this situation and will prevent view updates. 8. WITH [CASCADED | LOCAL] check option also determines whether the view can be updated. The local parameter indicates that the update view meets the definition conditions of the view. The CASCADED parameter indicates that the update view must meet the conditions of all related views and tables. If not specified, the default value is CASCADED. Iv. Delete view Note: deleting a view does not affect the data format in the basic table: drop view [if exists] view name list [restrict | cascade]; if exists parameter indicates that the view exists, if the view name does not exist, it is not executed. The "view Name List" parameter indicates the name list of views to be deleted. view names are separated by commas. 1. Check whether the drop permission exists. select drop_priv from mysql. user where user = 'username ';