Inserting Data

After your database and tables have been created, you can start populating them with data using the INSERT command. There are two formats for inserting data. With the first, you specify the columns to be used.

INSERT INTO mytable (col1, col2 ...) VALUES ('value1', 56, ...) INSERT INTO mytable (col4, col8) VALUES (8.2156, 'valueY')

Using this structure, you can add rows of records, populating only the columns that matter. The result will be that any columns not given a value will be treated as NULL (or given a default value, if one was established). However, if a column cannot have a NULL value (it was defined as NOT NULL), not specifying a value will cause an error.

The second format for inserting records is not to list the columns but to include a value for every one.

INSERT INTO mytable VALUES ('value1', NULL, 20.6, 'value3', )

If you use this second method, you must specify a value, even a NULL value, for every column. If there are six columns in the table, you must list six values. Failure to match the values or number of values to the columns (or number of columns) will also cause an error. For this reason, the first format of inserting records is generally preferable. Secondarily, if you add columns to a table at a later time, the first kind of INSERT will still work, whereas this second kind would fail.

Semicolons and Queries

In the mysql client, the semicolon is used to indicate the end of a command or query, letting mysql know when to try to execute a statement. This can be confusing, as people tend to think that the semicolon is part of the SQL query, when it is not.

For this reason, in this book I do not use a semicolon when discussing or demonstrating SQL queries in general. However, if a step expects that you are entering a query in the mysql client, then I will use a semicolon, as is required by mysql.

When running queries from a programming language or another interface like phpMyAdmin, the semicolon is most likely not necessary.

As you'll also see later in the chapter, you can terminate a query in mysql using \G instead of a semicolon. This alteration also tells mysql to return the results in a vertical listing, instead of the default horizontal table. I use this alternative command terminator where the query results are less legible in their default (horizontal) view.

MySQL also allows you to insert multiple rows at one time, separating each record from the next by a comma.

INSERT INTO mytable (col1, col4) VALUES ('valueA', 234), ('valueC', 4946), ('valueE', 2)

While you can do this with MySQL, it is not acceptable within the ANSI SQL standard (and therefore is not something that can be done with all database applications).

To insert data into a table:

1.	Insert a new row of data into the expense_categories table (Figure 5.1). Your syntax would be one of the following: INSERT INTO expense_categories (expense_category) VALUES ('Travel-Hotel'); INSERT INTO expense_categories VALUES (NULL, 'Travel-Airfare'); Since this table contains only two columns, one of which is automatically incremented, there's little difference between the two methods. Figure 5.1. Here are the two ways you can insert data into a table. The NULL value works because the expense_category_id column is defined as AUTO_INCREMENT. When MySQL sees NULL being used here, it will automatically choose the next highest integer value for that column.
2.	Insert several more values into the expense_categories table(Figure 5.2). INSERT INTO expense_categories VALUES (NULL, 'Books'), (NULL, 'Web Hosting'), (NULL, 'Computer Software'); Since MySQL allows you to insert multiple values at once, you can take advantage of this and fill up the table with records. Figure 5.2. With MySQL, I can enter as many values into a table as I want in one command.
3.	Insert a record into the clients table (Figure 5.3). Your query can be structured in many ways. Just three examples are: INSERT INTO clients VALUES (NULL, 'Acme Industries', '100 Main Street', 'Anytown', 'NY', 11111, '[phone]', 'Jane', 'Doe', 'Jane@acme_industries.com'); INSERT INTO clients (client_name, contact_first_name, contact_last_name) VALUES ('Winesburg Press', 'Sherwood', 'Anderson'); INSERT INTO clients VALUES (NULL, 'Galt on the Hill', '1000 Tuttle Drive', 'Brazilia', 'IL', 60000, NULL, NULL, NULL, '[e-mail]'); Just be absolutely certain that the number of values you have correspond to the number of columns, either in the table or named in the query. Figure 5.3. Populating the clients table.
4.	Continue Steps 2 and 3 until you've thoroughly populated the expense_categories and clients tables. At this point, because of the more complicated nature of a relational database, I am not going to add records to either the invoices or expenses tables, as these rely on information in the clients and expense_categories tables. You'll see later in the chapter how to insert records into them.

Tips

• The term INTO in INSERT statements is optional in current versions of MySQL.

• The SQL commands I used to populate my tables are available for download from the book's corresponding Web site: www.DMCInsights.com/mysql2. You don't have to use them yourself, but if you don't, your results in the rest of the chapter may differ from mine.