SQL　－情報処理シンプルまとめ

SQLに関するブログのアイキャッチ画像 データベース
2023.06.20

　基本情報技術者試験など情報処理技術者試験を受験する方にとっては必須の，SQLについてシンプルにまとめています。DDL（データ定義言語），DML（データ操作言語）の説明からスタートし，DDLについては，表（テーブル（実表））の定義と削除，ビュー（仮想表）の定義と削除，定義域（ドメイン）の定義と削除，権限の付与と取消し（削除）を説明しています。特に，表の定義については，データ型，列制約，表制約について詳しく説明しています。DMLについては，データの検索（SELECT文），データの挿入（追加）（INSERT文），データの更新（変更）（UPDATE文），データの削除（DELETE文）を説明しています。こちらも，特に，SELECT文については，抽出条件，表の結合（内部結合，外部結合），グループ化と集合関数，並べ替え，副問合せ，相関副問合せなどについて詳しく説明しています。たくさんあってシンプルではありませんが，頑張りましょう…。少し，読むのが辛いかも…。

SQL（Structured Query Language）

　SQLは，関係データベースの管理や操作をするために使用する世界標準のデータベース言語です。

DDL（Data Definition Language；データ定義言語）

　DDLは，データベースのデータ構造を定義する言語です。

表テーブル（実表）の定義CREATE　TABLE文
テーブル（実表）の削除DROP　TABLE文
ビュービュー（仮想表）の定義CREATE　VIEW文
ビュー（仮想表）の削除DROP　VIEW文
定義域定義域（ドメイン）の定義CREATE　DOMAIN文
定義域（ドメイン）の削除DROP　DOMAIN文
権限権限の付与GRANT文
権限の取消し（削除）REVOKE文

※ 各文の詳細は後述。

これ以降，テーブル（実表）は表，ビュー（仮想表）はビューとします。

DML（Data Manipulation Language；データ操作言語）

　DMLは，データベースの表やビューに対して，各操作を行うための言語です。

データ操作データの検索SELECT文
データの挿入（追加）INSERT文
データの更新（変更）UPDATE文
データの削除DELETE文

※ 各文の詳細は後述。

表の定義

　表には，実際にデータを格納します。「CREATE TABLE文」を使用して定義します。

CREATE　TABLE　表名 (
列名1　データ型　[ 列制約 ]，
列名2　データ型　[ 列制約 ]，
…
[ 表制約 ] )

※ [　　]は省略可能

データ型

　表を定義する場合，各列にはデータ型を指定する必要があります。よく使用されるデータ型は，次のとおりです。

文字型CHAR(n)英数字などの，長さnバイトの固定長文字列
VARCHAR(n)英数字などの，最大nバイトの可変長文字列
漢字型NCHAR(n)漢字などの，長さn文字の固定長文字列
数値型INTEGER整数値
NUMERIC(m，n)全桁数がm，小数部の桁数がnの数値
※ nを省略した場合は，整数値となる
FLOAT単精度浮動小数点数
DOUBLE倍精度浮動小数点数
日付型DATE「yy-mm-dd」形式の日付
TIME「hh-mm-ss」形式の時刻

列制約

　列制約は，1つの列（属性）に対する制約です。次のようなものがあります。

一意性制約PRIMARY　KEY主キーとなる列に指定する
UNIQUE重複を許可しない列に指定する
非NULL制約NOT　NULL空値を許可しない列に指定する
参照制約REFERENCES　被参照表名　[(列名)]外部キーに指定する

※ 被参照表の主キーを参照する場合，列名は省略可

検査制約CHECK　(条件)格納される値が満たすべき条件を指定する
既定値DEFAULT　デフォルト値値が指定されない場合の既定値を指定する

※ 複数の制約を指定する場合は，カンマで区切り指定します。

表制約

　表制約は，主キーや外部キーが複数の列（属性）から構成される場合に使用します（主キーや外部キーが1つの列から構成される場合でも使用できます）。

※ 列制約は，1つの列に対する制約なので，主キーや外部キーが複数の列から構成される場合は定義できません。

一意性制約（主キー）PRIMARY　KEY　(主キーを構成する列名のリスト)
参照制約（外部キー）FOREIGN　KEY　(外部キーを構成する列名のリスト)
REFERENCES　被参照表名　[(列名のリスト)]

