MySQL Database learning



Home / Database Structure / Tables / How to create table based on existing tables?
Share this job:


How to create table based on existing tables?

MySQL provides two ways to create a table based on another table:

CREATE TABLE ... SELECT can create a table that is empty or non-empty, depending on what is returned by the SELECT part. The following statements create a table that contains the entire content of the Product table, a table that contains partial content from Product, and an empty copy of Product:

CREATE TABLE Product_Copy1 SELECT * FROM Product;
CREATE TABLE Product_Copy2 SELECT * FROM Product WHERE price > 50;
CREATE TABLE Product_Copy3 SELECT * FROM Product WHERE 0;

Using the LIKE keyword with CREATE TABLE creates an empty table based on the definition of another table. The result is a new table with a definition that includes all column attributes and indexes of the original table. Suppose that table tbl1 looks like this:

CREATE TABLE tbl1 (i INT NOT NULL AUTO_INCREMENT, PRIMARY KEY(i)) ENGINE=InnoDB;

The result of CREATE TABLE ... LIKE differs from the result of using CREATE TABLE ... SELECT to create an empty table. Either of the following statements will create an empty copy of the table tbl1. However, the resulting copies differ in the amount of information retained from the original table structure:

CREATE TABLE tbl1_copy1 SELECT * FROM tbl1 WHERE 0;
CREATE TABLE tbl1_copy2 LIKE tbl1;

If you check structure of above tbl1_copy1 and tbl1_copy2 tables, you will find that the CREATE TABLE ... SELECT statement copies the column name and data type from the original table, but does not retain the PRIMARY KEY index information or the AUTO_INCREMENT column attribute information. The new table also uses the default storage engine, rather than the storage engine utilized by table tbl1. The copy created with CREATE TABLE ... LIKE has none of these problems.

Some table attributes are not copied, even when issuing CREATE TABLE ... LIKE. The most notable examples are:





Tables related other questions

How to add a new AUTO_INCREMENT field to an existing table?

ALTER TABLE [table] ADD [fieldname] [integer type] not null auto_increment key; Example query: ALTER TABLE buyers ADD buyer_no INT NOT NULL AUTO_INCREMENT KEY;or if you want it to be the primary k ...

How to get copy of table structure in MySQL using query?

Use the SHOW CREATE TABLE statement as follow. Run this in any MySQL client tool like phpMyAdmin etc. This may give you the desired result:SHOW CREATE TABLE [table_name]; ...

How to set AUTO_INCREMENT value other than 1 in MySQL?

The value can be set with CREATE TABLE or ALTER TABLE statements like this:CREATE TABLE `table_name` (`col_id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,`col_name` VARCHAR( 19 ) NOT NULL) ...

How to get constraints or keys of the existing table?

You can view this using SHOW INDEX statement which provides list keys already defined on the table. Try below: SHOW INDEX FROM buyers; ...

How to drop a column from the existing table?

Using ALTER TABLE statement, column can be dropped:ALTER TABLE [table_name] DROP `column_name`;Example: ALTER TABLE `buyers` DROP `age`; ...

How to add new column into an existing table in MySQL?

You may use ALTER statement as follow:ALTER TABLE `buyers` ADD `buyer_discount` INT NOT NULL;As per above example, when you add a new column to a table, MySQL places it after all existing co ...

How to drop all existing tables in one go?

To remove a table when you no longer need it, use the DROP TABLE statement:DROP TABLES tbl_users;In MySQL, a single DROP TABLE statement can name several tables to be dropped simultaneously. ...

How to change data type of an existing column?

This can be changed using ALTER statement. Take an example:ALTER TABLE `buyers` CHANGE `first_name` `first_name` CHAR(19); ...

How to get columns of an existing table in MySQL?

You can get columns of an existing table using MySQL SHOW COLUMNS statement. Take an example, this will show you columns of your existing table:SHOW COLUMNS FROM table_name;Also, you can us ...

How to get table metadata?

You can ask MySQL to show you table metadata; that is, information about your tables. Metadata includes information such as table names or column or index definitions.The INFORMATION_SCHEMA database ha ...

How to create table in MySQL?

MySQL provides more than one way to create tables: You can create an empty table, either by specifying its definition explicitly or by using the definition of an existing table. You c ...

How to create table based on existing tables?

MySQL provides two ways to create a table based on another table: CREATE TABLE ... SELECT creates a table and populates it from the result set returned by an arbitrary SELECT statement. In this ...

What is temporary table and how to create it in MySQL?

Each storage engine in MySQL implements tables with a particular set of characteristics. One characteristic held in common by all storage engines is that by default they create tables that exist until they ar ...

How to describe a table in MySQL?

MySQL provides DESCRIBE statement for this purpose. Take an example, first we will create a table then get it described: mysql> CREATE TABLE tbl_name -> ( -> id INT NOT NULL, ...

How to renaming a table in MySQL?

In MySQL, there are more than one way to rename a table. Renaming a table changes neither a table's structure nor its contents. The following statement renames table tbl1 to tbl2: ALTER TABLE tbl1 ...

How to specify multiple alterations for a table?

You can specify multiple alterations for a table with a single ALTER TABLE statement. Just separate the actions by commas. For example: ALTER TABLE `buyers` RENAME TO `shoppers`, MODIFY ID BIGI ...

How to delete table records?

To remove records from a table without removing the table itself, MySQL provides DELETE or TRUNCATE TABLE statement. Either of the following statements completely empties the named table: DELETE FRO ...