MySQL VIEW
Syntax
-
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition; ///Simple create view syntax
-
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]; /// Full Create view syntax
-
DROP VIEW [IF EXISTS] [db_name.]view_name; ///Drop view syntax
Parameters
| Parameters | Details |
|---|---|
| view_name | Name of View |
| SELECT statement | SQL statements to be packed in the views. It can be a SELECT statement to fetch data from one or more tables. |
Remarks
Views are virtual tables and do not contain the data that is returned. They can save you from writing complex queries again and again.
- Before a view is made its specification consists entirely of a
SELECTstatement. TheSELECTstatement cannot contain a sub-query in the FROM clause. - Once a view is made it is used largely just like a table and can be
SELECTed from just like a table.
You have to create views, when you want to restrict few columns of your table, from the other user.
- For example: In your organization, you want your managers to view few information from a table named-"Sales", but you don't want that your software engineers can view all fields of table-"Sales". Here, you can create two different views for your managers and your software engineers.
Performance. VIEWs are syntactic sugar. However there performance may or may not be worse than the equivalent query with the view's select folded in. The Optimizer attempts to do this "fold in" for you, but is not always successful. MySQL 5.7.6 provides some more enhancements in the Optimizer. But, regardless, using a VIEW will not generate a faster query.