表定義の例

　たとえば，「選手表」を定義するSQL文は，次のようになります。

※ データ型や列制約の記述方法にも注目してください。

　また，「投手別球種別球速表」を定義するSQL文は，次のようになります。

※ 列制約や表制約の記述方法にも注目してください。

表の削除

　表を削除する場合は，「DROP TABLE文」を使用します。

DROP　TABLE　表名

　たとえば，「選手表」を削除するSQL文は，次のようになります。

DROP　TABLE　選手表

　この場合，「選手表」が存在していれば削除されますが，存在していなければエラーになってしまいます。これに対応するためには，「IF EXISTS」を使用して表の存在確認を行い，表が存在する場合にだけ削除するようにします。

DROP　TABLE　IF　EXISTS　選手表

ビューの定義

　ビューは，表から作成される仮想的な表です。仮想的な表ですので，データは持ちません。「CREATE VIEW文」を使用して定義します。

CREATE　VIEW　ビュー名 ［（列名1，列名2，…）］
AS　SELECT　…

※ ビューの列名は，AS句のSELECT文の列名と一致しなければならない（ビューの列名を省略すると，SELECT文の列名がビューの列名となる）

※ SELECT文の詳細は後述

　ビューは，仮想的な表ではありますが，内容を更新すれば表（実表）にも反映されます（次の条件を満たす場合）。

  • 1つの表から作成されている
  • SELECT句に，算術式，集合関数を含まず，DISTINCTの指定がない
  • WHERE句に副問合せがない
  • GROUP BY句やHAVING句を含まない

ビュー定義の例

ビューの定義に関する説明画像

よくわからない場合は，とりあえず先（特にSELECT文）に進んで，また，戻ってきてみてください。

ビューの削除

　ビューを削除する場合は，「DROP VIEW文」を使用します。

DROP　VIEW　ビュー名

　たとえば，「合計本塁打数表」を削除するSQL文は，次のようになります。

DROP　VIEW　合計本塁打数表

定義域（ドメイン）の定義

　定義域（ドメイン）とは，列（属性）が取り得る値の集合のことをいいます。「CREATE DOMAIN文」を使用して定義します。

CREATE　DOMAIN　ドメイン名　データ型

　たとえば，「身長型」という定義域（ドメイン）を定義して，「選手表」の「身長」のデータ型として使用するSQL文は，次のようになります。

定義域（ドメイン）の削除

　定義域（ドメイン）を削除する場合は，「DROP DOMAIN文」を使用します。

DROP　DOMAIN　ドメイン名

　たとえば，「身長型」という定義域（ドメイン）を削除するSQL文は，次のようになります。

DROP　DOMAIN　身長型

権限の付与

　権限を付与する場合は，「GRANT文」を使用します。

GRANT　権限名　ON　データベースオブジェクト名
TO　ユーザー名
［ WITH　GRANT　OPTION ］

権限の種類

　権限の種類には，次のようなものがあります。

SELECTデータベースオブジェクトに対してSELECT（検索）を許可する
INSERTデータベースオブジェクトに対してINSERT（行の挿入）を許可する
UPDATEデータベースオブジェクトに対してUPDATE（値の更新）を許可する
DELETEデータベースオブジェクトに対してDELETE（行の削除）を許可する
ALL PRIVILEGES
（または，ALL）		データベースオブジェクトに対して，付与可能な権限をすべて付与する

　たとえば，「ユーザーA」に，「選手表」に対する「SELECT」権限と「UPDATE」権限を付与するSQL文は，次のようになります。

GRANT　SELECT，UPDATE　ON　選手表　TO　ユーザーA

権限の取消し（削除）

　権限を取り消す（削除する）場合は，「REVOKE文」を使用します。

REVOKE　権限名　ON　データベースオブジェクト名
FROM　ユーザー名

　たとえば、「ユーザーA」から，「選手表」に対するすべての権限を取り消す（削除する）SQLは，次のようになります。

REVOKE　ALL　ON　選手表　FROM　ユーザーA

データの検索（SELECT文）

　SELECT文は，表やビューからデータを抽出する場合に使用します。

