View

View is a database objectified version of subquery. View records SELECT command, which is originally unable to function as a database object.

By using view, you can store complicating SELECT commands and easily reuse them.

View is a virtual table, which doesn’t actually have a storage.


Creating and Deleting View

To create view, use CREATE VIEW command.

CREATE VIEW view_name AS SELECT command
mysql> CREATE VIEW sample_view_67 AS SELECT * FROM sample54;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM sample_view_67;
+------+------+
| no   | a    |
+------+------+
|    1 |  100 |
|    2 |  900 |
|    4 |   80 |
+------+------+
3 rows in set (0.00 sec)

You can also set column name for a new view. The number of names must be equal to that of SELECT command.

CREATE VIEW view_name(col1, col2, ...) AS SELECT command
mysql> CREATE VIEW sample_view_672(n, v, v2) AS
    -> SELECT no, a, a*2 FROM sample54;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM sample_view_672 WHERE n=1;
+------+------+------+
| n    | v    | v2   |
+------+------+------+
|    1 |  100 |  200 |
+------+------+------+
1 row in set (0.01 sec)

When deleting view, use DROP VIEW.

DROP VIEW view_name


Weakness of View

View doens’t occupy storage. Whenever view is referred, SELECT command of view is performed and the result is stored temporary. Therefore, if there are too much data in database or too many views are used, the rate of processing will decrease.

To solve the temporary storing problem, materialized view was developed. Materialized view is stored in the storage and updated frequently when data changed. However, not all products(including MySQL) support materialized view.

Additionally, in principle, SELECT command of view should be executed independently. This limitation could be avoided by using function table.



All images, except those with separate source indications, are excerpted from lecture materials.

댓글남기기