達人に学ぶSQL〜CASE式〜

 
おはようございます!愛知に行ってきます!
ちびまる子ちゃんランドに行くのがたのしみです。
 

この本のサブタイトルが通り、
「初心者で終わりたくない」ので読んでます。
中級者向けですね、わたしにはちょいムズです^^;
ただ、読み込めばいけそうな気がしてます!

 

今回は、その本の最初の節である「CASE式のススメ」を自分なりにまとめます。

CASE式の種類

– 単純CASE式
– 検索CASE式
「単純CASE式」で書ける =「検索CASE式」でも書ける

-- 単純CASE式
CASE  sex
  WHEN 1 THEN '男'
  WHEN 2 THEN '女'
ELSE 'その他' END

上の単純CASE式を検索CASE式で書くと、以下になります。

-- 検索CASE式
CASE
  WHEN sex = 1 THEN '男'
  WHEN sex = 2 THEN '女'
ELSE 'その他' END

CASE式の注意点

– CASE式の評価は真になるWHEN句が見つかった時点で打ち切られる。
よって、下位のWHEN句は無視される。

– 各分岐が返すデータの型は統一させる

– 「END」を忘れないようにする。→エラーはでる。

– 「ELSE X」は省略することができる。その場合は、「ELSE NULL」になる。
明示的に「ELSE X」は書くようにした方がいい!

– 「CASE文」ではない。「CASE式」

SELECT と GROUP BY で 同じCASE式を使う

→SELECT句の「AS {別名}」をGROUP BYで流用することができる。

※PostgrSQL,  MySQLで使用可

こんなテーブル(pop_tbl)があったとして、

# SELECT * FROM pop_tbl;
 pref_name | population 
-----------+------------
 徳島      |        100
 香川      |        200
 愛媛      |        150
 高知      |        200
 福岡      |        300
 佐賀      |        100
 長崎      |        200
 東京      |        400
 群馬      |         60

[結果] ←こうしたい

 region | sum 
--------+-----
 その他 | 660
 四国   | 650
 九州   | 400

別名を流用せずに書くと、こんな感じで長いです。

SELECT
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END) AS region
  ,SUM(population)
FROM pop_tbl
GROUP BY  
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END);

上記のような長いSQLを以下のように短くすることができます。

SELECT
  (CASE pref_name
    WHEN '徳島' THEN '四国'
    WHEN '香川' THEN '四国'
    WHEN '愛媛' THEN '四国'
    WHEN '高知' THEN '四国'
    WHEN '佐賀' THEN '九州'
    WHEN '福岡' THEN '九州'
  ELSE 'その他' END) AS region
  ,SUM(population)
FROM pop_tbl
GROUP BY region;

 

クロス形式で表にする

こんなテーブル(manju_tbl)があったとして、

# SELECT * FROM manju_tbl;
  name  | anco_id | count 
--------+---------+-------
 太郎   |       1 |    10
 太郎   |       2 |    10
 二郎   |       1 |    10
 二郎   |       2 |     0
 小次郎 |       1 |    14
 小次郎 |       2 |     8

※anco_id = 1→「黒あん」、anco_id = 2→「白あん」のイメージです。

[結果] ←こうしたい。
各人がkuroan_count/shiroroan_countを何回ずつ食べたのか1行でみれるようにしたい。

  name  | kuroan_count | shiroroan_count 
--------+--------------+-----------------
 二郎   |           10 |               0
 小次郎 |           14 |               8
 太郎   |           10 |              10

こんな感じです。
nameごとに1行にしたいので、GROUP BY nameを忘れない!
SUMで合計するので、ELSE 0にしにしたほうが読みやすいです。

SELECT
  name
  ,SUM(CASE WHEN anco_id = 1 THEN count ELSE 0 END) AS kuroan_count
  ,SUM(CASE WHEN anco_id = 2 THEN count ELSE 0 END) AS shiroroan_count
FROM manju_tbl
GROUP BY name;

 

CHECK制約でCASE式を使う

CONSTRAINT {制約名}  CHECK ({制約内容})

とし、制約に名前がつけれます。
これにより、エラー発生時に制約名が表示されるので、
どこで引っかかったのかわかりやすくなります。
CREATE TABLEやALTER TABLEで使えます。

 

