[CS] SQL

🔎 SQL(Structured Query Language)이란?

관계형 데이터베이스(RDBMS)에서 데이터를 저장, 조회, 수정, 삭제하기 위한 언어

SQL 기본 구문

1️⃣ DDL (Data Definition Language) : 데이터베이스의 구조를 정의하는 명령어

• CREATE : 데이터베이스, 테이블, 인덱스, 뷰 등을 생성

-- 데이터베이스 생성
CREATE DATABASE 데이터베이스 이름;

-- 테이블 생성
CREATE TABLE 테이블 이름 (
    컬럼1 데이터타입,
    컬럼2 데이터타입,
    컬럼3 데이터타입,
    ...
);

-- 인덱스 생성
CREATE INDEX 인덱스 이름 ON 테이블 이름 (컬럼 이름);

-- 뷰 생성
CREATE VIEW 뷰 이름 AS
SELECT 절 ...

• ALTER : 기존 테이블의 구조 변경

-- 컬럼 추가
ALTER TABLE 테이블 이름 ADD 컬럼이름 컬럼타입;

-- 컬럼 데이터 타입 변경
ALTER TABLE 테이블 이름 MODIFY 컬럼이름 변경할타입;

-- 컬럼 이름 변경
ALTER TABLE 테이블 이름 CHANGE 컬럼이름 바꿀컬럼이름 데이터타입;

-- 컬럼 삭제
ALTER TABLE 테이블 이름 DROP COLUMN 컬럼이름;

-- 기본 키 추가
ALTER TABLE 테이블 이름 ADD PRIMARY KEY (기본 키로 설정할 컬럼 이름);

-- 외래 키 추가
ALTER TABLE 테이블 이름 ADD FOREIGN KEY (외래 키로 설정할 컬럼 이름) REFERENCES 테이블이름(연결할 컬럼 이름);

• DROP : 테이블 삭제

-- 데이터베이스 삭제
DROP DATABASE 데이터베이스 이름;

-- 테이블 삭제
DROP TABLE 테이블 이름;

-- 인덱스 삭제
DROP INDEX 인덱스 이름 ON 테이블 이름;

-- 뷰 삭제
DROP VIEW 뷰 이름;

• TRUNCATE : 데이터 삭제

-- 데이터 삭제
TRUNCATE TABLE 테이블 이름;

TRUNCATE와 DELETE의 가장 큰 차이는 ROLLBACK 가능 여부다.
TRUNCATE는 ROLLBACK이 불가능하지만 DELETE는 ROLLBACK이 가능하다.

2️⃣ DML (Data Manipulation Language) : 데이터 조작을 위한 명령어

• SELECT : 데이터 조회

SELECT 컬럼1, 컬럼2, ...
FROM 테이블 이름
WHERE 조건
ORDER BY 정렬 조건;

• INSERT : 데이터 삽입

INSERT INTO 테이블 이름 (컬럼1, 컬럼2, ...)
VALUES (값1, 값2, ...);

• UPDATE : 데이터 수정

UPDATE 테이블 이름
SET 변경할 내용
WHERE 조건;

• DELETE : 데이터 삭제

DELETE FROM 테이블 이름 WHERE 조건;

3️⃣ DCL (Data Control Language) : 데이터 접근 권한을 제어하는 명령어

• GRANT : 권한 부여

GRANT 부여할 권한 ON 테이블 이름 TO 부여할 계정;

• REVOKE : 권한 회수

REVOKE 회수할 권한 ON 테이블 이름 FROM 회수할 계정;

🔎 JOIN

JOIN의 종류

• 내부 조인 (INNER JOIN)

  • 교차 조인 (CROSS JOIN - CARTESIN JOIN)
  • 등가/동등/동일 조인 (EQUI JOIN)
  • 비등가 조인 (NON-EQUI JOIN)
  • 자연 조인 (NATURAL JOIN)

• 외부 조인 (OUTER JOIN)

  • 완전 외부 조인 (FULL OUTER JOIN)
  • 왼쪽 (LEFT OUTER)
  • 오른쪽 (RIGHT OUTER)

• 셀프 조인 (SELF JOIN)

• 안티 조인 (ANTI JOIN)

• 세미 조인 (SEMI JOIN)

1️⃣ 내부 조인 (INNER JOIN)

