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 | 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. |
Views are virtual tables and do not contain the data that is returned. They can save you from writing complex queries again and again.
SELECT
statement. The SELECT
statement cannot contain a sub-query in the FROM clause.SELECT
ed from just like a table.You have to create views, when you want to restrict few columns of your table, from the other user.
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.