Database Reference
In-Depth Information
These field names don't have to be the same as the columns in the tables for which they
will be associated, and don't have to conform to any convention for our purposes. We'll
join the results of this SQL statement with the previous one withthe
UNION
, but with the
field names first. This was also covered in
Chapter9
.
Having tested the
SELECT
statements, we're now ready to put them together to export
data to a text file. Execute the following on your server:
(
SELECT
'scientific name'
,
'common name'
,
'family name'
)
UNION
(
SELECT
birds
.
scientific_name
,
IFNULL
(
common_name
,
''
),
bird_families
.
scientific_name
FROM
rookery
.
birds
JOIN
rookery
.
bird_families
USING
(
family_id
)
JOIN
rookery
.
bird_orders
USING
(
order_id
)
WHERE
bird_orders
.
scientific_name
=
'Charadriiformes'
ORDER BY
common_name
INTO OUTFILE
'/tmp/birds-list.csv'
FIELDS
ENCLOSED BY
'"'
TERMINATED BY
'|'
ESCAPED BY
'\\'
LINES TERMINATED BY
'\n'
);
That should have executed without any problems. Because we've already discussed the
SELECT
statements in general, let's focus on the
INTO OUTFILE
clause in the second
SELECT
statement. First notice that the path for the export file is
/tmp
. MySQL will gen-
erally only write to an accessible directory like this one, one in which everyone on the
server has full read and write privileges. Next notice that the subclauses are listed after the
file path and name — the opposite of
LOAD DATA INFILE
. The subclauses, though,
are the same.
Here we're enclosing fields with double quotes and separating them with a vertical bar.
We're using the backslash as the escape character. For the
SELECT...INTO OUTFILE
statement, you have to include the
ESCAPED BY
subclause, because there is no default
escape character for this statement. There are two backslashes here because the first es-
capes the second; a backslash by itself is an escape character in this command. Finally,
we're terminating each line with a newline character.
Here are the first few lines of the file generated by the previous
SELECT...INTO
OUTFILE
statement:
"scientific name"|"common name"|"family name"
"Charadrius vociferus"|"Killdeer"|"Charadriidae"
"Charadrius montanus"|"Mountain Plover"|"Charadriidae"
"Charadrius alexandrinus"|"Snowy Plover"|"Charadriidae"
