カテゴリー: 技術者っぽいこと

寒いですね。なんかすんごく太りました (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特性　の　原子性(１か0か)

 

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

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

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

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

 

■設計について

▶︎一般的な設計手順

(1)概念設計

(2)論理設計

(3)物理設計

 

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

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

 

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

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

 

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

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

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

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

 

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

 

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 | 粉が多いのもあるよね

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

 

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

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

 

 

しかし、右に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)に関しては、３行あります。

 

 

▶︎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文にて結合テーブルに対して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);

 

うんうん、いける！

 

カラム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って便利ですよね。

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

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

この違いなんだ？

 

 

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

 

上司「主キーがあればレコード識別ができるから、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');

 

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

 

ん？

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制約が追加されました。

 

 

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

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

 

 

つまり、

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

と

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

は同じ！

 

試しに、後者を実行。

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

 

 

 

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

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