関係データベースのデータ操作

要点と定義

関係データベースでは、表の行を条件で絞る操作を「選択」、必要な列を取り出す操作を「射影」といいます。たとえば商品表から全行の商品番号と数量だけを取り出すのは射影です。行の条件を変えずに列だけを減らす点が判断の手掛かりです。

複数の表から関連する情報を得るには、対応する列を結合条件にして表を結びます。児童の参加実績と保護者情報を得る場合、児童表と活動表は児童番号で、児童表と保護者表は保護者番号で結びます。さらに学年やイベント番号で対象を絞り、保護者番号と氏名を重複なく出すなら、出力に DISTINCT を指定できます。結合は表間の対応をたどるために必要で、絞込み条件は目的に合う行を選ぶために必要です。

結合する列が同じ名前とは限らないため、どの表のどの列を比較するかを確認します。学生の所属と学部名を結び、学部表の住所で絞る例では、学生表の住所ではなく学部表の住所を条件に使います。

集約関数は、対象となる複数行から値を計算します。COUNT(*) は行数を数え、AVG は平均、MAX と MIN は最大値と最小値、SUM は合計を求めます。参加記録の一行が一人の参加を表すなら、イベントごとの参加者数はそのイベントに結び付く参加記録の行数です。イベント番号の平均・最大・合計は、参加者数とは別の量です。

GROUP BY は、指定した列の値の組ごとに行をまとめ、集約する単位を決めます。クラスと教科ごとの平均点なら、クラス名と教科名でグループ化して AVG(点数) を求めます。学生番号まで加えると学生ごとに分かれ、クラス・教科ごとの平均とは異なる集約になります。ORDER BY は結果の表示順を決める指定です。クラス名、教科名の順に昇順で並べるなら、両方をその順で指定します。集約単位と並び順は別々に考えます。

文字列の条件に LIKE を使うと、% は任意の長さの文字列、_ は任意の一文字に対応します。4文字の年度文字列が「201」で始まる条件は LIKE '201_' のように表せます。= は文字列全体の一致を調べるので、= '201%' では % はワイルドカードとして働きません。

ビューは、問合せ結果を表のように扱える仕組みです。ビュー定義の CASE 式は、元の値に応じて表示する値を変換できます。たとえば性別コードが0なら「男」、1なら「女」、それ以外なら「－」をビューの列として返す定義では、ビュー上で男子を選ぶ条件は数値の0ではなく文字列の「男」です。問合せ条件は元表の値だけでなく、ビューが返す列の値と型にも合わせます。

外部結合は、一方の表の行をすべて結果に残し、対応する行がない側の列を NULL にします。COUNT(*) は結果の行そのものを数える一方、COUNT(列) はその列が NULL でない値を数えます。階級表の全階級を残す結合では、該当する生徒がいない階級にも行ができます。その行の身長は NULL になるため、COUNT(身長) なら該当者のいない階級を人数に含めません。

副問合せは、SQL文の中に置く別の問合せです。児童表に保護者番号が残っていない保護者を選ぶ条件は、保護者番号を児童表の番号集合と照合する NOT IN で表せます。退会する児童を先に削除してから副問合せを実行すれば、削除後に在籍児童がいない保護者を判断できます。どの時点の表を参照する条件なのかが重要です。

適用条件と例

問合せを組み立てるときは、まず出力列と行の条件を整理し、次に必要な表と表間の対応列を確認します。たとえばイベント参加者の保護者を抽出するなら、参加実績を記録する表を含めて児童表と結合し、保護者表も対応する番号で結びます。参加実績の表がなければ、イベントに参加したかを判定できません。同じ保護者に複数の対象児童がいる場合は、保護者を一度だけ出すために DISTINCT を使えます。