MySQL Optimization-view and mysql View

MySQL Optimization-view and mysql View

Reprinted please indicate the source: http://blog.csdn.net/l1028386804/article/details/46761521

What is the reason for using the view?

1. Security: This is generally done: Create a view and define the data operated by the view.

Then, bind the user permission to the view. In this way, a feature is used: the grant statement can grant permissions to the view.

2. Improved query performance

3. If you have flexible functions and need to modify the table structure, resulting in a large workload, you can use a virtual table to achieve the effect of less modification.

This is useful in actual development.

4. For complex query requirements, you can break down the problem and create multiple views to obtain data. Combine the view to get the desired result.

Create View

View creation syntax

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

"CREATE" indicates creating a view;

REPLACE: REPLACE an existing view.

ALGORITHM: indicates the view selection ALGORITHM.

View_name: View name

Column_list: attribute Column

Select_statement: select statement

The [WITH [CASCADED | LOCAL] check option] parameter indicates that the view is within the permission range when it is updated.

The optional ALGORITHM clause is an extension of standard SQL MySQL.

ALGORITHM can have three values: MERGE, TEMPTABLE, or UNDEFINED.

If there is no ALGORITHM clause,The default algorithm is UNDEFINED (UNDEFINED). The algorithm affects the way MySQL processes the view.

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 use. If possible, it tends to be MERGE rather than TEMPTABLE,

This is because MERGE is generally more effective, and if a temporary table is used, the view cannot be updated.

LOCAL and CASCADED are optional parameters, which determine the test scope. The default value is CASCADED.

The data in the script view comes from two tables.

