Hevo’s Guide on MySQL Views for Beginners

This article was published as a part of the Data Science Blogathon.

A MySQL view can be defined as a virtual table based on the result of SQL statements, which helps in simplifying the data for analysis and reporting and offers better security. Here is how to create MySQL views and update, drop or rename them using the specific queries. A MySQL view is ultimately a result of the SQL query execution which returns the required data from the multiple tables.

What are MySQL Views?

MySQL Views are virtual tables based on the set of SQL statements, they are database objects that have no values as they do not store any data of their own but display the contents that are based on the source table. Views contain rows and columns just like the real table and can be created in MySQL using queries by joining one or more tables. Views help the users as they are saved from writing complex queries again and again, and by showing only the intended data to the authorized users they help in protecting the sensitive data of the businesses thus resulting in improved security of the database.

Why do we Use View in MySQL?

Some of the reasons why we use views in MySQL are as follows:-

• Views can be used as a security mechanism as they offer data security by surfacing only the intended data to the users thus keeping a check on the security of the sensitive data of the business such as the personal details and banking information.

• Views help in simplifying the access to the data because they get users rid of writing complex queries again and again, by using views the complex queries can be converted into a single line of queries which improves their integration in the application thus increasing the reusability.

• Views help in optimizing the database experience as they assist in defining tables without using any extra storage and also help in data analysis.

• Using Views of the original table structure instead of the actual tables helps in ensuring that when you refactor your database, then your legacy code will see the original schema via the view which helps legacy code from breaking allowing you to change the legacy code as per your requirement.

How to Create Views in MySQL?

In order to create views in MySQL, we can use CREATE VIEW Command, the basic syntax used to create a view in MySQL is:-

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

Example:- Suppose we want to create a view that shows all the customers from Bangalore city then we can use the following SQL:-

CREATE VIEW [Bangalore Customers] AS

SELECT CustomerName, ContactName

FROM Customers

WHERE City = ‘Bangalore’;

Now we can query the above as follows:-

SELECT * FROM [Bangalore Customers];

How to Update Views in MySQL?

In order to update a view in MySQL, we can use CREATE OR REPLACE VIEW statement, here we can provide the information regarding what needs to be updated and the query will process it. The syntax for CREATE OR REPLACE VIEW statement is:-

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

Example:- Let’s say we want to add the “Sector” column to the “Bangalore Customers” View, then:-

CREATE VIEW [Bangalore Customers] AS

SELECT CustomerName, ContactName, Sector

FROM Customers

WHERE City = ‘Bangalore’;

Now we can query the above as follows:-

SELECT * FROM [Bangalore Customers];

How to Drop Views in MySQL?

In order to drop views in MySQL, we can use the DROP VIEW statement, here we just need to provide the name of the view that we want to drop along with the syntax and it will be dropped. The syntax for the DROP VIEW statement is as follows:-

DROP VIEW view_name;

Example: The following SQL will drop the View “Bangalore Customers”:-

DROP VIEW [Bangalore Customers];

How to Rename Views in MySQL?

In order to rename a view in MySQL, we can use the RENAME TABLE statement because the view and table share the same namespace in MySQL, the syntax for RENAME TABLE statement is as follows:-

RENAME TABLE original_view_name
TO new_view_name;

In the above syntax first, specify the name of the view that you want to rename after the RENAME TABLE keywords then specify the new name of the view after the TO keyword and the view will be renamed.

Example: Let’s rename the “Bangalore Customers” view with the “Bengaluru Customers”:-

RENAME TABLE Bangalore Customers

To Bengaluru Customers;

Advantages of MySQL Views

Using views in MySQL offers various advantages such as:-

1. Improved Data Security: A table can expose a lot of data such as personal and banking details, MySQL View can serve as a security mechanism by surfacing only the intended data to the users thus restricting their access to sensitive business information.

2. Query Simplicity: By using Views the complex queries can be converted into a single line of queries which helps in simplifying the access to the data because the users get rid of writing complex queries again and again.

3. Database Optimization: Views help in optimizing the database experience as they assist in defining tables without using any extra storage and also help in data analysis.

5. Consistency: A View helps to maintain consistency by presenting an unchanged, consistent image of the database structure even when the source tables are split, restructured, or renamed.

6. Backward Compatibility: MySQL views can enable backward compatibility in legacy systems, Let’s say we want to split a large table into many smaller ones without affecting the current applications that refer to the table. In this case, we will create a view with the same name as the real table so that the current applications can reference the view as if it were a table.

Conclusion

In this blog, we talked about major aspects of MySQL views such as how to create views, update, drop and rename them along with their syntax and some examples which can serve you in creating MySQL views for analysis and reporting. The blog also talked about the uses and advantages of MySQL views. Here are some of the key takeaways from the article:-

• Views are virtual tables created using specific queries, they do not contain any data of their own.

• Views help in simplifying the process and saving time as you do not have to write complex queries again and again.

• SQL Views offers better security by allowing us the option to give users access to the view instead of the actual tables.

The media shown in this article is not owned by Analytics Vidhya and is used at the Author’s discretion.