MySQL: دليل شامل لوظائف MySQL المدمجة وتطبيقاتها العملية

-- Standardize Phone Number Formats
SELECT name, REPLACE(phone, '-', '') AS clean_phone FROM users;

-- Standardize Date Formats
SELECT name, STR_TO_DATE(created_at, '%Y/%m/%d') AS clean_date FROM users;

-- Calculate the discounted price and round it off
SELECT 
    product_name,
    price,
    ROUND(price * 0.85, 2) AS sale_price
FROM products;

-- Calculate the number of pages (Round up)
SELECT CEIL(103 / 10) AS total_pages;  -- 11 pages

-- Random sort
SELECT * FROM users ORDER BY RAND() LIMIT 5;

-- Full name (combined)
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;

-- Clean phone numbers
SELECT REPLACE(REPLACE(phone, '-', ''), ' ', '') AS clean_phone FROM users;

-- Extract the email domainSELECT 
    email,
    SUBSTRING(email, LOCATE('@', email) + 1) AS domain
FROM users;

-- Generate order number
SELECT CONCAT('ORD', LPAD(id, 6, '0')) AS order_no FROM orders;

+-------------+------------+
| email       | domain     |
+-------------+------------+
| a@gmail.com | gmail.com  |
| b@163.com   | 163.com    |
+-------------+------------+

-- Check today's data
SELECT * FROM orders WHERE DATE(created_at) = CURDATE();

-- View recent 7 days
SELECT * FROM orders WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY);

-- Monthly Statistics
SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount
FROM orders
GROUP BY month
ORDER BY month;

-- Calculate Age
SELECT 
    name,
    birth_date,
    TIMESTAMPDIFF(YEAR, birth_date, CURDATE()) AS age
FROM users;

+---------+-------------+--------------+
| month   | order_count | total_amount |
+---------+-------------+--------------+
| 2026-01 |         120 |     45000.00 |
| 2026-02 |         135 |     52000.00 |
| 2026-03 |         148 |     58000.00 |
+---------+-------------+--------------+

-- Common Formats
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');           -- 2026-07-03
SELECT DATE_FORMAT(NOW(), '%Y/%m/%d');           -- 2026/07/03
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');           -- 10:30:00
SELECT DATE_FORMAT(NOW(), '%W, %M %d, %Y');     -- Friday, July 03, 2026

-- IF Decision
SELECT 
    name,
    score,
    IF(score >= 60, 'Pass', 'Fail') AS result
FROM students;

-- IFNULL Processing NULL
SELECT 
    name,
    IFNULL(phone, 'No phone') AS phone
FROM users;

-- CASE Multiple conditions
SELECT 
    name,
    score,
    CASE 
        WHEN score >= 90 THEN 'A'
        WHEN score >= 80 THEN 'B'
        WHEN score >= 70 THEN 'C'
        WHEN score >= 60 THEN 'D'
        ELSE 'F'
    END AS grade
FROM students;

+-------+-------+-------+
| name  | score | grade |
+-------+-------+-------+
| Alice |    95 | A     |
| Bob   |    82 | B     |
| Carol |    67 | D     |
| Dave  |    45 | F     |
+-------+-------+-------+