Conditional modifications allow precise control over data changes. To reduce ages for users with four-character usernames:

UPDATE user_profiles SET age = age - 3 WHERE handle REGEXP '^.{4}$';

Update only the first three records, or apply ordering before limiting:

UPDATE user_profiles SET age = age + 10 LIMIT 3;
UPDATE user_profiles SET age = age + 10 ORDER BY user_id DESC LIMIT 3;

Conditional deletions follow similar patterns:

DELETE FROM user_profiles WHERE gender = 'male' ORDER BY age DESC LIMIT 3;

Joins combine data from multiple tables. Without proper joining conditions, Cartesian products occur:

-- Unintentional cross product
SELECT u.user_id, u.handle, r.region_name FROM user_profiles u, regions r;

Filter with explicit join conditions using WHERE or ON:

-- Equi-join
SELECT u.user_id, u.handle, r.region_name 
FROM user_profiles u, regions r
WHERE u.region_id = r.region_id;

-- Explicit INNER JOIN
SELECT u.user_id, u.handle, u.email, u.gender, r.region_name
FROM user_profiles u
INNER JOIN regions r ON u.region_id = r.region_id;

Complex queries combine joins with filtering, grouping, and aggregation:

SELECT u.user_id, u.handle, u.email, u.gender, r.region_name, COUNT(*) AS region_count
FROM user_profiles u
JOIN regions r ON u.region_id = r.region_id
WHERE u.gender = 'male'
GROUP BY r.region_name
HAVING COUNT(*) >= 1
ORDER BY u.user_id ASC
LIMIT 0, 2;

Outer joins preserve non-matching rows. LEFT JOIN returns all left table records plus matching right table data:

SELECT u.user_id, u.handle, u.email, u.gender, r.region_name
FROM user_profiles u
LEFT JOIN regions r ON u.region_id = r.region_id;

Foreign keys enforce referential integrity between parent and child tables. Both must use InnoDB:

CREATE TABLE IF NOT EXISTS divisions (
    division_id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    division_name VARCHAR(20) NOT NULL UNIQUE
) ENGINE=InnoDB;

INSERT INTO divisions (division_name) VALUES ('Engineering'), ('Marketing'), ('Sales');

CREATE TABLE IF NOT EXISTS staff (
    staff_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(20) NOT NULL UNIQUE,
    division_id TINYINT UNSIGNED,
    FOREIGN KEY (division_id) REFERENCES divisions(division_id)
) ENGINE=InnoDB;

Referential actions define behavior on parent changes. CASCADE propagates deletions and updates:

CREATE TABLE staff (
    staff_id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(20) NOT NULL UNIQUE,
    division_id TINYINT UNSIGNED,
    CONSTRAINT fk_division FOREIGN KEY (division_id) 
    REFERENCES divisions(division_id) 
    ON DELETE CASCADE 
    ON UPDATE CASCADE
) ENGINE=InnoDB;

Other actions include SET NULL (sets child key to NULL), RESTRICT (prevents parent changes if children exist), and NO ACTION (equivalent to RESTRICT in MySQL).

Set operations combine results from multiple queries. UNION removes duplicates; UNION ALL preserves them:

SELECT full_name FROM staff 
UNION 
SELECT username FROM user_profiles;

SELECT full_name FROM staff 
UNION ALL 
SELECT username FROM user_profiles;

Subqueries nest queries within clauses. Use IN for set membership:

SELECT staff_id, full_name FROM staff 
WHERE division_id IN (SELECT division_id FROM divisions);

Comparison operators work with single-value subqueries:

SELECT student_id, name FROM students 
WHERE score >= (SELECT threshold FROM honors WHERE level = 1);

EXISTS checks for correlated row existence:

SELECT staff_id, full_name FROM staff s
WHERE EXISTS (SELECT 1 FROM divisions d WHERE d.division_id = s.division_id);

Quantified comparisosn use ANY (or SOME) and ALL:

SELECT student_id, name, score FROM students 
WHERE score >= ANY (SELECT threshold FROM scholarship_levels);

Create tables from query results:

CREATE TABLE score_archive (
    id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    points TINYINT UNSIGNED
) SELECT student_id, score FROM students;

Regular expressions provide pattern matching beyond LIKE. Use REGEXP or RLIKE:

-- Start anchor
SELECT * FROM user_profiles WHERE handle REGEXP '^t';

