This causes an index to be built on the column and disallows duplicate IDs. The other
two columns are Name and Price.
Name is a variable-length character string that may be up to 32 characters long. Price is
a floating-point number with six digits before the decimal point and two digits after.
That's a perfect setup for money.
Next, we will need to put some items in the table. Since we're only creating a demo, we'll
fill in some items we might expect in a supermarket along with some dummy prices. To
do this we'll use the INSERT statement. Listing 17.2 is an example of this procedure.
Each SQL statement ends with a semicolon, much as in PHP. We're telling the MySQL
server that we want to insert a row into the catalog table and we'll be supplying only the
name and price. Since we're leaving out ID, MySQL creates one. This is due to our
defining the column as AUTO_INCREMENT. The VALUES keyword lets the server know
we are about to send the values we promised earlier in the command. Notice the use of
single quotes to surround text, as is standard in SQL.
Listing 17.1 Creating Catalog Table
CREATE TABLE catalog
(
ID INT(11) NOT NULL AUTO_INCREMENT,
Name VARCHAR(32),
Price FLOAT(6,2),
PRIMARY KEY (ID)
);
Listing 17.2 Inserting Data into Catalog Table
INSERT INTO catalog (Name, Price) VALUES (Toothbrush', 1.79); INSERT INTO catalog (Name, Price) VALUES (Comb', 0.95);
INSERT INTO catalog (Name, Price) VALUES (Toothpaste', 5.39); INSERT INTO catalog (Name, Price) VALUES (Dental Floss',
3.50);
INSERT INTO catalog (Name, Price) VALUES (Shampoo', 2.50); INSERT INTO catalog (Name, Price) VALUES (Conditioner',
3.15);
INSERT INTO catalog (Name, Price) VALUES (Deodorant', 1.50); INSERT INTO catalog (Name, Price) VALUES (Hair Gel',
6.25);
INSERT INTO catalog (Name, Price) VALUES (Razor Blades', 2.99); INSERT INTO catalog (Name, Price) VALUES (Brush', 1.15);