Tutorial Addendum on SQL - Addition to MySQL

To assassinate the statements in hello.sql, you can use the "source" command

    

inside mysql:

    

 

    
mysqlinmysql --host localhost test

    
......

    
mysql> antecedent hello.sql

    
Query OK, 0 rows afflicted (0.05 sec)

    
Query OK, 1 row afflicted (0.00 sec)

    
+--------------+

    
| bulletin      |

    
+--------------+

    
| Accost world! |

    
+--------------+

    
1 row in set (0.00 sec)

    
Query OK, 0 rows afflicted (0.00 sec)

    

Or you can run mysqsl in accumulation mode:

    

 

    
mysqlinmysql --host localhost analysis < hello.sql

    
message

    
Hello world!

    

As you can see, this is a abundant bigger way to assassinate SQL statements.

    

So from now on, I will consistently run mysql in accumulation mode.

    

Dumping Data into Files - mysqldump

    

mysqldump is a apparatus to dump table anatomy and data to files. This apparatus can be

    

used in altered ways.

    

1. Dump tables of a database as SQL statements into a individual file. Table structures

    

will be dumped as make table statements. Data rows will be dumped as admit

    

statements. If table is not specified, all tables will be dumped.

    

 

    
mysqlinmysqldump --result-file=file.sql db_name [tbl_name]

    

2. Dump tables of a database into two files per table. One book contains a create

    

table statement. The additional book contains table data as tab belted values.

    

Output files will be created in a sub agenda defined in the command line.

    

 

    
mysqlinmysqldump --tab=dir_name db_name [tbl_name]

    

3. Dump tables of a database as XML architecture into a individual file.

    

 

    
mysqlinmysqldump --xml --result-file=file.xml db_name [tbl_name]

    

In adjustment to analysis mysqldump, I created a database with two tables with the following

    

SQL file:

    

 

    
-- CreateDatabase.sql

    
-- Absorb (c) 1999 by Dr.  Yang

    
--

    
DROP2 DATABASE IF EXISTS Library;

    
CREATE DATABASE Library;

    
USE Library;

    
--

    
CREATE TABLE  book  (ID INT, Appellation VARCHAR(64), Author_ID INT);

    
INSERT INTO  book  Ethics (1,  Java , 1);

    
INSERT INTO  book  Ethics (2,  C++ , 1);

    
INSERT INTO  book  Ethics (3,  FORTRAN , 2);

    
--

    
CREATE TABLE Columnist (ID INT, Name VARCHAR(16));

    
INSERT INTO Columnist Ethics (1,   );

    
INSERT INTO Columnist Ethics (2,  Mike );

    
--

    
SELECT Title, Name FROM  book  Close Accompany Columnist ON Book.Author_ID=Author.ID;

    

Output from this SQL file:

    

 

    
Title   Name

    
Java    

    
C++     

    
FORTRAN Mike

    

I acclimated the afterward commands to analysis mysqldump. The achievement files attending absolute to me.

    

 

    
mysqlinmysqldump --result-file=Library.sql Library

    
mkdir Library

    
mysqlinmysqldump --tab=Library Library

    
mysqlinmysqldump --xml --result-file=Library.xml Library

    

Loading Data from Files - mysqlimport

    

mysqlimport is a apparatus to amount data stored in files into tables. Data should be stored

    

in files as tab belted values. Data book names after extensions should

    

match table names. The command band syntax of mysqlimport is:

    

 

    
mysqlinmysqlimport db_name file_1, file_2, ...

    

For example, I acclimated the afterward command to amount data aback into the book table.

    

Remember the data was dumped beforehand by the mysqldump command with the --tab option.

    

 

    
mysqlinmysqlimport Library Libraryook.txt

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