Understanding Databases, Tables, Rows, and Columns in MySQL

Databases, tables, rows, and columns are the fundamental building blocks of a relational database management system (RDBMS). MySQL organizes information into databases, stores related information in tables, and represents individual records using rows and their attributes using columns. Understanding these concepts is essential for designing databases, writing SQL queries, managing records, and developing database-driven applications.

Learn these concepts through JustAcademy MySQL Training or explore the course by clicking Register for a MySQL Course Demo.

1. Learning Objectives

After completing this topic, learners will be able to:

• Understand what a database is and why it is used.
• Understand the purpose and structure of tables.
• Identify rows and columns in a database table.
• Understand the relationship between databases, tables, rows, and columns.
• Learn how data types define the values stored in columns.
• Create databases and tables using MySQL commands.
• Insert, retrieve, update, and delete records.
• Understand primary keys, foreign keys, and basic database relationships.
• Use SQL commands to inspect database structures and retrieve information.

2. What Is a Database?

A database is an organized collection of data stored electronically so that information can be accessed, managed, updated, and retrieved efficiently. Databases help applications maintain information in a structured and consistent manner.

For example, a college may maintain a database containing student information, course details, teacher records, attendance, and examination results.

Features of a Database

• Organized Storage: Stores information in a structured format.
• Easy Retrieval: Allows users to find specific information using queries.
• Data Modification: Supports inserting, updating, and deleting records.
• Data Integrity: Supports rules and constraints that help maintain accurate information.
• Security: Allows access to be controlled through database users and privileges.
• Reduced Duplication: Good database design helps avoid unnecessary repeated information.
• Data Relationships: Relational databases connect related information across multiple tables.

Example of a Database

Consider a database named college_db. It may contain several tables, such as Students, Courses, Teachers, and Enrollments.

college_db
    |
    |-- students
    |-- courses
    |-- teachers
    |-- enrollments

Here, college_db is the database, and the items listed underneath it are tables that organize different types of information.

How to Create a Database in MySQL

CREATE DATABASE college_db;

The CREATE DATABASE statement creates a new database named college_db.

How to Display Databases

SHOW DATABASES;

This command displays the databases available to the current MySQL account, subject to its permissions.

How to Select a Database

USE college_db;

The USE statement selects college_db as the default database for subsequent commands in the current session.

3. What Is a Table?

A table is a database object that organizes related data into rows and columns. Each table typically represents a particular entity or subject, such as students, employees, products, customers, or orders.

For example, a Students table stores information about students. Its columns may include student_id, student_name, email, and age. Each row represents one student.

Example of a Students Table

In this example, Students is the table name. The table contains four columns and three rows of student data.

Characteristics of Tables

• Every table has a name within its database.
• Columns define the structure of the table.
• Rows contain individual records.
• Each column has a data type that defines the kind of value it can store.
• Constraints can be applied to enforce rules on the stored data.
• Primary keys can uniquely identify records.
• Foreign keys can connect records to related tables.

How to Create a Table

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    email VARCHAR(150),
    age INT
);

This statement creates a table named students with four columns: student_id, student_name, email, and age.

How to Display Tables

SHOW TABLES;

This command displays the tables in the currently selected database that the account is permitted to see.

4. What Is a Row?

A row is a horizontal record in a table. It contains values for the columns defined in that table. In a Students table, each row represents one student's information.

Example of a Row

The highlighted example represents one complete student record. Its values describe student ID 1, the student's name, email address, and age.

Important Characteristics of Rows

• A row represents one record or entry.
• A row contains values associated with the table's columns.
• Each row can be identified using a primary key when one is defined.
• Rows can be inserted, updated, selected, or deleted using SQL.
• A table can contain many rows, depending on storage capacity and system limits.

How to Insert a Row

INSERT INTO students
    (student_id, student_name, email, age)
VALUES
    (1, 'Aarav Sharma', '[email protected]', 20);

The INSERT statement adds a new record to the students table.

How to Retrieve Rows

SELECT * FROM students;

This query retrieves all columns and all rows from the students table.

How to Retrieve a Specific Row

SELECT *
FROM students
WHERE student_id = 1;

The WHERE clause filters the results and returns the record whose student_id is 1.

5. What Is a Column?

A column is a vertical field in a table that represents a particular attribute of the stored data. For example, a Students table may contain student_id, student_name, email, and age columns.

Each column has a name and a data type. Depending on the column definition, it may also have constraints, a default value, or other properties.

Example of Columns

Characteristics of Columns

• Every column has a name.
• Each column has a defined data type.
• A column can have constraints such as NOT NULL or UNIQUE.
• A column can allow NULL values if its definition permits them.
• A column may have a default value.
• Columns can be used in filtering, sorting, grouping, and calculations.

How to Display Column Information

DESCRIBE students;

The DESCRIBE statement displays the column names, data types, nullability, key information, default values, and other available structural details.

How to Retrieve a Specific Column

SELECT student_name
FROM students;

This query retrieves only the student_name column from the students table.

6. Difference Between Database, Table, Row, and Column

Simple Real-Life Example

Imagine a school office that maintains student records.

• Database: The complete digital record system of the school.
• Table: The Students register within that system.
• Row: One student's complete record.
• Column: A field such as name, age, or email.

This comparison helps explain how a database groups information and how tables organize individual records.

7. Understanding Data Types in Columns

A data type specifies what kind of values a column can store. Choosing appropriate data types improves data consistency, validation, and storage efficiency.

Common MySQL Data Types

Example of Data Types

CREATE TABLE employees (
    employee_id INT,
    employee_name VARCHAR(100),
    salary DECIMAL(10,2),
    joining_date DATE,
    is_active BOOLEAN
);

In this table, employee_id stores an integer, employee_name stores text, salary stores an exact decimal value, joining_date stores a date, and is_active stores a Boolean-style value.

8. Understanding NULL and Default Values

NULL represents a missing, unknown, or unavailable value. It is different from zero, an empty string, or the text 'NULL'. A column can contain NULL only when its definition and constraints permit it.

Example of NULL

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    phone_number VARCHAR(20) NULL
);

The phone_number column can contain NULL because the column explicitly allows it. The employee_name column cannot contain NULL because it is defined as NOT NULL.

Checking for NULL Values

SELECT *
FROM employees
WHERE phone_number IS NULL;

Use IS NULL or IS NOT NULL to check for NULL values. Comparisons such as phone_number = NULL do not correctly test for NULL.

Using a Default Value

CREATE TABLE tasks (
    task_id INT PRIMARY KEY,
    task_name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'Pending'
);

If a new task is inserted without a status value, MySQL uses the default value Pending when the column is omitted and the applicable table definition allows it.

9. Primary Keys and Unique Identification of Rows

A primary key is a column or combination of columns that uniquely identifies each row in a table. A primary key must contain unique, non-NULL values.

Example of a Primary Key

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    age INT
);

Here, student_id is the primary key. Two students cannot have the same student_id, and the primary key cannot contain NULL.

Using AUTO_INCREMENT

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    age INT
);

AUTO_INCREMENT allows MySQL to generate a new integer identifier automatically when a value is not explicitly provided for the column.

Why Are Primary Keys Important?

• They uniquely identify individual records.
• They prevent duplicate key values.
• They allow other tables to reference specific records.
• They help applications retrieve and update the correct record.

10. Foreign Keys and Relationships Between Tables

A foreign key is a constraint that connects a column or group of columns in one table to a referenced key in another table or, in supported designs, the same table. Foreign keys help enforce referential integrity.

Example

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
);

In this example, department_id in the employees table references department_id in the departments table. This helps prevent an employee record from referencing a department that does not exist, subject to the foreign-key rules.

Common Types of Relationships

• One-to-One: One record in a table corresponds to at most one record in another table.
• One-to-Many: One record in a table can be related to multiple records in another table.
• Many-to-Many: Multiple records in one table can relate to multiple records in another, usually through a junction table.

Example of a One-to-Many Relationship

One department may contain many employees. Each employee references a department through department_id.

11. Creating a Complete Database and Table in MySQL

Follow these steps to create a database and define its table structure.

Step 1: Create the Database

CREATE DATABASE school_db;

Step 2: Select the Database

USE school_db;

Step 3: Create a Table

CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE,
    age INT,
    course_name VARCHAR(100)
);

Step 4: Insert Records

INSERT INTO students
    (student_name, email, age, course_name)
VALUES
    ('Aarav Sharma', '[email protected]', 20, 'MySQL'),
    ('Priya Patel', '[email protected]', 21, 'Data Analytics'),
    ('Rohan Verma', '[email protected]', 19, 'Python');

Step 5: Display All Records

SELECT * FROM students;

Expected Output

The generated student_id values shown above are illustrative. Actual values depend on the database's existing data and AUTO_INCREMENT state.

12. Retrieving Specific Rows and Columns

SQL allows you to retrieve all records, selected columns, or records that match a condition.

Retrieve All Columns

SELECT * FROM students;

The asterisk (*) selects every column in the table.

Retrieve Specific Columns

SELECT student_name, course_name
FROM students;

This query returns only the student_name and course_name columns.

Retrieve a Specific Row

SELECT *
FROM students
WHERE student_id = 2;

This query retrieves the student whose student_id equals 2.

Filter Rows Using a Condition

SELECT student_name, age
FROM students
WHERE age > 19;

This query returns the names and ages of students whose age is greater than 19.

Sort Records

SELECT student_name, age
FROM students
ORDER BY age ASC;

ORDER BY sorts the results by age in ascending order. Use DESC for descending order.

13. Updating and Deleting Rows

Rows can be changed or removed when the information in a database needs to be maintained.

Update a Record

UPDATE students
SET course_name = 'Advanced MySQL'
WHERE student_id = 1;

This query changes the course_name for the student whose student_id is 1.

Delete a Record

DELETE FROM students
WHERE student_id = 3;

This query deletes the row whose student_id is 3.

Important: Always check the WHERE condition before running UPDATE or DELETE. Without a WHERE clause, an UPDATE may modify every row, and a DELETE may remove every row from the table.

14. Adding and Modifying Columns

