This tutorial explains how to use the MySQL CREATE TABLE AS statement with syntax and examples.
The MySQL CREATE TABLE AS statement is used to create a table from an existing table by copying the existing table's columns.
It is important to note that when creating a table in this way, the new table will be populated with the records from the existing table (based on the SELECT Statement).
The syntax for the CREATE TABLE AS statement in MySQL is:
Parameters or Arguments
IF NOT EXISTS
Optional. If specified, the CREATE TABLE AS statement will not raise an error if the table already exists.
The name of the table that you wish to create.
Optional. Whether you specify the AS keyword or not has no impact on the creation of the table.
The columns from the existing_tables that you would like created in the new_table. The column definitions from those columns listed will be transferred to the new_table that you create.
The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).
Optional. The conditions that must be met for the records to be copied to the new_table.
The column definitions from the existing_tables will be copied to the new_table.
The new_table will be populated with records based on the conditions in the WHERE clause.
Let's look at a MySQL CREATE TABLE AS example that shows how to create a table by copying all columns from another table.
This example would create a new table called local_companies that included all columns from the companies table.
If there were records in the companies table, then the new local_companies table would be populated with the records returned by the SELECT statement.
Next, let's look at a CREATE TABLE AS example that shows how to create a table by copying selected columns from multiple tables.
This example would create a new table called suppliers based on column definitions from both the companies and categories tables. Notice in this example that we have aliased the company_id field as supplier_id since we want the field in the new suppliers table to be called supplier_id and not company_id.