今回の例では、
「女性社員の給与は20万円以下である」
という制約をみたすためのテーブル作成時のSQLです。
※この制約のポイントは男性社員に関しては、給与の制約がないことです。

CREATE TABLE Salaries(
  name VARCHAR(50),
  sex SMALLINT,
  salary INT CONSTRAINT Salaries_salary_check CHECK (
    CASE WHEN sex = 2
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
  )
);
--すでにテーブル Salariesがあり、カラムsexとカラムsalaryがある前提
ALTER TABLE Salaries ADD CONSTRAINT
  Salaries_salary_check CHECK (
    CASE WHEN sex = 2 
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
 );

 

データ挿入時に、制約が走ります。

-- 以下3つの INSERT文は 正常に追加されます。
INSERT INTO salaries VALUES('太郎', 1, 220000);  
INSERT INTO salaries VALUES('二郎', 1, 190000);
INSERT INTO salaries VALUES('花子', 2, 200000);
-- 制約に違反すると
-- 以下のSQL エラー と表示されます。
-- ERROR:  new row for relation "salaries" violates check constraint "salaries_salary_check"

INSERT INTO salaries VALUES('卑弥呼', 2, 220000);

 

 

制約名は省略もできます。

・制約名を省略したパターン

CREATE TABLE Salaries2(
  name VARCHAR(50),
  sex SMALLINT,
  salary INT CHECK (
    CASE WHEN sex = 2
         THEN CASE WHEN salary <= 200000
                   THEN 1 ELSE 0 END
    ELSE 1 END = 1
  )
);
--すでにテーブル Salaries2があり、カラムsexとカラムsalaryがある前提 
ALTER TABLE Salaries4 ADD CHECK (
 CASE WHEN sex = 2 
      THEN CASE WHEN salary <= 200000
                THEN 1 ELSE 0 END
 ELSE 1 END = 1
);

・エラーの場合の表記

-- ERROR: new row for relation "salaries2" violates check constraint "salaries2_check"

制約名を省略した場合のエラー時の制約名は、

制約名(デフォルト): {テーブル名}_check

ってなってますね。

 

PostgreSQL 9.4.5文書 では、以下のように書いてますね。詳細わかりませんね。

(この方法で制約名を指定しない場合は、システムにより名前が付けられます。)

 

まとめ

CASE式は、実行時に評価されて1つの値に定まるので、

列名や定数がかける部分であれば、どこにでもかけます!

今回例を出した以外の、UPDATE文や集約関数のなかでもCASE式は使うことができてとても便利です。

BEAR.SundayでAPIを作ってみた。

 

ども!最近、久々の一人暮らしになった私です。自由ですね。

前回の記事でクイックスタートをやってみたBEAR.Sundayですが、
APIが簡単に作れるということで、なんか使えるAPI作りたいなと考えました。

 

\名言APIを作りました。/

https://today-saying.herokuapp.com/

名言チョイスはわたし好みです。リロードしたらランダムにでます。
偉人のお言葉ってなんか元気でます!元気ない人は是非みてください!!

(攻撃はやめてくださいねw)

〜こんなやーつ〜
・名言を登録できる(INSERT)
・名言を削除できる(DELETE)
・名言をランダムに返す(RANDOM)
・名言一覧を返す(SHOW)

〜使ったもの〜
・PHP
・BEAR.Sunday
・SQLite3
・heroku

 

ソースはこちら。herokuでデプロイするために、調整してます。
https://github.com/kin29/bear-work

 

API:https://today-saying-sqlite.herokuapp.com/?mode=show

 

 

これ作るにあたって、わかったこととか後々書こうと思いますー

とりあえず、披露したかっただけです^^

 

MySQLとPostgreSQLのコマンド比較してみる。

前回記事で、コメントの比較しましたが、

これ→ MySQLとPostgreSQLのコメントの書き方のちがい

その実験してたときに、MySQL・PostgreSQLそれぞれのコマンド使ってたんですが、

なかなか違ってて使い分けムズってなったので、メモ用にとのことで書きまーす。

 

参考:

https://qiita.com/aosho235/items/c657e2fcd15fa0647471

https://qiita.com/tamano/items/be43de7bb733ad38362c

 

 

■接続

MySQL
ポイント → 「 -p 」を忘れがち。

mysql -u [ユーザ名] -p
Enter password: 

 

PostgreSQL

