Create relational tables and query them using MySQL syntax and tools.
Before starting: Basic SQL queries and a disposable local database.
Practice project: Build a constrained schema and compare expected and actual query results.
1. MySQL Architecture & Storage Engines (InnoDB vs MyISAM)
- MySQL Introduction: Client-Server Architecture, SQL Standard & MySQL 8.x
- Storage Engines: InnoDB vs MyISAM, Memory & the Pluggable Engine Architecture
- InnoDB Internals: Buffer Pool, Redo Log, Undo Log & Doublewrite Buffer
2. MySQL Data Types & Storage Architecture
- Numeric Data Types: INT, BIGINT, UNSIGNED & Fixed-Point DECIMAL Arithmetic
- String Types & Charsets: VARCHAR vs CHAR, TEXT vs BLOB & utf8mb4 Collation
- Temporal Data Types: DATE, DATETIME vs TIMESTAMP & Automated Timezone Conversions
- Native JSON Data Type: Binary Storage, Operators (->, ->>) & JSON Functions
3. DDL: Database, Table Creation & Constraints
- DDL Statements: CREATE, ALTER, DROP & MySQL 8 Atomic DDL Architecture
- Constraints & Data Integrity: PRIMARY KEY, FOREIGN KEY (CASCADE/RESTRICT), UNIQUE & CHECK
4. DML: High-Performance Data Insertion & Mutation
- High-Performance DML: Multi-Row Bulk INSERT & ON DUPLICATE KEY UPDATE (Upsert)
- Safe UPDATE & DELETE: LIMIT Clamping, ORDER BY & Multi-Table Joins
5. Querying & Filtering with Advanced Operators
- String Pattern Matching: LIKE, ESCAPE & MySQL 8 ICU-Compliant REGEXP_LIKE
- Three-Valued Logic: NULL Semantics, IS NULL, COALESCE & the Null-Safe <=> Operator
6. Sorting, Grouping & Analytical Aggregations
7. Relational Joins & Set Operations
8. Subqueries, Derived Tables & Common Table Expressions (CTEs)
9. Window Functions & Analytical SQL
- Window Functions: OVER(), PARTITION BY, ROW_NUMBER(), RANK() & DENSE_RANK()
- Value & Cumulative Windows: LEAD(), LAG(), Running Totals & Moving Averages
10. Views & Virtual/Generated Columns
11. Stored Procedures, Functions & Control Flow
12. Triggers & Event Scheduler Automation
- Triggers: BEFORE/AFTER Events, OLD & NEW Pseudorecords & Audit Logging
- MySQL Event Scheduler: Automated Recurring Cron Jobs & Table Maintenance
13. Indexing Architecture & EXPLAIN Optimization
- Indexing Architecture: B+Tree Anatomy, Clustered vs Secondary Indexes
- Composite Indexes: Column Ordering & the Crucial Leftmost Prefix Rule
- Covering Indexes: Achieving Zero-Disk-Lookup 'Using Index' Performance
- Query Optimization: EXPLAIN Plan Interpretation, Join Types & EXPLAIN ANALYZE
- Advanced Indexing: Index Condition Pushdown (ICP) & MySQL 8 Multi-Valued Indexes for JSON
14. ACID Transactions, Locks & Concurrency Control
- ACID Properties & InnoDB Architecture: Atomicity, Consistency, Isolation & Durability
- Transaction Isolation Levels: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ & SERIALIZABLE
- MVCC Architecture: Multi-Version Concurrency Control, Read Views & Consistent Non-Locking Reads
- Locking Mechanisms: Record Locks, Gap Locks, Next-Key Locks & Intent Locks
- Deadlock Detection: Wait-For Graphs, Deadlock Logging & Application Retry Patterns
15. Security, Backups, Replication & High Availability
- Security & Access Control: Users, Roles, caching_sha2_password & Least Privilege
- Backup & Disaster Recovery: Logical (mysqldump) vs Physical Backups (XtraBackup/Enterprise)
- Replication Architecture: Asynchronous vs Semi-Sync, GTIDs & Source-Replica Topologies
- High Availability: MySQL InnoDB Cluster, Group Replication & MySQL Shell Router
- Performance Profiling: Performance Schema, sys Schema & Bottleneck Diagnostics
5. Querying & Filtering with Advanced Operators
3. DDL: Database, Table Creation & Constraints
- Table Partitioning: RANGE, LIST, HASH & KEY Partitioning Strategies
- Partition Maintenance: Instant Data Purging with DROP PARTITION & REORGANIZE
2. MySQL Data Types & Storage Architecture
5. Querying & Filtering with Advanced Operators
- Built-in String Functions: CONCAT_WS, SUBSTRING, REPLACE, TRIM & LPAD Formatting
- Date & Time Math: DATE_ADD(), DATEDIFF(), TIMESTAMPDIFF() & DATE_FORMAT()
- Conditional Logic: Searched CASE Expressions, Simple CASE & the IF() Shortcut
- Mathematical & Cryptographic Functions: ROUND, CEIL, MOD, SHA2 & AES_ENCRYPT
15. Security, Backups, Replication & High Availability
1. MySQL Architecture & Storage Engines (InnoDB vs MyISAM)
- Connection Architecture: Thread Pooling, max_connections & Wait Timeouts
- Memory Optimization: Sizing the InnoDB Buffer Pool, Log Buffers & Flush Strategies
13. Indexing Architecture & EXPLAIN Optimization
15. Security, Backups, Replication & High Availability
Check SQL queries with a tiny known dataset
A query that runs can still return the wrong rows. Build a small fixture whose expected result you can calculate by hand. Include a boundary value, a missing value, and an unmatched row when testing joins. Keep fixtures separate from production data. SQLite syntax below is deliberately explicit about its dialect.
SQLite 3; run only in a disposable learning database
CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 50), ('Ravi', 49), ('Mira', NULL);
SELECT name, score
FROM scores
WHERE score >= 50
ORDER BY name;Check the output
name | score
Asha | 50Add a score of 100 and another NULL. Explain why NULL does not pass score >= 50. Adapt the table types when practicing another SQL dialect.
Test database constraints before adding application code
Test both accepted and rejected writes. In a relational database, a primary key prevents duplicate identifiers and a NOT NULL constraint prevents missing required values. Application validation improves feedback but does not replace database constraints. MongoDB uses different mechanisms, including unique indexes and collection validators; do not copy SQL statements into its shell.
SQLite 3 example; concept also applies to other databases using their own syntax
CREATE TABLE lessons (
id INTEGER PRIMARY KEY,
title TEXT NOT NULL
);
INSERT INTO lessons (id, title) VALUES (1, 'Loops');
SELECT id, title FROM lessons;Check the output
id | title
1 | LoopsWrite a checklist for valid insert, duplicate key, missing required field, and update. Translate the checks into your database dialect.
Open the MySQL learning path on DevStudyHub · Play coding games on this blog
No comments:
Post a Comment
Please do not enter any spam link in the comment box.