久しぶりにSQLをコンソールから触ったら面白かったので少し遊んでみます。

テーブル名を変更する

misaka=# alter table lockman rename to characters;
ALTER TABLE
misaka=# select * from characters;
 id |     name
----+--------------
  1 | 澁谷かのん
  2 | 唐可可
  3 | 平安名すみれ
  4 | 嵐千砂都
  5 | 葉月恋
(5 rows)

テーブルを作成する

misaka=# CREATE TABLE "profile" (
misaka(# "id" INTEGER NULL,
misaka(# "description" VARCHAR NULL
misaka(# )
misaka-# ;
CREATE TABLE
misaka=# select * from profile;
 id | description
----+-------------

insert

INSERT INTO profile (id,description) VALUES (2,'誕生日：7月17日 / 血液型：O型 / 身長159㎝');

外部結合１

misaka=# SELECT *
misaka-# FROM characters a
misaka-# LEFT OUTER JOIN profile b
misaka-# ON a.id = b.id
misaka-# WHERE a.id = '1';
 id |    name    | id |               description
----+------------+----+------------------------------------------
  1 | 澁谷かのん |  1 | 誕生日：5月1日 / 血液型：A型 / 身長159㎝
(1 row)

外部結合２

misaka=# SELECT *
misaka-# FROM characters a
misaka-# LEFT OUTER JOIN profile b
misaka-# ON a.id = b.id
misaka-# WHERE a.id = '6'
misaka-# ;
 id |   name   | id | description
----+----------+----+-------------
  6 | 涼風青葉 |    |
(1 row)

内部結合１

misaka=# SELECT *
misaka-# FROM characters a
misaka-# INNER JOIN profile b
misaka-# ON a.id = b.id
misaka-# WHERE a.id = '3'
misaka-# ;
 id |     name     | id |                description
----+--------------+----+--------------------------------------------
  3 | 平安名すみれ |  3 | 誕生日：9月28日 / 血液型：AB型 / 身長161㎝
(1 row)

内部結合２

misaka=# SELECT *
misaka-# FROM characters a
misaka-# INNER JOIN profile b
misaka-# ON a.id = b.id
misaka-# WHERE a.id = '6'
misaka-# ;
 id | name | id | description
----+------+----+-------------
(0 rows)

カラムを追加

misaka=# ALTER TABLE profile ADD hobby VARCHAR(20) NULL;
ALTER TABLE
misaka=# select * from profile;
 id |                description                 | hobby
----+--------------------------------------------+-------
  1 | 誕生日：5月1日 / 血液型：A型 / 身長159㎝   |
  2 | 誕生日：7月17日 / 血液型：O型 / 身長159㎝  |
  3 | 誕生日：9月28日 / 血液型：AB型 / 身長161㎝ |
  4 | 誕生日：2月25日 / 血液型：B型 / 身長155㎝  |
  5 | 誕生日：11月24日 / 血液型：A型 / 身長163㎝ |
(5 rows)

update

misaka=# UPDATE profile SET hobby = '短歌' WHERE id = 5;
UPDATE 1
misaka=# select * from profile;
 id |                description                 |  hobby
----+--------------------------------------------+----------
  1 | 誕生日：5月1日 / 血液型：A型 / 身長159㎝   | 歌
  2 | 誕生日：7月17日 / 血液型：O型 / 身長159㎝  | コスプレ
  3 | 誕生日：9月28日 / 血液型：AB型 / 身長161㎝ | 読書
  4 | 誕生日：2月25日 / 血液型：B型 / 身長155㎝  | ダンス
  5 | 誕生日：11月24日 / 血液型：A型 / 身長163㎝ | 短歌
(5 rows)

exists１

misaka=# SELECT *
misaka-# FROM characters
misaka-# WHERE EXISTS (
misaka(# SELECT 1 FROM profile
misaka(# WHERE profile.hobby = '短歌'
misaka(# );
 id |     name
----+--------------
  1 | 澁谷かのん
  2 | 唐可可
  3 | 平安名すみれ
  4 | 嵐千砂都
  5 | 葉月恋
  6 | 涼風青葉
(6 rows)

exists２

misaka=# SELECT *
misaka-# FROM characters
misaka-# WHERE EXISTS (
misaka(# SELECT 1 FROM profile
misaka(# WHERE characters.id = profile.id
misaka(# AND profile.hobby = '短歌'
misaka(# );
 id |  name
----+--------
  5 | 葉月恋
(1 row)

in

misaka=# SELECT *
misaka-# FROM characters
misaka-# WHERE id IN (
misaka(# SELECT id
misaka(# FROM profile
misaka(# WHERE hobby = 'ダンス'
misaka(# );
 id |   name
----+----------
  4 | 嵐千砂都
(1 row)

not in

misaka=# SELECT *
misaka-# FROM characters
misaka-# WHERE id NOT IN (
misaka(# SELECT id
misaka(# FROM profile
misaka(# WHERE hobby = 'ダンス'
misaka(# );
 id |     name
----+--------------
  1 | 澁谷かのん
  2 | 唐可可
  3 | 平安名すみれ
  5 | 葉月恋
  6 | 涼風青葉
(5 rows)

自己結合

カラム追加しました

misaka=# select * from characters order by id;
 id |     name     | sistersid | sistersname
----+--------------+-----------+-------------
  1 | 澁谷かのん   |         7 | 澁谷ありあ
  2 | 唐可可       |           |
  3 | 平安名すみれ |           |
  4 | 嵐千砂都     |           |
  5 | 葉月恋       |           |
  6 | 涼風青葉     |           |
  7 | 澁谷ありあ   |         1 | 澁谷かのん
(7 rows)

misaka=# SELECT a.*
misaka-# FROM characters a
misaka-# INNER JOIN characters b
misaka-# ON a.id = b.sistersid;
 id |    name    | sistersid | sistersname
----+------------+-----------+-------------
  7 | 澁谷ありあ |         1 | 澁谷かのん
  1 | 澁谷かのん |         7 | 澁谷ありあ
(2 rows)

SQLのメモ