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]
AS
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
AS
SELECT id, name, countrycode FROM city;
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.
—
MySQL Tutorials
MySQL Tutorials on this website:
https://www.testingdocs.com/mysql-tutorials-for-beginners/
For more information on MySQL Database: