Database Reference
In-Depth Information
DROP
TABLE
IF
EXISTS
limbs
;
CREATE
TABLE
limbs
(
thing
VARCHAR
(
20
),
#
what
the
thing
is
legs
INT
,
#
number
of
legs
it
has
arms
INT
#
number
of
arms
it
has
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'human'
,
2
,
2
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'insect'
,
6
,
0
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'squid'
,
0
,
10
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'fish'
,
0
,
0
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'centipede'
,
100
,
0
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'table'
,
4
,
0
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'armchair'
,
4
,
2
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'phonograph'
,
0
,
1
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'tripod'
,
3
,
0
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'Peg Leg Pete'
,
1
,
2
);
INSERT
INTO
limbs
(
thing
,
legs
,
arms
)
VALUES
(
'space alien'
,
NULL
,
NULL
);
To execute the statements in this SQL script file, change location into the
tables
directory
of the
recipes
distribution and run this command:
%
mysql cookbook < limbs.sql
You'll note that the script contains a statement to drop the table if it exists before creating
the table anew and loading it with data. That enables you to experiment with the table,
perhaps making changes to it, confident that you can easily restore it to its baseline state
any time by running the script again.
The command just shown illustrates how to specify an input file for
mysql
on the com‐
mand line. Alternatively, to read a file of SQL statements from within a
mysql
session,
use a
source
filename
command (or
\.
filename
, which is synonymous):
mysql>
source limbs.sql;
mysql>
\. limbs.sql;
SQL scripts can themselves include
source
or
\.
commands to include other scripts.
This gives you additional flexibility, but take care to avoid source loops.
A file to be read by
mysql
need not be written by hand; it could be program generated.
For example, the
mysqldump
utility generates database backups by writing a set of SQL
statements that re-create the database. To reload
mysqldump
output, feed it to
mysql
.
For example, you can copy a database over the network to another MySQL server like
this:
%
mysqldump cookbook > dump.sql
%
mysql -h other-host.example.com cookbook < dump.sql
mysql
can also read a pipe, so it can take output from other programs as its input.
Any
command that produces output consisting of properly terminated SQL statements can
be used as an input source for
mysql
. The dump-and-reload example can be rewritten