[MySQL] SQL 코딩테스트용 문법 정리

SQL문

작성 순서 : S-F-W-G-H-O
SELECT (→ DISTINCT) → FROM (→ JOIN) → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

실행 순서 : F-W-G-H-S-O
FROM (→ JOIN) → WHERE → GROUP BY → HAVING → SELECT (→ DISTINCT) → ORDER BY → LIMIT

---

테이블 간 관계 (예시)

erDiagram

    STUDENTS {
        INT id PK
        VARCHAR name
        INT age
        VARCHAR major
    }
    COURSES {
        INT id PK
        VARCHAR title
        INT credits
    }
    ENROLLMENTS {
        INT student_id FK
        INT course_id FK
        VARCHAR grade
    }
    STUDENTS ||--o{ ENROLLMENTS: enrolls
    COURSES  ||--o{ ENROLLMENTS: "is taken by"

---

1. 데이터 조회 (SELECT)

SELECT 컬럼명 FROM 스키마;

-- 전체 컬럼 조회
SELECT * 
  FROM STUDENTS;

-- 특정 컬럼 조회
SELECT name, age 
  FROM STUDENTS ㅁㄴ ;

2. 중복 제거(DISTINCT) & 페이징(LIMIT)

SELECT DISTINCT 컬럼명
FROM 스키마
LIMIT 페이징개수

-- 중복 없이 전공 목록만
SELECT DISTINCT major 
  FROM STUDENTS;

-- 상위 3명만
SELECT * 
  FROM STUDENTS
 LIMIT 3;

3. 필터링 (WHERE + 연산자)

비교연산자 : AND

SELECT * 
  FROM STUDENTS
 WHERE age >= 20
   AND age < 30;

논리 연산자 : OR

SELECT * 
  FROM STUDENTS
 WHERE major = 'AI'
    OR major = 'Math';

패턴검색 : LIKE + %

SELECT * 
  FROM STUDENTS
 WHERE name LIKE '김%';

리스트 비교 : IN + 목록

SELECT * 
  FROM STUDENTS
 WHERE major IN ('AI','CS','Math');

범위 검색 : BETWEEN 값1 AND 값2

SELECT * 
  FROM STUDENTS
 WHERE age BETWEEN 22 AND 25;

NULL 여부 : IS NULL

SELECT * 
  FROM STUDENTS
 WHERE major IS NULL;

4. 정렬 (ORDER BY)

-- 나이 내림차순
SELECT * 
  FROM STUDENTS
 ORDER BY age DESC;

-- 전공 오름차순, 나이는 내림차순
SELECT * 
  FROM STUDENTS
 ORDER BY major ASC, age DESC;

5. 집계 & 그룹화

-- 전체 학생 수
SELECT COUNT(*) AS total_students 
  FROM STUDENTS;

-- 전공별 학생 수, 평균 나이
SELECT major,
       COUNT(*)       AS cnt,
       AVG(age)       AS avg_age,
       MIN(age)       AS min_age,
       MAX(age)       AS max_age
  FROM STUDENTS
 GROUP BY major;

그룹화(GROUP BY) + 조건(HAVING)

-- 그룹화 후 조건 (전공별 2명 이상만)
SELECT major, COUNT(*) AS cnt
  FROM STUDENTS
 GROUP BY major
HAVING COUNT(*) >= 2;

6. 조건 분기 (CASE)

SELECT name,
       age,
       CASE
         WHEN age < 20 THEN '청년'
         WHEN age < 30 THEN '청장년'
         ELSE '중장년'
       END AS age_group
  FROM STUDENTS;

7. 테이블 연결 (JOIN ~ ON)

INNER JOIN

-- 수강 정보와 학생 정보 결합 
SELECT S.name, C.title, E.grade
  FROM ENROLLMENTS E
 INNER JOIN STUDENTS S
    ON E.student_id = S.id
 INNER JOIN COURSES C
    ON E.course_id = C.id;

LEFT JOIN

-- 학생 정보는 모두, 수강 정보는 있을 때만 
SELECT S.name, C.title
  FROM STUDENTS S
  LEFT JOIN ENROLLMENTS E
    ON S.id = E.student_id
  LEFT JOIN COURSES C
    ON E.course_id = C.id;

8. 결과 결합 (UNION)

-- 두 개의 SELECT 결과를 합치되, 중복 제거
SELECT name, major FROM STUDENTS WHERE age < 22
UNION
SELECT name, major FROM STUDENTS WHERE age > 24;

-- 중복도 모두 보고 싶으면 UNION ALL
SELECT name, major FROM STUDENTS WHERE age < 22
UNION ALL
SELECT name, major FROM STUDENTS WHERE age > 24;

9. 문자열·수치·날짜 함수

-- 문자열 자르기
SELECT SUBSTR(name,1,2) AS name_prefix
  FROM STUDENTS;

-- 소수점 반올림
SELECT ROUND(AVG(age), 1) AS avg_age_1decimal
  FROM STUDENTS;

-- 날짜 더하기 (MySQL 예)
SELECT DATE_ADD('2025-06-10', INTERVAL 7 DAY) AS next_week;

---

SQL 흐름 정리

1. FROM: 테이블 지정
2. JOIN: 테이블 연결
3. WHERE: 행 필터링
4. GROUP BY: 그룹화 (집계 전)
5. HAVING: 그룹 필터링
6. SELECT: 반환할 컬럼/함수
7. ORDER BY: 정렬
8. LIMIT: 개수 제한