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
As we will be working in MySQL itself, we need to log in:
mysql -u root -p
Enter your MySQL root password.
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:
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,
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.
Let’s see if the table was created:
The output is:
mysql> SHOW tables;
| Tables_in_mytestdb |
| clients |
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;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | NO | PRI | NULL | auto_increment |
| email | varchar(45) | YES | | NULL | |
2 rows in set (0.01 sec)
Excellent – not only is that what we wanted, but it looks darned impressive!
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.
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;
The next article will examine how to add more columns to the table as well as adding data to the table.
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