Basic SQL - Learning MySQL - page 163

INSERT Basics
Inserting data typically occurs in two situations: when you bulk-load in a large batch
as you create your database, and when you add data on an ad hoc basis as you use the
database. In MySQL, there are different optimizations built into the server for each
situation and, importantly, different SQL syntaxes available to make it easy for you to
work with the server in both cases. We explain a basic INSERT syntax in this section,
and show you examples of how to use it for bulk and single record insertion.
Let's start with the basic task of inserting one new row into the artist table. To do this,
you need to understand the table's structure. As we explained in Chapter 4 in “The
Music Database,” you can discover this with the SHOW COLUMNS statement:
mysql> SHOW COLUMNS FROM artist;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| artist_id | smallint(5) | NO | PRI | 0 | |
| artist_name | char(128) | NO | | | |
+-------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
This tells you that the two columns occur in the order artist_id and then
artist_name , and you need to know this for the basic syntax we're about to use.
Our new row is for a new artist, “Barry Adamson.” But what artist_id value do we
give him? You might recall that we already have six artists, so we should probably use
7. You can check this with:
mysql> SELECT MAX(artist_id) FROM artist;
+----------------+
| MAX(artist_id) |
+----------------+
| 6 |
+----------------+
1 row in set (0.04 sec)
The MAX( ) function is an aggregate function, and it tells you the maximum value for
the column supplied as a parameter. This is a little cleaner than SELECT artist_id FROM
artist , which prints out all rows and requires you to inspect the rows to find the max-
imum value; adding an ORDER BY makes it easier. Using MAX( ) is also much simpler than
SELECT artist_id FROM artist ORDER BY artist_id DESC LIMIT 1 , which also returns
the correct answer. You'll learn more about the AUTO_INCREMENT shortcut to automati-
cally assign the next available identifier in Chapter 6, and about aggregate functions in
Chapter 7.
We're now ready to insert the row. Here's what you type:
mysql> INSERT INTO artist VALUES (7, "Barry Adamson");
Query OK, 1 row affected (0.00 sec)