psql -U [ユーザ名]
Password for user [ユーザ名]: 

 

 

■データベース一覧をみる

MySQL

[ユーザ名]> show databases;
+--------------------+
| Database           |
+--------------------+
| db_name1           |
| db_name2           |
+--------------------+
3 rows in set (0.00 sec)

postgreSQL

[ユーザ名]=# \l
                                    List of databases
        Name         |    Owner     | Encoding | Collate | Ctype |   Access privileges   
---------------------+--------------+----------+---------+-------+-----------------------
db_name1             | table_user   | UTF8     | C       | C     | 

 

 

■データベースの切り替え

MySQL

[ユーザ名]> \u test_db
Database changed

postgreSQL

[ユーザ名]=# \c test_db
You are now connected to database "test_db" as user "[ユーザ名]".
test_db=# 

 

■テーブル一覧をみる

MySQL

[ユーザ名]> show tables;

postgreSQL

[ユーザ名]=# \d

 

■テーブル定義を確認

MySQL

[ユーザ名]> DESC test_table;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| title | varchar(255) | YES  |     | NULL    |                |
| body  | varchar(255) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

postgreSQL

[ユーザ名]=#\d test_table
                         Table "public.test_table"
 Column |  Type   |                        Modifiers                        
--------+---------+---------------------------------------------------------
 id     | integer | not null default nextval('test_table_id_seq'::regclass)
 title  | text    | 
 body   | text    | 

 

 

■SQLダンプ出力

MySQL

su - [ユーザ名]
mysqldump -p [データベース名] [テーブル名] > [ファイル名].sql

PostgreSQL

su - [ユーザ名]
pg_dump (-t [テーブル名]) [データベース名] > [ファイル名].sql

 

 

うーん地味に違う。

覚えきらんですねw

MySQLとPostgreSQLのコメントの書き方のちがい

 

どーも、最近ベランダでビール飲むのにはまってます。

すずしくて気持ちええです。

 

普段PostgreSQLを使うことが多かったのですが、

MySQLを久々触る機会があったので、ある違和感感じました!

コメントの書き方が違う!!!

 

例えばこんなテーブルがあるとします。(PostgreSQLにて)

test_db=#SELECT *FROM test_table;
id | title | body
----+--------+----------------
1 | top | トップですよ。
2 | detail | 詳細ですよ。

 

「–」をつかったコメントを入りのSELECT文。よく使ってるやつ。

PostgreSQLでは問題なく使える。コメントって認識してくれてる。うん。

test_db=# SELECT
test_db-# id,
test_db-# --nameはいらない
test_db-# body
test_db-# FROM
test_db-# test_table;
id | body
----+----------------
1 | トップですよ。
2 | 詳細ですよ。
(2 rows)

 

 

\しかし/

MySQLだとお。。。

mysql> SELECT * FROM test_table;
+----+--------+-----------------------+
| id | title | body |
+----+--------+-----------------------+
| 1 | top | トップですよ。 |
| 2 | detail | 詳細ですよ。 |
+----+--------+-----------------------+

 

 

「–」をつかったコメントを入りのSELECT文。MySQLでは怒られる。

「–」以下をカラムと認識してる。コメントって認識してないみたい。

mysql> SELECT
-> id,
-> --nameはいらない
-> body
-> FROM
-> test_table
-> ;
ERROR 1054 (42S22): Unknown column 'nameはいらない' in 'field list'

 

 

調べてみたら、MySQLの場合、「–」の後に半角スペースがいるみたい。

参考:https://dev.mysql.com/doc/refman/5.6/ja/comments.html

まじだった・・・。半角スペースいれたらできたし!

mysql> SELECT
-> id,
-> -- nameはいらない
-> body
-> FROM
-> test_table
-> ;
+----+-----------------------+
| id | body |
+----+-----------------------+
| 1 | トップですよ。 |
| 2 | 詳細ですよ。 |
+----+-----------------------+
2 rows in set (0.00 sec)

 

 

他にも….

●「#」をつかったコメントを入りのSELECT文。お!コメントって認識したみたい。

※「#」の後に、半角スペースはない。

mysql> SELECT
-> id,
-> #nameはいらない
-> body
-> FROM
-> test_table
-> ;
+----+-----------------------+
| id | body |
+----+-----------------------+
| 1 | トップですよ。 |
| 2 | 詳細ですよ。 |
+----+-----------------------+
2 rows in set (0.00 sec)

 

