View of MySQL

A view is a table that is exported from one or more tables. is a virtual existence of a table. A view is like a window through which you can see the data that is specifically provided by the system. This allows the user to not see the data in the entire database table, but only the data that is useful to them. The view can make the user's operation more convenient, and can guarantee the security of the database system.

only the definition of the view is stored in the database, and the data in the view is not stored. the data is stored in the original table, and the database system pulls the corresponding data from the original table when querying the data using the view. Therefore, the data in the view is dependent on the data in the original table. As soon as the data in the table changes, the data displayed in the view changes.

A view is an SQL statement of a query stored in a database, and it is mainly for two reasons: security reasons, the view can hide some data, such as: Employee Information table, you can display only name, length of service, address, not social security number and wages, etc., another reason is to make complex queries easy to understand and use.

The role of the view

The view acts like a filter for the underlying table referenced in it. A filter that defines a view can come from one or more tables or other views of the current or other database. There are no restrictions on querying through views, and there are few restrictions on data modification through them. The following points summarize the function of the view as follows:

1. Simplicity

What you see is what you need. Views not only simplify the user's understanding of the data, but also simplify the operation of the data. Queries that are used frequently can be defined as views, so that users do not have to specify all the conditions for each subsequent operation.

2. Security

The security of a view prevents unauthorized users from viewing specific rows or columns, and permissions users can only see the following methods for specific rows in the table:

(1) Add a column in the table that marks the user name;

(2) To establish a view, users can only see the line marked with their own user name;

(3) Delegate the view to other users.

3. Logical Data Independence

Views enable applications and database tables to be somewhat independent. If there is no view, the program must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view. Views can make programs and data independent in the following ways:

(1) If the application is built on a database table, when the database table changes, the view can be established on the table, the change of the table is masked through the view, and the application can not move.

(2) If the application is built on a database table, when the application changes, you can establish a view on the table, masking the changes to the application through the view, so that the database table does not move.

(3) If the application is built on a view, when the database table changes, the view can be modified on the table, and the table can be masked by the change of the view, so that the application does not move.

(4) If the app is built on a view, when the app changes, you can modify the view on the table, masking the app's changes through the view, so the database can be fixed.

Create a View

Creating a view refers to establishing a view on a database table that already exists. Views can be built into a single table or in multiple tables.

creating a view requires permission to create view, and you should have SELECT permission for the columns involved in the query. You can query these permission information using the SELECT statement , which is as follows:

SELECT Select_priv, Create_view_priv from Mysql.user WHERE user= ' root ';

In MySQL, creating views is implemented through the CREATE VIEW statement. Its syntax is as follows:

CREATE [algorithm={undefined| merge| TempTable}] view name [(property list)]

As SELECT statement With [cascaded| LOCAL] CHECK OPTION;

Algorithm is an optional parameter that represents the algorithm of the view selection;

The view name parameter represents the name of the view to be created;

The property manifest is an optional parameter that specifies the nouns for each property in the view, which by default is the same as the property queried in the SELECT statement;

The SELECT statement parameter is a complete query statement that identifies certain records that satisfy the criteria from a table and imports those records into the view;

With CHECK option is an optional parameter that indicates that the view should be updated with permission within that view.

CREATE Algorithm=VIEWbook_view1 (A_sort, A_talk, A_books, a_name) asSELECT sort, talk, books, Tb_user.name  from WHERE tb_book.id=tb_user.idwithCHECKOPTION;

Here are a few things to keep in mind when creating views:

(1) The statement that runs the CREATE view requires the user to have the CREATE VIEW permission, and if [or replace] is added, the user has permission to delete the view (drop view);

(2) The SELECT statement cannot contain subqueries from the FROM clause;

(3) The SELECT statement cannot refer to system or user variables;

(4) The SELECT statement cannot refer to the preprocessing statement parameters;

(5) Within the stored subroutine, the definition cannot refer to subroutine parameters or local variables;

(6) The table or view referenced in the definition must exist. However, when you create a view, you can discard the table or view that defines the reference. To check whether the view definition has such problems, you can use the Check table statement;

(7) The temporary table cannot be referenced in the definition, and the temporary view cannot be created;

(8) A table named in the view definition must already exist;

(9) The triggering program cannot be associated with the view;

(10) An order by is allowed in the view definition, but it is ignored if a selection is made from a specific view that uses a statement with its own order by.

View View

View view refers to viewing a view that already exists in the database. View must have the Show view permission. The methods for viewing views include describe statements, show TABLE status statements, show CREATE view statements, and so on. select * from Information_schema.views;

1.DESCRIBE statements

Describe can be abbreviated to DESC, the format of the DESC statement is as follows:

