MySQL/Language/Table manipulation

CREATE TABLE
Create table syntax is:

The rows returned by the "select" query can be saved as a new table. The datatype will be the same as the old table. For e.g.

The table size limit depends on the filesystem, and is generally around 2TB.

Moreover, MySQL can assure the unique keys auto-incrementation with the option. In case of table truncation, the counter can be reset with:

Copy a table
To duplicate the same structure (names, fields types, and indexes, but no record):

To copy the records into the previous result:

Temporary tables
It's possible to create variables of type "table", which as the other variables, will be erased at the end of their scripts. It's called the "temporary tables":

Example with a named column:

Attention: if the temporary table column name doesn't correspond to the field which fills it, an additional column will be added with this field name. Eg: Field 	Type 	Null 	Key 	Default 	Extra id1 	int(11) 	YES 		NULL id 	int(11) 	NO 		0

Attention: all temporary tables are dropped at the end of the MySQL connection which had created them.

ALTER TABLE
ALTER TABLE command can be used when you want to add/delete/modify the columns and/or the indexes; or, it can be used to change other table properties.

Add a column:

Modify a column:

Drop a column:

Re-order the record in a table: (this operation is only supported by some Storage Engines; it could make some query faster)

Rename a table
In order to rename a table, you must have ALTER and DROP privileges on the old table name (or on all the tables), and CREATE and INSERT privileges on the new table name (or on all the tables).

You can use ALTER TABLE to rename a table:

You can rename more than one table with a single command:

RENAME is a shortcut. You can also use the ALTER TABLE statement: Using ALTER TABLE you can only rename one table per statement, but it's the only way to rename temporary tables.

DROP TABLE
Will completely delete the table and all the records it contains.

You can also drop more than one table with a single statement:

There are come optional keywords: TEMPORARY must be specified, to drop a temporary table. IF EXISTS tells the server that it must not raise an error if the table doesn't exist.