Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

Changing a Column Name

Sometimes you may need to change the name of one of your columns. Maybe you misspelled it when you
created it and didn't notice until a colleague pointed it out. Or maybe your boss has a naming convention that
you need to follow. Either way, changing a column name is pretty painless.

If you need to change the name of a column, do the following:

1. Make sure the mysqld daemon is running and that you are in the mysql
   directory.


2. Start up the MySQL monitor as you did before, using the Meet_A_Geek database
   as the active database.


3. To change the name of the First_Name column to FirstName in the
   Customers table, enter the following from the command line:


4. ALTER TABLE Customers


5. CHANGE First_Name FirstName VARCHAR(20);


6. DESCRIBE Customers;
   You must specify the data type again, or you will get an error. I used the DESCRIBE command to verify
   the changes. It is not necessary to use that command after you change the table structure—I do it out of
   habit.

Changing a Column Type

Changing a column's type is similar to changing a column's name. You are going to change the Last_Name
from a VARCHAR(30) to a VARCHAR(50). Follow steps 1 and 2 of the previous example (changing a column
name). Then, instead of typing what is in step 3, type the following:

ALTER TABLE Customers

CHANGE Last_Name Last_Name VARCHAR(50);

DESCRIBE Customers;

Notice that you must use the column name twice. The reason behind this is that MySQL creates a

temporary table to hold your changes. This allows users to continue using the database as you make

changes.

Renaming a Table

To change a table's name, make sure the mysqld daemon is running and that you are in the mysql
directory. After you are sure that everything is up and running, start the MySQL monitor. From the monitor's
command line, type the following:

ALTER TABLE Customers RENAME Customer_Table;

SHOW TABLES FROM Meet_A_Geek;

Altering an existing table or column is pretty straightforward. A few syntactical gotchas are out there, but

it is generally an easy process. The hardest part is in the design. Keep that in mind when you are

planning or estimating the length of a job.

Deleting/Adding Columns and Tables

As you can see, when a table or column is created, it is not written in stone and can be changed easily. This
even applies to adding columns to an existing table or deleting unwanted columns or tables. The process,
again, is pretty straightforward.

Dropping Tables and Columns

To drop or delete tables or columns, make sure the mysqld process is running and that your current
directory is the mysql directory. Start up the MySQL monitor with the database you need to make changes
to as the active database. After you are up and running, enter the following commands:

To delete an existing table, type

DROP tablename;