Q&A

Q: What can I do with the contents of a mysqldump file?

A:

This file is a complete replica of your database in SQL format. You can do a

lot of things with this data. You could re-create your database in Microsoft

SQL Server or Sybase by simply cutting and pasting the contents of the file.

You could also restore your database by using the dump file and the

batching ability of the mysql program. For example, to reload your

database from the output of a mysqldump command, you would use the

following syntax:

bin/mysql < file_name_of_dump_file

This would totally restore your database.

Q: Am I limited to exporting the data in text format?

A:

Don't think of it as being limited; think of it as being limitless. The developers

at TcX believe that parsing the data into various formats is a job that doesn't

belong on the server level. They believe it should be up to the programmer

and the application to format the data. They have provided a means to

export the data in the simplest manner. After you get the data, it is up to you

to do what you want with it. This is the same for importing the data. You

write the front-end that gets the data—all you have to do is produce a text

file for MySQL. Pretty easy stuff.

Exercises

1. Use the mysqlimport command, with the proper options, to complete the following
   task:

You need to import a spreadsheet from the shipping department. The shipping

department gives you its worksheet to import into the database that keeps track of all the

orders that have been shipped. The fields are separated by forward slashes, and the

data is enclosed by single quotes. Everything that is on the sheet is new data, no old

data has been updated. Import the file.

2. Use the mysqldump command properly in the following scenario:
   The boss wants to give a report to the accountants based on the Orders table. The
   accountants need the quantity and price of every item that was ordered to figure out
   commissions. They do not need the DDL, but they do need the report comma-delimited.
   Create the report.

Day 9: Querying the Database

Overview

Defining the structure of a database is only the first part of a RDBMS. The real power and usefulness of a
database is in the way it is manipulated. MySQL uses the Structured Query Language to do this.

Today, you will learn

ƒ What Structured Query Language (SQL) is and its history.

ƒ SQL basics—SELECT, UPDATE, INSERT, DELETE

ƒ How to use joins

ƒ Aggregate functions

What Is SQL and Where Did It Come From?

There have been many books written on SQL (Sams SQL Unleashed, 2nd Edition, is a great place to start).
My goal here is to give you a working knowledge of the language and its syntax—enough to let you do most
of the things you are going to do with a database. I have found that the SQL language is very easy to learn—
it was built that way. The difficult part is actually using it in a productive manner. This can only be learned by
experience.

The Structured Query Language or SQL—pronounced either as "Ess Que El" or "Sequel"—was

developed by IBM in the mid 1970s. It was developed for the logical manipulation of data in a relational

database. Until then, there was no easy way of manipulating data residing in databases. The goal of the

language was to be easy to use and very English-like in its syntax. IBM envisioned businessmen and