How to create, modify, rename and delete tables using MySQL

This guide covers the basics of creating, modifying, and dropping (deleting) tables in MySQL, using clear examples to illustrate each process.

Creating Tables

Creating a table in MySQL involves defining its structure: the columns it will contain, and the type of data each column will hold.

Example: Creating a User Table

Let's create a simple table named Users:

SQL
CREATE TABLE Users (    UserID INT AUTO_INCREMENT,    Name VARCHAR(100),    Email VARCHAR(100),    PRIMARY KEY (UserID)
);

In this example:

  • UserID is an integer that automatically increments with each new record.
  • Name and Email are strings (character data) with a maximum length of 100 characters.
  • UserID is designated as the primary key, uniquely identifying each record.

Data Types

Common data types in MySQL include:

  • INT for integers.
  • VARCHAR for variable-length strings.
  • DATE for dates.

Constraints

You can also add constraints:

  • NOT NULL: Ensures a column cannot have a NULL value.
  • UNIQUE: Ensures all values in a column are unique.

Modifying Table Structure

Once a table is created, you might need to modify its structure to add, remove, or change columns.

Example: Adding a Column

To add a DateOfBirth column:

SQL
ALTER TABLE Users ADD DateOfBirth DATE;

Example: Modifying a Column

To change the Name column to hold 150 characters:

SQL
ALTER TABLE Users MODIFY Name VARCHAR(150);

Example: Dropping a Column

To remove the Email column:

SQL
ALTER TABLE Users DROP COLUMN Email;

Renaming a Table

To rename the Users table to Customers:

SQL
RENAME TABLE Users TO Customers;

Deleting Tables

If you no longer need a table, you can remove it from the database.

Example: Deleting a Table

To delete the Customers table:

SQL
DROP TABLE Customers;

Be cautious with this command, as it will permanently remove the table and all its data.

Best Practices

  • Backup Data: Always back up your data before making structural changes to your tables.
  • Use Descriptive Names: Choose clear and descriptive names for tables and columns.
  • Test Changes: Test your changes in a development environment before applying them to your production database.