/ WEB, BACKEND, DB

DB 및 SQL 기초 - Database Querying

본 게시글의 내용은 KWeb 준회원 스터디 강의 자료를 참조하였다.

CRUD Functions

sql은 테이블에 대하여 CRUD 연산을 수행하는 기능을 제공한다.

Create (Insert)

INSERT INTO <table-name> (<c1>, <c2>, ..., <cn>) VALUES (<v1>, <v2>, ..., <vn>);

table-name 테이블의 c1, ..., cn 컬럼에 v1, ..., vn 값을 넣는다. 생략된 column에 기본값이 정의되어 있을 경우 자동으로 기본값이 들어간다. 모든 column에 값을 삽입할 경우에는 column 명을 전부 생략 가능하다. 또한 VALUES 튜플에서 기본값 삽입을 명시할 때, DEFAULT 키워드를 사용할 수 있다.

Read (Select)

SQL에서 가장 중요하고 복잡한 기능이다.

SELECT <c1>, <c2>, ..., <cn> FROM <table-name> WHERE <condition>

SELECT 문의 가장 기본적인 형태로, 선택된 column들 중에서 condition 을 만족하는 row를 조회하는 기능을 수행한다.

모든 column을 선택할 때에는 column 명을 나열하는 대신 * 키워드를 사용할 수 있다. 또한 조건이 필요하지 않을 경우 WHERE clause를 생략 가능하다.

Advanced Selection

SELECT <c1>, <c2>, ..., <cn>,<e1>,...,<en>,... FROM <table-name> WHERE <condition>

SELECT 문에서 column을 명시하는 부분에는 단순히 column명뿐만 아니라, 간단한 표현식 또는 Aggregation Function도 명시할 수 있다. 예를 들어, .45*<c1> + .55*<c2> column을 선택할 수 있다. .45*<c1> 은 <c1>의 모든 row에 0.45를 곱한 값을 뜻하고, .55*<c2>는 <c2>의 모든 row에 0.55를 곱한 값을 뜻한다. 이 두 값을 더한 값에 해당하는 column은 원본 테이블에 존재하지 않지만, 조회 시에 계산되어 반환되는 것이다.

DISTINCT

SELECT 바로 뒤에 DISTINCT 키워드를 추가하면 유니크한 row만을 반환한다. 예를 들어, SELECT DISTINCT <c1>, <c2> FROM <table-name> 은 <c1>, <c2> column의 모든 row를 조회하여 중복된 row를 제거한 후 반환한다.

AS

AS 키워드로 조회 시 반환되는 column 명을 지정할 수 있다. 예를 들어, SELECT .45*<c1> + .55*<c2> AS <new-c1> 는 new-c1 column을 반환하며, 이 column의 값은 .45*<c1> + .55*<c2> 의 값에 해당한다.

AS는 생략 가능하다. SELECT .45*<c1> + .55*<c2> <new-c1> 과 같이 작성할 수 있다.

ORDER BY

FROM <table-name> 뒤에 ORDER BY <column> + DESC 또는 ASC 를 명시하여 column-name 의 값을 기준으로 내림차순 혹은 오름차순으로 정렬할 수 있다. 기본값은 오름차순이다.

LIMIT

LIMIT <offset> <row-num> 키워드로 offset 번째1 row부터 row-num 개의 row만 선택할 수 있다.

GROUP BY

Aggregate Functions (집계 함수) 참조

LIKE 및 String Operations

WHERE clause에서 LIKE 키워드를 사용하여 문자열을 비교할 수 있다. LIKE 키워드는 = 와 같은 비교 연산자와 달리, 문자열의 일부분만을 비교할 수 있다. LIKE 키워드는 % 와 _ 를 사용하여 문자열을 비교한다.

%: 모든 substring과 매치된다.

_: 한 글자와 매치된다.

문자 % 및 _ 자체를 매치하고 싶을 경우, \ 를 사용하여 escape 시켜야 한다: \%, \_ 이렇게 말이다.

SQL은 이외에도 다양한 String Operation들을 지원한다.

  • CONCAT: 두 문자열을 이어 붙인다.

    • || 를 사용해도 문자열을 이어 붙일 수 있다. 예를 들어, SELECT <c1> || <c2> FROM <table-name> 은 <c1> 과 <c2> column의 모든 row를 조회하여 이어 붙인 값을 반환한다.

  • LENGTH: 문자열의 길이를 반환한다.

  • SUBSTRING: 문자열의 일부분을 반환한다.

  • TRIM: 문자열의 앞뒤 공백을 제거한다.

  • LOWER, UPPER: 문자열을 소문자, 대문자로 변환한다.

WHERE 조건 모음

WHERE 에서 사용 가능한 조건(Predicate)들을 모아보면 다음과 같다.

WHERE clause에서 AND OR 키워드를 사용하면 여러 조건을 만족하는 row를 조회할 수 있다.

BETWEEN: column의 값이 v1과 v2 사이에 있는 row를 조회한다.

Tuple comparison: where (c1, c2) = (v1, v2) 와 같이 사용하여, c1과 c2의 값이 각각 v1과 v2와 같은 row를 조회한다.

