Inserting Data - Learning MySQL and MariaDB - page 113

scientific_name: This family includes ducks, geese and swans.
brief_description: NULL
order_id: 103
The first row is fine; we entered it correctly, before. But because MySQL didn't receive a
good value for the family_id column for the row we just entered, it ignored what we
gave it and automatically set the column to 101 — the default value based on
AUTO_INCREMENT . It took the description text that was intended for
brief_description column and put that in the scientific_name column. It put
the NULL we meant for the family_id column and put it in the
brief_description column. This row needs to be fixed or deleted. Let's delete it
and try again. We'll usethe DELETE statement like this:
DELETE FROM bird_families
WHERE family_id = 101 ;
This will delete only one row: the one where the family_id equals 101. Be careful with
the DELETE statement. There's no UNDO statement, per se, when working with the data
like this. If you don't include the WHERE clause,you will delete all of the data in the table.
For this table, which has only two rows of data, it's not a problem to re-enter the data. But
on a server with thousands of rows of data, you could lose plenty of data — permanently,
if you don't have a backup copy. Even if you do have a backup of the data, you're not go-
ing to be able to restore the data quickly or easily. So be careful with the DELETE state-
ment and always use a WHERE clause that limits greatly the data that's to be deleted.
Let's re-enter the data for the duck family, Anatidae , but this time we'll try a different syn-
tax for the INSERT statement so that we don't have to give data for all of the columns
and so that we can give data in a different order from how it'sstructured in the table:
INSERT INTO bird_families
( scientific_name , order_id , brief_description )
VALUES ( 'Anatidae' , 103 , "This family includes ducks, geese and
swans." );
To let us give only three columns in this SQL statement, and in a different order, we put
the names of the columns in parentheses before the set of values. Listing the names of the
columns is optional, provided data is in the correct format for all of the columns and in or-
der. Because we are not doing that with this SQL statement, we had to list the columns for
which we are giving data, matching the order that the data is given in the VALUES clause
in the set of values and in parentheses. Basically, we're telling the server what each value
represents; we're mapping the data to the correct columns in the table. Again, for the
columns that we don't provide data or don't name in the SQL statement, the server will