Creating Your Web Database CHAPTER 8 195 You can run an existing SQL file, such as one loaded from the CD-ROM, through MySQL by typing mysql -h host -u bookorama books -p | Creating Your Web Database 195 Chapter 8 You can run an existing SQL file such as one loaded from the CD-ROM through MySQL by typing mysql -h host -u bookorama books -p Using file redirection is pretty handy for this because it means that you can edit your SQL in the text editor of your choice before executing it. Listing SQL to Create the Tables for Book-O-Rama create table customers customerid int unsigned not null auto_increment primary key name char 30 not null address char 40 not null city char 20 not null create table orders orderid int unsigned not null auto_increment primary key customerid int unsigned not null amount float 6 2 date date not null create table books isbn char 13 not null primary key author char 30 title char 60 price float 4 2 create table order_items orderid int unsigned not null isbn char 13 not null quantity tinyint unsigned primary key orderid isbn create table book_reviews isbn char 13 not null primary key review text 8 Creating Your Web Database 196 Using MySQL Part II Each of the tables is created by a separate CREATE TABLE statement. You see that we ve created each of the tables in the schema with the columns that we designed in the last chapter. You ll see that each of the columns has a data type listed after its name. Some of the columns have other specifiers too. What the Other Keywords Mean NOT NULL means that all the rows in the table must have a value in this attribute. If it isn t specified the field can be blank NULL . AUTO_INCREMENT is a special MySQL feature you can use on integer columns. It means if we leave that field blank when inserting rows into the table MySQL will automatically generate a unique identifier value. The value will be one greater than the maximum value in the column already. You can only have one of these in each table. Columns that specify AUTO_INCREMENT must be indexed. PRIMARY KEY after a column name specifies that this column is the primary key for the table. Entries
