23 June 2022 / DEVOPS

postgreSQL 기본 사용법 [update 中]

DevOps 관련 포스팅

이 글은 postgreSQL의 기본적인 사용법에 대해 다루고 있습니다.

0. SQL Shell 사용하기

cmd에서 psql 명령으로 postgreSQL 접속하기
psql --version 명령이 실행되지 않는다면, 환경 변수 설정이 선행되어야 함
- [환경 변수] - [시스템 변수: Path]선택 - 확인 - 새로 만들기 - 찾아보기 - C:\Program Files\PostgreSQL\14\bin 선택 - 확인- 확인 - cmd 종료 후 재실행
psql 실행
psql -U postgres 실행으로 user 암호 입력하기

1. CREATE DATABASE

\l명령으로 database 목록 확인
SQL syntax는 대문자로 입력하며 명령의 끝은 세미콜론(;)으로 끝맺어야 실행됨
```
CREATE DATABASE test;
```

\l명령으로 database가 생성되었는지 확인

                             데이터베이스 목록
  이름     |  소유주  | 인코딩 |    Collate     |     Ctype      |   액세스 권한
-------------+----------+-------+----------------+----------------+-----------------
test         | postgres | UTF8  |Korean_Korea.949|Korean_Korea.949|

\c + db 이름으로 생성된 database에 연결하기(connect)

\c test

접속정보: 데이터베이스="test", 사용자="postgres".

2. CREATE TABLE

datatype에 익숙해지기 datatype 목록

입력

CREATE TABLE person (
     id BIGSERIAL NOT NULL PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
     gender VARCHAR(7) NOT NULL,
     date_of_birth DATE NOT NULL,
     email VARCHAR(150) );

CREATE TABLE

\d 명령으로 테이블 확인

      릴레이션(relation) 목록
 스키마 |     이름      |  종류  |  소유주
--------+---------------+--------+----------
 public | person        | 테이블 | postgres
 public | person_id_seq | 시퀀스 | postgres
(2개 행)

※ BIGSERIAL (autoincrementing eight-byte integer) 때문에 person_id_seq 테이블이 생성됨

\dt 명령으로 실제 테이블 확인

릴레이션(relation) 목록
 스키마 |  이름  |  종류  |  소유주
--------+--------+--------+----------
 public | person | 테이블 | postgres
(1개 행)

\d + table 이름으로 해당 테이블 확인

"public.person" 테이블
 필드명     |          종류          | Collation | NULL허용 |               초기값
---------------+------------------------+-----------+----------+------------------------------------
 id            | bigint                 |           | not null | nextval('person_id_seq'::regclass)
 first_name    | character varying(50)  |           | not null |
 last_name     | character varying(50)  |           | not null |
 gender        | character varying(7)   |           | not null |
 date_of_birth | date                   |           | not null |
 email         | character varying(150) |           |          |
인덱스들:
 "person_pkey" PRIMARY KEY, btree (id)

3. INSERT INTO

data 입력하기

INSERT INTO person (first_name, last_name, gender, date_of_birth)
VALUES ('Anne', 'Smith', 'FEMALE', date '1988-01-09');

sql 파일로 data 입력하기
```
\i [download한 file의 경로]
```
※ 해당 파일이 있는 곳에서 bash를 열어 pwd 명령을 이용하면 경로를 확인할 수 있음
```
\i Downloads/person.sql
```

4. SELECT

4-1. ASC/DESC

person 테이블의 id, first_name, country_of_birth를 country_of_birth의 오름차순(ASC)으로 정렬

  SELECT id, first_name, country_of_birth 
  FROM person 
  ORDER BY country_of_birth ASC;

  id  |   first_name   |         country_of_birth
  -----+----------------+----------------------------------
| Gasper         | Afghanistan
| Mohandis       | Afghanistan
| Vevay          | Afghanistan
| Jodee          | Afghanistan
| Killian        | Afghanistan
| Jereme         | Afghanistan
| Finley         | Albania
| Leanna         | Albania
| Robinetta      | Albania
| Haskel         | Angola
| Devondra       | Argentina

