MySQL Introduction

A database is an organized collection of related data, stored so that it can be searched, updated, and protected efficiently.

Concept

What Is a Database?

A database is an organized collection of related data, stored so that it can be searched, updated, and protected efficiently. A university keeps students, courses, and grades; a bank keeps customers, accounts, and transactions; a vehicle test lab keeps vehicles, ECUs, software versions, and test results.

Simple explanation: a database is like a very disciplined set of spreadsheets that many people and programs can use at the same time without corrupting each other's work.

What Is a DBMS?

A DBMS (Database Management System) is the software that stores, retrieves, secures, and manages databases. It handles:

Storing data on disk and caching it in memory Letting many users work concurrently

Transactions (all-or-nothing changes) and crash recovery Security (users, passwords, permissions) Backup and restore What Is an RDBMS?

An RDBMS (Relational DBMS) stores data in tables (relations) made of rows and columns, links tables using keys, and is queried with SQL. MySQL is an RDBMS.

The organized data The eh_company database Software managing any kind of database MySQL, MongoDB, Redis DBMS using the relational (table) model MySQL, PostgreSQL, Oracle, SQL Server What Is MySQL?

MySQL is an open-source RDBMS originally created by MySQL AB (Michael "Monty" Widenius, David Axmark, Allan Larsson) in 1995, acquired by Sun Microsystems in 2008, and owned by Oracle Corporation since 2010.

Editions: MySQL Community Server (free, GPL-licensed) and MySQL Enterprise Edition (commercial, adds tools such as Enterprise Backup, Audit, Firewall, Data Masking, and Oracle support). Oracle also offers MySQL HeatWave, a managed cloud service with an in-memory analytics engine.

Forks and relatives: MariaDB (a community fork by Monty Widenius, now diverging in features) and Percona Server for MySQL (a drop-in, performance-enhanced build).

Release model: since 2023 MySQL publishes LTS (Long-Term Support) releases (8.4 LTS) and more frequent Innovation releases (9.x). Production systems usually run an LTS release.

MySQL vs SQL (and SQL vs MySQL) A language (ISO/ANSI standard) for working with relational data

A software product (an RDBMS) that understands SQL A particular person who speaks English with a regional accent Standardized by ISO/IEC

Oracle Corporation (Community edition is open source) Implemented by many systems Has its own SQL dialect and extensions SELECT name FROM customers;

Executes that statement; also accepts MySQL-only syntax such as LIMIT, AUTO_INCREMENT, ON DUPLICATE KEY UPDATE

In short: you write SQL; MySQL runs it. MySQL's SQL is close to the standard but has extensions and a few differences (for example, || means logical OR by default, not string concatenation).

Why MySQL Is Used Free and open source (Community edition), with an enterprise option.

Easy to install and learn, with excellent documentation and a huge community.

Fast and reliable for web and OLTP (transaction processing) workloads.

InnoDB storage engine: ACID transactions, row-level locking, crash recovery, foreign keys.

Replication and high availability built in (asynchronous replication, Group Replication, InnoDB Cluster).

Runs everywhere: Windows, Linux, macOS, Docker, Kubernetes, and every major cloud (Amazon RDS/Aurora MySQL, Azure Database for MySQL, Google Cloud SQL, Oracle HeatWave).

Connectors for every language: Python, Java, Node.js, PHP, C#, C/C++, Go, Rust.

Proven at scale: used by major web platforms and countless SaaS products; it is the "M" in the classic LAMP stack (Linux, Apache, MySQL, PHP).

MySQL Architecture Overview MySQL uses a layered architecture: a common SQL layer on top of pluggable storage engines.

Connection layer Accepts connections, authenticates users, applies TLS, assigns a thread per connection

Parses SQL, checks privileges, optimizes (chooses indexes, join order), executes Storage engine layer

Physically stores and retrieves rows; InnoDB provides transactions, locking, and crash recovery Files and logs

Tablespaces (.ibd), redo log, undo log, binary log, error log, slow query log (Older versions also had a query cache; it was removed in MySQL 8.0.)

