MySQL RDBMS: A Comprehensive Guide with Examples

Introduction to MySQL RDBMS

MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage and manipulate data. It is widely used for web applications, data warehousing, and online transaction processing (OLTP). MySQL is known for its speed, reliability, and ease of use, making it a popular choice for developers and businesses.

What is an RDBMS?

A Relational Database Management System (RDBMS) organizes data into tables (relations) consisting of rows (records) and columns (fields). It establishes relationships between tables using keys (Primary and Foreign Keys), ensuring data integrity and reducing redundancy.

Key Features of MySQL RDBMS

1. Open-Source & Free – Available under the GNU General Public License.
2. Cross-Platform Support – Works on Windows, Linux, macOS, and more.
3. Scalability – Handles small to large-scale applications.
4. High Performance – Optimized for fast data retrieval and storage.
5. Security – Supports encryption, user authentication, and access control.
6. ACID Compliance – Ensures Atomicity, Consistency, Isolation, and Durability for transactions.

MySQL Database Structure

MySQL organizes data in a structured way:

• Database → Contains multiple tables.
• Table → Stores data in rows and columns.
• Row (Record) → A single entry in a table.
• Column (Field) → A specific attribute of a record.
• Primary Key → A unique identifier for each record.
• Foreign Key → Links two tables together.

Step-by-Step MySQL Example

Let’s create a simple database for an “Online Bookstore” with two tables: books and authors.

Step 1: Install MySQL

Download and install MySQL from the official website. Alternatively, use XAMPP/WAMP for a local server setup.

Step 2: Access MySQL Command Line

Open MySQL Command Line Client or a terminal and log in:

mysql -u root -p

mysql -u root -p

(Enter your password when prompted.)

Step 3: Create a Database

CREATE DATABASE bookstore;
USE bookstore;

CREATE DATABASE bookstore;
USE bookstore;

Step 4: Create Tables

Authors Table

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country VARCHAR(50)
);

CREATE TABLE authors (
    author_id INT AUTO_INCREMENT PRIMARY KEY,
    author_name VARCHAR(100) NOT NULL,
    country VARCHAR(50)
);

Books Table

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE books (
    book_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2),
    author_id INT,
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Step 5: Insert Data

Add Authors

INSERT INTO authors (author_name, country) 
VALUES 
    ('J.K. Rowling', 'UK'),
    ('George R.R. Martin', 'USA'),
    ('Agatha Christie', 'UK');

INSERT INTO authors (author_name, country) 
VALUES 
    ('J.K. Rowling', 'UK'),
    ('George R.R. Martin', 'USA'),
    ('Agatha Christie', 'UK');

Add Books

INSERT INTO books (title, price, author_id) 
VALUES 
    ('Harry Potter', 19.99, 1),
    ('A Game of Thrones', 24.99, 2),
    ('Murder on the Orient Express', 14.99, 3);

INSERT INTO books (title, price, author_id) 
VALUES 
    ('Harry Potter', 19.99, 1),
    ('A Game of Thrones', 24.99, 2),
    ('Murder on the Orient Express', 14.99, 3);

Step 6: Query Data

Select All Books

SELECT * FROM books;

SELECT * FROM books;

Output:

Join Tables (Books with Authors)

SELECT books.title, authors.author_name, books.price 
FROM books 
JOIN authors ON books.author_id = authors.author_id;

SELECT books.title, authors.author_name, books.price 
FROM books 
JOIN authors ON books.author_id = authors.author_id;

Output:

Step 7: Update & Delete Data

Update a Book’s Price

UPDATE books SET price = 21.99 WHERE book_id = 1;

UPDATE books SET price = 21.99 WHERE book_id = 1;

Delete a Book

DELETE FROM books WHERE book_id = 3;

DELETE FROM books WHERE book_id = 3;

Why Use MySQL?

1. Web Development – Powers websites like Facebook, Twitter, and WordPress.
2. Data Analytics – Efficiently handles large datasets.
3. Cloud Integration – Works with AWS, Google Cloud, and Azure.
4. Community Support – Large developer community and extensive documentation.

Conclusion

MySQL is a powerful, flexible, and widely-used RDBMS suitable for various applications. By following this guide, you’ve learned how to:

• Create a database and tables.
• Insert, query, update, and delete data.
• Establish relationships between tables.

Whether you’re a beginner or an experienced developer, mastering MySQL will enhance your ability to manage and analyze data efficiently.

Further Learning

• Explore MySQL Workbench for a GUI-based experience.
• Learn advanced SQL queries (GROUP BY, HAVING, subqueries).
• Study indexing and optimization for better performance.