Views in MySQL

Updated May 10, 2023

Introduction to Views in MySQL

The following article provides an outline for Views in MySQL. SQL view is nothing but a virtual table of the database. The view contains fields like a real table, but those fields are from one or more tables in the database which is executed by running a bunch of MySQL queries. We can perform operations like WHERE and JOIN clauses in the virtual tables. On the other hand, VIEW is nothing but SELECT queries.

Syntax:

CREATE VIEW view_name AS
SELECT column1, column2,…
FROM table;

• “CREATE VIEW view_name” commands MySQL to create a view/virtual table in the name of view_name.
• The “AS SELECT column1, column2 FROM table” statement fetches column1 and column2 from the actual table. Then it saves those fields in the virtual table.

How to Create Views in MySQL?

Let’s create one customer table with the following attributes:

Customer_id	Customer_name	Contact_no	Email	Purchased_amount	City
184	Ravi Kumar	9887463893	[email protected]	8000.00	Kolkata
987	Vinay Das	9839878678	[email protected]	12000.00	Delhi
452	K.Amarnath	7598759387	[email protected]	15000.00	Kolkata
874	Abhinash Desai	7675878798	[email protected]	5000.00	Mumbai

We will create one customer_archive table with our required attribute.

Code:

CREATE VIEW customer_archive AS
SELECT customer_id, customer_name, contact_no, city
FROM customer;

Output:

Customer_id	Customer_name	Contact_no	City
184	Ravi Kumar	9887463893	Kolkata
987	Vinay Das	9839878678	Delhi
452	K.Amarnath	7598759387	Kolkata
874	Abhinash Desai	7675878798	Mumbai

We can also apply the condition to this query while creating a view.

Code:

CREATE VIEW customer_archive AS
SELECT customer_id, customer_name, contact_no, purchased_amont, city
FROM customer
WHERE purchased_amont > 10000;

Output:

Customer_id	Customer_name	Contact_no	Purchased_amount	City
987	Vinay Das	9839878678	12000.00	Delhi
452	K.Amarnath	7598759387	15000.00	Kolkata

Different View Options in MySQL

Following are the different view options in MySQL:

1. DROP: A view/virtual table can be deleted using the DROP VIEW command. Suppose we want to delete the customer_archive table.

Syntax:

DROP VIEW customer_archive;

2. CREATE OR REPLACE: We can update a view / virtual table with the CREATE OR REPLACE VIEW command.

Syntax:

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2,….
FROM table;

3. JOIN: We also can create a view by joining multiple tables. This join will fetch matched records from both tables. Different kinds of joins are named inner join, left join, right join, full outer join, cross join, etc.

Syntax:

CREATE VIEW view-name AS
SELECT column1, column2, column3, …
FROM table_name1 INNER JOIN table_name2
ON table_name1.column = table_name2.column;

Above is an example of an inner join. In the same manner, we can also apply other joins. In the above example, a view will be created by consolidating the records present in table_name1 and table_name2 based on a common field.

Examples of Views in MySQL

Let us consider the above example:

Customer_id	Customer_name	Contact_no	Email	Purchased_amount	City
184	Ravi Kumar	9887463893	[email protected]	8000.00	Kolkata
987	Vinay Das	9839878678	[email protected]	12000.00	Delhi
452	K.Amarnath	7598759387	[email protected]	15000.00	Kolkata
874	Abhinash Desai	7675878798	[email protected]	5000.00	Mumbai
987	Aseem Kumar	9697679867	[email protected]	18000.00	Cuttack
989	Sakti	9847984788	[email protected]	60000.00	Cuttack

This is the customer table database.

We will create another view of it, naming it as a premium_customer. Conditions for premium customers will be while purchased_amount will be greater than 10000.

Code:

CREATE VIEW premium_customer AS
SELECT customer_id, customer_name, contact_no, purchased_amont, city
FROM customer
WHERE purchased_amont > 10000;

Output:

Customer_id	Customer_name	Contact_no	Purchased_amount	City
987	Vinay Das	9839878678	12000.00	Delhi
452	K.Amarnath	7598759387	15000.00	Kolkata
987	Aseem Kumar	9697679867	18000.00	Cuttack
989	Sakti	9847984788	60000.00	Cuttack

If we want to drop this premium_customer virtual table, then below is the syntax.

DROP VIEW premium_customer;

If some modification is required to this above premium table by modifying some conditions, then

Code:

CREATE OR REPLACE VIEW premium_customer AS
SELECT customer_id, customer_name, contact_no, email, purchased_amont, city
FROM customer
WHERE purchased_amount > 6000;

Output:

Customer_id	Customer_name	Contact_no	Email	Purchased_amount	City
184	Ravi Kumar	9887463893	[email protected]	8000.00	Kolkata
987	Vinay Das	9839878678	[email protected]	12000.00	Delhi
452	K.Amarnath	7598759387	[email protected]	15000.00	Kolkata
987	Aseem Kumar	9697679867	[email protected]	18000.00	Cuttack
989	Sakti	9847984788	[email protected]	60000.00	Cuttack

Advantages and Disadvantages of Views in MySQL

Given below are the advantages and disadvantages mentioned:

Advantages

• Security: Many tables are restricted from many users as some attributes in those tables will be very sensitive. So, if we can create views with some specific attributes for respective users, users can access some views in an authorized database. This can maintain the security and integrity of data, and users can perform their tasks with respective authorized columns.
• Query Simplicity: A view can be created by fetching data from several tables. So, all the cumulative records from all tables can be represented by a single table using the view query.
• Structural Simplicity: we can create a specialized or personalized user-specific view. So we can represent the database as a set of virtual tables that make sense to the user.
• Consistency: we are mentioning consistency here because this view can represent a consistent and unchanged image of the database structure even if we manipulate the main table or master table.
• Data Integrity: if the data is accessed to a view, the database always checks the data to ensure whether it satisfies the integrity constraints or not.

Disadvantages

• Performance: Views are the virtual table or representative of master tables. When we run some queries to create a view, DBMS translates those queries against the views into the queries in underlying tables. So, if the view query is complex and contains multiple sources and difficult algorithms, simple action against those views takes considerable time.
• Update Restrictions: While changing rows in a view, the DBMS must translate the request into an update on rows of the underlying source table. The update can be done in the simple query, but in the case of a complex query, DBMS won’t allow updating as views are often restricted to read-only.

Conclusion

After going through the above-described stuff, we can know the significance of this command. This comes in handy in many real-time scenarios. The significant advantage is that we can perform many complex queries to understand our underlying algorithm’s effectiveness. Maintaining data security and integrity is the crucial advantage of view command.

Recommended Articles

We hope that this EDUCBA information on “Views in MySQL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. Top 3 MySQL Operators
2. Different MySQL Query Commands
3. Key Differences of Inner Join vs. Outer Join
4. MySQL Outer Join | How to Use?