fastread homefastrread library fastread menu

MariaDB : CREATE TABLE AS Statement

Tutorial by:      Date: 2016-04-18 05:38:37

❰ Previous Next ❱

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.

table_name

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 ('Fastread.aitechtonic.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 ('Fastread.aitechtonic.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.

 

❰ Previous Next ❱


MariaDB

Submit Your Thought, Tutorial, Articls etc.

Submit Your Information India's Number one online promotion website