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.
- 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.
- 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
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