[PostgreSQL] psql 쉘에서 쿼리 작성하기

다양한 작업을 수행하기 위해서 콘솔에 코드를 입력하여 데이터베이스를 관리하고 조작할 수 있습니다. 사용자 편의를 위해 PostgreSQL은 많은 명령어를 기본적으로 제공하고 있습니다. 콘솔을 이용하면 데이터베이스를 이용해 다양한 애플리케이션에 연결하고 데이터를 백업 및 복원할 수도 있습니다.

한 가지 주의할 점은, 콘솔에 입력하는 코드의 종류에 대해 혼동해서는 안됩니다. 콘솔 명령어는 PostgreSQL 프로그램을 실행하기 위한 코드이며 SQL 문법은 프로그램 내부에서 이해할 수 있는 데이터베이스 고유의 프로그래밍 언어입니다.

psql

콘솔이란 무엇인가?

콘솔이란 사람이 컴퓨터를 조작하기 위해 운영체제를 이용할 때 텍스트로 접근하는 방식을 뜻합니다. 즉, 콘솔이란 컴퓨터를 조작하기 위한 도구라고 생각하거나 더 편하게 컴퓨터와 대화하는 채널이라고 생각하면 이해하기 쉽습니다. 또한 콘솔은 PostgreSQL만을 위한 것이 아니라, 그 외의 컴퓨터를 조작하는 방법으로도 포괄적으로 사용되며 쉘이라고 부르기도 합니다.

콘솔은 언제 쓰는가?

사용자의 컴퓨터 운영체제에 따라 콘솔을 실행하는 방법은 조금 다릅니다.

psql -U [사용자 계정]

명령어를 입력하면 패스워드를 입력하라는 결과가 출력되고 패스워드를 입력하면 데이터베이스에 접속하게 됩니다. 그리고 [데이터베이스명]=# 옆에 커서가 깜박이며 입력을 대기하는 상태가 됩니다. 이 상태를 커맨드 라인이라고 합니다.

콘솔은 대부분 psql을 조작하거나 서버, 데이터베이스 관리 등에 사용됩니다.

명령어설명예시코드

\q	psql을 종료합니다	-
\l	데이터베이스를 조회합니다	-
\c	입력한 데이터베이스로 이동합니다.	\c 데이터베이스명
\e	외부편집기로 SQL 쿼리를 입력할 수 있습니다	-
\dt	현재 데이터베이스에서 테이블을 확인할 수 있습니다	-

콘솔에서 쿼리문 작성하기

쿼리문 입력 시 한 줄이 길어지만 적당히 끊고 엔터를 누릅니다. 줄바꿈이 된 상태에서도 쿼리문을 계속 작성해도 되며 모든 코드 입력이 완료되면 세미콜론(;)을 붙여줍니다. 그러면 콘솔은 세미콜론 이전까지 입력된 코드를 한 줄로 인식하고 쿼리문이 입력됩니다.

postgres=# CREATE TABLE develop_book (
postgres=#  book_id INTEGER
postgres=#  date DATE
postgres=#  name VARCHAR(80)
postgres=# );

--psql은 다음처럼 인식합니다.
CREATE TABLE develop_book (book_id INTEGER, date DATE, name VARCHAR(80));

테이블 생성하기

데이터베이스 생성하기

CREATE DATABASE [데이터베이스 이름];

커맨드 라인에서 데이터베이스를 생성합니다. 데이터베이스가 제대로 생성되었는지 확인하려면 \l 명령어를 입력하여 모든 데이터베이스를 조회하여 확인합니다.

postgres=# \c [데이터베이스 이름]

현재 접속되어 있는 데이터베이스와 연결을 끊고 이동하려는 데이터베이스로 접속합니다. 데이터베이스명은 대문자로 작성을 하더라도 소문자로 바뀌어서 저장됩니다. 데이터베이스명 뿐만 아니라 테이블, 컬럼을 생성할 때에는 한눈에 이해할 수 있도록 이름을 짓는 것이 중요합니다.

