Views are virtual tables which groups necessary data from two or more real tables and represent them into a single virtual table. Views can also be used to present the limited data from a single table or when we have to manipulate some data before presenting.
A view is a virtual table based on the result-set of the Sql query. These are searchable objects in the database. It does not contain its own data but only the data from already existing tables.
- View is used when we have to give access to limited amount of data while denying the access to the original table.
- So views are created mainly for security purposes but they appear like a normal table to the user.
- Then you might ask why views instead of tables? Views combine multiple table subsets and contains only the selected data to be viewed by users. Hence it is better(performance and resource utilization) to transform the data once and return it each time it is accessed instead of transforming it each time programmatically.
- Hence it acts as an abstraction of the data in the table which is not known by the end user.
- Main purpose of views is it restricts access to certain columns and rows in the table and hides the real table name thereby providing security.
- Views are used along with JOINS in Sql to merge tables with specific information.
Views do not exist physically and stored in the database as objects. They are temporarily created for security purposes by hiding information from original table and encapsulating the table name.
Types of views
There are two types:
- Simple view: These views are created only from single table
- Complex view: These are created from multiple tables and groups more data. JOINS are applied in complex views
Views displays only those data present in the query at the time of creation. It can be created using same or different databases.
CREATE VIEW view_name AS
- view_name: Name of the view you want to create
- column1,column2: The column names you want to combine from different tables
- tables: Name of one or more tables you want to extract data from
- WHERE condition: it is an optional one. The conditions for the records to satisfy before adding in views
1) Simple view
Consider the “student” table,
|safa|| 16|| 10|| 9|
| kia|| 14||8|| 7|
| chris||15|| 9|| 8|
|ada||12|| 6|| 9|
CREATE VIEW my_view AS
SELECT Name, Grade
SELECT * FROM my_view
| chris|| 9|
Only the selected rows are present in “my_view” and it hides all the other confidential information.
2) Complex view
Suppose, there is another table “Student_Id”
We need only the information such as a student’s ID, name and grade for the users to view. This can be done using views as:
CREATE VIEW Student_view AS
SELECT Student_Id.ID, student.Name, student.Grade
SELECT * FROM Student_view
Updating existing views refers to modifying the definition of the views without actually dropping it. For this, we use CREATE OR REPLACE VIEW statement.
CREATE OR REPLACE VIEW view_name AS
SELECT v1.column_name1, v2.column_name2, v2.column_name3 etc
FROM table_name1 AS v1, table_name2 AS v2...
We will add another column in the view “Student_view” as follows:
CREATE OR REPLACE VIEW Student_view AS
SELECT Student_Id.ID, student.Name, student.Grade, student.Points
WHERE student.Age > 13
SELECT * FROM Student_view
In the above table, the view is updated with additional column and reduced in row based on the condition in the update query.
Dropping views means deleting the entire view which is similar to dropping the table.
DROP VIEW view_name
DROP VIEW Student_view
“Student_view” View will be deleted completely including the view structure.
Learning never exhausts the mind! keep learning!!
Do share and subscribe and comment below for any suggestion, query or opinion.
Keep Coding! Happy Coding! 🙂