Database Reference
In-Depth Information
an
INSERT INTO...SELECT
statement. Enter the following to delete the alternative
table:
DROP TABLE
birds_new_alternative
;
Be careful with the
DROP TABLE
statement.Once you delete a table, there is usually no
way (or at least no easy way) to get it back, unless you have a backup copy of the data-
base. That's why I suggested that you make a backup at the beginning of this chapter.
Let's now alter the new table and add a column named
wing_id
to be able to join the
table to the
birds_wing_shapes
table. To add the column, enter thefollowing SQL
statement in
mysql
:
ALTER TABLE
birds_new
ADD COLUMN
wing_id
CHAR
(
2
);
This will add a column named
wing_id
to the table with a fixed character data type and
a maximum width of two characters. I have made sure to give the column the exact same
data type and size as the corresponding column in
birds_wing_shapes
, because that
enables us to refer to the column in each table to join the tables.
Let's look at the structure of the
birds_new
table to see how it looks now. Enter the fol-
lowing in your
mysql
client:
DESCRIBE birds_new;
+-----------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default |
Extra |
+-----------------+--------------+------+-----+---------+----------------+
| bird_id | int(11) | NO | PRI | NULL |
auto_increment |
| scientific_name | varchar(100) | YES | UNI | NULL
| |
| common_name | varchar(50) | YES | | NULL
| |
| family_id | int(11) | YES | | NULL
| |
| description | text | YES | | NULL
| |
| wing_id | char(2) | YES | | NULL
| |
+-----------------+--------------+------+-----+---------+----------------+
Looking over the results set for the table, you should recognize the first six columns.
