Learning the Table Creation Syntax

The table creation command requires

• Name of the table

• Names of fields

• Definitions for each field

The generic table creation syntax is

CREATE TABLE table_name (column_name column_type);

The table name is up to you of course, but should be a name that reflects the usage of the table. For example, if you have a table that holds the inventory of a grocery store, you wouldn't name the table s. You would probably name it something like grocery_inventory. Similarly, the field names you select should be as concise as possible and relevant to the function they serve and the data they hold. For example, you might call a field holding the name of an item item_name, not n.

This example creates a generic grocery_inventory table with fields for ID, name, description, price, and quantity:

mysql> CREATE TABLE grocery_inventory (    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,    -> item_name VARCHAR (50) NOT NULL,    -> item_desc TEXT,    -> item_price FLOAT NOT NULL,    -> curr_qty INT NOT NULL    -> ); Query OK, 0 rows affected (0.02 sec)

By the Way

The id field is defined as a primary key. You will learn more about keys in later chapters, in the context of creating specific tables as parts of sample applications. By using auto_increment as an attribute of the field, you are telling MySQL to go ahead and add the next available number to the id field for you.

The MySQL server responds with Query OK each time a command, regardless of type, is successful. Otherwise, an error message is displayed, telling you where your query went awry.