実は，SQLはデータ定義言語(DDL：Data Definition Language)とデータ操作言語(DML：Data Manipulation Language)に分類されます．

前回見たSELECTやらWHEREやらはDMLに分類されます．

後半の記事では，DDLや表の結合，副問合せを扱います．

1.データ定義言語 DDL

前回はDMLを見ましたが，今回はDDLについて見ていきます．

1.1.CREATE TABLE

CREATE TABLE文は，テーブルを新規作成するコマンドです．

基本的な構文が3つあるので順に見ていきます．

1.1.1.構文1

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
)

主なデータ型は下の通りです．

f:id:chankilu23:20210109194124j:plain

また，主な列制約は下の通りです．

f:id:chankilu23:20210109194150j:plain

構文1に従って表を作成する例を挙げます．

例1

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3) CHECK(成績<=100),
学部コード $\hspace{0.1cm}$ DEC(3) REFERENCES 学部
)

1.1.2.構文2

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
表制約
)

主な表制約を挙げます．

PRIMARY KEY("列名1", "列名2", ...)

"列名1", "列名2", ...を主キーに設定します．

なお，同時に非NULL制約と一意性制約が設定されます．

FOREIGN KEY("列名1", "列名2", ...) REFERENCES "表名" ("列名A", "列名B", ...) [+]

"列名1", "列名2", ...を，"表名"を参照先とする外部キーに設定します．

"列名1", "列名2", ...に対応する主キーの名称を指定する場合は"列名A", "列名B", ...とします．

"列名1", "列名2", ...と"列名A", "列名B", ...が同じ名称の場合は省略可能です．

また，[+]で指定した規則に従います．

[+]はを参照してください．

CHECK("条件")

検査制約を設定します．

以上を踏まえて，構文2に従って表を作成する例を挙げます．

例2

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学部コード) REFERENCES (学部),
CHECK(成績　<= 100)
)

1.1.3.構文3

CONSTRAINTを用いますが，これは制約に名前をつけるだけで，表制約の書き方は上の構文2と同じです．

CONSTRAINT "制約名" "表制約" の形で記述し，"表制約"に"制約名"という名前をつけられます．

CREATE TABLE(

"列名1" "データ型" "列制約"
"列名2" "データ型" "列制約"
$\vdots$
CONSTRAINT "制約名1" "表制約", $\dots$
CONSTRAINT "制約名1" "表制約", $\dots$
)

構文3に従って表を作成する例を挙げます．

例3

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4) PRIMARY KEY,
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
CONSTRAINT 学生制約1 PRIMARY KEY(学籍番号),
CONSTRAINT 学生制約2 FOREIGN KEY(学部コード) REFERENCES (学部),
CONSTRAINT 学生制約3 CHECK(成績　<= 100)
)

1.2. 外部キーの更新規則と削除規則

以前話扱った参照制約の話に関係します．

外部キーの参照先の表の行が更新・削除されたときに，外部キー側(参照元)ではどうするかという話をここでは扱います．

以下の4つのパターンがあり，ON UPDATE(更新時)と，ON DELETE(削除時)に続けて指定します．

・NO ACTION

参照先のある行を更新・削除したとき，参照元側に参照先の対象の行と同じ値を持つ行があったとき，更新・削除は拒否されます．

例4

