Views in MySQL

Views are virtual tables that do not store their own data but display all or a few rows of data stored in other tables. View helps to simplify the complex business logic written in the SQL queries to simple one.

Syntax

CREATE VIEW `view_name` AS SELECT statement;

Example

create view enginnringStudent as select name, email from studentregister where grade="b.tech"

Execute

SELECT * FROM enginnringStudent;

MySQL Updatable View

The MySQL views are Updatable means we can perform INSERT, UPDATE, and DELETE queries on the database view and the base tables are also updated

Please note that updatable view must not have the following:

1. Any aggregate function, e.g., MIN, MAX, AVG, SUM, and COUNT
2. Subqueries in SELECT and WHERE clause
3. UNION or UNION ALL
4. Outer Join or Left Joins
5. HAVING and GROUP BY Clause

Insert value in View

INSERT INTO `enginnringstudent`(`name`, `email`) VALUES (1,1)

Update value in View

UPDATE enginnringstudent SET name =”abhishek” where email=abhishek@gmail.com

Delete value in view

DELETE FROM `enginnringstudent` WHERE name=”abhishek”

Alter View Syntax

ALTER VIEW view_name AS SELECT columns FROM table WHERE conditions;

Drop View Syntax

DROP VIEW view_name;

MySQL Create View with JOIN Clause Example

  CREATE VIEW joinStudentTeacher      AS SELECT s.name, s.email, t.designation     

  FROM studentregister s, teacherregister t  WHERE s.s_id= t.t_id;