데이터베이스 삭제하기

DROP DATABASE 데이터베이스명;

테이블 생성하기

CREATE TABLE develop_book (book_id INTEGER, date DATE, name VARCHAR(80));

명령어(혹은 자리)설명

CREATE TABLE	데이터베이스 안에 테이블을 새로 생성하는 명령어
테이블명	새로 생성하는 테이블의 이름을 지정
컬럼명	데이터와의 관계를 표현하기 위한 컬럼이름을 지정
자료형	컬럼의 자료 데이터 타입을 지정

생성한 테이블을 조회하고 싶다면 \dt 명령어를 사용합니다. 현재 데이터베이스에서 생성된 테이블을 모두 조회할 수 있습니다.

테이블 삭제하기

데이터베이스를 구축하다가 테이블을 잘못 만들었다거나, 필요 없어졌다면 테이블을 삭제해야 합니다. 삭제를 하는 쿼리문은 다음과 같습니다.

DROP TABLE 테이블명;

데이터 추가하기

순서 지정 없이 데이터 자료 추가하기

우선 기본적으로 테이블 컬럼의 순서에 맞춰서 넣어줄 데이터의 순서도 맞춰줍니다.

INSERT INTO [테이블명] VALUES(value1, value2, value2);

순서 시정하고 데이터 자료 추가하기

컬럼 항목이 많은 경우 순서를 모두 기억할 수 없습니다. 따라서 직접 컬럼 순서를 미리 지정하고 그 순서에 맞추어 입력합니다.

INSERT INTO [테이블명] (컬럼1, 컬럼2, 컬럼3) VALUES (데이터1, 데이터2, 데이터3)

테이블에 자료 여러 개 추가하기

INSERT INTO [테이블명] VALUES
(데이터1, 데이터1, 데이터1),
(데이터2, 데이터2, 데이터2),
(데이터3, 데이터3, 데이터3),
...
(데이터n, 데이터n, 데이터n);

위와 다르게 아래는 컬럼의 순서를 지정하여 자료 여러 개를 추가하는 방법입니다.

INSERT INTO [테이블명] (컬럼1, 컬럼2, 컬럼3) VALUES
(데이터1, 데이터1, 데이터1),
(데이터2, 데이터2, 데이터2),
(데이터3, 데이터3, 데이터3),
...
(데이터n, 데이터n, 데이터n);

데이터 조회하기

모든 자료 조회하기

명령어(혹은 자리)설명

SELECT	컬럼을 지정한다.
*	모든 컬럼을 의미한다.
FROM	테이블을 지정한다.

SELECT * FROM [테이블명]

선택한 자료 조회하기

테이블의 모든 컬럼을 조회하는 경우는 개발자가 데이터를 확인할 때 주로 사용합니다. 대부분 어떤 서비스 상에서는 데이터를 모두 확인하는 것이 아니라 일부만 조회합니다.

SELECT [컬럼] FROM [테이블명]

명령어설명

LIMIT	반환하는 로우의 개수를 지정한다.
OFFSET	반환하는 로우의 시작시점을 지정한다.
ORDER BY	반환하는 로우를 정렬할 때 사용한다.
WHERE	지정한 로우만 조회가 되도록 필터 기능을 한다.

LIMIT

LIMIT 명령어는 테이블로부터 가져오는 로우의 개수를 제한할 수 있습니다.

SELECT * FROM [테이블명]
LIMIT 5;
...
(5개 행 결과 출력)

하지만 LIMIT 명령어만 사용하면 처음에 존재하는 로우부터 출력됩니다. 중간로우부터 출력하려면 OFFSET 명령어를 사용하여 반환하는 로우의 시작 지점을 지정합니다.

SELECT * FROM [테이블명]
LIMIT 6
OFFSET 1;

ORDER BY

