Back to Blog
DBSQLDMLSELECTINSERTUPDATEDELETESubquery

0x04. SQL 데이터 조작어 (DML)

SQL의 핵심인 DML을 다룬다. SELECT, INSERT, UPDATE, DELETE 문법부터 중첩 서브쿼리, 집합 연산까지 예제와 함께 정리한다.

SQL이란?

SQL(Structured Query Language) 은 관계형 데이터베이스를 기술하고 조작하기 위한 표준 언어다.

SQL의 가장 큰 특징은 선언적(declarative) 이라는 점이다. "어떻게 할지(how)"가 아니라 "무엇을 할지(what)"만 기술하면, DBMS가 최적의 실행 방법을 스스로 결정한다. 이 과정을 쿼리 최적화(Query Optimization) 라고 부른다.

SQL은 크게 다음과 같은 영역으로 나뉜다.

  • DDL(Data Definition Language): 테이블 구조(스키마) 정의
  • DML(Data Manipulation Language): 데이터 조회 및 변경
  • 트랜잭션 제어: 트랜잭션의 시작과 종료를 지정
  • 권한 제어: 테이블, 뷰에 대한 접근 권한 설정
  • 임베디드 SQL / 동적 SQL: 범용 프로그래밍 언어에 SQL을 내장하는 방법 정의

이 글에서는 그중 DML 에 집중한다.

SQL의 간략한 역사

SQL의 기원은 IBM이 System R 프로젝트의 일환으로 개발한 SEQUEL(Structured English Query Language) 이다. 이후 SEQUEL은 SQL로 이름이 바뀌었고, System R은 System/38(1979), SQL/DS(1981), DB2(1983) 로 발전했다.

최초의 상용 SQL 구현은 Relational Software, Inc가 출시한 Oracle V2다. 이 회사가 지금의 Oracle Corporation이다.

이후 ANSI와 ISO가 SQL 표준을 제정했다. SQL-86, SQL-89, SQL-92, SQL:1999 등을 거쳐 현재는 SQL:2016이 최신 표준이다. 대부분의 데이터베이스 시스템은 SQL-92를 기본으로 지원한다.

SELECT - 데이터 조회

SELECT 절은 쿼리 결과에 포함할 속성(attribute) 을 지정한다. 관계 대수의 프로젝션(projection) 연산에 해당한다.

알아둘 점 몇 가지가 있다.

  • SQL의 이름은 대소문자를 구분하지 않는다. Name, NAME, name은 모두 같다. SQL 명령어를 대문자로 쓰는 것은 관례일 뿐이다.

    • 단, MySQL의 lower_case_table_names 옵션을 켜면 대소문자를 구분하게 된다.

  • SQL은 기본적으로 중복을 허용한다 (기본값 = ALL). 중복을 제거하려면 DISTINCT 키워드를 사용한다.

SELECT DISTINCT dept_name
FROM instructor;
  • 별표(*) 는 "모든 속성"을 의미한다.
SELECT *
FROM instructor;
  • FROM 절 없이 리터럴 값을 선택할 수도 있다.
SELECT '437';
  • FROM 절과 함께 리터럴을 쓰면 테이블의 행 수만큼 반복된다.
SELECT 'A'
FROM instructor;

WHERE - 조건 지정

WHERE 절은 결과가 만족해야 할 조건을 지정한다. 관계 대수의 선택(selection) 연산에 해당한다.

논리 연결자 AND, OR, NOT을 사용할 수 있고, 비교 연산자로는 <, <=, >, >=, =, <>를 지원한다. 주의할 점은 SQL에서는 ==이나 !=를 사용하지 않는다는 것이다.

BETWEEN은 범위 비교에 유용하다. 예를 들어 급여가 90,000 이상 100,000 이하인 교수의 이름을 찾으려면 다음과 같이 작성한다.

SELECT name
FROM instructor
WHERE salary BETWEEN 90000 AND 100000;

튜플 비교(Tuple Comparison) 도 가능하다. 여러 속성을 묶어서 한 번에 비교할 수 있다.

