OSSDB標準教科書1章 SQLによるデータベースの操作 基礎編

PostgreSQLRDB勉強メモ

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)