조회할 데이터를 오름차순 또는 내림차순으로 정렬하는 기능을 가지고 있습니다.

SELECT * FROM [테이블명]
ORDER BY [컬럼명] ASC;

명령어설명

ASC	반환할 데이터를 오름차순으로 정렬한다.
DESC	반환할 데이터를 내림차순으로 정렬한다.

SELECT * FROM [테이블명]
ORDER BY 컬럼1, 컬럼2;

ORDER BY 절에 컬럼명을 적어주면 컬럼1을 기준으로 먼저 정렬하고 컬럼2를 기준으로 정렬한다. 또한 SELECT 문에 지정한 컬럼을 ORDER BY 문에 지정할 때에는 SELECT 문에 지정한 순서로 표현할 수 있다.

SELECT 컬럼1, 컬럼2 FROM [테이블명]
ORDER BY 2, 1;

WHERE

WHERE절은 어떤 조건을 만족시키면 그 조건에 부합하는 데이터만 출력하도록 필터링해줍니다. 또한, WHERE절은 SELECT절과 함께 쓰이기도 합니다.

연산자설명

=	서로 같다
<>	서로 다르다
>	오른쪽 값보다 왼쪽 값이 더 크다
<	왼쪽 값보다 오른쪽 값이 더 크다
>=	오른쪽 값보다 왼쪽 값이 더 크거나 같다
<=	왼쪽 값보다 오른쪽 값이 더 크거나 같다

SELECT * FROM [테이블명]
WHERE id=1;

SELECT * FROM [테이블명]
WHERE id <> 1;

SELECT * FROM [테이블명]
WHERE id > 3;

서브쿼리

쿼리문 안에 쿼리문이 반복되는 구조를 서브쿼리라고 합니다. 서브쿼리에서 첫 번째 쿼리문을 ‘메인 쿼리문’이라고 지칭하고, 바로 따르는 소괄호 안에 쿼리문을 ‘서브쿼리문’이라고 합니다.

SELECT * FROM devleop_book
WHERE '2020-01-03' = (
SELECT date FROM develop_book
WHERE book_id=3
)

데이터 수정하기

선택한 컬럼 데이터 수정하기

UPDATE 테이블명
  SET 컬럼명 = 바꿀 데이터 내용
  WHERE 수정할 로우의 조건
RETURNING *; --수정한 내용 바로 조회하기

UPDATE라는 키워드와 함께 테이블을 지정합니다. 그리고 WHERE 키워드로 수정할 컬럼의 범위를 지정하고, SET 키워드로 데이터를 수정합니다. 여기서 WHERE 키워드는 선택사항입니다. 만약 SET 명령어에서 수정할 컬럼을 지정하지 않았다면 모든 컬럼의 값을 변경합니다.

테이블 복사와 정렬

데이터베이스는 최근의 수정된 로우를 가장 마지막에 출력합니다. 그래서 데이터를 수정한 이후 원하는 순서대로 정렬하려면 ORDER BY 쿼리문을 입력해야 합니다.

SELECT * FROM dcevelop_book
ORDER BY book_id ASC;

AS 명령어

출력 결과의 컬럼명을 원래 이름과 다르게 변경하여 출력하는 기능이 있습니다.

SELECT name AS title FROM develop_book;

복사와 정렬

기존 테이블 데이터를 정렬하여 복사한 뒤 새로운 테이블에 붙여넣습니다.

CREATE TABLE develop_book_2 AS
SELECT * FROM devleop_book
ORDER BY book_id ASC;

데이터 삭제하기

선택한 컬럼 데이터 삭제하기

데이터가 더 이상 필요 없어진 경우에는 데이터를 삭제해야 합니다. 컬럼의 데이터를 삭제하는 쿼리문의 키워드는 DELETE입니다.

DELETE FROM 테이블명 WHERE 컬럼명 = 삭제할 데이터;

모든 데이터 삭제하기

DELETE FROM 테이블명;