MySQL - Creating and Deleting Tables

This article will teach you how to create and delete tables in your MySQL server.



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


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` (
   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.


Let's see if the table was created:

SHOW tables;

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;

© 2015 Rackspace US, Inc.

Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License

See license specifics and DISCLAIMER