Project 3 - 3 Remove Data and Revisit Changing Data


Project 3 “3 Remove Data and Revisit Changing Data

This project gives you hands-on experience with the commands and concepts discussed in the preceding section. You will remove data from your existing database and revisit the changing of existing data, using the DROP TABLE , DELETE FROM , INSERT INTO , UPDATE , and TRUNCATE commands.

Step by Step

  1. Drop the entire num_names table with its data from the duckwear database and use a SHOW command to verify the results:

     DROP TABLE num_names; SHOW TABLES; 
  2. In the cust_num_names table, delete the data already contained in the duck_cust table: customer numbers 1 through 7. Then use a SELECT command to verify the results:

     DELETE FROM cust_num_names WHERE names_num<=7; SELECT * FROM cust_num_names; 
  3. Insert the remaining data of first and last names into the duck_cust table and check the results:

     INSERT INTO duck_cust (cust_last, cust_first) VALUES ("Davidson", "Wolf"), ("Danson", "Raccoon"), ("Filled", "Bob"), ("Billiards", "Monte"), ("Beauvais", "Jet"), ("Seta", "Kevin"); SELECT * FROM duck_cust; 

    The auto-incrementing cust_num column has given each inserted record its unique customer number, and all fields in each record, other than cust_last and cust_first , have been filled with their default value, NULL .

  4. You now know that the last list of six customers live in the same apartment complex, so the addresses are all identical, except for their apartment numbers. By an amazing coincidence , they all named their ducks Quackers. The following syntax will allow you to update all six of their records at once:

     UPDATE duck_cust SET cust_title=9, cust_add1="1976 Stony Pointe Lane",  cust_city="Elysium", cust_state="OK", cust_zip1="73102", cust_duckname="Quackers" WHERE cust_num>=9; 
  5. The customers with numbers 9 through 14 live in apartments numbered 113, 243, 365, 142, 517, and 301, respectively. Using the following code as a guide, update each of their records with their correct apartment number:

     UPDATE duck_cust SET cust_add2="113" WHEREcust_num=9; 
  6. Use a SELECT command to verify the results of the updated table:

     SELECT * FROM duck_cust: 
  7. Now that you have consolidated all your customer data into the duck_cust table, you no longer need the data in the cust_num_names table. Use the following command:

     DELETE FROM cust_num_names; 

    You will get an error, because you ran a global command without the WHERE clause referencing a key field, and you have safe-updates in your configuration file.

  8. You can allow this DELETE command to execute in one of two ways: either put a reference to a key field in a WHERE clause, as in WHERE cust_num > =1 , or by temporarily turning off the safe-updates option (exit the MySQL Client, reenter it using the --safe-updates=0 command-line option, and then reenter the previous command). Choose whichever method you prefer.

  9. Use the SHOW TABLES; command, and then the SELECT * FROM cust_num_names; command to check the results. You will see that the table still shows up in the database, but it is now an empty set.

  10. You no longer need the data in the copy_duck_cust table either. Empty it using the TRUNCATE command, and then check the results:

     TRUNCATE copy_duck_cust; SELECT * FROM copy_duck_cust; 

    TRUNCATE returns rows affected , yet the table still exists as an empty set.

  11. Use the DROP TABLE command to remove both of the empty tables from the database, and then check the results:

     DROP TABLE copy_duck_cust, cust_num_names; SHOW TABLES; 

    Now, only the cust_names , duck_cust , and duck_title tables remain in the duckwear database.

Project Summary

In Project 3-3, you dropped entire tables, as well as deleted and truncated data from tables while leaving the tables intact. You revisited using the INSERT command to add data to single and multiple rows, and used the UPDATE command to update existing rows with new data. You finished by removing the extra tables created in earlier projects, leaving you with a clean database on which to base the projects in the next module, which covers basic reporting.




MySQL(c) Essential Skills
MySQL: Essential Skills
ISBN: 0072255137
EAN: 2147483647
Year: 2006
Pages: 109

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net