MySQL: التصفية المتقدمة واستعلامات التعبيرات العادية في MySQL

SELECT * FROM users 
WHERE first_name REGEXP '^john|john$|john' 
   OR email REGEXP 'john';

-- Search: Engineering or Sales department, and salary > 5000
SELECT * FROM employees 
WHERE (department = 'Engineering' OR department = 'Sales')
  AND salary > 5000;

-- Search: Salary 5000-8000, not HR department
SELECT * FROM employees 
WHERE salary BETWEEN 5000 AND 8000
  AND department != 'HR';

-- Search: Hired after 2024, or salary > 10000
SELECT * FROM employees 
WHERE hire_date >= '2024-01-01' OR salary > 10000;

-- IN Search
SELECT * FROM products WHERE category_id IN (1, 3, 5, 7);

-- NOT IN Search
SELECT * FROM products WHERE category_id NOT IN (2, 4, 6);

-- Subquery IN
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location = 'Beijing');

-- NOT EXISTS replacement for NOT IN (Better performance)
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Value Range
SELECT * FROM products WHERE price BETWEEN 100 AND 500;

-- Date Range
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-06-30';

-- String Range
SELECT * FROM employees WHERE last_name BETWEEN 'A' AND 'M';

-- NOT BETWEEN
SELECT * FROM products WHERE price NOT BETWEEN 100 AND 500;

-- % Match any number of characters (including 0)
SELECT * FROM users WHERE name LIKE 'J%';        -- Starts with J
SELECT * FROM users WHERE name LIKE '%son';      -- Ends with son
SELECT * FROM users WHERE name LIKE '%john%';    -- Contains john

-- _ Matches exactly one character
SELECT * FROM users WHERE name LIKE 'J_hn';      -- J_hn (John, Johan)

-- Used in combination
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- ESCAPE escaping special characters
SELECT * FROM files WHERE name LIKE '%\_%' ESCAPE '\\';  -- Contains _
SELECT * FROM files WHERE name LIKE '%%' ESCAPE '\\';    -- Contains %

-- Starts with J or j
SELECT * FROM users WHERE first_name REGEXP '^[Jj]';

-- Contains numbers
SELECT * FROM users WHERE username REGEXP '[0-9]';

-- Email format validation (Simple)
SELECT * FROM users WHERE email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$';

-- Mobile phone number format (Mainland China)
SELECT * FROM users WHERE phone REGEXP '^1[3-9][0-9]{9}$';

-- Contains CJK characters
SELECT * FROM users WHERE name REGEXP '[\\u4e00-\\u9fff]';

-- LIKE matches must be at the beginning and end only
SELECT * FROM users WHERE name LIKE '%john%';  -- Contains john

-- REGEXP allows for precise control
SELECT * FROM users WHERE name REGEXP '^john$'; -- Exactly equal to john (Case-insensitive)
SELECT * FROM users WHERE name REGEXP 'john|jane'; -- john or jane
SELECT * FROM users WHERE name REGEXP '^[A-Z]'; -- Starts with a capital letter

-- Query Customers with Orders
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Query Customers with No Orders
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);