MySQL CREATE VIEW

has average rating 7 out of 10. Total 11 users rated.

<<PreviousNext>>

Description

In MySQL, CREATE VIEW command creates a new view.

What is a view

A view is a virtual table. View is a data object which does not contain any data. Contents of the view are the resultant of a base table. They are operated just like base table but they don’t contain any data of their own. The difference between a view and a table is that views are definitions built on top of other tables (or views). If data is changed in the underlying table, the same change is reflected in the view. A view can be built on top of a single table or multiple

Syntax

CREATE VIEW [view name] AS [select statement];

Arguments

Example

Sample table : category

Code

CREATE VIEW View_Category 	AS SELECT * FROM category;

Explanation

The above MySQL statement will create a view 'View_Category' alike table 'category'.

MySQL create view with specific column

Description

CREATE VIEW command can be used to create a view with one specific column from a table.

Example

Sample table : category

Code

CREATE VIEW View_Category AS SELECT cate_descrip FROM category;

Explanation

The above statement will create a view 'View_Category' as cate_descrip column of category table.

MySQL CREATE VIEW with WHERE

Description

CREATE VIEW command can be used with WHERE clause.

Example

Sample table : author

Code

CREATE VIEW view_author 
AS SELECT * 
FROM author 
WHERE country='USA'

Explanation

The above MySQL statement will create a view 'view_author' taking records (for all columns) of author table if those records contain the value USA for country column.

MySQL CREATE VIEW with AND and OR

Description

CREATE VIEW command can be used with AND and OR operators.

Example

Sample table : publisher

Code

CREATE VIEW view_publisher 
AS SELECT pub_name,pub_city,country
FROM publisher	
WHERE (country='USA' AND pub_city='New York')
OR 	(country='India' AND pub_city='Mumbai');

Explanation

The above MySQL statement will create a view 'view_publisher' taking records for pub_name, pub_city and country columns of publisher table, if (A)(i)value of the country column is USA, and (ii)value of the pub_city is New York; or (B)(i)value of the country column is INDIA, and (ii)value of the pub_city is Mumbai.

MySQL CREATE VIEW with GROUP BY

Description

CREATE VIEW command can be used with GROUP BY clause.

Example

Sample table : book_mast

Code

CREATE VIEW view_bookmast
AS SELECT pub_lang, count(*)
FROM book_mast 
GROUP BY pub_lang

Explanation

The above statement will create a view 'view_bookmast' taking all records grouped w.r.t. pub_lang, from pub_lang and number of books for each language (pub_lang).

MySQL CREATE VIEW with ORDER BY

Description

CREATE VIEW command can be used with ORDER BY clause.

Example

Sample table : book_mast

Code

CREATE VIEW view_bookmast
AS SELECT pub_lang,count(*) 
FROM book_mast 
GROUP BY pub_lang 	ORDER BY pub_lang;

Explanation

The above MySQL statement will create a view 'view_bookmast' taking all the records grouped w.r.t. pub_lang and sorted against pub_lang, from pub_lang and number of books for each language (pub_lang) of book_mast table.

MySQL CREATE VIEW with BETWEEN and IN

Description

CREATE VIEW command can be used with BETWEEN and IN operator.

Example

Sample table : book_mast

Code

CREATE VIEW view_bookmast
AS SELECT *
FROM book_mast
WHERE book_name BETWEEN 'A' AND 'G' 
AND no_page IN(165,250,350,400,510);

Explanation

The above statement will create a view 'view_bookmast' taking all the records of book_mast table, if (A)name of the book (book_name) starts with any of the characters from 'A' through 'G' and (B) number of pages (no_page) are any of the following 165, 250, 350, 400, 510.

MySQL CREATE VIEW with LIKE

Description

CREATE VIEW command can be used with LIKE operator.

Example

Sample table : author

Code

CREATE VIEW view_author 
AS SELECT *
FROM author
WHERE aut_name  
NOT LIKE 'T%' AND aut_name NOT LIKE 'W%';

Explanation

The above MySQL statement will create a view 'view_author' taking all the records of author table, if (A)name of the author (aut_name) does not start with 'T' and (B) name of the author (aut_name) does not start with 'W'.

MySQL CREATE VIEW using subqueries

Description

CREATE VIEW command can be used with subqueries.

Example

Sample table : purchase

Sample table : book_mast

Code

CREATE VIEW view_purchase 
AS SELECT invoice_no,book_name,cate_id 
FROM purchase	
WHERE cate_id= 	(SELECT cate_id FROM book_mast WHERE no_page=201);

Explanation

The above MySQL statement will create a view 'view_purchase' taking all the records of invoice_no, book_name and cate_id columns of purchase table, if category id (cate_id) satisfies the condition defined within a subquery (followed by cate_id=).

The subquery retrieves only cate_ids from book_mast table, which contain books with 201 pages.

MySQL CREATE VIEW with JOIN

Description

CREATE VIEW command can be used along with a JOIN statement.

Example

Sample table : category

Sample table : purchase

Code

CREATE VIEW view_purchase 	
AS SELECT a.cate_id,a.cate_descrip, b.invoice_no,
b.invoice_dt,b.book_name        
FROM category a,purchase b 
WHERE a.cate_id=b.cate_id;

Explanation

The above MySQL statement will create a view 'view_purchase' along with a JOIN statement.

The JOIN statement here retrieves cate_id, cate_descrip from category table and invoice_no, invoice_dt and book_name from purchase table if cate_id of category table and that of purchase are same.

MySQL CREATE VIEW with UNION

Description

CREATE VIEW command can be used with UNION.

Example

Sample table : book_mast

Code

CREATE VIEW view_bookmast AS
SELECT * 
FROM book_mast
WHERE pub_id='P001' UNION
SELECT * 
FROM book_mast	
WHERE book_name BETWEEN 'A' AND 'G' UNION
SELECT *
FROM book_mast 
WHERE no_page IN(165,250,350,400,510);

Explanation

The above MySQL statement will create a view 'view_bookmast' contains columns as in the 'book_mast'.

The records will be inserted with the union of three subqueries.

The first query inserts those rows in to the 'view_bookmast' view from the 'book_mast' table whose 'pub_id' is 'P001'.

The second query inserts those rows in to the 'view_bookmast' view from the 'book_mast' table whose rows have the 'book_name' column beginning with any letter between 'A' to 'G'.

The third query inserts those rows in to the 'view_bookmast' view from the 'book_mast' table whose rows have any of the following values 165,250,350,400,510 in 'no_page'.