Views

This document describes how to use views in MatrixOne.

Overview

A view acts as a virtual table, whose schema is defined by the SELECT statement that creates the view.

Key Feature:

  • Simplified user action: The view mechanism allows users to focus on the data they care about. If the data is not directly from the base table, you can define views to make the database look simple and simplify the user’s data query operation.

  • Multiple perspectives on the same data: The view mechanism enables different users to view the same data differently, which is necessary when many users share the same database.

  • Provides a degree of logical independence for refactoring the database: Physical data independence means that the user’s application does not depend on the physical structure of the database. The logical independence of the data indicates that when the database is restructured, such as adding new relationships or adding new fields to existing relationships, the user’s application is not affected. Hierarchical databases and mesh databases can support the physical independence of data but can not fully support logical independence.

Before you start

Make sure you have already Deployed standalone MatrixOne.

Preparation

Create two tables to prepare for using the VIEW:

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

Query the table t00:

  1. mysql> select * from t00;
  2. +------+
  3. | a    |
  4. +------+
  5. |    1 |
  6. |    2 |
  7. +------+

Query the table t01:

  1. mysql> select * from t01;
  2. +------+
  3. | a    |
  4. +------+
  5. |    1 |
  6. +------+

Create a view

A complex query can be defined as a view with the CREATE VIEW statement. The syntax is as follows:

  1. CREATE VIEW view_name AS query;

you cannot create a view with the same name as an existing view or table.

Example:

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

Query views

Once a view is created, you can use the SELECT statement to query the view just like a normal table.

  1. mysql> SELECT * FROM v0;
  2. +------+------+
  3. | a    | b    |
  4. +------+------+
  5. |    1 |    1 |
  6. |    2 | NULL |
  7. +------+------+

Use the SHOW CREATE TABLE|VIEW view_name statement:

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

Drop view

Use the DROP VIEW view_name; statement to drop a view.

  1. mysql> DROP VIEW v0;