34,333
edits
Changes
New page: A database would be of little use if it did not contain any data. It is, therefore, not surprising that the ''INSERT'' statement is one of the most commonly used statements. In this chapt...
A database would be of little use if it did not contain any data. It is, therefore, not surprising that the ''INSERT'' statement is one of the most commonly used statements.
In this chapter of [[MySQL Essentials]] we will explain how to insert data into database tables using the SQL ''INSERT'' statement.
== The Basics of Data Insertion ==
The purpose of the SQL ''INSERT'' statement is to add new rows of data to a specified database table. Using the ''INSERT'' statement, it is possible to insert a complete row, a partial rwo, multiple rows or rows generated as the result of a database query. In this chapter we will look at each of these techniques in turn.
== Inserting a Complete Row ==
In order to add a single, complete row to a table the ''INSERT'' statement must be provided with the name of the table which the row is to be added, together with the column names and associated values to be added. The ''INSERT'' command first takes a comma separated list of column names enclosed in parentheses. This is followed by the ''VALUES'' keyword and then a comman separated list of values for each column. Note that the values must be listed in the same order as the column names. For example:
<pre>
INSERT INTO products(
product_id,
product_name,
product_description,
product_location,
product_quantity)
VALUES(
NULL,
'CD-RW Model 4543',
'CD Writer',
'Shelf 4B',
10
};
</pre>
Note that the product_id is specified as NULL. This is because the product_id in our imaginmary table is set to AUTO_INCREMENT so we do not specifically set this. Instead, the MySQL database engine will automatically create a new value for us.
A less safe way to add records to a table is to provide only the values. Whilst this approach works it is vital that the values be specified in the ''exact order'' in which the columns were specified when the table was created. Getting the wrong order will, at the very least result in a an error message (if the data types do not match the columns) and at the worst result in data going into the wrong columns.
In this chapter of [[MySQL Essentials]] we will explain how to insert data into database tables using the SQL ''INSERT'' statement.
== The Basics of Data Insertion ==
The purpose of the SQL ''INSERT'' statement is to add new rows of data to a specified database table. Using the ''INSERT'' statement, it is possible to insert a complete row, a partial rwo, multiple rows or rows generated as the result of a database query. In this chapter we will look at each of these techniques in turn.
== Inserting a Complete Row ==
In order to add a single, complete row to a table the ''INSERT'' statement must be provided with the name of the table which the row is to be added, together with the column names and associated values to be added. The ''INSERT'' command first takes a comma separated list of column names enclosed in parentheses. This is followed by the ''VALUES'' keyword and then a comman separated list of values for each column. Note that the values must be listed in the same order as the column names. For example:
<pre>
INSERT INTO products(
product_id,
product_name,
product_description,
product_location,
product_quantity)
VALUES(
NULL,
'CD-RW Model 4543',
'CD Writer',
'Shelf 4B',
10
};
</pre>
Note that the product_id is specified as NULL. This is because the product_id in our imaginmary table is set to AUTO_INCREMENT so we do not specifically set this. Instead, the MySQL database engine will automatically create a new value for us.
A less safe way to add records to a table is to provide only the values. Whilst this approach works it is vital that the values be specified in the ''exact order'' in which the columns were specified when the table was created. Getting the wrong order will, at the very least result in a an error message (if the data types do not match the columns) and at the worst result in data going into the wrong columns.