SELECT name, course_id
FROM instructor, teaches
WHERE (instructor.ID, dept_name) = (teaches.ID, 'Biology');

FROM - 테이블 지정

FROM 절은 쿼리에 관련된 테이블(릴레이션) 을 나열한다. 관계 대수의 카티션 곱(Cartesian Product) 에 해당한다.

카티션 곱 자체는 실용적이지 않지만, WHERE 절과 결합하면 조인(JOIN) 이 된다. 즉, 카티션 곱 + 선택 = 조인이다.

예를 들어 Music 학과에서 강의한 적이 있는 교수의 이름과 과목 ID를 찾으려면 다음과 같이 작성한다.

SELECT name, course_id
FROM instructor, teaches
WHERE instructor.ID = teaches.ID AND instructor.dept_name = 'Music';

AS - 별칭

AS 키워드는 테이블이나 속성에 별칭(alias) 을 부여한다. 같은 테이블을 여러 번 참조해야 할 때 특히 유용하다.

예를 들어 컴퓨터과학과의 어떤 교수보다 급여가 높은 교수를 찾으려면, 같은 instructor 테이블을 두 번 참조해야 한다.

SELECT DISTINCT T.name
FROM instructor AS T, instructor AS S
WHERE T.salary > S.salary AND S.dept_name = 'Comp. Sci.';

AS 키워드는 생략 가능하다. instructor AS T와 instructor T는 같은 의미다.

NULL 값

튜플의 일부 속성은 NULL 값을 가질 수 있다. NULL은 값이 알려지지 않았거나(unknown) 존재하지 않음을 나타낸다.

NULL을 포함한 산술 연산의 결과는 항상 NULL이다. 예를 들어 5 + NULL은 NULL을 반환한다.

NULL 여부를 확인하려면 IS NULL 또는 IS NOT NULL 술어를 사용한다.

집합 연산

SQL은 UNION, INTERSECT, EXCEPT 세 가지 집합 연산을 지원한다. 이 연산들은 자동으로 중복을 제거한다. 중복을 유지하려면 ALL 키워드를 붙인다.

UNION

두 쿼리 결과의 합집합을 반환한다.

(SELECT course_id FROM teaches WHERE semester = 'Fall' AND year = 2017)
UNION
(SELECT course_id FROM teaches WHERE semester = 'Spring' AND year = 2018);

INTERSECT

두 쿼리 결과의 교집합을 반환한다. 단, MySQL은 INTERSECT를 지원하지 않는다.

EXCEPT

첫 번째 쿼리 결과에서 두 번째 쿼리 결과를 뺀 차집합을 반환한다. MySQL은 EXCEPT를 지원하지 않으므로 NOT IN으로 대체한다.

SELECT course_id
FROM teaches
WHERE semester = 'Fall' AND year = 2017
  AND course_id NOT IN (
    SELECT course_id
    FROM teaches
    WHERE semester = 'Spring' AND year = 2018
  );

문자열 연산

SQL은 문자열 비교를 위한 패턴 매칭 연산자 LIKE를 제공한다.

  • % : 임의의 부분 문자열과 매칭
  • _ : 임의의 한 문자와 매칭

예시를 보면 직관적으로 이해할 수 있다.

패턴의미
'Intro%'"Intro"로 시작하는 모든 문자열
'%Comp%'"Comp"를 포함하는 모든 문자열
'___'정확히 3글자인 문자열
'___%'3글자 이상인 문자열

패턴은 대소문자를 구분한다.

이스케이프 문자는 기본적으로 백슬래시(\)를 사용한다. 예를 들어 "100%"라는 문자열을 찾으려면 다음과 같이 작성한다.

LIKE '100\%' ESCAPE '\'

ESCAPE 키워드로 이스케이프 문자를 직접 지정할 수도 있다.

LIKE '100#%' ESCAPE '#'

그 외에도 SQL은 문자열 연결(||), LOWER(), REGEXP 등 다양한 문자열 함수를 제공한다.

정렬

ORDER BY는 결과를 특정 속성 기준으로 정렬한다. 기본값은 오름차순(ASC) 이며, 내림차순으로 정렬하려면 DESC를 사용한다.

