Insert new records

To insert new records in an MySQL table, the INSERT INTO command is used. INSERT INTO allows you to insert one or more rows into a table you specify. Here is the syntax:

INSERT INTO table_name (column1, column2...) VALUES (value1, value2,...);

The table_name parameter specifies the table you would like to insert a row into. After the table name, a list of comma-separated column names is specified. This tells MySQL that these are the fields into which the data will be inserted. Finally, after the VALUES keyword, a comma-separated values of the corresponding columns are specified.

Here is an example. In the previous chapters we’ve created an empty table called testtable. This table contains three columns: name, surname, and year. Let’s say that we want to insert a new record. Here is how we would do that:

mysql> INSERT INTO testtable (name, surname, year) VALUES ('Amy','Goodridge','1991');
 Query OK, 1 row affected (0.01 sec)

To display the content of our table, we can use the SELECT * FROM testtable command:

mysql> SELECT * FROM testtable;
 +------+-----------+------+
 | name | surname | year |
 +------+-----------+------+
 | Amy | Goodridge | 1991 |
 +------+-----------+------+
 1 row in set (0.00 sec)

As you can see from the output above, the record was successfully added to the table.

If you specify the value of the corresponding column for all columns in the table, you don’t need to specify the column names, only their values. Here is an example:

mysql> INSERT INTO testtable VALUES ('Mark','Smith','1955');
 Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM testtable;
 +------+-----------+------+
 | name | surname | year |
 +------+-----------+------+
 | Amy | Goodridge | 1991 |
 | Mark | Smith | 1955 |
 +------+-----------+------+
 2 rows in set (0.00 sec)

Notice how we didn’t specify the column names in the command displayed above.