勉強日記

チラ裏

OSSDB標準教科書4章 基礎編 演習

RDB SQL PostgreSQL 勉強メモ

OSSDB標準教科書

oss-db.jp

4章 基礎編 演習

演習1

表の確認

ossdb=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | customer     | table    | postgres
 public | orders       | table    | postgres
 public | prod         | table    | postgres
 public | staff        | table    | postgres
 public | staff_id_seq | sequence | postgres
(5 rows)
SELECT * FROM prod;

 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん |    50
       2 | りんご |    70
       3 | メロン |   100
(3 rows)

すべての商品の価格を10%アップ

UPDATE prod SET price = price * 1.1;

SELECT * FROM prod;

 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん |    55
       2 | りんご |    77
       3 | メロン |   110
(3 rows)

価格が100以上の商品の価格を元に戻す

UPDATE prod SET price = price / 1.1 WHERE price >= 100;

SELECT * FROM prod;

 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん |    55
       2 | りんご |    77
       3 | メロン |   100
(3 rows)

表のデータをすべてファイルにコピー

  • あえてメタコマンドでやってみる
ossdb=# \copy prod to '/var/lib/postgresql/data/prod.csv' with csv
COPY 3
ossdb=# \copy orders to '/var/lib/postgresql/data/orders.csv' with csv
COPY 5
ossdb=# \copy customer to '/var/lib/postgresql/data/customer.csv' with csv
COPY 3
ossdb=# \! bash -c "cat /var/lib/postgresql/data/{prod,orders,customer}.csv"

1,みかん,55
2,りんご,77
3,メロン,100
1,2019-01-28 23:04:57.664912,1,1,10
2,2019-01-28 23:04:57.689655,2,2,5
3,2019-01-28 23:04:57.714919,3,3,8
4,2019-01-28 23:04:57.742378,2,1,3
5,2019-01-28 23:04:57.764938,3,2,4
1,佐藤商事
2,鈴木物産
3,高橋商店

表をすべて削除

DROP TABLE prod, orders, customer;

ossdb=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | staff        | table    | postgres
 public | staff_id_seq | sequence | postgres
(2 rows)

表を再度作成

CREATE TABLE prod
(prod_id   integer,
 prod_name text,
 price     integer);

CREATE TABLE customer
(customer_id integer,
 customer_name text);

CREATE TABLE orders
(order_id integer,
 order_date timestamp,
 customer_id integer,
 prod_id integer,
 qty integer);

ossdb=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | customer     | table    | postgres
 public | orders       | table    | postgres
 public | prod         | table    | postgres
 public | staff        | table    | postgres
 public | staff_id_seq | sequence | postgres
(5 rows)

データをファイルからコピー

ossdb=# \copy prod from '/var/lib/postgresql/data/prod.csv' with csv
COPY 3
ossdb=# \copy orders from '/var/lib/postgresql/data/orders.csv' with csv
COPY 5
ossdb=# \copy customer from '/var/lib/postgresql/data/customer.csv' with csv
COPY 3
ossdb=# SELECT * FROM prod;
 prod_id | prod_name | price
---------+-----------+-------
       1 | みかん |    55
       2 | りんご |    77
       3 | メロン |   100
(3 rows)

ossdb=# SELECT * FROM orders
 order_id |         order_date         | customer_id | prod_id | qty
----------+----------------------------+-------------+---------+-----
        1 | 2019-01-28 23:04:57.664912 |           1 |       1 |  10
        2 | 2019-01-28 23:04:57.689655 |           2 |       2 |   5
        3 | 2019-01-28 23:04:57.714919 |           3 |       3 |   8
        4 | 2019-01-28 23:04:57.742378 |           2 |       1 |   3
        5 | 2019-01-28 23:04:57.764938 |           3 |       2 |   4
(5 rows)

ossdb=# SELECT * FROM customer;
 customer_id | customer_name
-------------+---------------
           1 | 佐藤商事
           2 | 鈴木物産
           3 | 高橋商店
(3 rows)

郵便番号データベース

郵便番号データのダウンロード

  • postgresコンテナにwgetとかunzipとかが入ってなかった
apt update && apt install wget unzip -y
  • 郵便番号データを拾ってくる
wget http://www.post.japanpost.jp/zipcode/dl/oogaki/zip/ken_all.zip

ken_all.zip                   100%[=================================================>]   1.61M  6.60MB/s    in 0.2s

2019-02-07 13:00:22 (6.60 MB/s) - ‘ken_all.zip’ saved [1686808/1686808]
unzip ken_all.zip

Archive:  ken_all.zip
  inflating: KEN_ALL.CSV
  • 見てみる
head -5 KEN_ALL.CSV

