Database Reference
In-Depth Information
addition we just made. Notice that the new column,
wing_id
, was added to the end of
the table. Where a column is located matters little to MySQL or MariaDB. However, it
may matter to you as a developer, especially when working with wider tables or with
tables that have many columns. Let's try adding this column again, but this time tell
MySQL to put it after the
family_id
. First, we'll delete the column we just added. Be-
cause it's a new column, we can do this without losing data.
ALTER TABLE
birds_new
DROP COLUMN
wing_id
;
This was even simpler than adding thecolumn. Notice that we don't mention the column
data type or other options. The command doesn't need to know that in order to drop a
column. The
DROP COLUMN
clause removes the column and all of the data contained in
the column from the table. There's no
UNDO
statement in MySQL or in MariaDB, so be
careful when working with a live table.
Let's add the
wing_id
column again:
ALTER TABLE
birds_new
ADD COLUMN
wing_id
CHAR
(
2
)
AFTER family_id
;
This will put the
wing_id
column after the
family_id
in the table. Run the
DESCRIBE
statement again to see for yourself. By the way, to add a column to the first
position, you would use thekeyword
FIRST
instead of
AFTER
.
FIRST
takes no column
name.
With the
ADD COLUMN
clause of the
ALTER TABLE
statement, we can add more than
one column at a time and specify where each should go. Let's add three more columns to
the
birds_new
table. We'll add columns to join the table to the
birds_body_shapes
and
birds_bill_shapes
tables we created in the exercises
at the end of
Chapter4
. We'll also add a field to note whether a bird is an endangered spe-
cies. While we're making changes, let's change the width of the
common_name
column.
It's only 50 characters wide now. That may not be enough for some birds that have
lengthy common names. For that change, we'll usethe
CHANGE COLUMN
clause. Enter
the following in
mysql
:
ALTER TABLE
birds_new
ADD COLUMN
body_id
CHAR
(
2
)
AFTER wing_id
,
ADD COLUMN
bill_id
CHAR
(
2
)
AFTER body_id
,
ADD COLUMN
endangered
BIT
DEFAULT
b
'1'
AFTER bill_id
,
CHANGE COLUMN
common_name common_name
VARCHAR
(
255
);