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
Search WWH ::




Custom Search