How to Create a View in MySQL

In MySQL, a view is a virtual table that does not store data but shows data stored in other tables. Views are nothing but SQL queries because they store SQL queries that return a result set. They are also known as virtual tables.

This tutorial goes over how to create and use MySQL views in your database to perform various actions. For instance, you can use views to hide specific data from users, thus allowing them to view the data stored in a table using a view. Views can also enable developers to create simple and abstract connections to a database.

Views: Basic Usage

You can use the general syntax below to create a view in MySQL:

CREATE [OR REPLACE] VIEW `view_name` AS SELECT cols FROM tbl_name;

We start by calling the CREATE VIEW clause, followed by the name of the view we wish to create. The name of the view should be unique across a database and should not be the same name as an existing table. That is due to the feature where views and tables share a similar namespace.

The OR REPLACE clause is an optional parameter that allows you to replace an existing view with the current one. If not specified, creating a view with a name that exists will return an error.

Finally, we specify the SELECT clause followed by the names of the columns for the view. It is good to note that you can also add conditions to the statement to select specific tables where the conditions are met.

Example Use cases

Let us use various examples to illustrate how we can create views in MySQL.

To create a simple view, we can use any sample database such as the Sakila sample DB or create one. For the sake of simplicity, I will use the Sakila database. Consider the query below to create a simple view.

USE sakila;
CREATE VIEW sample_view AS SELECT rental_id, amount FROM payment GROUP BY rental_id;
SHOW TABLES;

Once we execute the above queries, we will create a view with the columns specified. You can see the view created by calling show tables in MySQL as shown below:

----------TRUNCTATED----------------------
 | sample_view                |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+

NOTE: As mentioned, views and tables share the same namespace; thus, they can be viewed as a table in the command above. However, it is not a table that can be seen using the SHOW FULL COMMAND:

| sample_view                | VIEW       |

Once the query is created, you can query the information stored in it as if it were a normal MySQL table. For example:

SELECT * FROM sample_view LIMIT 5;
+-----------+--------+
| rental_id | amount |
+-----------+--------+
|      NULL |   1.99 |
|         1 |   2.99 |
|         2 |   2.99 |
|         3 |   3.99 |
|         4 |   4.99 |
+-----------+--------+
5 rows in set (0.04 sec)

The output will depend on the columns stored in the view.

How to Update a View

MySQL also allows you to modify or update the information in a view without dropping it by using the ALTER clause.

The general syntax for updating a view is:

ALTER VIEW view_name AS SELECT cols FROM table_name;

For example, we can perform a simple update to the sample_view by adding a column with the sum of values as shown in the query below:

Mysql> ALTER VIEW sample_view AS SELECT rental_id, amount, SUM(amount * 10) FROM payment GROUP BY rental_id;
Mysql> DESC sample_view;
+------------------+---------------+------+-----+---------+-------+
| Field            | Type          | Null | Key | Default | Extra |
+------------------+---------------+------+-----+---------+-------+
| rental_id        | int           | YES  |     | NULL    |       |
| amount           | decimal(5,2)  | NO   |     | NULL    |       |
| SUM(amount * 10) | decimal(29,2) | YES  |     | NULL    |       |
+------------------+---------------+------+-----+---------+-------+

The above query updates the data stored in the view without destroying the existing view.

How to Drop a View

Deleting an existing view in MySQL is as simple as calling the DROP clause followed by the name of the view.

For example, to remove the sample_view created in the above sections, we can do:

DROP VIEW IF EXISTS sample_view;

The above query destroys the specified view and all the data stored in it.

Conclusion

In this tutorial, you learned how to create and use MySQL views to create copies of a database table.

from Linux Hint https://ift.tt/3yneD66