View In MySQL
By definition, a database view or view is a virtual or logical table which is composed of result set of a SELECT query. Because the database view is similar to the database table which consists of row and column so you can retrieve and update data on it in the same way with table.
Database views provide several advantages as follows:
- Simplify complex query.
- Limited access data to the specific users.
- Provide extra security.
- Computed column.
- Backward compatibility.
To read more about view in MySQL, please go to: www.mysqltutorial.org
The SELECT statement cannot contain a subquery in the FROM clause
As for simplify complex query, you can join several tables to make a view table then you can retrieve data from it with simpler query.
CREATE VIEW SalePerOrder AS SELECT orderNumber, SUM (quantityOrdered * priceEach) total FROM orderDetails GROUP by orderNumber ORDER BY total DESC
Then you can retrieve data from the view by more simpler query:
SELECT total FROM salePerOrder WHERE orderNumber = 10102
But, the select statement in creating view can not be contain subquery in the FROM clause. If you want to make a view but to retrieve data you should have subquery in it, then the solution is:
- Make view for every subquery then access those views from within your final view.
The key is that you can also create view from views.