집계 함수

SQL은 다음과 같은 집계 함수(Aggregate Function) 를 제공한다.

함수설명
AVG평균값
MIN최솟값
MAX최댓값
SUM합계
COUNT개수

GROUP BY

GROUP BY는 특정 속성을 기준으로 튜플을 그룹화한다. 주의할 점은, SELECT 절에 나오는 속성 중 집계 함수에 포함되지 않은 속성은 반드시 GROUP BY에 명시해야 한다는 것이다.

다음은 잘못된 쿼리의 예시다. ID가 GROUP BY에 포함되지 않았기 때문이다.

/* erroneous query */
SELECT dept_name, ID, AVG(salary)
FROM instructor
GROUP BY dept_name;

HAVING

HAVING과 WHERE는 모두 조건을 걸지만, 적용 시점이 다르다.

  • WHERE: 그룹이 형성되기 전에 적용
  • HAVING: 그룹이 형성된 후에 적용

LIMIT

결과 행 수를 제한할 때 사용한다.

SELECT * FROM r LIMIT 5;          -- 처음 5개 행 (0~4)
SELECT * FROM r LIMIT 4, 10;      -- 5번째부터 10개 행 (첫 번째 파라미터는 0-indexed)

INSERT - 데이터 삽입

INSERT는 테이블에 새로운 튜플을 삽입한다. 기본 문법은 다음과 같다.

INSERT INTO tablename
VALUES (col1_value, col2_value, ...);

몇 가지 규칙이 있다.

  • 값은 테이블 스키마의 순서와 동일하게 나열해야 한다.
  • 값을 모르면 NULL을 사용한다.
  • 문자열은 작은따옴표로 감싸는 것이 표준이다 (큰따옴표도 허용). 따옴표 안의 값은 대소문자를 구분한다.

특정 컬럼만 지정해서 삽입할 수도 있다.

INSERT INTO tablename (col1_name, col3_name, col4_name)
VALUES (col1_value, col3_value, col4_value);

지정하지 않은 컬럼에는 기본값 또는 NULL이 들어간다.

INSERT INTO student
VALUES ('3003', 'Green', 'Finance', null);

참고로 외래 키(Foreign Key) 제약이 걸려 있으면, 삽입하려는 값이 참조 대상 테이블에 반드시 존재해야 한다.

SELECT 결과를 INSERT

SELECT 쿼리의 결과를 바로 삽입할 수도 있다. 예를 들어 Music 학과에서 144학점을 초과한 학생을 교수로 등록하려면 다음과 같이 작성한다.

INSERT INTO instructor (
  SELECT ID, name, dept_name, 18000
  FROM student
  WHERE dept_name = 'Music' AND total_cred > 144
);

이때 SELECT 문은 INSERT가 실행되기 전에 완전히 평가된다. 따라서 INSERT INTO table1 SELECT * FROM table1 같은 쿼리도 무한 루프 없이 안전하게 동작한다.

UPDATE - 데이터 수정

UPDATE는 기존 튜플의 값을 변경한다.

UPDATE tablename
SET col1_name = new_value, col2_name = new_value, ...
WHERE predicate;

예를 들어 평균 급여보다 낮은 교수에게 5% 인상을 적용하려면 다음과 같다.

UPDATE instructor
SET salary = salary * 1.05
WHERE salary < (SELECT AVG(salary) FROM instructor);

실행 순서에 주의

UPDATE를 여러 번 실행할 때는 순서가 중요하다. 예를 들어 급여 100,000 초과는 3%, 나머지는 5% 인상한다고 하자.

UPDATE instructor SET salary = salary * 1.03 WHERE salary > 100000;
UPDATE instructor SET salary = salary * 1.05 WHERE salary <= 100000;

만약 순서를 바꿔서 5% 인상을 먼저 실행하면, 원래 98,000이던 급여가 102,900이 되어 3% 인상까지 중복 적용될 수 있다.

이런 문제를 피하려면 CASE 문을 사용하는 것이 안전하다.

