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

This article mainly introduces the operation of MySQL DDL statement view method, the content of the article is carefully selected and edited by the author, with a certain pertinence, for everyone's reference significance is still relatively great, the following with the author to understand the operation of MySQL DDL statement view method.

1. Background

* views are virtual tables in the database. Contains a series of row and column data with names. The view is derived from one or more tables, and the behavior of the view is very similar to that of the table. Users can use select statements to query data, and use INSERT, UPDATE and DELETE to modify records. The view makes the user more convenient and ensures the security of the database system.

* once the view is defined, it is stored in the database, and the corresponding data is not stored in the database like the table. The data seen through the view is only the data stored in the basic table. You can query, modify, and delete views as well as tables. When the data seen through the view is modified, the data of the corresponding basic table will also change, and if the data of the basic table is released, the change will be automatically reflected in the view.

two。 View function

* make the query very clear, the data stored in the view is the data we want, and can simplify the user operation.

* make the data more secure, the data in the view does not exist in the view, or in the basic table, through the view relationship, we can effectively protect our important data

* improve the logical independence of the table, and the view can shield the impact of changes in the original table structure.

3. View Typ

* MERGE: merges the text of the statement referencing the view with the view definition so that a certain part of the view definition replaces the corresponding part of the statement.

* TEMPTABLE: the result of the view will be placed in a temporary table and then used to execute the statement.

* UNDEFINED: algorithm used by default. MySQL prefers MERGE to TEMPTABLE because MERGE is generally more efficient.

4. Example of view type

* create a base table users

Mysql > CREATE TABLE users (- > id BIGINT PRIMARY KEY NOT NULL AUTO_INCREMENT,-> name VARCHAR (64) NOT NULL,-> sex ENUM ('M é,'F') NOT NULL,-> age INT NOT NULL->) ENGINE=INNODB CHARSET=utf8mb4;Query OK, 0 rows affected (0.15 sec)

* insert data into the base table users

Mysql > INSERT INTO users VALUES (NULL, 'tom',' sec, 23), (NULL, 'jak',' Fidelity, 32), (NULL, 'jus',' sec, 35); Query OK, 3 rows affected (0.04 sec) Records: 3 Duplicates: 0 Warnings: 0

* View base table data

Mysql > SELECT * FROM users;+----+ | id | name | sex | age | +-- + | 1 | tom | M | 23 | 2 | jak | F | 32 | 3 | jus | M | 35 | +-+ 3 rows in set (0.00 sec)

* create user id and name vusers1 views of type UNDEFINED

Mysql > CREATE ALGORITHM=UNDEFINED VIEW vusers1 AS SELECT id, name FROM users;Query OK, 0 rows affected (0.01 sec)

* analyze and view the vusers1 view execution plan

[temporary table not used]

Mysql > EXPLAIN SELECT * FROM vusers1 +-- + | id | select_type | Table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | users | NULL | ALL | NULL | 3 | 100.00 | NULL | +-+-- -+-+ 1 row in set 1 warning (0.00 sec)

* create user id and name vusers2 views of type merge

Mysql > CREATE ALGORITHM=MERGE VIEW vuser2 AS SELECT id, name FROM users;Query OK, 0 rows affected (0.03 sec)

* analyze and view the vusers2 Vision implementation Plan

[temporary table not used]

Mysql > EXPLAIN SELECT * FROM vuser2 +-- + | id | select_type | Table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +- -+-+ | 1 | SIMPLE | users | NULL | ALL | NULL | 3 | 100.00 | NULL | +-+-- -+-+ 1 row in set 1 warning (0.01 sec)

* create user id and name vusers3 views of type TEMPTABLE

Mysql > CREATE ALGORITHM=TEMPTABLE VIEW vuser3 AS SELECT id, name FROM users;Query OK, 0 rows affected (0.19 sec)

* analyze and view the vusers3 Vision implementation Plan

[temporary tables are used]

Mysql > EXPLAIN SELECT * FROM vuser3 +-+ | id | | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +-+ -- + | 1 | PRIMARY | | NULL | ALL | NULL | 3 | 100.00 | NULL | 2 | DERIVED | users | NULL | ALL | NULL | 3 | 100.00 | NULL | + -+ 2 rows in set 1 warning (0.00 sec)

5. View view information

* desc view basic view information

Mysql > desc vusers1 +-+ | Field | Type | Null | Key | Default | Extra | +-+- -+ | id | bigint (20) | NO | | 0 | | name | varchar (64) | NO | | NULL | | +-+-+ 2 rows in set (0.00 sec)

* show table status view basic view information

Mysql > show table status like 'vusers1' +- -+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Auto_increment | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-+- +-- -+ | vuser1 | NULL | NULL | NULL | NULL | VIEW | +- -- +- -+ 1 row in set (0.00 sec)

* show create view information

Mysql > show create view vuser1 +- -+ | View | Create View | | character_set_client | collation_connection | +-- | -+- -+-+ | vuser1 | CREATE ALGORITHM=UNDEFINED DEFINER= `root` @ `localhost` SQL SECURITY DEFINER VIEW `vuser1` AS select `users`.`id`AS `id` `users`.`name` AS `name` from `users` | utf8 | utf8_general_ci | +-+- -+ 1 row in set (0.00 sec)

6. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.

After reading the above DDL statement view method of operating MySQL, many readers must have some understanding. If you need to get more industry knowledge and information, you can continue to follow our industry information column.

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.