Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to easily 'create table from view' syntax in MySQL?
You can create a table from view using create table select syntax. The syntax is as follows −
CREATE TABLE yourTableName AS SELECT yourColumnName1,yourColumnName2,yourColumnName3,........N from yourViewName;
To run the above query, first you need to create a table and after that you need to create a view on that table. After that run the query.
First, you need to create a table. The query to create a table is as follow −
mysql> create table StuedntInformation -> ( -> Id int, -> Name varchar(100) -> ); Query OK, 0 rows affected (0.54 sec)
Above, we have created a table. After that you need to create a view. The query to create a view is as follows −
mysql> CREATE VIEW view_Student AS SELECT Id,Name from StuedntInformation; Query OK, 0 rows affected (0.11 sec)
Now I have created a view with the name ‘view_Student’. Check the view using show command.
The query is as follows −
mysql> SHOW CREATE VIEW view_Student;
Output
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | View | Create View | character_set_client | collation_connection | +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ | view_student | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `view_student` AS select `stuedntinformation`.`Id` AS `Id`,`stuedntinformation`.`Name` AS `Name` from `stuedntinformation` | utf8 | utf8_general_ci | +--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+ 1 row in set (0.00 sec)
We will use the above view name ‘view_Student’ to create a table. The following is the query to create a table using view −
mysql> CREATE TABLE CreatingTableUsingViewStudent AS -> select Id,Name from view_Student; Query OK, 0 rows affected (0.50 sec) Records: 0 Duplicates: 0 Warnings: 0
Now you can check the DDL of a table using show command. The query is as follows −
mysql> show create table CreatingTableUsingViewStudent;
Output
+-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | CreatingTableUsingViewStudent | CREATE TABLE `creatingtableusingviewstudent` ( `Id` int(11) DEFAULT NULL, `Name` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci | +-------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
Updated on: 30-Jul-2019
2K+ Views
Advertisements