UPDATE instructor
SET salary = CASE
    WHEN salary <= 100000 THEN salary * 1.05
    ELSE salary * 1.03
  END;

스칼라 서브쿼리를 활용한 UPDATE

서브쿼리의 결과로 값을 갱신할 수도 있다. 다음은 학생의 총 학점을 재계산하는 예시다.

UPDATE student S
SET tot_cred = (
  SELECT SUM(credits)
  FROM takes, course
  WHERE takes.course_id = course.course_id AND
    S.ID = takes.ID AND
    takes.grade <> 'F' AND
    takes.grade IS NOT NULL
);

DELETE - 데이터 삭제

DELETE는 조건에 맞는 튜플을 제거한다.

-- 특정 행 삭제
DELETE FROM tablename
WHERE predicate;

-- 모든 행 삭제
DELETE FROM tablename;

-- 또는
TRUNCATE TABLE tablename;

예를 들어 Watson 건물에 있는 학과의 교수를 모두 삭제하려면 다음과 같다.

DELETE FROM instructor
WHERE dept_name IN (
  SELECT dept_name
  FROM department
  WHERE building = 'Watson'
);

DELETE와 서브쿼리의 평가 순서

다음 쿼리를 보자.

DELETE FROM instructor
WHERE salary < (SELECT AVG(salary) FROM instructor);

삭제가 진행되면 평균 급여가 바뀌므로 문제가 될 수 있다. SQL의 해결 방식은 다음과 같다.

  1. 먼저 AVG(salary)를 계산하고, 삭제 대상 튜플을 모두 식별한다.
  2. 그 후 식별된 튜플을 한 번에 삭제한다 (AVG를 다시 계산하지 않는다).

중첩 서브쿼리 (Nested Subquery)

서브쿼리(Subquery) 는 다른 쿼리 안에 포함된 SELECT-FROM-WHERE 표현식이다. SQL의 세 절 모두에서 사용할 수 있다.

  • FROM 절: 릴레이션 rir_i 자리에 유효한 서브쿼리를 넣을 수 있다.
  • WHERE 절: 조건부에 속성 연산자 (서브쿼리) 형태로 사용한다.
  • SELECT 절: 단일 값을 반환하는 서브쿼리(스칼라 서브쿼리)를 넣을 수 있다.

FROM 절의 서브쿼리

평균 급여가 42,000을 초과하는 학과의 이름과 평균 급여를 찾는 예시다.

SELECT D.dept_name, D.avg_salary
FROM (
  SELECT dept_name, AVG(salary) AS avg_salary
  FROM instructor
  GROUP BY dept_name
) AS D
WHERE D.avg_salary > 42000;

이 쿼리는 GROUP BY dept_name HAVING AVG(salary) > 42000과 동일한 결과를 반환한다.

WITH 절 - 임시 릴레이션

WITH 절은 임시 릴레이션(temporary relation) 을 정의한다. 쿼리가 종료되면 자동으로 해제된다.

예를 들어 최대 예산을 가진 학과를 찾으려면 다음과 같이 작성한다.

WITH max_budget (value) AS (
  SELECT MAX(budget)
  FROM department
)
SELECT department.dept_name
FROM department, max_budget
WHERE department.budget = max_budget.value;

여기서 max_budget은 value라는 하나의 컬럼을 가진 임시 테이블이다.

스칼라 서브쿼리 (Scalar Subquery)

스칼라 서브쿼리는 단일 값이 기대되는 위치에 사용한다. 서브쿼리가 둘 이상의 결과를 반환하면 런타임 에러가 발생한다.

각 학과와 해당 학과의 교수 수를 함께 조회하는 예시다.

SELECT dept_name,
  (SELECT COUNT(*)
   FROM instructor
   WHERE department.dept_name = instructor.dept_name
  ) AS num_instructors
FROM department;

집합 멤버십 (SOME, ALL, EXISTS)

IN - 교집합

IN은 서브쿼리 결과에 포함되는지 여부를 검사한다. 집합의 교집합을 구하는 데 활용할 수 있다.

