Understanding Databases & Tables in MySQL

Before learning how to write SQL queries, it is important to understand how MySQL organizes data internally.
MySQL does not store data randomly. It follows a clear structure using databases and tables, which makes data easy to store, search, and manage.

This article explains these core ideas step by step.

What Is a Database in MySQL?

A database in MySQL is a collection of related data stored together.

You can think of a database as a folder on your computer.

For example:

  • One folder for college work
  • Another folder for personal documents

Similarly, MySQL uses databases to group related information.

Example databases:

  • college_db
  • company_db
  • online_store

Each database contains tables that store actual data.

Creating a Database in MySQL

To create a database, we use the CREATE DATABASE command.

CREATE DATABASE college_db;

Once this command runs successfully, MySQL creates an empty database.

To view all databases present in MySQL:

SHOW DATABASES;

Deleting a Database

To delete a database, we use the DROP DATABASE command.

DROP DATABASE college_db;

Important:

  • This command permanently deletes the database
  • All tables and data inside them are removed
  • There is no recovery option

Always be careful while using this command.

What is a Table?

A table is where actual data is stored inside a database.

If a database is a folder, then a table is like an Excel sheet inside that folder.

Each table:

  • Stores one type of data
  • Has rows and columns
  • Follows a fixed structure

Examples:

  • A student's table stores student details
  • Ateacher'ss table stores teacher details

Different types of data are always stored in different tables.

Rows, Columns, Records, and Fields

Understanding these terms is essential when working with databases.

Image

Columns (Fields)

  • Columns define what kind of data will be stored
  • Each column has a name and a data type

Example columns in student'sts table:

  • student_id
  • name
  • age
  • email

A field is another name for a column.

Rows (Records)

  • Each row stores a complete set of data
  • One row represents one real-world entry

Example:

  • One row contains all the details of one student

A record is another name for a row.

Terminology Summary

Term

Meaning

Column

Type of data

Field

Same as column

Row

One full entry

Record

Same as row

How MySQL Stores Table Data

Internally, MySQL stores data in tables using a structured format.

  • Each table has predefined columns
  • Data is stored row by row
  • MySQL enforces data types and constraints

MySQL also handles:

  • Data storage
  • Searching and indexing
  • Data consistency
  • Security and access control

As a user, you only need to interact with the data using SQL queries.

Naming Conventions and Best Practices

Using proper names makes databases easier to understand and maintain.

Database Naming Conventions

Best practices:

  • Use lowercase letters
  • Use underscores instead of spaces
  • Use meaningful names

Good examples:

college_db

employee_management

online_store

Avoid:

CollegeDatabase

my database

db1

Table Naming Conventions

  • Use plural names
  • Clearly describe the data stored

Good examples:

students

employees

orders

products

Avoid:

data

table1

info

Column Naming Conventions

  • Use clear and descriptive names
  • Avoid short or unclear abbreviations

Good examples:

student_id

email

created_at

phone_number

Avoid:

sid

x

col1

Summary

  • A database is a container for related data
  • Tables store actual data in rows and columns
  • Columns define a data structure
  • Rows store individual records
  • Proper naming improves clarity and maintainability