Pagination and Retrieval

To retrieve a specific subset of records sorted by time, use the LIMIT clause. MySQL does not support the TOP keyword found in other dialects.

SELECT * FROM media_assets 
ORDER BY created_at DESC 
LIMIT 10 OFFSET 5;

View Creation and Logic Precedence

Views can encapsulate complex filtering logic. Note that logical OR operations typically have lower precedence than AND, so parentheses are crucial when mixing conditions.

CREATE VIEW featured_posts AS 
SELECT * FROM blog_entries 
WHERE (category_id = 5 AND status = 1) 
   OR (category_id = 5 AND status = 2);

Filtering with Sets and Unions

Use the IN operator for matching against a list of values. To combine results from multiple queries, UNION merges result sets vertically.

SELECT * FROM inventory WHERE sku IN ('A100', 'B200', 'C300');

(SELECT * FROM blog_entries WHERE category_id = 5 AND status = 1) 
UNION 
(SELECT * FROM blog_entries WHERE category_id = 5 AND status = 2);

Advanced Grouping and Aggregation

When analyzing data groups, you may need to filter based on aggregate counts before selecting specific records. For instance, finding the latest entry ID within groups that appear more than twice.

SELECT MAX(entry_id) 
FROM (
    SELECT DISTINCT * FROM blog_entries 
    WHERE category_id IN (
        SELECT category_id FROM blog_entries GROUP BY category_id HAVING COUNT(*) > 2
    )
) AS filtered_groups 
GROUP BY category_id, status;

Employee and Salary Analysis

For hierarchical data such as staff records, finding the most recent hire involves comparing dates against the maximum value.

SELECT * FROM personnel 
WHERE hire_date = (SELECT MAX(hire_date) FROM personnel);

To find the nth most recent hire, use a subquery with distinct dates and limit offsets.

SELECT * FROM personnel 
WHERE hire_date = (
    SELECT hire_date FROM (
        SELECT DISTINCT hire_date FROM personnel ORDER BY hire_date DESC
    ) AS date_list 
    LIMIT 2, 1
);

Joining management and salary tables requires matching employee IDs and ensuring current validity via date ranges.

SELECT DISTINCT s.emp_no, s.salary, s.from_date, s.to_date, d.dept_no 
FROM dept_management d
JOIN salaries s ON s.emp_no = d.emp_no 
WHERE s.to_date = '9999-01-01' AND d.to_date = '9999-01-01';

Include employees without department assignments using LEFT JOIN.

SELECT p.last_name, p.first_name, d.dept_no 
FROM personnel p 
LEFT OUTER JOIN department_assignments d ON p.emp_no = d.emp_no;

String and Type Manipulation

Concatenate strings directly within queries.

SELECT CONCAT('Prefix_', user_id, '_Suffix') FROM users;

Convert data types explicitly using CAST.

SELECT CAST(numeric_value AS CHAR) FROM metrics;

The FIND_IN_SET function locates a string within a comma-separated list.

SELECT * FROM tags WHERE FIND_IN_SET('urgent', priority_list);

Aggregate multiple row values into a single string using GROUP_CONCAT.

SELECT employee_name, GROUP_CONCAT(project_id) 
FROM assignments 
GROUP BY employee_name;

Ranking and Row Number Emulation

MySQL versions prior to 8.0 lack window functions. Ranking can be simulated by counting rows with higher values.

SELECT id, amount, 
(1 + (SELECT COUNT(*) FROM payroll p2 WHERE p2.amount > p1.amount)) AS rank_val 
FROM payroll p1 
ORDER BY rank_val;

Sequential row numbering uses user-defined variables.

SET @row_counter := 0;
SELECT id, (@row_counter := @row_counter + 1) AS row_num 
FROM payroll 
ORDER BY amount;

Note the difference between := (assignment) and = (comparison or assignment in specific contexts).

To emulate the NTILE function, calculate buckets based on row counts.

SET @num := 1;
SELECT id, CEILING(@num := @num + 1 / (SELECT COUNT(*) FROM payroll WHERE employee_name = 'John Doe') * 3) AS bucket 
FROM payroll 
WHERE employee_name = 'John Doe';

Enumerations and Storage Optimization

ENUM types store strings as integers internally, optimizing space.

CREATE TABLE task_status (
    status ENUM('pending', 'active', 'completed') NOT NULL
);
-- Retrieving the internal integer value
SELECT status + 0 FROM task_status;

Store IP addreses as integers using INET_ATON for faster comparison than string storage.

SELECT INET_ATON('192.168.1.1');

For URL indexing, calculate a CRC32 checksum. Use a trigger to maintain this hash automatically.

CREATE TABLE url_index (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(255) NOT NULL,
    url_hash INT UNSIGNED NOT NULL
);

DELIMITER //
CREATE TRIGGER before_url_insert 
BEFORE INSERT ON url_index
FOR EACH ROW 
BEGIN
    SET NEW.url_hash = CRC32(NEW.url);
END//
DELIMITER ;

Query using both the hash and the original string to avoid collisions.

SELECT * FROM url_index 
WHERE url = 'https://example.com' AND url_hash = CRC32('https://example.com');

Architecture and Administration

Sharding strategies often require a abstraction layer to handle routing, consistency, and aggregation across nodes. Tools like database proxies can manage load balancing using algorithms such as round-robin, least connections, or weighted distribution.

Backup strategies vary by engine. mysqldump is standard for logical backups, while ibbackup supports hot backups for InnoDB.

Manage access control via GRANT and REVOKE.

GRANT SELECT ON database.table TO 'user'@'host';
REVOKE SELECT ON database.table FROM 'user'@'host';

Concurrency control relies on mechanisms like Multi-Version Concurrency Control (MVCC). Snapshot isolation provides a consistent view of data at the transaction start. Use FOR UPDATE to lock rows during selection and prevent concurrent modifications.

SELECT * FROM accounts WHERE id = 100 FOR UPDATE;

In stored procedures, handle missing result sets using handlers.

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Use EXISTS for efficient existence checks without returning data.

SELECT * FROM orders o 
WHERE o.customer_id = 5 
AND EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id);