As application requirements change, you may need to add a new column or change an existing column definition.

Add a New Column

ALTER TABLE students
ADD phone_number VARCHAR(20);

This statement adds a phone_number column to the students table.

Modify a Column Definition

ALTER TABLE students
MODIFY COLUMN student_name VARCHAR(150) NOT NULL;

This statement changes the student_name column's maximum length to 150 characters while retaining the NOT NULL requirement. Existing data must satisfy the new definition.

Remove a Column

ALTER TABLE students
DROP COLUMN phone_number;

This statement removes the phone_number column and its stored values. Review dependencies and back up important data before performing schema changes.

15. Useful MySQL Commands for Databases and Tables

Warning: DROP TABLE and DROP DATABASE are destructive commands. Use them only when you are certain that the affected objects and data are no longer needed.

16. Common Mistakes Beginners Should Avoid

• Confusing a Database with a Table: A database can contain multiple tables; a table organizes related records.
• Confusing Rows with Columns: Rows represent records, while columns represent attributes.
• Choosing Incorrect Data Types: Use suitable types for text, numbers, dates, and other values.
• Ignoring Primary Keys: Define appropriate keys when records need unique identification.
• Allowing Unnecessary NULL Values: Use NOT NULL when a value is required.
• Using SELECT * in Every Query: Selecting only required columns can make queries easier to maintain.
• Forgetting WHERE Conditions: Carefully check conditions before updating or deleting records.
• Ignoring Relationships: Use appropriate foreign keys when related tables require referential integrity.
• Dropping Tables Accidentally: Verify the selected database and table name before destructive operations.

17. Frequently Asked Interview Questions

Q1. What is a database?

A database is an organized collection of data that can be stored, accessed, and managed electronically.

Q2. What is a table in MySQL?

A table is a database object that organizes related data into rows and columns.

Q3. What is a row?

A row is a record containing values for the columns defined in a table.

Q4. What is a column?

A column represents a specific attribute of the data, such as a name, email address, or age.

Q5. What is the difference between a row and a column?

A row represents one record, while a column represents one type of information stored for each record.

Q6. Can a database contain multiple tables?

Yes. A database can contain multiple tables, along with other database objects such as views, indexes, and stored routines.

Q7. What is a primary key?

A primary key is a column or combination of columns that uniquely identifies each row in a table and cannot contain NULL values.

Q8. What is a foreign key?

A foreign key is a constraint that links a column or set of columns to a referenced key and helps maintain referential integrity.

Q9. What is the purpose of the DESCRIBE command?

DESCRIBE displays information about a table's columns, data types, nullability, key information, and other structural details.

Q10. What does SELECT * FROM students do?

It retrieves all columns and rows from the students table.

Q11. What is the difference between NULL and zero?

NULL represents a missing, unknown, or unavailable value. Zero is a numeric value.

Q12. What is AUTO_INCREMENT in MySQL?

AUTO_INCREMENT allows MySQL to generate sequential numeric identifiers for new records when a value is not explicitly supplied.

Q13. How do you create a database in MySQL?

Use the CREATE DATABASE statement, such as CREATE DATABASE school_db;.

Q14. How do you display the tables in a database?

Select the database with USE database_name; and then execute SHOW TABLES;.

Q15. Why are data types important?

Data types define the values a column can store and help maintain data consistency and appropriate storage behavior.

18. Practical Exercises

1. Create a database named library_db.
2. Create a books table with book_id, title, author, and price columns.
3. Insert at least five book records into the table.
4. Retrieve all records from the books table.
5. Display only the title and author columns.
6. Find books with a price greater than 500.
7. Sort books by price in descending order.
8. Update the price of a particular book using its book_id.
9. Add a publication_year column to the books table.
10. Create an authors table and connect it to books using an appropriate foreign key design.
11. Use DESCRIBE books; to inspect the table structure.
12. Explain the difference between the database, books table, a single book row, and the title column.

19. Revision Checklist

• Understand the meaning of a database.
• Understand how tables organize related information.
• Identify rows as records and columns as attributes.
• Know the purpose of common MySQL data types.
• Understand NULL, DEFAULT, NOT NULL, and UNIQUE.
• Understand primary keys and foreign keys.
• Practice CREATE DATABASE, CREATE TABLE, and INSERT statements.
• Practice SELECT, WHERE, ORDER BY, UPDATE, and DELETE.
• Know how to use SHOW DATABASES, SHOW TABLES, and DESCRIBE.
• Understand the risks of destructive SQL commands.

20. Conclusion

Databases, tables, rows, and columns form the foundation of relational database management in MySQL. A database organizes related objects, a table stores a particular category of information, rows represent individual records, and columns describe the attributes of those records. Data types and constraints help ensure that stored information follows the intended structure and rules.

By learning these concepts and practicing basic SQL commands, beginners can build a strong foundation for database design, application development, data analysis, and database administration.

Continue your learning journey with JustAcademy MySQL Training or Register for a MySQL Course Demo.

21. Additional Learning Resources

• MySQL Getting Started Guide
• Official MySQL Documentation: Getting Information About Databases and Tables
• Official MySQL SQL Tutorial