Working with Your MySQL Database CHAPTER 9 225 Let’s look at a few of the more common uses of ALTER TABLE. One thing that comes up frequently is the realization that you haven’t made a particular column “big enough” for the data it has to hold. For example, in our Customers table, we have allowed names to be 30 characters long. After we start getting some data, we might notice that some of the names are too long and are being truncated. We can fix this by changing the data type of the column so that it is 45 characters long instead: alter table customers. | 225 Working with Your MySQL Database Chapter 9 Let s look at a few of the more common uses of ALTER TABLE. One thing that comes up frequently is the realization that you haven t made a particular column big enough for the data it has to hold. For example in our Customers table we have allowed names to be 30 characters long. After we start getting some data we might notice that some of the names are too long and are being truncated. We can fix this by changing the data type of the column so that it is 45 characters long instead alter table customers modify name char 45 not null Another common occurrence is the need to add a column. Imagine that a sales tax on books is introduced locally and that Book-O-Rama needs to add the amount of tax to the total order but keep track of it separately. We can add a tax column to the Orders table as follows alter table orders add tax float 6 2 after amount Getting rid of a column is another case that comes up frequently. We can delete the column we just added as follows alter table orders drop tax Deleting Records from the Database Deleting rows from the database is very simple. You can do this using the DELETE statement which generally looks like this DELETE FROM table WHERE condition LIMIT number If you write DELETE FROM table on its own all the rows in a table will be deleted so be careful Usually you want to delete specific rows and you can specify the ones you want to delete with a WHERE clause. You might do this if for example a particular book were no longer available or if a particular customer hadn t placed any orders for a long time and you wanted to do some housekeeping delete from customers where customerid 5 9 Working with Your MySQL Database The LIMIT clause can be used to limit the maximum number of rows that are actually deleted. 226 Using MySQL Part II Dropping Tables At times you may want to get rid of an entire table. You can do this with the DROP TABLE statement. This is very simple and it looks like this DROP