person 테이블의 country_of_birth를 중복 없이(DISTINCT) 내림차순(DESC)으로 정렬

  SELECT DISTINCT country_of_birth 
  FROM person 
  ORDER BY country_of_birth DESC;

  country_of_birth
  -----------------
  Afghanistan
  Albania
  ...
  Yemen
  Zambia

4-2. WHERE 조건절

gender = 'Female'인 column(id, first_name, gender, country_of_birth)

  SELECT id, first_name, gender, country_of_birth 
  FROM person 
  WHERE gender = 'Female';

  id  |   first_name   | gender |         country_of_birth
  -----+----------------+--------+----------------------------------
      7 | Oona           | Female | Indonesia
      8 | Colline        | Female | China
   13 | Willyt         | Female | China
   14 | Marice         | Female | Poland
                                                  ...

4-3. WHERE … AND … 조건절

gender = 'Female'이고(AND), country_of_birth = 'Poland'인 조건

  SELECT id, first_name, gender, country_of_birth 
  FROM person 
  WHERE gender = 'Female' AND country_of_birth = 'Poland';

  id  | first_name | gender | country_of_birth
  -----+------------+--------+------------------
| Marice     | Female | Poland
| Elisabeth  | Female | Poland
| Aurlie     | Female | Poland
| Sallie     | Female | Poland
| Larine     | Female | Poland
| Asia       | Female | Poland

4-4. WHERE … AND …( ~ OR ~) 조건절

gender = 'Female'이고(AND),
country_of_birth = 'Poland' 이거나(OR) country_of_birth = 'China'인 조건

  SELECT id, first_name, gender, country_of_birth 
  FROM person 
  WHERE gender = 'Female' AND (country_of_birth = 'Poland' OR country_of_birth = 'China');

  id  | first_name | gender | country_of_birth
  -----+------------+--------+------------------
       8 | Colline    | Female | China
      13 | Willyt     | Female | China
      14 | Marice     | Female | Poland
      37 | Bari       | Female | China
      46 | Bobine     | Female | China
      49 | Elisabeth  | Female | Poland
                                  ...

gender = 'Female'이고(AND),
country_of_birth = 'Poland' 이거나(OR) country_of_birth = 'China' 이며(AND),
last_name = 'Quest'인 조건

  SELECT id, first_name, last_name, gender, country_of_birth 
  FROM person 
  WHERE gender = 'Female' AND (country_of_birth = 'Poland' OR country_of_birth = 'China') AND last_name = 'Quest';

  id | first_name | last_name | gender | country_of_birth
  ----+------------+-----------+--------+------------------
  14 | Marice     | Quest     | Female | Poland
  (1개 행)

5. Comparison Operator(비교 연산)

number, string, date 등 여러 datatype에 대해 연산이 가능함

TRUE

  SELECT 1 = 1;

  SELECT 'MONKEY' <> 'monkey';

  ?column?
  ----------
   t
  (1개 행)

FALSE

  SELECT 1 = 2;

  SELECT 2 <= 1;

  ?column?
  ----------
   f
  (1개 행)

6. LIMIT

한정된 개수로 DATA 제한

최초의 3개 행(LIMIT 3)

  SELECT id, first_name, country_of_birth 
  FROM person 
  LIMIT 3;

  id | first_name |    country_of_birth
  ----+------------+------------------------
   1 | Cody       | Bosnia and Herzegovina
   2 | Prescott   | China
   3 | Trev       | Indonesia
  (3개 행)

7. OFFSET … LIMIT

지정된 INDEX부터 한정된 개수로 DATA 제한

INDEX 2(OFFSET 2)부터 3개 행(LIMIT 3)

  SELECT id, first_name, country_of_birth 
  FROM person 
  OFFSET 2 
  LIMIT 3;

  id | first_name  | country_of_birth
  ----+-------------+------------------
