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.

Views Advantages

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.

examples:

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.