MySQL/Language/Table manipulation

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search

CREATE TABLE[edit]

Create 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

ALTER TABLE[edit]

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:

 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)

Renaming a table[edit]

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:

 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.