A view is a virtual table whose definition is stored in the database. But, unlike tables, views do not actually contain any data. Instead, it provides a way to store commonly used complex queries in the database. However, you can use the view in a SQL SELECT statement to access the data just as you would use a normal or base table.
Views can also be used as a security mechanism by allowing users to access data through the view, rather than giving them direct access to the entire base tables.
Views are created using the CREATE VIEW
statement.
view_name
AS select_statement
;Theoretically, you can also perform INSERT
, UPDATE
, and DELETE
on views in addition to the SELECT
statement. However, not all views are updatable i.e. capable of modifying the data of an underlying source table. There are some restrictions on the updatability.
Generally a view is not updatable if it contains any of the following:
DISTINCT
, GROUP BY
or HAVING
clauses.AVG()
, COUNT()
, SUM()
, MIN()
, MAX()
, and so forth.UNION
, UNION ALL
, CROSSJOIN
, EXCEPT
or INTERSECT
operators.WHERE
clause that refers to a table in the FROM
clause.If a view satisfies these conditions, you can modify the source table using that view.
The following statement will update the salary of the employee whose emp_id is equal to 1.
UPDATE emp_dept_view SET salary = '6000'
WHERE emp_id = 1;
Similarly, if you no longer need a view, you can use the DROP VIEW
statement to drop it from the database, as shown in the following syntax:
The following command will drop the view emp_dept_view from the database.
DROP VIEW emp_dept_view;