Views in MySQL

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.


CREATE VIEW `view_name` AS SELECT statement;


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


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
  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

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,, t.designation     

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

Post Your Comments & Reviews

Your email address will not be published. Required fields are marked *