CREATE TABLE 学生(

学部表(参照先)の学部コード02の行を削除しようとすると，学生表(参照元)にも学部コード02の行があるため，学部表の学部コード02の行の削除は拒否されます．

なお，以下青字は主キーを表します．

f:id:chankilu23:20210114110152j:plain

・CASCADE

参照先のある行を更新・削除したとき，参照元側に参照先の対象の行と同じ値を持つ行があったとき，更新・削除を許可し，参照元側も同様に更新・削除をします．

例5

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，こちらも削除します．

f:id:chankilu23:20210114110239j:plain

・SET DEFAULT

参照先のある行を削除しようとしたとき，参照先の削除を許可し，参照元側に参照先の主キーの値と同じ行があれば，デフォルト値で更新します．

例6

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，その行の学部コードをデフォルト値に設定します．

(ここでは，00をデフォルトにしました)

f:id:chankilu23:20210114110335j:plain

・SET NULL

参照先のある行を削除しようとしたとき，参照先の削除を許可し，参照元側に参照先の主キーの値と同じ行があれば，NULL値で更新します．

例7

CREATE TABLE 学生(

①学部表(参照先)で学部コード02の行を削除しようとします．

②学生表(参照元)にも学部コード02の行があるので，その行の学部コードをNULL値に設定します．

f:id:chankilu23:20210114110354j:plain

1.3. ビュー

ビューは簡単に言うとSELECT文に別の名前をつけたもので，一時的に作成される仮想的な表です．

主なメリットは以下の2つです．

・複雑なクエリ(コマンドのようなもの)をあらかじめビューとして作成することで，何度も実行するときに大幅に効率が良くなる．

・アクセス権を設定できるため，セキュリティ向上につながる．

ビューを作るコマンドは以下のCREATE VIEWの構文に沿って行われます．

CREATE VIEW "ビュー名" ("列名1", "列名2", ...)

AS SELECT "列名A", "列名B", ...

FROM "表名1", "表名2", ...
WHERE "条件"

SELECTで指定した"列名A", "列名B", ...の値がそれぞれ"列名1", "列名2", ...に順次格納されます．

以下に例と対応する表(表5)を示します．

例8

CREATE VIEW 学部別学生一覧(学部コード，学部，学籍番号，氏名，)

AS SELECT 学部コード，学部，学籍番号，氏名

FROM 社員，部門
WHERE 学生.学部コード=学部.学部コード
ORDER BY 学部.学部コード，学生番号

f:id:chankilu23:20210114163557j:plain

上で作成したビューを用いて，学部コードが03の行を抽出する例と対応する表(表6)を示します．

例9

SELECT *

FROM 学部別学生一覧
WHERE 学部コード=03

f:id:chankilu23:20210114163616j:plain

このSELECT文を実行するには，学部別学生一覧に対するSELECT権限を持っている必要があります．

なお，学生表および学部表に対するSELECT権限は無くてもOKです．

(権限の話は後述します)

1.3.2. ビューの更新

1.3.1.で触れたように，ビューは複雑なクエリなどをまとめるようにして作成した仮想的な表ですが，実は条件を満たせばビューを用いて元の表の更新ができます．

条件とは例えば以下のものです．

SELECTの後に集合関数を含まない．

DISTINCTを含まない．

HAVINGやGROUP BYを含まない．

ただし，上記意外にも例外はあります．

1.4. アクセス権付与

ここでは表やビューへのアクセス権を扱います．

表，ビューの作成者(オーナという)は，作成した表やビューに関する参照，挿入，削除，更新などの権限を全て持っています．

オーナは，表やビューに関する諸権限を他のユーザに付与したり取り消したりできます．

そこで使うコマンドが，GRANTとREVOKEです．

1.4.1. GRANT

GRANTは以下のような構文で，他のユーザにアクセス権を付与できます．

GRANT "権限範囲" ON "表名" TO "ユーザ1", "ユーザ2", ... $\hspace{0.5cm}$ (WITH GRANT OPTION)

WITH GRANT OPTIONをつけると，指定したユーザが，指定した"権限範囲"に関する権限を他のユーザに付与できるようになります．

権限範囲は主に以下のようなものがあります．

上記の構文に沿って具体的に示していきます．

SELECT

行の参照権限を指定したユーザに付与します．

以下の例では，木村に学生表に関する参照権限を付与します．

例10

GRANT SELECT ON 学生 TO 木村

INSERT

行の挿入権限を指定したユーザに付与します．

なお， INSERTの後に("列名1", "列名2", ...)をつけると，"列名1", "列名2", ...のみの挿入権限を付与できます．

以下の例では，任意のユーザに学生表に関する挿入権限を付与します．

(PUBLICを指定すると，任意のユーザを指定できます)

例11

GRANT INSERT ON 学生 TO PUBLIC

DELETE

行の削除権限を指定したユーザに付与します．

例は省略します．上と同じです．

UPDATE

行の更新権限を指定したユーザに付与します．

なお， UPDATEの後に("列名1", "列名2", ...)をつけると，"列名1", "列名2", ...のみの更新権限を付与できます．

以下の例では，木村に学生表に関する更新権限を付与します．

また，木村に「学生表に関する更新権限を付与する」権限を付与します．

(つまり，木村は学生表に関するUPDATEのみ，他のユーザにGRANTできます)

例12

GRANT UPDATE ON 学生 TO 木村

REFERENCES ("列名1", "列名2", ...)

CREATE TABLE文において，主キーの"列名1", "列名2", ...に対する外部キーを設定する権限を付与します．

以下の例では，佐藤に学部表の主キーである学部コードの外部キー設定の権限を付与します．

例13

GRANT REFERENCES(学部コード) ON 学部 TO 佐藤

つまり，佐藤は例えば次のようなCREATE TABLE文を実行できます．

CREATE TABLE 学生(

学籍番号 $\hspace{0.55cm}$ DEC(4),
氏名 $\hspace{1.45cm}$ NCHAR(10) NOT NULL,
成績 $\hspace{1.45cm}$ DEC(3),
学部コード $\hspace{0.1cm}$ DEC(3),
PRIMARY KEY(学籍番号),
FOREIGN KEY(学部コード) REFERENCES (学部)
)

ALL PRIVILEGES

上で紹介した全ての権限を付与します．

1.4.2. REVOKE

REVOKEは以下のような構文で，他のユーザのアクセス権を取り消せます．

REVOKE "権限範囲" ON "表名" FROM "ユーザ1", "ユーザ2", ...

権限範囲は1.4.1.のGRANTと同様なので省略します．

以下の例では，木村に付与した学生表に関するINSERTとDELETEの権限を取り消します．

例14

REVOKE INSERT, DELETE ON 学生 FROM 木村

1.4.3. CREATE ROLE

ロール(role:役割)が割り当てられたユーザにまとめて権限を付与します．

ロールに関する構文は以下です．

・ロールの作成
CREATE ROLE "ロール名"
・ロールへの権限付与
GRANT "権限範囲" ON "表名" TO "ロール名"
・ロールをユーザに割り当て
GRANT "ロール名" TO "ユーザ1", "ユーザ2", ...

トップへ

2. 結合

ここでは，複数の表を1つの表にまとめる結合という操作を扱います．

結合には，内部結合，外部結合，自己結合の3種類があります．

2.1. 内部結合 INNER JOIN

FROM句でまずは結合する2つの表を指定します．

さらに，それぞれの表の列を指定し，列の値が一致する行を抽出し，1つの表に結合します．

基本的な構文は以下です．

SELECT "列名1", "列名2", 集合関数, ...
$\hspace{1cm}$ FROM "表名1" INNER JOIN "表名2"
$\hspace{1cm}$ ON "列名1" = "列名2"
$\hspace{1cm}$ WHERE "抽出条件

以下は，学生表と学部表を，学部コードで内部結合する例です．

例15(表7参照)

SELECT *

FROM 学生 A INNER JOIN 学部 B
ON A.学部コード=B.学部コード

f:id:chankilu23:20210124112037j:plain

実は，内部結合は以下のようにWHEREを使ったSQL文に書き換えることが可能です．

SELECT *
$\hspace{0.5cm}$ FROM 学生 A，学部 B
$\hspace{0.5cm}$ WHERE A.学部コード=B.学部コード

また，以下のように，INNER JOINの前にNATURALをつけると，学部コードは一度しか現れません．

例16(表8参照)

SELECT *

FROM 学生 NATURAL INNER JOIN 学部

f:id:chankilu23:20210124112055j:plain

2.2. 外部結合

外部結合は，基準となる表側の値は必ず結果表に含む結合です．

結合条件に一致しない場合でも基準の表の値は結果表に含まれます．

外部結合には，左外部結合，右外部結合，全外部結合があります．

外部結合の構文は以下のとおりです．

SELECT "列名1", "列名2", ... $\hspace{0.5cm}$ FROM "表名1" "外部結合指定" "表名2" $\hspace{0.5cm}$ ON "列名1" = "列名2" $\hspace{0.5cm}$ WHERE "抽出条件"

外部結合指定というのが上で示した左外部結合，右外部結合，全外部結合です．

それぞれ記法と共に見ていきます．

2.2.1. 左外部結合

以下は，学生表を基準に，学部表と左外部結合する例です．

例17(表9参照)

SELECT *

FROM 学生 A LEFT OUTER JOIN 学部 B
ON A.学部コード=B.学部コード

f:id:chankilu23:20210128111416j:plain

2.2.2. 右外部結合

以下は，学部表を基準に，学生表と右外部結合する例です．

例18(表10参照)

SELECT *

FROM 学生 A RIGHT OUTER JOIN 学部 B

f:id:chankilu23:20210128111433j:plain

2.2.3. 全外部結合

以下の例は，学生表と学部表を基準に，全外部結合する例です．

例19(表11参照)

SELECT *

FROM 学生 A FULL OUTER JOIN 学部 B

f:id:chankilu23:20210128111449j:plain

2.3. 自己結合

自己結合は，そこまで深く触れません．

自己結合とは，1つの表に異なる相関名を2つつけることで別の表として扱い，その2つの表を1つに結合することです．

例えば，以下のように，大学ー学部ー学科ー学生，という関係を考えます．

f:id:chankilu23:20210128132030j:plain

これを1つの表に，例えば次のように"大学内階層"として表すことができます．

f:id:chankilu23:20210128132346j:plain

大学内階層表に相関名X, Yをつけると，X.上位コードとY.コードが等しい行の一部は以下の色付き線のようになります．

これが自己結合です．

f:id:chankilu23:20210128132708j:plain

2.4. その他の結合

UNION JOIN

UNION JOINでは，それぞれの表の全ての行を新しい表に追加し，相手の列は問答無用でNULLにします．

以下は，学生表と学部表をUNION JOINする例です．

例20(表15参照)

SELECT *

FROM 学生 A UNION JOIN 学部 B

f:id:chankilu23:20210128193057j:plain

CROSS JOIN

CROSS JOINでは，2つの表の全ての行と列の組み合わせを作ります．

いわゆる直積演算をすることと同じになります．

以下は，学生表と学部表をCROSS JOINする例です．

例21(表16参照)

SELECT *

FROM 学生 A CROSS JOIN 学部 B

f:id:chankilu23:20210128193112j:plain

トップへ

3.副問合せ

実は，WHERE句の中にSELECT文を書くことができます．

このとき，SELECT文が二重になることがわかると思いますが，内側のSELECT文以降を副問合せといいます．

外側の問合わせを主問合せといいます．

下の図(?)において，赤字が主問合せ，青字が副問合せです．

SELECT "列名1", "列名2", ...

FROM "表名1", "表名2", ...
WHERE "抽出条件" (SELECT "列名A"

FROM "表名a", "表名b", ...
WHERE "抽出条件", "結合条件")

副問合せのSELECT文が1つの値を返すか，複数の値を返すかで少し主問い合わせの書き方が違います．

具体例と一緒に確認してみます．

3.1. 副問合せが1つの値を返すとき

以下は，副問合せが1つの値を返し，それを主問合せに渡す例です．

副問合せで学生の成績の平均を返し，主問合せで成績が平均以上の学生の表を抽出します．

例22(表17参照)

SELECT *

FROM 学生 WHERE 成績 >= (SELECT AVG(成績)

FROM 学生)

これは，副問合せでの実行結果が平均の63.83であることより，主問合せが以下のようになると解釈すると見通しが良くなります．

SELECT *

FROM 学生 WHERE 成績 >= 63.83

f:id:chankilu23:20210130141202j:plain

3.2.副問合せが複数の値を返すとき

以下は，副問合せが複数の値を返し，それを主問合せに渡す例です．

副問合せで成績が60以上の学生の学籍番号を返し，主問合せで成績が60以上の学生かつ学部コードが工学部である学生の行を抽出します．

(もっとも，これは副問合わせをせずとも，より簡単なSQL文で書けますが...)

例23(表18参照)

SELECT *

FROM 学生 WHERE 学部コード = 03 AND
学籍番号 IN (SELECT 学籍番号

FROM 学生
WHERE 成績 >= 60)

これは，副問合せでの実行結果が(0002, 0003, 0005)であることより，主問合せが以下のようになると解釈すると見通しが良くなります．

SELECT *

FROM 学生
WHERE 学部コード = 03 AND
学籍番号　IN (0002, 0003, 0005)

f:id:chankilu23:20210130141216j:plain

3.3.相関副問合せ

副問合せのうち，主問合せのFROM句で指定した表を副問合せで使っているとき，この問合わせを相関副問合せといいます．(下図の赤字部分が該当)

相関副問合せのうち，主問合せのWHERE句でEXITS またはNOT EXISTが指定されているときはいわゆる二重ループの処理を行うことになります．

SELECT "列名1", "列名2", ...

FROM "表名1"
WHERE EXIST ( SELECT "列名A", "列名B", ...

FROM "表名2"
WHERE "表名1"を含む条件式

C言語っぽい擬似コードで一般的に相関問合せの処理手順を書いてみます．

for(i=0; i<"表名1"の行数; i++){

for(j=0; j<"表名2"の行数; j++){

if(副問合せのWHERE句が真){

if(主問合わせのWHERE句が真){
"表名1"のi行目を抽出
}

}

3行目のif文の中身，副問合せのWHERE句は，"表名1"のi行目と"表名2"のj行目に関する条件式です．

簡単に言うと，"表名1"の全ての行と"表名2"の全ての行を総当たり的にWHERE句を満たすかを確かめます．

以下は，少なくとも1つ評定が6未満の講義がある学生を抽出する例です．

例24(表19参照)

SELECT *

FROM 学生 X
WHERE EXISTS (SELECT 学籍番号

FROM 評定 Y
WHERE Y.評定 < 06 AND X.学籍番号 = Y.学籍番号

f:id:chankilu23:20210131194343j:plain

学生表が3行，評定表が8行あるので，3 $\times$ 8=24回の操作が行われることになります．

まずは学生表の1行目，0001の木村について見るので，SQL文最終行について，X.学籍番号=0001になります．

つまり，WHERE句内は

Y.評定 < 06 AND 0001 = Y.学籍番号

になりますが，これを満たす行は評定表にはありません．

よって，EXISTSには引っかからず，0001の木村は抽出されません．

同様に考えると，0002の佐藤については評定表の6, 8行目が該当します．

0003の田中については評定表の2行目が該当します．

以上より，出力結果は表19のようになります．

トップへ

これでSQLの説明が一通り終わりました．表を作るのがとても時間がかかった...

次はトランザクションの話です．

参考：金子則彦「データベーススペシャリスト合格教本」

目次

1.データ定義言語 DDL

1.1.CREATE TABLE

1.1.1.構文1

1.1.2.構文2

PRIMARY KEY("列名1", "列名2", ...)

FOREIGN KEY("列名1", "列名2", ...) REFERENCES "表名" ("列名A", "列名B", ...) [+]

CHECK("条件")

1.1.3.構文3

1.2. 外部キーの更新規則と削除規則

・NO ACTION

・CASCADE

・SET DEFAULT

・SET NULL

1.3. ビュー

1.3.2. ビューの更新

1.4. アクセス権付与

1.4.1. GRANT

SELECT

INSERT

DELETE

UPDATE

REFERENCES ("列名1", "列名2", ...)

ALL PRIVILEGES

1.4.2. REVOKE

1.4.3. CREATE ROLE

2. 結合

2.1. 内部結合 INNER JOIN

2.2. 外部結合

2.2.1. 左外部結合

2.2.2. 右外部結合

2.2.3. 全外部結合

2.3. 自己結合

2.4. その他の結合

UNION JOIN

CROSS JOIN

3.副問合せ

3.1. 副問合せが1つの値を返すとき

3.2.副問合せが複数の値を返すとき

3.3.相関副問合せ