Create table student (stuno INT, stuname NVARCHAR (60) create table stuinfo (stuno INT, class NVARCHAR (60), city NVARCHAR (60) insert into student VALUES (1, 'hanglin'), (2, 'gaoli'), (3, 'shanghai') insert into stuinfo VALUES (1, 'shanghai', 'henanc'), (2, 'libban', 'shanghai'), (3, 'qibanc', 'shandong') -- create view stu_class (id, NAME, glass) as select student. 'std', student. 'stamp', stuinfo. 'class' FROM student, stuinfo WHERE student. 'std' = stuinfo. 'std' SELECT * FROM stu_class

View

To VIEW a VIEW, you must have the show view permission.

VIEW methods include DESCRIBE, show table status, and show create view.

DESCRIBE view Basic Information

DESCRIBE view name DESCRIBE stu_class

The results show the field definitions of the view, the Data Type of the field, whether it is null, whether it is the primary/foreign key, default value, and additional information.

DESCRIBE is generally abbreviated as DESC

Show table status statement to view basic view information

You can use the show table status method to view information.

SHOW TABLE STATUS LIKE 'stu_class'Name       Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time  Update_time  Check_time  Collation  Checksum  Create_options  Comment---------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -----------  -----------  ----------  ---------  --------  --------------  -------stu_class  (NULL)   (NULL)  (NULL)      (NULL)          (NULL)       (NULL)           (NULL)        (NULL)     (NULL)          (NULL)  (NULL)       (NULL)       (NULL)      (NULL)       (NULL)  (NULL)          VIEW   

If the value of COMMENT is VIEW, the table is VIEW. If the other information is NULL, This is a virtual table. If it is a base table, the information of the base table is displayed. This is the difference between the base table and the VIEW.

Show create view statement VIEW Details

SHOW CREATE VIEW stu_classView       Create View                                                                                                                                                                                                                                                               character_set_client  collation_connection---------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------  --------------------stu_class  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_class` AS select `student`.`stuno` AS `id`,`student`.`stuname` AS `name`,`stuinfo`.`class` AS `class` from (`student` join `stuinfo`) where (`student`.`stuno` = `stuinfo`.`stuno`)  utf8                  utf8_general_ci     

The execution result displays the view name, statements for creating the view, and other information.

View details in the VIEWS table

In MYSQL, The INFORMATION_SCHEMA VIEWS table stores information about VIEWS in the database.

You can query the VIEWS table to view the details of all VIEWS in the database.

SELECT * FROM `information_schema`.`VIEWS`TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  VIEW_DEFINITION                                                                                                                                                                                                                         CHECK_OPTION  IS_UPDATABLE  DEFINER         SECURITY_TYPE  CHARACTER_SET_CLIENT  COLLATION_CONNECTION-------------  ------------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------  ------------  --------------  -------------  --------------------  --------------------def            school        stu_class   select `school`.`student`.`stuno` AS `id`,`school`.`student`.`stuname` AS `name`,`school`.`stuinfo`.`class` AS `class` from `school`.`student` join `school`.`stuinfo` where (`school`.`student`.`stuno` = `school`.`stuinfo`.`stuno`)  NONE          YES           root@localhost  DEFINER        utf8                  utf8_general_ci     

The current instance has only one view stu_class

Modify View

Modifying a view is a view existing in a database. When some fields in a basic table change, you can modify the view to maintain consistency with the basic table.

MYSQL uses the create or replace view statement and ALTER statement to modify the VIEW.

Syntax:

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

This statement is used to change the definition of an existing view. Its syntax is similar to create view. Create a view if it does not exist and modify it if it exists

Modify View

DELIMITER $$CREATE OR REPLACE VIEW `stu_class` AS SELECT  `student`.`stuno`   AS `id`FROM (`student` JOIN `stuinfo`)WHERE (`student`.`stuno` = `stuinfo`.`stuno`)$$DELIMITER ; 

View the modified view definition through DESC

DESC stu_class

You can see that only one field is queried.

ALTER statement modification View

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

The keyword here is the same as above. We will not introduce it here

Use the ALTER statement to modify the view stu_class

ALTER VIEW  stu_class AS SELECT stuno FROM student;

View with DESC

DESC stu_class

Update View

Updating a view refers to inserting, updating, and deleting table data through a view, because the view is a virtual table with no data.

When a view is updated, it is updated to the base table. If you add or delete a record to or from the view, it is actually adding or deleting a record to or from the base table.

First, modify the view definition.

ALTER VIEW  stu_class AS SELECT stuno,stuname FROM student;

Query view Data

UPDATE

UPDATE stu_class SET stuname='xiaofang' WHERE stuno=2

Query view Data

Updated

 INSERT

INSERT INTO stu_class VALUES(6,'haojie')

Inserted successfully

 

DELETE

DELETE FROM stu_class WHERE stuno=1

Deleted

When the view contains the following content, the view update operation cannot be executed.

(1) The view contains columns that are basically defined as non-empty.

(2) mathematical expressions are used in the field list after the SELECT statement of the definition view.

(3) Use Aggregate functions in the field list after the SELECT statement of the definition view

(4) The DISTINCT, UNION, TOP, group by, and HAVING clauses are used in the SELECT statement defining the view.

 

Delete View

Delete a view using the drop view syntax

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

Drop view can delete one or more views. You must have the DROP permission on each view.

You can use the keyword if exists to prevent errors due to nonexistent views.

Delete stu_class View

DROP VIEW IF EXISTS stu_class 

Delete A View named stu_class if it exists.

Use the show create view statement to VIEW the results

SHOW CREATE VIEW stu_class 
Query: -- update stu_class set stuname='xiaofang' where stuno=2; -- delete from stu_class where stuno=1 -- select * from stu_class; -- ...Error Code: 1146Table 'school.stu_class' doesn't existExecution Time : 0 secTransfer Time  : 0 secTotal Time     : 0.004 sec---------------------------------------------------

The view does not exist. The view is successfully deleted.

 

Summary

SQLSERVERActually followMYSQLLikewise, there is an information architecture view.

Information Architecture view (Transact-SQL)

Information Architecture view is one of several methods provided by SQL Server to obtain metadata.

The information architecture view provides an internal SQL Server metadata view independent of the system table.

Despite significant modifications to the basic system table, the Information Architecture view can still make the application work normally.

The information architecture view contained in SQL Server complies with the information architecture definition in the ISO standard.

The data in the Information Architecture view is stored in the system database Resource database.

mssqlsystemresource.mdf 

Concatenate SQL statements using the INFORMATION_SCHEMA View

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.