MySQL Basics: Views

Mysql View

1. Introduction to the View

1) Meaning of the view:

A view is a virtual table. Unlike a table that contains data, a view contains only queries that retrieve data dynamically when used.

2) Why use Views:

Reuse SQL statements.

simplifies complex SQL operations. After writing a query, it is easy to reuse it without having to know its basic query details.

Use parts of the table instead of the entire table.

　　　Protect data. You can give a user access to a specific part of a table rather than an entire table.　　　　

Change the data format and representation. Views can return data that differs from the representation and format of the underlying table.

Attention:

It is important to know that a view is just a facility for viewing data stored elsewhere. The views themselves do not contain data, so the data they return is retrieved from other tables. When you add or change data in these tables, the view returns the changed data.

3) View rules and Restrictions:

　　　　As with tables, the view must be uniquely named (You cannot give the view the same name as another view or table).

There is no limit to the number of views that can be created.

In order to create a view, you must have sufficient access rights. These limits are typically granted by database administrators.

Views can be nested, that is, you can construct a view with queries that retrieve data from other views.

The order by can be used in the view, but if the data is retrieved from the view the SELECT also contains an order by, then the order by in the view is overwritten.

Views cannot be indexed or have associated triggers or default values.

Views can be used with tables. For example, write a SELECT statement that joins tables and views.

2. Create a View

CREATE   = | | temptable          VIEW    View name   (property manifest)         as   SELECT    statement          with     | LOCAL   CHECK   OPTION;

Algorithm is an optional parameter that represents the algorithm for the view selection.

UNDEFINED: Indicates that MySQL will automatically select the algorithm used;

MERGE: Indicates that the view statement that will be used is combined with the view definition so that some part of the view definition supersedes the corresponding part of the statement;

TempTable: Indicates that the result of the view is stored in a staging table and then executes the statement using a temporary table.

A property manifest is an optional parameter that specifies a noun for each property in the view. The properties that are queried in the SELECT statement are the same by default.

The SELECT statement is a complete query statement that represents a record that satisfies a condition from a table query.

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

Cascaded: Represents the conditions for all related views and tables to be met when the view is updated;

LOCAL: Indicates that you want to meet the definition criteria for the view itself when updating the view.

Note: Create views are divided into: 1) Create views from single table, 2) Create views from multiple tables.

3. View View

DESCRIBE   view name; SHOW  TABLE  STATUS like     ' view name '; SHOW  CREATEview     name;
SELECT * from  information_schema.views;

The first two are basic information for viewing views, and the last two are details for viewing views.

4. Modify the View

Modifying a view refers to modifying the fields of the tables contained in the view.

1) CREATE OR REPLACE View Modify views

CREATE   OR  REPLACE  = | | temptable                       VIEW    View name   (property manifest)                      as   SELECT    statement                       with     | LOCAL   CHECK   OPTION;

2) ALTER statement

ALTER   = | | temptable         VIEW    View name   (property manifest)        as   SELECT    statement         with     | LOCAL   CHECK   OPTION;

Attention:

Create or REPLACE view modifies an existing view or creates a view. and alter can only modify the view.

5. Update the View

Update view refers to inserting, updating, and deleting data from a table by using a viewport.

Because the view is a virtual table with no data. Updating through view updates is done by switching to the base table. And only the data within the permission range can be updated, and the out of range cannot be updated.

Can be updated by INSERT, UPDATE, delete.

Attention:

The view cannot be updated in these situations:

(1) functions such as SUM (), COUNT (), MAX (), MIN () are protected in the view;

(2) The view contains the Union, union All, DISTINCT, GROUP by, having and other keywords;

(3) constant view;

(4) The Select in the view contains a subquery;

(5) A view exported from a non-updatable view;

(6) When creating the view, the algorithm is the temptable type;

(7) A column with no default values exists on the table corresponding to the view, and the column is not included in the view.

6. Delete a view

DROP  VIEW  [IF EXISTS]   List of view names   [RESTRICT | CASCADE];

MySQL Basics: Views