SELECT　[ ALL　|　DISTINCT ]　選択リスト

FROM
表名リスト
[ WHERE
抽出条件や結合条件 ]
[ GROUP　BY
列名リスト ]
[ HAVING
グループ抽出条件 ]
[ ORDER　BY
列名 [ ASC または DESC ]，… ]

※ | は「または」を意味する

※ [　　]は省略可能

※ 実行順序は，FROM句→WHERE句→GROUP BY句→HAVING句→ORDER BY句→SELECT句

FROM句データを抽出する対象となる表（または，ビュー）を指定する
指定する表（または，ビュー）が1つの場合は，その表（または，ビュー）を次のWHERE句に渡す。複数の場合は，それらの表（または，ビュー）から直積を作成し，次のWHERE句に渡す
WHERE句特定の行を抽出するための抽出条件や，表の結合条件を記述する
（FROM句から）受け取ったデータから抽出条件や結合条件を満たす行を抽出し，次の句に渡す。WHERE句が省略された場合は，すべての行を抽出し，次の句に渡す
※ 抽出条件については後述
GROUP BY句グループ化する列を指定する
受け取ったデータから，指定した列の同じ値を持つ行をグループ化し，次の句に渡す
HAVING句特定のグループを抽出するための抽出条件を記述する
受け取ったグループ化されたデータから，抽出条件を満たす行を抽出し，次の句に渡す
※ 抽出条件については後述
ORDER BY句行（または，グループ）を，昇順（または，降順）に並べ替えるためのキー列（または，グループの代表値）を指定する
受け取ったデータを，昇順（または，降順）に並べ替える
SELECT句受け取ったデータから，抽出する列，算術式，集合関数，定数を指定する。すべての列を抽出する場合は，「*」（アスタリスク）を指定する
重複行（同じ行）の抽出について，
  • 「DISTINCTを指定した場合」⇒重複行を取り除く
  • 「ALL」を指定するか省略した場合⇒重複行を取り除かない
選択リストについて，
  • 列が指定されていれば，その列を，
  • 算術式が指定されていれば，その結果を，
  • 集合関数（列）が指定されていれば，その列で集計した結果を，
  • 定数が指定されていれば，その値を新たな列の値とする

この表，ぜんぜんシンプルじゃないです…。はじめての人は，見ても分からないと思います…。一度に覚えるのは難しいので，下の例を見ながら，少しずつ（理解しながら）覚えましょう。

抽出条件

　抽出条件は，WHERE句やHAVING句に記述します。

比較述語（比較演算子，論理演算子）
比較演算子意味例
=等しい列a = 100列aの値が100に等しい
<>等しくない列a <> 100列aの値が100に等しくない
>より大きい列a > 100列aの値が100より大きい
<より小さい
（未満）		列a < 100列aの値が100より小さい（未満）
>=以上列a >= 100列aの値が100以上（より大きいか等しい）
<=以下列a <= 100列aの値が100以下（より小さい（未満）か等しい）
論理演算子意味例
OR論理和A OR BAを満たすか，または，Bを満たす
AND論理積A AND BAを満たし，かつ，Bも満たす
NOT否定NOT AAではない

　たとえば，次の「選手表」から，「打」が「右」で，「打率」が「.270」以上の行の「選手名」と「打率」を抽出するSQL文は，次のようになります。

比較述語に関する説明画像
BETWEEN述語

　BETWEEN述語は，列の値が，ある範囲に含まれているかどうかを調べる場合に使用します。

　たとえば，次の「選手表」から，「打率」が「.270」以上「.290」以下の行の「選手名」と「打率」を抽出するSQL文は，次のようになります。

LIKE述語

　LIKE述語は，列の値（文字列）が，指定したパターンに一致するかどうかを調べる場合に使用します。

パターン意味例
%任意の0文字以上の文字と一致’麻%’
’%美’
’%友%’		「麻」で始まる（前方一致）
「美」で終わる（後方一致）
「友」を含む（任意一致）
_任意の1文字と一致’麻_ _’「麻」で始まる3文字

　たとえば，次の「2023年度打撃成績（上位5傑）表」から，「選手名」が「あお」で始まる行の「選手番号」と「選手名」を抽出するSQL文は，次のようになります。

