MySQL View

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.

Syntax:-

CREATE VIEW view_name AS

SELECT col1, col2,..

FROM table_name

WHERE conditions.

Example:-

1. 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;

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

SELECT colname

FROM tablename

WHERE condition

Example:-

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

FROM tableName

WHERE condition;

Example:-

CREATE OR REPLACE VIEW tenthstudent AS ID,  FirstName, LastName FROM students WHERE std = 10;

Drop OR Delete View:-

Syntax:-

DROP VIEW [IF EXIST] viewname;

Example:-

DROP VIEW tenthstudent;

Rename View:-

Syntax:-

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

Like:-

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

1. view simplify complex queries and we can reuse it.
2. View enables the backward compatibility
3. It improves data security.

Disadvantage:-

1. The simple view can be updated but the complex view which joins the table or uses subquery cannot be updated.
2. MySQL does not support Materialized view.
3. The view cannot create an index.
4. We cannot create a temporary view
5. the view can not be associated with the trigger.

Read Also:–

MySQL Store Function
MySQL Function
MySQL Procedure
MySQL Triggers

For more detail about MySQL Create function please read MySQL manual