Use of MySQL View

Use of MySQL View
Overview:

A view is a virtual table, also known as a derived table, because its content is derived from the query results of other tables. Although the view looks like a basic table, they are not basic tables. The basic table content is persistent, while the View content is dynamically generated during use. -- From SQLite authoritative guide

Advantages of using a view:

1. Reliable Security
2. Improved query performance
3. effectively respond to flexible functional requirements
4. easily cope with complex query requirements

Basic use of a view: Create:

For example, we have a basic table like this:

Mysql> select * from students;
+ ------ + ---------------- + ------- +
| Id | name | age |
+ ------ + ---------------- + ------- +
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
+ ------ + ---------------- + ------- +

You can create a view like this:

Create view stu_view as select name FROM students;

Query OK, 0 rows affected (0.01 sec)

After creating a view, you can view 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 created in the current database has been put into the table set of the database. Because a view is also a type of table, it is a virtual table.

Query:

View query is the same as basic table query. Because view is also a data table, 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 it again for verification:

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

Next, let's take a look at how the view changes if we change the original basic table:

Mysql> insert into students (id, name, age) VALUES (2, 'zeus', 100000 );
Query OK, 1 row affected (0.00 sec)

Check the basic table:

Mysql> SELECT * FROM students;
+ ------ + ---------------- + -------- +
| Id | name | age |
+ ------ + ---------------- + -------- +
| 1 | bumblebee | 200 |
| 1 | king of monkey | 10000 |
| 1 | Zeus | 100000 |
+ ------ + ---------------- + -------- +
3 rows in set (0.00 sec)

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

Verification:

Mysql> select * from stu_view2;
+ ------ + ---------------- +
| Id | name |
+ ------ + ---------------- +
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Zeus |
+ ------ + ---------------- +
3 rows in set (0.00 sec)

Update 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

Check view update results:

Mysql> SELECT * FROM stu_view2;
+ ------ + ---------------- +
| Id | name |
+ ------ + ---------------- +
| 1 | bumblebee |
| 1 | king of monkey |
| 2 | Medusa |
+ ------ + ---------------- +
3 rows in set (0.00 sec)

Check the UPDATE results of the basic table:

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:

The above operations are based on a single table. Next we will do some practical work from the two tables.

Create an additional info table as the auxiliary data table, as shown below:

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)

Create a view stu_view3 that connects two basic tables.

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 monkeys | 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 connected to two basic tables:

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 monkeys | 10000 | Hero in Chinese Mythology. |
| 3 | Medusa | 100000 | In Greek mythology the Gorgon. |
+ ------ + ---------------- + -------- + ----------------------------------- +
3 rows in set (0.00 sec)

Verification basic table:

Mysql> select * from students;
+ ------ + ---------------- + -------- +
| Id | name | age |
+ ------ + ---------------- + -------- +
| 1 | bumblebee | 800 |
| 1 | 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 table syntax.
2. When creating a view, if the view name is the same as the name of an existing table, an error is returned and creation is not allowed. A view is a special table.