Table can be created using create statement. We can create table using following statement like

create table employee (id int(11) not null auto_increment primary key, name varchar(255));

But sometime we need to create a new table based on the content of old table. We use following statement to create new tables based on old tables.

1. Create new table, copy table structure only from other table

Syntax

create table employee_new like employee

Explanation:

It will create an empty table with same structure as old table. Here it will create a table employee_new having same columns as employee. It will copy indexes from old table to new table as well. create table statement with 'Like' works with table only not for views.

2. Create new table, copy both table structure and data from other table

Syntax

create table employee_new as select * from employee

CREATE table employee_new SELECT * FROM employee

CREATE table employee_new (SELECT * FROM employee)

Explanation:

It will create a new table, employee_new with both structure and content of old table, employee. It will not copy indexes from old table

3. Create new table, Copy few column only from old table

Syntax

Create table employee_new (select a,b from employee);

Explanation:

It will create a new table employee_new having two columns as 'a' and 'b'. It will copy structure as well as data from table employee.

4. Create new table, copy few column from old table and add additional column in new table

Syntax

Create table employee_new (userName varchar(255)) select * from employee

Explanation:

It will create a new table employee_new, having structure and data same as old table employee. In addition to data and structure of old table it will have additional column 'userName'. If same name present in new column name and in old table, then it uses content of old tables. Currently if userName exists in employee table, it will use content of employee table only. It will not copy indexes from old table

5. Create new table, copy data from old table based on condition

Syntax

create table employee_new as select * from employee where id<300

create table employee_new select * from employee where id<300

CREATE table employee_new (SELECT * FROM employee where id<300)

Explanation:

It will create a table a new table having same column name and data of old table employee with records using condition having id less than 300. It will not copy indexes from old table

6. Create new table, copy structure and data from multiple tables using join

Syntax

CREATE table employee_new (SELECT em.id,em.name,lc.place FROM employee em, location lc where em.id=lc.emp_id)

Explanation:

It will create a new table employee_new having two columns from employee table and one column from location table. It will not copy indexes from old table