Creating Tables

Once you have a database, you can begin making the tables, also using the CREATE term:

CREATE TABLE tablename (
column1name description,
column2name description...
)

As you can see from the preceding syntax, after naming the table, you define each columnin orderwithin parentheses. Each column and description should be separated by a comma. Should you choose to create indexes at this time, you can add those at the end of the creation statement (you can add indexes at a later time, as well).

CREATE TABLE tablename (
column1name description,
column2name description,
indextype (columns)
)

If you want to name any of the indexes, change that part of the query to

indexname (columns)

To specify the storage engine when you define a table, add a clause to the end of the creation statement:

CREATE TABLE tablename (
column1name description,
column2name description...
) ENGINE = INNODB

(Prior to MySQL 4.0.18, you have to use the word TYPE instead of ENGINE). If you don't specify a storage engine when creating tables, MySQL will use the default type (InnoDB on Windows, MyISAM otherwise).

To specify a character set or collation for the entire table, add another clause to the end of the CREATE query:

CREATE TABLE tablename (
column1name description,
column2name description...
) ENGINE = MyISAM CHARACTER SET setname COLLATE collationname

I'll now create the four tables that compose the accounting database.

To create tables:

1.	Access the `mysql` client and select the accounting database (Figure 4.8). `USE accounting;` It'll be easier to create the tables if you select the database first. You will need to be logged in as a user that has permission to create tables on this database. Figure 4.8. The first steps I take are to log in to mysql and select the accounting database.

2.	Create the `invoices` table (Figure 4.9). CREATE TABLE invoices ( invoice_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, client_id SMALLINT(3) UNSIGNED NOT NULL, invoice_date TIMESTAMP NOT NULL, invoice_amount DECIMAL(10,2) UNSIGNED NOT NULL, invoice_description TINYTEXT NOT NULL, date_invoice_paid DATE, PRIMARY KEY (invoice_id), INDEX (client_id), INDEX (invoice_date), INDEX (invoice_amount), INDEX (date_invoice_paid) ); This step takes the information about the `invoices` table I established earlier and integrates that within the `CREATE` table syntax. The order in which you enter the columns here will dictate the order the columns appear in the table. You'll also need to specify any indexes last so that they are enacted after the column itself has been created. Figure 4.9. The mysql client allows you to enter commands over multiple lines, making long SQL statements more readable. Because the `mysql` client will not run a query until it encounters a semicolon, you can enter statements over multiple lines as I do in Figure 4.9.
3.	Create the `clients` table (Figure 4.10). CREATE TABLE clients ( client_id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, client_name VARCHAR(40) NOT NULL, client_street VARCHAR(80) NOT NULL, client_city VARCHAR(30) NOT NULL, client_state CHAR(2) NOT NULL, client_zip MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL, client_phone VARCHAR(14), contact_name VARCHAR(40), contact_email VARCHAR(60), PRIMARY KEY (client_id), INDEX (client_name) ); This table has the most columns but only two indexes. Figure 4.10. MySQL will report if a command worked, using the Query OK statement as indicated here.

4.	Create the `expenses` table (Figure 4.11). CREATE TABLE expenses ( expense_id SMALLINT(4) UNSIGNED NOT NULL AUTO_INCREMENT, expense_category_id TINYINT(3) UNSIGNED NOT NULL, expense_amount DECIMAL(10,2) UNSIGNED NOT NULL, expense_description TINYTEXT NOT NULL, expense_date TIMESTAMP NOT NULL, PRIMARY KEY (expense_id), INDEX (expense_category_id), INDEX (expense_amount), INDEX (expense_date) ); Figure 4.11. Creating the third table.
5.	Finally, create the expense_categories table (Figure 4.12). CREATE TABLE expense_categories ( expense_category_id TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT, expense_category VARCHAR(30) NOT NULL, PRIMARY KEY (expense_category_id), UNIQUE (expense_category) ); This is the simplest of the four tables, with only two columns and two indexes. Figure 4.12. Creating the fourth and final table. [View full size image]

6.	Confirm the existence of the tables (Figure 4.13). SHOW TABLES; SHOW COLUMNS FROM invoices; The `SHOW` command can reveal the tables in a database or the column names and types in a table. Figure 4.13. You can confirm the existence and structure of databases and tables using the SHOW command. Don't be alarmed by the results of the SHOW COLUMNS command. MySQL has its own way of describing a table that might differ from how you created it. [View full size image]

Tips

DESCRIBE tablename, which you might see in other resources, is the same statement as SHOW COLUMNS FROM tablename.
You can also run a SHOW CREATE TABLE tablename query to see how an existing table was created.
If you run a SHOW CREATE TABLE tablename query after making a table, you'll see how MySQL implements your creation. Doing so will help you see how MySQL may change column types for its own performance purposes.

Creating Tables

Figure 4.8. The first steps I take are to log in to mysql and select the accounting database.

Figure 4.9. The mysql client allows you to enter commands over multiple lines, making long SQL statements more readable.

Figure 4.10. MySQL will report if a command worked, using the Query OK statement as indicated here.

Figure 4.11. Creating the third table.

Figure 4.12. Creating the fourth and final table.

Figure 4.13. You can confirm the existence and structure of databases and tables using the SHOW command. Don't be alarmed by the results of the SHOW COLUMNS command. MySQL has its own way of describing a table that might differ from how you created it.