My MySQL learning experience (11) View

This article "My MySQL Learning experience (11)" will explain the MySQL view

What are the reasons for using a view?

1, security: This is generally done: Create a view that defines the data that the view operates on.

The user rights are then bound to the view in such a way that a feature is used: The GRANT statement can grant permissions on the view.

2. Improved query performance

3, with the flexibility of the functional requirements, the need to change the structure of the table resulting in a larger workload, you can use the form of virtual tables to achieve less modification effect.

This is useful in practical development.

4, complex query requirements, you can do problem decomposition, and then create multiple views to get the data. Combine the views together to get the results you need.

Create a View

Syntax for creating views

CREATE [OR REPLACE] [algorithm = {UNDEFINED | MERGE | TempTable}]    VIEW view_name [(column_list)] as    select_statement    [with [cascaded | LOCAL] CHECK OPTION]

Where Create: Represents a new view;

Replace: Represents replacing an existing view

Algorithm: Represents the View selection algorithm

View_name: View Name

Column_list: Attribute Column

Select_statement: Represents a SELECT statement

[With [cascaded | LOCAL] CHECK OPTION parameter indicates that the view is guaranteed to be within the view's permission range when updating

The optional algorithm clause is a MySQL extension to standard SQL.

Algorithm three values are desirable: MERGE, temptable, or undefined.

If there is no algorithm clause, the default algorithm is undefined (undefined). The algorithm affects how MySQL handles views.

For merge, the text of the statement referencing the view is merged with the view definition so that some part of the view definition supersedes the corresponding part of the statement.

For temptable, the result of the view is placed in the staging table and then used to execute the statement.

The algorithm to use for Undefined,mysql's own choice. If possible, it tends to merge rather than temptable,

This is because the merge is usually more efficient, and if a temporary table is used, the view is not updatable.

Local and cascaded are optional parameters, which determine the scope of the inspection test and the default value is cascaded.

The data for the script view comes from two tables

CREATE TABLE student (Stuno int, stuname NVARCHAR) CREATE TABLE stuinfo (Stuno int, class NVARCHAR, City NVARCHAR (60 ) insert into student values (1, ' Wanglin '), (2, ' Gaoli '), (3, ' Zhanghai ') inserts into Stuinfo values (1, ' Wuban ', ' Henan '), (2 , ' Liuban ', ' Hebei '), (3, ' Qiban ', ' Shandong ')--Creates a view of the Create views Stu_class (Id,name,glass) as SELECT student. ' Stuno ', Student. ' Stuname ', Stuinfo ' class ' from student, Stuinfo WHERE student. ' Stuno ' =stuinfo. ' Stuno ' SELECT * from Stu_class

View View

View view must have Show view permission

Ways to view views include: DESCRIBE, show TABLE STATUS, show CREATE view

Describe view basic information

DESCRIBE View Name

DESCRIBE Stu_class

The results show the field definition of the view, the data type of the field, whether it is empty, whether the primary/foreign key, the default value, and additional information

Describe is generally abbreviated as DESC.

Show TABLE Status Statement View basic information for viewing views

View information can be viewed by means of the show TABLE status

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)          VIEW   

The value of comment is the view description for the table, the other information is null to indicate that this is a virtual table, if it is a base table then the base table information, which is the difference between the base table and the view

Show CREATE View Statement view detail information

SHOW CREATE VIEW Stu_class

 View 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_gen Eral_ci 

Execution results display the name of the view, the statement that created the view, and so on

View details in the Views table

In MySQL, the INFORMATION_SCHEMA views table stores information about the view in the database

You can view the details of all views in the database by querying the view table

SELECT * from ' information_schema '. ' Views '

TABLE_CATALOG TABLE_SCHEMA table_name View_definition Check_option is_updatable definer security_type character_set_client Collation_co Nnection------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------------  ------------  ------------  --------------  -------------  --------- -------------------------------def school stu_class Select ' School '. ' Student '. ' Stuno ' as ' id ', ' schoo L '. ' Student '. ' Stuname ' as ' name ', ' school '. ' Stuinfo '. ' Class ' as ' class ' from ' school '. ' Student ' join ' school '. ' Stuinfo ' Where (' school '. ' Student '. ' Stuno ' = ' School '. ' Stuinfo '. ' Stuno ') NONE YES [email protected] definer UTF8 utf8_general      _ci

There is only one view under the current instance Stu_class

Modify a View

Modifying a view refers to modifying the view that exists in the database, and you can modify the view to maintain consistency with the base table when certain fields of the base table change.

Modify the view by using the Create OR REPLACE view statement and the ALTER statement in MySQL

The syntax is as follows:

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. Created when a view is not present, modified when it exists

Modify a 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 definition after change with desc

DESC Stu_class

You can see that only one field is queried

Alter statement Modify view

ALTER [algorithm = {UNDEFINED | MERGE | Temptable}]view view_name [(column_list)]as Select_statement[with [cascaded | LOCAL] CHECK OPTION]

Here the keyword is the same as the previous one, here does not introduce

To modify a view by using the ALTER statement Stu_class

ALTER VIEW  Stu_class as SELECT Stuno from student;

Use desc to view

DESC Stu_class

Update view

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

Updating from a view is done by going to the base table for updates, and if you add or delete records, you actually add or delete records to the base table

Let's change the view definition first.

ALTER VIEW  Stu_class as SELECT stuno,stuname from student;

Querying View data

UPDATE

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

Querying View data

Update successful

INSERT

INSERT into Stu_class VALUES (6, ' Haojie ')

Insert Successful

DELETE

DELETE from Stu_class WHERE stuno=1

Delete Succeeded

When the view contains the following content, the update operation of the view will not be executed

(1) The view contains columns that are defined as non-empty in the base

(2) A mathematical expression is used in the field list after the SELECT statement of the definition view

(3) Use aggregate function in the field list after defining the view's SELECT statement

(4) Distinct, UNION, TOP, GROUP by, having clauses are used in the SELECT statement that defines the view

Delete a view

Delete view using Drop view syntax

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

Drop view can delete 1 or more views. You must have drop permissions on each view

You can use the keyword if exists to prevent errors due to non-existent views

Delete Stu_class View

If a view named Stu_class exists then delete

Viewing results using the show CREATE view statement

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.00 4 sec---------------------------------------------------

The view does not exist, the deletion succeeds

Summarize

SQL Server actually has the same information architecture view as MYSQL .

Information Schema View (Transact-SQL)

The information Schema view is one of several ways that SQL Server provides access to metadata.

The Information Schema view provides an internal SQL Server metadata View independent of the system tables.

Although important modifications have been made to the underlying system tables, the information Schema view can still make the application work.

The information Schema View contained in SQL Server conforms to the information schema definition in the ISO standard.

Information Schema View data is stored in the System Database resource database

Using the Information_schema view to stitch SQL statements

My MySQL learning experience (11) View