ぺチコン2021レポート〜1日目〜

 

今年もぺチコンに参加させてもらいました!(オンライン)
オンラインなので👶がいる私には参加しやすかったです〜
YouTubeでアーカイブも残ってるのもありがたいです!何度も見直しますね(きっと)

今回初めてDiscordも活用させていただきました。リアルタイムでいろんな達人の方々が補足してくれるのでめちゃくちゃ勉強になりました!!!
また、会社のスポンサーツアーにもちょこっと出させてもらいました〜🙌

\ ぺチコン2021のタイムテーブルはこちら /
https://fortee.jp/phpcon-2021/timetable

コロナと落ち着いたら、またオフラインで参加したいです🥺
\ 過去のぺチコンレポートをどうぞ /
https://kin29.info/tag/phpcon/

気になったトークのメモ

🟡PHPにおけるコーディング規約と自動整形

コーディング規約の重要性を再確認させてもらいました。

php-cs-fixerとphp_codesnifferの違いについての理解は曖昧だったので、Discord上での解説もあり超納得でした!

php-cs-fixer → 書式しか見ないが全てを自動修正できる

php_codesniffer → 構造も見るのでeraly exitとかも違反にできるが、全ては自動修正できないより厳しくできる

🟡独自フレームワークPHPアプリケーションの改善戦略

auto_prepend_fileっていう設定ができるの初知りでした!auto_prepend_fileについて別記事書いたので良かったらみてください。

PHPのauto-prepend-fileを設定して、symfony/var-dumperを使ってみる

🟡PHPer が知るべき MySQL クエリチューニング

SQL苦手系PHPerなのでめちゃくちゃ勉強になりました。EXPLAINの見方はよく知らなかったので、わかりやすい説明で大変理解できました!

会社の技術ブログで実際に手を動かしてEXPLAIN使って見たレポを書いたので良かったら見てください。
https://tech.quartetcom.co.jp/2021/10/07/phpcon-2021-report/

🟡PHPで学ぶオブジェクト指向プログラミング入門

いい声なので動画貼っておきます。https://youtu.be/gdCE-UGqeSM?t=18952
変数名、関数名だけでも抽象化は表現できる!
複雑さ=条件分岐 → わかりやすくする=条件分岐を減らす
異なるものを同じように扱う事=ポリモーフィズム
JankenGameのコードはdisplayLangResolverを追加したい!って思ってしまいました。
Smalltalkっていう言語のOOP流派もある(←普段使わない方)
ポリモーフィズムは3つある。サブタイプポリモーフィズム/パラメトリックポリモーフィズム/アドホックポリモーフィズム
いつも通り、成瀬さんの説明はめちゃくちゃわかりやすいです。DDDの本読みました!
「OOPって何?なんでいいの?」って聞かれたらおすすめしたいトークです。

🟡ドメインをモデリングして PHP コードに落とし込む

新原さんも声もいい☺️https://youtu.be/gdCE-UGqeSM?t=23609
例題のドメインがワクチン接種なのが現代的!でコードも見れてわかりやすかった!

モデリング→複数の視点(俯瞰でみるかその中の要素でみるか)、手法で徐々に形にしていく
– ユースケース図 …システム化の範囲、ユーザが見えてくる
– 用語集の作成(ちょっと面倒) …項目は用語/英語表記/内容。英語表記があることでコード上での表記ゆれを防ぐ。解釈のずれがなくなるのでおすすめとのこと。
– 概念モデル図 …クラス図。多重度がわかる。用語を適当にばーっと並べて線で結ぶことからはじめて、徐々にブラッシュアップしていく感じ。
UML図ちゃんとは書いたことない。。。💦

ドメインモデル実装
– 1ドメイン = 1クラス
– POPO(Plain Old PHP Odject)、プレーンなPHPで作成
– クラス名やメソッド名にドメインモデル用語を使う
– setterを作らない方が良い …ドメインロジックによってプロパティ値を変更する

日本語クラスは作ったことないのでやってみたい!PHPStormはバリバリ対応してるらしい。

モデリングしてコードを実装することで理解が深まる、コードを書いてて気づくことが多いのはめちゃわかる!
→モデリングの一環としてコードを書くことと、結構良い!テストで実行検証できるのも良い🙆‍♀️

ステータスの状態遷移図もあると便利。
Enumは型としても使える、日本語も使える!
まずは、用語集→ユースケース図が良さそう、用語集でドメイン知識の認識を合わせることが重要💡
図やドキュメントのメンテは? → Wikiレベルで良い、とりあえずあれば嬉しい。

何度も見返したいトークでした!!!🙇‍♂️

🟡【IMO】コードレビューって難しいよね

IMO=in my option 私の意見では
わたしもレビューされる方が好きです。
レビューしてもらうとその分学びが増える感じが好きです。
pullpanda使ったことないので、使って見たいと思いました!
https://pullpanda.com/

さいごに

二日目レポートも書きます!(きっと)

Herokuのアドオン「Heroku Postgres」を使ってみる



