MySQL CREATE VIEW Statement

Overview

In this tutorial, we will learn about MySQL CREATE VIEW statement. A view is a database object that is defined in terms of a SELECT statement that retrieves the data from regular database tables or another views. The underlying table used in the view statement is called as the base table.

Views can also be used with statements like INSERT, DELETE and Update to modify the underlying base table.

CREATE VIEW Statement

Views are called as ‘virtual tables’. We can define a view with a CREATE VIEW statement.

Syntax

The general syntax of the statement is as follows::

CREATE [OR REPLACE] VIEW view_name [columns]

SELECT_STATEMENT

The CREATE VIEW statement creates a new view or replaces an existing view if the REPLACE clause is specified. The SELECT_STATEMENT is the SELECT statement that defines the view.

By default, the new view would be created in the default database. To create the view explicitly in a particular database, we need to specify the database or schema name.

<database_name>.<view_name>

A view can refer to base tables or views in other databases as well. We can qualify the table or view with the database name.

Example

Let’s create a view on the city base table in the world MySQL database. The city table has five columns. However, in this view, we want to hide the district and population columns.

mysql> CREATE VIEW v_city

SELECT id, name, countrycode FROM city;

MySQL CREATE VIEW Statement

A database cannot contain a base table and a view that have the same name. Columns retrieved by the SELECT statement for the view can also use functions, operators, etc. A view can be created from Joins, subqueries, etc.

—