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: 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.