MySQL VIEW

What is a VIEW in SQL and why is it used?

A VIEW is used to view the data of one or more tables by selecting specific rows and columns and arranging the data in a proper way. So, it works like a virtual table.

A VIEW is mainly used to view the required data from a large table without directly working with the complete table.

A VIEW is based on an SQL query. It does not normally store a separate copy of the original table's data. Instead, the actual data remains stored in the original table, and the VIEW shows the required data according to the SQL query defined for it.

It helps you access the required data without directly working with the complete table and without losing the original data.

Syntax:

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

Example:

Suppose we have a Customer table with the following data:

Customer_ID	Name	City	Salary
1	Rajesh	Chennai	30000
2	Amita	Patna	140000
3	Neha Sinha	Noida	160000
4	Pooja Khanna	Jamshedpur	80000

Now, you can view the data of customers whose salary is greater than 100000. For this, you can create a VIEW by using the following statement:

CREATE VIEW High_Salary_Customers AS
SELECT Customer_ID, Name, City, Salary
FROM Customer
WHERE Salary > 100000;

After creating the VIEW, you can use the following statement to view its data:

SELECT * FROM High_Salary_Customers;

Output:

Customer_ID	Name	City	Salary
2	Amita	Patna	140000
3	Neha Sinha	Noida	160000

Here, both customers have a salary greater than 100000, so they are shown in the High_Salary_Customers VIEW.

Types of VIEW in MySQL

Four types of VIEW in MySQL are the following:

1. Simple VIEW
2. Complex VIEW
3. Updatable VIEW
4. Read-Only VIEW

1. Simple VIEW:

In a Simple VIEW, you can see the data of only one table by selecting one or more columns using the VIEW statement. The selected data is shown in a separate virtual table.

In this VIEW, no complex operation or function is normally used. It simply takes the required data from the table and shows it through the VIEW.

Example:

Suppose we want to view only the Name and City columns from the Customer table.

CREATE VIEW Customer_Simple_View AS
SELECT Name, City
FROM Customer;

To view the data of the VIEW:

SELECT * FROM Customer_Simple_View;

Output:

Name	City
Rajesh	Chennai
Amita	Patna
Neha Sinha	Noida
Pooja Khanna	Jamshedpur

Here, the output simply shows the name and city of all customers, which means you can see this data through the VIEW.

2. Complex VIEW:

A Complex VIEW is not as simple as a Simple VIEW. It is complex because one or more operations and functions are applied to it, such as JOIN, GROUP BY, aggregate functions, or calculated values.

So, it is used to view the data from one or more tables by applying different operations and functions to the data to show the specific data required by the user.

Example:

Suppose we want to view the average salary of customers according to their city.

CREATE VIEW Customer_Salary_View AS
SELECT City, AVG(Salary) AS Average_Salary
FROM Customer
GROUP BY City;

To view the data of the VIEW:

SELECT * FROM Customer_Salary_View;

Output:

City	Average_Salary
Chennai	30000
Patna	140000
Noida	160000
Jamshedpur	80000

3. Updatable VIEW:

An Updatable VIEW is a VIEW through which you can update the data of the original table whose data you want to view. It provides the facility to make changes to the original table through the VIEW, such as INSERT, UPDATE, and DELETE.

But you can do this only when the VIEW meets all the required conditions checked by MySQL for updating the data.

Now, here is the list of full conditions:

• To check that the VIEW is based on only one underlying table.
• To check that the VIEW statement does not use aggregate functions such as SUM(), COUNT(), and AVG(), and does not use GROUP BY, HAVING, or DISTINCT.
• To check that the VIEW statement does not use the UNION and UNION ALL statements.
• To check that the query applied in the VIEW is updatable.

Example:

Suppose we want to create a VIEW to view the Customer_ID, Name, and Salary columns of the Customer table.

CREATE VIEW Customer_Update_View AS
SELECT Customer_ID, Name, Salary
FROM Customer;

To update the salary of a customer through the VIEW:

UPDATE Customer_Update_View
SET Salary = 150000
WHERE Customer_ID = 2;

Here, the customer's salary was 140000 before. It was updated through the VIEW statement, and the new salary is 150000.

4. Read-Only VIEW:

A Read-Only VIEW is a VIEW in which data is fetched from the columns of the original table and shown in a virtual table only for reading.

In this VIEW, data can only be read. It does not provide the facility to update, insert, or delete data like an Updatable VIEW. A VIEW is read-only when it contains operations or features that make it non-updatable.

Example:

Suppose we want to view the average salary of customers according to their city.

CREATE VIEW Customer_ReadOnly_View AS
SELECT City, AVG(Salary) AS Average_Salary
FROM Customer
GROUP BY City;

To view the data of the VIEW:

SELECT * FROM Customer_ReadOnly_View;

Output:

City	Average_Salary
Chennai	30000
Patna	140000
Noida	160000
Jamshedpur	80000

Here, the VIEW statement is used only to see the required average salary data according to the city.