Structured Query Language (SQL) is a standardized programming language designed for managing relational databases. It enables users to define, query, manipulate, and control data within database management systems. The term SQL can be pronounced either letter by letter (/ˌɛsˌkjuːˈɛl/) or as a single word (/ˈsiːkwəl/).

A relational database organizes data into tables where relationships between entities are defined through structured connections. These tables consist of rows (records) and columns (attributes), forming a network of interrelated data points.

Database Management Systems (DBMS) like MySQL, PostgreSQL, MariaDB, Oracle, and SQL Server serve as the core infrastructure that handles all CRUD operations—create, read, update, and delete. Together with the database itself and supporting tools such as drivers and GUI clients, they form a complete database system ecosystem.

Modern systems also include stream processing engines succh as Apache Spark, Flink, and Storm, which extend relational concepts to handle real-time data flows at scale.

SQL statements fall into several categories:

• DDL (Data Definition Language): Used to create, alter, or drop database objects.
• DML (Data Manipulation Language): Handles data insertion, updates, and deletions.
• DQL (Data Query Language): Primarily refers to SELECT statements for retrieving data.
• DCL (Data Control Language): Manages user permissions and access control.

Constraints enforce data integrity rules. Common types include primary keys, unique constraints, foreign keys, NOT NULL, DEFAULT values, and CHECK conditions. Constraints can be queried via:

SELECT * FROM information_schema.table_constraints;

MySQL supports various data types:

Numeric Types

Type	Signed Range	Unsigned Range
TINYINT	-128 to 127	0 to 255
SMALLINT	-32768 to 32767	0 to 65535
MEDIUMINT	-8388608 to 8388607	0 to 16777215
INT / INTEGER	-2147483648 to 2147483647	0 to 4294967295
BIGINT	-9223372036854775808 to 9223372036854775807	0 to 18446744073709551615
DECIMAL(M,D)	Varies	Varies
FLOAT(P)	±3.402823466E+38 to ±1.175494351E-38	0 to 3.402823466E+38
DOUBLE(M,D)	±1.7976931348623157E+308 to ±2.2250738585072014E-308	0 to 1.7976931348623157E+308
BIT(M)	-2^(M-1) to 2^(M-1)-1	0 to 2^M-1
BOOLEAN	N/A	N/A
SERIAL	N/A	1 to 18446744073709551615

String Types

Type	Description
CHAR(M)	Fixed-length string (padded with spaces)
VARCHAR(M)	Variable-length string
TINYTEXT	Up to 255 characters
TEXT	Up to 65,535 characters
MEDIUMTEXT	Up to 16,777,215 characters
LONGTEXT	Up to 4,294,967,295 characters
ENUM('v1','v2',...)	Restricted list of values
SET('v1','v2',...)	Multiple values from a predefined set
BINARY(M)	Binary fixed-length string
VARBINARY(M)	Binary variable-length string
TINYBLOB	Up to 255 bytes
BLOB	Up to 65,535 bytes
MEDIUMBLOB	Up to 16,777,215 bytes
LONGBLOB	Up to 4,294,967,295 bytes

Date and Time Types

Type	Range
DATE	'1000-01-01' to '9999-12-31'
DATETIME	'1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP	'1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC
TIME	'-838:59:59' to '838:59:59'
YEAR	1901–2155 or 0000

When selecting data types, prioritize minimal storage footprint to improve I/O efficiency and reduce disk usage.

Character sets (e.g., utf8mb4, latin1) and collations determine how strings are stored and compared. utf8mb4 supports full Unicode including emojis, while collation defines sorting and comparison behavior—such as case sensitivity.

Database Operations

Create a database:

CREATE DATABASE IF NOT EXISTS myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Modify database settings:

ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Drop a database:

DROP DATABASE IF EXISTS myapp;

List databases:

SHOW DATABASES LIKE '%myapp%';
-- or via data dictionary
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME LIKE '%myapp%';

Table Creation

Define tables using CREATE TABLE with proper column definitions and constraints:

CREATE TABLE trade_user (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    email LONGTEXT,
    age TINYINT UNSIGNED,
    birthday DATETIME,
    created_at DATETIME,
    updated_at DATETIME,
    PRIMARY KEY (id),
    INDEX idx_name (name)
);

CREATE TABLE trade_order (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    order_no BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    total_amount BIGINT UNSIGNED NOT NULL,
    paied_amount BIGINT UNSIGNED NOT NULL
);

Avoid pitfalls like CREATE TABLE ... AS SELECT, which drops indexes and constraints.

Modifying Tables

Use ALTER TABLE to add, modify, or drop columns, indexes, or constraints:

