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 );
Search WWH ::




Custom Search