今回は基本情報技術者試験及び、応用情報技術者試験にて出題される可能性のあるSQLのクエリについてまとめていきたいと思います。
午前問題でも出題され、特に応用情報では午後問題で記述形式の穴埋めで出題されることもあるので、しっかりとカバーしておきたいところです。
説明は簡易的なものになりますので、何が知識として必要なのか俯瞰して確認したい人に参考になれば幸いです。
なお、一応文法を記載していますが、午後問題を選択しない限りはクエリ名だけ抑えておけば大丈夫かと思います。
午後問題をとる場合は形だけでも覚えておくと良いかと思います。
データの新規作成に関するクエリ
データの新規作成はCRUDでいえばCにあたる操作です。
CRUDについてはわからない方は以下の記事をご参照ください。
-INSERT
データの新規作成にはINSERTを使用します。
データベースにはデータがないと始まりませんので、データの操作としては最初の操作となるでしょう。
データの選択に関するSQLクエリ
それではデータ選択にまつわるクエリについてみていきます。
データを取得する処理にまつわるクエリは非常に種類が多いので混同しないように一つ一つ特徴を覚えましょう。
SELECTの構文
データの検索にはSELECTを使用します。
また、テーブルの指定にFROM、検索データの絞り込みにWHERE句が用いられます。
カラムを全取得する場合には「*(アスタリスク)」を用いることで指定できます。
WHERE句のオプション
WHERE句では複雑な条件を実現可能とするためいくつかオプションがあります。
-OR
ORはいずれかの条件に該当するデータを対象とします。
「A OR B」の指定であればデータがAかBのものが対象になります。
-AND
ANDは複数指定した条件のうちすべてに当てはまるデータを対象とします。
「A AND 1 」であればデータがAかつ1のデータであるものを対象になります。
-IN
INは複数指定した条件を一括でまとめて指定することができます。
以下の2つは同じ意味を持ちます。
-BETWEEN
BETWEENは与えられた範囲内のデータを対象とするクエリです。
≦ ≧で指定した場合と同等の動きをします。そのため、同値も含みます。（以上、以下と同じです）
-LIKE
LIKEはあいまい検索を可能とするクエリです。
前方一致、後方一致、部分一致を指定できます。
％を使って表現し、％になっている箇所は「なんでもいいよ」を意味します。（ワイルドカードといいます。）
テーブル結合
データベースは基本的に正規化された設計になっておりますので、自分が欲しいデータというのはいくつかのテーブルを結合して使用することになります。
そのため、多くの場合、問題ではテーブル結合がなされた状態で登場します。
-INNER JOIN
INNER JOINでは指定したキーをもとにお互いのテーブルに存在するデータのみ取得するクエリです。
内部結合とも呼ばれます。
-LEFT JOIN , RIGHT JOIN
LEFT JOINおよび、RIGHT JOINでは左右に2つテーブルがあると見たときにどちらか一方のテーブルを主軸にし、キーをもとに共通するデータがある行だけ片方のテーブルから貼り付けるようなイメージの結合方法です。
INNER JOINとの違いは一方にデータが存在しなくても主軸にしたテーブルであれば行を表示し、結合したテーブルの内容はNULLで表示する点です。INNER JOINの場合は片方のテーブルにデータが存在しない行の取得が行われません。
LEFT JOINであれば左側のテーブルを主軸にし、RIGHT JOINであれば右側のテーブルを主軸としますが、基本的に書き順の問題になるので違いがあまりなく、多くの場合はLEFT JOINが使われます。
LEFT JOIN , RIGHT JOINは外部結合と呼ばれ、DBの種類によっては OUTER JOIN と表記する場合があります。
集合演算子
集合演算子ではテーブル結合と似たような動きをしますが、データの取得方法が異なります。
結合と集合、どちらでもいい場面があれば、どちらか一方でしか使えない場面もあるので、その違いをついて試験で出題される場合があります。
ここら辺の違いについては以下の記事で詳しく解説いたしますので、必要な方はご参照ください。
-UNION
UNIONは2つの結果セットから得られたデータを両方合わせて表示させます。結果は所謂和集合になります。
JOINが外からデータを肉付けするようなイメージなら、UNIONはデータをミルフィーユの層のように重ね合わせて表示します。
なお、UNIONは重複行を除外しますが、UNION ALL と指定すると重複行も含めて表示します。
-EXCEPT
EXCEPTは2つの結果セットから前者に指定した結果セットに存在するデータのみ表示させます。所謂差集合です。
「Except」は除外するという意味を持つので、EXCEPTより後ろに指定したデータ群を前のテーブルより除外するという風に理解すると覚えやすいかと思います。
-INTERSECT
INTERSECTは2つの結果セットから両方の結果セットに存在するデータのみを抽出するSQLクエリです。積集合を意味します。INNER JOINと似た動きになります。
副問い合わせ
副問い合わせは一つのSQLクエリの内部にそのクエリとは別に発行するクエリのことです。
マトリョーシカのように問い合わせが入れ子になっている状態というのがイメージしやすいかなと思います。
-EXISTS
EXISTSは副問い合わせにて得られたデータの中に存在するデータのみ取得するクエリです。
字面がEXCEPTと似ているので混同しがちですがEXCEPTは除外で、EXISTSは存在するのため真逆の動きになります。
ただし、NOT EXISTS とすれば存在しないデータを取得できるようになるので、EXCEPTと似たような動きになります。
その他
-AS
ASはクエリ内でカラム名やテーブル名を別名で利用するためのものです。
-DISTINCT
DISTINCTは重複したデータを除外して検索します。
例えば、COLUMN1 のカラムに データ1という値が入ったレコードが2行あった場合、
検索結果は2行でなく1行になります。
データの更新に関するクエリ
次にデータの更新に関連するクエリについてです。
とはいってもデータの更新に関するクエリはUPDATEのみです。
-UPDATE
UPDATEはデータを更新するためのクエリです。
UPDATEのクエリ自体は単純なものの、データの更新の対象となる結果セットが複雑になる場合が多いです。
データの削除に関するクエリ
次にデータの削除に関するクエリについてです。
削除についてもクエリの種類は2種類しかありませんのでそれぞれしっかりと覚えておきましょう。
-DELETE
DELETEはデータを削除するための基本となるクエリです。
全件削除したり、一部のデータのみ対象としたりできます。
-TRUNCATE
TRUNCATEはDELETE文以外のデータを削除するためのもう一つの方法です。
TRUNCATEはデータを全件削除することができ、DELETE文より処理が高速という特徴があります。
全件削除するという処理しか行わない場合はこちらのクエリの方が有効です。
その他のクエリ
上記はテーブルに対して行う主な操作になりますが、これ以外にも重要な操作がありますので、覚えておきましょう。
データベースのスキーマの操作に関するクエリ
データベースにテーブルやユーザーを追加したり、逆に削除したり、テーブルの定義を変えたり、データベースのスキーマを操作するクエリについてです。
-CREATE
CREATEはデータベース自体を作成したり、テーブル、ユーザーなどの作成に使われるクエリです。
-DROP
DROPはCREATEとは逆にCREATE文で作成したものを削除するためのクエリです。
-ALTER
ALTERはテーブルにカラムを新しく追加したり、削除したり、カラム自体の内容を変更したり（例えばSTRING型からDECIMAL型へ変更）テーブルの定義を変えるためのクエリです。
-GRANT
GRANTはユーザーにアクセス権限を与えるためのクエリです。
たまに、午前問でユーザーにアクセス権限を与えるためにどのクエリを使用したらよいか？みたいな形で問われることがあります。
数値に関するクエリ
数値データを操作するためのクエリがいくつかありますので、重要なものを覚えておきましょう。
なお、＋,－などの四則演算は説明するまでもないと思いますので、ここでは割愛します。
-MOD
MODは数値の余剰を求める関数です。
[%]でも表現される場合があり、使用するデータベースの種類によってまちまちなので、使用できる方を選択しましょう。
-ROUND
ROUNDは数値を四捨五入するための関数です。
引数を用いることで、四捨五入の方法も変えることができます。
-CEILING
CEILINGは数値の切り上げを行うためのクエリです。
1.1であれば2になります。
-FLOOR
FLOORはCEILINGとは逆に数値の切り捨てを行うクエリです。
1.1であれば1になります。
文字列に関するクエリ
文字列データの操作に関するクエリです。
こちらも簡単に名称とそれに伴う処理だけでも覚えておきましょう。
-CONCAT
CONCATは文字列同士のデータを連結するためのクエリです。
単純に[+]で表現できる場合もあります。
-SUBSTRING
SUBSTRINGは文字列の一部を指定して切り出すためのクエリです。
指定には開始位置とそこからの長さを指定します。
集計に関するクエリ
集計に関するクエリはかなりの頻度で出てきますので、他のものよりは優先して覚えておきたいです。
数値だけでなく、日付や文字列とデータ型に縛られずに使用されます。
-GROUP BY
GROUP BYは指定した列を基にデータをグループ化するためのクエリです。
グループ化することでそのデータ群の行数や、合計値、最大値などが得られます。
また、GROUP BYに指定された列でデータがまとめられるので、指定した列から見た時に重複した行がある場合は削除され結果セットとして得られます。
-COUNT
COUNTはテーブルデータの行数を数えるためのクエリです。
GROUP BYと併用することで、意図したデータグループの行数を数えることができます。
また *(アスタリスク)を指定することですべての件数を数えることができます。
-MAX
MAXはデータ群の最大値を取得するためのクエリです。
GROUP BY でグループ化した際、SELECTに指定できるのはGROUP BYに指定した列のみですが、MAXを用いることで、GROUP BY になくても SELCTに指定できるようになるので、GROUP BY に関与しない列のデータがとりあえず欲しいという場合に用いられます。
-MIN
MINはMAXとは逆にデータ群の最小値を取得するためのクエリです。
GROUP BYを使用するときにもMAXと同じような使用方法ができますが、基本はMAXが使われます。
-HAVING
HAVINGは特定のデータ群に対して条件を指定して、その条件に合致するデータを抽出するための処理です。
WHEREとほとんど同じような動きをしますが、ポイントは記載する場所がGROUP BY の後ろになるです。
GROUP BY の後に判定が走るので、グループ化したデータに対し条件指定をすることができ、COUNTやMAXなどで集計した値を対象に条件を追加することができます。これはWHEREではサブクエリを使わないとできない動きです。
HAVINGについてもっと詳しく知りたいという方は以下の記事でWHERE句とHAVING句の違いについて解説しておりますのでご参照ください。
-SUM
SUMはデータ群の合計値を求めるクエリです。
MAXなどと同様にGROUP BY の指定がある場合でも SUMで取得すれば GROUP BY 句に含める必要はありません。
-AVG
AVGはデータ群の平均値を求めるクエリです。
こちらもSUMと同様にGROUP BY句に指定が無くてもAVGで取得できます。
その他
-CURSOR
CURSORとはクエリによって結果セットを1件ずつ処理するためのクエリです。
結果のどの位置に焦点を当ててるか、つまりどこにカーソルがあるかを保持して、プログラムなどでいろいろな処理を実施します。
文言が簡単に出てくるので、導出表を1行ずつプログラムで処理するための仕組みとだけ覚えておきましょう。
因みにDECLAREとはSQLで使われる変数を定義するための文言です。
VBのDimやjavascriptのvarなんかと同じようなものです。
-COMMIT
COMMITはトランザクションの内容を確定させるためのクエリです。
データの挿入や更新などは一旦仮の処理として扱うことができ、様々な変更内容をトランザクションとして保持します。
その保持した更新を確定させるのがCOMMITというわけです。
ファイルの更新なんかも保存ボタンを押すまでは更新されず、保存ボタンやCtrl + Sなどで保存することでファイルの更新が反映されますよね。この保存ボタンがデータベースに置き換えるとCOMMITというクエリになります。
-ROLLBACK
ROLLBACKはトランザクションの内容を破棄し、データベースをトランザクション前の状態に戻すためのクエリです。
COMMITは確定する、所謂保存ボタンのような動きでしたが、ROLLBACKは保存せずに終了する処理と同じイメージをもっていただければ良いと思います。
いかがでしたでしょうか。
上記の内容で100%カバーできているというわけではありませんが、これだけ覚えていれば十分に対策できると思います。
今回はここまでです。
お疲れ様でした。
コメント