View in MySQL


  • In MySQL, view is a virtual table whose contents are result-set of an SQL statement.
  • A View does not hold any data. It only stores SQL statement.
  • A view contains rows and columns.The columns in a view are columns from one or more base tables in the database.
  • We can create a view by selecting columns from one or more tables.
  • We can create View using CREATE VIEW statement.
Syntax 
CREATE OR REPLACE VIEW view_name AS 
SELECT
   column1,
   column2... 
FROM
   table_name 
WHERE
   conditions;

Example 1: Creating View

Sample Table : order_details

In this example we will create a View named report from the table order_details.

Create View named report 
CREATE VIEW report AS 
SELECT
   order_no,
   sum(qty) as QTY,
   sum(total) as TOTAL 
FROM
   order_details 
GROUP BY
   order_no;
We can query the view as follows: 
SELECT * FROM report;

Example 2 : Updating a View

We can update a view with the CREATE OR REPLACE VIEW statement.

The following SQL removes the QTY column from the report view:

Create View named report 
CREATE OR REPLACE VIEW report AS 
SELECT
   order_no,
   sum(total) as TOTAL 
FROM
   order_details 
GROUP BY
   order_no;

Example 3 : Dropping a View

We can Delete a view with the DROP VIEW statement.

Syntax 
DROP VIEW view_name;

The following SQL Delete the report view:

Syntax 
DROP VIEW report;