Sometimes our database over time becomes too complex, confusing (or maybe the architecture in the first place is too magic) to control everything. It will take time, effort to work with it.
Therefore, MySql has created a solution to support some of this problem. It is called View.
With it, you can have a simpler but more efficient view, storing calculations and hiding the complexity below. Like modern framwork, create many useful, developer-friendly functions.
View allows us to aggregate results from multiple tables, depending on desired criteria. Therefore, from a lengthy, cluttered query, we can separate meaningful, reusable segments, and then make each one into a view.
The nature of the view is just a piece of sql code that is saved to the database server. If it is not about the designer’s usage, or its practical meaning in each case, it is just like a store procedure, or save sql code to a file somewhere and read it out for execution.
It is considered a virtual table with the same capabilities as a normal table, but it does not store data physically. When extracting internal data, it will obtain aggregated data always synchronized with the component tables.
The syntax for creation is as follows:
CREATE [OR REPLACE] VIEW [db_name.]view_name [(column_list)]
- OR REPLACE – add to overwrite the old view with the same name if available.
- db_name. – database name
- (column_list) – the default view’s columns will always be taken with the result set of the select-statement, used when you want to rename them.
Note that the view name is also recognized as the table name so it cannot be created the same.
Because it is used like a normal table, retrieving data is also very normal as
SELECT * FROM view_name;
ALTER VIEW view_name [(column_list)]
RENAME TABLE view_name
DROP VIEW [IF EXISTS] view_name;
From what was presented above, the benefits can be summed up as follows:
Simplify complex queries
The number of tables to be used in a query will be reduced, the query will be easier to understand (of course not for mysql).
Modules are query criteria groups.
Meaningful queries are gathered into views and reused, especially convenient for those who do not understand database architecture.
Increase the security layer
There will be important data that needs to be thoroughly visible only to certain audiences.
View will block this root, tables with confidential data will not be directly queried, only the specified data will flow through the view.
Use instead of splitting tables (backward compatible technique)
Create views whose result set is a child of the table you want to split, so the aggregate data remains and the data that needs to be used for each case has also been split.
Hope the article will be useful to everyone!
Reference link: https://www.mysqltutorial.org/mysql-views-tutorial.aspx