Database

[Database] PostgreSQL Commands - DML

kahnco 2024. 7. 17. 01:42
반응형

개요

지난 시간에는 PostgreSQL 데이터베이스의 DDL 에 대해서 알아보았습니다. 이번 시간에는 PostgreSQL의 DML에 대해서 알아보겠습니다.

Inserting Data

기본적인 문법은 다음과 같습니다.

INSERT INTO products VALUES (1, 'Cheese', 9.99);

 

테이블의 열 순서와 반드시 일치하게 데이터를 작성해야하는 방식이기 때문에 불편할 수 있습니다. 다음과 같이 열 이름을 명시적으로 선언하여 해당 문제를 해소할 수 있습니다.

INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

 

만약, 해당 테이블의 모든 열 값이 작성되지 않았다면, 시스템에서는 자동으로 해당 열의 기본 값으로 채워줍니다.

 

또한, 쿼리의 결과문을 주입하는 것 또한 가능합니다. (쿼리 결과문이 0개, 1개, 여러 개여도 가능합니다)

INSERT INTO products (product_no, name, price)
  SELECT product_no, name, price FROM new_products
    WHERE release_date = 'today';

Updating Data

특정 레코드의 값을 수정하기 위해서는 해당 레코드의 UNIQUE 값을 알고 있어야 합니다. 그렇지 않다면 명시적으로 선언된 혹은 모든 레코드에 대해서 값이 수정되게 됩니다.

UPDATE products SET price = 10 WHERE price = 5;

UPDATE products SET price = price * 1.10;

 

또한, 여러 개의 열 값을 변경할 수도 있습니다.

UPDATE mytable SET a = 5, b = 3, c = 1 WHERE a > 0;

Deleting Data

DELETE FROM products WHERE price = 10;

Returning Data from Modified Rows

일반적으로, INSERT, UPDATE, DELETE 는 적용된 레코드의 값을 반환시키지 않습니다. 하지만 해당 값으로 연속적인 작업을 해야하는 경우라면 반환받는 것이 유리할 수 있습니다.

CREATE TABLE users (firstname text, lastname text, id serial primary key);

INSERT INTO users (firstname, lastname) VALUES ('Joe', 'Cool') RETURNING id;

 

UPDATE 의 경우라면, 업데이트 이후의 값을 반환 받습니다.

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

 

DELTE 의 경우라면, 삭제된 레코드를 반환 받습니다.

DELETE FROM products
  WHERE obsoletion_date = 'today'
  RETURNING *;

Queries

개요

기본적인 SELECT 구문의 문법은 다음과 같습니다.

SELECT * FROM table1;

// b와 c 열이 숫자 타입일 경우, 이렇게 계산할 수도 있습니다
SELECT a, b + c FROM table1;

Table Expressions

테이블 식은 테이블을 계산합니다. 테이블 식은 FROM 절을 포함하고 선택적으로 WHERE, GROUP BY, HAVING 절이 뒤따릅니다. 사소한 테이블 식은 단순히 디스크 상의 테이블, 소위 기본 테이블을 가리키지만, 더 복잡한 표현은 기본 테이블을 다양한 방식으로 수정하거나 결합하는데 사용될 수 있습니다.

 

테이블 식의 옵션인 WHERE, GROUP BY, HAVING 절은 FROM 절에서 파생된 테이블에서 수행되는 연속적인 변환의 파이프라인을 지정합니다. 이 모든 변환은 쿼리의 출력 행을 계산하기 위해 SELECT 목록에 전달되는 행을 제공하는 가상 테이블을 생성합니다.

FROM Clause

FROM 절은 쉼표로 구분된 테이블 참조 목록에 있는 하나 이상의 다른 테이블에서 테이블을 가져옵니다.

FROM table_reference [, table_reference [, ...]]

 

테이블 참조는 테이블 이름일 수도 있고, 서브 쿼리, JOIN 구성 요소 또는 이들의 복합적인 조합과 같은 파생된 테이블일 수도 있습니다. FROM 절에 테이블 참조가 둘 이상 나열되면, 테이블은 CROSS-JOIN 됩니다. (즉 행의 데카르트 곱이 형성됨) FROM 목록의 결과는 중간- 가상 테이블이며, 이후 WHERE, GROUP BY, HAVE 절에 의해 변환될 수 있으며, 이는 최종적으로 전체 테이블 식의 결과입니다.

 

Joined Tables

 

조인된 테이블은 특정 조인 유형의 규칙에 따라 두 개의 다른(Real or Derived) 테이블에서 파생된 테이블입니다. INNER, OUTER, CROSS JOIN 을 사용할 수 있습니다. 조인된 테이블의 일반적인 표현식은 다음과 같습니다.

