This MySQL 4 Windows Tutorial shows you how to create and verify MySQL tables.

Creating MySQL Tables

This tutorial continues with our case study using MySQL to provide database management services for a small company that makes specialty cookies. We highly recommend that you read our article Introduction to MySQL Tables before and sometimes during this tutorial.

Step 1 Start your MySQL system and logon as the user samuel. Then list your databases and activate the cookies database as shown in Figure 1.

Creating MySQL tables tutorial Figure 1
Samuel connects to and verifies his database.

Step 2 The next step is create the Customer table. I pulled down on the scroll bar at the right margin of the window to focus on the present statement. The previous statements may be reviewed by pulling up on the scroll bar. Create this table using the following statement:
CREATE TABLE Customer (Custnum INT NOT NULL, Custname VARCHAR(18), Custtype CHAR(1), Custaddr VARCHAR(15), Zipcode CHAR(5), Phone CHAR(8)); The results are shown in Figure 2.

Creating MySQL tables tutorial Figure 2
Creating the Customer table.

Step 3 Verify the table creation with the SHOW TABLES; statement as as shown in Figure 3.

Creating MySQL tables tutorial Figure 3
Verifying the table creation.

Step 4 We have verified the table name but we must also verify that the table was created correctly. To do so enter the DESCRIBE Customer; command as shown in Figure 4.

Creating MySQL tables tutorial Figure 4
Check that the table was created correctly.

Step 5 If you see that your Customer table has one or more errors you must recreate it. The present tutorial does not describe the many changes that you can make to a table structure such as modifying, adding, or removing columns. At this point the simplest thing to do is enter the DROP TABLE Customer; to delete the table. Then redo Step 2 being more careful.

Step 6 Create the Purchase table using the following statement:
CREATE TABLE Purchase (Purchnum INT, Prodnum INT NOT NULL, Custnum INT NOT NULL, Purchdat DATE, Readydat DATE, Kilos FLOAT); The results are shown in Figure 5.

Creating MySQL tables tutorial Figure 5
This is the Purchase table.

Exercises. Keep up with your own case study. Create two tables. Make sure that they are linked by a common field, as the Customer and Purchase tables were linked by the Custname field.