You’ll accomplish this task via the MySQL CLI. You can insert data into a database via a number of methods including importing from a file or inserting through a program or script. Those topics are covered elsewhere in this book. For now, the MySQL CLI will work because you are only going to add a few records. The basic syntax for an insert is as follows: INSERT [ low_priority | delayed ] [ ignore ] [ into ]
< tablename>
[ ( < columnname>, ...) ] VALUES (< insert expression>,) Cross-Other syntax for the insert statement is discussed in Chapter 9 and in Appendix A.
Reference
084932-4 ch06.F 5/29/02 3:39 PM Page 93
Chapter 6 ✦ Starting MySQL
93
The low_priority keyword delays the insert until no other clients are reading from the table. Use of the low_priority keyword is not recommended for MyISAM
table types because simultaneous inserts are not possible in such usage.
Use the delayed keyword if some clients cannot wait for an insert to complete.
This keyword only works with ISAM and MyISAM table types. The client receives an OK for the statement immediately, and the insert is run when the table is not in use by another thread.
Specifying the ignore keyword causes duplicate rows to be ignored and not entered when a value collides with a primary key or unique value. If this keyword is not specified, an error occurs and the insert is aborted. It is important to note that records will be inserted up to the record where the collision occurred. For example, if you import a number of records and a collision occurs at record number 432, all records up to number 432 will be inserted into the database.
The into keyword is not required, though it can be useful for improving readability.
Note
If you don’t want to insert values into all columns in a table, specify the column names in parentheses, separated by commas, before you type the required VALUE
keyword.
You can now insert a few rows of data into the example database; the SQL for the insert is as follows:
INSERT into product VALUES (NULL, “8 inch Mirror Ball”, 48, 14.95);
INSERT into product VALUES (NULL, “AM100 4 port Mixer”, 12, 48.95);
INSERT into product VALUES (NULL, “FA1201 1200 watt Amplifier”, 4, 149.95);
The sample data just given shows the most common type of insert. Notice that you placed the keyword NULL in the first value. Recall that the first column in the table definition is product_id, which is an auto-incrementing field; therefore, it is not acceptable to enter a value for this field. The NULL keyword is used as a placeholder in this example and will be substituted automatically with the correct value by MySQL.
Selecting data
Because selecting is at least as common as inserting, I’ll jump ahead slightly and use a simple SELECT statement. Figure 6-6 shows the three records you just entered.
084932-4 ch06.F 5/29/02 3:39 PM Page 94
94
Part I ✦ Getting Started
Figure 6-6: Sample output of a basic SELECT statement Cross-For the full syntax of the SELECT statement, see Chapter 9.
Reference
Since I’m looking to jumpstart your usage of MySQL, the basic syntax for the SELECT statement is as follows:
SELECT < select phrase> [ FROM < table(s)> [ WHERE < where phrase> ]
[ GROUP BY < group-by clause> ] [ORDER BY < order-by clause> ]
[ LIMIT # of rows ] ]
The example above selects all columns and rows from the product table.
Use of the where clause modifies the select statement to look only for rows that match the < where phrase>. For example, if you wanted to look for products that had a price less than $30.00 you could issue the statement in Figure 6-7.
Figure 6-7: Selecting records that show a price less than $30.00.
084932-4 ch06.F 5/29/02 3:39 PM Page 95
Chapter 6 ✦ Starting MySQL
95
The where clause is extremely useful (and almost constantly in use) whenever you work with data in a MySQL database.
The group by clause modifies the results of the select by ordering the columns by similar data. The order by clause arranges the resulting data from a select statement into the order specified by the order-by clause. For example, to select all the products in order by price (from lowest to highest), you issue the statement like the following (its results are shown in Figure 6-8):
select * from product order by price;
Figure 6-8: Selecting records in a certain order
Note
It just happens that the price is in order of product ID for the example database.
Such a neat result rarely happens with live data.
If you had a large amount of data in a table and you needed to look at the first n rows of data, you could do so by adding the limit clause onto the end of the SELECT statement. An example is shown in Figure 6-9.
Figure 6-9: Limiting rows of a SELECT statement
084932-4 ch06.F 5/29/02 3:39 PM Page 96
96
Part I ✦ Getting Started
Cross-
There are many more options for the SELECT statement. Those options, along Reference
with additional coverage of the where, group_by, order_by, and limit clauses, appear in greater detail in Chapter 9 and in Appendix A.
Updating data
Updating your data is just as important as inserting the original data — if not more so. Commonly used syntax for the UPDATE statement looks like this: UPDATE < tablename> SET < columnname> = < value> ( , < columnname>