Shulou(Shulou.com)06/01 Report--

The view in the database is a virtual table. Like a real table, a view contains a series of named row and column data. Row and column data to freely define the table referenced by the view query and dynamically generate when the view is referenced.

10.1. Overview of the view

A view is derived from one or more tables, and its behavior is very similar to a table, but the view is a virtual table. In the view, users can use select statements to query, and use INSERT, UPDATE, and DELETE to modify records.

A view is a virtual table that is derived from one or more tables in the database. Attempts can also be defined based on existing views. Once the view is defined, it is stored in the database, and the corresponding data is not stored in the database like a table. The data you see through the view is just the data that exists in the basic table.

The main advantages of the view are:

1. Viewpoint concentration

The view set is to make the user only care about the specific data they are interested in and the specific tasks they are responsible for. This improves the security of the data by allowing the user to see only the data defined in the view rather than the data in the view reference table.

two。 Simplify operation

The view greatly simplifies the user's operation of the data. Because when defining a view, if the view itself is the result set of a complex query, it is not necessary to rewrite these complex query statements each time the same query is executed, just a simple query view statement. The visible view hides the complex join operations between tables from the user.

3. Customized data

Views enable different users to see different or the same datasets in different ways. Therefore, this is extremely important when there are many different levels of users sharing the same database.

4. Merge split data

In some cases, because of the large amount of data in the table, the table is often divided horizontally or vertically in the design of the table, but the change of the structure of the table has a negative impact on the application. If you use the view, you can re-maintain the original structural relationship, so that the external schema remains unchanged, and the original application can still reload the data through the view.

5. Security.

Views can be used as a security mechanism. Users can only view and modify the data they can see through the view. Other databases or tables are neither visible nor accessible. If a user wants to access the result set of the view, he or she must be granted access. The access permissions of the tables referenced by the view do not affect the settings of the view permissions.

10.2. Create a view

Syntax for creating views

The CREATE VIEW statement is used to create the view, and its syntax format is:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE means to create a new view

REPLACE means to replace the view you have created

An algorithm for ALGORITHM to represent View selection

UNDEFINED:MySQL automatic selection algorithm

MERGE: combine the view statement used with the view definition so that a part of the view definition replaces the corresponding part of the statement

TEMPTABLE: store the results of the view in a temporary table, and then use the temporary table to execute the statement

View_name is the name of the view, and column_list is the attribute column select_statement that represents the SELECT statement

The WITH [CASCADED | LOCAL] CHECK OPTION parameter indicates that the view is within the scope of the view's permissions when it is updated.

CASCADED: indicates that all relevant views and tables should be met when updating the view

LOCAL: when updating a view, the conditions defined by the view itself can be met

Create a view on a single table

Create a view named view_t on the t table

Mysql > CREATE TABLE t (qty INT, price INT); Query OK, 0 rows affected (0.02 sec) mysql > INSERT INTO t VALUES (3,50); Query OK, 1 row affected (0.02 sec) mysql > CREATE VIEW view_t AS SELECT qty, price, qty * price FROM t; Query OK, 0 rows affected (0.01 sec) mysql > SELECT * FROM view_t +-+ | qty | price | qty * price | +-+ | 3 | 50 | 150 | +-+ 1 row in set (0.00 sec)

Create a view named view_t2 on the t table

Mysql > CREATE VIEW view_t2 (qty, price, total) AS SELECT qty, price, qty * price FROM t politics query OK, 0 rows affected (0.01 sec) mysql > SELECT * FROM view_t2 +-+ | qty | price | total | +-+ | 3 | 50 | 150 | +-+ 1 row in set (0.00 sec)

Create a view on multiple tables

Create a view stu_glass on table student and table stu _ info