T1 join_type T2 [ join_condition ]

 

  • Cross Join
T1 CROSS JOIN T2

 

위 구문처럼, T1 테이블과 T2 테이블 행의 가능한 모든 조합에 대해 JOIN 되며, T1의 모든 열과 T2의 모든 열로 구성된 행이 포함됩니다. 테이블에 각각 N개와 M개의 행이 있으면 결합된 표에는 N*M 개의 행이 포함됩니다.

FROM T1 CROSS JOIN T2 는 FROM T1 INNER JOIN T2 ON TRUE 와 같은 결과값을 가집니다. 이는 FROM T1, T2 와 같습니다.
또한, JOIN 구문이 쉼표(,) 보다 더 제한적으로 바인딩되기 때문에, 만약 조인에 참여하는 테이블이 여러 개일 경우에는 기준 테이블이 명확해지지 않을 수 있습니다. 예를 들어 FROM T1 CROSS JOIN T2 INNER JOIN T3 ON 조건은 첫 번째의 경우에는 T1 을 참조할 수 있지만 두 번째 경우에는 참조할 수 없기 때문에 FROM T1, T2 INNER JOIN T3 ON 조건과 동일하지 않습니다.

 

  • Qualified Joins
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 ON boolean_expression
T1 { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2 USING ( join column list )
T1 NATURAL { [INNER] | { LEFT | RIGHT | FULL } [OUTER] } JOIN T2

 

INNER와 OUTER 이라는 단어는 모든 형식에서 선택적으로 사용됩니다. INNER 값이 기본 값이고, LEFT, RIGHT, FULL 구문은 OUTER 조인의 경우에 사용됩니다.

JOIN 조건은 ON 과 USING 구문으로 명시되거나 NATURAL 이라는 단어로 명시됩니다. 사용 가능한 Qualified Joins의 종류는 다음과 같습니다.

 

  1. INNER JOIN
    • T1 테이블의 각 행 R1에 대해, 조인 테이블에는 R1과 결합 조건을 만족하는 T2 테이블의 각 행에 대한 행이 있습니다.
  2. LEFT OUTER JOIN
    • 우선적으로 INNER 조인이 실행됩니다. 그러고 나서, T2 테이블의 각 행과 결합 조건을 만족시키지 않는 T1의 각 행에 대해 T2의 열에 null 값으로 조인된 행을 추가합니다. 따라서 조인된 테이블은 항상 T1의 각 행에 대해 적어도 하나의 행을 갖습니다.
  3. RIGHT OUTER JOIN
    • 우선적으로 INNER 조인이 실행됩니다. 그러고 나서, T1 테이블의 각 행과 결합 조건을 만족시키지 않는 T2의 각 행에 대해 T1의 열에 null 값으로 조인된 행을 추가합니다. 따라서 조인된 테이블은 항상 T2의 각 행에 대해 적어도 하나의 행을 갖습니다.
  4. FULL OUTER JOIN
    • 우선적으로 INNER 조인이 실행됩니다. 그러고 나서, T2 테이블의 각 행과 결합 조건을 만족시키지 않는 T1의 각 행에 대해 T2의 열에 null 값으로 조인된 행을 추가합니다. 또한 T1 테이블의 각 행과 결합 조건을 만족시키지 않는 T2의 각 행에 대해 T1 의 열에 null 값으로 조인된 행을 추가합니다.

ON 절은 가장 일반적인 조인 조건입니다. WHRE 절에서 사용되는 것과 같은 종류의 부울 값 식을 사용합니다. ON 식을 true로 평가하면 T1과 T2의 행 쌍이 일치합니다.

 

USING 절은 조인의 양쪽이 조인 열(들)에 대해 동일한 이름을 사용하는 특정 상황을 이용할 수 있는 축약어입니다. 이 절은 공유된 열 이름의 쉼표로 구분된 목록을 사용하고 각 열에 대한 동등성 비교를 포함하는 조인 조건을 형성합니다.

T1과 T2를 USING (a,b) 로 조인하면 조인 조건이 ON T1.a = T2.a AND T1.b = T2.b 와 동일합니다.

 

또한, JOIN USING 의 출력은 중복되는 열을 제한합니다. 일치하는 두 열은 동일한 값을 가져야 하기 때문에 둘다 출력할 필요가 없습니다. JOIN ON은 T1에서 모든 열을 생성하고 이후에 T2에서 모든 열을 생성하는 반면, JOIN USING 은 USING 구문 뒤에 나열된 각 열 쌍에 대해, 나열된 순서대로 하나의 출력 열을 생성하고 T1에서 나머지 열 생성, T2에서 나머지 열을 생성합니다.

 

마지막으로 NATURAL 은 USING 구문의 축약형으로 두 입력 테이블에 나타나는 모든 열 이름으로 구성된 USING 목록을 형성합니다. USING 과 마찬가지로 이러한 중복되는 열은 출력 테이블에 한 번만 나타납니다. 공통 열 이름이 없으면 NATURAL JOIN 은 JOIN ... ON TRUE 처럼 동작하여 CROSS-PRODUCT-JOIN 을 생성합니다.

 

# T1 Table
 num | name
-----+------
   1 | a
   2 | b
   3 | c
   
# T2 Table
 num | value
-----+-------
   1 | xxx
   3 | yyy
   5 | zzz
   
=> SELECT * FROM t1 CROSS JOIN t2;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   1 | a    |   3 | yyy
   1 | a    |   5 | zzz
   2 | b    |   1 | xxx
   2 | b    |   3 | yyy
   2 | b    |   5 | zzz
   3 | c    |   1 | xxx
   3 | c    |   3 | yyy
   3 | c    |   5 | zzz
(9 rows)

=> SELECT * FROM t1 INNER JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
(2 rows)

=> SELECT * FROM t1 INNER JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 NATURAL INNER JOIN t2;
 num | name | value
-----+------+-------
   1 | a    | xxx
   3 | c    | yyy
(2 rows)

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
(3 rows)

=> SELECT * FROM t1 LEFT JOIN t2 USING (num);
 num | name | value
-----+------+-------
   1 | a    | xxx
   2 | b    |
   3 | c    | yyy
(3 rows)

=> SELECT * FROM t1 RIGHT JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   3 | c    |   3 | yyy
     |      |   5 | zzz
(3 rows)

=> SELECT * FROM t1 FULL JOIN t2 ON t1.num = t2.num;
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |   3 | yyy
     |      |   5 | zzz
(4 rows)

 

ON으로 지정된 조인 조건에는 조인과 직접 관련이 없는 조건도 포함될 수 있습니다. 이는 일부 쿼리에 유용할 수 있지만 신중하게 사용되어야 합니다.

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num AND t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
   2 | b    |     |
   3 | c    |     |
(3 rows)

 

만약, 위 쿼리에서 WHERE 구문을 사용한다면, 아마 의도된 결과 값이 나올 것입니다.

=> SELECT * FROM t1 LEFT JOIN t2 ON t1.num = t2.num WHERE t2.value = 'xxx';
 num | name | num | value
-----+------+-----+-------
   1 | a    |   1 | xxx
(1 row)

 

 

Table And Column Aliases

 

쿼리의 나머지 부분에서 파생된 테이블에 대한 참조에 사용할 테이블 및 복잡한 테이블 참조에 임시 이름을 지정할 수 있습니다. 이를 테이블 별칭이라고 합니다.

FROM table_reference AS alias

# or
FROM table_reference alias

# 조인 테이블 가독성 증가
SELECT * FROM some_very_long_table_name s JOIN another_fairly_long_name a ON s.id = a.num;

# 만약, 별칭이 지정되었다면 원래 테이블 명은 사용할 수 없습니다
SELECT * FROM my_table AS m WHERE my_table.a > 5;    -- wrong

# 별칭은 주로 표기법 편의를 위한 것이지만 자기 참조에 또한 필요합니다
SELECT * FROM people AS mother JOIN people AS child ON mother.id = child.mother_id;

# 또한 괄호는 모호성을 해결하기 위해 사용됩니다.
# 별칭 a를 my_table의 첫번째 인스턴스에 할당, 별칭 b를 my_table의 두번째 인스턴스에 할당
SELECT * FROM my_table AS a CROSS JOIN my_table AS b ...

# 조인의 결과 값에 별칭 b를 할당
SELECT * FROM (my_table AS a CROSS JOIN my_table) AS b ...

# 별칭은 변수처럼 할당 영역이 있습니다
SELECT a.* FROM (my_table AS a JOIN your_table AS b ON ...) AS c

다음과 같이 PostgreSQL 에서 사용하는 DML 들에 대해서 알아보았습니다. 다음 시간에는 DCL 과 트랜잭션에 대해 알아보겠습니다.

반응형
저작자표시

'Database' 카테고리의 다른 글

[Database] PostgreSQL - Index  (0) 2024.07.18
[Database] PostgreSQL Commands - DCL  (0) 2024.07.18
[Database] PostgreSQL Commands - DDL  (8) 2024.07.15
[Database] RDBMS - PostgreSQL  (0) 2024.07.10
[Database] NoSQL 이란?  (0) 2024.07.10

'Database'의 다른글

  • 현재글[Database] PostgreSQL Commands - DML

관련글

티스토리툴바