MySQL: شرح مفصل لقواعد بناء جملة SELECT في استعلامات MySQL…

SELECT 
    customer_name,
    SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2026-06-01' AND order_date < '2026-07-01'
GROUP BY customer_name
ORDER BY total_spent DESC
LIMIT 10;

-- Query all fields（Use with caution in production environments）
SELECT * FROM employees;

-- Query a Specific Field（Recommendations）
SELECT first_name, last_name, salary FROM employees;

-- AS Set Aliases for Keywords
SELECT 
    first_name AS first_name,
    last_name AS last_name,
    salary AS salary
FROM employees;

-- (Omit) AS (same result)
SELECT first_name first_name, last_name last_name FROM employees;

-- If an alias contains spaces, it must be enclosed in quotation marks.
SELECT first_name AS 'First Name' FROM employees;

+--------+--------+--------+
| first_name | last_name | salary |
+--------+--------+--------+
| John   | Smith  | 5000   |
| Jane   | Doe    | 6000   |
+--------+--------+--------+

-- Calculate Annual Salary
SELECT 
    first_name,
    salary,
    salary * 12 AS annual_salary
FROM employees;

-- String Concatenation
SELECT 
    CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

-- Equal Value Query
SELECT * FROM employees WHERE department = 'Engineering';

-- Non-equality query
SELECT * FROM employees WHERE salary != 5000;

-- Range Query
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;

-- List Query
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing', 'HR');

-- Fuzzy Search
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- NULL Search
SELECT * FROM employees WHERE manager_id IS NULL;

-- AND Combination
SELECT * FROM employees 
WHERE department = 'Engineering' AND salary > 6000;

-- OR Combination
SELECT * FROM employees 
WHERE department = 'Sales' OR department = 'Marketing';

-- Mixed Combinations（Use parentheses to specify precedence）
SELECT * FROM employees 
WHERE (department = 'Sales' OR department = 'Marketing')
  AND salary > 5000;

-- NOT Negate
SELECT * FROM employees WHERE NOT (department = 'HR');

-- Single-Field Duplicate Removal
SELECT DISTINCT department FROM employees;

-- Multi-field combination dedup
SELECT DISTINCT department, job_title FROM employees;

-- Count the number of unique entries
SELECT COUNT(DISTINCT department) AS dept_count FROM employees;

+-------------+
| department  |
+-------------+
| Engineering |
| Sales       |
| Marketing   |
| HR          |
+-------------+

-- Single-field ascending order (Default)
SELECT * FROM employees ORDER BY salary ASC;

-- Single-field descending order
SELECT * FROM employees ORDER BY salary DESC;

-- Sorting by Multiple Fields
SELECT * FROM employees 
ORDER BY department ASC, salary DESC;

-- Sort by expression
SELECT 
    first_name,
    salary,
    salary * 12 AS annual
FROM employees
ORDER BY annual DESC;

-- Sort by field position (2nd column)
SELECT first_name, salary FROM employees ORDER BY 2 DESC;

+------------+--------+
| first_name | salary |
+------------+--------+
| Charlie    |   8000 |
| Jane       |   6000 |
| John       |   5000 |
+------------+--------+

-- Query the first 10 rows
SELECT * FROM employees LIMIT 10;

-- Skip the first 10 rows, take 10 rows (Page 2)
SELECT * FROM employees LIMIT 10 OFFSET 10;

-- Shorthand: LIMIT offset, count
SELECT * FROM employees LIMIT 10, 10;  -- Page 2

-- Page numbering formula: LIMIT (page-1) * pageSize, pageSize
-- Page 3, 20 rows per page
SELECT * FROM employees LIMIT 40, 20;

-- Page 1
SELECT * FROM products ORDER BY id LIMIT 0, 10;

-- Page 2
SELECT * FROM products ORDER BY id LIMIT 10, 10;

-- Page 3
SELECT * FROM products ORDER BY id LIMIT 20, 10;

-- Query the top 5 highest-paid employees in Engineering
SELECT 
    CONCAT(first_name, ' ', last_name) AS employee_name,
    department,
    salary,
    hire_date
FROM employees
WHERE department = 'Engineering'
  AND salary IS NOT NULL
ORDER BY salary DESC
LIMIT 5;

-- Query employees hired in 2025, statistics by department
SELECT 
    department,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary
FROM employees
WHERE hire_date BETWEEN '2025-01-01' AND '2025-12-31'
GROUP BY department
ORDER BY avg_salary DESC;