The subset of SQL that enables you to insert data and to update and delete existing records in a table is known as the Data Manipulation Language.
The INSERT statement adds a new row of data to a table. At its simplest, INSERT is followed by a table name, the keyword VALUES, and a list of values in parentheses that correspond to each column in the table in turn.
The products table contains four columnsproduct_code, name, price, and weightso you can insert a new product using the following statement:
mysql> INSERT INTO products -> VALUES ('NEWPROD', 'A new product', 19.99, 3.5); Query OK, 1 row affected (0.02 sec)
The response from MySQL indicates that a row has been successfully inserted.
The second INSERT format requires you to specify the name of each column before giving the valuesthis is known as a full insert. The following statement works the same way as the previous example:
mysql> INSERT INTO products (product_code, name, price, weight) -> VALUES ('NEWPROD2', 'Another new product', 29.99, 5.2); Query OK, 1 row affected (0.02 sec)
This might seem a little long-winded, but it means that you do not need to give a value for every column in the table, or list the values in the correct order. One big advantage is that if the table structure changes, the second example in this section will still work, whereas the first example will fail because every column in the table is not given a value.
Notice that the second example used a different product_code value than the first. This is because product_code is a PRIMARY KEY column and can contain only unique values. If you try to perform an insert that violates a database constraint, you will see an error like the following:
mysql> INSERT INTO products -> VALUES ('NEWPROD', 'A new product', 19.99, 3.5); ERROR 1062 (23000): Duplicate entry 'NEWPROD' for key 1
Default Column Values
When you use a full insert, any columns that are not listed have their default value inserted for the new data row, or NULL if there is no default. A column that has the AUTO_INCREMENT property is assigned the next number in sequence by default.
If you explicitly want to use a column's default value in an INSERT statement, use the DEFAULT keyword as its value. In the following examples, you insert new record into the orders table, where the order_id column has the AUTO_INCREMENT property:
mysql> INSERT INTO orders -> (order_id, customer_code, order_date) -> VALUES (DEFAULT, 'MUSGRP', '2006-04-01'); Query OK, 1 row affected (0.01 sec)
mysql> INSERT INTO orders (customer_code, order_date) -> VALUES ('MUSGRP', '2006-04-01'); Query OK, 1 row affected (0.00 sec)
Both formats of the INSERT statement perform the same job. You can then query the orders table to verify that the two records were inserted with sequential order_id values.
Inserting Multiple Rows
You can insert several rows in a single INSERT statement by supplying multiple lists of values. Each list must be enclosed in parentheses and separated by a comma.
The following statement inserts three new rows into the customers table:
mysql> INSERT INTO customers (customer_code, name) -> VALUES -> ('ACME', 'ACME Enterprises'), -> ('BLOGGS', 'Bloggs and Company'), -> ('CORPLTD', 'Corporation Limited'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0
If one of the rows in a multiple INSERT statement causes an error (for instance, a duplicate value in a UNIQUE column), the insert will fail. The values are inserted in sequence, however, so rows before the one that caused the error will have already been inserted into the database.
Atomic transactionsdatabase operations that all take place at the same timeare discussed in the "Transactions in MySQL" section later in this lesson.
Using a Query to Insert Rows
Instead of supplying a list of values as the data for a new row, you can use the result of a query to insert one or more rows into a table.
The format for this type of INSERT command uses a SELECT statement in place of VALUES. The requirements of an INSERT must hold true: The query must return the correct number of columns for the insert, and the insert must not violate any column constraints.
The following query inserts a new contact for each customer named Joe Soap. This is a trivial example, but it demonstrates how this feature can be used with powerful results.
mysql> INSERT INTO customer_contacts -> (customer_code, first_name, last_name) -> SELECT customer_code, 'Joe', 'Soap' -> FROM customers; Query OK, 6 rows affected (0.00 sec) Records: 6 Duplicates: 0 Warnings: 0
A new row in customer_contacts is inserted for each row in the customers table. The customer_code column is populated from the customer_code value in customers, whereas first_name and last_name are given static values; these are returned as is by the query and, therefore, are used as fixed values in the insert.
The contact_id column in customer_contacts is an AUTO_INCREMENT column, so it is automatically assigned sequential numbers for each row inserted by this statement.