IN, NOT IN

WHERE clause에서 IN 키워드를 사용하여, column의 값이 v1, ..., vn 중 하나와 같은 row를 조회할 수 있다. NOT IN 키워드를 사용하면 column의 값이 v1, ..., vn 중 하나와 같지 않은 row를 조회할 수 있다.

이때 v1, ..., vn 대신 Subquery를 사용할 수 있다.

Set Opreations

UNION, INTERSECT, EXCEPT 키워드를 사용하여, 두 개의 SELECT 문의 결과를 합집합, 교집합, 차집합으로 만들 수 있다.

주의할 점은 일반적인 relational algebra에서와 마찬가지로 두 개의 피연산자는 Same arity(동일한 column 수)를 가지며 Compatible type(호환 가능한 데이터 타입)을 가져야 한다는 것이다.

Views

View 는 테이블과 유사한 개념이지만, 실제 데이터를 저장하지 않고 다른 테이블의 데이터를 참조하여 생성된다. 흔히 View 는 특정 테이블의 일부 column만을 선택하여 보여주는 역할을 한다.

SELECT 문을 사용했을 때 반환되는 결과가 바로 View라고 할 수 있다. 매번 SELECT 문을 사용하지 않고 해당 결과를 저장하고 싶으면 View 를 생성하여 사용하면 된다. SELECT문의 shortcut이라고 생각해도 좋다.

CREATE VIEW <view-name> AS <select-statement>

이때 VIEW는 생성된 결과 자체를 저장하지 않고 해당 결과를 생성하는 expression만을 저장한다.

SELECT FROM에서 테이블이 아닌 View를 선택하여 새로운 View를 생성하는 것도 가능하다.

Update

UPDATE <table-name> SET <c1>=<v1>, <c2>=<v2>, ..., <cn>=<vn> WHERE <condition>

UPDATE의 기본적인 형태로, condition을 만족하는 row들의 c1, ..., cn 컬럼을 v1, ..., vn 값으로 업데이트한다.

ALTER TABLE

테이블에 새로운 column을 추가하거나, 사용하지 않는 column을 삭제할 수 있다. ADD 키워드를 사용하여 column을 추가하고, DROP 키워드를 사용하여 column을 삭제한다.

CHANGE 키워드를 사용하여 column의 이름 및 타입을 변경할 수 있다.

MODIFY 키워드를 사용하여 column의 타입만을 변경할 수 있다.

타입 변경 시 주의점은 VARCHAR 타입의 경우 기존 길이에서 늘리는 것만 가능하다는 것이다.

ALTER TABLE <table-name> ADD <column-name> <data-type> <constraint>

새로 생성된 column의 값은 기본값을 지정하지 않았을 경우 모두 null 값이다.

ALTER TABLE <table-name> DROP <column-name>

Delete

DELETE FROM <table-name> WHERE <condition>

명시된 table에서, condition 을 만족하는 row를 전부 삭제하는 구문이다.

Aggregate Functions (집계 함수)

여러 row들로 이루어진 데이터의 집합에 집계 연산을 수행하여, 대푯값을 구하는 함수를 뜻한다. MariaDB 및 MySQL에서는 20가지의 집계 함수를 지원하며, 그 중 7가지 함수만 소개한다.

함수의 인자는 일반적으로 column이다.

  • Avg: 평균값을 반환하는 함수 (계산 시 null값은 제외한다.)

  • Count: NULL이 아닌 값들의 개수를 반환하는 함수

인자로 *을 넘겨준 형태의 Count(*) 는 row의 개수를 반환한다.

이때는 row의 개수를 반환하므로 모든 열이 NULL인 row도 포함하여 개수를 센다.

  • Group_concat: 값들을 연결하여 생성된 문자열을 반환하는 함수

  • Max: 최댓값을 반환하는 함수

  • Min: 최솟값을 반환하는 함수

  • Stddev: 표준편차를 반환하는 함수

  • Sum: 총 합계를 반환하는 함수

relational algebra와 조금 이질적인 부분은 쿼리에서 그룹을 지정하는 부분과 aggregation 함수를 명시하는 부분이 멀리 떨어져 있다는 것이다. aggregation 함수를 적용한 결과가 선택 대상이므로 aggregation 함수는 SELECT 바로 뒤에 나타나고, 함수가 연산을 수행할 대상이 되는 row, 즉 모집단을 한정하는 부분은 FROM <table-name> 뒤에 GROUP BY clause를 사용하여 명시한다.

HAVING clause

WHERE clause는 SELECT 문에서 FROM clause 뒤에 나타나는 조건을 지정하는 반면, HAVING clause는 SELECT 문에서 GROUP BY clause 뒤에 나타나는 조건을 지정한다. HAVING clause는 WHERE clause와 마찬가지로 AND OR 키워드를 사용하여 여러 조건을 지정할 수 있다.

Join Operation

