Creating a New Database Table

Creating a New Table

It is rather easy to create new tables in MySQL. If the user is already connected to the database, just type the CREATE command specifying various information about the fields. Here is how the table scores was created.

mysql> CREATE TABLE scores (Name VARCHAR(20), Num INT(5));

The system will reply that the table is OK:

Query OK, 0 rows affected (0.00 sec)

It is better to identify one of the variables as the "PRIMARY KEY", a variable that is considered the main item in the table and something that should be unique. That variable must also be specified as "NOT NULL" so that a table entry cannot be added without this important value. In this case, it would be foolish to have scores listed for a nonexistant person, but it makes sense to have a person listed who may not have a score yet. We would also want the names of individuals in the table to be unique, so that there is no confusion as to who owns a certain score. Therefore, the PRIMARY KEY should be the Name field. The syntax for this modification is:

mysql> CREATE TABLE scores (Name VARCHAR(20) NOT NULL, Num INT(5), PRIMARY KEY (Name));

At this point, the table contains no entries, so a request to show its contents should return NULL.

Data Types for Fields in a Table

There are a number of types of fields that can be utilized when creating variables in a table. Here are a few of the more common data types:

INT(length)
Standard integer type. Can also be referenced as INTEGER.
FLOAT(length, decimals)
Standard floating point decimal numeral. Can be referenced as REAL or DECIMAL.
DOUBLE(length, decimals)
Double precision floating point value.
VARCHAR(length)
Variable length character value. The variable length attribute minimizes storage so that empty space is not allocated in the database.
TEXT
A block of ASCII text that is expected to be less than 64K long. If more space is needed, check out other variations such as LONGTEXT variables which can be up to 4GB in length.
BLOB
Basically the same as TEXT except that the file type is binary. There are also variations that range from TINYBLOB of 255 characters to LONGBLOB which is 4GB in size.

Numerical values can also be assigned options such as UNSIGNED and ZEROFILL, and there are many more options depending upon the sophistication of the table design. There are ways to identify KEYS and INDEX traits, as well as whether fields cannot be NULL. Please check the MySQL web site under documentation for a complete listing.

Initializing a Table

The original creation of a table is sometimes difficult to fill, especially if there is quite a bit of data entry. Rather than using the insert command, it is often better to initialize the table from a data file of plain text information. The only requirement for the data is to be sure that the fields are listed in the same order as the way the table was created, with one line for each table row. Column values should be separated by tabs. The following command will load a textfile into the database from a file in the same directory in which MySQL was initially accessed:

mysql> LOAD DATA LOCAL INFILE "players.txt" INTO TABLE scores;

The system will respond:

Query OK, 8 rows affected (0.01 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

The text file "players.txt" had the names of the original 8 players, and it was loaded into the database. It is possible to provide paths to access files in other directories.

Getting Rid of Tables

Since tables can be created, then it will at some time be necessary to get rid of useless tables. The command to delete a table is called "DROP TABLE" and the syntax is rather straight forward:

mysql> DROP TABLE scores;

This will destroy the table and all of its contents.