Introduction

A view is a database object that has no data. The table that acts as the foundation is the basis for the contents. The rows and columns are identical to those of a real table. A View in MySQL is a virtual table created by joining one or more tables in a query. It functions similarly to the base table, except it does not include any data of its own. A view and a table are fundamentally different in that views are definitions built on top of other tables (or views). The changes in the underlying table are mirrored in the View as well.

Advantages of Views

Simplify a difficult query

It enables the user to make complicated queries more understandable. Instead of repeating the lengthy query, we may create a view based on it and use a simple SELECT command.

Reusability is improved.

We know that View simplifies complex queries and lowers them to a single line of code to use VIEWS. Integrating with our applications is significantly easier with this type of coding. This prevents the same formula from being repeated in each query, making the code more reusable and readable.

Assistance with Data Security

It also allows us to show users only the information they are allowed to see while hiding sensitive data like personal and financial information. We can limit the amount of data that consumers have access to by only providing them with the information they require.

Backward compatibility 

A view can also provide backward compatibility in legacy systems. Let’s imagine we want to split a large table into numerous smaller ones without causing any problems for the apps that are already using it. We’ll construct a view with the same name as the real table in this case so that existing apps can refer to it as a table.

MySQL Command Line Client

Using the CREATE VIEW and SELECT statements, we can create a new view. To create a VIEW, SELECT queries are used to extract data from the source table.

Syntax:

CREATE [OR REPLACE] VIEW view_name AS

SELECT columns

FROM tables

[WHERE conditions];

Example:

CREATE VIEW trainer AS    
SELECT course_name, trainer     
FROM courses;  
Using the following syntax, we can see the newly generated view:
SELECT * FROM view_name;

 

Example:

SELECT * FROM trainer;

Output:

MySQL Update VIEW

The syntax for updating an existing view in MySQL is as follows:

ALTER VIEW view_name AS

SELECT columns

FROM table

WHERE conditions;

Example:

ALTER VIEW trainer AS    
SELECT id, course_name, trainer  
FROM courses;

 

MySQL Drop VIEW

The syntax for updating an existing view in MySQL is as follows:

Syntax

DROP VIEW [IF EXISTS] view_name;

Example:

DROP VIEW trainer;

Output: