MySQL View act as a virtual table. It is also a stored query. When it invokes then it produces a result.
Just like a real table view containing rows and columns. All column names and data types of view are based on real tables of the database.
If the data will come from a single table then we can add functions and SQL statements to a view.
A view will always show recent data.
CREATE VIEW statement is used for creating a view.
CREATE VIEW view_name AS
SELECT col1, col2,..
- A view will create which will give all student names from class 10th from the students’ table
CREATE VIEW tenthstudent AS FirstName, LastName FROM students WHERE std = 10;
For seeing tenthstudent view:-
SELECT * FROM tenthstudent;
- If we want to create a view by joining two tables, like students and teachers table will fetch the class teacher’s name and student tables will fetch tenth stander student’s name
CREATE VIEW tenthstudent AS s.FirstName, s.LastName, t.FirstName, t.Lastname FROM s.students, t.teachers WHERE s.ID = t.ID AND s.std = 10;
UPDATE View:- For update view, we use ALTER VIEW syntax.
ALTER VIEW viewname AS
ALTER VIEW tenthstudent AS ID, FirstName, LastName FROM students WHERE std = 10;
A view can be updated using CREATE OR REPLACE VIEW Syntax:-
CREATE OR REPLACE VIEW view_name AS
SELECT col1, col2, …
CREATE OR REPLACE VIEW tenthstudent AS ID, FirstName, LastName FROM students WHERE std = 10;
Drop OR Delete View:-
DROP VIEW [IF EXIST] viewname;
DROP VIEW tenthstudent;
RENAME TABLE original_view_name TO new_view_name;
Note:- Using the SHOW FULL TABLES command we can see all my views in the command line
SHOW FULL TABLES WHERE table_type = ‘VIEW”;
Note:- Using the CHECK TABLE, we can check whether the view is valid or not.
Advantage Of View:- There are the following advantages of view:-
- view simplify complex queries and we can reuse it.
- View enables the backward compatibility
- It improves data security.
- The simple view can be updated but the complex view which joins the table or uses subquery cannot be updated.
- MySQL does not support Materialized view.
- The view cannot create an index.
- We cannot create a temporary view
- the view can not be associated with the trigger.
For more detail about MySQL Create function please read MySQL manual