MySQL follows a client-server model:

MySQL Server (mysqld) is the background process (daemon/service) that owns the data files, listens for connections (default TCP port 3306), and executes SQL.

MySQL Client is any program that connects to the server and sends SQL: the mysql command-line client, MySQL Workbench, MySQL Shell (mysqlsh), or your application using a connector library.

The client never touches the data files directly; it sends SQL text and receives results.

Client and server can be on the same machine (localhost) or on different machines across a network or the internet.

Database, Tables, Rows, Columns, Records, Fields In MySQL, a database is a named container of tables (MySQL treats DATABASE and SCHEMA as synonyms).

Collection of rows with the same structure (one kind of entity) One item: one customer, one order Field, attribute

One property of every row: city, email The value of one column in one row

Rule for allowed values: INT, VARCHAR(100), DATE (Some people say "field" for the column definition and "field value" for the cell; both usages are common.)

Primary Concepts of Relational Databases Tables represent entities (customers, orders, vehicles).

Primary key — a column (or columns) whose value uniquely identifies each row (customer_id).

Foreign key — a column that references a primary key in another table (orders.customer_id → customers.customer_id), creating a relationship and enforcing referential integrity.

Relationships — one-to-one, one-to-many (customer → orders), many-to-many (orders ↔ products via order_items).

Constraints — rules such as NOT NULL, UNIQUE, CHECK that keep data valid.

Normalization — store each fact once to avoid inconsistencies.

Transactions (ACID) — groups of changes that succeed or fail together.

SQL — the declarative language used to define, query, and change data.

NULL — marker for "unknown/missing", not zero or an empty string.

Real-World Applications of MySQL Web and e-commerce Product catalogues, carts, orders, user accounts (WordPress, Magento, many SaaS apps) Banking and fintech

Transactions, ledgers, KYC records (with InnoDB for ACID) Appointments, patient records, lab results

Student information systems, learning platforms Subscriber data, call-detail summaries

Shipments, tracking events, warehouse inventory Vehicle/ECU inventories, software version tracking, diagnostic trouble codes (DTCs), CAN signal databases, HIL test results Embedded / IoT

Collecting telemetry from gateways, device registries, firmware release management

Test case management, execution results, defect tracking back-ends

Operational reporting; with HeatWave, in-database analytics and ML

Real-world example: a vehicle test lab runs thousands of HIL (hardware-in-the-loop) tests per night. Each test run writes a row into a MySQL test_results table with the vehicle, ECU software version, test case, result, and duration. Engineers query the next morning: "Which test cases fail on the newest BMS software?" — a single SQL query answers it.

Advantages and Limitations of MySQL Free Community edition; low total cost

Some enterprise tools (audit, firewall, masking, Enterprise Backup) are commercial Simple installation and administration

Fewer advanced SQL features than PostgreSQL (e.g., no FULL OUTER JOIN, no native materialized views, no partial/filtered indexes, CHECK constraints only since 8.0.16)

High performance for OLTP/web reads and writes Complex analytical queries on huge data are slower than in columnar/analytics engines (unless using HeatWave)

InnoDB: ACID, row locks, MVCC, crash recovery Some behaviors depend on configuration (SQL mode) — lenient settings can silently truncate data

Mature replication and HA (Group Replication, InnoDB Cluster)

DDL is not fully transactional (it commits implicitly), although DDL itself is atomic since 8.0

Huge ecosystem: connectors, ORMs, hosting, cloud services Horizontal write scaling (sharding) requires external tools (Vitess, ProxySQL, application sharding) Cross-platform, well documented

Stored-program language is less powerful than PL/pgSQL or PL/SQL

flowchart TD
    A[Client applications<br/>mysql CLI, Workbench, Python, Java, PHP] -->|TCP 3306 / socket / named pipe| B[Connection layer<br/>authentication, TLS, thread per connection]
    B --> C1
    subgraph SQL[SQL layer]
        C1[Parser] --> C2[Preprocessor / resolver<br/>names, privileges]
        C2 --> C3[Optimizer<br/>chooses execution plan]
        C3 --> C4[Executor]
    end
    C4 --> D[Storage engine API]
    D --> E[InnoDB<br/>default, transactional]
    D --> F[MyISAM / MEMORY / CSV / ARCHIVE]
    E --> G[(Data files .ibd,<br/>redo and undo logs)]
    C4 --> H[(Binary log<br/>replication and PITR)]