●「/*」「*/」をつかったコメントを入りのSELECT文。

※「/*」の後に、半角スペースはない。

※ 改行しても「/*」「*/」内はコメントとして認識

mysql> SELECT
-> id,
-> /*
/*> nameはいらない
/*> */
-> body
-> FROM
-> test_table
-> ;
+----+-----------------------+
| id | body |
+----+-----------------------+
| 1 | トップですよ。 |
| 2 | 詳細ですよ。 |
+----+-----------------------+
2 rows in set (0.00 sec)

 

 

\ちなみに!/

PosgreSQLでも「/*」「*/」は使えたーーーーーーーーあ。

全くしらんかった….

test_db=# SELECT
test_db-# id,
test_db-# /*
test_db*# nameはいらない
test_db*# */
test_db-# body
test_db-# FROM
test_db-# test_table
test_db-# ;
id | body
----+----------------
1 | トップですよ。
2 | 詳細ですよ。
(2 rows)

 

 

まあ、けど、PosgreSQLで「#」は使えないみたいです。

test_db=# SELECT
test_db-# id,
test_db-# #nameはいらない
test_db-# body
test_db-# FROM
test_db-# test_table
test_db-# ;
ERROR: column "nameはいらない" does not exist
LINE 3: #nameはいらない

 

 

PostgreSQLとMySQLってなんだかんだ、似てるからだいたい同じやろって

思ってたけど、改めて違いを認識。

コマンドでもデータベース切り替えの書き方違ったりで、なかなか勉強になりました。

 

SQLの色々

寒いですね。なんかすんごく太りました (x_x)

SQLの本で「なるほど」ってなったことを書きます。

 

■SERIAL型 …連番をふることができる。(postgreSQL)

CREATE TABLE test_db(
  id smaillint,
  name text
);
INSERT INTOtest_db VALUES(1,'ガズ');
INSERT INTO test_db VALUES(2,'シャーロット');
INSERT INTO test_db VALUES(3,'マーニー');

今時点では
id   |     name     |
——–+————–+-
1      | ガズ         |
2      | シャーロット |
3      | マーニー     |

 

そこに、SERIAL型のカラムnumberを追加する。

ALTER TABLE test_db ADD COLUMN number serial;

id   |     name     | number
——–+————–+——–+—–
1      | ガズ         |      1 |
2      | シャーロット|      2 |
3      | マーニー     |      3|

勝手に連番ふってくれる!!!

CREATE SEQUENCEをしなくても、シーケンスをつくってくれます。

 

カラムnumberを指定しなくても連番を勝手にいれてくれる!

INSERT INTO test_db VALUES(4,'アーロン');

id   |     name     | number
——–+————–+——–+—–
1      | ガズ         |      1 |
2      | シャーロット|      2 |
3      | マーニー     |      3|
4      | アーロン     |      4|

 

■複数の論理演算子の優先度

(1) NOT

(2) AND

(3) OR

 

■ JOIN の色々

▶︎INNER JOIN(内部結合)

書き方: JOIN テーブル名

片方にしか存在しない行は結合されない

 

▶︎OUTER JOIN(外部結合)

・FULL OUTER JOIN

書き方: FULL (OUTER) JOIN テーブル名

片方にしか存在しない行でも他方をNULLにして結合する

 

・LEFT OUTER JOIN

書き方:LEFT (OUTER) JOIN テーブル名

左テーブルにしか存在しない行は右テーブルをNULLにして結合する。

右テーブルにしか存在しない行は結合されない。

 

・RIGHT OUTER JOIN

書き方:RIGHT (OUTER) JOIN テーブル名

右テーブルにしか存在しない行は左テーブルをNULLにして結合する。

左テーブルにしか存在しない行は結合されない。

 

■集合関数におけるNULLの扱い

SUM/MAX/MIN/AVG … NULLは無視

→NULLを0に置き換えて集計したい時は、COALESCE関数を使うといい。

 

COUNT … カラム名指定の場合、NULLはカウントしない。

しかし、「*」の時はNULLを含めてカウントする。

→count(カラム名)とcount(*)で結果が異なることがある。

 

 

 

■SQLの区分

・DML …Data Manipulation Language – データ操作言語

SELECT / INSERT / UPDATE / DELETE など

 

