Learning Mysql

If you want it added in a specific position, use the
AFTER
keyword:
mysql> ALTER TABLE artist ADD formed YEAR AFTER artist_id;
Query OK, 6 rows affected (0.03 sec)
Records: 6 Duplicates 0 Warnings 0
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | PRI | 0 | |
| formed | year) | YES | | NULL | |
| artist_name | char) | YES | | Unknown | |
+-------------+-------------+------+-----+---------+-------+
3 rows inset sec)
To remove a column, use the
DROP
keyword followed by the column name. Here’s how to get rid of the newly added formed column:
mysql> ALTER TABLE artist DROP formed;
Query OK, 6 rows affected (0.02 sec)
Records: 6 Duplicates 0 Warnings This removes both the column structure and any data contained in that column. It also removes the column from any index it was in if it’s the only column in the index, the index is dropped, too. You can’t remove a column if it’s the only one in a table to do this, you drop the table instead as explained later in Deleting Structures Be careful when dropping columns you discard both the data and the structure of your table.
When the structure of a table changes, you will generally have to modify any
INSERT
statements that you use to insert values in a particular order. We described
INSERT
statements in The INSERT Statement in Chapter 5.
MySQL allows you to specify multiple alterations in a single ALTER TABLE
statement by separating them with commas. Here’s an example that adds anew column and adjusts another:
mysql> ALTER TABLE artist ADD formed YEAR, MODIFY artist_name char(256);
Query OK, 6 rows affected, 1 warning (0.08 sec)
Records: 6 Duplicates 0 Warnings Its very efficient to join multiple modifications in a single operation, as it potentially saves the cost of creating anew table, copying data from the old table to the new table,
dropping the old table, and renaming the new table with the name of the old table for each modification individually.

Download 4.24 Mb.

Share with your friends: