Views
Views in MySQL are essentially virtual tables that do not store data themselves
but instead display the results of a stored query. They provide a way to present
data in a structured format without physically storing it. Views are useful for
simplifying complex queries, providing a security layer by restricting access to
certain columns or rows of a table, or creating a summarized or aggregated view
of data.
Conclusion
A view is a pre-defined query on one or more tables.
Retrieving information from a view is done in the same manner as
retrieving from a table.
With some views you can also perform DML operations (delete, insert, and
update) on the base tables (updatable views)
Views do not store data, they only access rows in the base tables-avoid
data redundancy
View can hide the underlying base tables and columns-data security.
By writing complex queries as a view, we can hide complexity from an end
user.
View only allows a user to access certain rows in the base tables. We can
create a view with the "WHERE" clause also.
Creating Views
create view customer_view as
select firstname, lastname from customers;
Changing the name of the views
create view customer_view as
select firstname v_firstname, lastname l_lastname from customers;
Updating a View
create or replace view customer_view as
select CustomerID, FirstName, LastName from customers;
Views From Multiple Table
create view both_table_view as
Select
Orders.OrderID,
Orders.OrderDate,
Orders.TotalAmount,
Customers.FirstName,
Customers.LastName,
Customers.Email
from
customers, orders
where
Orders.CustomerID = Customers.CustomerID;
CLASSIFICATION OF VIEWS
Views can be classified as updateable views and non-updateable
views.
Updateable Views
By updateable we mean to say that one can insert, update, and
delete records from view. Actually all the DML operations are
performed on the base table.
View with the following characteristics is called an updateable view.
It is created from a single table.
It includes all the PRIMARY KEYS and NOT NULL columns of
base table.
Aggregate functions like SUM,AVG,MAX have not been used.
It should not have DISTINCT, GROUP BY, HAVING clauses.
It must not use constants, strings, or value expressions like
salary * 2.
It must not contain function calls (e.g. RPAD, SUBSTR, etc.).
If a view is defined from another view, then that view must
also be updateable.
Non-updateable Views
Non-updateable means we cannot insert, update, delete records
from that view.
View with the following characteristics is called a non-updateable
view.
It is created from more than one table.
It has DISTINCT, GROUP BY, HAVING clause. Even if view is
derived from single table but contains any of these clauses
then it is not updateable.
It does not include all the PRIMARY KEYS and NOT NULL
columns of base tables.
TASK
Try to insert values into join_view
Try all dml commands
MATERIALIZED VIEWS
create materialized view mvw as select * from orders;
Improved Query Performance
Materialized views store precomputed results, leading to faster
query response times.
Reduced Resource Consumption
Offloads expensive query processing to precomputed results,
reducing the load on the database server.