NULL述語

　NULL述語は，列の値が，NULL値（空値）であるかどうかを調べる場合に使用します。

列名　IS　NULL列の値がNULL値（空値）であるかどうかを調べる
列名　IS　NOT　NULL列の値がNULL値（空値）でないかどうかを調べる

　たとえば，次の「2023年度打撃成績（上位5傑）表」から，「チーム番号」がNULL値（空値）の行（どのチームにも所属していない選手という意味です）の「選手番号」と「選手名」を抽出するSQL文は，次のようになります。

IN述語

　IN述語は，列の値が，値リストの，いずれかに一致するかどうかを調べる場合に使用します（副問合せの結果のいずれかに一致するかどうかを調べる場合にも使用できます）。

※ 副問合せについては後述。

列名　IN　値リスト | 副問合せ列の値が値リスト（または，副問合せの結果）のいずれかに一致するかどうか
列名　NOT　IN　値リスト | 副問合せ列の値が値リスト（または，副問合せの結果）のいずれとも一致しないかどうか

※ | は「または」を意味する。

　たとえば，次の「2023年度打撃成績（上位5傑）表」から，「チーム番号」が「OSA」または「HIJ」の行の「選手番号」と「選手名」を抽出するSQL文は，次のようになります。

表の結合

　複数の表を結合する場合は，WHERE句に結合条件を記述し，FROM句に結合する表を指定します。

※ FROM句に「INNER JOIN」（内部結合）を指定する方法もあります。

　たとえば，次の「打撃成績表」と「選手表」を結合し，「年」と「選手番号」と「選手名」と「打率」を抽出するSQL文は，次のようになります。

複数の表に同じ列名が存在する場合は，区別するために「表名．列名」と記述します。

相関名（別表名）

　FROM句に指定する表名については，「表名　AS　別名」とすることで別名を付けることができます。「AS」は省略可能で，一般的には省略されます。

SELECT　年，P．選手番号，選手名，打率
FROM　打撃成績表　B，選手表　P
WHERE　B．選手番号 = P．選手番号

外部結合

　外部結合の場合，2つの表に存在するレコード同士は結合して抽出し，基準となる表にだけ存在するレコードについては，そのまま抽出します。

LEFT　OUTER　JOIN
（左外部結合）		FROM句で左側に指定する表を基準として結合する
例）A表を基準としてB表と結合する
FROM　A　LEFT　OUTER　JOIN　B
RIGHT　OUTER　JOIN
（右外部結合）		FROM句で右側に指定する表を基準として結合する
例）A表を基準としてB表と結合する
FROM　B　RIGHT　OUTER　JOIN　A
FULL　OUTER　JOIN
（完全外部結合）		FROM句で指定する両方の表を基準として結合する
例）A表とB表の両方を基準として結合する
FROM　A　FULL　OUTER　JOIN　B

　たとえば，次の「選手表」を基準として「打撃成績表」と結合し，「選手名」と「打率」を抽出するSQL文は，次のようになります。

グループ化と集合関数

　同じ値でグループ化して1つにまとめる場合は，グループ化する列をGROUP BY句に指定します。GROUP BY句を使用した場合，SELECT句には，GROUP BY句に指定した列名，集合関数，定数のみ指定できます。また，グループの中から特定のグループを抽出する場合は，HAVING句に抽出条件を記述します。

　集合関数は，抽出されたグループに対して集計します。HAVING句とSELECT句に指定することができます。

SUM（列名）指定した列でグループごとに合計を求める
AVG（列名）指定した列でグループごとに平均を求める
MAX（列名）指定した列でグループごとに最大値を求める
MIN（列名）指定した列でグループごとに最小値を求める
COUNT（＊）行の総数（データの件数）を求める
COUNT（列名）指定した列でグループごとにNULL値（空値）でない行の総数（データの件数）を求める

※ 集合関数を入れ子にすることはできません。

※ SUM関数とAVG関数に指定する列のデータ型は整数型でなければなりません。

※ NULL値（空値）は，集計の対象になりません。

※ 「列名」だけでなく，算術式を指定することもできます。

