The INSERT Statement

Now that tables have been created, the next step is to populate them with data. Although there are a variety of ways to get data into MySQL tables, the INSERT statement is the most common method for getting data into a table. The INSERT statement uses the following general syntax;

INSERT INTO table_name (column_list) VALUES(value_list);

An insert can include a only the value(s) without a column list (if the exact column order, quantity and types are known), as follows;

INSERT INTO numbers VALUES(250);

Row contents will be as follows;

+------+
| n         |
+------+
| 250    |
+------+


To add multiple column data into a table (containing an inventory of used books) on a First Edition copy of the book A Tale of Two Cities by Charles Dickens, the following would be used;




INSERT INTO used_books (author, title, edition) VALUES ('Charles Dickens', 'A Tale of Two Cities', 'First Edition');




Row contents will be as follows;
+------------------------+-----------------------------+--------------------+
| author                     | title                               | edition             |
+------------------------+-----------------------------+--------------------+
| Charles Dickens  | A Tale of Two Cities  | First Edition      |
+------------------------+------------------------------+---------------------+

The first syntax for INSERT uses separate column and value lists following the name of the table into which the record needs to be added. The number of columns and values must be the same.The following statement uses a slightly different syntax in order to create 3 new rows in the people tablesimultaneously (with id set to 31, name set to 'Bruce, and age set to 49 for the first record, etc.);

INSERT INTO people (id,name,age) VALUES(31,'Bruce',49), (04,'Avery',21),(11,'Mackenzie',17);


Note: All column values must be enclosed in single quotes (for string and temporal data types) in the INSERT statement.




Row contents will be as follows;
+----------------+-----------+
| id  | name           | age  |
+----------------+------------+
| 31 | Bruce          | 49     |
| 04 | Avery          | 21     |
| 11 | Mackenzie   | 17   |
+----------------+------------+