| Now that you have created your tables and started working with them, the details of the CREATE TABLE statement are discussed next. When you created your first table (book), you used this command: CREATE TABLE book ( book_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(255), author VARCHAR(255), cond ENUM('poor','fine','good','mint'), PRIMARY KEY (book_id) );
Note the following characteristics of the CREATE TABLE command: CREATE TABLEThe CREATE TABLE command is used to create tables. In this case, a table named book will be created in the current database (as was selected via the USE statement). Column definition Within the book table, you want to create four columns. The portion of the SQL statement that defines the columns (and indexes) is enclosed in parentheses. Each statement that defines a column or an index is separated by a comma. For each column, the name of the column is followed by the column's data type and any modifiers (such as UNSIGNED or NOT NULL). For example, title VARCHAR(255) defines a column called title that can contain from 0 to 255 characters. Index definition Indexes are defined using the INDEX or KEY clause. Index definitions are slightly different than column definitions. The general form is INDEX optional_name (list, of, columns, ...) The INDEX statement (the KEY statement can also be used) asserts that you want to define an index. After this, you can give the index a name (if desired). In general, this is not needed. After the optional name is a required list of one or more columns enclosed in parentheses. Additionally, each column in the column list can be only partially indexedthat is, instead of indexing the full width of the entire column, a smaller portion can be indexed. The benefit of this approach is that the index can be updated more rapidly and less space is required for the index. The drawback is that the index is less accurate. However, in most cases, this is not a problem. The syntax for this is INDEX optional_name (column(length), ...) To index only the first 20 characters of the title of a book, you would add this index definition statement to your table definition: INDEX (title(20)). Primary key definition In MySQL, primary keys are a special form of index that is defined using the PRIMARY KEY clause. The syntax for the primary key definition is much the same as the definition for an index. The major difference is that no optional name can be used for a primary key.
|
