MySQL and mSQL

Managing Data

The first thing you do with a newly created table is add data to it. With the data in place, you may want to make changes and eventually remove it.

Inserts

Adding data to a table is one of the more straightforward concepts in SQL. You have already seen several examples of it in this book. Both MySQL and mSQL support the standard SQL INSERT syntax:

INSERT INTO table_name (column1, column2, ..., columnN)
VALUES (value1, value2, ..., valueN)

When inserting data into numeric fields, you can insert the value as is; for all other fields, you must wrap them in single quotes. For example, to insert a row of data into a table of addresses, you might issue the following command:

INSERT INTO addresses (name, address, city, state, phone, age)
VALUES('Irving Forbush', '123 Mockingbird Lane', 'Corbin', 'KY',
       '[phone]', 26)

In addition, the escape character—`\' by default—enables you to escape single quotes and other literal instances of the escape character:

# Insert info for the directory Stacie's Directory which
# is in c:\Personal\Stacie
INSERT INTO files (description, location) 
VALUES ('Stacie\'s Directory', 'C:\\Personal\\Stacie')

MySQL allows you to leave out the column names as long as you specify a value for every single column in the table in the exact same order they were specified in the table’s CREATE call. If you want to use the default values for a column, however, you must specify the names of the columns for which you intend to insert nondefault data. If you do ...