Using the INSERT Command
After you have created some tables, you'll use the SQL command INSERT for adding new records to these tables. The basic syntax of INSERT is
INSERT INTO table_name (column list) VALUES (column values);
Within the parenthetical list of values, you must enclose strings within quotation marks. The SQL standard is single quotes, but MySQL enables the usage of either single or double quotes. Remember to escape the type of quotation mark used, if it's within the string itself.
By the Way
Integers do not require quotation marks around them.
Here is an example of a string where escaping is necessary:
O'Connor said "Boo"
If you enclose your strings in double quotes, the INSERT statement would look like this:
INSERT INTO table_name (column_name) VALUES ("O'Connor said \"Boo\"");
If you enclose your strings in single quotes instead, the INSERT statement would look like this:
INSERT INTO table_name (column_name) VALUES ('O\'Connor said "Boo"');
A Closer Look at INSERT
Besides the table name, there are two main parts of the INSERT statement: the column list and the value list. Only the value list is actually required, but if you omit the column list, you must specifically provide for each column in your value listin the exact order.
Using the grocery_inventory table as an example, you have five fields: id, item_name, item_desc, item_price, and curr_qty. To insert a complete record, you could use either of these statements:
Give both of them a try and see what happens. You should get results like this:
mysql> insert into grocery_inventory -> (id, item_name, item_desc, item_price, curr_qty) -> values -> (1, 'Apples', 'Beautiful, ripe apples.', 0.25, 1000); Query OK, 1 row affected (0.01 sec) mysql> insert into grocery_inventory values (2, 'Bunches of Grapes', -> 'Seedless grapes.', 2.99, 500); Query OK, 1 row affected (0.01 sec)
Now for some more interesting methods of using INSERT. Because id is an auto-incrementing integer, you don't have to put it in your value list. However, if there's a value you specifically don't want to list (such as id), you then must list the remaining columns in use. For example, the following statement does not list the columns and also does not give a value for id, and it will produce an error:
mysql> insert into grocery_inventory values -> ('Bottled Water (6-pack)', '500ml spring water.', 2.29, 250); ERROR 1136: Column count doesn't match value count at row 1
Because you didn't list any columns, MySQL expects all of them to be in the value list, causing an error on the previous statement. If the goal was to let MySQL do the work for you by auto-incrementing the id field, you could use either of these statements:
Go ahead and pick one to use so that your grocery_inventory table has three records in total. It makes no different to MySQL, but as with everything based on user preferences, be consistent in your application development. Consistent structures will be easier for you to debug later, because you'll know what to expect.