Mysql view learning notes

Mysql5.1 has the view function. The following articles will introduce some considerations for mysql views, creation, deletion, modification, and creation. I hope this article will be helpful to you.

Create 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]

The corresponding syntax variable information is as follows:

[Or replace]
The or replace keyword in the brackets is optional. If the view with the specified name already exists in the current database, this keyword is not found, an error message is displayed. If the or replace keyword is used, the view being created will overwrite the view with the same name.
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
The ALGORITHM clause is optional. It indicates the ALGORITHM used to process the view. In addition, it is not a part of standard SQL, but a Function Extension of standard SQL by MySQL. ALGORITHM can be set to MERGE, TEMPTABLE, or UNDEFINED. If the ALGORITHM clause is not available, the default value is UNDEFINED (UNDEFINED ).
For MERGE, the text of the statements that reference the view is combined with the view definition, so that a part of the view definition replaces the corresponding part of the statement.

For TEMPTABLE, the view result is placed in a temporary table and then executed using it.

For UNDEFINED, MySQL selects the algorithm to be used. If possible, it tends to be MERGE rather than TEMPTABLE, because MERGE is generally more effective, and if a temporary table is used, the view cannot be updated.

The TEMPTABLE option is provided because TEMPTABLE can release the lock on the base table after creating a temporary table and before completing the statement processing. Compared with the MERGE algorithm, the lock release speed is faster, so that other clients using the view will not be blocked for a long time.

In addition, the MERGE algorithm requires that rows in the view have a one-to-one relationship with rows in the base table. If the view contains Aggregate functions (SUM (), MIN (), MAX (), COUNT (), etc), DISTINCT, group by, HAVING, UNION or union all, or any referenced text value (for example, SELECT 'hello';) in a base table, the one-to-one relationship is lost, and temporary tables must be used instead.
[(Column_list)]
(Column_list) is used to customize the names of fields in the view. If this command option is not available, the names of the fields queried by the view are consistent with those of the data table used by the view. The following is a common SQL statement used to create 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 called id and username, the two field names in the v_user view are also id and username by default. Now, the field names in the v_user view are defined as 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 a view. In short, this is a user-defined SELECT statement.
[WITH [CASCADED | LOCAL] check option]
CASCADED in this OPTION is the default value, and local check option is used to prevent inserting or updating rows in updatable views. This option is generally not used, so we will not go into details. For more information, see the relevant information on the MySQL official website.

1. Example
For example, create a product table and a purchase record table (purchase), and then query the details of the purchase using the view purchase_detail.

The Code is as follows:

Copy code

Create table product ( Product_id int not null, Name VARCHAR (50) not null, Price DOUBLE NOT NULL ); Insert into product VALUES (1, 'apple', 5.5 ); Create table purchase ( Id int not null, Product_id int not null, Qty int not null default 0, Gen_time DATETIME NOT NULL ); Insert into purchase VALUES (1, 1, 10, NOW ()); Create view purchase_detail as select product. name as name, product. price as price, purchase. qty as qty, product. price * purchase. qty as total_value from product, purchase where product. product_id = purchase. product_id;

After the creation is successful, enter: SELECT * FROM purchase_detail;

The running effect is as follows:

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

| Name | price | qty | total_value |

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

| Apple | 5.5 | 10 | 55 |

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

1 row in set (0.01 sec)

1. Notes

Note the following when creating a view:

(1) To run the statement for creating a VIEW, you must have the permission to create a VIEW (crate view). If [or replace] is added, you must also have the permission to delete the VIEW (drop view) permissions;

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

(3) SELECT statements cannot reference system or user variables;

(4) SELECT statements cannot reference preprocessing statement parameters;

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

(6) The table or view referenced in the definition must exist. However, after creating a view, you can discard the referenced table or view. To CHECK whether view definitions have 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) The table named in the view definition must already exist;

(9) The trigger program and view cannot be associated;

(10) order by can be used in the view definition. However, if you select from a specific view and the view uses a statement with its own order by, it will be ignored.

Delete View

The method for deleting a view in MySQL is very simple. The detailed syntax is as follows:

-- Delete one or more views with the specified name

The Code is as follows:	Copy code
Drop view [if exists] View_name [, view_name2]...

The keyword "if exists" is used to prevent errors because the view does not exist. In this case, only the view is deleted. The drop view statement can delete multiple views at a time. You only need to separate multiple VIEW names with commas. If multiple views exist in different databases, the view name of the current database must be prefixed with db_name.

-- Delete view v_user
Drop view v_user;

1. Example

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

Eg2. delete an unknown VIEW: drop view if exists test_view;

Eg3. Delete multiple views: drop view if exists test_view1, test_view2;

1. Notes

The drop view permission must be granted to one or more views to be deleted.

Modify View

See the or replace keyword in the create view syntax. If a view creation statement with this keyword is used, the SQL statement of the view is modified.
View

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

The Code is as follows:	Copy code
-- Execute show tables Mysql> show tables; -- The output result is as follows: + ---------------- + | Tables_in_test | + ---------------- + | User | | V_user | + ---------------- + 2 rows in set (0.00 sec)

However, simply using the show tables Statement, in the output results, we cannot tell which are the views and which are the real data tables (of course, the view name can start with "v ). In this case, run the show full tables command to list the extra table_type columns. If the value of this column on the corresponding output line is "VIEW", this is a VIEW.

The Code is as follows:	Copy code
-- Execute show full tables Mysql> show full tables; -- The output result is as follows: + ---------------- + ------------ + | Tables_in_test | Table_type | + ---------------- + ------------ + | User | base table | | V_user | VIEW | + ---------------- + ------------ + 2 rows in set (0.00 sec)

After finding the view we need through the above command, we can use the following command to view the detailed statement for creating the View:

Show create view view_name

For example, we can use this command to view the SQL statement for creating a view v_user:

The Code is as follows:

Copy code

-- Because the output results of the statement are messy, use the G command to format the output. Mysql> show create view v_user G; -- The following is the formatted output result. * *************************** 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. Example
For example, modify the view purchase_detail created in the previous section and remove the qty column. The statement is 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;

In this case, the query result is as follows: select * from purchase_detail:

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

| Name | price | total_value |

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

| Apple | 5.5 | 55 |

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