OSSDB標準教科書3章 データベース定義 基礎編
OSSDB標準教科書
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
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文と同じことをできる
- 構文は違う
- 次章演習にて触る