・DDL …Data Definition language – データ定義言語

データベース、テーブル、ビューなどの基本データを定義・作成するための言語。

CREATE / ALTER / DROP など

インデックスやシーケンスを含む。データベールオブジェクトと言うこともある。

 

・DCL …Data Control Language – データ制御言語

データベースのユーザー権限の管理やデータのトランザクション処理を行う為の言語

GRANT / REVOKE / BEGIN / COMMIT / ROLLBACK など

 

■トランザクション

…複数の指示をひとかたまりのSQL文として扱うこと。

「一方のテーブルでは差し引き、他方のテーブルでは差し引いた分足す」

といった指示間を他の人に邪魔されると一貫性がなくなるものとかに使う。

 

トランザクション制御を扱うためのSQL

・BEGIN: 開始の指示。この指示以降のSQL文を1つのトランザクションとする。

・COMMIT: 終了の指示。この指示までを人のSQL文とし、変更するを確定する。

・ROLLBACK: 終了の指示。この指示までを人のSQL文とし、変更するを取り消す。

→ACID特性 の 原子性(1か0か)

 

■似た言葉だけど全然違う、ロールバックとロールフォワード。

・ロールバック…実行した処理を取り消す。SQLの実行失敗やデッドロークなどで発生する。

・ロールフォワード …まだ実行されていない処理を実行する。

障害復旧時、バックアップにより障害前の状態にした後、ログより、障害前から障害直前の状態までデータを更新すること。

 

■設計について

▶︎一般的な設計手順

(1)概念設計

(2)論理設計

(3)物理設計

 

▶︎正規化 ….(2)論理設計の時にする。

・第一正規形:繰り返しの列、重複列がない。

 

・第二正規形:部分従属がない

(例)部分従属とは…{A,B}→C = A→CまたはB→C

 

・第三正規形:推移従属がない

※推移従属とは…{A,B}→C = A→BかつB→CでB→Aではない。

(例)クラス→出席番号→名前

名前は、クラスと出席番号が特定されないとわからない、そんな関係性。

 

実際読んだ本がこちらです↓

重複排除して、他カラムも取得できるDISTINCT ON

 

SQLの話です。

使ったもの → Postgres

 

ここにこんなテーブルがあります。

# SELECT * FROM duplication_table;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 1 | 普通の牛乳 | 190 | 生乳100%
1 | 2 | 濃厚な牛乳 | 240 | 一番好きなやつ
1 | 3 | 生乳30%の牛乳 | 180 |
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
2 | 2 | ハーゲンなダッツ | 250 | たまに買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

 

Q.重複idは排除してidを知りたい時ってどうしてますか?

A.DISTINCTですよね!

# SELECT DISTINCT(id) FROM duplication_table;
id
—-
1
3
2

 

 

まあ、これはよく見る。

 

 

Q.「他のカラム情報も欲しいんや!」って時ありません?

A.朗報です!!DISTINCT ONってのがあります!

# SELECT DISTINCT ON(id)* FROM duplication_table;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 1 | 普通の牛乳 | 190 | 生乳100%
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

ほらね?けど、これどの条件で絞ってるんだっけ?

謎ですね。sub_idっぽいけど。。。。。

 

id中の最小priceを残して、重複排除

# SELECT DISTINCT ON(id)* FROM duplication_table ORDER BY id,price;
id | sub_id | name | price | description
—-+——–+——————+——-+———————-
1 | 3 | 生乳30%の牛乳 | 180 |
2 | 1 | スーパーなカップ | 120 | 結局これ買っちゃう
3 | 1 | ハッピーなターン | 150 | 粉が多いのもあるよね

こうすれば全カラム取得できました!!!

 

INNER JOINとOUTER JOIN

テーブルって結合すると便利ですよね。

結合しないと得たいものを得ることができないことがよくあります。

 

 

しかし、右にJOINする?左?いや、INNER?

ってなる

結果、副問い合わせをする。

パフォーマンス的にどうなの?ってなる。

 

 

ということで、今日の目標は

JOIN使いこなせるようになろう!

 

<データ>

# SELECT * FROM parent_table ORDER BY id;
id | name
—-+——–
1 | サザエ
2 | フネ
3 | タイ子

 