SELECT DISTINCT course_id
FROM teaches
WHERE semester = 'Fall' AND year = 2017 AND
  course_id IN (
    SELECT course_id
    FROM teaches
    WHERE semester = 'Spring' AND year = 2018
  );

NOT IN - 차집합

NOT IN은 서브쿼리 결과에 포함되지 않는지 여부를 검사한다.

SELECT DISTINCT name
FROM instructor
WHERE name NOT IN ('Mozart', 'Einstein');

튜플 단위의 NOT IN도 가능하다. ID가 10101인 교수가 가르친 과목을 수강한 고유 학생 수를 구하는 예시다.

SELECT COUNT(DISTINCT ID)
FROM takes
WHERE (course_id, sec_id, semester, year) IN (
  SELECT course_id, sec_id, semester, year
  FROM teaches
  WHERE teaches.ID = 10101
);

SOME - 하나라도 만족

SOME은 서브쿼리 결과 중 하나라도 비교 조건을 만족하면 참을 반환한다.

Biology 학과의 어떤 교수보다 급여가 높은 교수를 찾는 예시다.

SELECT name
FROM instructor
WHERE salary > SOME(
  SELECT salary
  FROM instructor
  WHERE dept_name = 'Biology'
);

= SOME은 IN과 동일하고, <> SOME은 NOT IN과 동일하지 않다는 점에 주의한다.

ALL - 모두 만족

ALL은 서브쿼리 결과의 모든 값에 대해 비교 조건을 만족해야 참을 반환한다.

Biology 학과의 모든 교수보다 급여가 높은 교수를 찾는 예시다.

SELECT name
FROM instructor
WHERE salary > ALL (
  SELECT salary
  FROM instructor
  WHERE dept_name = 'Biology'
);

<> ALL은 NOT IN과 동일하고, = ALL은 IN과 동일하지 않다.

EXISTS - 존재 여부

EXISTS는 서브쿼리의 결과가 비어 있지 않으면 참을 반환한다. 상관 서브쿼리(correlated subquery)와 함께 자주 사용된다.

2017년 가을학기와 2018년 봄학기에 모두 개설된 과목을 찾는 예시다.

SELECT course_id
FROM teaches AS T1
WHERE semester = 'Fall' AND year = 2017 AND
  EXISTS (
    SELECT *
    FROM teaches AS T2
    WHERE semester = 'Spring' AND year = 2018 AND
      T1.course_id = T2.course_id
  );

NOT EXISTS

NOT EXISTS는 서브쿼리의 결과가 비어 있으면 참을 반환한다. "모든 ~를 만족하는" 조건을 표현할 때 유용하다.

Music 학과에서 제공하는 모든 과목을 수강한 학생을 찾는 예시다.

SELECT DISTINCT S.ID, S.name
FROM student AS S
WHERE NOT EXISTS (
  SELECT course_id
  FROM course
  WHERE dept_name = 'Music' AND
    course_id NOT IN (
      SELECT T.course_id
      FROM takes AS T
      WHERE S.ID = T.ID
    )
);

일부 시스템은 EXCEPT 절을 지원하지만, MySQL은 지원하지 않으므로 위처럼 NOT EXISTS + NOT IN 조합을 사용한다.

UNIQUE - 중복 검사

UNIQUE는 서브쿼리 결과에 중복 튜플이 없는지 검사한다. 중복이 없으면 참을 반환한다. 단, MySQL은 UNIQUE 테스트를 지원하지 않는다 (MySQL에서 UNIQUE는 제약 조건 지정자로만 사용된다).

2017년에 최대 한 번만 개설된 과목을 찾는 예시다.

SELECT T.course_id
FROM course AS T
WHERE UNIQUE (
  SELECT R.course_id
  FROM teaches AS R
  WHERE T.course_id = R.course_id AND R.year = 2017
);

HGU 전산전자공학부 홍참길 교수님의 23-1 Database System 수업을 듣고 작성한 포스트이며, 첨부한 모든 사진은 교수님 수업 PPT의 사진 원본에 필기를 한 수정본입니다.

데이터베이스 시스템 시리즈8편