　たとえば，次の「年度別打撃成績（本塁打）表」から，「選手番号」と「選手名」でグループ化し，「本塁打の合計」が「30」以上のグループの，「選手番号」，「選手名」と「本塁打の合計」を抽出するSQL文は，次のようになります。

　GROUP BY句を使用した場合，SELECT句には，

  • GROUP BY句に指定した列
  • 集合関数
  • 定数

のみ指定できます。

並べ替え

　抽出した行やグループを並べ替える場合は，並べ替え対象の列をORDER BY句に指定します。

※ 昇順に並べ替える場合は「ASC（または省略）」，降順に並べ替える場合は「DESC」を指定します。

　たとえば，次の「年度別打撃成績（打率）表」を「打率」の降順に並べ替えて，「年」と「打率」を抽出するSQL文は，次のようになります。

SELECT句

　SELECT句には，受け取ったデータから，抽出する列，算術式，集合関数，定数を指定します。すべての列を抽出する場合は，「＊」（アスタリスク）を指定します。

※ 「DISTINCT」を指定した場合，重複行は取り除かれます（「ALL」を指定するか省略した場合は取り除かれません）。

  • 「DISTINCT」を指定した場合 ⇒ 重複行は取り除かれる
  • 「ALL」を指定するか省略した場合 ⇒ 重複行は取り除かれない
別名（エイリアス）

　SELECT句に指定する列については，「列名　AS　別名」とすることで別名を付けることができます。集合関数で集計した列についても，「集合関数（列名） AS　別名」とすることで別名を付けることができます。ASは省略可能ですが，一般的には省略しません。

※ 「グループ化と集合関数」の例を参照。

関係演算とSELECT文

選択行を抽出する。抽出条件は，WHERE句に記述する
射影列（属性）を抽出する。列（属性）は，SELECT句に指定する
結合結合する表をFROM句に「，」（カンマ）で区切って指定し，結合条件をWHERE句に記述する
内部結合の場合は，結合する表をFROM句で「INNER JOIN」を使用して指定し，結合条件をON句に記述する
外部結合の場合は，結合する表をFROM句で「［LEFT | RIGHT | FULL］ OUTER JOIN」を使用して指定し，結合条件をON句に記述する

※ 関係演算については，「関係モデル（論理設計）　－情報処理シンプルまとめ」を参照してください。

関係モデル（論理設計）　－情報処理シンプルまとめ
基本情報技術者試験など情報処理技術者試験を受験する方にとっては必須の，関係モデル（リレーショナルモデル）についてシンプルにまとめています。はじめに，データベースの論理設計について説明し，次に，関係モデルについて，キー（候補キー，主キー，外部キー），関数従属（完全関数従属，部分関数従属，推移関数従属），正規化（第1正規化，第2正規化，第3正規化），関係演算・集合演算（和（UNION），積（INTERSECT），差（EXCEPT），直積，選択，射影，結合（内部結合，外部結合），商）を説明しています。少しややこしいかもしれませんが，基本情報技術者試験や応用情報技術者試験で，実際に出題される内容ですので避けてはとおれません。根気よく読んでみましょう。
johostudy.com
2023.10.23

副問合せ

　副問合せとは，SELECT文（主問合せ）の中に記述することのできる，もう一つのSELECT文（副問合せ）のことをいいます。

副問合せが単一行を返す場合

SELECT　選択リスト　FROM　表名1
WHERE　列名1　比較演算子　（SELECT　列名2　FROM　表名2　…）

※ 列名1と列名2は同じ属性でなければならない

※ 副問合せの結果が1行の場合のみ使用可能

　たとえば，次の「チーム表」から「チーム名」が「広島」の行を抽出し，その抽出した行の「チーム番号」と「2023年度打撃成績（上位5傑）表」の「チーム番号」が一致した場合に，その行を抽出するSQL文は，次のようになります。

IN述語（副問合せが複数行を返す場合）

　IN述語は，列の値が，副問合せの結果のいずれかに一致するかどうかを調べる場合に使用します。

SELECT　選択リスト　FROM　表名1
WHERE　列名1 ［NOT］ IN （SELECT　列名2　FROM　表名2　…）

※ 列名1と列名2は同じ属性でなければならない

