How do I create a view in MySQL?

To create a view in MySQL, you can use CREATE VIEW command. The syntax is as follows −

CREATE VIEW yourViewName as SELECT yourColumName1, yourColumName2, yourColumName3,...... yourColumNameN from yourTableName;

To understand the above syntax, let us create a table first. The following is the query to create a table −

mysql> create table ViewDemo
   −> (
   −> Id int,
   −> Name varchar(200),
   −> Age int
   −> );
Query OK, 0 rows affected (0.58 sec)

Implement the above syntax to create a view. The following is the query to create a view in MySQL −

mysql> create view LookUpTable as select Id,Name from ViewDemo;
Query OK, 0 rows affected (0.15 sec)

Now check the view is created or not using show command. The query is as follows −

mysql> show create table LookUpTable;

The following is the output −

+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View        | Create View                                                                                                                                                         | character_set_client | collation_connection |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| lookuptable | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `lookuptable` AS select `viewdemo`.`Id` AS `Id`,`viewdemo`.`Name` AS `Name` from `viewdemo` | utf8 | utf8_general_ci |
+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
1 row in set (0.00 sec)

Rishi Rathor

Published on 16-Jan-2019 14:06:55