totn MariaDB

MariaDB: CREATE TABLE AS Statement

This MariaDB tutorial explains how to use the MariaDB CREATE TABLE AS statement with syntax and examples.

Description

The MariaDB 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).

Syntax

The syntax for the CREATE TABLE AS statement in MariaDB is:

CREATE TABLE [ IF NOT EXISTS ] new_table [ AS ] 
  SELECT expressions
  FROM existing_tables
  [WHERE conditions];

Parameters or Arguments

IF NOT EXISTS
Optional. If specified, the CREATE TABLE AS statement will not raise an error if the table already exists.
new_table
The name of the table that you wish to create.
AS
Optional. Whether you specify the AS keyword or not has no impact on the creation of the table.
expressions
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.
existing_tables
The existing tables from which to copy the column definitions and the associated records (as per the WHERE clause).
WHERE conditions
Optional. The conditions that must be met for the records to be copied to the new_table.

Note

  • 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.

Example

Let's look at a MariaDB CREATE TABLE AS example that shows how to create a table by copying all columns from another table.

CREATE TABLE preferred_sites AS 
  SELECT *
  FROM websites
  WHERE website_name in ('TechOnTheNet.com', 'CheckYourMath.com');

This example would create a new table called preferred_sites that included all columns from the websites table.

If there were records in the websites table, then the new preferred_sites 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.

For example:

CREATE TABLE stats AS
  SELECT pages.page_id AS "stat_id", 
         websites.website_name, pages.file_size
  FROM websites
  INNER JOIN pages ON pages.website_id = websites.website_id
  WHERE website_name in ('TechOnTheNet.com', 'CheckYourMath.com');

This example would create a new table called stats based on column definitions from both the websites and pages tables. Notice in this example that we have aliased the page_id field as stat_id since we want the field in the new stats table to be called stat_id and not page_id.

Share on: