MySQL CREATE TABLE AS Keyword
The MySQL CREATE TABLE AS keyword is used to create a table from an existing table by copying the existing table's columns. 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 using CREATE TABLE AS keyword in MySQL given below:
/* copy all columns from a table */ CREATE TABLE new_table AS (SELECT * FROM old_table); /* copy selected columns from a table */ CREATE TABLE new_table AS (SELECT column_1, column2, ... FROM old_table); /* copy columns from multiple table */ CREATE TABLE new_table AS (SELECT column_1, column2, ... FROM old_table_1, old_table_2, ...);
Example:
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
Copy all columns of a table: The below statement is used to create a new table called Employee_PT by copying all columns of Employee table. The new table will contain those records of Employee table where the age of the employee is less than 28.
CREATE TABLE Employee_PT AS (SELECT * FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 2 Marry New York 24 2750 3 Jo Paris 27 2800 -
Copy selected columns of a table: It is also possible to copy selected columns when using CREATE TABLE AS statement. In the example below, the columns which are copied are: EmpID, Name and City.
CREATE TABLE Employee_PT AS (SELECT EmpID, Name, City FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City 1 John London 2 Marry New York 3 Jo Paris -
Using AS clause: The AS clause can be used to rename the column name. See the example below:
CREATE TABLE Employee_PT AS (SELECT EmpID, Name AS EmployeeName, City FROM Employee WHERE Age < 28); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID EmployeeName City 1 John London 2 Marry New York 3 Jo Paris -
Copy columns from multiple tables: Consider one more table called Contact_Info with the following records:
Phone_Number EmpID Address Gender +1-8054098000 2 Brooklyn, New York, USA F +33-147996101 3 Grenelle, Paris, France M +31-201150319 4 Geuzenveld, Amsterdam, Netherlands F +86-1099732458 6 Yizhuangzhen, Beijing, China M +65-67234824 7 Yishun, Singapore M +81-357799072 8 Koto City, Tokyo, Japan M The below statement is used to create a new table called Employee_PT which will contain selected columns of Employee and Contact_Info tables. The new table will contain records based on INNER JOIN.
CREATE TABLE Employee_PT AS (SELECT A.EmpID, A.Name, A.City, B.Address FROM Employee A INNER JOIN Contact_Info B ON A.EmpID = B.EmpID); -- see the result SELECT * FROM Employee_PT;
This will produce the result as shown below:
EmpID Name City Address 2 Marry New York Brooklyn, New York, USA 3 Jo Paris Grenelle, Paris, France 4 Kim Amsterdam Geuzenveld, Amsterdam, Netherlands 6 Huang Beijing Yizhuangzhen, Beijing, China
❮ MySQL Keywords