ssh 프로그램이나 docker attach 명령, 또는 jupyter notebook의 terminal을 이용하여 도커 컨테이너 콘솔로 들어간 뒤
sudo service postgresql start
콘솔에서 데이터베이스르 조작하는 명령은 권한을 가진 postgres
라는 계정으로 실행해야 하므로 항상 sudo -u postgres
를 붙여서 실행한다.
sudo -u postgres psql
postgres=# \q
postgres=# \?
postgres=# \h
$ wget -O dvdrental.zip http://www.postgresqltutorial.com/download/dvd-rental-sample-database/?wpdmdl=969
$ unzip dvdrental.zip
$ 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)
$ 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)
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()
SELECT * FROM customer;
vi 모드에서 나갈 때는 ESC + q
SELECT first_name,
last_name,
email
FROM customer;
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;
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;
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()
cur.fetchmany(10)
data = cur.fetchall()
data[0]
conn.close()