How to easily 'create table from view' syntax in MySQL?

You can create a table from a view using the create table selection syntax. The syntax is as follows -

CREATE TABLE yourTableName AS SELECT
yourColumnName1,yourColumnName2,yourColumnName3,........N from yourViewName;

To run the above query, you first need to create a table and then you need to create a view on the table. Then run the query.

First, you need to create a table. The query to create a table is as follows -

mysql> create table StuedntInformation
   -> (
   -> Id int,
   -> Name varchar(100)
   -> );
Query OK, 0 rows affected (0.54 sec)

Above, we created a table. After that you need to create a view. The query to create the 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 named "view_Student". Use the show command to inspect the view.

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. Following is the query to create the 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 the 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)

The above is the detailed content of How to easily 'create table from view' syntax in MySQL?. For more information, please follow other related articles on the PHP Chinese website!