My MySQL learning experience (11)

Original: My MySQL learning experience (11)

My MySQL learning experience (11)

My MySQL learning experience (a)

My MySQL learning Experience (ii)

My MySQL learning Experience (iii)

My MySQL learning experience (iv)

My MySQL learning experience (v)

My MySQL learning experience (vi)

My MySQL learning experience (vii)

My MySQL learning experience (eight)

My MySQL learning experience (ix)

My MySQL learning experience (10)

My MySQL Learning experience (12)

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[(column_list)]     as select_statement     [with[cascaded | LOCAL]CHECKOPTION]

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 TABLEStudent (StunoINT, StunameNVARCHAR( -))CREATE TABLEStuinfo (StunoINT, classNVARCHAR( -), CityNVARCHAR( -))INSERT  intoStudentVALUES(1,'Wanglin'),(2,'Gaoli'),(3,'Zhanghai')INSERT  intoStuinfoVALUES(1,'Wuban','Henan'),(2,'Liuban','Hebei'),(3,'Qiban','Shandong')--Create a ViewCREATE VIEWStu_class (Id,name,glass) as SELECTstudent. ' Stuno ', student ' stuname ', Stuinfo. ' Class ' fromStudent, StuinfoWHEREStudent. ' Stuno '=stuinfo. ' Stuno 'SELECT *  fromStu_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

TABLE  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   

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

CREATE VIEW Stu_class

View       Create View Character_set_client collation_connection---------  ---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------------------------  --------------------  --------------------Stu_classCREATEAlgorithm=UNDEFINED Definer=' Root ' @ ' localhost ' SQL SECURITY definerVIEW' 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

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 collatio N_connection-------------  ------------  ----------  -------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ---------------------------------------------  ------------  ------------  --------------  -------------  --------------- -----  --------------------DEF school Stu_classSelect' 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@localhostDefiner 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[(column_list)] as select_ Statement[with[cascaded | LOCAL]CHECKOPTION]

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  as SELECT '   student '. ' Stuno '   as  ' id 'fromJOIN  ' Stuinfo ')  WHERE=  ' 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[(column_list)] as select_ Statement[with[cascaded | LOCAL]CHECKOPTION]

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   as SELECT  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   as SELECT  from student;

Querying View data

UPDATE

UPDATE SET stuname='xiaofang'WHERE Stuno=2

Querying View data

Update successful

INSERT

INSERT  into VALUES (6,'haojie')

Insert Successful

DELETE

DELETE  from 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]  ... [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

DROP VIEW IF EXISTS

If a view named Stu_class exists then delete

Viewing results using the show CREATE view statement

CREATE VIEW

-- Update stu_class set stuname= ' Xiaofang ' where stuno=2;--delete from Stu_class where stuno=1--select * from STU_CL the; -- ...  1146Table'school.stu_class' doesn't Existexecution time:0 sectransfer time  : 0 sectotal time     : 0.004 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

If there is a wrong place, welcome everyone to shoot brick O (∩_∩) o