SQL 기초¶

기초 개념¶

Database

DBMS(Database Management System)

DBMS의 종류
- 관계형 데이터베이스 관리 시스템 (RDBMS: Relational Database Management System)
  - SQL(Structured Query Language)
- NoSQL DBMS

DBMS의 구조
- 데이터베이스(database)
- 테이블(table)
  - 스키마(schema)
- 레코드(record)

SQL
- 생성(Create): INSERT
- 읽기(Read): SELECT
- 갱신(Update): UPDATE
- 삭제(Delete): DELETE

트랜잭션(Transaction)
- 데이터베이스의 작업 단위
- 커밋(commit)
  - 트랜잭션 내용을 적용
- 롤백(rollback)
  - 과거의 상태로 되돌림

ACID
- 원자성(Atomicity)
  - 작업들이 부분적으로 실행되지 않고 완결되는 것을 보장
- 일관성(Consistency) *
- Isolation
- Durability

교재¶

https://www.w3schools.com/sql/

PostgreSQL¶

가동¶

ssh 프로그램이나 docker attach 명령, 또는 jupyter notebook의 terminal을 이용하여 도커 컨테이너 콘솔로 들어간 뒤

sudo service postgresql start

콘솔¶

콘솔에서 데이터베이스르 조작하는 명령은 권한을 가진 postgres 라는 계정으로 실행해야 하므로 항상 sudo -u postgres를 붙여서 실행한다.

시작
```
sudo -u postgres psql
```

종료
```
postgres=# \q
```

콘솔 도움말(vi 모드)
```
postgres=# \?
```

SQL 도움말
```
postgres=# \h
```

교재¶

http://www.postgresqltutorial.com/

샘플 데이터베이스 다운로드¶

$ wget -O dvdrental.zip http://www.postgresqltutorial.com/download/dvd-rental-sample-database/?wpdmdl=969
$ unzip dvdrental.zip

샘플 데이터베이스 생성 (bash shell에서)¶

$ sudo -u postgres createdb dvdrental

$ sudo -u postgres pg_restore -d dvdrental dvdrental.tar

$ sudo -u postgres psql
psql (9.5.6)
Type "help" for help.

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".

dvdrental-# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

샘플 데이터베이스 생성 (psql 내부에서)¶

$ sudo -u postgres psql
psql (9.5.6)
Type "help" for help.

postgres=# CREATE DATABASE dvdrental;
CREATE DATABASE

postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".

dvdrental=# \i dvdrental.tar

...


dvdrental-# \dt
             List of relations
 Schema |     Name      | Type  |  Owner
--------+---------------+-------+----------
 public | actor         | table | postgres
 public | address       | table | postgres
 public | category      | table | postgres
 public | city          | table | postgres
 public | country       | table | postgres
 public | customer      | table | postgres
 public | film          | table | postgres
 public | film_actor    | table | postgres
 public | film_category | table | postgres
 public | inventory     | table | postgres
 public | language      | table | postgres
 public | payment       | table | postgres
 public | rental        | table | postgres
 public | staff         | table | postgres
 public | store         | table | postgres
(15 rows)

자주 사용하는 psql 콘솔 명령¶

\l : 데이터베이스 목록
\c databasename : 데이터베이스 연결
\dt : 테이블 목록
\d+ tablename : 테이블 스키마

스키마 확인¶

dvdrental=# \d+ customer


                                                              Table "public.customer"
   Column    |            Type             |                           Modifiers                            | Storage  | Stats target | Description
-------------+-----------------------------+----------------------------------------------------------------+----------+--------------+-------------
 customer_id | integer                     | not null default nextval('customer_customer_id_seq'::regclass) | plain    |              |
 store_id    | smallint                    | not null                                                       | plain    |              |
 first_name  | character varying(45)       | not null                                                       | extended |              |
 last_name   | character varying(45)       | not null                                                       | extended |              |
 email       | character varying(50)       |                                                                | extended |              |
 address_id  | smallint                    | not null                                                       | plain    |              |
 activebool  | boolean                     | not null default true                                          | plain    |              |
 create_date | date                        | not null default ('now'::text)::date                           | plain    |              |
 last_update | timestamp without time zone | default now()                                                  | plain    |              |
 active      | integer                     |                                                                | plain    |              |
