OSSDB標準教科書
[http://oss-db.jp/ossdbtext/text.shtml:embed:cite]
1章
DB利用の基本パターン
- CREATE TABLE
- INSERT
- SELECT
- UPDATE
- DELETE
psqlツールの利用
環境構築はこれだけでよかった
docker run -it -v postgres:/var/lib/postgresql/data postgres bash
- CMDを
bash
で上書きしたため、postgresデーモンが動かない - Dockerfileを読み解いて下記をバックグラウンドで実行
root@02d21791bc03:/# ./docker-entrypoint.sh postgres &
psqlでデータベースに接続する
- psqlはrootで使えないのでpostgresユーザにログイン
su - postgres
DB一覧表示
postgres@02d21791bc03:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
DBつくる
postgres@02d21791bc03:~$ createdb ossdb
postgres@02d21791bc03:~$ psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+------------+------------+-----------------------
ossdb | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
DBに接続する
postgres@02d21791bc03:~$ psql ossdb
psql (11.1 (Debian 11.1-1.pgdg90+1))
Type "help" for help.
ossdb=#
ヘルプ
ossdb=# help
You are using psql, the command-line interface to PostgreSQL.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
メタコマンド
- こいつら
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
\h
SQLコマンド一覧出る
ossdb=# \h
Available help:
ABORT CREATE FOREIGN DATA WRAPPER DROP ROUTINE
ALTER AGGREGATE CREATE FOREIGN TABLE DROP RULE
ALTER COLLATION CREATE FUNCTION DROP SCHEMA
ALTER CONVERSION CREATE GROUP DROP SEQUENCE
ALTER DATABASE CREATE INDEX DROP SERVER
...
- 特定のSQLコマンドのヘルプ
ossdb=# \h DELETE
Command: DELETE
Description: delete rows of a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
- メタメタコマンド
ossdb=# psql \?
General
\copyright show PostgreSQL usage and distribution terms
\crosstabview [COLUMNS] execute query and display results in crosstab
\errverbose show most recent error message at maximum verbosity
\g [FILE] or ; execute query (and send results to file or |pipe)
\gdesc describe result of query, without executing it
psqlを終了する
これ
\gx [FILE] as \g, but forces expanded output mode
\q quit psql
\watch [SEC] execute query every SEC seconds
ossdb-# \q
postgres@02d21791bc03:~$
表の確認
これ
Informational
(options: S = show system objects, + = additional detail)
\d[S+] list tables, views, and sequences
\d[S+] NAME describe table, view, sequence, or index
\da[S] [PATTERN] list aggregates
ossdb=# \d
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | customer | table | postgres
public | orders | table | postgres
public | prod | table | postgres
(3 rows)
ossdb=# \d customer
Table "public.customer"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+---------
customer_id | integer | | |
customer_name | text | | |
ossdb=# \d orders
Table "public.orders"
Column | Type | Collation | Nullable | Default
-------------+-----------------------------+-----------+----------+---------
order_id | integer | | |
order_date | timestamp without time zone | | |
customer_id | integer | | |
prod_id | integer | | |
qty | integer | | |
ossdb=# \d prod
Table "public.prod"
Column | Type | Collation | Nullable | Default
-----------+---------+-----------+----------+---------
prod_id | integer | | |
prod_name | text | | |
price | integer | | |
表とテーブル、リレーション
List of relations
Table "public.prod"
- 表とテーブルは基本的に同じものと考えて良い
-
リレーションはデータの集合
- RDBでは表形式なので、表とほぼ同義と考えてよい
SQLの実行方法
;
を打つまで複数行書けるよ
テキストファイルからの読み込み実行
postgres@02d21791bc03:~$ echo '\d' > test.sql
postgres@02d21791bc03:~$ psql -f test.sql ossdb
List of relations
Schema | Name | Type | Owner
--------+----------+-------+----------
public | customer | table | postgres
public | orders | table | postgres
public | prod | table | postgres
(3 rows)
データの検索(SELECT)
ossdb=# SELECT * FROM customer;
customer_id | customer_name
-------------+---------------
1 | 佐藤商事
2 | 鈴木物産
3 | 高橋商店
(3 rows)
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
(3 rows)
ossdb=# SELECT * FROM orders;
order_id | order_date | customer_id | prod_id | qty
----------+----------------------------+-------------+---------+-----
1 | 2019-01-28 22:32:48.41173 | 1 | 1 | 10
2 | 2019-01-28 22:32:48.433042 | 2 | 2 | 5
3 | 2019-01-28 22:32:48.457791 | 3 | 3 | 8
4 | 2019-01-28 22:32:48.483149 | 2 | 1 | 3
5 | 2019-01-28 22:32:49.560937 | 3 | 2 | 4
(5 rows)
SELECT項目リスト
ossdb=# SELECT prod_name,price FROM prod;
prod_name | price
-----------+-------
みかん | 50
りんご | 70
メロン | 100
(3 rows)
WHERE句による絞り込み検索
等しい、等しくない
ossdb=# SELECT * FROM customer WHERE customer_id = 2;
customer_id | customer_name
-------------+---------------
2 | 鈴木物産
(1 row)
ossdb=# SELECT * FROM customer WHERE customer_id <> 2;
customer_id | customer_name
-------------+---------------
1 | 佐藤商事
3 | 高橋商店
(2 rows)
ossdb=# SELECT * FROM customer WHERE customer_name='佐藤商事';
customer_id | customer_name
-------------+---------------
1 | 佐藤商事
(1 row)
大なり・小なり、以上・以下
ossdb=# SELECT * FROM prod WHERE price > 70;
prod_id | prod_name | price
---------+-----------+-------
3 | メロン | 100
(1 row)
ossdb=# SELECT * FROM prod WHERE price >= 70;
prod_id | prod_name | price
---------+-----------+-------
2 | りんご | 70
3 | メロン | 100
(2 rows)
ORDER BY句による並べ替え
- デフォルトASC(昇順)
ossdb=# SELECT * FROM prod ORDER BY price;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
(3 rows)
- DESC(降順)
ossdb=# SELECT * FROM prod ORDER BY price DESC;
prod_id | prod_name | price
---------+-----------+-------
3 | メロン | 100
2 | りんご | 70
1 | みかん | 50
(3 rows)
表の結合
JOIN句による結合
- 結合を行う元の表
orders
ossdb=# SELECT order_id, customer_id, prod_id, qty FROM orders;
order_id | customer_id | prod_id | qty
----------+-------------+---------+-----
1 | 1 | 1 | 10
2 | 2 | 2 | 5
3 | 3 | 3 | 8
4 | 2 | 1 | 3
5 | 3 | 2 | 4
(5 rows)
SELECT orders.order_id
,customer.customer_name
,prod.prod_name
,orders.qty
FROM orders
JOIN customer ON orders.customer_id = customer.customer_id
JOIN prod ON orders.prod_id = prod.prod_id;
order_id | customer_name | prod_name | qty
----------+---------------+-----------+-----
1 | 佐藤商事 | みかん | 10
4 | 鈴木物産 | みかん | 3
2 | 鈴木物産 | りんご | 5
5 | 高橋商店 | りんご | 4
3 | 高橋商店 | メロン | 8
(5 rows)
行データの入力(INSERT)
ossdb=# INSERT INTO prod(prod_id, prod_name, price) VALUES (4, 'バナナ', 30);
INSERT 0 1
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
4 | バナナ | 30
(4 rows)
データの更新(UPDATE)
- 絶対値
ossdb=# UPDATE prod SET price = 40 WHERE prod_id = 4;
UPDATE 1
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
4 | バナナ | 40
(4 rows)
- 元の値からの相対値(+10, -10)
ossdb=# UPDATE prod SET price = price + 10;
UPDATE 4
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 60
2 | りんご | 80
3 | メロン | 110
4 | バナナ | 50
(4 rows)
ossdb=# UPDATE prod SET price = price - 10;
UPDATE 4
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
4 | バナナ | 40
(4 rows)
行データの削除
ossdb=# DELETE FROM prod WHERE prod_id = 4;
DELETE 1
ossdb=# SELECT * FROM prod;
prod_id | prod_name | price
---------+-----------+-------
1 | みかん | 50
2 | りんご | 70
3 | メロン | 100
(3 rows)