Views in MySQL

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.
• “AS SELECT column1, column2 FROM table” statement fetches column1 and column2 from the real 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	ravi@gmail.com	8000.00	Kolkata
987	Vinay Das	9839878678	vinay@yahoo.in	12000.00	Delhi
452	K.Amarnath	7598759387	amar@gmail.com	15000.00	Kolkata
874	Abhinash Desai	7675878798	desai@gmail.com	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. If we want to delete the customer_archive table.

Syntax:

DROP VIEW customer_archive;

2. CREATE OR REPLACE: With CREATE OR REPLACE VIEW command we can update a view / virtual table.

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 the tables. There are different kinds of joins named as an 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 the example of an inner join. In the same manner, we can also apply other joins. In the above example view will be created by consolidating the records which are present in both table_name1 and table_name2 on the basis of 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	ravi@gmail.com	8000.00	Kolkata
987	Vinay Das	9839878678	vinay@yahoo.in	12000.00	Delhi
452	K.Amarnath	7598759387	amar@gmail.com	15000.00	Kolkata
874	Abhinash Desai	7675878798	desai@gmail.com	5000.00	Mumbai
987	Aseem Kumar	9697679867	as@gmail.com	18000.00	Cuttack
989	Sakti	9847984788	hdugu@gmail.com	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 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	ravi@gmail.com	8000.00	Kolkata
987	Vinay Das	9839878678	vinay@yahoo.in	12000.00	Delhi
452	K.Amarnath	7598759387	amar@gmail.com	15000.00	Kolkata
987	Aseem Kumar	9697679867	as@hmail.com	18000.00	Cuttack
989	Sakti	9847984788	hdugu@gmail.com	60000.00	Cuttack

Advantages and Disadvantages of Views in MySQL

Given below are the advantages and disadvantages mentioned:

Advantages:

• Security: There are many tables that 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, then users can be given permission to access some set of views into a database which is authorized to them. This can maintain the security and integrity of data as well as 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 structure of the database even if we do some manipulation to 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 very complex which contains multiple sources and difficult algorithms, then 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 clearly get to know the significance of this command. This comes handy in many real-time scenarios. The major advantage of this is, we can perform many complex queries just to know how effective our underlying algorithm is. Maintaining data security and integrity is the key advantage of view command.

Recommended Articles

This is a guide to Views in MySQL. Here we discuss how to create views in MySQL? different view options, examples, advantages & disadvantages. You may also look at the following articles to learn more –

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?