Coding Guides
MySQL logo

MySQL Beginner

Relational Database Management System — structured schemas, ACID transactional standard, indexing architectures, and rapid relational querying.

Table of Contents

  1. Database & Table Creation
  2. Core Column Types
  3. Data Manipulation (CRUD)
  4. Filtering with WHERE Clauses
  5. Relational JOIN Operations
  6. Aggregation & Grouping
  7. Subqueries & Nested Filters
  8. Indexes & Performance Control

1 Database & Table Creation

Structured Query Language (SQL) coordinates relational engines. DDL commands like CREATE set up strict target containers, defining keys and relationship requirements early.

sql · schema.sql 
CREATE DATABASE project_db;
USE project_db;

CREATE TABLE users (
    id INT AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

2 Core Column Types

Fields require strict, upfront data allocation definitions to guarantee memory optimization and validate storage integrity across the storage engine blocks.

INT / BIGINT Standard and large numerical values id INT
VARCHAR(N) Variable-length string optimization title VARCHAR(255)
DECIMAL(P, S) Exact fixed-point financial numbers amount DECIMAL(10, 2)
BOOLEAN / TINYINT Logical true or false assertions is_active TINYINT(1)
DATE / DATETIME Calendar dates and precision clocks logged_at DATETIME
TEXT / BLOB Unstructured metadata body payloads description TEXT

3 Data Manipulation (CRUD)

Data mutation operations utilize INSERT, UPDATE, and DELETE. These commands populate tables or alter record sets within safe transactional contexts.

sql · manipulation.sql 
-- 1. Create: Append values into targeted structural matrices
INSERT INTO users (username, email) 
VALUES ('viduxsh', 'ivanvidux@gmail.com');

-- 2. Update: Modify existing records based on specific filters
UPDATE users 
SET username = 'ivan_vidux' 
WHERE id = 1;

-- 3. Delete: Purge matching rows instantly from database blocks
DELETE FROM users 
WHERE id = 1;
**Never omit the WHERE clause in UPDATE or DELETE statements!** Forgetting it applies the operation globally, updating or erasing all rows in the table.

4 Filtering with WHERE Clauses

Isolate and filter tracking metrics by using specific logic operators, string pattern match match mappings, and range boundaries inside your queries.

sql · queries.sql 
-- Extract records meeting advanced comparative parameters
SELECT id, username, email 
FROM users 
WHERE created_at >= '2026-01-01 00:00:00'
  AND email LIKE '%@gmail.com'
ORDER BY created_at DESC
LIMIT 10;

5 Relational JOIN Operations

Relational databases avoid data duplication by splitting metrics across tables. Use JOIN operations to cleanly link related rows back together on shared key boundaries.

sql · joins.sql 
-- Correlate primary data fields and secondary structural vectors
SELECT u.username, t.amount, t.category 
FROM users u
INNER JOIN transactions t ON u.id = t.user_id
WHERE t.status = 'verified';

6 Aggregation & Grouping

Condense multiple matching rows into a single summary block. Group records by shared attributes, and use HAVING statements to filter those combined sets.

sql · aggregation.sql 
SELECT category, SUM(amount) AS total_spent, COUNT(id) AS txn_count
FROM transactions
GROUP BY category
HAVING total_spent > 500.00;
The WHERE clause filters raw input data rows **before any groupings compile**, whereas HAVING filters calculated metrics **after group aggregations finalize**.

7 Subqueries & Nested Filters

You can embed queries inside other queries. This lets you construct dynamic criteria where a nested data search directly controls the main outer filter path.

sql · subqueries.sql 
-- Isolate user contexts whose ledger balances exceed global averages
SELECT username, email 
FROM users 
WHERE id IN (
    SELECT user_id 
    FROM transactions 
    WHERE amount > (
        SELECT AVG(amount) FROM transactions
    )
);

8 Indexes & Performance Control

As tables scale up, looking up values sequentially can stall execution pipelines. Adding structural B-Tree **Indexes** lets the engine quickly map and locate records directly.

sql · optimization.sql 
-- Generate specialized B-Tree indexing nodes over frequent search keys
CREATE INDEX idx_transactions_user_date 
ON transactions (user_id, logged_at);

-- Append EXPLAIN prefix ahead of a statement to review engine parsing footprints
EXPLAIN SELECT * FROM transactions 
WHERE user_id = 42 AND logged_at > '2026-06-01';
With database architecture fundamentals clear, your next steps involve coordinating transactions via **ACID isolation isolation steps** and exploring asynchronous connection layers.
Back to Coding Back to Top