MYSQL VIEWS

The VIEW is the virtual table that can combines the columns of multiple tables and returns the records. It doesnt hold the actual table data.

With the help of VIEW we can hide the complex query behavior from others.

CREATE VIEW

Using the CREATE VIEW statement it is possible to create a new view.

Syntax for CREATE VIEW

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

Example

CREATE VIEW [Indian Employees] AS
select * from employees
where location = 'India';

To query the above view with the name [Indian Employees], we use the following query

select * from [Indian Employees];

This will return the records of the employees whose location is India.

Example 2

CREATE VIEW [Ordered Customers] AS
    select customer.name from 
    customer
    inner join orders
    on customer.cust_id=orders.cust_id;

Query the [Ordered Customers] view with the following query

SELECT * FROM [Ordered Customers];

UPDATE VIEW

Its possible to update an existing VIEW using the command CREATE OR REPLACE VIEW.

Syntax

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

Example

CREATE VIEW [Indian Employees] AS 
select name,location from employees 
where location = 'India';

The above query will update [Indian Employees] view to return the name and location of Indian employees.

DROP VIEW

To delete an existing view, use the DROP VIEW command.

Syntax

DROP  VIEW  view_name;

Example

DROP VIEW [Indian Employees];

Most Read