Retrieving Information from a Table
201
You could create a text file
pet.txt
containing one record per line, with values separated by tabs,
and given in the order in which the columns were listed in the
CREATE TABLE
statement. For missing
values (such as unknown sexes or death dates for animals that are still living), you can use
NULL
values. To represent these in your text file, use
\N
(backslash, capital-N). For example, the record for
Whistler the bird would look like this (where the whitespace between values is a single tab character):
Whistler Gwen bird \N 1997-12-09 \N
To load the text file
pet.txt
into the
pet
table, use this statement:
mysql>
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;
If you created the file on Windows with an editor that uses
\r\n
as a line terminator, you should use
this statement instead:
mysql>
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
->
LINES TERMINATED BY '\r\n';
(On an Apple machine running OS X, you would likely want to use
LINES TERMINATED BY '\r'
.)
You can specify the column value separator and end of line marker explicitly in the
LOAD DATA
statement if you wish, but the defaults are tab and linefeed. These are sufficient for the statement to
read the file
pet.txt
properly.
If the statement fails, it is likely that your MySQL installation does not have local file capability enabled
by default. See
Section 6.1.6, “Security Issues with
LOAD DATA LOCAL
”
, for information on how to
change this.
When you want to add new records one at a time, the
INSERT
statement is useful. In its simplest
form, you supply values for each column, in the order in which the columns were listed in the
CREATE
TABLE
statement. Suppose that Diane gets a new hamster named “Puffball.” You could add a new
record using an
INSERT
statement like this:
mysql>
INSERT INTO pet
->
VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);
String and date values are specified as quoted strings here. Also, with
INSERT
, you can insert
NULL
directly to represent a missing value. You do not use
\N
like you do with
LOAD DATA
.
From this example, you should be able to see that there would be a lot more typing involved to load
your records initially using several
INSERT
statements rather than a single
LOAD DATA
statement.
3.3.4. Retrieving Information from a Table
The
SELECT
statement is used to pull information from a table. The general form of the statement is:
SELECT
what_to_select
FROM
which_table
WHERE
conditions_to_satisfy
;
what_to_select
indicates what you want to see. This can be a list of columns, or
*
to indicate “all
columns.”
which_table
indicates the table from which you want to retrieve data. The
WHERE
clause
is optional. If it is present,
conditions_to_satisfy
specifies one or more conditions that rows must
satisfy to qualify for retrieval.
3.3.4.1. Selecting All Data
The simplest form of
SELECT
retrieves everything from a table:
mysql>
SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name | owner | species | sex | birth | death |
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
