Create and modify a MySQL table

How to create, modify, and drop a MySQL table using SQL scripts?

Tutorial to learn how to create, modify, empty, and drop a table with MySQL scripts. MySQL is a relational database management system (RDBMS) that allows you to store and manage data in an organized way. In this tutorial, we will see how to create a table, insert data into it, delete data from it, modify the table to add or delete columns, clear all data from the table, and delete the table.

Table of Contents

1. Create a MySQL table

To create a table in a MySQL database, we use the CREATE TABLE command. Here is an example code that creates a table named users with two columns, id and username, respectively of type INT and VARCHAR(255).

The following code create a MySQL table, to execute it, connect to PHPMyAdmin and run the code. This will create a users table with 5 different columns.

id : an integer that will be automatically incremented and used as the primary key for the table
username: a string with a maximum length of 255 characters, and is not allowed to be NULL
email: a string with a maximum length of 255 characters, and is not allowed to be NULL
password: a string with a maximum length of 255 characters, and is not allowed to be NULL
created_at: a datetime value that will be set to the current timestamp by default

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  username VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  password VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

We can also specify constraints for each column. For example, we have defined the id column as the primary key of the table and as an auto-incremented integer.

To verify that the table was successfully created, we can use the SHOW TABLES command, as follows.

SHOW TABLES;

Check out this detailed tutorial to create a Pivot Table with Excel to analyse data easily.

Create an Excel pivot table

2. Insert data into a MySQL table

To insert data into a MySQL table, we use the INSERT INTO command. Here is an example code that inserts a record into the users table:

INSERT INTO users (username, email, password)
   VALUES ('johndoe', 'johndoe@example.com', 'mypassword');

We can also insert multiple records at once using the following syntax:

INSERT INTO users (username, email, password)
   VALUES
     ('johndoe', 'johndoe@example.com', 'mypassword'),
     ('janedoe', 'janedoe@example.com', 'anotherpassword');

To insert a row and specifying the data type for each value, especially the DATE. Please note that the following statement will duplicate the line with the same name, because the primary key is the auto incremented ID and not the user name.

To allow one unique name only, change the table structure to add a constraint on the username column.

INSERT INTO users (username, email, password, created_at)
   VALUES ('johndoe', 'johndoe@example.com', 'mypassword', CAST('2022-01-01' AS DATE));

3. Delete data with MySQL

To delete data from a MySQL table, we use the standard SQL DELETE FROM command.

3.1 Deleting all data from a MySQL table

Here is an example code that deletes all records from the users table.

DELETE FROM users;

3.2 Deleting specific data from a MySQL table

We can also use a WHERE clause to specify deletion conditions. For example, to delete a record from the users table where the username is ‘johndoe’, we can use the following query.

DELETE FROM users
   WHERE username = 'johndoe';

List files recursively with cmd in folders and subfolders

4. Modify columns with MySQL

It is possible to modify a MySQL table by adding or deleting columns.

4.1 Adding a MySQL Column

To add a column to a MySQL table, we use the ALTER TABLE and ADD COLUMN commands. Here is an example code that adds a column named email to the users table.

ALTER TABLE users
   ADD COLUMN email VARCHAR(255);

4.2 Deleting a Column from a MySQL Table

To delete a column from a MySQL table, we use the ALTER TABLE and DROP COLUMN commands. Here is an example code that deletes the email column from the users table.

ALTER TABLE users DROP COLUMN email;

5. Clear all data from a MySQL table

To clear all data from a MySQL table, we use the TRUNCATE TABLE command. Here is an example code that clears all data from the users table.

TRUNCATE TABLE users;

6. Drop a MySQL table from a database

To delete a MySQL table, we use the DROP TABLE command. Here is an example code that deletes the users table.

DROP TABLE users;

7. Conclusion on how to manage MySQL tables

In this tutorial, we have learned how to create, modify, clear, and delete a MySQL table using various SQL commands. It is important to note that these actions are irreversible, so it is always a good idea to make a backup of your data before making any changes. It is also recommended to use these commands with caution, as they can have a significant impact on your database.

Another tutorial on how to connect to PHPMyAdmin and execute SQL queries to create a table with a primary key.