flowchart LR
    subgraph Clients
        W[MySQL Workbench]
        CLI[mysql command-line client]
        APP[Web application<br/>Python / Java / PHP]
        TOOL[Test tool / script]
    end
    subgraph Server host
        S[mysqld<br/>MySQL Server process]
        DB[(Databases:<br/>eh_company, vehicle_test, ...)]
        S --- DB
    end
    W -->|SQL over network| S
    CLI -->|SQL| S
    APP -->|SQL via connector| S
    TOOL -->|SQL| S
    S -->|result sets| W

MySQL Server (instance)
 ├── Database: eh_company
 │    ├── Table: customers
 │    ├── Table: orders
 │    └── Table: order_items
 └── Database: vehicle_test
      ├── Table: vehicles
      └── Table: test_results

Table: customers
+-------------+--------------+--------------------+--------+---------+   ← columns (fields)
| customer_id | name         | email              | city   | country |
+-------------+--------------+--------------------+--------+---------+
| 1           | Ananya Gupta | [email protected] | Mumbai | India   |   ← row (record)
| 2           | Ben Carter   | [email protected]    | London | UK      |   ← row (record)
+-------------+--------------+--------------------+--------+---------+
                    ↑
            one value = a field value in a record

Why It Matters

Understanding what MySQL is (and is not) helps you make good technology decisions, talk accurately in interviews ("SQL is the language, MySQL is the product"), and understand error messages that refer to the server, the client, or the storage engine.

Syntax

Your first MySQL statements, typed at the mysql> prompt:

SELECT VERSION();             -- MySQL server version
SELECT NOW();                 -- current date and time on the server
SHOW DATABASES;               -- list databases you can see
SELECT 'Hello, MySQL!' AS greeting;

Example

USE eh_company;
SHOW TABLES;
SELECT customer_id, name, city FROM customers WHERE country = 'India';

Expected Result

+-----------------------+
| Tables_in_eh_company  |
+-----------------------+
| customers             |
| departments           |
| employees             |
| order_items           |
| orders                |
| products              |
+-----------------------+

+-------------+--------------+--------+
| customer_id | name         | city   |
+-------------+--------------+--------+
|           1 | Ananya Gupta | Mumbai |
|           4 | Diya Patel   | Pune   |
+-------------+--------------+--------+
2 rows in set (0.00 sec)

Common Mistakes

Saying "MySQL is a language" — SQL is the language; MySQL is the RDBMS.

Confusing the server (mysqld) with the client (mysql).

Assuming MySQL and MariaDB are identical — they share history but differ in features and system tables.

Assuming MySQL syntax is standard SQL everywhere (LIMIT, backtick quoting, AUTO_INCREMENT are MySQL-specific).

Thinking a table's rows have a guaranteed order without ORDER BY.

Best Practices

Use an LTS release (currently 8.4) for production.

Always use the InnoDB storage engine unless you have a specific reason not to.

Use utf8mb4 as the character set (the default in MySQL 8).

Learn standard SQL first, then MySQL extensions.

Interview Relevance

Common: "What is MySQL?", "SQL vs MySQL?", "DBMS vs RDBMS?", "What is the default storage engine?" (InnoDB), "What is the default port?" (3306), "Who owns MySQL?" (Oracle), "MySQL vs MariaDB?".

Practice Questions

Explain the difference between SQL and MySQL in two sentences.

Draw the MySQL client-server architecture and label where parsing and optimization happen.

Name four clients that can connect to a MySQL server.

Give two advantages and two limitations of MySQL compared with PostgreSQL.

In the customers table, identify a column, a row, and a field value.

Describe a real-world engineering system that could use MySQL and name three of its tables.