Views in MySQL

Introduction

A database management system (DBMS) like MySQL possesses the important property of developing an abstract view of a database providing different views in a database for different users. This abstract view enables the end users to access the data easily without worrying about the underlying complexities. Therefore, to implement abstraction in a database, there is a concept known as views in MySQL.

Views in MySQL are nothing but virtual tables generated by the result of a SELECT query. A view can be created by joining one or more base tables. A view is a concept rather than an actual table. The tuples or data inside a view in MySQL are from the base table only. View does not store any physical data rather it retrieves data from underlying base tables. The concept of view in a database helps us to simplify complex tables and queries by storing them in a virtual or abstract table. Any changes made in the base table will reflect in the virtual table (view) as well.

Views in MySQL provide security as it refrains users from directly accessing the database. It provides an extra layer of security by allowing restricted access to sensitive or complex data. The performance of a database also improves by using views as it provides an abstraction to underlying complexities which eventually results in a much simpler version of the table that is easier to use and understand.

MySQL Allows Us to Create a View in Mainly Two Ways

View in MySQL can be created using both command line client and GUI a based MySQL DBMS.

Let's look at the syntax:

MySQL Command Line Client

Syntax:

Above is the syntax used to create a view for an existing table using the Command line client provided by MySQL.

MySQL Workbench

Syntax:

Above is the syntax used to create a view using MySQL Workbench (GUI) application.

Parameters:

Both ways will have the same parameters used to create a view which is as follows:

• view_name: view_name refers to the name of the view we will create.
• WHERE Clause: WHERE clause in a SQL query refers to a condition that is used to filter the data. This parameter is optional when working with views in MySQL.

Example

Let's create a view in MySQL Command line client using an existing table inside a database. Suppose, we have a table EMPLOYEE which displays the details of employees working in an organization.

We will be using this table as a reference in the upcoming examples.

Let's create a view out of the table Employee as EMP_DETAILS which will display two columns from the existing or base table.

Query:

Output:

Explanation: Using the EMPLOYEE table as a base table, we have created a view EMP_DETAILS. The columns Emp_id and Emp_salary are displayed as a new table that doesn't have its data but retrieves the data from the base table EMPLOYEE.

MySQL Update VIEW

Views in MySQL can be updated using the ALTER VIEW command. The ALTER VIEW command updates or changes a view without deleting it.

Syntax:

Example Here, we will update the already existing view EMP_DETAILS by adding an extra column first_name in the view.

Query:

Output:

Explanation: In the above query, we have updated the number of columns being displayed in the view EMP_DETAILS using the ALTER VIEW command. The original table can be seen in the previous example.

MySQL Drop VIEW

MySQL DROP VIEW command is used to delete or drop a particular view by providing the name of the view to be dropped.

Syntax

Parameters

• IF EXISTS: IF EXISTS checks whether a view is already present in a database or not before dropping it. Using IF EXISTS is a good practice to handle errors. This is optional.
• nameOfView: nameOfView refers to the name of the view created.

Example Here, we will drop the EMP_DISPLAY view using the DROP VIEW command in MySQL.

Query:

Output:

Explanation: As seen in the output window, the view EMP_DETAILS is deleted or dropped from the database, and therefore it shows EMP_DETAILS doesn't exist.

MySQL Create View with JOIN Clause

Views in MySQL can also be created using the JOIN clause. JOIN clause joins two tables based on a condition. Here, we will create a view that involves more than one table.

Suppose, we have one more table Employee_Address which depicts the address of respective employees of the EMPLOYEE table.

Query:

Output:

Explanation:

In the above query, we have created a view ADD_DETAILS using two tables EMPLOYEE and Employee_Address. The concept of INNER JOIN is applied to join both tables and to create a virtual table (view) that displays listed columns from both tables.

Create View using MySQL Workbench

MySQL Workbench is a GUI application developed by Oracle that is used to create and manipulate data in a database.

Let's create a view using the MySQL Workbench application:

STEP 1: In the schema section of MySQL workbench, right-click on the view option from the emp database section. Then, click on the Create button.

STEP 2: On clicking the create button, a space for creating a view opens where we can write the query for view creation.

STEP 3: Now, click on the apply button.

After which in the next screen the query should be reviewed and then again click on apply button.

STEP 4: A view EMP_DETAILS is created which can be displayed using a SELECT query.

Why We Use View?

Views in MySQL are beneficial in several ways. Let's discuss them in detail:

Simplify Complex Query:

Views in MySQL help us to convert complex queries into simpler ones. We can display a view by using a simple SELECT query rather than using a complex query.

Increases the Re-usability:

Views convert long complex queries into simpler single-line queries. This makes the queries more readable. Simpler queries can be used several times in an application which increases re-usability.

Help in Data Security:

Views in MySQL provide data security as it refrains users from directly accessing the database.

Enable Backward Compatibility:

Views in MySQL enable backward compatibility as virtual tables (view) can be changed without affecting the base table.