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
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
At the root of the database, a new section will be added that has views
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.
