MySQL Views

Namaste UI (Author)
MySQL SELECT
Share on Pinterest Share on LinkedIn Share on WhatsApp Share on Telegram

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

Leave a Reply

Your email address will not be published. Required fields are marked *