OSSDB標準教科書3章 データベース定義 基礎編

PostgreSQLRDBSQL勉強メモ

OSSDB標準教科書

[http://oss-db.jp/ossdbtext/text.shtml:embed:cite]

3章 データベース定義 基礎編

表の作成(CREATE TABLE)

表を作成する

  • こういうの作る
列名 データ型
社員番号 id integer
氏名 name text
誕生日 birthday date
CREATE TABLE staff
(id integer,
name text,
birthday date);
CREATE TABLE
\d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | customer | table | postgres
 public | orders   | table | postgres
 public | prod     | table | postgres
 public | staff    | table | postgres
(4 rows)
\d staff
                Table "public.staff"
  Column  |  Type   | Collation | Nullable | Default
----------+---------+-----------+----------+---------
 id       | integer |           |          |
 name     | text    |           |          |
 birthday | date    |           |          |

ちゃんとできてるわね

staff表にデータを格納する

  • シャロちゃんを投入する
INSERT INTO staff(id, name, birthday) VALUES (1, '桐間紗路' , '2000-07-15');
INSERT 0 1
SELECT * FROM staff;
 id |     name     |  birthday
----+--------------+------------
  1 | 桐間紗路 | 2000-07-15

表定義の修正(ALTER TABLE)

ALTER TABLE staff ADD COLUMN customer_id integer;
ALTER TABLE
\d staff
                  Table "public.staff"
   Column    |  Type   | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
 id          | integer |           |          |
 name        | text    |           |          |
 birthday    | date    |           |          |
 customer_id | integer |           |          |
UPDATE staff SET customer_id = 1 WHERE id = 1;
UPDATE 1
SELECT * FROM staff;
 id |     name     |  birthday  | customer_id
----+--------------+------------+-------------
  1 | 桐間紗路 | 2000-07-15 |           1
(1 row)

表定義の修正は原則として行わない

  • データの不整合を招くため

    • すでにデータが格納されている状態での列の定義の変更
  • CREATE TABLE文を修正し、表を定義しなおせ
  • データを追加する場合は、表を追加して既存の表と関連付けろ
  • やむを得ないこともある

    • すでに大量の行データが格納されている
    • 新規に表を作成して結合するのが現実的でない

表の削除(DROP TABLE)

\d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | customer | table | postgres
 public | orders   | table | postgres
 public | prod     | table | postgres
 public | staff    | table | postgres
(4 rows)
DROP TABLE staff;
DROP TABLE
\d
          List of relations
 Schema |   Name   | Type  |  Owner
--------+----------+-------+----------
 public | customer | table | postgres
 public | orders   | table | postgres
 public | prod     | table | postgres
(3 rows)

DELETE文、DROP TABLE文、 TRUNCATE文の使い分け

  • DELETE

    • 対象データが多いと時間がかかる
  • DROP TABLE

    • 表と表データだけでなく、関連するその他のものも削除する
    • リファレンス日本語
    • リファレンス英語

      • インデックス
      • ルール
      • トリガ
      • 制約
      • CASCADEを付けると…

        • 依存するビュー
        • FK制約

          • テーブル自体は消さない
  • TRUNCATE

    • 行データだけ全削除

【補】TRUNCATEのmysqlとの挙動の違い

  • postgresのTRUNCATEは、自動増加キーの採番を初期化しない
CREATE TABLE staff
(id SERIAL,
name text,
birthday date);
                             Table "public.staff"
  Column  |  Type   | Collation | Nullable |              Default
----------+---------+-----------+----------+-----------------------------------
 id       | integer |           | not null | nextval('staff_id_seq'::regclass)
 name     | text    |           |          |
 birthday | date    |           |          |
INSERT INTO staff(name, birthday) VALUES ('桐間紗路' , '2000-07-15');
INSERT INTO staff(name, birthday) VALUES ('天々座理世' , '2000-02-14');

SELECT * FROM staff;
 id |      name       |  birthday
----+-----------------+------------
  1 | 桐間紗路    | 2000-07-15
  2 | 天々座理世 | 2000-02-14
(2 rows)
TRUNCATE staff;

INSERT INTO staff(name, birthday) VALUES ('桐間紗路' , '2000-07-15');
INSERT INTO staff(name, birthday) VALUES ('天々座理世' , '2000-02-14');

SELECT * FROM staff;
 id |      name       |  birthday
----+-----------------+------------
  3 | 桐間紗路    | 2000-07-15
  4 | 天々座理世 | 2000-02-14
(2 rows)

1からにならない(MySQLのAUTO_INCREMENTは1からになる)

行データのセーブ・ロード

行データのセーブ

 COPY staff TO '/var/lib/postgresql/data/staff.csv' (FORMAT csv);
COPY 2
  • \!でシェルコマンドを実行できる

    \! cat /var/lib/postgresql/data/staff.csv
1,桐間紗路,2000-07-15
2,天々座理世,2000-02-14

CSVファイルのロード

TRUNCATE staff;
SELECT * FROM staff;
 id | name | birthday
----+------+----------
(0 rows)
COPY staff FROM '/var/lib/postgresql/data/staff.csv' (FORMAT csv);
COPY 2
SELECT * FROM staff;
 id |      name       |  birthday
----+-----------------+------------
  1 | 桐間紗路    | 2000-07-15
  2 | 天々座理世 | 2000-02-14
(2 rows)

\COPYメタコマンド

  • COPY文と同じことをできる
  • 構文は違う
  • 次章演習にて触る