A view is a database object that doesn’t have any data in it. Its contents are based on the table that serves as the foundation. It has the same rows and columns as a genuine table. In MySQL, a View is a virtual table that is produced by connecting one or more tables in a query. It works in the same way as the base table, but it doesn’t have any data of its own. The fundamental distinction between a view and a table is that views are definitions constructed on top of other tables (or views). If the underlying table changes, the changes are mirrored in the View as well.
MySQL allows us to create a view in mainly two ways:
Let us discuss both in detail. We can create a new view by using the CREATE VIEW and SELECT statement. SELECT statements are used to take data from the source table to make a VIEW.
CREATE [OR REPLACE] VIEW view_name AS
Suppose our database has a table course, and we are going to create a view based on this table. Thus, the below example will create a VIEW name “test” that creates a virtual table made by taking data from the table courses.
CREATE VIEW test AS
SELECT test_name, id
Once the execution of the CREATE VIEW statement becomes successful, MySQL will create a view and stores it in the database.
We can see the created view by using the following syntax:
SELECT * FROM view_name;
Let’s see how it looks the created VIEW:
SELECT * FROM test;
NOTE: A view does not store the data physically. When we execute the SELECT statement for the view, MySQL uses the query specified in the view’s definition and produces the output. Due to this feature, it is sometimes referred to as a virtual table.
In MYSQL, the ALTER VIEW statement is used to modify or update the already created VIEW without dropping it.Syntax:
Following is the syntax used to update the existing view in MySQL:
ALTER VIEW view_name AS
Example: The following example will alter the already created VIEW name “trainer” by adding a new column.
ALTER VIEW test AS
SELECT test_name, id, id2
Once the execution of the ALTER VIEW statement becomes successful, MySQL will update a view and stores it in the database. We can see the altered view using the SELECT statement, as shown in the output:
We can drop the existing VIEW by using the DROP VIEW statement.
DROP VIEW [IF EXISTS] view_name;
Here, we will see the complex example of view creation that involves multiple tables and uses a join clause.
CREATE VIEW test
AS SELECT c.test, c.id, t.test
FROM tests c, test2 t
WHERE c.id = t.id;
We can verify the view using the SELECT statement shown in the below image:
Create View using MySQL Workbench
To create a view in the database using this tool, we first need to launch the MySQL Workbench and log in with the username and password to the MySQL server. It will show the following screen:
1. Go to the Navigation tab and click on the Schema menu. Here, we can see all the previously created databases. Select any database under the Schema menu, for example, testdb1. It will pop up the option that can be shown in the following screen.
2. Next, we need to right-click on the view option, and a new pop up screen will come:
3. As soon as we select the “Create View” option, it will give the below screen where we can write our own view.
4. After completing the script’s writing, click on the Apply button, we will see the following screen:
5. In this screen, we will review the script and click the Apply button on the database
6. Finally, click on the Finish button to complete the view creation. Now, we can verify the view as below:
The user benefits from MySQL view in the following ways:
Simplify difficult queries – It allows the user to make complex queries easier to understand. We can develop a view based on the complex query to utilise a simple SELECT statement instead of typing the complex query again. Reusability is improved.
To use VIEWS, we know that View simplifies complex queries and reduces them to a single line of code. This type of coding makes integrating with our programme much easier. This eliminates the possibility of repeating the same formula in each query, making the code more reusable and readable.
Assistance with Data Security
It also enables us to display just permitted information to users while concealing sensitive data such as personal and financial information. We can limit which information consumers have access to by giving them only the information they need.
Backward compatibility should be enabled.
In legacy systems, a view can also offer backward compatibility. Let’s say we wish to break a large table into several smaller ones without disrupting the present apps that use it. In this situation, we’ll create a view with the same name as the real table so that existing apps can refer to it like a table.