MySQL Database Systems Study Reviewer
MySQL Architecture & Core Relational Concepts
MySQL: One of the world's most widely utilized open-source Relational Database Management Systems (RDBMS). Known for its performance, velocity, robustness, and simple compatibility, it acts as a central storage backend for web services, enterprise systems, and data-driven systems.
Relational Database Management System (RDBMS): A database software system that structures data logically inside flat, multi-column tables and maintains explicit relationships across columns using
PRIMARY KEYandFOREIGN KEYlinks.Client-Server Architecture: The underlying network software engine design of MySQL, where the central MySQL database server runs perpetually on a machine, managing data storage files and operations, and multiple client systems connect to it via network channels to issue queries.
InnoDB: The standard default transactional storage engine of MySQL. InnoDB supports transaction-safe processing, full referential checking via
FOREIGN KEYconstraints, and row-level locking to enable concurrent access from multiple clients.MyISAM: An older, non-transactional storage engine in MySQL. While it offers high-speed read-only execution, it lacks transaction safety, ignores
FOREIGN KEYintegrity rules, and locks entire tables during writes, making it unsuitable for highly concurrent write-heavy systems.ACID Transactions: An architectural guarantee ensuring database transaction reliability. Transactions are bundles of SQL operations treated as a single cohesive unit, complying with four core attributes:
Atomicity: The complete block succeeds or is rolled back completely.
Consistency: The database state remains valid throughout execution.
Isolation: Independent execution processes operate without cross-interference.
Durability: Committed modifications survive system crashes and power failures.
Replication: A system capability that automatically duplicates and syncs data transactions from a main master database server to one or more auxiliary slave servers, improving system backup resilience, query load distribution, and database availability.
Common Relational Database Architecture Categories:
Database: A physical logical container holding a collection of schemas, tables, views, procedures, and security indexes. Inside MySQL, each database behaves as an independent container.
Table: A physical structure representing a collection of related records, composed of rows (records) and columns (attributes).
Row: Represents a single complete structured record within a table.
Column: Represents a specific field with its associated storage rules and data types (e.g.,
VARCHAR,INT,DATE).
-- Creating and Opening a Database in MySQL
CREATE DATABASE customer_records;
USE customer_records;
Data Types & Storage Selection Optimization
Choosing optimized, correct data types is critical for database structural health. It limits disk allocation, speeds up indexing, and ensures strict data alignment.
Numeric Data Types:
TINYINT: A very small integer type. Signed range is to ; unsigned range is to . Ideal for status flags, states, or boolean configurations ( or ).
SMALLINT: A small integer type. Signed range is to ; unsigned range is to .
INT / INTEGER: A standard integer type used for general quantities, identifying codes, or primary key increments. Range spans approximately to (specifically to ).
BIGINT: A large integer type for very high numeric scales (e.g., international financial transactions, system logs, or user session counters).
FLOAT / DOUBLE: Floating-point types for real numbers with approximate precision.
FLOATuses single-precision (), whileDOUBLEuses double-precision ().DECIMAL / NUMERIC: An exact fixed-point numeric type where the exact scale and precision are defined by the designer. Ideal for currency, monetary fields, and absolute precision calculations. Example:
DECIMAL(10, 2)guarantees total digits, with dedicated to the fractional part.
Date and Time Data Types:
DATE: Stores calendar dates in the
YYYY-MM-DDformat, without any time zone attachment. Ranges from1000-01-01to9999-12-31.DATETIME: Stores composite date and time in the
YYYY-MM-DD HH:MM:SSformat. Spans1000-01-01 00:00:00to9999-12-31 23:59:59. Independent of local server time zones.TIMESTAMP: Stores timezone-aware date and time values. It records the total seconds elapsed since
1970-01-01 00:00:00 UTC. Values automatically adjust dynamically to correspond with the client's current connection timezone.TIME / YEAR:
TIMEstores time offsets or durations in theHH:MM:SSformat, whileYEARstores four-digit calendar years.
String and Text/Binary Data Types:
CHAR: A fixed-length character string. If an insert contains fewer characters than the predefined length, MySQL pads the empty spaces. Highly optimized for uniform-length strings like country abbreviations, state codes, or fixed hashes.
VARCHAR: A variable-length character string. It allocates only the actual size of the input string plus for length indexing. Essential for variable text fields like names, emails, and address fields.
TEXT: For storing large text-based payloads. Available in sizes:
TINYTEXT,TEXT(up to ),MEDIUMTEXT, andLONGTEXT(up to ).TEXTcolumns cannot have default values and are stored outside the primary table row space.BLOB (Binary Large Object): Stores large unformatted binary data blocks (e.g., media files, document PDFs, or encrypted binary code). Available as
TINYBLOB,BLOB,MEDIUMBLOB, andLONGBLOB.
Keys, Relationship Cardinality & Referential Actions
Core Relational Database Keys:
Primary Key: A column (or column group) that uniquely identifies each record in a table. It cannot contain duplicate values and strictly blocks any
NULLentries, enforcing Entity Integrity.Foreign Key: A column mapping to a parent table's Primary Key. It establishes a relational constraint between columns in a child table and parent table, enforcing Referential Integrity.
Unique Key: Enforces distinct values across all column records in a table to prevent duplication. Unlike primary keys, a Unique Key allows a single
NULLvalue.Composite Key: A primary or unique key made up of two or more columns that together uniquely identify a record, used when a single attribute is insufficient.
Database Relationship Types:
One-to-One (1:1): An association where each record in Table A matches exactly one record in Table B. This is typically implemented by placing the primary key of Table A as a
UNIQUEForeign Key inside Table B.One-to-Many (1:N): The most common database relationship. Each row in Table A can match multiple rows in Table B, but a row in Table B can only relate to a single row in Table A. Implemented by placing Table A's Primary Key as a Foreign Key inside Table B.
Many-to-Many (M:N): An association where multiple rows in Table A can map to multiple rows in Table B. It is structurally impossible to link them directly; instead, a Junction Table (or Bridge Table) is created with Foreign Keys referencing both parent tables.
Advanced Cascade Rules and Referential Actions:
ON DELETE CASCADE: A referential rule. If a record in the parent table is deleted, MySQL automatically and immediately deletes all referencing rows inside the child table, preventing orphan keys.
ON UPDATE CASCADE: If a Primary Key value inside a parent table is modified, MySQL automatically updates the corresponding Foreign Key values inside the referencing child table rows to maintain reference alignment.
SET NULL: If a parent row is deleted or modified, MySQL automatically resets the referencing child row's Foreign Key columns to
NULL, provided those columns are not configured with aNOT NULLconstraint.
-- Configuring Foreign Keys with Cascade Actions
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
MySQL Integrity Constraints & Data Validation
Constraints are defensive validation rules applied to columns. They block invalid or malformed write attempts, protecting database accuracy at the transactional boundary.
NOT NULL: Enforces a mandatory value policy for a column, rejecting any insert or update attempt that omits this column or supplies a
NULLvalue.UNIQUE: Ensures all data within the column remains distinct. Perfect for securing unique credentials like usernames or emails.
PRIMARY KEY: Enforces both
NOT NULLandUNIQUEon the specified column to uniquely identify each row.FOREIGN KEY: Validates that any entered value exists in the referenced parent table, enforcing referential links.
CHECK: Validates inputs against a logical Boolean expression. If an input evaluates to
FALSE, the system rejects the statement. Example:CHECK (unit_price >= 0.00).DEFAULT: Supplies a fallback default value for a column if no explicit value is provided during an
INSERTcommand.
-- Composite Constraints & Validation Rules in Table Creation
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
sku_code VARCHAR(50) UNIQUE NOT NULL,
product_name VARCHAR(100) NOT NULL,
unit_price DECIMAL(10, 2) DEFAULT 0.00 CHECK (unit_price >= 0.00),
stock_quantity INT DEFAULT 0 CHECK (stock_quantity >= 0)
);
Structural Query Language (SQL) Basics & CRUD
SQL commands are categorized based on their functional purpose within the database:
DDL (Data Definition Language): Commands that define, modify, or delete the physical structural schemas of database objects (e.g.,
CREATE,ALTER,DROP,TRUNCATE).DML (Data Manipulation Language): Commands that manipulate and write data within the structured tables without modifying the schema structure (e.g.,
INSERT INTO,UPDATE,DELETE).DQL (Data Query Language): Commands that retrieve and filter data records from tables (e.g.,
SELECT).DCL (Data Control Language): Commands that manage user security access privileges and database permissions (e.g.,
GRANT,REVOKE).Core CRUD Syntax Guides & Best Practices:
INSERT INTO: DML command used to add new rows of data into a table. Values can be added to specific columns or all columns in order of schema definition.
sql -- Inserting records INSERT INTO products (sku_code, product_name, unit_price) VALUES ('SKU-100', 'Optical Mouse', 25.50); SELECT: DQL command to retrieve and filter data rows. Can be combined with
WHEREclauses to filter records, orORDER BYto sort them.sql -- Filtering and retrieving values SELECT product_name, unit_price FROM products WHERE unit_price > 20.00; UPDATE: DML command used to modify existing values in a table. It must be combined with a
WHEREclause; otherwise, all records will be updated globally.sql -- Updating specific row values UPDATE products SET unit_price = 29.99 WHERE sku_code = 'SKU-100'; DELETE: DML command to remove specific rows from a table using a
WHEREfilter. If theWHEREclause is omitted, all records are deleted, but the table structure, columns, and constraints remain intact.sql -- Deleting specific rows DELETE FROM products WHERE sku_code = 'SKU-100';
Database Table Manipulation & ALTER Commands
ALTER TABLE is the primary DDL command used to modify the structural definition of an existing database table. It acts as a parent command for specific sub-commands to modify, add, delete, or rename columns and integrity constraints.
Adding Columns (
ADD COLUMN): Appends a new column to an existing table structure. By default, newly added columns are initialized withNULLvalues for all pre-existing rows.
-- Appending a column post-creation
ALTER TABLE products ADD COLUMN discount_rate DECIMAL(3, 2) DEFAULT 0.00;
```
- **Modifying Column Specifications (`MODIFY COLUMN`)**: Changes the properties of an existing column, such as altering its data type, changing character length, or adding/removing constraints.
sql -- Modifying data type size and constraints ALTER TABLE products MODIFY COLUMN product_name VARCHAR(150) NOT NULL; ```
Renaming Columns and Tables:
RENAME COLUMN: Changes the physical name of an existing column in a table, adjusting system references without altering any underlying data.
-- Renaming columns ALTER TABLE products RENAME COLUMN unit_price TO base_price; ``` - **RENAME TABLE**: DDL command to rename an entire table to adjust system references.sql -- Renaming a table RENAME TABLE products TO store_items; ```
Deleting Columns and Post-Creation Constraints:
DROP COLUMN: Permanently deletes an existing column from a table, immediately and irreversibly erasing all data stored within that column.
-- Dropping a column ALTER TABLE store_items DROP COLUMN discount_rate; ``` - **ADD CONSTRAINT**: Appends a new named constraint (e.g., primary or foreign key) to an existing table structure.sql -- Appending post-creation primary and unique keys ALTER TABLE store_items ADD CONSTRAINT pk_items PRIMARY KEY (product_id); ```
DROP CONSTRAINT: Removes a defined constraint from a table using its constraint name.
sql -- Dropping constraints ALTER TABLE store_items DROP CONSTRAINT pk_items;
Duplicating and Copying Tables:
Copying Structure Only: Utilizes the
LIKEclause to create an exact structural clone of a table (including data types, indexes, and constraints) without copying any data records.
-- Creating structural empty backup CREATE TABLE backup_items LIKE store_items; ``` - **Copying Structure and Data**: Uses an `INSERT INTO ... SELECT` statement to clone both the physical structure and copy all existing rows from the original table to the destination table.sql -- Copying structure with data records CREATE TABLE archive_items LIKE store_items; INSERT INTO archive_items SELECT * FROM store_items; ```
Metadata Inspections & Schema Diagnostics:
DESCRIBE (DESC): Utility command used to display the detailed metadata and physical structure of a table, displaying column names, data types, sizes, nullability, constraints, and default values.
sql -- Inspecting table structure DESCRIBE store_items;
Advanced Destruction Commands & Comparison
DELETE: A transactional DML command that removes rows based on a
WHEREfilter. This statement deletes rows individually, logs each deletion, and preserves the table structure, columns, and constraints.TRUNCATE TABLE: A highly efficient DDL command that removes all rows from a table by deallocating the database storage pages. It maintains the table schema, resets auto-incrementing identity columns, operates faster than
DELETE, and cannot be rolled back.DROP TABLE: A highly destructive DDL command that completely deletes a table's schema, structure, indexes, metadata, and all of its data from the database. Once dropped, the table ceases to exist.
Comparison Matrix of Deletion Strategies:
Feature / Aspect | DELETE Command | TRUNCATE TABLE | DROP TABLE |
|---|---|---|---|
Command Type | Data Manipulation Language ( | Data Definition Language ( | Data Definition Language ( |
Logical Action | Removes specific or all rows based on a | Deallocates database storage pages, emptying the table. | Permanently destroys the table schema, constraints, and data. |
Performance | Slower; row-by-row logging. | Extremely Fast; minimally logged. | Instantaneous; drops structural definitions. |
Rollback Ability | Yes; fully rollable back in transactions. | No; cannot be rolled back under standard execution. | No; completely irreversible. |
Resets Auto-ID | No; values resume from where they stopped. | Yes; resets the increment sequence seed back to . | N/A; the table and sequence are completely destroyed. |
WHERE Clause | Yes; allows targeted row deletion. | No; always purges the entire table structure. | No; target filters are not supported. |