Preparation

If you deleted ‘mytestdb’ then please see the article link above for details on how to create and delete databases.

Or simply enter this command from the terminal:

mysqladmin -u root -p create mytestdb

Done.

Log in

As we will be working in MySQL itself, we need to log in:

mysql -u root -p

Enter your MySQL root password.
Which database?

At this stage there are a minimum of three databases within MySQL (the two MySQL admin databases and the ‘mytestdb’ database).

If we said the equivalent of ‘add a table named clients’, MySQL wouldn’t know to which database we were referring.

As such, you always need to specify which database you want to work with like so:

use mytestdb;

Create a table

The first table within ‘mytestdb’ will hold the details of our clients.

At this stage we need a unique ID for our client and their email address:

CREATE TABLE `clients` (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(45)
);

All we did there was create two columns named ‘id’ and ’email’.

The id column has some specific parameters such as needing to be an integer, it must exist, it will be used as the primary key for the table and the id will be automatically incremented.

The email column is a little simpler in that it is to be a simple text entry of no more than 45 characters.
Show

Let’s see if the table was created:

SHOW tables;

The output is:

mysql> SHOW tables;

1 row in set (0.00 sec)

Looks good to me but let’s take a closer look:

SHOW columns FROM clients;

which gives us:

mysql> SHOW columns FROM clients;

2 rows in set (0.01 sec)

Excellent – not only is that what we wanted, but it looks darned impressive!
Rename

Moving onto administering our tables, we can look at renaming it:

RENAME TABLE clients TO customers;

That’s it. Have a check with the ‘SHOW tables;’ command.
Drop

At some point we may have to say goodbye to a particular table. As with users and databases, the command is drop:

DROP TABLE customers;

Done.

The next article will examine how to add more columns to the table as well as adding data to the table.
Summary

Manipulating MySQL from the command line can save a lot of time and hassle.

You may be seeing a pattern in the commands when administering users, databases and tables: create, show, rename and drop are common commands and useful to remember.

Categorised as: Linux