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
-
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文と同じことをできる
- 構文は違う
- 次章演習にて触る