Views in MySQL 5 – an overview

Views have been introduced MySQL. Lets see how they can be created and used.

Views are nothing but subset of a table in a database.

You may not want all tabular data for a particular purpose. You may want to certain users not to see some part of the tabular data.

Views comes to your rescue in such a situation.

general syntax from MySQL manual

[code lang=”sql”]

CREATE
[OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = { user | CURRENT_USER }]
[SQL SECURITY { DEFINER | INVOKER }]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

[/code]

A very good example straight from the manual.

[code lang=”sql”]

mysql> CREATE TABLE t (qty INT, price INT);
mysql> INSERT INTO t VALUES(3, 50);
mysql> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
mysql> SELECT * FROM v;

[/code]

+------+-------+-------+
| qty  | price | value |
+------+-------+-------+
|    3 |    50 |   150 |
+------+-------+-------+

Published by

Varun Krish

Varun Krish has been dabbling with computers and websites for almost 2 decades. He has traveled to over 30 countries and hopes to visit every country on earth one day. He is currently the Editor-in-Chief of FoneArena.com and also advises startups and product companies on how to build better products. You can follow him on @varunkrish

Leave a Reply

Your email address will not be published.