How to Create, Select, and Manage Databases and Tables in MySQL

Databases and tables form the foundation of MySQL. A database organizes related information, while tables store that information in rows and columns. Understanding how to create, select, modify, and remove databases and tables is an essential part of working with MySQL.

Learning Objectives

• Create and select MySQL databases

• View available databases

• Drop databases when they are no longer required

• Create and manage tables

• Use AUTO_INCREMENT

• Rename tables

• Add and remove columns

• Create temporary tables

• Work with generated columns

• Drop tables safely

Managing Databases in MySQL

A database is an organized collection of related information. In MySQL, databases contain tables and other database objects.

Before creating tables and storing data, you first need to create and select a database.

Create a Database

Use the CREATE DATABASE statement to create a new database.

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE TrainingDB;

To verify that the database has been created, run:

SHOW DATABASES;

You should see TrainingDB in the list of available databases.

Select a Database

Before creating tables or running database-specific operations, select the database you want to use.

Syntax

USE database_name;

Example

USE TrainingDB;

After executing the command, TrainingDB becomes the active database.

You can check the currently selected database using:

SELECT DATABASE();

Expected Result

TrainingDB

Drop a Database

The DROP DATABASE statement permanently removes a database and its contents.

Syntax

DROP DATABASE database_name;

Example:

DROP DATABASE TrainingDB;

Important: Dropping a database removes its tables and stored data. Use this command carefully.

For practice, keep TrainingDB because we'll use it in the following examples.

Managing Tables in MySQL

A table stores information in rows and columns.

For example, a student table may contain:

Before creating the table, select the database:

USE TrainingDB;

Create a Table

Use CREATE TABLE to create a new table.

Example

CREATE TABLE Students (

    student_id INT,

    student_name VARCHAR(100),

    course VARCHAR(100),

    marks INT

);

To view the structure:

DESCRIBE Students;

Using AUTO_INCREMENT

AUTO_INCREMENT automatically generates a sequential numeric value when a new record is inserted.

It is commonly used with ID columns.

Example

CREATE TABLE Employees (

    employee_id INT AUTO_INCREMENT PRIMARY KEY,

    employee_name VARCHAR(100),

    department VARCHAR(50)

);

When records are added, MySQL can automatically generate values such as:

1

2

3

4

This means you don't need to manually assign an ID for every new employee.

Rename a Table

Use RENAME TABLE to change an existing table name.

Example

RENAME TABLE Students TO StudentDetails;

You can verify the change using:

SHOW TABLES;

To continue with the examples below, rename it back:

RENAME TABLE StudentDetails TO Students;

Add a Column

Use ALTER TABLE with ADD COLUMN to add a new column.

Example

ALTER TABLE Students

ADD COLUMN email VARCHAR(100);

Check the updated table:

DESCRIBE Students;

The email column should now appear in the table structure.

Drop a Column

Use DROP COLUMN when a column is no longer required.

Example

ALTER TABLE Students

DROP COLUMN email;

The column and the data stored in it are permanently removed.

Drop a Table

Use DROP TABLE to permanently remove a table.

Syntax

DROP TABLE table_name;

Example:

DROP TABLE Students;

Important: This removes the table structure and its stored data.

For practice, don't drop the Students table if you want to use it in later exercises.

Temporary Tables

A temporary table exists only for the current database session and can be useful for temporarily storing intermediate data.

Example

CREATE TEMPORARY TABLE TempStudents (

    student_id INT,

    student_name VARCHAR(100)

);

You can work with the temporary table during the current session.

For example:

INSERT INTO TempStudents

VALUES (1, 'John');

Then:

SELECT * FROM TempStudents;

When the session ends, the temporary table is automatically removed.

Generated Columns

A generated column calculates its value automatically using an expression based on other columns.

For example, consider a table containing quantity and price.

CREATE TABLE Orders (

    order_id INT AUTO_INCREMENT PRIMARY KEY,

    product_name VARCHAR(100),

    quantity INT,

    price DECIMAL(10,2),

    total_amount DECIMAL(10,2)

        GENERATED ALWAYS AS (quantity * price) STORED

);

Here, total_amount is calculated automatically using:

quantity × price

If:

quantity = 3

price = 100.00

the generated value will be:

300.00

This reduces the need to manually calculate and store the total for every record.

Database vs Table

Common Mistakes

When managing databases and tables, watch for these common issues:

• Creating a table without selecting the correct database

• Attempting to create a database or table that already exists

• Using incorrect data types for columns

• Accidentally dropping a database or table containing required information

• Dropping a column without checking whether existing data depends on it

• Forgetting that temporary tables are available only during the current session