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 09:36:55

• Related Questions & Answers
• How do I see what a MySQL view is made of?
• How do I view the auto_increment value for a table in MySQL?
• How to create a MySQL view?
• How do I create and use a sequence in MySQL?
• How do I create a random four-digit number in MySQL?
• How can I view cascades in MySQL?
• How do I create a Python namespace?
• How to create a table from view in MySQL?
• How can we create a MySQL view based on another existing view?
• How can we modify a MySQL view with CREATE OR REPLACE VIEW statement?
• How can we create a MySQL view with a subquery?
• How do I remove a MySQL database?
• How to create an empty VIEW in MySQL?
• How do I create a namespace package in Python?
• How do I create a java.sql.Date object in Java?