두 테이블에서 일치하는 데이터만 조회하는 조인

-- Employees와 Departments 테이블에서 일치하는 DepartmentID만 조회
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

1️⃣.1️⃣ 교차 조인 (CROSS JOIN, CARTESIAN JOIN)

모든 가능한 조합(데카르트 곱, Cartesian Product)을 생성

-- Employees 테이블의 모든 행과 Departments 테이블의 모든 행을 모든 가능한 조합으로 결합
SELECT e.Name, d.DepartmentName
FROM Employees e
CROSS JOIN Departments d;

1️⃣.2️⃣ 등가 조인 (EQUI JOIN)

두 테이블에서 특정 컬럼 값이 같은 경우만 조회

-- ON 대신 WHERE을 사용하여 동일한 값을 가진 행을 조회
SELECT e.Name, d.DepartmentName
FROM Employees e, Departments d
WHERE e.DepartmentID = d.DepartmentID;

1️⃣.3️⃣ 비등가 조인 (NON-EQUI JOIN)

= (등호) 대신 비교 연산자 (<, >, <=, >=) 사용

-- 급여(Salary)가 특정 범위(MinSalary ~ MaxSalary)에 속하는 직원들을 조회
SELECT e.Name, s.SalaryRange
FROM Employees e
JOIN SalaryGrades s ON e.Salary BETWEEN s.MinSalary AND s.MaxSalary;

1️⃣.4️⃣ 자연 조인 (NATURAL JOIN)

공통 컬럼을 자동으로 감지하여 조인

-- Employees와 Departments에서 공통된 컬럼(예: DepartmentID) 을 자동으로 찾아 조인
SELECT * FROM Employees NATURAL JOIN Departments;

★ 명확한 제어가 어렵고, 예상치 못한 조인이 발생할 수 있음

2️⃣ 외부 조인 (OUTER JOIN)

한쪽 테이블에 일치하는 데이터가 없어도 NULL을 포함하여 조회

2️⃣.1️⃣ 왼쪽 외부 조인 (LEFT OUTER JOIN)

왼쪽 테이블의 모든 데이터 + 오른쪽 테이블에서 일치하는 데이터를 조회

-- Employees 테이블의 모든 행을 유지하면서 Departments에서 일치하는 값이 없는 경우 NULL 반환
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

2️⃣.2️⃣ 오른쪽 외부 조인 (RIGHT OUTER JOIN)

오른쪽 테이블의 모든 데이터 + 왼쪽 테이블에서 일치하는 데이터를 조회

-- Departments 테이블의 모든 데이터를 유지하며 일치하지 않는 Employees 데이터는 NULL 반환
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT OUTER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

2️⃣.3️⃣ 완전 외부 조인 (FULL OUTER JOIN)

양쪽 테이블의 모든 데이터를 포함하며, 일치하지 않는 경우 NULL을 포함

★ MySQL은 FULL OUTER JOIN을 지원하지 않으므로 UNION 활용

SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.DepartmentID = d.DepartmentID
UNION
SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.DepartmentID = d.DepartmentID;

3️⃣ 셀프 조인 (SELF JOIN)

같은 테이블을 자기 자신과 조인하는 방식

-- 직원(e1)과 그의 관리자(e2)를 연결하여 조회
SELECT e1.Name AS Employee, e2.Name AS Manager
FROM Employees e1
JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

4️⃣ 안티 조인 (ANTI JOIN)

서브 쿼리내에서 존재하지 않는 데이터만 추출하여 메인 쿼리에서 추출하는 조인

-- 부서 번호가 2 이상이 아닌 직원들 조회
SELECT e.Name
FROM Employees e
WHERE e.DepartmentID NOT IN (SELECT *
			FROM Departments d
    		WHERE d.DepartmentID >= 2);

5️⃣ 세미 조인 (SEMI JOIN)

안티 조인과 반대로 서브 쿼리 내에서 존재하는 데이터만을 가지고 메인 쿼리에서 추출

-- 부서가 존재하는 직원들만 조회
SELECT e.Name
FROM Employees e
WHERE EXISTS (
    SELECT 1 FROM Departments d WHERE e.DepartmentID = d.DepartmentID
);

🔎 SELECT

