Databases Reference
In-Depth Information
TERMINATED BY
clause, the server will use tabs as the default separator between the data
values.
You can view the contents of the file
artists_and_albums.csv
in a text editor, or import
it into a spreadsheet program:
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
Notice how the comma in
Power,
Corruption
&
Lies
has been automatically escaped
with a backslash to distinguish it from the separator. Spreadsheet programs understand
this and remove the backslash when importing the file.
Creating Tables with Queries
You can create a table or easily create a copy of a table using a query. This is useful
when you want to build a new database using existing data—for example, you might
want to copy across a list of countries—or when you want to reorganize data for some
reason. Data reorganization is common for producing reports, merging data from two
or more tables, and redesigning on the fly. This short section shows you how it's done.
From MySQL 4.1 onward, you can easily duplicate the structure of a table using a
variant of the
CREATE TABLE
syntax:
mysql>
CREATE TABLE artist_2 LIKE artist;
Query OK, 0 rows affected (0.24 sec)
mysql>
DESCRIBE artist_2;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | | PRI | 0 | |
| artist_name | char(128) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.09 sec)
mysql>
SELECT * FROM artist_2;
Empty set (0.30 sec)