01101,"060  ","0600000","�¶��޳","����ۼ�ճ���","��ƹ�����Ų�ޱ�","�k�C��","�D�y�s������"," �ȉ��Ɍf�ڂ��Ȃ��ꍇ",0,0,0,0,0,0
01101,"064  ","0640941","�¶��޳","����ۼ�ճ���","��˶޵�","�k�C��","�D�y�s������","���P�u",0,0,1,0,0,0
01101,"060  ","0600041","�¶��޳","����ۼ�ճ���","���޵�˶޼","�k�C��","�D�y�s������","���ʓ�",0,0,1,0,0,0
01101,"060  ","0600042","�¶��޳","����ۼ�ճ���","���޵�Ƽ(1-19�ֳ�)","�k�C��","�D�y�s������"," ���ʐ��i�P�`�P�X���ځj",1,0,1,0,0,0
01101,"064  ","0640820","�¶��޳","����ۼ�ճ���","���޵�Ƽ(20-28�ֳ�)","�k�C��","�D�y�s������","���ʐ��i�Q�O�`�Q�W���ځj",1,0,1,0,0,0
  • 申し訳ないがSHIFT-JISはNG
apk install nkf

nkf -w KEN_ALL.CSV > KEN_ALL_UTF8.CSV
head -5 KEN_ALL_UTF8.CSV

01101,"060  ","0600000","ホツカイドウ","サツポロシチユウオウク","イカニケイサイガナイバアイ","北海道","札幌市中央区","以下に掲載がない場合",0,0,0,0,0,0
01101,"064  ","0640941","ホツカイドウ","サツポロシチユウオウク","アサヒガオカ","北海道","札幌市中央区","旭ケ丘",0,0,1,0,0,0
01101,"060  ","0600041","ホツカイドウ","サツポロシチユウオウク","オオドオリヒガシ","北海道","札幌市中央区","大通東",0,0,1,0,0,0
01101,"060  ","0600042","ホツカイドウ","サツポロシチユウオウク","オオドオリニシ(1-19チヨウメ)","北海道","札幌市中央区","大通西（１〜１９丁目）",1,0,1,0,0,0
01101,"064  ","0640820","ホツカイドウ","サツポロシチユウオウク","オオドオリニシ(20-28チヨウメ)","北海道","札幌市中央区","大通西（２０〜２８丁目）",1,0,1,0,0,0

郵便番号データベース表の作成

CREATE TABLE zip(
lgcode    char(5),
oldzip    char(5),
newzip    char(7),
prefkana  text,
citykana  text,
areakana  text,
pref      text,
city      text,
area      text,
largearea integer,
koaza     integer,
choume    integer,
smallarea integer,
change    integer,
reason    integer
);

ossdb=# \d
              List of relations
 Schema |     Name     |   Type   |  Owner
--------+--------------+----------+----------
 public | customer     | table    | postgres
 public | orders       | table    | postgres
 public | prod         | table    | postgres
 public | staff        | table    | postgres
 public | staff_id_seq | sequence | postgres
 public | zip          | table    | postgres
(6 rows)

ossdb=# \d zip
                    Table "public.zip"
  Column   |     Type     | Collation | Nullable | Default
-----------+--------------+-----------+----------+---------
 lgcode    | character(5) |           |          |
 oldzip    | character(5) |           |          |
 newzip    | character(7) |           |          |
 prefkana  | text         |           |          |
 citykana  | text         |           |          |
 areakana  | text         |           |          |
 pref      | text         |           |          |
 city      | text         |           |          |
 area      | text         |           |          |
 largearea | integer      |           |          |
 koaza     | integer      |           |          |
 choume    | integer      |           |          |
 smallarea | integer      |           |          |
 change    | integer      |           |          |
 reason    | integer      |           |          |

データの文字コードについて

  • nkfでUTF-8にした
  • psqlの\encodingメタコマンドでSJISにしてから\copy fromして、UTF-8に戻すでもよい
ossdb=# \copy zip from KEN_ALL_UTF8.CSV with csv
COPY 124250

郵便番号データの確認

SELECT * FROM zip WHERE newzip = '1500002';

 lgcode | oldzip | newzip  |      prefkana      |   citykana   | areakana  |   pref    |   city    |  area  | largearea| koaza | choume | smallarea | change | reason
--------+--------+---------+--------------------+--------------+-----------+-----------+-----------+--------+-----------+-------+--------+-----------+--------+--------
 13113  | 150    | 1500002 | トウキヨウト | シブヤク | シブヤ | 東京都 | 渋谷区 | 渋谷 |         0 |     0 |      1 |         0 |      0 |      0
(1 row)
  • まいてつの聖地
SELECT * FROM zip WHERE city = '人吉市' LIMIT 5;

 lgcode | oldzip | newzip  |      prefkana      |    citykana     |                areakana                 |   pref    |   city    |              area              | largearea | koaza | choume | smallarea | change | reason
--------+--------+---------+--------------------+-----------------+-----------------------------------------+-----------+-----------+--------------------------------+-----------+-------+--------+-----------+--------+--------
 43203  | 868    | 8680000 | クマモトケン | ヒトヨシシ | イカニケイサイガナイバアイ | 熊本県 | 人吉市 | 以下に掲載がない場合 |         0 |     0 |      0 |         1 |      0 |      0
 43203  | 86808  | 8680825 | クマモトケン | ヒトヨシシ | アカイケハラマチ                | 熊本県 | 人吉市 | 赤池原町                   |         0 |     0 |      0 |         0 |      0 |      0
 43203  | 86808  | 8680824 | クマモトケン | ヒトヨシシ | アカイケミズナシマチ          | 熊本県 | 人吉市 | 赤池水無町                |         0 |     0 |      0 |         0 |      0 |      0
 43203  | 868    | 8680035 | クマモトケン | ヒトヨシシ | イツカマチ                         | 熊本県 | 人吉市 | 五日町                      |         0 |     0 |      0 |         0 |      0 |      0
 43203  | 868    | 8680027 | クマモトケン | ヒトヨシシ | イノクチマチ                      | 熊本県 | 人吉市 | 井ノ口町                   |         0 |     0 |      0 |         0 |      0 |      0
