Lesson 1

SQL SELECT INTO Statement

Now that you have gone through the basics of the different core SQL statements, it is time to really work with the SELECT statement. SELECT is extremely powerful, and can provide some amazing sets of information once you understand how best to use it. The main strength of the SELECT statement comes from your ability to define what information you need. What this means is that you have the ability to "cut" the database tables any number of ways.With SELECT, you can limit which columns are returned, which and how many rows are returned. In this module, you will learn more about the SELECT statement, how to use it, and how to leverage the different filtering capabilities to your advantage. You will see how to create a results set that exactly matches what you are looking for.
You can filter and control the results from your SQL statements by using two techniques. We'll cover these in the next two lessons.
The SELECT INTO statement copies data from one table and inserts it into a new table.

Writing Data into Comma-Delimited Files

You can use the

SELECT INTO OUTFILE

statement to write out the result of a query into a comma-separated values (CSV) file that can be opened by a spreadsheet or other program.
Let us export the list of artists from our music database into a CSV file. The query used to list all the artists is shown below:

mysql> USE music;
Database changed
mysql> 
SELECT artist_name, album_name FROM artist, album 
WHERE artist.artist_id=album.artist_id;

+---------------------------+-------------------------------------+
| artist_name | album_name |
+---------------------------+-------------------------------------+
| New Order | Retro - John McCready FAN |
| New Order | Substance (Disc 2) |
| New Order | Retro - Miranda Sawyer POP |
| New Order | Retro - New Order / Bobby Gillespie LIVE |
| New Order | Power, Corruption & Lies |
| New Order | Substance 1987 (Disc 1) |
| New Order | Brotherhood |
| Nick Cave & The Bad Seeds | Let Love In |
| Miles Davis | Live Around The World |
| Miles Davis | In A Silent Way |
| The Rolling Stones | Exile On Main Street |
| The Stone Roses | Second Coming |
| Kylie Minogue | Light Years |
+---------------------------+-------------------------------------+
13 rows in set (0.10 sec)

We can change this SELECT query slightly to write this data into an output file as commaseparated values:

mysql> 
SELECT artist_name, album_name 
FROM artist, album 
WHERE artist.artist_id=album.artist_id
INTO OUTFILE '/tmp/artists_and_albums.csv' FIELDS TERMINATED BY ',';
Query OK, 13 rows affected (0.02 sec)

Ad SQL Guide

Here, we have saved the results into the file artists_and_albums.csv in the /tmp directory;
the MySQL server must be able to write to the directory that you specify. On a Windows system, specify a path such as C:\artists_and_albums.csv instead. If you omit the FIELDS