# SELECT * FROM child_table ORDER BY id;
id | name | parent_id
—-+——–+———–
1 | サザエ | 2
2 | カツオ | 2
3 | ワカメ | 2
4 | タラ     | 1
5 | イクラ | 3
6 | タマ     |

 

 

まずは、INNER JOIN

・child_tableにINNNER JOINするパターン

# SELECT * FROM child_table INNER JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子

 

タマはparent_idがないため、結合されませんでした。

 

・parent_tableにINNNER JOINするパターン

# SELECT * FROM parent_table INNER JOIN child_table ON parent_table.id =child_table. parent_id ORDER BY child_table.id;
id | name | id | name | parent_id
—-+———-+—-+———–+———–
2 | フネ  | 1 | サザエ | 2
2 | フネ  | 2 | カツオ | 2
2 | フネ  | 3 | ワカメ | 2
1 | サザエ | 4 | タラ  | 1
3 | タイ子 | 5 | イクラ | 3

 

ここでも、タマはparent_idがないため、結合されませんでした。

よって、左右テーブルどちらにINNER JOINしてもデータの内容としては変わらない。

また、フネ(parent_id=2)に関しては、3行あります。

 

 

▶︎INNER JOINは、

左右それぞれのテーブル.カラムの値が一致するレコードだけを取得する。

よって、一致しないレコードは削除される。

 

 

 

 

次はOUTER JOIN。

まずは、parent_tableにOUTER JOIN

parent_tableが右に来るのでRIGHT JOINしてます。

# SELECT * FROM child_table RIGHT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子

右のparent_tableを基本として結合するため、タマのように結合条件のparetn_table.idを持たないchild_table.parent_idは結合されませんでした。

 

 

次に、child_tableにOUTER JOIN

child_tableが左に来るのでLEFT JOINしてます。

# SELECT * FROM child_table LEFT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ  | | |

左のchild_tableを基本として結合するため、タマのように結合条件のchild_table.parent_idがparent_table.idに存在しなくてもNULLとして結合してくれる。

 

▶︎OUTER JOINは、

基本となったテーブル(左右どちらか)のレコードは全て取得し、左右それぞれのテーブル.カラムの値が一致しないレコードもNULLとして結合する。

 

 

※FULL JOINについて

 

ここで、データを追加する。

# INSERT INTO parent_table VALUES (4, ‘花澤さん’);

# SELECT * FROM parent_table ORDER BY id;
id | name
—-+———-
1 | サザエ
2 | フネ
3 | タイ子
4 | 花澤さん

 

# SELECT * FROM child_table ORDER BY id;
id | name | parent_id
—-+——–+———–
1 | サザエ | 2
2 | カツオ | 2
3 | ワカメ | 2
4 | タラ  | 1
5 | イクラ | 3
6 | タマ  |

 

おわかりの通り、花澤さんにひもづくchild_table.parent_idはない。

また、先ほどに続いてタマにひもづくparent_table.idもない。

 

>parent_table(右)に結合!

# SELECT * FROM child_table RIGHT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+———-
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
|    |  | 4 | 花澤さん

child_tableのタマのレコードはないが、

parent_tableは全て取得されている。

 

 

>child_table(左)に結合!

# SELECT * FROM child_table LEFT JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+——–
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ  |  |   |

parent_tableの花澤さんのレコードはないが、

child_tableは全て取得されている。

 

 

>>本題のFULL JOIN

# SELECT * FROM child_table FULL JOIN parent_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | parent_id | id | name
—-+——–+———–+—-+———-
1 | サザエ | 2 | 2 | フネ
2 | カツオ | 2 | 2 | フネ
3 | ワカメ | 2 | 2 | フネ
4 | タラ  | 1 | 1 | サザエ
5 | イクラ | 3 | 3 | タイ子
6 | タマ  |  |  |
|    |  | 4 | 花澤さん

# SELECT * FROM parent_table FULL JOIN child_table ON child_table.parent_id = parent_table.id ORDER BY child_table.id;
id | name | id | name | parent_id
—-+———-+—-+——–+———–
2 | フネ   | 1 | サザエ | 2
2 | フネ   | 2 | カツオ | 2
2 | フネ   | 3 | ワカメ | 2
1 | サザエ  | 4 | タラ  | 1
3 | タイ子  | 5 | イクラ | 3
|     | 6 | タマ  |
4 | 花澤さん |  |    |

左右どちらにJOINするにしても、左右全てのレコードを取得する。

 

 

