Making Changes to Tables and Records

As you begin to use your MySQL database, you will find that you need to make changes to both the structure and content of the database tables. The following section describes how you can alter the structure of your MySQL tables and change the content of MySQL records. If you are not already connected to a mysql session, type the following command (assuming the mysql user name of root):

 $ mysql -u root -p Enter password: ******* mysql>  

To use the examples shown in the following sections, identify the database (allusers in this example) as the current database by typing the following:

 mysql> USE allusers; Database changed 

Altering MySQL tables

After you have created your database tables, there will inevitably be changes you want to make to them. This section describes how to use the ALTER command during a mysql session for the following tasks: adding a column, deleting a column, renaming a column, and changing the data type for a column.

To add a column to the end of your table that displays the current date, type the following:

mysql> ALTER TABLE names ADD curdate TIMESTAMP; 

The previous line tells mysql to change the table in the current database called names (ALTER TABLE names), add a column named curdate (ADD curdate), and assign the value of that column to display the last edit date (TIMESTAMP data type). If you decide later that you want to remove that column, you can remove it by typing the following:

mysql> ALTER TABLE names DROP COLUMN curdate;  

If you want to change the name of an existing column, you can do so using the CHANGE option to ALTER. Here is an example:

mysql> ALTER TABLE names CHANGE city town varchar(20);  

In the previous example, the names table is chosen (ALTER TABLE names) to change the name of the city column to town (CHANGE city town). The data type of the column must be entered as well (varchar(20)), even if you are not changing it. In fact, if you just want to change the data type of a column, you would use the same syntax as the previous example but simply repeat the column name twice. Here’s an example:

mysql> ALTER TABLE names CHANGE zipcode zipcode INTEGER; 

The previous example changes the data type of the zipcode column from its previous type (varchar) to the INTEGER type.

Updating and deleting MySQL records

You can select records based on any value you choose and update any values in those records. When you are in your mysql session, you can use UPDATE to change the values in a selected table. Here is an example:

mysql> UPDATE names SET streetaddr = "933 3rd Avenue" WHERE firstname = "Chris";  Query OK, 1 row affected (0.00 sec)  Rows matched: 1 Changed: 1 Warnings: 0  

This example attempts to update the names table (UPDATE names). In this example, each record that has the firstname column set to "Chris" will have the value of the streetaddr column for that record changed to "933 3rd Avenue" instead. Note that the query found one (1) row that matched. That one row matched was also changed, with no error warnings necessary. You can use any combination of values to match records (using WHERE) and change column values (using SET) that you would like. After you have made a change, it is a good idea to display the results to make sure that the change was made as you expected.

To remove an entire row (that is, one record), you can use the DELETE command. For example, if you wanted to delete any row where the value of the firstname column is "Chris", you would type the following:

mysql> DELETE FROM names WHERE firstname = "Chris"; Query OK, 1 row affected (0.00 sec) 

The next time you show the table, there should be no records with the first name Chris.