Skip to content

View

This document introduces you to the view functions of MO Intelligence.

Overview

The view acts as a virtual table, performs storage queries, and produces a result set when called.

What is the view:

  • Simplify user operations: The view mechanism allows users to focus on the data they are concerned about. If this data does not come directly from the basic table, you can define the view to make the database look simple and clear, and simplify the user's data query operations.

  • View the same data from multiple perspectives: The view mechanism enables different users to view the same data in different ways, and this flexibility is very necessary when many different types of users share the same database.

  • Provides a certain degree of logical independence for refactoring the database: the physical independence of data refers to the user's application not dependent on the physical structure of the database. The logical independence of data means that when the database is reconstructed, if a new relationship is added or a new field is added to the original relationship, the user's application will not be affected. Hierarchical databases and mesh databases generally can better support the physical independence of data, but cannot fully support the logical independence.

Prepare before starting

You need to confirm that before you start, the following tasks have been completed:

Data preparation

Create two new tables to facilitate subsequent preparation for using the view:

CREATE TABLE t00(a INTEGER);
INSERT INTO t00 VALUES (1),(2);
CREATE TABLE t01(a INTEGER);
INSERT INTO t01 VALUES (1);

You can check the data structure of table t00:

mysql> select * from t00;
+------+
| a |
+------+
| 1 |
| 2 |
+------+

You can check the data structure of table t01:

> select * from t01;
+------+
| a |
+------+
| 1 |
+------+

Create a view

You can define a more complex query as a view through the CREATE VIEW statement, and its syntax is as follows:

CREATE VIEW view_name AS query;

The created view name cannot be duplicated with the existing view or table.

Examples are as follows:

mysql> CREATE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a);
Query OK, 0 rows affected (0.02 sec)

Query View

After the view is created, you can use the SELECT statement to query the view like querying a general data table.

mysql> SELECT * FROM v0;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | NULL |
+------+------+

Use the SHOW CREATE VIEW view_name statement:

mysql> SHOW CREATE VIEW v0;
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| View | Create View |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| v0 | CREATE VIEW v0 AS SELECT t00.a, t01.a AS b FROM t00 LEFT JOIN t01 USING(a) |
+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

Delete view

The DROP VIEW view_name;` statement can delete the created views.

mysql> DROP VIEW v0;