UPDATE文の失敗。

 

UPDATE文にて結合テーブルに対してWHERE句で条件しぼってるのに全行アップデートされるという悪夢におそわれました。

原因不明です。解決策は見つけました!

 

<実行環境>

・PostgreSQL 8.4.20

・PhpPgAdmin 5.0.4

 

<データ>
fruits_table

id name description
1
日向夏 宮崎のくだもの
2
バナナ 私の嫌いなくだもの
3
ぶどう 私の好きなくだもの

 

price_table

id price
1
300
2
100
3
200

 

くっつけると、、、

id name description price
1
日向夏 宮崎のくだもの
300
2
バナナ 私の嫌いなくだもの
100
3
ぶどう 私の好きなくだもの
200

 

 

<失敗SQL>

UPDATE 
 price_table
SET
 price = 150
FROM
 price_table T1
 JOIN fruits_table T2
 ON T1.id = T2.id
WHERE
 T2.name = 'バナナ'
id name description id price
1
日向夏 宮崎のくだもの
1
150
2
バナナ 私の嫌いなくだもの
2
150
3
ぶどう 私の好きなくだもの
3
150

 

(゚⊿゚)!!!

「バナナ」だけpriceを150にしたいのに

すべてのpriceが150になっちゃいました><

 

 

落ち着いて見る

SELECT 
 *
FROM
 fruits_table T1
 INNER JOIN price_table T2
 ON T1.id = T2.id
WHERE 
 T1.name = 'バナナ'

これ(上のSQL)実行したらちゃんとバナナだけの行を選択できるのにな・・・。

id name description id price
2
バナナ 私の嫌いなくだもの
2
100

 

 

 

うーん、ASをつかわず以下を実行すると

UPDATE 
 price_table
SET
 price = 100
FROM
 price_table
 JOIN fruits_table
 ON fruits_table.id = price_table.id
WHERE
 fruits_table.name = 'バナナ'

SQL エラー:

ERROR:  table name "price_table" specified more than once

と言われる。

 

 

(教えて、Googleせんせーい!)

検索ワード「update テーブル結合 postgres」 ポチっ

発見したーーー!!!→リンク

JOINではなくWHEREで結合させるんだってさ。

UPDATE 
 price_table T1
SET
 price = 150
FROM
 fruits_table T2
WHERE
 T2.id = T2.id
 AND T2.name = 'バナナ'
id name description id price
1
日向夏 宮崎のくだもの
1
300
2
バナナ 私の嫌いなくだもの
2
150
3
ぶどう 私の好きなくだもの
3
200

 

これで思い通りのアップデートが無事できました^^

一意性制約違反について

 

今、実は「基本情報技術者試験」に向け、勉強してます。

んで、ちょうどデータベース分野について調べてました。

 

 

前回と内容がかぶるんですが、

今回は一意性制約(UNIQUEキー制約)について学んだことを書きますー!

 

 

一意性制約(UNIQUEキー制約)とは、、、

  • NULL以外の各行を一意に制約する。
  • ただし、NOT NULLではないためNULLを許します。
    →NULLが複数行存在することもあります。

 

私「NULL以外を一意にするだけで、NULL許すんだー、結構ゆる〜い。」

そこで気になる記事を見つけました。

 

複合一意キーにおいてすべてのキー列に対して NULL を持つ行も同様に複数持つことが可能。

ただし、1つ以上のキー列に対して NULL を持ち、その他のキー列に対して同じ組合せの値を持つ 2つの行は制約違反となる。(※)

一意キーは 1 つの表に 複数定義してもよい。

つまり

  • 単一列における一意キー制約において NULL が存在して NULL を INSERT してもは制約に違反しない。
  • 複合列における一意キー制約において
    (NULL,NULL) ⇔ (NULL,NULL) は制約に違反しないが
    (NULL,1000) ⇔ (NULL,1000) は一意キー制約違反となる。
    (1000,NULL,NULL) ⇔ (1000,NULL,NULL) も同様に違反となる。 (※)

https://www.shift-the-oracle.com/constraint/

 

 

ん?

よくわかんないのでやってみる。(バカです、はい)

※大好きなphpPgAdminでやってます。

 

CREATE TABLE id_number_unique_key_table(
  id integer UNIQUE,
  number integer UNIQUE
);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

