MySQL Views

A database view is a virtual table which is defined as a SQL select query with JOIN statement. Same as database table it consists of rows and columns.

When any data of a tables changes, the corresponding view reflects that changes as well.

Advantages:

• A database view allows you to simplify complex queries. You do not need to join a table in run time to fetch data.
• Views provides extra security for a database management system like read-only to particular users.
• You can limit access of data to particular users also.
• If cache is enabled then view is stored in cache and increases the performance of query.

Disadvantages:

• Querying data is slow in view.
• Whenever any changes is made on a table like structure then you have change the view as well.

Simple view example:

CREATE VIEW userOrder
AS
SELECT userID, orderID
SUM  (qty * price) total
FROM orders
GROUP by orderID
ORDER BY total DESC

You can also use JOIN and sub-query to the view.

Once a view created in a database, you can remove it by using the DROP VIEW statement:

DROP VIEW [IF EXISTS] userOrder

After a view is defined, you can modify it by using the ALTER VIEW statement:

ALTER VIEW userOrder
AS
SELECT userID, orderID, discount_amount
SUM (qty * price) total
FROM orders
GROUP by orderID
ORDER BY total DESC

Updateable Views:

To create a updateable views you need to consider following things:

• SELECT statement must only refer to single database table i.e. do not use JOIN query.
• Avoid DISTINCT, GROUP BY or HAVING clause i.e. must not contain aggregates functions.
• Must be write permission.

UPDATE userOrder
SET total = 2250
WHERE userID = 5 AND orderID = 135