Project 3 - 2 Make Changes to a Database

Project 3 “2 Make Changes to a Database

This project gives you hands-on experience with the commands and concepts discussed in the preceding section. You will make changes to your existing database structure and its data, using the ALTER TABLE , CREATE INDEX , UPDATE , and INSERT INTO commands, as well as set the SAFE-UPDATES option to see how it functions.

Step by Step

1. Open a command-line window and log in to your MySQL Client as in the previous projects. Specify using the duckwear database.

2. Use the following code to look at the contents of cust_names and cust_num_names :

    SELECT * FROM cust_names; SELECT * FROM cust_num_names; 

3. You can see that both tables have first name and last name columns , but cust_names does not have a customer number column. Use the following code to compare the description of the customer number columns in each table:

    DESC cust_names; DESC cust_num_names; 

4. Use the following ALTER TABLE command to add the names_num column to the cust_names table definition and change the names of the other two columns:

    ALTER TABLE cust_names ADD names_num MEDIUMINT(9) PRIMARY KEY AUTO_INCREMENT FIRST, CHANGE cust_first names_first CHAR(15), CHANGE cust_last names_last CHAR(20); 

5. Use the following commands to view how the table description and contents have changed:

    DESC cust_names; SELECT * FROM cust_names; 

   You see that a new column has been added and set as an auto-incrementing key, and the first and last name columns have had their names changed. When you compare the description of the cust_names table with the cust_num_names description, which you are trying to match, it is clear that none of the columns in cust_names have been set to NOT NULL .

6. Use the ALTER TABLE command to set all three columns in cust_names to NOT NULL :

    ALTER TABLE cust_names MODIFY names_num MEDIUMINT(9) NOT NULL AUTO_INCREMENT, MODIFY names_first CHAR(15) NOT NULL, MODIFY names_last CHAR(20) NOT NULL; DESC cust_names; DESC cust_num_names; 

   Now the descriptions of cust_names and cust_num_names are identical.

7. Use two SELECT commands to view and compare the data in both tables:

    SELECT * FROM cust_num_names; SELECT * FROM cust_names; 

8. Before you use the UPDATE command to make the data in both tables match, open your configuration file ( my.cfg or my.ini ) and add the following code to the bottom of the file:

    [mysql] safe-updates 

9. Exit the MySQL Client and open it again to update the configuration file. The safe-updates option is now running.

10. Choose your duckwear database with the USE command.

11. By examining the results of step 7, you can see that customers 8 and 9 have an extra space in front of their last names, and customers 12 and 13 need their first and last names placed into the correct fields. The following syntax uses both the UPDATE and REPLACE INTO commands to correct these discrepancies:

     UPDATE cust_names SET names_last="Davidson" WHERE names_num=8; UPDATE cust_names SET names_last="Danson" WHERE names_num=9; UPDATE cust_names SET names_first="Jet", names_last="Beauvais" WHERE names_num=12; REPLACE INTO cust_names (names_num, names_first, names_last) VALUES ("13", "Kevin", "Seta"); 

12. Use the SELECT * FROM cust_names; command to see the repaired table data.

13. In order to experiment with the LIMIT option of the UPDATE command, you need a table that has duplicate information. Since copy_duck_cust lost its key definition when it was made, you can use it. First, insert a duplicate row.

     INSERT INTO copy_duck_cust VALUES (6, 5, 'Kazui', 'Wonko', 'PhD', '42 Cube Farm Lane', 'Gatehouse',  'Vlimpt', 'CA', '46362', 0, 'Fitzwhistle', '1961:12:04'); 

14. Use the SELECT * FROM copy_duck_cust; command. You will see that there are now two identical records in the copy_duck_cust table.

15. You have been informed that Sir Wonko has two addresses and wants catalogs sent to both of them. His second address is 1630 Revello Drive, Shady Hill, CA 31626-2882. Use the following code to change only one of the two duplicate records and then verify the results:

     UPDATE copy_duck_cust SET cust_num=8, cust_add1="1630 Revello Drive", cust_add2=null,    cust_city="Shady Hill", cust_zip1="31626", cust_zip2="2882" WHERE cust_last="Kazui"LIMIT 1; SELECT * FROM copy_duck_cust WHERE cust_last="Kazui"; 

    Sir Wonko now has two addresses and a customer number for each, but the rest of his information is correctly duplicated in both records. Without the LIMIT clause, both records would have been changed, because both of them fit the WHERE criteria.

16. Now that all the records currently in copy_duck_cust are unique, create a unique index on the cust_num column using the following command:

     CREATE UNIQUE INDEX copy_index ON copy_duck_cust(cust_num); 

17. Use the DESC copy_duck_cust; command to check that the cust_num column is now listed as the primary key.

18. Since the record for customer number 8 is now correct, insert it into duck_cust :

     INSERT INTO duck_cust SELECT * FROM copy_duck_cust WHERE cust_num=8; 

19. Alter the num_names table by dropping the cust_num field and verify the results:

     ALTER TABLE num_names DROP names_num; SELECT * FROM num_names; 

    Now the num_names table has only two fields defined, instead of its previous three.

Project Summary

In this project, you practiced using the ALTER TABLE , CREATE INDEX , REPLACE INTO, and UPDATE commands. You have learned that there are sometimes multiple ways of accomplishing the same data-altering results by using different commands.

With a certain amount of forethought, and occasionally a little cleaning up of the original source, you can use a file with consistent separators to load many records into a database with one command, thereby saving yourself a great deal of work.

You have placed the safe-updates option in your configuration file, in order to safeguard your database from being inadvertently overwritten. Earlier in this section, you learned the command-line instruction that turns off safe-updates temporarily, so that you can accomplish a global insert operation.

Using what you ve learned in this section and practiced in this project, you can now add or drop indexes or column definitions; change field names; modify field types, sizes, or options; and update data within single or multiple records, all with existing tables.

Progress Check

1.	What are the four main options to the ALTER TABLE command?
2.	How do you add an index called Play_Index to a field called dramas in an existing table called Shakespeare ?
3.	What command syntax should you use when you need to put new values into an entire record in a table?
4.	What do you place into your configuration file to guard against accidental global updates?

Answers

1.	The four main ALTER TABLE options are ADD, MODIFY, CHANGE, and DROP.
2.	You can add this index with the following command: CREATE INDEX Play_Index ON Shakespeare(dramas);
3.	To put new values into an entire record in a table, use the following syntax: REPLACE INTO <table_name> (<field_name1>[, <field_name2> , . . .]) VALUES (<value1>[, <value2>, . . .)];
4.	To guard against accidental global updates, add the following to the configuration file: [mysql] safe-updates