ALTER TABLE trade_user ADD COLUMN status VARCHAR(10) DEFAULT 'active';
ALTER TABLE trade_user DROP COLUMN status;
ALTER TABLE trade_user ADD INDEX idx_age (age);

MySQL 8.0 supports instant DDL operations for certain changes, improving performance on large tables.

Data Insertion

Insert data using three main forms:

-- Basic insert
INSERT INTO trade_user (name, email, age, birthday, created_at, updated_at) VALUES
('Alice', 'alice@example.com', 30, '1992-01-10 08:30:00', NOW(), NOW());

-- Using SET syntax
INSERT INTO trade_user SET name = 'Bob', email = 'bob@example.com';

-- Insert from query
INSERT INTO trade_order (user_id, order_no, created_at, updated_at, total_amount, paied_amount)
SELECT 1000, 1000001, NOW(), NOW(), 2500, 2500
UNION ALL SELECT 1001, 1000002, NOW(), NOW(), 3000, 3000;

Data Deletion

Delete records carefully using DELETE:

DELETE FROM trade_user WHERE id = 1;
-- Use TRUNCATE for full table removal
TRUNCATE TABLE trade_user;

Always backup before deletion.

Querying with SELECT

Basic queries:

SELECT * FROM trade_user;
SELECT name FROM trade_user;
SELECT DISTINCT name FROM trade_user;

Filter using WHERE clauses with comparison (=, !=, <, >), logical operators (AND, OR, NOT), and IN clauses:

SELECT * FROM trade_user WHERE age IN (12, 13);
SELECT * FROM trade_user WHERE age IS NULL;

Use LIKE for pattern matching:

SELECT * FROM trade_user WHERE name LIKE '%al%';

Note: Leading wildcards (%) may not use indexes unless covered by other columns in the query.

Limit results:

SELECT * FROM trade_user LIMIT 10 OFFSET 20000000;

Aggregation Functions

Count rows:

SELECT COUNT(*) FROM trade_user; -- counts all rows
SELECT COUNT(age) FROM trade_user; -- excludes NULLs

Sum values:

SELECT COALESCE(SUM(paied_amount), 0) FROM trade_order;

Average, max, min:

SELECT AVG(paied_amount), MAX(paied_amount), MIN(paied_amount) FROM trade_order;

Joins

Combine tables using:

• INNER JOIN: Returns only matching records
• LEFT JOIN: Includes all left-side records, even if no match
• RIGHT JOIN: Includes all right-side records
• CROSS JOIN: Cartesian product

Example:

SELECT u.name, o.order_no
FROM trade_user u
LEFT JOIN trade_order o ON u.id = o.user_id;

Subqueries

Nested queries can be correlated or non-correlated:

-- Non-correlated
SELECT * FROM trade_order WHERE user_id IN (SELECT id FROM trade_user WHERE id > 100000);

-- Correlated
SELECT u.id, u.name FROM trade_user u
WHERE EXISTS (SELECT 1 FROM trade_order o WHERE o.user_id = u.id);

Sorting

Order results:

ORDER BY age DESC, id ASC;
-- Avoid expressions involving NULL
ORDER BY age + id; -- NULL causes unpredictable behavior

Grouping

Group data with GROUP BY and aggregate functions:

SELECT email, COUNT(*) FROM trade_user GROUP BY email;

Enable ONLY_FULL_GROUP_BY mode to avoid ambiguous queries.

UNION and UNION ALL

Merge result sets:

SELECT 1 AS id UNION SELECT 1; -- removes duplicates
SELECT 1 AS id UNION ALL SELECT 1; -- preserves duplicates

Window Functions

Apply calculations across rows without collapsing groups:

SELECT id, name, 
       ROW_NUMBER() OVER (ORDER BY age) AS rn
FROM trade_user;

NULL Handling

NULL represents missing or unknown data. Key rules:

• NULL = NULL evaluates to UNKNOWN
• Use IS NULL or IS NOT NULL for checks
• In ORDER BY, NULLs are treated as lowest value
• Arithmetic with NULL returns NULL
• Aggregate functions ignore NULL values except COUNT(*), which counts all rows

Indexes and Performance Tuning

Index frequently queried columns to speed up lookups. Use EXPLAIN to analyze query plans and identify bottlenecks.

Transactions

Ensure ACID compliance with transactions:

START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Or ROLLBACK on error

Locking

Understand row-level locks, gap locks, and next-key locks in relation to isolation levels.

Internals

Study join algorithms (nested loop, hash join, merge join), MVCC (Multi-Version Concurrency Control), and how MySQL implements transactional consistency.