(5 rows)
  • ものべのの聖地
SELECT * FROM zip WHERE city = '香美市' AND area LIKE '物部町%';

 lgcode | oldzip | newzip  |    prefkana     | citykana  |                    areakana                     |   pref    |   city    |              area              | largearea | koaza | choume | smallarea | change | reason
--------+--------+---------+-----------------+-----------+-------------------------------------------------+-----------+-----------+--------------------------------+-----------+-------+--------+-----------+--------+--------
 39212  | 78146  | 7814643 | コウチケン | カミシ | モノベチヨウイチウ                     | 高知県 | 香美市 | 物部町市宇                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814401 | コウチケン | カミシ | モノベチヨウオオドチ                  | 高知県 | 香美市 | 物部町大栃                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814528 | コウチケン | カミシ | モノベチヨウオオニシ                  | 高知県 | 香美市 | 物部町大西                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78146  | 7814641 | コウチケン | カミシ | モノベチヨウオカノウチ               | 高知県 | 香美市 | 物部町岡ノ内             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814403 | コウチケン | カミシ | モノベチヨウオスダニ                  | 高知県 | 香美市 | 物部町押谷                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814524 | コウチケン | カミシ | モノベチヨウカジサコ                  | 高知県 | 香美市 | 物部町楮佐古             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814523 | コウチケン | カミシ | モノベチヨウカミイケ                  | 高知県 | 香美市 | 物部町神池                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814531 | コウチケン | カミシ | モノベチヨウクボ                        | 高知県 | 香美市 | 物部町久保                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814522 | コウチケン | カミシ | モノベチヨウクロダイ                  | 高知県 | 香美市 | 物部町黒代                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814526 | コウチケン | カミシ | モノベチヨウゴオドウ                  | 高知県 | 香美市 | 物部町五王堂             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814404 | コウチケン | カミシ | モノベチヨウコハマ                     | 高知県 | 香美市 | 物部町小浜                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814530 | コウチケン | カミシ | モノベチヨウササ                        | 高知県 | 香美市 | 物部町笹                   |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814413 | コウチケン | カミシ | モノベチヨウシヨウダニアイ         | 高知県 | 香美市 | 物部町庄谷相             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814406 | コウチケン | カミシ | モノベチヨウセンドウ                  | 高知県 | 香美市 | 物部町仙頭                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814412 | コウチケン | カミシ | モノベチヨウツブセ                     | 高知県 | 香美市 | 物部町拓                   |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814414 | コウチケン | カミシ | モノベチヨウトンジヨウ               | 高知県 | 香美市 | 物部町頓定                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814529 | コウチケン | カミシ | モノベチヨウナカウエ                  | 高知県 | 香美市 | 物部町中上                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814411 | コウチケン | カミシ | モノベチヨウナカタニガワ            | 高知県 | 香美市 | 物部町中谷川             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 784    | 7840065 | コウチケン | カミシ | モノベチヨウナカツオ                  | 高知県 | 香美市 | 物部町中津尾             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814405 | コウチケン | カミシ | モノベチヨウネキヤ                     | 高知県 | 香美市 | 物部町根木屋             |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78146  | 7814642 | コウチケン | カミシ | モノベチヨウベツチヤク               | 高知県 | 香美市 | 物部町別役                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78146  | 7814644 | コウチケン | カミシ | モノベチヨウベフ                        | 高知県 | 香美市 | 物部町別府                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814415 | コウチケン | カミシ | モノベチヨウマイカワ                  | 高知県 | 香美市 | 物部町舞川                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814527 | コウチケン | カミシ | モノベチヨウミナミイケ               | 高知県 | 香美市 | 物部町南池                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814521 | コウチケン | カミシ | モノベチヨウヤスマル                  | 高知県 | 香美市 | 物部町安丸                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78145  | 7814525 | コウチケン | カミシ | モノベチヨウヤナイセ                  | 高知県 | 香美市 | 物部町柳瀬                |         0 |     0 |      0 |         0 |      0 |      0
 39212  | 78146  | 7814645 | コウチケン | カミシ | モノベチヨウヤマサキ(クワノカワ) | 高知県 | 香美市 | 物部町山崎（桑ノ川） |         1 |     0 |      0 |         0 |      0 |      0
 39212  | 78144  | 7814402 | コウチケン | カミシ | モノベチヨウヤマサキ(ソノタ)       | 高知県 | 香美市 | 物部町山崎（その他） |         1 |     0 |      0 |         0 |      0 |      0
(28 rows)