-- End anchor
SELECT * FROM user_profiles WHERE handle REGEXP 'g$';

-- Any single character
SELECT * FROM user_profiles WHERE handle REGEXP 'R..G';

-- Character classes
SELECT * FROM user_profiles WHERE handle REGEXP '[lto]';
SELECT * FROM user_profiles WHERE handle REGEXP '[^aeiou]';

-- Alternation
SELECT * FROM user_profiles WHERE handle REGEXP 'admin|root|user';

-- Quantifiers
SELECT * FROM user_profiles WHERE handle REGEXP 'se*';      -- Zero or more
SELECT * FROM user_profiles WHERE handle REGEXP 'se+';      -- One or more
SELECT * FROM user_profiles WHERE handle REGEXP 'se{2}';    -- Exactly 2
SELECT * FROM user_profiles WHERE handle REGEXP 'se{1,3}';  -- Range

MySQL supports arithmetic (+, -, *, /, DIV, %), comparison (>, <, =, !=, <=>, IS NULL, BETWEEN, IN), and logical (AND, OR, NOT, XOR) operators.

Mathematical functions include ceiling, floor, modulo, power, rounding, and randomization:

SELECT CEILING(4.2), FLOOR(4.8), MOD(10, 3), POWER(2, 3), 
       ROUND(3.14159, 2), TRUNCATE(3.14159, 2), ABS(-5), 
       PI(), RAND(), SIGN(-10), EXP(1);

String operations handle length, concatenation, case conversion, padding, trimming, and searching:

SELECT CHAR_LENGTH('hello'), LENGTH('hello'),
       CONCAT('a', 'b', 'c'), CONCAT_WS('-', '2024', '01', '01'),
       UPPER('test'), LOWER('TEST'),
       LEFT('abcdef', 3), RIGHT('abcdef', 3),
       LPAD('42', 5, '0'), RPAD('42', 5, '0'),
       TRIM('  test  '), TRIM('x' FROM 'xxxtestxxx'),
       REPEAT('ab', 3), SPACE(5),
       REPLACE('hello world', 'world', 'mysql'),
       STRCMP('a', 'b'), SUBSTRING('abcdef', 2, 3),
       REVERSE('abc'), ELT(2, 'a', 'b', 'c');

Date and time functions extract components and calculate intervals:

SELECT CURDATE(), CURTIME(), NOW(),
       MONTH(NOW()), MONTHNAME(NOW()), DAYNAME(NOW()),
       DAYOFWEEK(NOW()), WEEKDAY(NOW()), WEEK(NOW()),
       YEAR(NOW()), HOUR(NOW()), MINUTE(NOW()), SECOND(NOW()),
       DATEDIFF('2024-12-31', '2024-01-01');

Conditional logic uses IF, IFNULL, and CASE expressions:

SELECT IF(score > 90, 'A', 'B'), IFNULL(middle_name, 'N/A'),
       CASE 
           WHEN score >= 90 THEN 'Excellent'
           WHEN score >= 80 THEN 'Good'
           ELSE 'Average'
       END AS grade
FROM students;

System functions provide metadata:

SELECT VERSION(), CONNECTION_ID(), DATABASE(), USER(),
       CHARSET('test'), COLLATION('test'), LAST_INSERT_ID();

Indexes improve query performance at the cost of write speed. MySQL primarily uses B-tree indexes; hash indexes are only available with MEMORY tables.

Index types include:

• Primary: Unique identifier, clustered
• Unique: Enforces uniqueness on non-primary columns
• Index (Standard): Basic lookup acceleration
• Fulltext: For text search on string columns
• Spatial: For geometric data

Create indexes during table definition:

CREATE TABLE customer_data (
    cust_id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    last_name VARCHAR(50),
    first_name VARCHAR(50),
    profile_text TEXT,
    INDEX idx_email (email),
    UNIQUE INDEX idx_phone (phone),
    INDEX idx_name (last_name, first_name),
    FULLTEXT INDEX idx_profile (profile_text),
    INDEX idx_phone_prefix (phone(10))  -- Prefix index
) ENGINE=InnoDB;

Add or remove indexes on existing tables:

CREATE INDEX idx_last_name ON customer_data(last_name);
ALTER TABLE customer_data ADD INDEX idx_first_name (first_name);
DROP INDEX idx_last_name ON customer_data;

Composite indexes support multiple column lookups. Prefix indexes index only the leading portion of string columns to reduce size.