MySQL Views

MySQL CREATE VIEW Statement

In SQL, a view is a virtual table created from the result-set of an SQL query.

It consists of rows and columns, similar to a physical table, where the fields are derived from one or more real tables in the database.

Views allow you to incorporate SQL statements and functions, presenting data as though it originates from a single table.

A view is established using the CREATE VIEW statement.

CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Note: A view always displays current data! The database engine refreshes the view each time it is queried.

The following SQL statement creates a view that displays all customers from Brazil:

CREATE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName
FROM Customers
WHERE Country = ‘Brazil’;

We can retrieve data from the view above using the following query:

SELECT * FROM [Brazil Customers];

The following SQL statement creates a view that selects every product from the ‘Products’ table with a price higher than the average price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

We can retrieve data from the above view using the following query:

SELECT * FROM [Products Above Average Price];

A view can be modified using the CREATE OR REPLACE VIEW statement.

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

The following SQL statement includes the “City” column in the “Brazil Customers” view:

CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = ‘Brazil’;

A view is removed using the DROP VIEW statement.

DROP VIEW view_name;

The following SQL statement deletes the “Brazil Customers” view:

DROP VIEW [Brazil Customers];