Mysql > CREATE TABLE student (id INT, name CHAR (11)); Query OK, 0 rows affected (0.03 sec) mysql > CREATE TABLE stu_info (- > id INT,-> name CHAR (11),-> glass CHAR (11)->); Query OK, 0 rows affected (0.02 sec) mysql > INSERT INTO student VALUES (1), (2)), (3) Query OK, 3 rows affected (0. 01 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > INSERT INTO stu_info VALUES (1, 'wuban','henan'), (2) Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0mysql > CREATE VIEW stu_glass (id,name, glass) AS SELECT student.id,student.name, stu_info.glass FROM student, stu_info WHERE student.id=stu_info.id Query OK, 0 rows affected (0.01sec) mysql > SELECT * FROM stu_glass +-+ | id | name | glass | +-+ | 1 | wanglin1 | henan | | 2 | gaoli | hebei | | 3 | zhanghai | shandong | +-+ 3 rows in set (0.00 sec)

10.3. View the view

A view view is a definition of a view that already exists in the database. You must have SHOW VIEW permission to view the view. The methods to view views are DESCRIBE, SHOW TABLE STATUS, and SHOW CREATE VIEW.

View the definition of the view view_t through the DESCRIBE statement

Mysql > DESCRIBE view_t +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | qty | int (11) | YES | | NULL | | price | int (11) | YES | | NULL | | qty * price | bigint (21) | YES | | NULL | | +- -+-+ 3 rows in set (0.00 sec)

Use the SHOW TABLE STATUS command to view view information

Mysql > SHOW TABLE STATUS LIKE 'view_t'\ G * * 1. Row * * Name: view_t Engine: NULL Version: NULL Row_format: NULL Rows: NULL Avg_row_length: NULL Data_length: NULLMax_data_length: NULL Index_length: NULL Data_free: NULL Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Collation: NULL Checksum: NULL Create_options: NULL Comment: VIEW1 row in set (0.00 sec) ERROR: No query specifiedmysql > SHOW TABLE STATUS LIKE 't'\ G * * 1. Row * Name: t Engine: InnoDB Version: 10 Row_format: Compact Rows: 1 Avg_row_length: 16384 Data_length: 16384Max_data_length: 0 Index_length: 0 Data_free: 7340032 Auto_increment: NULL Create_time: 2017-08-04 19:38:50 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULL Create_options: Comment: 1 row in set (7340032 sec) ERROR: No query specified

SHOW CREATE VIEW views the detailed definition of the view. The code is as follows:

Mysql > SHOW CREATE VIEW view_t\ G * * 1. Row * * View: view_t Create View: CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost`SQL SECURITY DEFINER VIEW `view_ t`AS select `t`.`qty`AS `qty`, `t`.`price`AS `price` (`t`.`qty` * `t`.`price`) AS `qty * price` from `t`character _ set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec) ERROR: No query specified

View the detailed definition of the view in the views table

Mysql > SELECT * FROM information_schema.views\ G * * 1. Row * * TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: stu_glass VIEW_DEFINITION: select `test`.`student`.`id`AS `id`, `test`.`student`.`name`AS `name` `test`.`stu _ info`.`glass`AS `glass`test`.`student`join `test`.`stu _ info`where (`test`.`student`.`id` = `test`.`stu _ info`.`id`) CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8_general_ci** 2. Row * * * TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: view_t VIEW_DEFINITION: select `test`.`t`.`qty`AS `qty` `test`.`t`.`price`AS `price` (`test`.`t`.`qty` * `test`.`t`.`price`) AS `test`.`t` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci** 3. Row * * TABLE_CATALOG: def TABLE_SCHEMA: test TABLE_NAME: view_t2 VIEW_DEFINITION: select `test`.`t`.`qty`AS `qty` `test`.`t`.`price`AS `price`, (`test`.`t`.`qty` * `test`.`t`.`price`) AS `total`from `test`.`t` CHECK_OPTION: NONE IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: utf8COLLATION_CONNECTION: utf8_general_ci3 rows in set (0.00 sec) ERROR: No query specified

10.4. Modify the view

Modify the view refers to modify the view that exists in the database, when some fields of the basic table change, you can modify the view to maintain consistency with the basic table. MySQL modifies the view through CREATE OR REPLACE VIEW statements and ALTER statements.

The basic syntax for using CREATE OR REPLACE VIEW is:

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Modify the view view_t

Mysql > DESC view_t +-+ | Field | Type | Null | Key | Default | Extra | +- -+-+ | qty | int (11) | YES | | NULL | | price | int (11) | YES | | NULL | | qty * price | bigint (21) | YES | | NULL | | +- -+-+ 3 rows in set (0.00 sec) mysql > CREATE OR REPLACE VIEW view_t AS SELECT * FROM t Query OK, 0 rows affected (0.07 sec) mysql > DESC view_t +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | qty | int (11) | YES | | NULL | price | int (11) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec)

The ALTER statement is another way provided by MySQL to modify the view, and its syntax format is:

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

Modify the view view_t using alter statement

Mysql > DESC view_t +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | qty | int (11) | YES | | NULL | | price | int (11) | YES | | NULL | | +-+-+ 2 rows in set (0.00 sec) mysql > ALTER VIEW view_t AS SELECT qty FROM t Query OK, 0 rows affected (0.01 sec) mysql > DESC view_t +-+ | Field | Type | Null | Key | Default | Extra | +-+-+ | qty | int (11) | YES | | NULL | | +-+-+ 1 row in set (0.00 sec)

10.5. Update the view

Updating a view refers to inserting, updating, and deleting data from a table through a view, because the view is a virtual table with no data in it. When you update through the view, you go to the basic table to update it.

Update the view view_t using the update statement

Mysql > SELECT * FROM view_t; / * View views before update * / +-+ | qty | +-+ | 3 | +-+ 1 row in set (0.00 sec) mysql > SELECT * FROM t / * View the table before update * / +-+-+ | qty | price | +-+-+ | 3 | 50 | +-+-+ 1 row in set (0.00 sec) mysql > UPDATE view_t SET qty=5 / * Update view * / Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT * FROM t; / * View updated table * / +-+-+ | qty | price | +-+-+ | 5 | 50 | +-+-+ 1 row in set (0.00 sec) mysql > SELECT * FROM view_t / * View updated view * / +-+ | qty | +-+ | 5 | +-+ 1 row in set (0.00 sec) mysql > SELECT * FROM view_t2 +-+ | qty | price | total | +-+ | 5 | 50 | 250 | +-+ 1 row in set (0.00 sec)

Use the insert statement to insert a record in the base table t

Mysql > INSERT INTO t VALUES (3jue 5); Query OK, 1 row affected (0.02 sec) mysql > SELECT * FROM twitterMutti + | qty | price | +-+-+ | 5 | 50 | 3 | 5 | +-+ 2 rows in set (0.00 sec) mysql > SELECT * FROM view_t2 +-+ | qty | price | total | +-+ | 5 | 50 | 250 | 3 | 5 | 15 | +-+ 2 rows in set (0.00 sec)

Update operations for the view cannot be performed when the view contains the following:

The view does not contain columns defined as non-empty in the base table

Mathematical expressions are used in the list of fields after the SELECT statement that defines the view

Use aggregate functions in the list of fields after the SELECT statement that defines the view

The DISTINCT, UNION, TOP, GROUP BY, or HAVING clause is used in the SELECT statement that defines the view.

10.6. Delete the view

When the view is no longer needed, you can delete it in the syntax format:

DROP VIEW [IF EXISTS] view_name [, view_name]... [RESTRICT | CASCADE]

Delete stu_glass view

Mysql > DROP VIEW IF EXISTS stu_glass;Query OK, 0 rows affected (0.00 sec) mysql > SHOW CREATE VIEW stu_glass;ERROR 1146 (42S02): Table 'test.stu_glass' doesn't exist

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.