はじめに
以下はスッキリわかるSQL入門第2章から第4章までの学習備忘録です。
引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。SQLのタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。
また私が曖昧な理解や、知識として定着していない個所を記述していますので、基本的な文法や構文に関しては記述されていない可能性があることをご了承ください。
RDMSについてはMySQLを想定しています。
第2章 基本文法と4大命令
2.2 データ型とリテラル
リテラルの記述に関するルール
- シングルクォート(')でくくらずに記述されたリテラルは数値情報として扱われる。
- シングルクォート(')でくくられたリテラルは、基本的に文字情報として扱われる。
- シングルクォート(')でくくられ、'2018-02-25'のような一定の形式で表されたリテラルは日付情報として扱われる。
代表的なデータ型
| データ種別 | 区分 | 代表的なデータ型名 |
|---|---|---|
| 数値 | 整数 | INTEGER型 |
| 少数 | DECIMAL型、REAL型 | |
| 文字列 | 固定長 | CHAR型 |
| 可変長 | VARCHAR型 | |
| 日付と時刻 | ------- | DATETIME型、DATE型、TIME型 |
2.4 SELECT文ーデータの検索
SELECT * の濫用に注意
* による全列検索は便利だが、データベースの設計変更などで列が増えたり減ったりすると、検索結果が変化してしまい、データベースを検索するアプリのプログラムで『SELECT * 』を使用していた場合バグの原因になることがある。
よって、実際の開発では極力使用しない方が良い。
2.7 INSERT文ーデータの追加
INSERT INTO文のテーブル名の後ろの()の引数に列を指定する場合、その指定順序は自由。ただし3行目で列挙する値も列に対応するように同じ順番にする必要がある。
一方、INSERT INTO文の列指定を丸ごと省略した場合、VALUES句に記述する値は、テーブルにおける列の順序([SELECT *]を実行して表示される順)と同じでなければならない。
2.8 4つのSQLをスッキリ学ぶコツ
4大命令の分類方法(1) 検索系と更新系
- 検索系: SELECT
- 更新系: INSERT、UPDATE、DELETE
検索系SQLの実行結果は表が返ってくるが、一方更新系SQLの実行結果は基本的に『成功か失敗』であり表が返されることはない。
4大命令の分類方法(2) 既存系と新規系
- 既存系: SELECT、UPDATE、DELETE
- 新規系: INSERT
検索や更新そして削除は既存のデータに対して行う処理であるため、その対象行を指定するための共通した文法としてWHERE句が利用可能。
一方、既存のデータに対する処理ではないINSERT文では、WHERE句が利用できない。
スッキリかける SQL
1. まず、命令文(SELECTやINSERTなど)を記述する
2. 次に、テーブル指定の部分を記述する
3. テーブル指定より後ろの部分を記述する
4. テーブル指定より前の部分を記述する(SELECT文)のみ
第3章 操作する行の絞り込み
3.2 条件式
WHERE句に書けるもの
結果が必ず真(true)または偽(false)となる条件式
3.3 さまざまな比較演算子
主な比較演算子
| 比較演算子 | 意味 | 補足 |
|---|---|---|
| = | 左右の値が等しい | |
| < | 左辺は右辺より小さい | |
| > | 左辺は右辺より大きい | |
| <= | 左辺は右辺の値以下 | |
| >= | 左辺は右辺の値以上 | |
| <> | 左右の値が等しくない | 『!=』演算子も『<>』と同じ意味 |
特殊な比較演算子
| 論理演算子 | 意味 | 補足・注意事項 |
|---|---|---|
| IS NOT NULL | NULLでないことを判定する | |
| LIKE | 文字列があるパターンに合致しているかをチェックする |
『%』は任意の0文字以上の文字列
『_』は任意の1文字 |
| BETWEEN | ある値が範囲内に収まっているか判定する | 論理演算子『AND』でも同じ判定が可能だが、状況にもよるが『BETWEEN』の方が処理性能が悪い。 |
| IN | 値がカッコ内に列挙した複数の値(値リスト)のいずれかに合致するかを判定する | =演算子では、1つの値しか比較できないが、IN演算子を使えば一度にたくさんの値の比較が可能 |
| NOT IN | カッコ内に列挙した値のどれとも合致しないことを判定する | |
| ANY | 値リストのそれぞれと比較して、いずれかが真なら真 | 式 基本比較演算子 ANY (値1、値2、値3・・・) |
| ALL | 値リストのそれぞれと比較して、すべて真なら真 | 式 基本比較演算子 ALL (値1、値2、値3・・・) |
以降は、特殊な演算子についての補足説明を記述。
NULLとは
- そこに何も格納されていない、未定義である事を表す
- 数字のゼロや空白文字とも異なる
NULLは『=』では判定できない
NULLは『=』や『<>』では判定できない。必ずIS NULLや IS NOT NULLを使って条件式を作る必要がある。
比較演算子の『=』でNULLかどうかを判定してはいけない理由 ー 3論理値
SQLの条件式には結果として『TRUE』、『FALSE』以外に3つ目の値『UNKNOWN(不明、計算不能)』という値が存在する(3論理値)。
『=』や『<>』はそもそも値と値を比較するためのものであり、『値ではないNULL』と比較すると不明な結果である『UNKNOWN』となる。
WHERE句による絞り込みは、条件式が『TRUE』となる行だけが抽出される。
ALLやANYなどの演算子は単体で利用しても意味がない
『式』や『副問い合わせ』などの道具と組み合わせてはじめて、その真価を発揮する。
3.4 複数の条件式を組み合わせる
論理演算子
| 論理演算子 | 意味 | 補足・注意事項 |
|---|---|---|
| AND | 2つの条件式の両方が真の場合だけ、真となる(AかつB) | 条件式1 AND 条件式2 |
| OR | 2つの条件式のどちらかが真ならば、真となる(AまたはB) | 条件式1 OR 条件式2 |
| NOT | NOTを記述すると、右辺の条件式の結果は、真は偽に、偽は真に逆転する | NOT 条件式 |
論理演算子の優先順位
複数の論理演算子が使われている場合では(1)NOT、(2)AND、(3)ORの優先順位に従って処理される。
例えば『条件式1 OR 条件式2 AND 条件式3 OR 条件式4』という式で論理演算子が複数使われている場合、『条件式2 AND 条件式3』から処理される。
もし『条件式1 OR 条件式2』と『条件式3 OR 条件式4』の処理を先に行いたい場合、それぞれの条件式をカッコでくくると、評価の優先順位を上げることができる。
3.5 主キーとその必要性
主キーとなる列が持つべき特性
- 必ず何らかのデータが格納される(NOT NULL 制約)
- 他の行と値が重複しない(UNIQUE 制約)
主キーとなる役割を果たせる列
- 自然キー・・・例えば社員テーブルがあるとして、社員番号という列が自然に登場し、主キーの役割が果たせる列のこと
- 人工キー/代替キー・・・家計簿テーブルの場合、自然と主キーの役割を果たす列が存在しない。そのため、このような場合には特定の行を識別可能にするためだけに、主キーの為の列を無理やり作ることがある。このように管理目的のためだけに人為的に追加された列のことを人工キーや代替キーと呼んだりする。
複合主キー
単独では重複の可能性がある列でも、複数を組み合わせれば重複する可能性が実質的になくなる場合があり、このように複数の列を1つの主キーとして取り扱うこと。
第4章 検索結果の加工
4.1 検索結果の加工
検索結果を加工するキーワード
| キーワード | 内容 | 補足・注意事項 |
|---|---|---|
| DISTINCT | 検索結果から重複行を排除する | |
| ORDER BY | 検索結果の順序を並び替える | |
| OFFSET-FETCH | 検索結果から件数を限定して取得する | ※FETCHはMySQL、MariaDB、SQliteではサポートされない |
| UNION | 検索結果に他の検索結果を足し合わせる | |
| EXCEPT | 検索結果から他の検索結果を差し引く | MySQLでは使用不可 |
| INTERSECT | 検索結果と他の検索結果で重複する部分を取得する | MySQLでは使用不可 |
4.2 DISTINCT ー 重複行を除外する
DISTINCT データの種類を取得
/* 結果表の中で内容が重複している行があれば取り除く */
SELECT DISTINCT 列名・・・ FROM テーブル名
4.3 ORDER BY ー 結果を並び替える
ORDER BY 並び替えの基本
/* 指定した列の値を基準として、検索結果を並び替えて取得できる
並び順は、ASCまたはDESC(省略するとASCと同じ順になる)
*/
SELECT * FROM テーブル名 ORDER BY 列名 並び順
/* 複数の列を基準にした並び替え */
SELECT * FROM テーブル名 ORDER BY 列名 ASC(DESC), 列名 ASC(DESC)
/* 列番号を指定した並び替え
列番号とは、選択列リストにおける列の順番のこと。ORDER BY句における列指定に列番号を使用する場合、
SELECT文の選択列リストの記述を修正すると、並び替えの結果にも影響が及ぶことに注意。
*/
SELECT * FROM テーブル名 ORDER BY 列番号 ASC(DESC), 列番号 ASC(DESC)
照合順序
ORDER BY句に文字列を指定すると、DBMSに設定された照合順序(文字コード順、アルファベット順)を基準として並び替えられる。
-
utf8mb4_general_ci(デフォルト値)
英字の大文字小文字が区別されない。
半角と全角は区別する。 -
utf8mb4_unicode_ci
大文字小文字、全角半角、ひらがな・カタカナ、濁音・半濁音が区別されない。 -
utf8mb4_bin
すべて区別
/* データベース、テーブル、カラムに設定可能
一覧の表示
*/
show collation where charset like '%utf8%';
-- 照合順位の確認
use utf8mb4_db;
select @@collation_database
4.4 OFFSET
※ OFFSET句に関してはMySQLをベースにした内容になっていなかったので、↓の記事を参考にさせていただきました
https://www.javadrive.jp/sqlite/select/index10.html
LIMIT句を指定した場合は先頭のデータから指定した数のデータを取得するが、先頭からではなく指定した位置からデータを取得することもできる。データの取得を行う最初の位置を指定するには OFFSET句を使用する。
LIMIT 句の後に取得するデータの行数、そして OFFSET 句の後にデータを取得する開始位置を指定する。OFFSET 句を使用しない場合はまず開始位置を指定し、その後に取得する行数を指定することに注意。
注意する点として、例えば OFFSET に 4 を指定した場合、最初から 4 番目までのデータを飛ばして 5 番目のデータから取得を行う。4 番目のデータから取得を開始するわけではないことに注意。また LIMIT 句を省略して OFFSET 句だけを記述することはできない。
/* OFFSET句を使用してデータを取得する */
SELECT カラム名, ... FROM テーブル名 LIMIT 行数 OFFSET 開始位置;
/* OFFSET句を使用しないでデータを取得する */
SELECT カラム名, ... FROM テーブル名 LIMIT 開始位置, 行数;
DBMSにとって並び替えは大仕事
並び替えという処理は、DBMSにとってかなり負荷のかかる処理ということは覚えておく必要がある。そのためORDER BY句使用の際には注意。インデックスの併用を推奨。
DISTINCTやUNIONも内部的には並び替えを行っていることがあるので、濫用は注意。
また、ORDER BY句を指定しないときは、SELECTの結果はデータベース環境やDBMSの内部処理ロジックに依存される。
したがって、決まった順序での抽出が必要な場合は、必ずORDER BY句を使用する。