Join은 곱집합을 구하는 연산으로, 두 개 이상의 table을 연결하여, 하나의 table처럼 사용할 수 있도록 하는 연산이다. Join 연산은 Natural Join 외 4가지의 형태를 가진다.

  • (INNER) JOIN: 두 개의 table에서 조건을 동시에 만족하는 row들만을 모두 선택하는 연산, 하나의 table에서만 만족하는 row는 선택되지 않는다. JOIN 연산의 기본 형태로, INNER 키워드는 생략 가능하다.
  • LEFT OUTER JOIN: 왼쪽 table에서 조건을 만족하는 row들을 모두 선택하고, 오른쪽 table에서는 조건을 만족하는 row가 없는 경우에는 NULL 값을 채워서 선택하는 연산
  • RIGHT OUTER JOIN: 오른쪽 table에서 조건을 만족하는 row들을 모두 선택하고, 왼쪽 table에서는 조건을 만족하는 row가 없는 경우에는 NULL 값을 채워서 선택하는 연산

  • FULL OUTER JOIN: 두 개의 table에서 조건을 만족하는 row들을 모두 선택하고, 조건을 만족하지 않는 row는 NULL 값을 채워서 선택하는 연산

  • NATURAL JOIN: 두 개의 table에서 동일한 column을 가지고 있고, 그 column의 값이 동일한 row들을 모두 선택하는 연산

    • 동일한 이름의 column이 여러 개 존재할 경우, 모든 column의 값이 동일한 row들만을 선택하므로 주의해야 한다.

    • 주의할 점은 INNER JOIN과 OUTER JOIN과 분리된 별개의 연산이 아니라 단지 Equality 조건을 생략한 일반적인 JOIN과 같다는 것이다. INNER 또는 OUTER 키워드를 생략하면 일반적인 JOIN과 같이 INNER JOIN 형태로 동작하나, OUTER JOIN 과도 결합하여 사용할 수 있다.

SELECT <columns> FROM <left-table>
(NATURAL) (INNER/LEFT OUTER/RIGHT OUTER) JOIN <right-table> ON <join-condition>
WHERE <condition>

sql에서는 위와 같이 JOIN clause를 사용하여 Join 연산을 수행할 수 있다. JOIN clause는 FROM clause 뒤에 위치하며, ON clause를 사용하여 Join 연산을 수행할 조건을 지정할 수 있다. (=Theta join)

WHERE clause로 명시하여 조회할 수 있는 조건을 ON 으로 대신할 수 있는 경우도 있으며, 이 경우 되도록 ON을 사용하는 편이 속도가 빠르다. 왜냐하면 WHERE clause로 조건을 명시할 경우, 우선 JOIN 이 수행된 테이블이 먼저 생성된 후에 조건을 검사하는 반면 ON clause로 조건을 명시할 경우, JOIN 연산이 수행하여 테이블을 생성하는 과정에서 조건을 검사하기 때문이다.

동일한 이름의 column을 조건으로 지정할 경우 ON 대신 USING <column-name> 키워드를 사용할 수 있다.

Nested Subqueries

SELECT 문 안에 SELECT 문을 사용하여, SELECT 문의 결과를 테이블처럼 사용할 수 있다.

일반적으로 set membership, set comparisons, set cardinality 테스트에 사용된다.

Set Comparison

SELECT <columns> FROM <table-name> WHERE <column> <comparison-operator> (SELECT <column> FROM <table-name> WHERE <condition>)

일반적인 사용법은 위와 같은데, 이때 Subquery에 SOME과 같은 키워드를 추가할 수 있다.

SOME 키워드를 추가하면 Subquery의 결과 중 하나라도 조건을 만족하는 row가 있으면 선택된다. ANY 키워드는 SOME과 동일한 기능을 수행한다. SOME 없이도 Subquery에 MIN, MAX Aggregate function을 사용해서 동일한 기능을 수행할 수 있다.

반대로 ALL 키워드를 추가하면 Subquery의 결과가 모두 조건을 만족하는 경우에만 선택된다.

EXISTS, NOT EXISTS

EXISTS는 Subquery가 비어있지 않은 경우 TRUE를 리턴하고, NOT EXISTS는 Subquery가 비어있는 경우 TRUE를 리턴한다.

시스템 내부적으로 Subquery가 실행되기 전에, 바깥쪽 SELECT 문이 실행되므로, 바깥쪽 SELECT 문에서 지정한 column명을 Subquery에서 사용할 수 있다.

Derived Tables (Relations)

FROM clause에서 SELECT 문을 사용하여, SELECT 문의 결과를 테이블처럼 사용할 수 있다.

이때 FROM clause에 사용된 SELECT 문 결과를 Derived Table 또는 Derived Relation이라고 한다. MySQL에서는 반드시 Derived Table에 AS 키워드로 이름(Alias)을 지정해야 한다.

HAVING clause를 사용하지 않고 먼저 aggregated된 column을 갖는 테이블을 만든 후에 그 column에 대해 WHERE로 조건을 지정함으로써 HAVING과 유사하게 그룹 조건을 지정할 수 있는 특징이 있다.

  1. 0부터 시작하므로 주의해야 한다. ↩