CREATE TABLE
editCreate table syntax is:
Create table tablename (FieldName1 DataType, FieldName2 DataType)
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.
CREATE TABLE LearnHindi
select english.tag, english.Inenglish as english, hindi.Inhindi as hindi
FROM english, hindi
WHERE english.tag = hindi.tag
The table size limit depends on the filesystem, and is generally around 2TB[1].
Moreover, MySQL can assure the unique keys auto-incrementation with the option AUTO_INCREMENT
. In case of table truncation, the counter can be reset with:
ALTER TABLE tablename AUTO_INCREMENT = 1
Copy a table
editTo duplicate the same structure (names, fields types, and indexes, but no record):
CREATE TABLE `new1` LIKE `old1`;
To copy the records into the previous result:
INSERT INTO `new1` SELECT * FROM `old1`;
Temporary tables
editIt'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":
CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1 AS (SELECT * FROM MyTable1)
Example with a named column:
CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1(id INT) AS (SELECT id FROM MyTable1)
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:
CREATE TEMPORARY TABLE IF NOT EXISTS MyTempTable1(id1 INT) AS (SELECT id FROM MyTable1);
SHOW FIELDS FROM MyTempTable1;
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[2].
ALTER TABLE
editALTER 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:
ALTER TABLE awards
ADD COLUMN AwardCode int(2)
Modify a column:
ALTER TABLE awards
CHANGE COLUMN AwardCode VARCHAR(2) NOT NULL
ALTER TABLE awards
MODIFY COLUMN AwardCode VARCHAR(2) NOT NULL
Drop a column:
ALTER TABLE awards
DROP COLUMN AwardCode
Re-order the record in a table:
ALTER TABLE awards ORDER BY id
(this operation is only supported by some Storage Engines; it could make some query faster)
Rename a table
editIn 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:
RENAME TABLE `old_name` TO `new_name`
You can rename more than one table with a single command:
RENAME TABLE `old1` TO `new1`, `old2` TO `new2`, ...
RENAME is a shortcut. You can also use the ALTER TABLE statement:
ALTER TABLE `old` RENAME `new`
Using ALTER TABLE you can only rename one table per statement, but it's the only way to rename temporary tables.
DROP TABLE
edit DROP TABLE `awards`
Will completely delete the table and all the records it contains.
You can also drop more than one table with a single statement:
DROP TABLE `table1`, `table2`, ...
There are come optional keywords:
DROP TEMPORARY TABLE `table`;
DROP TABLE `table` IF EXISTS;
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.