Editing Records

You can insert, update, or delete a database record using SQL.

To insert a record using SQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
2.	At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) to choose the travelclub database. Next you'll insert a new record into the members table using the INSERT INTO clause, just as you did when you first added records to the table.
3.	At the prompt, type INSERT INTO members VALUES ("18754", "Rita","Villard","[e-mail]"); and then press Enter (Windows) or Return (Mac). The next step involves using the * operator to select all the records from the members table so that you can verify the new record is included.
4.	At the prompt, type SELECT * FROM members; and press Enter (Windows) or Return (Mac). The new record (row) has been added to the members table (Figure 5.23). Figure 5.23. Adding a new record to an existing table.
5.	At the prompt, do one of the following: If you're running Windows, type exit and press Enter to close the MySQL Monitor. If you're running Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window.

To update a record using SQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
2.	At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) to choose the travelclub database. Now we'll use an UPDATE statement containing a SET clause to change the value for the seat_location field in all records that have an id field value less than 20000.
3.	At the prompt, type UPDATE flightprefs SET seat_location = "window" WHERE id < 20000; and press Enter (Windows) or Return (Mac). Next we'll use a wildcard operator (*) to select all the records from the flightprefs table to verify that the records are updated.
4.	At the prompt, type SELECT * FROM flightprefs; and press Enter (Windows) or Return (Mac) to display the entire table. The data has been updated for the record with the id 11664 (Figure 5.24). Figure 5.24. The data has been updated.
5.	At the prompt, do one of the following: If you're running Windows, type exit and press Enter to close the MySQL Monitor. If you're running Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window.

Tip

• Be sure you include a WHERE clause when you update a record using SQL. Otherwise, you'll change the specified value for every record in the table.

Tip

• As you may have already noticed, typing SELECT * is very useful when you want to view all the records in a table.

To delete a record using SQL:

1.	Do one of the following: To start the MySQL Monitor in Windows, follow Steps 1 through 4 of "To create a database using MySQL in Windows," earlier in this chapter. To start MySQL in a Terminal window on a Mac, follow Steps 1 through 8 of "To create a database using MySQL on a Mac," earlier in this chapter.
2.	At the mysql prompt, type use travelclub; and press Enter (Windows) or Return (Mac) to choose the travelclub database.
3.	At the prompt, type DELETE FROM members WHERE id = "18754"; and press Enter (Windows) or Return (Mac).
4.	At the prompt, type SELECT * FROM members; and press Enter (Windows) or Return (Mac) to view all the records in the table. The record with id 18754 has been deleted from the table (Figure 5.25). Figure 5.25. The record has been deleted.
5.	At the prompt, do one of the following: If you're running Windows, type exit and press Enter to close the MySQL Monitor. If you're on a Mac, type exit and press Return to stop MySQL, type exit and press Return to log out, and then quit Terminal and close the Terminal window.