Data Insertion (INSERT)

Adding records to a database table utilizes the INSERT statement. It allows explicit column targeting or full-row insertion. Multiple rows can be added in a single transaction for improved efficiency.

INSERT INTO staff_directory (employee_id, department_code, full_name, office_extension)
VALUES (201, 'ENG', 'Marcus Cole', '5501'), (202, 'MKT', 'Sarah Lin', '5502'), (203, 'FIN', 'David Park', '5503');

If a specific column list is omitted, values must align sequentially with the table's complete schema. When a primary or unique constraint violation occurs, MySQL typically halts execution. To handle conflicts gracefully by updating existing rows instead, append the ON DUPLICATE KEY UPDATE clause:

INSERT INTO staff_directory VALUES (202, 'HR', 'Sarah Lin', '5600')
ON DUPLICATE KEY UPDATE department_code = VALUES(department_code), office_extension = VALUES(office_extension);

Alternatively, the REPLACE statement automatically deletes conflicting rows before inserting new data. If no conflict exists, it funcsions identically to a standard insertion.

Data Retrieval (SELECT)

Fetching information relies on SELECT, frequently combined with filtering, sorting, and pagination clauses.

Eliminating Duplicates (DISTINCT)

Applying DISTINCT filters out identical result combinations. Uniqueness is evaluated across all column listed after the keyword, not per column individually.

-- Removes identical region and category pairs
SELECT DISTINCT region, product_category FROM sales_records;

Filtering Conditions (WHERE)

The WHERE clause restricts results based on logical or comparison criteria. Standard operators include >, <, >=, <=, =, !=/<>, BETWEEN ... AND, IN (...), IS NULL, and IS NOT NULL. For string pattern matching, use LIKE with % (any character sequence) or _ (single character).

Null comparisons require attention: NULL = NULL evaluates to NULL (unknown), whereas <=> serves as a null-safe equality operator. Combine conditions using AND, OR, and NOT.

Sorting Results (ORDER BY)

Arrange output using ORDER BY column_name ASC (ascending, default) or DESC (descending).

Paginaiton (LIMIT and OFFSET)

Restrict the number of returned rows to optimize query performance and enable interface pagination.

-- Skips the first 5 records and returns the next 3
SELECT * FROM sales_records ORDER BY transaction_date LIMIT 3 OFFSET 5;

Data Modification (UPDATE)

Existing records are altered using UPDATE. Always pair this command with a WHERE condition to prevent accidental bulk modifications.

UPDATE project_tasks
SET status = 'ARCHIVED', completion_rate = 100
WHERE due_date < CURDATE() AND status != 'COMPLETED'
ORDER BY priority ASC LIMIT 10;

Data Removal (DELETE vs TRUNCATE)

Removing rows is performed via DELETE. Like UPDATE, it accepts WHERE, ORDER BY, and LIMIT clauses for precise targeting. Omitting WHERE removes every row but retains the table structure and auto-increment counters.

DELETE FROM audit_logs WHERE log_timestamp < '2022-01-01' LIMIT 5000;

For complete table clearing, TRUNCATE TABLE table_name is often preferred. It deallocates data pages directly, bypassing row-by-row deletion. This operation is significantly faster, resets auto-increment sequences, but cannot be rolled back in most storage engines and does not trigger DELETE events.

Aggregate Functions

Aggregates compute a single value from a column set: COUNT(), SUM(), AVG(), MAX(), and MIN(). By default, these functions ignore NULL values.

• COUNT(expr) returns row counts. COUNT(DISTINCT column) tallies unique non-null entries.
• Mathematical aggregates (SUM, AVG) applied to string columns yield 0 and generate warnings.
• To include NULLs in calculations, wrap columns with IFNULL(col, 0) or COALESCE(col, 0).

-- Calculates minimum discount, treating NULLs as zero
SELECT MIN(IFNULL(discount_percentage, 0)) AS lowest_discount FROM promotions;

Grouping Aggregates (GROUP BY & HAVING)

Partitioning datasets into subsets allows per-group calculations. GROUP BY clusters rows sharing identical values in specified columns. Only grouping columns or aggregate functions should appear in the SELECT list.

-- Calculates average salary and headcount per department
SELECT department_code, AVG(monthly_salary), COUNT(employee_id)
FROM payroll_records
GROUP BY department_code;

Multi-column grouping creates hierarchical buckets, such as analyzing metrics by department and job role simultaneously.

Post-Aggregation Filtering (HAVING)

While WHERE filters raw rows before grouping, HAVING evaluates conditions on aggregated results.

-- Identifies departments where the average salary exceeds a specific threshold
SELECT department_code, AVG(monthly_salary) AS dept_avg
FROM payroll_records
GROUP BY department_code
HAVING dept_avg > 75000;