Learn MySQL

MySQL Views

MySQL View is virtual table that allow us to query the data in it. Contents of the view are the result-set of a base table.If data is changed in the base tables, the same change is reflected in the view.MySQL Views need Version 5.0 or higher.

CREATE VIEW Syntax

CREATE [OR REPLACE] [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW [database_name].[view_name] AS [SELECT statement]

Specification

Used ALGORITHM:

MERGE: means the input query will be combined with the SELECT statement of the view definition. This mechanism is more efficient than TEMPTABLE.

TEMPTABLE:means that MySQL first creates a temporary table based on the SELECT statement of the view definition, and then it executes the input query against this temporary table.

UNDEFINED:is the default algorithm when you create a view without specifying an explicit algorithm.

View Name:

view_name is the name of the view

SELECT statement:

Select statement can select data from base tables or other views.

MySQL CREATE VIEW with WHERE

CREATE VIEW course_detail AS SELECT * FROM course WHERE course_name='MYSQL';

MySQL CREATE VIEW with AND and OR

CREATE VIEW course_detail AS SELECT course_name FROM course WHERE (course_name='MYSQL' AND course_name='CODEIGNITER') OR (id='2');

MySQL CREATE VIEW with GROUP BY

CREATE VIEW student_detail AS SELECT course_name, count(*) FROM student GROUP BY course_name;

MySQL CREATE VIEW with ORDER BY

CREATE VIEW student_detail AS SELECT course_name, count(*) FROM student GROUP BY course_name ORDER BY pub_lang;

MySQL CREATE VIEW with LIKE

CREATE VIEW student_detail AS SELECT student_name FROM student WHERE student_name NOT LIKE 'T%' ;

MySQL CREATE VIEW with Join

CREATE VIEW course_student AS SELECT student.student_name, course.course_name FROM `student` INNER JOIN `course` on student.course_id = course.id;

ALTER VIEW

ALTER VIEW course_student AS SELECT student.student_name, course.course_name FROM `student` LEFT JOIN `course` on student.course_id = course.id;

DROP VIEW Syntax

DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE]

Find All VIEW In Database

There several ways to find views in MySQL database.

Method1:

SHOW FULL TABLES

SHOW FULL TABLES statement list all tables in database.In the Table_type column we can see, whether it is a view or a table.

Method2:

SELECT TABLE_NAME FROM information_schema.VIEWS

This is the most efficient way to find views.Above query list all VIEW in database.