3 | Trev        | Indonesia
4 | Bartolemo   | Poland
5 | Christopher | Philippines
  (3개 행)

8. OFFSET

OFFSET만 사용시, 지정된 INDEX부터 끝까지

INDEX 926부터(OFFSET 926) 끝까지 (총 DATA는 930개)

  SELECT id, first_name, country_of_birth 
  FROM person 
  OFFSET 926;

  id  | first_name | country_of_birth
  -----+------------+------------------
  927 | Gayleen    | China
  928 | Mavis      | Peru
  929 | Melody     | United States
  930 | Isidoro    | Indonesia
  (4개 행)

9. FETCH

LIMIT 의 공식적인 버전

INDEX 1부터(OFFSET 1) 3개 행(FETCH FIRST 3 ROW ONLY)

  SELECT id, first_name, date_of_birth 
  FROM person 
  OFFSET 1 
  FETCH FIRST 3 ROW ONLY;

  id | first_name | date_of_birth
  ----+------------+---------------
   2 | Prescott   | 2022-05-17
   3 | Trev       | 2022-04-05
   4 | Bartolemo  | 2021-09-24
  (3개 행)

INDEX 111부터(OFFSET 111) 1개행(FETCH FIRST ROW ONLY)

1은 생략가능

  SELECT * 
  FROM person 
  OFFSET 111 
  FETCH FIRST ROW ONLY;

  SELECT * 
  FROM person
  OFFSET 111 
  FETCH FIRST 1 ROW ONLY;

  id  | first_name | last_name | email | gender | date_of_birth | country_of_birth
  -----+------------+-----------+-------+--------+---------------+------------------
  112 | Henry      | Wakes     |       | Male   | 2021-12-03    | China
  (1개 행)

10. IN

반복되는 컬럼에 대한 값을 지정할 때 사용

반복적인 OR을 IN 으로 교체

  SELECT id, first_name, country_of_birth 
  FROM person 
  WHERE country_of_birth = 'China'
  OR country_of_birth = 'France'
  OR country_of_birth = 'Brazil';

  SELECT id, first_name, country_of_birth
  FROM person
  WHERE country_of_birth IN ('China', 'Brazil', 'France');

  id  | first_name | country_of_birth
  -----+------------+------------------
2 | Prescott   | China
8 | Colline    | China
 						...
  920 | Krisha     | Brazil
  922 | Shirlee    | China
  927 | Gayleen    | China
  (255개 행)

IN으로 반복을 묶고, ORDER BY로 정렬

  SELECT id, first_name, country_of_birth
  FROM person
  WHERE country_of_birth IN ('China', 'Brazil', 'France')
  ORDER BY country_of_birth;

  id  | first_name | country_of_birth
  ----+------------+------------------
  663 | Darbee     | Brazil
  269 | Hamnet     | Brazil
                          ...
  825 | Jerrie     | France
  309 | Janel      | France
  (255개 행)

11. BETWEEN … AND …

구간 지정

  SELECT id, first_name, date_of_birth
  FROM person
  WHERE date_of_birth
  BETWEEN DATE '2021-12-31' AND '2022-01-01';

  id  | first_name | date_of_birth
  -----+------------+---------------
  576 | Seana      | 2022-01-01
  782 | Kelcey     | 2021-12-31
  789 | Jaine      | 2021-12-31
  842 | Burl       | 2022-01-01
  (4개 행)

12. LIKE / ILIKE

PostgreSQL LIKE 참고 문서

LIKE연산자는 =연산자처럼 작동함

LIKE : 대소문자 구분

  SELECT 'Her' LIKE 'HER';

  ?column?
  ----------
      f
  (1개 행)

ILIKE : 대소문자 구분하지 않음

  SELECT 'her' ILIKE 'HER';

  ?column?
  ----------
      t
  (1개 행)

