Microsoft Word - Sam's Teach Yourself MySQL in 21 Days - SAMS.doc

3. bin/mysqladmin ping


4. Start the MySQL monitor program.


5. bin/mysql –p Meet_A_Geek
   This command will start the Meet_A_Geek database as the active database. Of course,
   you will be prompted for the password.


6. From the command line, type the following command:


7. SELECT * INTO OUTFILE '/home/mark/Orders.txt'


8. FIELDS


9. TERMINATED BY = ','


10. FROM Orders


11. WHERE Order_Date >= '2000-01-01'
    After you press Return, your file will be created. This statement is just like a regular SELECT statement
    except that instead of the output going to the screen, it is redirected to the file that you have named.
    This means that you can do advanced queries using JOINs and multiple tables. This feature can also
    be used as a report generator. For example, you could combine some of the methods discussed in this
    chapter to produce some very interesting reports. Try one:


12. Create a text file called Report_G.rpt in the mysql directory and enter in the
    following lines:


13. USE Meet_A_Geek;


14. INSERT INTO Customers (Customer_ID, Last_Name, First_Name)


15. VALUES (NULL, "Kinnard", "Vicky");


16. INSERT INTO Customers (Customer_ID, Last_Name, First_Name)


17. VALUES (NULL, "Kinnard", "Steven");


18. INSERT INTO Customers (Customer_ID, Last_Name, First_Name)


19. VALUES (NULL, "Brown", "Sam");


20. SELECT Last_Name INTO OUTFILE '/home/mark/Report.rpt'


21. FROM Customers


22. WHERE Customer_ID > 1;


23. Next, make sure the mysql daemon is running and you are in the mysql
    directory.


24. Enter the following command:


25. bin/mysql < Report_G.rpt


26. Check the file that you named as the out file. It should have all the last names of
    the customers you have entered in the Customers table.

As you can see, you can combine the import/export methods you learned today to help generate

reports.

Summary

Today, you have learned why databases need to export and import data. Information is like a river, it is
always moving, flowing from one source to the next. Along the way, it can carry important pieces of
information that may need to be shared.
MySQL has many utilities to import as well as export data. You learned how to use the mysqlimport
utility to bring in data in all different types of formats. You also learned how to use its MySQL monitor
counterpart—the LOAD DATA INFILE statement. You learned that this statement can be used by the
various APIs to programmatically LOAD DATA into the database, and you learned about the mysql
command's ability to process a file with the redirection symbol.

You then applied your new-found knowledge by converting an Access table into a text file and importing

that file into MySQL. You saw how sometimes the data needed to be massaged before it would be

accepted by MySQL.

You also read about some of the various shareware utilities that exist to help make DBA's lives easier.

You learned about the many different ways MySQL can export data. You learned about the mysqldump

command, and how this utility could reverse-engineer your database, from the schema to the actual

stored records. Finally, you learned about the SELECT.......INTO OUTFILE statement. You saw

how this statement can be used to generate some very complex reports that are formatted to various

specifications.

Hopefully, you learned how data can be easily moved into and out of MySQL today.