In this tutorial
we will learn what is views in mysql,
what is the advantage of view, how
to create view in mysql, how to update a view in mysql ,create view with check option and
finally how to drop a view from mysql
.
What is views in mysql
Views is an important task for a mysql
database administrator. View is a
logical table .We can perform insert ,update and delete operation using view.
But when we execute one query on view ,the request goes to the physical table
and requested operation will be perform there. Then the acknowledgement sent back to view to see the
data of the table. Therefore performance will be degraded by using view in
mysql.
What is the advantage of views in MySql
There are
some advantages of view. These are
following.
1)
A
complex query will become simple query using the view: When create a complex operation like join we
can use view statement to do the query simple.
2)
View
provides extra layer of security of database: Suppose we have a table of
department which contains many department name .We want to restrict one department
data from other department we can do this using View. For this reason it
provides extra layer of security.
3)
We
can restrict data for user: Suppose we have a table of department which
contains many department name .We want to restrict one department data from
other department we can do this using View
How to create view in MySql
Suppose we have
one table named department and we want to restrict some data. We can create a view to do this task and the
syntax is given below.
create view <view_name>
as
[select statement] [condition]
From the above syntax
1)
create view is
the keyword to create the view.
2)
View_name is name
of the view which to be created
3)
We need to use “as”
keyword
4)
Then we need to
enter our statement for which view will be created.
Now we want mechanical department can view only mechanical department data. We can
execute the below query to do this task.
CREATE
view view_mech as select * from dept where deptname=’MECH’;
Now we have created one view named
view_mech. To see the view we can execute the below query. We can see only
record of mechanical dept after execute the query.
Select
* from view_mech;
How to update a view in MySql
If we need
to update our view statement which
is created the we need to use the “alter view” keyword. Syntax will be
alter view <view_name>
as
[update statement] [condition]
From the above syntax
1)
alter view is the
keyword to update the view.
2)
View_name is name
of the view which to be updated.
3)
We need to use “as”
keyword
4)
Then we need to
enter our statement for which view will be updated.
Create view in MySql with check option
We have created one view in the create view section to view,insert,update the record
only mechanical department. If we insert one data for other department the data
will be inserted.If we use “with check
option” keyword then we will get an error. The syntax will be.
create view <view_name>
as
[select statement] [condition] with check option
We must enter “with check option”
keyword to prevent the database. You can see demonstration at the video section .
Drop a view from MySql
We have
created view in MySql . Now it is the time to drop the view from the MySql. Below is the syntax to drop the view.
Drop view <view_name>
From the above syntax
1)
drop view is the
keyword to drop the view.
2)
View_name is name
of the view which to be dropped.
Here we are going to drop view_mech
using the below query.
drop
view view_mech;
In this tutorial
we have learned what is views in mysql, what is the advantage of view, how to create
view in mysql, how to update a view
in mysql ,create view with check
option and finally how to drop a
view from mysql .