どうも!
大好きな宮崎を旅立ちまして、名古屋市民になりました。
4日間荷物が来なかったので、
床に毛布一枚でねるという生活で腰が痛かったです。
荷物がきた時は嬉しくて、
4時間以内にすべてダンボールを開封し部屋完成させました。

Heroku好きなんですが、DB使えるの知らなかったです。
!! アドオンでPostgreSQLを使えるのです !!(無料枠あり)

使い方

1.Heroku Postgresの導入・データベースの作成

– コマンドより

$ cd [APP_NAME]
$ heroku addons:create heroku-postgresql:hobby-dev
Creating heroku-postgresql:hobby-dev on ⬢ [APP_NAME]... ⣾
Creating heroku-postgresql:hobby-dev on ⬢ [APP_NAME]... free
Database has been created and is available
 ! This database is empty. If upgrading, you can transfer
 ! data from another database with pg:copy
Created [DATABASE_NAME] as HEROKU_POSTGRESQL_GRAY_URL
Use heroku addons:docs heroku-postgresql to view documentation

データベースが作成されます。
[DATABASE_NAME]の部分に具体的なデータベース名が入ります。

$ heroku pg:info
(node:6031) [DEP0066] ...
=== DATABASE_URL
Plan:                  Hobby-dev
Status:                Available
Connections:           0/20
PG Version:            11.3
Created:               2019-06-19 01:54 UTC
Data Size:             8.1 MB
Tables:                3
Rows:                  1/10000 (In compliance)
Fork/Follow:           Unsupported
Rollback:              Unsupported
Continuous Protection: Off
Add-on:                [DATABASE_NAME]

 

– Herokuダッシュボードより
Herokuダッシュボード > Heroku Postgresを導入したいAPPを選択後、
Resorce  > Add-ons に遷移する。
「Heroku Postgres」を検索し、プランを選んでをProvisionする
今回はフリープランの、「Hobby Dev – Free」を選びました。

データベースが作成されます。
[DATABASE_NAME]の部分に具体的なデータベース名が入ります。

 

2.データベース操作

– コマンドより
※ローカルPCより、HerokuPostgresに接続して直接SQL操作をしたい場合、
ローカルPCにpsqlコマンドが導入されていないとできませんので以下を参考に導入してください。
参考:https://qiita.com/ucan-lab/items/fb74af3d78e71407db7b

$ heroku pg:psql [DATABASE_NAME]  //データベースに接続
(node:6110) [DEP0066] DeprecationWarning: OutgoingMessage.prototype._headers is deprecated
--> Connecting to [DATABASE_NAME]
psql (11.3)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.



-- テーブル作成
[APP_NAME]::DATABASE_URL=> CREATE TABLE member_tb (id serial PRIMARY KEY,user_id TEXT NOT NULL,display_name TEXT NOT NULL);
CREATE TABLE

[APP_NAME]::DATABASE_URL=> SELECT * FROM member_tb;
 id | user_id | display_name
----+---------+--------------
(0 rows)



-- レコード挿入
[APP_NAME]::DATABASE_URL=> INSERT INTO member_tb(user_id, display_name) VALUES ('abc123', 'taro');
INSERT 0 1

[APP_NAME]::DATABASE_URL=> SELECT * FROM member_tb;
 id | user_id | display_name
----+---------+--------------
  1 | abc123  | taro
(1 row)

 

– Herokuダッシュボードより
Datestoresにてデータベースを選択後、
Dataclips > Create a new dataclip

ここでは、読み込み(SELECT)のみ実行可能。
書き込み(CREATE TABLEやINSERT)はできませんでした・・・。

 

 

3.アプリとデータベースの接続例

データベースが作成されると自動で、
Setting > Config Varsに「DATABASE_URL」が追加されます。
これをgetenvで使用します!
※データベースが複数存在する場合、「HEROKU_POSTGRESQL_GRAY_URL」「HEROKU_POSTGRESQL_MAROON_URL」のように追加されます。

PHP Data Objects (PDO) 拡張モジュールを使いました。

<?php

$url = parse_url(getenv('DATABASE_URL'));
$dsn = sprintf('pgsql:host=%s;dbname=%s', $url['host'], substr($url['path'], 1));
$pdo = new PDO($dsn, $url['user'], $url['pass']);


$stmt = $pdo->prepare("SELECT * FROM member_tb WHERE id = ?");
$stmt->execute([$id]);
$result = $stmt->fetch();

 

参考:

http://neos21.hatenablog.com/entry/2018/12/06/080000
https://qiita.com/ucan-lab/items/fb74af3d78e71407db7b

まとめ

無料枠あるのすごいありがたいです^^
これでいっぱいアプリ作れますね。
わたしはHerokuPostgresを使ってLINEBOT作成中です。
近々、BOTの記事投稿できるように頑張りまーす!



達人に学ぶSQL〜CASE式〜

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

https://amzn.to/3vcFDbG

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

 

今回は、その本の最初の節である「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

 

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