• Choose a category
• All
• Classic-Fiction

WHERE name = ‘Bill Gates‘;

The UPDATE command has a few parts:

♦ The UPDATE command. This indicates which table you will modify.

♦ The SET command. This indicates a new assignment.

♦ Assign a new value to a field. This uses a standard programming-style assignment statement to attach a new value to the indicated field. You can modify more than one field at a time. Just separate the field = value pairs with commas.

♦ Specify a WHERE clause. You don’t want this change to happen to all the records in your database. You want to change only the e-mail address in records where the name is Bill Gates. Use the WHERE clause to specify which records you intend to update.

More than one person in your database may be named Bill Gates. Names aren’t guaranteed to be unique, so they aren’t really the best search criteria. This situation is actually a very good reason to use primary keys. A better version of this update looks as follows:

UPDATE contact

SET email = ‘bill@vistaRocks.com‘

WHERE contactID = 1;

The contactID is guaranteed to be unique and present, so it makes an ideal search criterion. Whenever possible, UPDATE (and DROP) commands should use primary key searches so that you don’t accidentally change or delete the wrong record.

Deleting a record

Sometimes, you need to delete records. SQL has a command for this eventuality, and it’s pretty easy to use:

DELETE FROM contact

WHERE contactID = 1;

The preceding line deletes the entire record with a contactID of 1.

Be very careful with the DELETE command, as it is destructive. Be absolutely sure that you have a WHERE clause, or you may delete all the records in your table with one quick command! Likewise, be sure that you understand the WHERE clause so that you aren’t surprised by what gets deleted. You’re better off running an ordinary SELECT using the WHERE clause before you DELETE, just to be sure that you know exactly what you’re deleting. Generally, you should DELETE based on only a primary key so that you don’t produce any collateral damage.

Exporting Your Data and Structure

After you’ve built a wonderful data structure, you probably will want to export it for a number of reasons:

♦ You want a backup. Just in case something goes wrong!

♦ You want to move to a production server. It’s smart to work on a local (offline) server while you figure things out, but eventually you’ll need to move to a live server. Moving the actual database files is tricky, but you can easily move a script.

♦ You want to perform data analysis. You may want to put your data in a spreadsheet for further analysis or in a comma-separated text file to be read by programs without SQL access.

♦ You want to document the table structure. The structure of a data set is extremely important when you start writing programs using that structure. Having the table structure available in a word-processing or PDF format can be very useful.

MySQL (and thus phpMyAdmin) has some really nice tools for exporting your data in a number of formats.

Figure 2-15 shows an overview of the Export tab, showing some of the features.

The different styles of output are used for different purposes:

♦ CSV (comma-separated value) format: A plain ASCII comma-separated format. Each record is stored on its own line, and each field is separated by a comma. CSV is nice because it’s universal. Most spreadsheet programs can read CSV data natively, and it’s very easy to write a program to read CSV data, even if your server doesn’t support MySQL. If you want to back up your data to move to another server, CSV is a good choice. Figure 2-16 shows some of the options for creating a CSV file.

Figure 2-15: These are some of the various output techniques.

Figure 2-16: You have several options for creating CSV files.

The data file created using the specified options looks like the following:

‘contactID‘,‘name‘,‘company‘,‘email‘

‘1‘,‘Bill Gates‘,‘Microsoft‘,‘bill@msBob.com‘

‘2‘,‘Steve Jobs‘,‘Apple‘,‘steve@rememberNewton.com‘

‘3‘,‘Linus Torvalds‘,‘Linux