How to add one row at a time to a database?

You use the INSERT query to add a row to a database. This query tells MySQL which table to add the row to and what the values are for the fields in the row.
The general form of the query is

INSERT INTO tablename (columnname, columnname,...,columnname)
VALUES (value, value,...,value)

The following rules apply to the INSERT query:

• Values must be listed in the same order in which the column names are listed. The first value in the value list is inserted into the column that’s named first in the column list; the second value in the value list is inserted into the column that’s named second; and so on.
• A partial column list is allowed. You don’t need to list all the columns. Columns that are not listed are given their default value or left blank if no default value is defined.
• A column list is not required. If you’re entering values for all the columns, you don’t need to list the columns at all. If no columns are listed, MySQL will look for values for all the columns, in the order in which they appear in the table.
• The column list and value list must be the same length. If the list of columns is longer or shorter than the list of values, you get an error message like this: Column count doesn’t match value count.

The following INSERT query adds a row to the Member table:

INSERT INTO Member (loginName,createDate,password,lastName, street,city,state,zip,email,phone,fax)
VALUES (“bigguy”,”2001-Dec-2”,”secret”,”Smith”, “1234 Happy St”,”Las Vegas”,”NV”,”88888”, “gsmith@GSmithCompany.com”,”(555) 555-5555”,””)

Notice that firstName is not listed in the column name list. No value is entered into the firstName field. If firstName were defined as NOT NULL, MySQL would not allow this. Also, if the definition for firstName included a default, the default value would be entered, but because it doesn’t, the field is left empty. Notice that the value stored for fax is an empty string. To look at the data that you entered and ensure that you entered it correctly, use an SQL query that retrieves data from the database. I describe these SQL queries in detail in “Retrieving information,” later in this chapter. In brief, the following query retrieves all the data in the Member table:

SELECT * FROM Member