現場でよく使う実用的な SQL｜研修コースに参加してみた
calendar_month2022-08-31 公開
今回参加したコースは 現場でよく使う実用的な SQL です。
SQL の基本がわかっているのに、現場の SQL を見ると「これは何だろう？」と思うことや、「あれ、こうやりたいとき JOIN はどっちだっけ」と調べてしまう経験はありませんでしょうか？
このコースでは、そんな現場でよく見る SQL や間違ってしまいがちなところを中心に解説いただきました。
では、どのような内容だったのか、レポートします！
もくじ
コース情報
| 想定している受講者 | SQL でリレーショナルデータベース操作ができる |
|---|---|
| 受講目標 | 現場でみる SQL の分かりづらい構文が理解できる |
講師紹介
この “参加してみた” レポートでは初めての登場となる 市来 秀男さんが登壇されました。
講師紹介
市来 秀男
株式会社 khronos
講師経験 25 年以上を誇るベテラン講師。 新人研修の講師だけでなくテキストも開発、 Java を始め、 SWift などモバイルアプリ開発、クラウドインフラ技術 OpenStack まで幅広く研修できることが強み。
主な取得資格は、 OCP Java Silver SE 7 、 LPIC 101 ～ 301 、EXIN Foundation Certificate in OpenStack Software 、 OPCEL 認定プロフェッショナル（ LPI-Japan ）など。
現場でよく使われる SELECT 文
RDBMS や SELECT 文のおさらいをした上で、早速、現場でよく見る SQL を紹介いただきます。
as （別名）は省略できる
- 列を別名とする as は省略できる
- SQL は
,で列情報を解析している
mysql> select order_id OID from orders; +-----+ | OID | +-----+ | 2 | | 4 | | 3 | | 5 | | 1 | | 6 | +-----+ 6 rows in set (0.00 sec) - SQL は
確かに order_id と OID の 2 つの列を表示するようにも見えてしまいますが、 as を略して s や t など一文字に省略する表現はよく見ますね。
計算結果をうまく表示する
- SQL で列の値を計算できる
- 以下は price を元に 1.10 倍を計算
- 消費税込みの価格 tax_inluded を計算
mysql> select product_name, price * 1.10 tax_included from products; +--------------+--------------+ | product_name | price * 1.10 | +--------------+--------------+ | 鉛筆 | 33.00 | | 消しゴム | 55.00 | | シャープペン | 275.00 | | ボールペン | 165.00 | | 色鉛筆 | 770.00 | | ノート | 110.00 | +--------------+--------------+ 6 rows in set (0.05 sec)
ここでも as を省略していますが、わかりづらい場合には、明示的に as を入れるといいでしょう、とアドバイスをいただきました。 みんな全部 as 入れりゃいいんじゃないかな … （ボソボソ）。
NULL は空文字ではない
- NULL は SQL で混乱しやすいものの一つ
- NULL navigate_next 何もデータが定義されていない状態
- 空白（空文字） navigate_next 長さ 0 の文字が定義されている
- NULL 煩雑な処理が必要になりがち
- NULL をなるべく入れないようにしよう
では、実際、 NULL の挙動を見てみましょう。
- NULL は比較できない
mysql> select * from emp where dep = null; Empty set (0.00 sec) - NULL かどうかを調べるには「is null」を使う
mysql> select * from emp where dep is null; +----------+----------+------+--------+ | empno | empname | dep | post | +----------+----------+------+--------+ | 10000005 | 鈴木太郎 | NULL | 見習い | +----------+----------+------+--------+ 1 row in set (0.00 sec)
うかつに全件ではなく LIMIT などで特定の行数を出す
現場のデータベースでは数万～数百万レコードのデータが入っていることが多いので、うかつに select * from hoge をやってしまうとパフォーマンスが落ちて、トラブルになってしまうとのことでした。 講師の市来さんも経験があるとのこと。
- 絞り込みしよう
- limit を使う（ Oracle では limit を使えないので注意）
mysql> select * from users limit 100, 4; +---------+----------------------+-----+------+ | user_id | user_name | age | dept | +---------+----------------------+-----+------+ | 50101 | FTk8Lt5DNHJRRTjgEAqL | 34 | 76 | | 50102 | JsacIBC901DB8T3Kp1Ut | 77 | 45 | | 50103 | VhY7qcOcJ6nebeZ8DNzU | 66 | 23 | | 50104 | XdZEZLo1Aak05DYvOEux | 42 | 14 | +---------+----------------------+-----+------+ 4 rows in set (0.00 sec)limit 100, 4で 100 行を飛ばして 4 行出力- ただし出力していないだけで、走査はしている → 行数が多いとリソースを食う
mysql> select * from users limit 99900, 4; +---------+----------------------+-----+------+ | user_id | user_name | age | dept | +---------+----------------------+-----+------+ | 149901 | immI622f7jIummfGPhB9 | 50 | 40 | | 149902 | Xouu2DTVVsGQsQv8mpgw | 40 | 90 | | 149903 | UU3mVt8xT9K8tDDO15yO | 10 | 15 | | 149904 | LOQn9u23kzeSjcgOELJm | 29 | 74 | +---------+----------------------+-----+------+ 4 rows in set (0.05 sec)- さっきと所要時間が違う
betweenも場合によって使おうselect * from users where user_id between 149901 and 149904;
複数条件の ORDER BY を整理
並び替えと言えば、 ORDER BY ですが、実際の業務のデータは量が多いので、複数の列で並び替えることも多くなります。
しっかり整理しておきましょう。
- order by 列名1, 列名2, ･･･
- 列名1 のあとに 列名2 でソートしている
mysql> select * from order_detail order by product_id, quantity; +----------+-----+------------+----------+ | order_id | seq | product_id | quantity | +----------+-----+------------+----------+ | 1 | 1 | 10 | 10 | | 5 | 1 | 10 | 17 | | 5 | 2 | 20 | 5 | | 4 | 1 | 20 | 9 | | 2 | 1 | 20 | 15 | | 1 | 2 | 30 | 7 | | 3 | 1 | 30 | 8 | | 2 | 2 | 40 | 3 | | 4 | 2 | 40 | 21 | | 3 | 2 | 40 | 30 | | 3 | 3 | 50 | 12 | | 1 | 3 | 60 | 25 | | 3 | 4 | 60 | 50 | +----------+-----+------------+----------+ 13 rows in set (0.00 sec)- ただし 2 回並び替えするぶん、負担がかかる
- アプリケーション側で行うこともある
OR を再確認
条件の OR 指定は、ベテランでも間違えることがあるので、注意が必要です。
- ex. 「 empno が 10000003 か 10000005 」
mysql> select * from itemorder where empno = 10000003 or 10000005; +---------+----------+------------+ | orderno | emeno | orderdste | +---------+----------+------------+ | 1 | 10000004 | 2008-03-02 | | 2 | 10000005 | 2008-03-11 | | 3 | 10000003 | 2008-03-14 | | 4 | 10000004 | 2008-03-17 | | 5 | 10000005 | 2008-03-17 | +---------+----------+------------+ 5 rows in set (0.00 sec)- これは where なしと同じ結果
- or の前でデータベースは解析を止めるので、再度条件を書かないといけない
mysql> select * from itemorder where empno = 10000003 or empno = 10000005; +---------+----------+------------+ | orderno | emeno | orderdste | +---------+----------+------------+ | 2 | 10000005 | 2008-03-11 | | 3 | 10000003 | 2008-03-14 | | 5 | 10000005 | 2008-03-17 | +---------+----------+------------+ 3 rows in set (0.00 sec)
これはやってしまいがちです。
GROUP BY は RDBMS によって変わる
グループ化を行う GROUP BY の注意点は以下でした。
select * from order_detail group by seq;mysql> select seq, count(*), sum(quantity), avg(quantity) from order_detail group by seq;
+-----+----------+---------------+-------------- +
| seq | count(*) | sum(quantity) | awg(quantity) |
+-----+----------+---------------+-------------- +
| 1 | 5 | 59 | 11.8000 |
| 2 | 5 | 66 | 13.2000 |
| 3 | 2 | 37 | 18.5000 |
| 4 | 1 | 50 | 50.0000 |
+-----+----------+---------------+-------------- +
4 rows in set (0.00 sec)JOIN を再確認
現場でよく使う JOIN ですが、つなげるテーブルをどれにするか、 NULL の扱いなど現場で迷いがちなので整理しましょう。
- 内部結合
- 結合条件を満たす行のみを出力する
mysql> select e.empname, s.money, e.post from salary s inner join emp e using(empno); +----------+--------+------+ | empname | money | post | +----------+--------+------+ | 情報一郎 | 350000 | 部長 | | 情報次郎 | 300000 | 課長 | | 情報三郎 | 250000 | 主任 | | 山田花子 | 200000 | 一般 | +----------+--------+------+ 4 rows in set (0.00 sec) -
- empno がないレコードは表示されない
using(empno)をwhere s.empno = e.empnoでも OK
- 外部結合
- どちらかのテーブル上で結合条件を満たせば出力する
-
- 主となるテーブルを指定する必要がある
left outer joinで左のテーブルを優先するright outer joinで右のテーブルを優先する
mysql> select e.empname, s.money, e.post from emp e left outer join salary s using(empno); +----------+--------+--------+ | empname | money | post | +----------+--------+--------+ | 情報一郎 | 350000 | 部長 | | 情報次郎 | 300000 | 課長 | | 情報三郎 | 250000 | 主任 | | 山田花子 | 200000 | 一般 | | 鈴木太郎 | NULL | 見習い | | 鈴木次郎 | NULL | 見習い | +----------+--------+--------+ 6 rows in set (0.00 sec) mysql> select e.empname, s.money, e.post from emp e right outer join salary s using(empno); +----------+--------+------+ | empname | money | post | +----------+--------+------+ | 情報一郎 | 350000 | 部長 | | 情報次郎 | 300000 | 課長 | | 情報三郎 | 250000 | 主任 | | 山田花子 | 200000 | 一般 | +----------+--------+------+ 4 rows in set (0.00 sec) - 主となるテーブルを指定する必要がある
この JOIN で現場でよく使う SELECT 文は終了し、このあと INSERT や UPDATE についても少し紹介いただきました。
トランザクションの分離レベルに注意！
最後はトランザクションです。
現場で混同しやすいのが 分離レベル です。 あるトランザクションでレコードを使用しているときに、ほかのトランザクションからの使用をどこまで許すか、というお話です。
データの矛盾レベル
トランザクションでデータの矛盾をどこまで許容するか、レベルが決められています。
- Dirty Read
- 中間的な状態がほかのトランザクションからも見える
- 非常に悪い状態
- ほとんどの RDBMS では許容されていない
- Non-repeatable Read
- トランザクションが完了し更新された値を、他のトランザクションが使うのを許すかどうか
- Phantom Read
- トランザクション中に、ほかのトランザクションにより存在しないレコードが発生するのを許すかどうか
分離レベル
これらの矛盾をどこまで許すのか、それを分離レベルとして状態が規定されています。 RDMBS によって選択できるレベルがあり、設定が変更できます。
- READ UNCOMMITTED
- Dirty Read を許す状態。 許容されていない
- READ COMMITTED
- Non Repeatable Read を許す
- REPEATABLE READ
- Non Repeatable Read を防ぐ
- Phantom Read は許す
- Phantom Read を防ぐよう拡張実装されている RDBMS も多い
- SERIALIZABLE
- Phantom Read を防ぐ
- 絶対に矛盾が発生しないが、遅い
- 現在の業務には向かないと言われている
- MySQL の場合
- どちらかを選択
- READ COMMITTED: デフォルト
- REPEATABLE READ
- どちらかを選択
演習で確認しよう
REPEATABLE READ を確認
mysql> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE READ |
+-----------------------+
1 row in set, 1 warning (0.00 sec)- コンソールを 2 つ立ち上げ A と B でトランザクションを実行
- A で update を実行
mysql> update products set price = 200 where product_id = 60; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from products; +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 10 | 鉛筆 | 30 | | 20 | 消しゴム | 50 | | 30 | シャープペン | 250 | | 40 | ボールペン | 150 | | 50 | 色鉛筆 | 700 | | 60 | ノート | 200 | +------------+--------------+-------+ 6 rows in set (0.00 sec) - B で確認
mysql> select * from products; +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 10 | 鉛筆 | 30 | | 20 | 消しゴム | 50 | | 30 | シャープペン | 250 | | 40 | ボールペン | 150 | | 50 | 色鉛筆 | 700 | | 60 | ノート | 100 | +------------+--------------+-------+ 6 rows in set (0.00 sec) - A で commit
mysql> commit; Query OK, 0 rows affected (0.01 sec) - B で確認
mysql> select * from products; +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 10 | 鉛筆 | 30 | | 20 | 消しゴム | 50 | | 30 | シャープペン | 250 | | 40 | ボールペン | 150 | | 50 | 色鉛筆 | 700 | | 60 | ノート | 100 | +------------+--------------+-------+ 6 rows in set (0.00 sec)- 変更が反映されてない → REPEATABLE-READ だから
- B で commit して確認
mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> select * from products; +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 10 | 鉛筆 | 30 | | 20 | 消しゴム | 50 | | 30 | シャープペン | 250 | | 40 | ボールペン | 150 | | 50 | 色鉛筆 | 700 | | 60 | ノート | 200 | +------------+--------------+-------+ 6 rows in set (0.00 sec)- トランザクションが終わったので更新された
REPEATABLE READ で Dirty Read ではないことを確認
続いて、 Phantom Read を確認してみましょう
- A で insert を実行して commit
mysql> insert into products values(100, 'コンパス', 1000); Query OK, 1 row affected (0.00 sec) mysql> commit; Query OK, 0 rows affected (0.00 sec) - B で確認
mysql> select * from products; +------------+--------------+-------+ | product_id | product_name | price | +------------+--------------+-------+ | 10 | 鉛筆 | 30 | | 20 | 消しゴム | 50 | | 30 | シャープペン | 250 | | 40 | ボールペン | 150 | | 50 | 色鉛筆 | 700 | | 60 | ノート | 200 | +------------+--------------+-------+ 6 rows in set (0.00 sec)- 反映されていない
- MySQL の REPEATABLE READ では Phantom Read は防ぐ仕様
REPEATABLE READ で Phantom Read を確認
デッドロックをやってみよう
RDBMS によってデッドロックを検知したときの挙動が違うので、必ず確認が必要です。
MySQL はロールバックするので、それを確認してみましょう。
- A で update 1 を実行
mysql> update products set price = 100 where product_id = 10; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 - B で update 2 を実行
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update products set price = 300 where product_id = 60; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 - A で update 3 を実行（ update 2 と同じレコードを更新）
mysql> update products set price = 150 where product_id = 60;- 処理が終わらず待っている状態
- B で update 4 を実行（ update 1 と同じレコードを更新）
mysql> update products set price = 150 where product_id = 10; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction- デッドロックが発生
- A で commit 後、 B を commit すると 0 rows attached （何も更新されなかった = ロールバックした）
このデッドロックを体験したところで、このコースは修了しました。
まとめ
現場でよく見る SQL や間違えやすいことを中心に、実際に手を動かしながら学びました。
OR や JOIN での NULL など、調べ直しがちなことが多く、いい復習になりました。 また分離レベルやデッドロックの挙動は聞いたことがあるぐらいだったので、手を動かして実習してよくわかりました。
新人研修などで SQL の基本文法を身につけて、これから現場配属される方や、現場配属されて SQL がどうも読みにくい or 試すのが怖いと感じている人にはオススメです！
label SEカレッジを詳しく知りたいという方はこちらから !!
SEカレッジ
IT専門の定額制研修 月額 28,000 円 ～/ 1社 で IT研修 制度を導入できます。
年間 670 講座をほぼ毎日開催中!!
IT専門の定額制研修 月額 28,000 円 ～/ 1社 で IT研修 制度を導入できます。
年間 670 講座をほぼ毎日開催中!!
label 関連するタグ
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。