In this article, we will learn how to create, read, update and delete a View using SQL query.
Based on the result-set of a SQL statement a View is a virtual table.
Just like a real table, also a View contains rows and columns. The fields in View are fields from single or multiple real tables in the database.
We can add WHERE, JOIN statements and SQL functions to a View.
View – Create:
The CREATE VIEW statement is used to create a new view in a database.
Syntax
CREATE VIEW VIEW_NAME AS SELECT column1, column2, ...columnN FROM TABLE_NAME WHERE condition;
Example
The subsequent statement would create a view named “Popular Articles” that contains all Articles with more than 100 views:
CREATE VIEW [Popular Articles] AS SELECT * FROM Article WHERE Views > 100;
View – Read:
To see the data in the View, we can query the View in the same manner as we query a table using the SELECT statement.
Please check How To Write Select Query In SQL, to get a brief description of the SELECT statement.
Syntax
SELECT * FROM VIEW_NAME;
Example
The subsequent statement would display data of all available fields in the “Popular Articles” view.
SELECT * FROM [Popular Articles];
View – Update:
The SQL UPDATE statement is used to update a View, just like we update tables.
Please check How To Write Update Query In SQL, to get a brief description of the UPDATE statement.
Syntax
UPDATE VIEW_NAME SET column1 = value1, column2 = value2, ...columnN = ...valueN;
Example
The subsequent statement would update all records in the “Popular Articles” view with a single field named “Title)”:
UPDATE [Popular Articles] SET Title = 'Introduction';
View – Delete:
The DROP VIEW statement is used to delete a View.
Syntax
DROP VIEW VIEW_NAME;
Example
The subsequent statement would delete the existing view named “Popular Articles”:
DROP VIEW [Popular Articles];
Also, check How To Create Auto Increment Field Using Query In SQL