SELECT column1, COUNT(*)
FROM TableA
WHERE column2 = 'value'
GROUP BY column1
HAVING COUNT(*) > 1
ORDER BY column1 DESC;

SELECT절 처리 순서

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

🔎 GROUP BY

GROUP BY란?

특정 컬럼을 기준으로 데이터를 그룹화하여 집계 함수를 적용할 때 사용하는 것으로, 동일한 값을 가진 행을 하나의 그룹으로 묶고 그룹별로 집계된 데이터를 조회할 때 활용됨

집계 함수 : SUM, COUNT, AVG, MAX, MIN 등

사용법

SELECT 컬럼명, 집계함수(컬럼명)
FROM 테이블명
GROUP BY 그룹화할 컬럼명
HAVING 그룹 조건;

예시

-- 부서별 직원이 3명 이상인 부서 이름과 수 조회
SELECT department, COUNT(*) employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;

HAVING vs WHERE

WHERE은 기본적인 조건절로서 우선적으로 모든 필드를 조건에 둘 수 있다.
하지만 HAVING은 GROUP BY 된 이후 특정한 필드로 그룹화 되어진 새로운 테이블에 조건을 줄 수 있다.

즉, 전체 테이블 자체에서 쿼리를 수행하고 싶다면 WHERE, 전체 테이블을 그룹화한 뒤 해당 그룹에서 조건을 걸어 가져오고 싶다면 HAVING 사용

WHERE는 그룹화 전에 개별 행을 필터링
HAVING은 그룹화한 후 집계 결과를 필터링

이렇게 생각하면 이해하기 더 쉬울 것이다.

🔎 ORDER BY

ORDER BY란?

조회된 결과를 특정 컬럼을 기준으로 정렬할 때 사용하는 것으로, 데이터를 오름차순(ASC) 또는 내림차순(DESC)로 정렬하여 보기 쉽게 정리함

사용법

SELECT 컬럼명
FROM 테이블명
ORDER BY 기준으로 정렬할 컬럼 ASC|DESC;

예시

-- 부서별 직원이 3명 이상인 부서 이름과 수 조회한 후 부서 이름을 기준으로 내림차순
SELECT department, COUNT(*) employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
ORDER BY department DESC;

★ 참고로 오름차순 정렬하는 ASC는 생략 가능하며, 정렬 기준 컬럼이 SELECT절에 있다면 컬럼명 대신 숫자를 사용해도 된다.

-- 부서별 직원이 3명 이상인 부서 이름과 수 조회한 후 부서 이름을 기준으로 내림차순
SELECT department, COUNT(*) employee_count
FROM employees
GROUP BY department
HAVING COUNT(*) >= 3;
ORDER BY 1 DESC;

🔎 CASCADE와 참조 무결성

참조 무결성 (Referential Integrity)

외래키(FK)로 연결된 두 테이블 간 데이터의 일관성을 유지하는 규칙
부모 테이블의 값이 삭제되면 자식 테이블의 값도 삭제할 것인지 설정 가능

CASCADE 옵션

• ON DELETE CASCADE : 부모 데이터 삭제 시, 자식 데이터도 삭제
• ON DELETE SET NULL : 부모 데이터 삭제 시, 자식 데이터를 NULL로 설정
• ON DELETE RESTRICT : 부모 데이터가 참조될 경우 삭제 불가

-- ParentTable의 값이 삭제될 경우 ChildTable의 값도 삭제
ALTER TABLE ChildTable
ADD CONSTRAINT fk_child_parent FOREIGN KEY (parent_id)
REFERENCES ParentTable(id)
ON DELETE CASCADE;

🔎 DROP, TRUNCATE, DELETE 차이점

명령어	삭제 대상	롤백 가능 여부	성능
DROP	테이블 자체 삭제	불가능	가장 빠름
TRUNCATE	테이블 전체 데이터	불가능	빠름 (로그 기록 최소화)
DELETE	특정 행	가능(트랜잭션)	느림 (WHERE 조건 처리)

-- 특정 행 삭제 (ROLLBACK 가능)
DELETE FROM TableA WHERE id = 1;

-- 모든 행 삭제 (ROLLBACK 불가능)
TRUNCATE TABLE TableA;

-- 테이블 자체 삭제 (ROLLBACK 불가능)
DROP TABLE TableA;