Introduction to MySQL

Part:   1  2  

(Continued from previous part...)

To execute the statements in hello.sql, you can use the "source" command inside mysql:

\mysql\bin\mysql --host localhost test
......

mysql> source hello.sql
Query OK, 0 rows affected (0.05 sec)

Query OK, 1 row affected (0.00 sec)

+--------------+
| message      |
+--------------+
| Hello world! |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Or you can run mysqsl in batch mode:

\mysql\bin\mysql --host localhost test < hello.sql
message
Hello world!

As you can see, this is a much better way to execute SQL statements. So from now on, I will always run mysql in batch mode.

Dumping Data into Files - mysqldump

mysqldump is a tool to dump table structure and data to files. This tool can be used in different ways.

1. Dump tables of a database as SQL statements into a single file. Table structures will be dumped as create table statements. Data rows will be dumped as insert statements. If table is not specified, all tables will be dumped.

\mysql\bin\mysqldump --result-file=file.sql db_name [tbl_name]

2. Dump tables of a database into two files per table. One file contains a create table statement. The other file contains table data as tab delimited values. Output files will be created in a sub directory specified in the command line.

\mysql\bin\mysqldump --tab=dir_name db_name [tbl_name]

3. Dump tables of a database as XML format into a single file.

\mysql\bin\mysqldump --xml --result-file=file.xml db_name [tbl_name]

In order to test mysqldump, I created a database with two tables with the following SQL file:

-- CreateDatabase.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
DROP DATABASE IF EXISTS Library;
CREATE DATABASE Library;
USE Library;
--
CREATE TABLE Book (ID INT, Title VARCHAR(64), Author_ID INT);
INSERT INTO Book VALUES (1, 'Java', 1);
INSERT INTO Book VALUES (2, 'C++', 1);
INSERT INTO Book VALUES (3, 'FORTRAN', 2);
--
CREATE TABLE Author (ID INT, Name VARCHAR(16));
INSERT INTO Author VALUES (1, 'Herong');
INSERT INTO Author VALUES (2, 'Mike');
--
SELECT Title, Name FROM Book INNER JOIN Author ON Book.Author_ID=Author.ID;

Output from this SQL file:

Title   Name
Java    Herong
C++     Herong
FORTRAN Mike

I used the following commands to test mysqldump. The output files look perfect to me.

\mysql\bin\mysqldump --result-file=Library.sql Library
mkdir Library
\mysql\bin\mysqldump --tab=Library Library
\mysql\bin\mysqldump --xml --result-file=Library.xml Library

Loading Data from Files - mysqlimport

mysqlimport is a tool to load data stored in files into tables. Data should be stored in files as tab delimited values. Data file names without extensions should match table names. The command line syntax of mysqlimport is:

\mysql\bin\mysqlimport db_name file_1, file_2, ...

For example, I used the following command to load data back into the book table. Remember the data was dumped earlier by the mysqldump command with the --tab option.

\mysql\bin\mysqlimport Library Library\book.txt

Library.book: Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Part:   1  2