※ 「NOT」を付けると，副問合せの結果のいずれとも一致しないかどうかを調べることができる

　たとえば，次の「試合結果表」から重複なしで「選手番号」を抽出し，その結果のいずれかと「投手表」の「選手番号」が一致した場合に，その行の「選手番号」と「選手名」を抽出するSQL文は，次のようになります。

限定述語（ANY，ALL）（副問合せが複数行を返す場合）

　ANYは，副問合せの結果が0行ではなくて，列の値が副問合せの結果の少なくとも1つの値より大きいとか，小さいなどというように，比較の条件を満たしているかどうかを調べる場合に使用します。

SELECT　選択リスト　FROM　表名1
WHERE　列名1　比較演算子　ANY （SELECT　列名2　FROM　表名2　…）

※ 列名1と列名2は同じ属性でなければならない

※ 「列名＝ANY（副問合せ）」は，「列名 IN（副問合せ）」と同じ意味

　ALLは，副問合せの結果が0行（空）であるか，列の値が副問合せの結果のすべての値より大きいとか，小さいなどというように，比較の条件を満たしているかどうかを調べる場合に使用します。

SELECT　選択リスト　FROM　表名1
WHERE　列名1　比較演算子　ALL （SELECT　列名2　FROM　表名2　…）

※ 列名1と列名2は同じ属性でなければならない

※ 「列名<>ALL（副問合せ）」は，「列名 NOT IN（副問合せ）」と同じ意味

相関副問合せ

　相関副問合せは，主問合せの結果を1行ずつ副問合せに渡して実行し，結果を主問合せに返す副問合せです。

SELECT　選択リスト　FROM　表名1
WHERE ［NOT］ EXISTS （SELECT　列名2　FROM　表名2　…）

※ 列名1と列名2は同じ属性でなければならない

※ EXISTSは，副問合せからの結果が1行でもあれば真，1行もなければ偽と評価する演算子

　たとえば，主問合せで「選手表」から「選手番号」を検索して，その結果を1行ずつ副問合せに渡し，主問合せから渡された（選手表の）「選手番号」と「打撃成績表」の「選手番号」が一致し，かつ，「試合数」が100以上の行が，1行以上あれば真，1行もなければ偽と評価して主問合せに渡し，副問合せからの結果が真の行の「選手番号」を抽出するSQL文は，次のようになります。

相関副問合せに関する説明画像

行の挿入（追加）（INSERT文）

　行を挿入（追加）する場合は，「INSERT文」を使用します。

挿入する値を明示的に指定する場合

INSERT　INTO　表名　［（列名リスト）］　VALUES　（値リスト）

SELECT文で検索した結果を，すべて挿入する場合

INSERT　INTO　表名　［（列名リスト）］　SELECT文

　たとえば，次の「選手表」に，「選手番号」が「1003」，「選手名」が「さぶろー」，「投」が「右」，「打」が「右」の行を挿入するSQL文は，次のようになります。

　また，次の「殿堂入り選手一覧表」に「2023年度殿堂入り選手表」の，すべての行を挿入するSQL文は，次のようになります。

値の更新（変更）（UPDATE文）

　値を更新（変更）する場合は，「UPDATE文」を使用します。

UPDATE　表名　SET
列名1　＝　変更値，
列名2　＝　変更値，
…
［ WHERE　条件 ］

※ WHERE句の条件に一致した行の値が更新される

※ WHERE句を省略した場合は，すべての行の値が更新される

　たとえば，次の「打撃成績表」で，「選手番号」が「1001」の行の「打数」に5を，「安打」に4を加えるSQL文は，次のようになります。

行の削除（DELETE文）

　行を削除する場合は，「DELETE文」を使用します。

DELETE　FROM　表名　［ WHERE　条件 ］

※ WHERE句の条件に一致した行が削除される

※ WHERE句を省略した場合は，すべての行が削除される

　たとえば，次の「選手表」で，「選手番号」が「1002」の行を削除するSQL文は，次のようになります。

まとめ

　今回は，SQLについて，シンプル（ではないです…）にまとめてみました。例を見ながら少しずつ理解できたらよいと思います。根気よく頑張りましょう。

※ 「まとめ」だけは，シンプルにしました…。