PostgreSQL의 SQL 기본 사용법

오픈소스 데이터베이스인 PostgreSQL의 SQL 기본 사용법에 대해 정리한다.

 

PostgreSQL 소개와 Docker를 사용하여 설정하는 방법은 아래 포스팅을 참고한다.

Docker로 PostgreSQL 사용하기

 

PostgreSQL 공식 Docker 이미지에 한글을 설정한 Docker 이미지 생성 방법은 아래 포스팅을 참고한다.

공식 PostgreSQL Docker 이미지에 한글 적용하기

 

GitHub과 DockerHub 사이트는 다음과 같다.

• DockerHub - https://hub.docker.com/_/postgres
• GitHub - https://github.com/docker-library/postgres

 

PostgreSQL은 다음과 같은 구조를 가지고 있다.

각 기능은 다음과 같다.

• Users/Groups - 사용자 정보 관리
• Databases - 실제 레코드가 저장되는 Database 관리
• Tablespaces - Database의 Object가 저장 된 파일시스템의 경로 관리
• Schemas - PostgreSQL 내부에서 Database를 논리적으로 구분하여 관리
• Tables - Row와 Column으로 구성 된 실제 레코드가 저장되는 공간
• Views - 읽기 전용의 가상 테이블로 제한 된 정보만 제공하기 위한 공간

 

 

SQL 기본

PostgreSQL도 RDBMS이므로 레코드 관리는 SQL으로 진행한다.

Create (Insert), Read (Select), Update, Delete의 약어로 CRUD라고도 한다.

 

테이블간의 조합으로 원하는 정보를 추출하는 JOIN 연산도 포함한다.

 

SELECT

한 개 또는 여러 개의 테이블에서 원하는 레코드를 검색한다.

 

아래 명령을 사용한다.

SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] 
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]

 

옵션의 의미는 다음과 같다.

Option	기능
SELECT ALL	중복 결과 포함해서 출력
SELECT DISTINCT ON	중복을 제거해서 유일한 값만 출력
AS (= alias)	선택한 column의 이름을 새로운 이름으로 변경하여 출력
FROM	한 개 또는 여러 개의 테이블 지정 가능
WHERE	FROM에서 지정한 테이블에서 검색 조건 지정
LIKE	부분 일치 검색으로 _는 한 글자, %는 제한 없음 (WHERE에 사용)
GROUP BY	동일한 값을 가진 데이터를 집계하여 조회 (ex. 부서별 인원 카운트)
HAVING	GROUP BY 결과에서 검색 조건 지정 (ex. 조건 미충족 결과 제외)
UNION	결과 집합이 2개 이상일 때 열의 개수와 타입이 동일하면 통합하여 출력
INTERSECT	두 결과 집합에 모두 있는 행을 반환 (교집합)
EXCEPT	두 결과 집합 모두에 있는 행은 제외하고 반환 (차집합)
ORDER BY	결과 정렬 (ASC는 오름차순, DESC는 내림차순)
LIMIT	조회 결과가 너무 많은 경우 출력 개수 제한
OFFSET start	조회 결과가 너무 많은 경우 시작할 행 선택 (앞부분 건너뛰기)

 

대표적인 사용 예제는 다음과 같다.

## accident_report 테이블에서 장소, 일시, 사고유형 컬럼 조회
# location 컬럼 중복 제거
# location과 acc_date를 내림차순으로 정렬
postgres=# SELECT DISTINCT ON (location) location, acc_date, accident_case
    FROM accident_reports
    ORDER BY location, acc_date DESC;

## employee 테이블에서 사원 번호가 15인 사람의 모든 정보 출력
postgres=# SELECT * FROM employee WHERE emplyee_no= 15;

## movies 테이블에서 장르별 총 상영시간이 5시간 이내인 정보만 출력
# running_time 컬럼의 합계는 total로 컬럼명 변경
# genre 컬럼으로 집계하여 running_time을 합한 결과가 5시간 이내인 경우로 제한
postgres=# SELECT genre, sum(running_time) AS total FROM movies
  GROUP BY genre
  HAVING sum(running_time) < interval '5 hours';

## contributers와 reviewers 테이블에서 이름이 S로 시작하는 모든 결과 출력
# LIKE는 WHERE와 함께 사용 (%는 글자수 제한 없음)
# UNION은 두 조회 결과를 합치는 것으로, 컬럼 수와 타입이 동일해야 함
postgres=# SELECT contributers.name FROM contributers
    WHERE distributors.name LIKE 'S%'
    UNION
    SELECT reviewers.name FROM reviewers
    WHERE actors.name LIKE 'S%';

 

 

INSERT

테이블에 레코드를 입력하는 명령이다.

 

INSERT 구문의 자세한 사용 방법과 예제는 아래 기술 문서를 참고한다.

INSERT

 

먼저 레코드를 입력할 테이블을 생성한다.

postgres=# CREATE TABLE employee (
    employee_no integer,
    name text,
    salary numeric
);

 

생성한 테이블에 값을 입력한다. 간단한 경우만 정리한다.

## 단일 레코드 입력
postgres=# INSERT INTO employee VALUES (1, 'Thomas', 30000);

## 특정 컬럼만 입력
postgres=# INSERT INTO employee (employee_no, name) VALUES (2, 'Smith');

## 여러 레코드 입력
postgres=# INSERT INTO employee (employee_no, name, salary) VALUES
    (3, 'Bread', 50000),
    (4, 'Tim', 25000),
    (5, 'Daniel', 100000);

 

UPDATE

테이블에 입력 된 데이터의 값을 변경하는 명령이다.

 

UPDATE 구문의 자세한 사용 방법과 예제는 아래 기술 문서를 참고한다.

UPDATE

 

INSERT에서 생성한 테이블을 사용한다.

 

기존에 입력 된 테이블의 값을 변경한다. 간단한 경우만 정리한다.

## 단일 값 변경
# salary가 100000인 레코드의 값을 10000으로 변경
postgres=# UPDATE employee SET salary = 10000 WHERE salary = 100000;

## 모든 열의 값 변경
# salary 컬럼의 값에 0.5를 곱하여 갱신
postgres=# UPDATE employee SET salary = salary * 0.5;

## 특정 조건 만족하는 레코드의 값 변경
postgres=# UPDATE employee SET salary=100000, name='Mary'
    WHERE employee_no = 4;

 

 

DELETE

테이블에 입력 된 데이터를 삭제하는 명령이다.

단일, 여러개, 전체 삭제 모두 가능하다.

Primary Key를 알고 있는 경우 정확한 삭제가 가능하다.

 

INSERT와 UPDATE에서 사용한 테이블을 사용한다.

 

기존에 입력 된 테이블의 레코드를 삭제한다. 간단한 경우만 정리한다.

## 단일 값 삭제
# 이름이 Thomas인 레코드 삭제
postgres=# DELETE FROM employee WHERE name = 'Thomas';

## 여러 값 삭제
# salary가 50000 이상인 레코드 삭제
postgres=# DELETE FROM employee WHERE salary > 50000;

## 전부 삭제
postgres=# DELETE FROM employee;

 

DELETE 구문의 자세한 사용 방법과 예제는 아래 기술 문서를 참고한다.

DELETE

 

참고로 TRUNCATE 구문을 사용하면 레코드의 스캔을 하지 않으므로 더 빠른 삭제가 가능하다.

테이블을 잘라내는 명령이므로 DELETE와 동일한 명령이 아님을 참고한다.

자세한 사항은 아래 기술 문서를 참고한다.

TRUNCATE