View in MySQL

View in MySQL

1. What is a view?

In general, a view is the result set returned after a SELECT statement is executed. Therefore, when creating a view, the main task is to create this SQL query statement.

Ii. view features

A view is a reference to several basic tables. A virtual table queries the execution results of statements and does not store specific data. (The basic table data changes and the view changes accordingly );

You can perform addition, deletion, modification, and query operations like basic tables (ps: there are restrictions on addition, deletion, modification, and so on );

Iii. Role of views

Convenient operations, especially query operations, reduce complex SQL statements and enhance readability;

More secure. Database authorization commands cannot be limited to specific rows or columns. However, by creating a view reasonably, you can limit the permissions to the row and column levels;

Iv. application scenarios

During permission control, users do not want to access some columns containing sensitive information in the table, such as salary...

Key information comes from multiple complex associated tables. You can create a view to extract the information we need and simplify the operation;

V. View instance 1-create a view and query data

There are three tables: user, course, and user_course. The table structure and data are as follows:

Table definition:

------------------------------
-- Table structure for 'Course'
------------------------------
Drop table if exists 'Course ';
Create table 'Course '(
'Id' bigint (20) not null AUTO_INCREMENT,
'Name' varchar (200) not null,
'Description' varchar (500) not null,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8;

------------------------------
-- Records of course
------------------------------
Insert into 'Course' VALUES ('1', 'java', 'java class ');
Insert into 'Course' VALUES ('2', 'c ++ ', 'c ++ class ');
Insert into 'Course' VALUES ('3', 'c language ', 'c language class ');

------------------------------
-- Table structure for 'user'
------------------------------
Drop table if exists 'user ';
Create table 'user '(
'Id' bigint (20) not null AUTO_INCREMENT,
'Account' varchar (255) not null,
'Name' varchar (255) not null,
'Address' varchar (255) default null,
'Others 'varchar (200) default null,
'Others2' varchar (200) default null,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 4 default charset = utf8;

------------------------------
-- Records of user
------------------------------
Insert into 'user' VALUES ('1', 'user1', 'chen ', 'u.s.', '1', '1 ');
Insert into 'user' VALUES ('2', 'user2', 'zhang ', 'Japan', '2', '2 ');
Insert into 'user' VALUES ('3', 'user3', 'wang ', 'China', '3', '3 ');

------------------------------
-- Table structure for 'user _ course'
------------------------------
Drop table if exists 'user _ course ';
Create table 'user _ course '(
'Id' bigint (20) not null AUTO_INCREMENT,
'Userid' bigint (20) not null,
'Courseid' bigint (20) not null,
Primary key ('id ')
) ENGINE = InnoDB AUTO_INCREMENT = 7 default charset = utf8;

------------------------------
-- Records of user_course
------------------------------
Insert into 'user _ course' VALUES ('1', '1', '2 ');
Insert into 'user _ course' VALUES ('2', '1', '3 ');
Insert into 'user _ course' VALUES ('3', '2', '1 ');
Insert into 'user _ course' VALUES ('4', '2', '2 ');
Insert into 'user _ course' VALUES ('5', '2', '3 ');
Insert into 'user _ course' VALUES ('6', '3', '2 ');

Table data:

At this time, when we want to query the course-related information on John, we need to write a long SQL statement as follows:

SELECT
'Uc'. 'id' AS 'id ',
'U'. 'name' AS 'username ',
'C'. 'name' AS 'coursename'
FROM
'User' U'
Left join 'user _ course 'uc 'ON ('u'. 'id' = 'uc'. 'userid '))
Left join 'course ''c' ON ('uc '. 'courseid' = 'C'. 'id '))
WHERE
U. 'name' = 'zhang'

However, we can simplify operations through the view. For example, we create the view view_user_course as follows:

------------------------------
-- View structure for 'view _ user_course'
------------------------------
DROP VIEW
If exists 'view _ user_course ';

Create algorithm = UNDEFINED
DEFINER = 'root' @ 'localhost'
SQL SECURITY DEFINER
VIEW 'view _ user_course '(
SELECT
'Uc'. 'id' AS 'id ',
'U'. 'name' AS 'username ',
'C'. 'name' AS 'coursename'
FROM
(
(
'User' U'
Left join 'user _ course 'uc 'ON ('u'. 'id' = 'uc'. 'userid '))
)
Left join 'course ''c' ON ('uc '. 'courseid' = 'C'. 'id '))
)
);

Some notes (MySQL views are extended based on standard SQL ):

ALGORITHM = UNDEFINED: Specifies the view processing ALGORITHM;

DEFINER = 'root' @ 'localhost': Specifies the view creator;

SQL SECURITY DEFINER: Specifies the SECURITY authentication method when the View queries data;

After creating the view, we can directly use the following SQL statement to query the course-related information on the view sheet. The expected results can also be obtained:

SELECT
Vuc. username,
Vuc. coursename
FROM
View_user_course vuc
WHERE
Vuc. username = 'zhang'

Vi. View instance 2-add, delete, and modify data

To continue, we can try to add, delete, and modify data on view_user_course as follows:

Update view_user_course set username = 'test', coursename = 'javascript 'where id = 3

Unfortunately, the operation failed and the error message is as follows:

[SQL] update view_user_course set username = 'test', coursename = 'javascript 'where id = 3

[Err] 1393-Can not modify more than one base table through a join view 'demo. view_user_course'

Because the two tables cannot be modified simultaneously in a view connected by multiple associated tables;

Which operations can be performed on the view?

View and table are one-to-one relationships: If there are no other constraints (for example, fields not in the view are required in the basic table), you can add, delete, and modify data;

For example, we create the User Key Information view view_user_keyinfo, as shown below:

------------------------------
-- View structure for 'view _ user_keyinfo'
------------------------------
DROP VIEW
If exists 'view _ user_keyinfo ';

Create algorithm = undefined definer = 'root' @ 'localhost' SQL SECURITY DEFINER VIEW 'view _ user_keyinfo 'AS SELECT
'U'. 'id' AS 'id ',
'U'. 'account' AS 'account ',
'U'. 'name' AS 'username'
FROM
'User' U ';

The operation is successful as follows: (Note that other fields in the user table must be empty; otherwise, the operation fails ):

Insert into view_user_keyinfo (account, username)
VALUES
('Test1', 'test1 ');

DELETE
FROM
View_user_keyinfo
WHERE
Username = 'test1 ';

UPDATE view_user_keyinfo
SET username = 'updateuser'
WHERE
Id = 1

The relationship between a view and a table is one-to-multiple: if only the data of one table is modified and there are no other constraints (for example, fields not in the view are required in the basic table ), yes. You can modify the data, as shown in the following statement. The operation is successful;

Update view_user_course set coursename = 'java' where id = 1;

Update view_user_course set username = 'test2' where id = 3;

The following operations failed:

Delete from view_user_course where id = 3;

Insert into view_user_course (username, coursename) VALUES ('2', '3 ');

VII. Others

The query statement performance in the view needs to be optimized;

Be careful when modifying the operation. You have accidentally Modified multiple data entries in the basic table;

Other performance-related aspects to be practiced...

This article permanently updates the link address: