Description
SQL View is a database substance that can be made up of a table to improve the performance of SQL Database. A view has unique look on data from one or more tables. It can organize data in unique order, focus or hide some data. A SQL View comprises of a stored query accessible as a fundamental table composed of the outcome set. Beside standard tables a perspective does not shaped a part of the physical schema. It is a virtual table, dynamic in the database.
View is a stored query, and it can be attributed like a table. View object mainly classified into:
- Create view
- Alter view
- Drop view
Conceptual
figure
Description
Once a view is created from base table, all DML operations can be performed on that view which effects its base table. This kind of view is called simple view.
Create any number of columns in a table. The system privileges are required to create own schema, and can execute any object privilege on object type.
Syntax
The syntax for SQL Create View statement is as follows:
create view <table_name>as select <column_name> FROM <table_name> where <condition>;
Table_name => Any accurate table.
column_name=>The columns that inserted in the table.
Condition => The condition to get the specific result set .
Examples
By viewing the below example, the concepts of simple view can be understood easily.
[c]sql> select * from cars;
+--------+----------+--------+
| car_id | car_name | cost |
+--------+----------+--------+
| 1 | audi | 52642 |
| 2 | skoda | 526400 |
| 3 | volva | 52640 |
| 4 | volva | 52000 |
| 5 | hummer | 41400 |
+--------+----------+--------+
5 rows in set (0.00 sec)
sql> create view cheapcars as select name from cars where cost CREATE VIEW Cheapcars AS SELECT car_name from cars where cost select * from cheapcars;
+----------+
| car_name |
+----------+
| volva |
| hummer |
+----------+
2 rows in set (0.16 sec)
sql> create view vehicles as select name from cars;
+--------+----------+--------+
| car_id | car_name | cost |
+--------+----------+--------+
| 1 | audi | 52642 |
| 2 | skoda | 526400 |
| 3 | volva | 52640 |
| 4 | volva | 52000 |
| 5 | hummer | 41400 |
+--------+----------+--------+
5 rows in set (0.00 sec)[/c]
In the above example, a simple table car has been created and by applying view operation to that table, like inserting some condition on column name.(i.e., cost of car<52640 is the condition, and it show the cost of cars less than that value).
Description
Once a view is created from base table, all alter operations an be performed on that view which effects its base table.
The alter view proclamation is like the create view proclamation aside from create is replaced with alter keyword.
Syntax
The syntax for SQL Alter View statement is as follows:
Alter view <table_name> as select <column_name> from <table_name> where condition;
Table_name => Any accurate table.
column_name=>The columns that inserted in the table.
Condition => The condition to get the specific result set.
Examples
By viewing the below example, the concepts of Alter view can be understood easily.
[c]sql> select * from cars;
+--------+----------+--------+
| car_id | car_name | cost |
+--------+----------+--------+
| 1 | audi | 52642 |
| 2 | skoda | 526400 |
| 3 | volva | 52640 |
| 4 | volva | 52000 |
| 5 | hummer | 41400 |
+--------+----------+--------+
5 rows in set (0.00 sec)
sql> alter view cheapcars as select car_name from cars where cost select * from cheapcars;
+----------+
| car_name |
+----------+
| volva |
| hummer |
+----------+
2 rows in set (0.00 sec)
sql> alter view cheapcars as rangeovers;
sql> select * from rangeovers;
+--------+----------+--------+
| car_id | car_name | cost |
+--------+----------+--------+
| 1 | audi | 52642 |
| 2 | skoda | 526400 |
| 3 | volva | 52640 |
| 4 | volva | 52000 |
| 5 | hummer | 41400 |
+--------+----------+--------+
5 rows in set (0.00 sec)
[/c]
In the above example, a simple table car has been created and then alter the table by applying view operations to that table, like adding some condition on column values using where clause. (The column name cost have been alter by using where clause, i.e, cost<52640 will be show in the result set.
Description
Drop table will drop the table. In the same way drop view will drop the permission of view table, and there is no such table exist in the data bases.
Syntax
Drop table;
Table_name=>The accurate table stored in the database.
Examples
By viewing the below example, the concepts of Alter view can be understood easily.
[c]
sql> drop table cars;
Query OK, 0 rows affected (0.34 sec)
sql> select * from cheapcars;
ERROR 1356 (HY000): View 'employee.cheapcars' references invalid table(s) or col umn(s) or function(s) or definer/invoker of view lack rights to use them[/c]
In the above example, the table name cars have been dropped from the database, and cannot perform any operations on cars related to cheapcars.
Key Points
- SQL View -View is a database object.
- Create view - DML operations can be performed in create view.
- Alter view - DML operation can be performed in alter view.
- Drop view - drop view will drop a table.