SQL 레퍼런스

-- SQL 논리적 실행 순서 (작성 순서와 다름!)
-- 1. FROM     — 테이블 지정
-- 2. JOIN     — 테이블 결합
-- 3. WHERE    — 행 필터링
-- 4. GROUP BY — 그룹화
-- 5. HAVING   — 그룹 필터링
-- 6. SELECT   — 열 선택
-- 7. DISTINCT — 중복 제거
-- 8. ORDER BY — 정렬
-- 9. LIMIT    — 결과 개수 제한

-- 예시
SELECT department, AVG(salary) AS avg_sal  -- 6
FROM employees                              -- 1
WHERE hire_date >= '2023-01-01'            -- 3
GROUP BY department                         -- 4
HAVING AVG(salary) > 50000                  -- 5
ORDER BY avg_sal DESC                       -- 8
LIMIT 5;                                    -- 9

-- 문자열 함수
SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    UPPER(name) AS upper_name,
    LOWER(name) AS lower_name,
    LENGTH(name) AS name_length,
    SUBSTRING(name, 1, 3) AS short_name,
    TRIM(name) AS trimmed,
    REPLACE(phone, '-', '') AS clean_phone
FROM employees;

-- 날짜 함수
SELECT
    NOW() AS current_datetime,
    CURDATE() AS current_date_only,
    DATE_FORMAT(hire_date, '%Y년 %m월 %d일') AS formatted,
    DATEDIFF(NOW(), hire_date) AS days_worked,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS anniversary
FROM employees;

-- 조건 함수
SELECT name,
    COALESCE(phone, '번호없음') AS phone,
    IFNULL(manager_id, 0) AS manager,
    CASE
        WHEN salary >= 70000 THEN '상'
        WHEN salary >= 50000 THEN '중'
        ELSE '하'
    END AS salary_grade
FROM employees;