MySQL (basic) view

I. Introduction to a view, role, and Advantages 1. What is a view? A. A view is a virtual table that is exported from one or more tables in the database. the database only stores view definitions, but does not Store View data. The data is stored in the original table c. when you use a view to query data, the database system extracts the corresponding records from the original table.

I. Introduction to a view, role, and Advantages 1. What is a view? A. A view is a virtual table that is exported from one or more tables in the database. the database only stores view definitions, but does not Store View data. The data is stored in the original table c. when you use a view to query data, the database system extracts the corresponding records from the original table.

Zookeeper

I. View introduction, functions, and advantages

1. What is a view?

A. a view is a virtual table that is exported from one or more tables in the database.

B. The database only stores view definitions, but does not Store View data. The data is stored in the original table.

C. When you use a view to query data, the database system will retrieve the corresponding records from the original table.

2. View functions:

A. simplify operations

B. Increase Data Security

C. Improve the logical independence of tables

3. Advantages of the View:

A. Centralized Viewpoint

B. Simplified operations

C. Custom Data

D. Merge and split data

· E. Security

Ii. Create a view

CREATE [ALGORITHM = {UNDEFIEND | MERGE | TEMPTABLE}]

VIEW name [(attribute list)]

As select statement

[WITH [CASCADED | LOCAL] check option];

ALGORITHM is an optional parameter, indicating the ALGORITHM selected by the view;

The "view name" parameter indicates the name of the view to be created;

"Attribute list" is an optional parameter that specifies the nouns of various attributes in the view. By default, it is the same as the attributes queried in the SELECT statement;

The SELECT statement parameter is a complete query statement that identifies certain records that meet the conditions from a table and imports these records into the view;

With check option is an optional parameter. When updating a view, the table must be within the permission range of the view;

ALGORITHM includes three options: UNDEFINED, MERGE, and TEMPTABLE.

The UNDEFINED option indicates that MySQL will automatically select the algorithm to be used;

The MERGE option combines view statements with view definitions, so that a part of view definitions replaces the corresponding part of statements;

The TEMPTABLE option stores the view results to a temporary table and then uses the temporary table to execute the statement. CASCADED is an optional parameter, indicating that the view must meet all the related views and table conditions when updating the view, this parameter is the default value;

LOCAL indicates that when updating a view, you must satisfy the definition conditions of the view;

For example, create a view named v1, which contains all records of the t_book table.

Mysql> create view v1

AS

SELECT * FROM t_book;

Next, we can operate on this view to query records with id 2 in the view.

Mysql> SELECT * FROM v1 WHERE id = 2;

For example, create a view named v2 with only the title and price information displayed.

Mysql> create view v2

AS

SELECT bookName, price FROM t_book;

Mysql> SELECT * FROM v2;

Now the v2 view only contains the title and price information.

For example, you will find that the field name becomes the title and price

Mysql> create view v3 (title, price)

AS

SELECT bookName, price FROM t_book;

Mysql> SELECT * FROM v2;

Of course, this can also be written as follows:

Mysql> create view v4

AS

SELECT bookName AS 'title', price AS 'price' FROM t_book;

For example, create a view based on the t_book and t_booktype tables to query the title and book category.

Mysql> create view v5

AS

SELECT bookName, bookTypeName FROM t_book t1, t_booktype t2 WHERE t1.bookTypeId = t2.id;

Iii. View

4.1 DESCRIBE statement view basic view information

For example, view the basic information of the v4 view.

Mysql> DESC v4;

4.2 show table status like statement view basic view information

For example, view the basic information of the v5 view.

Mysql> show table status like 'v5 ';

Obviously, the view is a virtual table.

4.3 show create view statement VIEW Details

For example, view the details of the v5 view.

Mysql> show create view v5;

4.3 View details in the views table

Mysql> USE information_schema;

Mysql> SELECT * FROM views;

In this way, we can see the information of all created views.

4. Modify the view

4.1 create or replace view statement modify VIEW

Create or replace [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW name [(attribute list)]

As select statement

[WITH [CASCADED | LOCAL] check option];

If the view exists, REPLACE the view. If the view does not exist, Create the view.

Example: Mysql> create or replace view v3

AS

SELECT * FROM t_book WHERE id = 3;

4.2 ALTER statement modification View

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW name [(attribute list)]

As select statement

[WITH [CASCADED | LOCAL] check option];

Only views can be modified, but views cannot be created.

For example, Mysql> alter view v3

AS

SELECT * FROM t_book;

V. Update View

Updating a view refers to inserting (INSERT), updating (UPDATE), and deleting (DELETE) data in tables through the view. Because a view is a virtual table with no data. When the view is updated, it is updated by converting the basic table. When updating a view, only data within the permission range can be updated. If the update is out of the range, it cannot be updated.

5.1 INSERT)

For example, Mysql> insert into v4 VALUES (NULL, 'Psychology ', 45, 'three', 4 );

5.2 UPDATE)

For example, Mysql> UPDATE v4 SET bookName = 'psychology 2' WHERE id = 5;

5.3 DELETE)

Mysql> delete from v4 WHERE id = 5;

INSERT, UPDATE, and DELETE operations are performed on the original table.

Vi. Deleting A View

Deleting a view is to delete an existing view in a database. When deleting a view, only the view definition can be deleted, and data is not deleted;

Drop view [if exists] VIEW name list [RESTRICT | CASCADE]

For example, if a view named v4 exists, delete it.

Mysql> drop view if exists v4;

VII. Summary

This is the end of the view. Let's take a good look at the view. The next section will bring you the use of triggers. Thank you!