다운로드
작성자: admin 작성일시: 2017-05-25 20:52:13 조회수: 1808 다운로드: 105
카테고리: 개발/운용 환경 태그목록:

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

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

교재

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

$ 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 명령

SELECT * FROM customer;

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

SELECT first_name,
 last_name,
 email
FROM customer;

SQL 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 명령

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 명령

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
In:
import psycopg2
In:
conn = psycopg2.connect(host="localhost",database="dvdrental", user="admin", password="adminpass")
In:
cur = conn.cursor()
In:
cur.execute("SELECT * FROM customer;")
In:
cur.fetchone()
Out:
(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)
In:
cur.fetchmany(10)
Out:
[(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)]
In:
data = cur.fetchall()
In:
data[0]
Out:
(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)
In:
conn.close()

질문/덧글

아직 질문이나 덧글이 없습니다. 첫번째 글을 남겨주세요!