PostgreSQL은 두 가지 wildcard(%, _)를 제공함
- % : 일치하는 0개 이상의 문자
- _ : 일치하는 하나의 문자

%.com : .com으로 끝나는 문자열

  SELECT id, first_name, email 
  FROM person 
  WHERE email LIKE '%.com';

  id  | first_name  |                email
  ----+-------------+-------------------------------------
    1 | Cody        | cgiocannoni0@github.com
    3 | Trev        | tmackellar2@msn.com
                  ...
  927 | Gayleen     | gsawartrn@usnews.com
  928 | Mavis       | mrallingro@gravatar.com
  (384개 행)

%@mozilla.com : @mozilla.com으로 끝나는 문자열

  SELECT id, first_name, email 
  FROM person 
  WHERE email LIKE '%@mozilla.com';

   id  | first_name |          email
  -----+------------+--------------------------
   533 | Nonna      | nrucklessefr@mozilla.com
   744 | Jobye      | jhappsma@mozilla.com
  (2개 행)

@google. : @google.이 포함된 문자열

  SELECT id, first_name, email 
  FROM person 
  WHERE email LIKE '%@google.%';

    id  | first_name |            email
  ------+------------+------------------------------
    113 | Reuben     | rwinslett3a@google.ru
    162 | Rice       | rblayney4t@google.co.jp
                    ...
    738 | Emmy       | eswindenm4@google.cn
    751 | Hedvig     | hhardypigginmh@google.com.au
  (18개 행)

8자의 문자열과 @ 로 시작하는 email

  SELECT id, first_name, email 
  FROM person 
  WHERE email LIKE '________@%';

 id  | first_name |             email
-----+------------+-------------------------------
  12 | Matthus    | mallbutc@nyu.edu
  28 | Stefano    | srainers@npr.org
                ...
 894 | Anselm     | apirieqq@wisc.edu
 901 | Leonore    | lwykeyqx@list-manage.com
(78개 행)

P% : P로 시작하는 문자열

  SELECT id, first_name, country_of_birth 
  FROM person 
  WHERE country_of_birth LIKE 'P%';

id  | first_name  |   country_of_birth
----+-------------+-----------------------
  4 | Bartolemo   | Poland
  5 | Christopher | Philippines
          ...
926 | Edvard      | Portugal
928 | Mavis       | Peru
(136개 행)

13. GROUP BY

< 출처 >

‘Learn PostgreSQL Tutorial - Full Course for Beginners,’ 유튜브 비디오, 04:19:34, 게시자 ‘freeCodeCamp.org,’ 2019년04월04일, https://youtu.be/qw–VYLpxG4

postgreSQL 기본 사용법 [update 中]

0. SQL Shell 사용하기

1. CREATE DATABASE

2. CREATE TABLE

3. INSERT INTO

4. SELECT

4-1. ASC/DESC

4-2. WHERE 조건절

4-3. WHERE … AND … 조건절

4-4. WHERE … AND …( ~ OR ~) 조건절

5. Comparison Operator(비교 연산)

6. LIMIT

7. OFFSET … LIMIT

8. OFFSET

9. FETCH

10. IN

11. BETWEEN … AND …

12. LIKE / ILIKE

13. GROUP BY

AWS 배포하기(1) - RDS (MySQL)

Python(6) - ORM(Object-Relational Mapping)

0. SQL Shell 사용하기

1. CREATE DATABASE

2. CREATE TABLE

3. INSERT INTO

4. SELECT

4-1. ASC/DESC

4-2. WHERE 조건절

4-3. WHERE … AND … 조건절

4-4. WHERE … AND …( ~ OR ~) 조건절

5. Comparison Operator(비교 연산)

6. LIMIT

7. OFFSET … LIMIT

8. OFFSET

9. FETCH

10. IN

11. BETWEEN … AND …

12. LIKE / ILIKE

13. GROUP BY

백엔드 개발자의 노트