(이 글은 MySQL을 기준으로 작성되었습니다.)
Data Manipulation Language
SELECT
- 테이블의 행(들)에 대해, 어떤 열을 조회할 지 등을 적용할 때 사용한다.
- 함수가 쓰일 시, 리턴 값을 보여준다.
- 작성 순서는 SELECT - FROM - WHERE - GROUP BY - HAVING - ORDER BY 이다.
- 내부 실행 순서는 FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY 이다.
- FROM절에 쓰인 테이블을 가져와서,
- WHERE절에 의해 행을 선택한 다음,
- SELECT문에 있는 열 등을, 결과로써 테이블 형태로 보여주는 것이 일반적이다.
- 여러 개의 열을 조회할 시 쉼표로 구분하고, 모든 열을 조회할 시에는 ‘*’ 을 사용한다.
- DISTINCT: 열에 중복되는 값이 있을 시, 첫 번째 행만 보여준다.
- 2개 이상의 열을 조회할 시에는, 다른 열의 값이 다르면 서로 다른 것이라고 취급한다.
- ‘AS {alias}’를 통해, 조회 결과 테이블의 열 이름에 별칭을 적용하여 나타낼 수 있다.
- LIMIT: 결과 테이블에 보여지는 행들의 개수를 상위 n개로 제한할 수 있다.
FROM
- 어떤 테이블로부터 행들을 가져올 지 결정한다.
- ‘AS {alias}’를 통해, 테이블 이름을 별칭으로 줄여서 사용할 수 있다.
JOIN
- 여러 테이블을 연결하여 결합해, 하나의 테이블로 재구성한다.
- ON은 JOIN에 대한 조건으로,
각 테이블에서 연결할 기준이 되는 열(주로 외래키 관계)을 결정한다.- 각 테이블 간에 이름이 같은 열을 사용 시, ‘테이블명.열명’ 으로 특정한다.
- 재구성 테이블의 각 행은, ON 조건에 따라 각 테이블로부터의 행이 합쳐진 것이다.
- 따라서 재구성 테이블의 각 행은 각 테이블의 모든 열에 대한 값을 갖는다.
(NULL은 값이 없다는 말이지만, 이해를 돕기 위해 편의 상 이를 포함해서 이르자.)
- 따라서 재구성 테이블의 각 행은 각 테이블의 모든 열에 대한 값을 갖는다.
- ON은 JOIN에 대한 조건으로,
- INNER, LEFT/RIGHT, FULL이 대표적이다. (JOIN 조건이 ‘=’인, 동등 JOIN 위주로 알아본다.)
- INNER
- 교집합
- 각 테이블의 기준 열 값이 같은 행으로만 이루어진 테이블이 구성된다.
적어도 각 테이블에 모두 존재하는 행으로만 이루어진다는 말이므로,
JOIN에 따른 NULL은 발생하지 않는다.
- LEFT/RIGHT
- 왼쪽/오른쪽 집합 (교집합 포함)
- 왼쪽/오른쪽 테이블의 모든 행을 보여주는 것을 기반으로,
오른쪽/왼쪽 테이블에 기준 열 값이 같은 행이
존재하면 연결하여 결합해서 하나의 행을 온전히 구성하고,
그렇지 않으면 오른쪽/왼쪽 테이블에 대한 열 값은 NULL로 구성한다.
- FULL
- 합집합
- 왼쪽, 오른쪽 테이블의 모든 행을 보여주는 것을 기반으로,
서로 기준 열 값이 같은 행이 존재하면 연결하여 결합해서 행을 온전히 구성하고,
각 테이블 서로 간에 없는 행에 대해서는 그에 대한 열 값은 NULL로 구성한다. - MySQL에서 따로 지원하진 않지만 LEFT, RIGHT를 UNION하여 표현할 수 있다.
- WHERE절에 IS NULL과 함께 사용하여 차집합 등을 표현할 수도 있다.
- INNER
- ‘AS {alias}’를 이용하여 하나의 테이블을 두고 서로 다른 이름으로, self JOIN을 할 수도 있다.
- JOIN대신 FROM절에 여러 테이블을 두고, ON대신 WHERE절로 JOIN 조건을 거는 것도
JOIN의 일종으로 보긴 한다.
WHERE
- 테이블에서 특정 열의 값이 조건에 해당하는 행들만 가져오고 싶을 때 사용한다.
- 연산자
- 비교: >, <, =, !=(<>와 동일)
- 범위: 열 이름 BETWEEN 값 AND 값 (값 <= 열 이름 <= 값 과 동일)
- 집합: 열 이름 (NOT) IN 값의 집합
- 값의 집합은 반드시 괄호로 감싸준다.
- NULL: IS (NOT) NULL
- 패턴: LIKE ‘특정 문자’
- 특정 문자로 시작하면/끝나면 맨 뒤/앞에,
특정 문자를 포함하면 맨 앞과 뒤 모두에 '%'를 붙인다. - 문자 하나를 아무 문자로 대신하고자 할 때는 그 자리에 '_'을 둔다.
- 이 외에도 정규식에서 사용하는 다양한 와일드 문자를 사용할 수 있다.
- 대소문자를 딱히 구분하여 나열하지 않으므로 소문자부터 나타내지만,
BINARY(열 이름)을 사용하면 확실하게 구분한다.
- 특정 문자로 시작하면/끝나면 맨 뒤/앞에,
- 복합: &&(AND), ||(OR), !(NOT)
- 괄호를 사용하여 우선 순위를 적용할 수 있다.
GROUP BY
- 앞서 구해진 테이블의 행들에 대하여 특정 열을 기준으로 동일한 값인 행끼리 그룹을 지어,
여러 그룹이 형성된다. - 주로 집계를 목적으로, HAVING절에서 집계 함수와 함께 사용하는 형태로 쓴다.
- 일반적으로 GROUP BY로 그룹을 묶은 후 SELECT문에는,
그룹을 지을 때 기준으로 사용한 열과 집계 함수만 사용할 수 있다.- 따라서 조회 결과 테이블은 앞서 FROM-WHERE절에서 구해진 행들 각각이 아닌
그 행들이 여럿 묶인 각 그룹에 대한 것이 각각 하나의 행으로 구성되는 것으로,
각 행(그룹)에 대해 열을 조회하거나 집계 함수를 적용한 것이다.- 즉, 이 때 SELECT문에서는, 각 그룹인 행에 대한 열 또는 집계를 조회하는 것이다.
(따라서, 그룹이 아닌 행일 때의 집계와는 다르다.)
- 즉, 이 때 SELECT문에서는, 각 그룹인 행에 대한 열 또는 집계를 조회하는 것이다.
- 그룹을 지을 때 기준으로 사용한 열 대신, scalar sub-query 결과를 이용하기도 한다.
즉, 스칼라 값을 각 행(그룹)에 적용하는 방식이다.- 보통 이 때 sub-query에서는, 그룹을 지을 때 기준으로 사용한 열을 이용하게 된다.
(그리고 이는 곧, sub-query의 테이블에서는 기본키인 관계일 것이다.)
- 보통 이 때 sub-query에서는, 그룹을 지을 때 기준으로 사용한 열을 이용하게 된다.
- 따라서 조회 결과 테이블은 앞서 FROM-WHERE절에서 구해진 행들 각각이 아닌
HAVING
- 조건에 부합하는 그룹만 가져오도록 할 때 사용한다.
- WHERE절과 하는 역할은 비슷하지만, 집계 함수만 사용한다는 점이 다르다.
- 집계 함수를 통해 각 그룹에 대해 집계를 하고, 이에 조건을 걸어 필터링을 한다.
ORDER BY
- 조회 결과 테이블을 나타낼 때, 행들을 특정 열 값을 기준으로 정렬하여 보여준다.
- 기본 값은 오름차순(ASC) 이며, 내림차순(DESC)가 있다.
- 여러 개를 각각의 기준으로 나타낼 수도 있다.
내장 함수
- 상수나 열명을 인수로 받아, 단일 값을 결과로 반환한다.
- 집계 함수, 숫자 함수, 문자 함수, 날짜/시간 함수, IFNULL 등 여러 가지가 있다.
집계 함수
- 앞서 구해진 행들에 대해서 특정 열 값에 대한 집계를 할 수 있다.
- SUM, COUNT, AVG, MAX/MIN 등이 있다.
- 전체 행 개수를 알고자 할 때, COUNT에 ‘*’을 사용하여 알 수 있다.
- NULL 값에 대해서는 집계를 하지 않는다.
- DISTINCT를 사용하여 중복 값에 대한 집계는 한 번만 하도록 할 수 있다.
sub-query
- main-query에 속하여, 괄호로 감싸 작성한 query를 의미한다.
- SELECT문 query로만 사용할 수 있다.
- 끝에 ';'은 두지 않는다.
- SQL문 실행 순서(프웨그하세오)를 따르며,
sub-query가 있는 절에서 main-query보다 먼저 실행되어 처리된다. - correlated sub-query
- sub-query에서 main-query 테이블의 행을 참조하는 형태이다.
- main-query로부터 행을 하나씩 가져다 sub-query를 실행하게 된다.
(즉, sub-query가 먼저 실행되어 처리가 끝나는 것이 아니다.)- main-query로부터의 한 행에 대한 sub-query가 끝나면,
sub-query가 있는 main-query의 해당 절을 한 번 실행하고서,
main-query로부터 그 다음 행을 가져다가 또 다시 sub-query를 실행한다.
이를 main-query의 모든 행에 대해 반복한다. - 최종적으로 sub-query를 마치면, main-query에서는 남은 실행 순서를 따른다.
- main-query로부터의 한 행에 대한 sub-query가 끝나면,
위치에 따른 분류
- sub-query가 main-query에서 쓰인 위치에 따라 다음과 같이 분류한다.
- scalar sub-query
- SELECT문 (이 외에 UPDATE SET절 등)
- 결과 테이블은 단일행-단일열(1X1) 형태이다.
- 단일행 이어야 하므로 WHERE절에서는 보통 기본키,
단일열 이어야 하므로 SELECT문에서는 하나의 열만 조회하도록 한다.
- inline view
- FROM절
- 결과 테이블은 view(기존 테이블로부터 만들어진 임시의 가상 테이블)로 취급한다.
- nested sub-query
- WHERE절 (이 외에 HAVING절 등)
- 일반적으로 sub-query라 함은 이를 일컫는다.
- 결과 테이블의 형태에 따라
main-query의 WHERE절에서 사용할 수 있는 연산자가 다르다.
- 단일행-단일열(1X1): >, <, =, !=
- 다중행-단일열(nX1): (NOT) IN, (NOT) EXISTS, ALL, SOME(ANY)
- 다중행-다중열(nXn): (NOT) IN, (NOT) EXISTS
- scalar sub-query
variable
- 결과 테이블의 행에 번호를 붙이거나,
이에 따라 결과 테이블 행 개수를 조절하는 등에 사용할 수 있다. - ‘SET @변수명:=값;’ 으로, 최초 선언 및 할당한다.
- ‘@변수명’ 으로, 변수를 참조한다.
- ‘@변수명:=식’ 으로, SELECT절에서 재할당한다.
- WHERE절에는 변수에 대한 조건을 두곤 한다.
INSERT
- 테이블에 새로운 행을 삽입한다.
- ‘INSERT INTO 테이블명(열들) VALUES (값들)’ 형태로 사용한다.
- 순서대로 값이 대응되며, 개수는 동일하게 작성해야 한다.
- VALUES절 대신 SELECT문을 이용해, bulk insert도 가능하다.
UPDATE
- 행의 특정 열 값을 수정한다.
- ‘UPDATE 테이블명 SET 열명=값’ 형태로 사용한다.
- 일반적으로는 WHERE절에 기본키와 함께 사용하여 행을 특정한다.
- 쉼표로 구분하여, 한 번에 여러 열의 값을 수정할 수도 있다.
DELETE
- 특정 행을 삭제한다.
- ‘DELETE FROM 테이블명’ 형태로 사용한다.
- 일반적으로는 WHERE절에 기본키와 함께 사용하여 행을 특정한다.
- 물론 다른 곳에서 외래키로써 참조하고 있다면,
CASCADE 등의 옵션이 없는 한 참조 무결성 제약 조건에 의해 삭제되지 않는다.