DESCRIBE view name;

2.SHOW TABLE Status Statement

In MySQL, you can use the show TABLE status statement to View information for a view . The syntax format is as follows:

SHOW TABLE STATUS like ' view name ';

"Like" means a string followed by a match;

The view name parameter refers to the name of the view you want to view, which you need to define in single quotation marks.

3.SHOW CREATE View Statement

In MySQL, the show CREATE view statement can see a detailed definition of the view . The syntax format is as follows:

SHOW CREATE View Name

Modify a View

Modifying a view refers to modifying the definition of a table that already exists in the database. When some fields of the base table change, you can maintain consistency between the view and the base table by modifying the view. MySQL modifies the view by using the Create OR REPLACE view statement and the ALTER statement.

1.CREATE OR REPLACE VIEW

In MySQL, the CREATE OR REPLACE View statement can be used to modify the view. The use of this statement is very flexible. when the view is already present, the view is modified, and when the view does not exist, it can be created. The syntax for the CREATE OR REPLACE view statement is as follows:

CREATE OR REPLACE [algorithm={undefined| merge| TempTable}]

View views [(Property list)] As SELECT statement

with [cascaded| LOCAL] CHECK OPTION;

2.ALTER

The ALTER VIEW statement alters the definition of the view, including the indexed view, but does not affect the stored procedure or trigger on which it is dependent. The statement has the same limitations as the CREATE VIEW statement, and if you delete and rebuild a view, you must reassign the permissions to it.

The syntax for the ALTER VIEW statement is as follows:

Alter VIEW [algorithm={merge|temptable|undefined}]

View View_name [(column_list)]

As select_statement with [cascaded|local] Check option;

PS: When creating a view, when using the WITH CHECK option, with encryption, with schemabing or View_metadata option, if you want to preserve the functionality provided by these options, you must have the ALTER Include them in the view statement.

Update view

An update to a view is actually an update to the table, which refers to the data in the Insert (insert), update, and delete tables through the viewport. because the view is a virtual table, there is no data in it. When you update through a view, it is converted to the base table to update. When you update a view, only the data within the permission range is updated. is out of range and cannot be updated.

1. Update the View

Similar to the update used for the table

2. Updating the limits of a view

Not all views can be updated, and there are several scenarios in which views cannot be updated:

(1) The view contains functions such as count (), SUM (), MAX (), and Min (). For example:

CREATE VIEW book_view1 (A_sort, A_book)

As SELECT sort, books, COUNT (name) from Tb_book;

(2) The views contain keywords such as union, union All, DISTINCT, GROUP by, and Havig. For example:

CREATE VIEW book_view1 (A_sort, A_book)

As SELECT sort, books, from Tb_book GROUP by ID;

(3) A constant view. For example:

CREATE VIEW Book_view1

As SELECT ' Aric ' as A_book;

(4) The Select in the view contains subqueries. For example:

CREATE VIEW Book_view1 (a_sort)

As select (select name from Tb_book);

(5) A view that is exported by a non-updatable view. For example:

CREATE VIEW Book_view1

As Select*from book_view2;

(6) When creating a view, algorithm is the temptable type. For example:

CREATE algorithm=temptable

VIEW Book_view1

As Select*from Tb_book;

(7) A column with no default values exists on the table corresponding to the view, and the column is not included in the view. For example, the Name field contained in the table does not have a default value, but the field is not included in the view. Then this view cannot be updated. Because, when the view is updated, this record without a default value will have no value inserted and no null value inserted. The database system will not allow this to happen, and it will prevent this view from being updated.

The above situation is actually a situation, the rule is that the view data and the basic table data is not the same.

PS: Although the data can be updated in the view, there are many limitations. In general, it's a good idea to use a view as a virtual table for querying data instead of updating the data through a view. Because when you use a view to update data, you may fail to update data if you do not fully consider the limitations of updating data in the view.

Delete a view

Deleting a view means deleting a view that already exists in the database. When you delete a view, only the definition of the view is deleted and the data is not deleted . In MySQL, you can use the Drop VIEW statement to delete a view, but the user must have drop permissions.

The syntax for the DROP view statement is as follows:

DROP View IF EXISTS view name > [restrict| CASCADE]

The IF exists parameter refers to whether the view exists, executes if it exists, does not exist;

The view name list parameter represents the name and list of the view that you want to delete, separated by commas between each view name.

The statement removes the specified view definition from the data dictionary, or if the view exports other views, either by using the Cascade Cascade Delete, by explicitly deleting the exported view, deleting the view, and deleting the base table, all view definitions exported by the base table must be explicitly deleted.

View of MySQL