Indexes:
    "customer_pkey" PRIMARY KEY, btree (customer_id)
    "idx_fk_address_id" btree (address_id)
    "idx_fk_store_id" btree (store_id)
    "idx_last_name" btree (last_name)
Foreign-key constraints:
    "customer_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "payment" CONSTRAINT "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "rental" CONSTRAINT "rental_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON customer FOR EACH ROW EXECUTE PROCEDURE last_updated()

dvdrental=# \d+ payment


                                                              Table "public.payment"
    Column    |            Type             |                          Modifiers                           | Storage | Stats target | Description
--------------+-----------------------------+--------------------------------------------------------------+---------+--------------+-------------
 payment_id   | integer                     | not null default nextval('payment_payment_id_seq'::regclass) | plain   |              |
 customer_id  | smallint                    | not null                                                     | plain   |              |
 staff_id     | smallint                    | not null                                                     | plain   |              |
 rental_id    | integer                     | not null                                                     | plain   |              |
 amount       | numeric(5,2)                | not null                                                     | main    |              |
 payment_date | timestamp without time zone | not null                                                     | plain   |              |
Indexes:
    "payment_pkey" PRIMARY KEY, btree (payment_id)
    "idx_fk_customer_id" btree (customer_id)
    "idx_fk_rental_id" btree (rental_id)
    "idx_fk_staff_id" btree (staff_id)
Foreign-key constraints:
    "payment_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON UPDATE CASCADE ON DELETE RESTRICT
    "payment_rental_id_fkey" FOREIGN KEY (rental_id) REFERENCES rental(rental_id) ON UPDATE CASCADE ON DELETE SET NULL
    "payment_staff_id_fkey" FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT

dvdrental=# \d+ staff



                                                             Table "public.staff"
   Column    |            Type             |                        Modifiers                         | Storage  | Stats target | Description
-------------+-----------------------------+----------------------------------------------------------+----------+--------------+-------------
 staff_id    | integer                     | not null default nextval('staff_staff_id_seq'::regclass) | plain    |              |
 first_name  | character varying(45)       | not null                                                 | extended |              |
 last_name   | character varying(45)       | not null                                                 | extended |              |
 address_id  | smallint                    | not null                                                 | plain    |              |
 email       | character varying(50)       |                                                          | extended |              |
 store_id    | smallint                    | not null                                                 | plain    |              |
 active      | boolean                     | not null default true                                    | plain    |              |
 username    | character varying(16)       | not null                                                 | extended |              |
 password    | character varying(40)       |                                                          | extended |              |
 last_update | timestamp without time zone | not null default now()                                   | plain    |              |
 picture     | bytea                       |                                                          | extended |              |
Indexes:
    "staff_pkey" PRIMARY KEY, btree (staff_id)
Foreign-key constraints:
    "staff_address_id_fkey" FOREIGN KEY (address_id) REFERENCES address(address_id) ON UPDATE CASCADE ON DELETE RESTRICT