INSERT INTO id_number_unique_key_table
VALUES(NULL, NULL);

 

うんうん、いける!

ユニークキーが複数のテーブルの定義

ユニークキーが複数のテーブルに(NULL,NULL)をINSERT

 

カラムid,number両方において、同じNULLです。

一見、ユニークじゃないのにいいの?ってなるけど、

一意性制約はNULL以外に関しての制約なのでいいのです。

 

前回の復習をすると、ユニークだけどNULL(プライマリキーではない)だから、

レコードの識別ができないため、削除/編集ができない。

 

 

 

しかし、以下の文は一意性制約違反です。

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

 

( ー`дー´)/ダメッ てされる。

一意性制約違反

カラムnumberにおいて、1000,1000でかぶってますもんね。

一意ではないです。

NULLでもないです。

だから怒られちゃうんです。

 

 

 

もちろんこれはできます!

INSERT INTO id_number_unique_key_table
VALUES(NULL, 1000);

INSERT INTO id_number_unique_key_table
VALUES(NULL, 2000);

カラムid.number両方とも、NULLかユニークです。

ユニークキー(一意性制約)はNULLかユニークであれば違反ではない!

どちらかが一意であれあ違反にはならない

 

 

主キーがあればphpPgAdminで編集できる

 

PhpPgAdminって便利ですよね。

でも、私はたまに以下のことを感じていました。

各レコードの編集/削除が出来るときと出来ないときがある。

この違いなんだ?

 

 

そこでタイミングよく、敏腕上司が教えてくれました。

 

上司「主キーがあればレコード識別ができるから、GUIで編集/削除できる」

 

私「すんごい!本当だー!しらなかったー!さすが●●さん!」

ちょっと考えると、当たり前のことだなと理解しましたwww

 

上司「ブログのネタにできるね」

 

以上、こんな会話がありました。早速ネタにします。

ありがとう●●さん。

 

 

そこで自分でいろいろ試してみました。

参考:https://www.postgresql.jp/document/9.4/html/ddl-constraints.html

 

その前に、、、

データベースは整合性を保つための整合性制約があります。

<整合性制約>

・一意性制約(ユニーク制約)

・参照制約(外部キー制約)

・非NULL制約(NOT NULL制約)

の3つがあります。

 

 

私「主キーはユニークだから一意性制約か!じゃあ、uniquであればいいのか!」

いつも通り、先走る私。

CREATE TABLE id_unique_table(
 id integer UNIQUE,
 name text
);

INSERT INTO id_unique_table
VALUES(1, test1);
INSERT INTO id_unique_table
VALUES(2, test2);
INSERT INTO id_unique_table
VALUES(3, test3);
INSERT INTO id_unique_table
VALUES(NULL, 'null');

 

一意性制約のマーク(「1」)が付いています

ユニークなテーブルの定義

ユニークでNOTNULLなテーブルのデータ

 

ん?

id=NULLのところは編集/削除できんやーん><

 

 

ってことは….NOT NULLも必要なのか!

さっきのCREATE TABLE文に非NULL制約を追加

CREATE TABLE id_not_null_unique_table(
 id integer UNIQUE NOT NULL,
 name text
);

INSERT INTO id_not_null_unique_table
VALUES(1, 'test1');
INSERT INTO id_not_null_unique_table
VALUES(2, 'test2');
INSERT INTO id_not_null_unique_table
VALUES(3, 'test3');

※INSERT INTO id_not_null_unique_table
VALUES(null, ‘null’); は非NULL制約に反するためINSERTできませんでした。

 

想定通り、非NULL制約が追加されました。

ユニークでNOTNULLなテーブルの定義

ユニークでNOTNULLなテーブルのデータ

 

 

私「なるほど。id=NULLのレコードが複数あったらレコードを識別できない。

ってことは、主キーは一意性制約非NULL制約が必要なんだ!」

 

 

つまり、

CREATE TABLE テーブル(
 id integer UNIQUE NOT NULL,
 name text
);

CREATE TABLE テーブル(
 id integer PRIMARY KEY,
 name text
);

は同じ!

 

試しに、後者を実行。

PRIMARY KEY宣言したら、勝手にNOT NULLが入ってた!

idが主キーなテーブルの定義

 

 

 

ユニークでNOT NULLじゃないと、

そりゃー誰でも識別できんですわ。