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:
- Any aggregate function, e.g., MIN, MAX, AVG, SUM, and COUNT
- Subqueries in SELECT and WHERE clause
- UNION or UNION ALL
- Outer Join or Left Joins
- 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;