Use of MySQL view

Overview:

Views are virtual tables, also known as derived tables, because their contents are derived from query results from other tables. Although views look like basic tables, they are not basic tables. The content of the base table is persistent, and the content of the view is generated dynamically during use. --excerpted from the authoritative guide of SQLite

Advantages of using Views:

1. Reliable Security
2. Improved query performance
3. Functional requirements for effective response to flexibility
4. Easy response to complex query requirements

Basic use of views:Create:

For example, we have a basic table of this kind:

mysql> SELECT * from students;
+------+----------------+-------+
| ID | name | Age |
+------+----------------+-------+
| 1 |   Bumblebee | 200 |
| 1 | King of Monkey | 10000 |
+------+----------------+-------+

Then you can create a view like this:

CREATE VIEW Stu_view as SELECT name from students;

Query OK, 0 rows affected (0.01 sec)

Once you have created a view, you can view it by looking at all the data tables in the database:

Mysql> Show tables;
+-------------------+
| tables_in_student |
+-------------------+
| Stu_view |
| Students |
+-------------------+
2 rows in Set (0.00 sec)

You can see that the view you just created has been put into the table collection of the database in the current database. Because a view is also a table, it is a virtual table.

Enquiry:

The query for the view is the same as the query for the base table, because the view is also a data table, so you can query it like this

Mysql> select * from Stu_view;
+----------------+
| name |
+----------------+
| Bumblebee |
| King of Monkey |
+----------------+

Delete:

DROP VIEW Stu_view;

After deletion, you can query again for validation:

Mysql> select * from Stu_view;
ERROR 1146 (42S02): Table ' student.stu_view ' doesn ' t exist

Let's see what happens to the view if we change the original base table:

Mysql> INSERT into students (ID, name, age) VALUES (2, ' Zeus ', 100000);
Query OK, 1 row Affected (0.00 sec)

Check the base table:

Mysql> SELECT * from students;
+------+----------------+--------+
| ID | name | Age |
+------+----------------+--------+
| 1 |    Bumblebee | 200 |
| 1 |  King of Monkey | 10000 |
| 2 | Zeus | 100000 |
+------+----------------+--------+
3 Rows in Set (0.00 sec)

To check the view:

Mysql> SELECT * from Stu_view;
+----------------+
| name |
+----------------+
| Bumblebee |
| King of Monkey |
| Zeus |
+----------------+
3 Rows in Set (0.00 sec)

Update:

Mysql> CREATE VIEW stu_view2 as SELECT ID, name from students;
Query OK, 0 rows affected (0.01 sec)

Verify:

Mysql> select * from Stu_view2;
+------+----------------+
| ID | name |
+------+----------------+
| 1 | Bumblebee |
| 1 | King of Monkey |
| 2 | Zeus |
+------+----------------+
3 Rows in Set (0.00 sec)

To update the view:

mysql> UPDATE stu_view2 SET name= ' Medusa ' WHERE id=2;
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

To check the results of the view update:

Mysql> SELECT * from Stu_view2;
+------+----------------+
| ID | name |
+------+----------------+
| 1 | Bumblebee |
| 1 | King of Monkey |
| 2 | Medusa |
+------+----------------+
3 Rows in Set (0.00 sec)

Check for basic table update results:

Mysql> SELECT * from students;
+------+----------------+--------+
| ID | name | Age |
+------+----------------+--------+
| 1 |    Bumblebee | 200 |
| 1 |  King of Monkey | 10000 |
| 2 | Medusa | 100000 |
+------+----------------+--------+
3 Rows in Set (0.00 sec)

view associated with multiple tables:

These are all based on a single-table operation, and then we will do some actual combat from two tables.

We create an additional info table as an auxiliary data table, as follows:

Mysql> SELECT * from info;
+----+--------+---------------------------------+
| ID | stu_id | info |
+----+--------+---------------------------------+
|      1 | 1 | A member of the deformed steel. |
|      2 | 2 |      Hero in Chinese mythology. |
|      3 | 3 |  In Greek mythology the Gorgon. |
+----+--------+---------------------------------+
3 Rows in Set (0.00 sec)

We create a view that connects two basic tables STU_VIEW3

Mysql> CREATE VIEW stu_view3 as SELECT s.id, S.name, S.age, i.info from students s, info i WHERE i.stu_id=s.id;
Query OK, 0 rows Affected (0.00 sec)

Verification process:

Mysql> select * from STU_VIEW3;
+------+----------------+--------+---------------------------------+
| ID | name | Age | info |
+------+----------------+--------+---------------------------------+
| 1 |    Bumblebee | 200 | A member of the deformed steel. |
| 2 |  King of Monkey | 10000 |      Hero in Chinese mythology. |
| 3 | Medusa | 100000 |  In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 Rows in Set (0.00 sec)

Update the view stu_view3 that has two basic tables connected:

mysql> UPDATE stu_view3 SET age=800 WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched:1 changed:1 warnings:0

Verify View STU_VIEW3:

Mysql> select * from STU_VIEW3;
+------+----------------+--------+---------------------------------+
| ID | name | Age | info |
+------+----------------+--------+---------------------------------+
| 1 |    Bumblebee | 800 | A member of the deformed steel. |
| 2 |  King of Monkey | 10000 |      Hero in Chinese mythology. |
| 3 | Medusa | 100000 |  In Greek mythology the Gorgon. |
+------+----------------+--------+---------------------------------+
3 Rows in Set (0.00 sec)

To verify the base table:

Mysql> SELECT * from students;
+------+----------------+--------+
| ID | name | Age |
+------+----------------+--------+
| 1 |    Bumblebee | 800 |
| 2 |  King of Monkey | 10000 |
| 3 | Medusa | 100000 |
+------+----------------+--------+
3 Rows in Set (0.00 sec)

Summary:

1. When using a view, it is the same as using the syntax of the table
2. When creating a view, the name of the view if it is the same as the existing table, it will be an error, not allowed to create. A view is a special kind of table

Use of MySQL view