Getting Started with Databases: SQL & MySQL

Databases are at the heart of modern software systems. Having an efficient data storage and retrieval mechanism is essential in making sure that that platforms run smoothly. Whether it's an e-commerce platform, a mobile app, or a simple backend system, having an efficient way to store and retrieve data ensures the continued operations of the platform. Most applications would be useless without them.

This article walks through foundational database concepts using SQL and MySQL, based on my recent learning journey. Alright, let's get into it.

What is a Database?

A database is simply a collection of structured information (or data), typically stored electronically. Software applications generate large amount of data from user inputs (e.g a signup form), manipulation of existing data, etc. Therefore, having an organized way of storing these newly generated information, so that applications can access and retrieve them when needed, is essential.

This is why a database is important. It lets you arrange information in a structured way, making it easier to work with.

So in the technology world, a database is just one big central repository for storing data in a structured way that makes it easy for software applications to find and utilize information when needed. This here is the main purpose of a database — to store huge amounts of data for secure and speedy access by other programs.

How We Create Databases — Database Management System (DBMS)

If databases exist, then a way through which they are created must also exist right?

The way we create databases is by the use of something we call a Database Management System (DBMS).

A Database Management System is simply a software for creating and managing databases. It also enables user to create, query, update, modify, and delete data in a database. Think of it as an interface between you (or your application) and the raw data stored in your storage disk.

Core responsibilities of a database management system includes but not limited to creating databases and tables, adding, editing, and deleting data via queries, fetching specific data using queries, managing user access, security, backups, recovery, performance, etc.

A database management system has different components but one important component is the Storage Engine. The Storage Engine is the component responsible for how data is actually stored on disk. It interacts with the underlying file system of the operating system to store and manage data.

Read more about Database Management System (DBMS) here.

Types of Databases

Relational Databases

Relational databases (also called SQL databases) store and organize information into tables with rows (also called tuples) and columns (attributes). Relational (SQL) databases enforce a strict schema, meaning that the structure of the data (tables, columns, and data types) must be defined in advance. In a relational database, different tables have relationships between them (there is a link to explore what relationships are in SQL). These relationships are formed or established using primary keys and foreign keys. Relational databases are very well suited for systems that require data integrity and complex querying such as financial systems.

Talking about querying, relational databases support a language called Structured Query Language (SQL) for querying and manipulating data.

Some widely used database management systems that can be used for creating and managing relational databases include MySQL, MariaDB, PostgreSQL, Oracle. Each of these systems supports structured data stored in rows and columns, enforces relationships between tables, and uses SQL for interacting with the data. However, each has its own unique strengths and ideal use cases.

Must-Know Relational Database Concepts

Now, in a bid to keep this article as concise as possible, I’ll simply list these concepts as links so you can explore each one in more detail.

• Schema
• Table
• Row
• Column
• Relationship
• Primary Key
• Foreign Key

Non Relational Databases(or NoSQL Databases)

Relational databases have certain limitations e.g., they are not (or rather less) useful for storing unstructured data and are generally difficult to scale. Non relational databases in contrast are designed to handle unstructured and semi-structured data and are designed to scale horizontally.

Think of Non Relational Database (or NoSQL Database) as an umbrella term, that covers the different database models that typically do not implement SQL for defining or querying data. Each database model that does not support SQL have significant difference between them, but they are all collectively referred to as non relational database. Example, MongoDB (a non relational database) store data in the form of JSON-like documents, but Amazon DynamoDB (also a non relational database) store data as a collection of key-value pairs. They both differ significantly from each other but are both referred to as non relational or NoSQL database (simply because they do not use SQL for querying and manipulating data)

Popular examples of database management systems that can be used for creating and managing NoSQL databases includes MongoDB, Cassandra, Amazon DynamoDB.
NoSQL databases offer scalability, high performance, and are often preferred for big data, real-time applications, and distributed systems.

SQL and Common Data Types in SQL

Structured Query Language (SQL) is the standardized language for managing, manipulating, and querying data in relational databases. SQL provides a standardized way to perform various operations on databases.

Read more about SQL here.

Here are key SQL data types and their uses:

1. Numeric Types

Used to store numbers.

• INT, SMALLINT, BIGINT – Whole numbers
• DECIMAL, NUMERIC – Precise decimal values
• FLOAT, DOUBLE – Floating-point numbers (use with care for precision)

2. Character Types

Used to store text.

• CHAR(n) – Fixed-length strings
• VARCHAR(n) – Variable-length strings
• TEXT – Long-form text

3. Date & Time Types

Used to store temporal values.

• DATE – Stores date only (YYYY-MM-DD)
• TIME – Stores time only (HH:MM:SS)
• DATETIME, TIMESTAMP – Date and time

4. Boolean Type

Used for logical values.

• BOOLEAN – Typically when you define a column as BOOLEAN MySQL automatiocally stored it as TINYINT(1). That is BOOLEAN = TINYINT(1). This column takes a BOOLEAN value (TRUE or FALSE) and store it as 0 or 1 (0 for FALSE and 1 for TRUE).

CHAR vs VARCHAR: What's the Difference?

• CHAR(n) always stores exactly n characters (space-padded if shorter).
• VARCHAR(n) stores up to n characters and saves space by only using what's needed.

Use CHAR when all entries have the same length (e.g., country codes), and VARCHAR for variable-length text (e.g., names or addresses).

SQL Commands You Should Know

CREATE — This is use to create a new database or new table in a database.

