MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+


From WikiOD

Syntax[edit | edit source]

  • 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[edit | edit source]

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[edit | edit source]

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 SELECT statement. The SELECT statement 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.

Create a View[edit | edit source]


The CREATE VIEW statement requires the CREATE VIEW privilege for the view, and some privilege for each column selected by the SELECT statement. For columns used elsewhere in the SELECT statement, you must have the SELECT privilege. If the OR REPLACE clause is present, you must also have the DROP privilege for the view. CREATE VIEW might also require the SUPER privilege, depending on the DEFINER value, as described later in this section.

When a view is referenced, privilege checking occurs.

A view belongs to a database. By default, a new view is created in the default database. To create the view explicitly in a given database, use a fully qualified name

For Example:


mysql> CREATE VIEW test.v AS SELECT * FROM t;

Note - Within a database, base tables and views share the same namespace, so a base table and a view cannot have the same name.

A VIEW can:

  • be created from many kinds of SELECT statements
  • refer to base tables or other views
  • use joins, UNION, and subqueries
  • SELECT need not even refer to any tables

Another Example

The following example defines a view that selects two columns from another table as well as an expression calculated from those columns:

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;
| qty  | price | value |
|    3 |    50 |   150 |


  • Before MySQL 5.7.7, the SELECT statement cannot contain a subquery in the FROM clause.
  • The SELECT statement cannot refer to system variables or user-defined variables.
  • Within a stored program, the SELECT statement cannot refer to program parameters or local variables.
  • The SELECT statement cannot refer to prepared statement parameters.
  • Any table or view referred to in the definition must exist. After the view has been created, it is possible to drop a table or view that

the definition refers to. In this case, use of the view results in an error. To check a view definition for problems of this kind, use the CHECK TABLE statement.

  • The definition cannot refer to a TEMPORARY table, and you cannot

create a TEMPORARY view.

  • You cannot associate a trigger with a view.
  • Aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias

length of 256 characters).

  • A VIEW may or may not optimize as well as the equivalent SELECT. It is unlikely to optimize any better.

A view from two tables[edit | edit source]

A view is most useful when it can be used to pull in data from more than one table.

SELECT a.*, b.extra_data FROM main_table a 
LEFT OUTER JOIN other_table b 
ON =

In mysql views are not materialized. If you now perform the simple query SELECT * FROM myview, mysql will actually perform the LEFT JOIN behind the scene.

A view once created can be joined to other views or tables

Updating a table via a VIEW[edit | edit source]

A VIEW acts very much like a table. Although you can UPDATE a table, you may or may not be able to update a view into that table. In general, if the SELECT in the view is complex enough to require a temp table, then UPDATE is not allowed.

Things like GROUP BY, UNION, HAVING, DISTINCT, and some subqueries prevent the view from being updatable. Details in reference manual.

DROPPING A VIEW[edit | edit source]

    • Create and drop a view in the current database.
CREATE VIEW few_rows_from_t1 AS SELECT * FROM t1 LIMIT 10;
DROP VIEW few_rows_from_t1;
    • Create and drop a view referencing a table in a different database.
DROP VIEW table_from_other_db;