O'Reilly logo

Learning MySQL by Hugh E. Williams, Seyed M.M. Tahaghoghi

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

The INSERT Statement

The INSERT statement is used to add new data to tables. In this section, we explain its basic syntax and show you simple examples that add new rows to the music database. In Chapter 6, we’ll discuss how to load data from existing tables or from external data sources.

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required