Login | Contact Us | Site Map | Resources

Data Manipulation

Home > MYSQL Tutorial > Inserting MYSQL Data

Inserting MYSQL Data

"Insert" is the term generally used to refer to the act of putting data into a MYSQL database table. You will need to know not only the name of the table that you will be inserting data into, but also the names and types of each column. You don't want to put letters into a column that only accepts integers, or a 75-character phrase into a column that only accepts 25 characters!

The syntax used to insert data into a MYSQL table is: INSERT INTO tablename () VALUES ();

Inside the first set of parenthesis, immediately following your table name, you will specify the name of each column that you want data to be inserted into. Inside the second set of parenthesis, immediately following the word "VALUES", you will specify the data that should be inserted into each column that was specified.

This means that if you specify 3 different columns you will need to specify 3 different values, no more, no less.

Each value should be surrounded by single quotes and separated by commas. The columns do not need to be surrounded by single quotes, but they must be separated by commas.

The order of the values inserted must match the order of the columns specified. If you list columns "A" and "B" in that order, the first value will be inserted into "A", and the second value will be inserted into "B".

Consider the following examples, which will insert data into the table that we created in a previous examples.

INSERT INTO pets (name, breed, gender, dateofbirth, description, pedigree, medicalrecord) VALUES ('Chester', 'Shiba Inu', 'Male', '09-30-2008', 'Fluffy', 'Unknown', '');

INSERT INTO pets (
    name, breed, gender, dateofbirth, description, pedigree, medicalrecord
) VALUES (
    'Tamika', 'Ferret', 'Female', '03-12-2010', 'Slim', 'Unknown', ''
);

INSERT INTO pets (
    name,
    breed,
    gender,
    dateofbirth,
    description,
    pedigree,
    medicalrecord
) VALUES (
    'Sylvester',
    'Goldfish',
    'Male',
    '01-15-2011',
    'Slippery, Orangish',
    'Unknown',
    ''
);

Each example, although they are broken down in different manners, indicate valid records that can be inserted into the table.

But wait, did we forget about the table's first column, the "id" column? I didn't! Since the id column was created as an "AUTO INCREMENT" column, that means it fills itself in with an id number when a record is inserted into the database. You shouldn't have to mess around with this type of column.

If a column is not created as an "AUTO INCREMENT" or "NOT NULL" column, and you don't have any data to store in it yet, it can be left blank by leaving it out of the columns and values that are listed when a record is inserted. For example:

INSERT INTO pets (name, description) VALUES ('Percy', 'Grayish-Brownish');

The above example will insert a perfectly valid record into the pets table with a new id number and several blank columns.

The best advice that I can leave you with is to keep a close eye on your apostrophes and commas. Some of the most common errors are caused due to misplaced or excess apostrophes and/or commas.

Note: If you need to insert an apostrophe into a column as a part of the data, escape it with a backslash (\). Example:

INSERT INTO pets (name, description) VALUES ('Spot', 'Percy\'s Friend');