MySQL view learn to note records

Create a View

In MySQL, the complete syntax for creating a view is as follows:

CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}]
VIEW view_name [(column_list)]
As Select_statement
[With [cascaded | Local] CHECK OPTION

Its corresponding syntax variable information is as follows:

[OR REPLACE]
The or replace keyword in brackets is optional. If a view with the specified name already exists in the current database, the error message is prompted, and if the or replace keyword is used, the view that is currently being created overwrites the view with the same name.
[algorithm = {UNDEFINED | MERGE | TempTable}]
The algorithm clause is optional, which indicates which algorithm is used to process the view. In addition, it is not part of standard SQL, but rather a feature extension of MySQL to standard SQL. Algorithm can set three values: MERGE, temptable, or undefined. If there is no algorithm clause, the default value is undefined (undefined).
For merge, the text of the statement referencing the view is merged with the view definition, so that a part of the view definition replaces the corresponding part of the statement.

For temptable, the result of the view is placed in a temporary table and then used to execute the statement.

For Undefined,mysql, you will select the algorithm you want to use. If possible, it tends to merge rather than temptable, because the merge is usually more efficient, and if you use a temporary table, the view is not updatable.

The temptable option is provided because TempTable can release a lock on the base table after the temporary table is created and before the statement processing is complete. Locking is released faster than the merge algorithm, so that other clients that use the view are not masked for too long.

In addition, the merge algorithm requires a one-to-one relationship between the rows in the view and the rows in the base table. If the view contains aggregate functions (SUM (), MIN (), MAX (), COUNT (), DISTINCT, GROUP by, having, union, or union all, reference literal values that do not have a base table (for example: SELECT ' Hello ';) Any of these structures will lose a one-to-one relationship, and temporary tables must be used instead.
[(column_list)]
(column_list) is used to customize the names of each field in the view. If this command option is not available, the names of the fields that are queried through the view are consistent with the field names of the data tables used by the view. The following is a common SQL statement for creating a view:

The code is as follows	Copy Code
CREATE OR REPLACE VIEW v_user As SELECT ID, username from user;

Because the field names in the user table are ID and username, the two field names in the view V_user also default to IDs and username respectively. Now, we'll customize the field names in view V_user to UID and uname respectively.

The code is as follows	Copy Code
CREATE OR REPLACE VIEW v_user (uid, uname) As SELECT ID, username from user;

Select_statement
Select_statement is used to specify the content definition of the view. In short, this is a user-defined SELECT statement.
[With [cascaded | Local] CHECK OPTION
The cascaded in this option is the default, and local CHECK option is used to prevent rows from being inserted or updated in an updatable view. Because this option is generally not used, so do not repeat the details, please refer to the MySQL official website on the relevant information.

1. Use examples
Eg. This example creates a product table (products) and a Purchase record table (purchase), and then queries the purchase details through the view Purchase_detail.

code is as follows

copy code

CREATE TABLE product ( product_id INT not NULL, Name VARCHAR () not NULL, Price DOUBLE NOT null ); INSERT into Product VALUES (1, ' Apple ', 5.5); CREATE TABLE Purchase ( ID int not NULL, PRODUCT_ID int does null, Qty int NOT NULL DEFAULT 0, Gen_time DATETIME N OT NULL ); INSERT into purchase VALUES (1, 1, and now ()); CREATE VIEW Purchase_detail as SELECT product.name as name, product. Price as Price, Purchase.qty as qty, product. Pric E * Purchase.qty as Total_value from product, purchase where product.product_id = purchase.product_id;

Upon successful creation, enter: SELECT * from Purchase_detail;

The operation effect is as follows:

+-------+-------+-----+-------------+

| name | Price | Qty | Total_value |

+-------+-------+-----+-------------+

| Apple | 5.5 | 10 | 55 |

+-------+-------+-----+-------------+

1 row in Set (0.01 sec)

1. Matters needing attention

Creating a view has the following considerations:

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

(2) A SELECT statement cannot contain a subquery in the FROM clause;

(3) A SELECT statement cannot refer to a system or user variable;

(4) A SELECT statement cannot reference a preprocessing statement parameter;

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

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

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

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

(9) The trigger cannot be associated with the view;

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

Delete View

The way to delete a view in MySQL is simple, with detailed syntax as follows:

--Deletes one or more views of the specified name

The code is as follows	Copy Code
DROP VIEW [IF EXISTS] view_name [, View_name2] ...

Where the keyword if exists is used to prevent errors from being prompted because the view does not exist, the deletion is performed only if the view is present. The drop VIEW statement deletes multiple views at once, separated by an English comma between multiple view names. If more than one view exists in a different database, you must precede the view name of the current database with the db_name. prefix.

--Delete View V_user
DROP VIEW V_user;

1. Use examples

Eg1. Delete the view created in the previous section Purchase_detail:drop view Purchase_detail;

Eg2. Deletes an unknown view: Drop view IF EXISTS Test_view;

Eg3. Delete Multiple Views: Drop View IF EXISTS test_view1, test_view2;

1. Matters needing attention

You must have drop View permissions on one or more views that you want to delete.

Modify View

Refer to the OR Replace keyword in the CREATE VIEW syntax, as long as the view creation statement with that keyword is the SQL statement that modifies the view.
View View

In MySQL, show tables can be used not only to see which data tables exist in the current database, but also to see which views exist in the current database.

The code is as follows	Copy Code
--Execute Show tables Mysql> Show tables; --Here are the output results +----------------+ | Tables_in_test | +----------------+ | user | | V_user | +----------------+ 2 rows in Set (0.00 sec)

However, just using the show tables statement, in the output, we simply cannot distinguish between what is the view and which is the real data table (of course, the name of the view we can unify the agreement to start with "v_"). At this point, we need to use the command to show the full table, which lists the extra table_type columns, which is a view if the value of the column on the corresponding output row is "view."

The code is as follows	Copy Code
--Execute Show full tables Mysql> Show full tables; --Here are the output results +----------------+------------+ | Tables_in_test | Table_type | +----------------+------------+ | user | BASE TABLE | | V_user | VIEW | +----------------+------------+ 2 rows in Set (0.00 sec)

After we have found the view we need through the above command, we can use the following command to view the detailed statement that created the view:

Show CREATE VIEW view_name

For example, we use this command to view SQL statements that create VIEW V_user:

The code is as follows

Copy Code

--because the output of the statement is more cluttered, the G command is used to format the output Mysql> Show CREATE View V_user G; --The following is the formatted output 1. Row *************************** View:v_user Create view:create algorithm=undefined definer= ' root ' @ ' localhost ' SQL security definer View ' V_user ' as select ' user '. ' ID ' As ' uid ', ' user '. ' username ' As ' uname ' from ' user ' Character_set_client:utf8 Collation_connection:utf8_general_ci 1 row in Set (0.00 sec)

1. Use examples
Eg. Modify the view Purchase_detail created in the previous section to remove the Qty column, as follows:

The code is as follows	Copy Code
ALTER VIEW Purchase_detail as SELECT product.name as name, product. Price as price, product. Price * Purchase.qty as Total _value from product, purchase where product.product_id = purchase.product_id;

At this point, the results are as follows: SELECT * from Purchase_detail, and when you query the view:

+-------+-------+-------------

| name | Price | Total_value |

+-------+-------+-------------+

| Apple | 5.5 | 55 |

+-------+-------+-------------+