USE — This command works a bit like cd in linux. It tells the database management system to navigate and start using the selected database for all commands that would follow. Say we have database_A and database_B already created, and we want to create a table in database_B, how does the database management system know what database to use for creating the table? We'd have to specify what database we want to use for the command we're about to run. The USE command is how we tell MySQL for example, what database we want to work with.

Example Use Case:

CREATE DATABASE IF NOT EXISTS john_book_store; USE john_book_store; CREATE TABLE Books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(130) ); 

ALTER — The ALTER command is used to modify an existing table structure. Which means it can be used to add or remove a column in a table.

Now, let's add a new column (author_id) to our Books table above.

ALTER TABLE Books ADD publication_date DATE; 

DROP — The DROP command deletes an entire existing table or database with all its data. This should be used carefully as any data deleted is permanently lost.

Now, say we have a table named Authors that hold the information of various authors and we want to delete this entire table, this is how we do it.

DROP TABLE Authors; 

INSERT — This one is used to add new row(s) into a table.

Let's add a new book (title = Adventure of John Doe) to the Books table.

INSERT INTO Books (book_id, title, publication_date) VALUES (1, 'Adventure of John Doe', '2025-07-05'); 

UPDATE — I started writing this article yesterday during work period and couldn't finish it. Now if I want to change the publication_date of my book (Adventure of John Doe) which I just added to the Books table, I can do that using the UPDATE command.

Let's see how it works.

UPDATE Books SET publication_date = '2025-07-06' WHERE book_id = 1; 

The WHERE statement is used to filter records. In SQL, it is called a Clause and there are other clauses as well, like ORDER BY, GROUP BY, JOIN, HAVING and some others. Clauses help us control what data should be read or modified and how data retrieved should be presented.

SELECT — The SELECT statement is used to retrieve data from a database table.
For example, say we want to retrieve the title of the book we just added, this is how we can do it.

SELECT title FROM Books WHERE book_id = 1; 

This tells the database to retrieve only the title column in the table called Books. The addition WHERE clause filters the result by telling the database to return the title only for the row where book_id is equal to 1.

DELETE — This statement is used to remove a row or multiple row from a database table. Now let's delete the book we just added.

DELETE FROM Books WHERE book_id = 1; 

Note that the WHERE clause is recommended to filter the row or rows to be delete. Without it, all rows in the table will be deleted.

Combining Everything We've Learned: Let's Design an Online Bookstore

You should have MySQL installed and ready before going further. In case you do not have MySQL installed already, a simple google search should do the trick. There are numerous resources online that you can follow through step-by-step to setup MySQL on whatever Operating System (Windows, MacOS, Linux) you are using.

Now let’s see an example of how we might design a simple database for our bookstore:

Database Name: john_book_store

Tables:

-- Create the database CREATE DATABASE IF NOT EXISTS john_book_store; -- Use the database USE john_book_store; -- Now Let's start creating the schema (tables and their relationships) -- Create the Authors table CREATE TABLE Authors ( author_id INT AUTO_INCREMENT PRIMARY KEY, author_name VARCHAR(215) NOT NULL ); -- Create the Books table CREATE TABLE Books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(130) NOT NULL, author_id INT, price DOUBLE, publication_date DATE, FOREIGN KEY (author_id) REFERENCES Authors(author_id) ); -- Create the Customers table CREATE TABLE Customers ( customer_id INT AUTO_INCREMENT PRIMARY KEY, customer_name VARCHAR(215) NOT NULL, email VARCHAR(215), address TEXT ); -- Create the Orders table CREATE TABLE Orders ( order_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES Customers(customer_id) ); -- Create the Order_Details table CREATE TABLE Order_Details ( orderdetailid INT AUTO_INCREMENT PRIMARY KEY, order_id INT, book_id INT, quantity DOUBLE, FOREIGN KEY (order_id) REFERENCES Orders(order_id), FOREIGN KEY (book_id) REFERENCES Books(book_id) ); 

Specific Explanations

• CREATE DATABASE IF NOT EXISTS john_book_store; — This whole statement creates a new database named john_book_store, but only if it doesn't already exist. If there's already a database with that name, this line is skipped without error. The next line USE john_book_store will still run successfully, navigating to the existing database.

However, if you just do CREATE DATABASE john_book_store and the john_book_store database already exists, then the CREATE DATABASE john_book_store statement will fail, and your script would produce an error message similar to the one below.

I ran into that error while trying to create a database using Python.

• AUTO_INCREMENT — This tells MySQL to automatically increase count (e.g., 1, 2, 3, ...) with each new row added to the specific column.

• PRIMARY KEY — You should already be familiar with PRIMARY KEY if you explored the link I provided at the beginning of this article. This is how to practically implement it. Any column which has been specified as the PRIMARY KEY provides a way to identify each row in the table.

Note that it is recommended to define a PRIMARY KEY for any table you create.

• FOREIGN KEY (author_id) REFERENCES Authors(author_id) ); — This statement sets up a FOREIGN KEY. That is, it tells the database that author_id in the Books table must match author_id in the Authors table.

Conclusion

Understanding databases is really important if you want to build modern applications. Almost every application today uses a database to store, update and retrieve information.

In this article, we've only explored basic concepts by introducing what databases are, the different types, and dived a bit deeper into SQL and it's basic commands.

We've only set the ball rolling, you should explore databases more and their use cases (e.g., when to use a SQL database and when NoSQL is ideal).

I'll be publishing a short article on how to connect Python to a SQL database soon, so watch out for it.