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

Mysql View concept

The view itself is a virtual table that does not hold any data. When using SQL statements to access the view, the data obtained is generated by MySQL from other tables, and the view and the table are in the same namespace. View query data is relatively safe, can hide some data and structures, and only let users see the data within the authority, making complex queries easy to understand and use.

View usage

The following is the basic usage based on the user and order management demo view. (online learning video tutorials sharing: mysql video tutorials)

Basic table structure

CREATE TABLE v01_user (id INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', user_name VARCHAR (20) DEFAULT NULL COMMENT' username', phone VARCHAR (20) DEFAULT NULL COMMENT 'mobile number', pass_word VARCHAR (64) DEFAULT NULL COMMENT 'password', card_id VARCHAR (18) DEFAULT NULL COMMENT'ID card ID', pay_card VARCHAR (25) DEFAULT NULL COMMENT 'card number, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT' user list' CREATE TABLE v02_order (id INT (11) NOT NULL AUTO_INCREMENT COMMENT 'primary key ID', user_id INT (11) NOT NULL COMMENT' user ID', order_no VARCHAR (32) DEFAULT NULL COMMENT 'order number', good_name VARCHAR (60) DEFAULT NULL COMMENT 'commodity name', good_id INT (11) DEFAULT NULL COMMENT 'commodity ID', num INT (11) DEFAULT NULL COMMENT' purchase quantity' Total_price DECIMAL (10jue 2) DEFAULT NULL COMMENT 'Total Price', PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'order Table'

Basic grammar

CREATE OR REPLACE VIEW view_name AS select_statement

Note: tables and views share the same namespace in the database, so the database cannot contain tables and views with the same name.

User order View

CREATE OR REPLACE VIEW user_order_view AS SELECT t1.idforme t1.userroomnamerecoveryt2.orderroomnamerecoveryt2.goodstationid, t2.goodstationnamememe t2.numminary t2.totalcrippricefrom v01_user t1LEFT JOIN v02_order T2 ON t2.user_id = t1.id

View call

This is basically the same as MySQL's table query, and you can use a variety of query conditions.

SELECT * FROM user_order_view WHERE user_name='Cicada'; View SHOW CREATE VIEW user_order_view; modify View ALTER VIEW view_name AS select_statement; Delete View DROP VIEW [IF EXISTS] view_name

View update

If the specified conditions permit, you can update the related tables involved in the view by updating, deleting, or even writing data on the view.

UPDATE user_order_view SET user_name='smile' WHERE id='1'

Here, the v01 _ user table data is updated by performing an update operation on the view. If the view is defined using special operations such as aggregate functions, grouping, and so on, it cannot be updated. MySQL does not support creating triggers on views.

View implementation

1. Temporary table algorithm

The server will save the data of the view query SQL in the temporary table, and the structure of the temporary table is consistent with the view field structure, which is the most taboo operation in SQL query optimization. If the amount of data is slightly large, it will seriously affect the performance. If the view cannot have an one-to-one mapping with the original table, a temporary table will be generated, which shows that the view is not very simple, or even very complex.

2. Merging algorithm

The server executes the query based on the table used in the view, and finally merges the query structure and returns it to the client.

3. Distinguishing methods

Execute the following query statement to analyze the executed performance parameters.

EXPLAIN SELECT * FROM user_order_view

Observe the select_type field in the query results, and if it is DERIVED, the temporary table is used. Here SQL performs parsing after the syntax optimization section will be explained in more detail.

Matters needing attention

1. Performance issues

MySQL does not support creating indexes in views, which may cause many query performance problems when using views, so it is recommended to carefully examine and test them from multiple angles when using them.

2. Special usage

For view-based queries, you can modify part of the table structure so that the query of the view is not affected as long as the fields are not used in the view.

The above is a detailed explanation of the mysql view of the details, more please pay attention to other related articles!

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.