View

In SQL, a view is a virtual table based on the result set of an SQL statement. It contains rows and columns just like a real table. The query of a view is run every time the view is referenced in a query.

In the following situations, you can use views:

  • Simplifying complex queries, avoiding the need to repeatedly write and send complex statements for every query.
  • Granting read permissions to specific users while restricting access to certain columns and rows to ensure data security and isolation.

A view is created with the CREATE VIEW statement.

View examples

  1. CREATE VIEW cpu_monitor AS
  2.     SELECT cpu, host, ts FROM monitor;

The view name is cpu_monitor, and the query statement after AS is the SQL statement to present the data. Query the view:

  1. SELECT * FROM cpu_monitor;
  1. +------+-----------+---------------------+
  2. | cpu  | host      | ts                  |
  3. +------+-----------+---------------------+
  4. |  0.5 | 127.0.0.1 | 2023-12-13 02:05:41 |
  5. |  0.3 | 127.0.0.1 | 2023-12-13 02:05:46 |
  6. |  0.4 | 127.0.0.1 | 2023-12-13 02:05:51 |
  7. |  0.3 | 127.0.0.2 | 2023-12-13 02:05:41 |
  8. |  0.2 | 127.0.0.2 | 2023-12-13 02:05:46 |
  9. |  0.2 | 127.0.0.2 | 2023-12-13 02:05:51 |
  10. +------+-----------+---------------------+

Query view by WHERE:

  1. SELECT * FROM cpu_monitor WHERE host = '127.0.0.2';
  1. +------+-----------+---------------------+
  2. | cpu  | host      | ts                  |
  3. +------+-----------+---------------------+
  4. |  0.3 | 127.0.0.2 | 2023-12-13 02:05:41 |
  5. |  0.2 | 127.0.0.2 | 2023-12-13 02:05:46 |
  6. |  0.2 | 127.0.0.2 | 2023-12-13 02:05:51 |
  7. +------+-----------+---------------------+

Create a view that queries data from two tables:

  1. CREATE VIEW app_cpu_monitor AS
  2.     SELECT cpu, latency, host, ts FROM monitor LEFT JOIN app_monitor
  3.     ON monitor.host = app_monitor.host AND monitor.ts = app_monitor.ts

Then query the view as if the data were coming from one single table:

  1. SELECT * FROM app_cpu_monitor WHERE host = 'host1'

Update View

CREATE OR REPLACE VIEW to update a view, if it doesn’t exist, it will be created:

  1. CREATE OR REPLACE VIEW memory_monitor AS
  2.     SELECT memory, host, ts FROM monitor;

Shows the view definition

Shows the CREATE VIEW statement that creates the named view by SHOW CREATE VIEW view_name:

  1. SHOW CREATE VIEW cpu_monitor;
  1. +-------------+--------------------------------------------------------------+
  2. | View        | Create View                                                  |
  3. +-------------+--------------------------------------------------------------+
  4. | cpu_monitor | CREATE VIEW cpu_monitor AS SELECT cpu, host, ts FROM monitor |
  5. +-------------+--------------------------------------------------------------+

List Views

SHOW VIEWS statement to find all the views:

  1. > SHOW VIEWS;
  3. +----------------+
  4. | Views          |
  5. +----------------+
  6. | cpu_monitor    |
  7. | memory_monitor |
  8. +----------------+

of course, just like SHOW TABLES, it supports LIKE and WHERE:

  1. > SHOW VIEWS like 'cpu%';
  2. +-------------+
  3. | Views       |
  4. +-------------+
  5. | cpu_monitor |
  6. +-------------+
  7. 1 row in set (0.02 sec)
  9. > SHOW VIEWS WHERE Views = 'memory_monitor';
  10. +----------------+
  11. | Views          |
  12. +----------------+
  13. | memory_monitor |
  14. +----------------+

Drop View

Use DROP VIEW statement to drop a view:

  1. DROP VIEW cpu_monitor;

To be quiet if it does not exist:

  1. DROP VIEW IF EXISTS test;