How to Create a View in MySQL

In this tutorial, we are going to see how to create a view in MySQL. In MySQL, a view is not a physical table, but rather a virtual table created by a query joining one or more tables.
 

Syntax:

The syntax of the statement CREATE VIEW in MySQL is as follows:

CREATE [OR REPLACE] VIEW viewName AS
   SELECT column1, column2, ...
   FROM tables
   [WHERE conditions];

 

 

Example: How to Create a View in MySQL

Here is an example of using CREATE VIEW statement to create a view in MySQL:

CREATE VIEW stock_vw AS
  SELECT product, qt
  FROM stock
  WHERE category = 'Cosmetic';

This query will create a virtual table based on the results of the SELECT statement. This view will contains only the products that have category = ‘Cosmetic’ You can now query the MySQL view as follows:

SELECT * FROM stock_vw;

Output:

+---------------+-----------+
|    Product    |     qt    |
+---------------+-----------+
|   Relaxers    |     35    |
|   Eyeliner    |     10    |
|   Brow Pencil |     12    |
|   Mascara     |     114   |
+---------------+-----------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More