[PostgreSQL]データの挿入・参照

テストデータ

テーブル名: students

id	name
1	Albert
3	Emily
14	John

テーブル名: dates

id	date
1	2010-02-25
2	2010-07-20
3	2012-12-15

テーブル名: test_scores

id	student_id	date_id	math	physics	chemistry
1	1	1	69	42	74
2	3	1	80	93	85
3	14	1	35	42	38
4	1	2	72	53	68
5	3	2	75	85	87
6	14	2	46	54	46
7	1	3	63	54	65
8	3	3	85	88	92
9	14	3	43	53	41

テーブル作成

testdb=# CREATE TABLE test_scores (id SERIAL PRIMARY KEY, student_id INTEGER NOT NULL, date_id INTEGER NOT NULL, math INTEGER, physics INTEGER, chemistry INTEGER);
testdb=# CREATE TABLE students (id SERIAL PRIMARY KEY, name TEXT);
testdb=# CREATE TABLE dates (id SERIAL PRIMARY KEY, date DATE);

データ挿入

studentsテーブルに登録。

testdb=# INSERT INTO students (id, name) VALUES (1, 'Albert');
INSERT 0 1
testdb=# INSERT INTO students (id, name) VALUES (3, 'Emily');
INSERT 0 1
testdb=# INSERT INTO students (id, name) VALUES (14, 'John');

datesテーブルに登録。

testdb=# INSERT INTO dates (date) VALUES (date('2010-02-25'));
INSERT 0 1
testdb=# INSERT INTO dates (date) VALUES (date('2010-07-20'));
INSERT 0 1
testdb=# INSERT INTO dates (date) VALUES (date('2012-12-15'));
INSERT 0 1

test_scoreテーブルに登録。

testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 1, 69, 42, 74);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 1, 80, 93, 85);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 1, 35, 42, 38);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 2, 72, 53, 68);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 2, 75, 85, 87);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 2, 46, 54, 46);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (1, 3, 63, 54, 65);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (3, 3, 85, 88, 92);
INSERT 0 1
testdb=# INSERT INTO test_scores (student_id, date_id, math, physics, chemistry) VALUES (14, 3, 43, 53, 41);

参照

結合したテーブルを表示する。

testdb=# SELECT * FROM test_scores JOIN students ON test_scores.student_id = students.id JOIN dates ON test_scores.date_id = dates.id;
 id | student_id | date_id | math | physics | chemistry | id | name | id | date
----+------------+---------+------+---------+-----------+----+--------+----+---- --------
  1 | 1 | 1 | 69 | 42 | 74 | 1 | Albert | 1 | 2010-02-25
  2 | 3 | 1 | 80 | 93 | 85 | 3 | Emily | 1 | 2010-02-25
  3 | 14 | 1 | 35 | 42 | 38 | 14 | John | 1 | 2010-02-25
  4 | 1 | 2 | 72 | 53 | 68 | 1 | Albert | 2 | 2010-07-20
  5 | 3 | 2 | 75 | 85 | 87 | 3 | Emily | 2 | 2010-07-20
  6 | 14 | 2 | 46 | 54 | 46 | 14 | John | 2 | 2010-07-20
  7 | 1 | 3 | 63 | 54 | 65 | 1 | Albert | 3 | 2012-12-15
  8 | 3 | 3 | 85 | 88 | 92 | 3 | Emily | 3 | 2012-12-15
  9 | 14 | 3 | 43 | 53 | 41 | 14 | John | 3 | 2012-12-15
(9 行)

testdb=# SELECT test_scores.id, students.name, dates.date, test_scores.math, test_scores.physics, test_scores.chemistry FROM test_scores JOIN students ON test_scores.student_id = students.id JOIN dates ON test_scores.date_id = dates.id;
 id | name | date | math | physics | chemistry
----+--------+------------+------+---------+-----------
  1 | Albert | 2010-02-25 | 69 | 42 | 74
  2 | Emily | 2010-02-25 | 80 | 93 | 85
  3 | John | 2010-02-25 | 35 | 42 | 38
  4 | Albert | 2010-07-20 | 72 | 53 | 68
  5 | Emily | 2010-07-20 | 75 | 85 | 87
  6 | John | 2010-07-20 | 46 | 54 | 46
  7 | Albert | 2012-12-15 | 63 | 54 | 65
  8 | Emily | 2012-12-15 | 85 | 88 | 92
  9 | John | 2012-12-15 | 43 | 53 | 41
(9 行)