Referenced by:
    TABLE "payment" CONSTRAINT "payment_staff_id_fkey" FOREIGN KEY (staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
    TABLE "rental" CONSTRAINT "rental_staff_id_key" FOREIGN KEY (staff_id) REFERENCES staff(staff_id)
    TABLE "store" CONSTRAINT "store_manager_staff_id_fkey" FOREIGN KEY (manager_staff_id) REFERENCES staff(staff_id) ON UPDATE CASCADE ON DELETE RESTRICT
Triggers:
    last_updated BEFORE UPDATE ON staff FOR EACH ROW EXECUTE PROCEDURE last_updated()

SQL SELECT 명령¶

http://www.postgresqltutorial.com/postgresql-select/

SELECT * FROM customer;

vi 모드에서 나갈 때는 ESC + q

SELECT first_name,
 last_name,
 email
FROM customer;

SQL WHERE 명령¶

http://www.postgresqltutorial.com/postgresql-where/

SELECT last_name, first_name
FROM customer
WHERE first_name = 'Jamie';

SELECT last_name, first_name
FROM customer
WHERE first_name = 'Jamie' AND
 last_name = 'Rice';

SELECT customer_id,
 amount,
 payment_date
FROM payment
WHERE amount <= 1 OR amount >= 8;

SQL JOIN 명령¶

http://www.postgresqltutorial.com/postgresql-inner-join/

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id;

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
ORDER BY
 customer.customer_id;

SELECT
 customer.customer_id,
 first_name,
 last_name,
 email,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
WHERE
 customer.customer_id = 2;

SELECT
 customer.customer_id,
 customer.first_name customer_first_name,
 customer.last_name customer_last_name,
 customer.email,
 staff.first_name staff_first_name,
 staff.last_name staff_last_name,
 amount,
 payment_date
FROM
 customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
INNER JOIN staff ON payment.staff_id = staff.staff_id;

SQL GROUPBY 명령¶

http://www.postgresqltutorial.com/postgresql-group-by/

SELECT
 customer_id
FROM
 payment
GROUP BY
 customer_id;

SELECT
 customer_id,
 SUM (amount)
FROM
 payment
GROUP BY
 customer_id;

SELECT
 customer_id,
 SUM (amount)
FROM
 payment
GROUP BY
 customer_id
ORDER BY
 SUM (amount) DESC;

SELECT
 staff_id,
 COUNT (payment_id)
FROM
 payment
GROUP BY
 staff_id;

파이썬¶

$ conda install psycopg2

import psycopg2

conn = psycopg2.connect(host="localhost",database="dvdrental", user="admin", password="adminpass")

cur = conn.cursor()

cur.execute("SELECT * FROM customer;")

cur.fetchone()

(524,
 1,
 'Jared',
 'Ely',
 'jared.ely@sakilacustomer.org',
 530,
 True,
 datetime.date(2006, 2, 14),
 datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
 1)

cur.fetchmany(10)

[(1,
  1,
  'Mary',
  'Smith',
  'mary.smith@sakilacustomer.org',
  5,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (2,
  1,
  'Patricia',
  'Johnson',
  'patricia.johnson@sakilacustomer.org',
  6,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (3,
  1,
  'Linda',
  'Williams',
  'linda.williams@sakilacustomer.org',
  7,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (4,
  2,
  'Barbara',
  'Jones',
  'barbara.jones@sakilacustomer.org',
  8,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (5,
  1,
  'Elizabeth',
  'Brown',
  'elizabeth.brown@sakilacustomer.org',
  9,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (6,
  2,
  'Jennifer',
  'Davis',
  'jennifer.davis@sakilacustomer.org',
  10,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (7,
  1,
  'Maria',
  'Miller',
  'maria.miller@sakilacustomer.org',
  11,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (8,
  2,
  'Susan',
  'Wilson',
  'susan.wilson@sakilacustomer.org',
  12,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (9,
  2,
  'Margaret',
  'Moore',
  'margaret.moore@sakilacustomer.org',
  13,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1),
 (10,
  1,
  'Dorothy',
  'Taylor',
  'dorothy.taylor@sakilacustomer.org',
  14,
  True,
  datetime.date(2006, 2, 14),
  datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
  1)]

data = cur.fetchall()

data[0]

(11,
 2,
 'Lisa',
 'Anderson',
 'lisa.anderson@sakilacustomer.org',
 15,
 True,
 datetime.date(2006, 2, 14),
 datetime.datetime(2013, 5, 26, 14, 49, 45, 738000),
 1)

conn.close()

SQL 기초¶

기초 개념¶

교재¶

PostgreSQL¶

가동¶

콘솔¶

교재¶

샘플 데이터베이스 다운로드¶

샘플 데이터베이스 생성 (bash shell에서)¶

샘플 데이터베이스 생성 (psql 내부에서)¶

자주 사용하는 psql 콘솔 명령¶

스키마 확인¶

SQL SELECT 명령¶

SQL WHERE 명령¶

SQL JOIN 명령¶

SQL GROUPBY 명령¶

파이썬¶

질문/덧글