MySql Views

In MYSQL, views are act as virtual table generated by the result of a SELECT query. A view contains rows and columns, just like a real table.A view can be created by joining one or more base tables.So, the fields in a view are fields from one or more real tables in the database.

There are some points to define a View as follows:

• It is used to restrict access to the database.
• It adds an extra security layer.
• Hide data complexity.
• A view is stored as a select statement in the database.
• DML operations on a view like Insert, Update, Delete effects.

Syntax to Create View

CREATE VIEW view_name AS SELECT column1, column2, ...FROM table_name WHERE condition;

Query to create view:

create view myview as select ename,sal,job,deptno from emp where sal>2000;

Now check the created View:

select * from myview;

Output:

Query to Update view:

update myview set sal=10000 where ename='SCOTT';

Now check the created View:

select * from myview;

Output:

Query to Insert Values in a View:

insert into myview values('Neha',5000,'Accountant',10);

Now check the created View:

select * from myview;

Output:

Query to Detete Recoed from a View:

delete from myview where ename='Neha';

Now check the created View:

select * from myview;

Output:

Query to Drop a View:

drop view myview;

Output:

ERROR 1146 (42S02): Table 'mydb.myview' doesn't exist