how to create a view, sql views, mysql views, delete a view in sql, update a view in sql, types of views in mysql, mysql views vs tables

What are views in MySQL database

While working with MySQL databases you may found yourself in a situation where you need to either combine results from different database tables not while you are displaying the result but have a table that can combine the results of these two or more tables and store the result back in the database such that while you are displaying the results to the user you just use a simple select function. The MySQL database provides the use of views that store results temporally in the database.

In this article, we shall discuss what views in the MySQL database are, show how they are created, how they store data and give examples while using MySQL views.

What is a View in MySQL Database

A view is a virtual table that stores result based on the result-set of an SQL statement. In that, it stores real-time information depending on the result of a set query.

The view contains columns and rows just like a normal table but what we ought to understand is that views are not stand-alone since they depend on data from other tables.

A view is basically an SQL query presented as a table.

How to create a View in MySQL Database

Views are not created as normal tables rather they are created after a set result is obtained.

We use the CREATE view viewname AS statement

The create statement above takes the result from a SELECT statement and converts it as a view using the AS keyword.

For example, if we want to create a view called newusers from a database table called users we will use the create view statement as follows

CREATE VIEW newusers AS (SELECT * FROM users)

Working with Views in MySQL database

As we have seen above, views result from a set of queries.

When you create a view in a MySQL database, you are given access to it such that you can open it and view the data inside the view

The good thing with views is that they store real data as long as it matches the query that created it. For example, in a table that you have three entries and you create a view when the entries are three and in the next minute you add two more entries in the same table, you do not have to update the view to fetch the result, it will work with the real data while it's displaying the result.

Example  – creating and storing data in a view

I have a table called users like shown below in the screenshot

what is a view in mysql, sql views, create views in mysql database

I want to create a view that holds the values of users whose status is one

CREATE VIEW status1_users AS SELECT * FROM users WHERE status= '1'

When I execute the above code snippet, I will obtain a response like the one shown below

what is a view in mysql, sql views, create views in mysql database

At the root of the database, a new section will be added that has views

what is a view in mysql, sql views, create views in mysql database

In the above screenshot, you can see the difference between a normal table and a view as defined by the type and also you can note that in the view section there is no option to empty the view

When you open the view, the view will look exactly like a table with the result that you defined in the query

How to display data from a View

To display data from a view, the view is treated as a normal table therefore we use the SELECT keyword

SELECT * FROM status1_users

How to Update a View

Suppose you want to add an extra column that was not present at the time you were creating the view, you can update the view using this code snippet

In my case, I will now store the users that have status zero

CREATE OR REPLACE VIEW status1_users AS SELECT * FROM users WHERE status= '0'

How to Delete a View

To delete a view, we use the DROP keyword

DROP VIEW status1_users;


Hope you have learned what views are, what they do, how they store data, how to work with them, and the different operations that are performed on views. That’s all from this article.