MySQL is the most widely used database management system. This article introduces you to MySQL tables. Read it with MySQL up and running.

Introduction To MySQL Tables

Rather than present abstract, unconnected articles that describe MySQL syntax we are going to write a series of interconnected articles that illustrate MySQL syntax and features in the context of a simple case study. Samuel has been making speciality cookies for a few years. They have become so popular that he wants to use MySQL to help manage his record keeping so that he can spend more time on the cookie-making end of the business. We believe that you will get a lot more out of this article and its companions if you read it actively, in other words turn on your computer, download MySQL4, and bash in the commands. You will learn a lot more than by simply nodding your head.

As their name indicates, database management systems exist to create and process databases which are made up associated tables. This article presents what you need to know to start database table processing with MySQL4. As is often the case, MySQL5 retains most but not all of these statements. When you are ready for serious MySQL processing make sure that the on-line manual is available.

A database must be created which is a one time operation using the CREATE DATABASE statement. It must also be activated using the USE DATABASE statement.

This textbook gives recipes for MySQL, but no edible cookies.

The CREATE DATABASE command creates a database for a given user. For example, GRANT ALL PRIVILEGES ON cookies.* TO 'samuel'@'localhost' IDENTIFIED BY 'samuel63' WITH GRANT OPTION; creates the user named samuel on the local computer for the cookies database.

The USE cookies; statement activates the cookies database. The mysql monitor replies with the message Database changed. Only a single database may be active at one time.

The next step is to create the tables. In the beginning there will be only two tables, a Customer table containing data for customers who signed up to buy his cookies on a regular basis and a Purchase table containing data for individual purchases. Note that these tables are linked by the Custnum field. Later on Samuel can get the Custname and other customer data for the purchases. We have simplified reality to smooth the learning process. First comes the Customer table which will contain data about Samuel’s customers. For simplicity this table contains only six fields or columns containing information about each customer. Each column name is followed by the column type and perhaps other information such as the column size.

CREATE TABLE Customer (Custnum INT NOT NULL, Custname VARCHAR(18), Custtype CHAR(1), Custaddr VARCHAR(15), Zipcode CHAR(5), Phone CHAR(8));

Let’s take a look at the various parts of the above statement. The table is named Customer. It consists of six columns each describing an attribute of a given customer. Custnum is an integer (whole number). The phrase NOT NULL specifies that customer number may not be left blank. Custname is a character field whose maximum length is 18 but if the customer name is shorter the system will save on storage. The Custype is a single character. The Custaddr is similar to the Custname but has a smaller maximum value. Both the Zipcode and the Phone are fixed length character fields. If you were really going to set up such an application you would probably require additional fields such as City and State. Many customers would provide two or more telephone numbers which would have to include an area code. Our objective is to get you started with MySQL relatively painlessly.

Next comes the Purchase table which will contain data about purchases created by the following statement: CREATE TABLE Purchase (Purchnum INT, Prodnum INT NOT NULL, Custnum INT NOT NULL, Purchdat DATE, Readydat DATE, Kilos FLOAT);

We are only going to comment on new features in the above statement. The command includes two fields defined as DATEs. Samuel has gone metric and sells his cookies in Kilograms. The Kilos field is FLOAT and not INT; customers may order 5.5 kilograms instead of being restricted to a whole number with an INT field.

The next step will be entering data into the cookies database.