[DB]PostgreSQL 문법과 기능 정리 (DDL, DML, DCL)

0. PostgreSQL

PostgreSQL은 오픈 소스 관계형 데이터베이스 시스템으로, 다양한 문법과 기능을 제공합니다. 여기에 주요 문법과 기능들을 간략하게 정리해 보겠습니다.

 

1. 데이터 정의 언어 (DDL)

CREATE: 데이터베이스, 테이블, 인덱스, 뷰, 트리거, 함수 등을 생성합니다.

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

 

ALTER: 기존의 데이터베이스 객체를 수정합니다.

ALTER TABLE table_name
ADD column_name datatype;

 

DROP: 데이터베이스 객체를 삭제합니다.

DROP TABLE table_name;

 

2. 데이터 조작 언어 (DML)

INSERT: 테이블에 새로운 데이터 행을 추가합니다.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

 

UPDATE: 테이블의 기존 데이터를 수정합니다.

UPDATE table_name
SET column1 = value1
WHERE condition;

 

DELETE: 테이블에서 데이터를 삭제합니다.

DELETE FROM table_name WHERE condition;

 

SELECT: 테이블에서 데이터를 조회합니다.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

 

• SELECT : 조회할 열(컬럼)을 지정합니다.
• FROM : 조회할 테이블을 지정합니다.
• WHERE : 조건에 맞는 행(레코드)을 필터링합니다.

3. 데이터 제어 언어 (DCL)

GRANT: 사용자에게 권한을 부여합니다.

GRANT SELECT ON table_name TO user_name;

 

REVOKE: 사용자의 권한을 제거합니다.

REVOKE SELECT ON table_name FROM user_name;

 

BEGIN: 트랜잭션 시작을 선언합니다.

 

COMMIT: 모든 변경사항을 데이터베이스에 영구적으로 반영합니다.

 

ROLLBACK: 트랜잭션 내의 모든 변경사항을 취소합니다.

 

4. 데이터 유형

 

기본 유형: INTEGER, SERIAL, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, CHAR, VARCHAR, TEXT, DATE, TIMESTAMP 등.

 

특수 유형: ARRAY, JSON, XML, HSTORE (키-값 쌍), BYTEA (바이너리 데이터), POINT, LINE, LSEG, BOX, PATH, POLYGON, CIRCLE 등 기하학적 타입.

 

5. 자주 사용하는 문법 

쿼리를 작성할 때 자주 사용되는 PostgreSQL 문법들은 주로 데이터를 조회하고 조작하는 데 초점을 맞추며, 이를 위해 필요한 주요 구성 요소들을 다룹니다. 여기에 그러한 문법들을 자세히 설명하겠습니다.

 

WHERE 절

특정 조건을 만족하는 행을 선택합니다.

SELECT * FROM table_name
WHERE condition;

 

조건은 비교 연산자 (=, !=, <, >, <=, >=) 및 논리 연산자 (AND, OR, NOT)를 사용하여 지정합니다.

 

ORDER BY 절

결과를 특정 열의 오름차순 또는 내림차순으로 정렬합니다.

SELECT * FROM table_name
ORDER BY column1 ASC, column2 DESC;

 

ASC는 오름차순, DESC는 내림차순을 의미합니다.

 

GROUP BY 및 집계 함수

그룹화된 결과에 대해 집계(요약) 함수를 적용합니다.

SELECT column, COUNT(*), AVG(column2)
FROM table_name
GROUP BY column;

GROUP BY: 지정된 열에 따라 행을 그룹화합니다.

 

집계 함수 : COUNT(), SUM(), AVG(), MIN(), MAX() 등이 있습니다.

 

JOIN

두 개 이상의 테이블을 결합합니다.

SELECT *
FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

 

JOIN의 종류 : INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN 등이 있습니다.

 

ON : 결합 조건을 지정합니다. 보통 KEY값을 사용하여 결합합니다.

 

LIMIT

조회 결과의 수를 제한합니다.

SELECT * FROM table_name
LIMIT number;

 

number에 반환할 행의 최대 개수를 지정합니다.

 

OFFSET

결과 집합에서 특정 수의 행을 건너뛴 후 데이터를 반환합니다.

SELECT * FROM table_name
LIMIT number OFFSET start;

 

start에 건너뛸 행의 수를 지정합니다.

 

IN

열 값이 지정된 값 목록 중 하나와 일치하는 행을 찾습니다.

SELECT * FROM table_name
WHERE column_name IN (value1, value2, ...);

 

BETWEEN

열 값이 두 값 사이에 있는 행을 찾습니다.

SELECT * FROM table_name
WHERE column_name BETWEEN value1 AND value2;

 

LIKE 및 ILIKE

문자열이 지정된 패턴과 일치하는지 확인합니다.

SELECT * FROM table_name
WHERE column_name LIKE pattern;

 

LIKE: 대소문자를 구분합니다.

 

ILIKE: 대소문자를 구분하지 않습니다.

 

%: 임의의 문자열을 나타냅니다 (예: %text%).

 

_: 하나의 문자를 나타냅니다 (예: te_t).

 

DISTINCT

중복된 값을 제거한 결과를 반환합니다.

SELECT DISTINCT column_name
FROM table_name;