３．１SQL言語

関係データベースの標準データベース言語であるSQL（Structured Query Language）。今は使わないエンジニアはいないほど重要な分野です。

データベーススペシャリスト試験の基礎　学習の内容をまとめ。

３．１．１データベース言語方式

データベース言語の構成

データベース言語

• データ定義言語（DDL：Data Definition Language）データベースの論理構造である概念スキーマを定義
  • スキーマ言語（CREATE SCHEMA）：実表定義
  • サブスキーマ定義言語（CREATE VIEW）：概念スキーマの上位にある外部スキーマ、つまり利用者が概念スキーマベースではビュー、CODASYL仕様データベースまたはNDLではサブスキーマと呼ばれる
• データ操作言語（DML：Data Manipulation Language）

データベース言語方式

データベース言語方式

• ホスト言語方式
  • モジュール言語方式
  • 埋め込み方式（埋め込みSQL）
• 利用者言語方式：会話型SQL（フォームインタフェース）、QBE（Query By Example）
• 自立型言語方式：TSQL、PL/SQL、SQL/PSMなど。第４世代言語

３．１．２SQLの特徴・言語レベル

SQLの特徴

宣言的に問い合わせを行うこと。宣言的とは、どのように（HOW）ではなく、何をやりたいか（WHAT)を強調・名言すること。このような特徴を非手続き型の言語という。従来型のC言語やCOBOL言語などで１レコードずｔ操作をする言語を手続き型の言語という。ＳＱＬは関係完備が言語。

SQLはデータ構造やデータインテグリティの定義、表に対するデータ操作を行うだけでなく、セキュリティやトランザクションに関する機能も持つ

SQLー９２の言語レベル

SQLー９２には初級ＳＱＬ，中級ＳＱＬ、完全ＳＱＬの３つの言語レベルがある。商用のＲＤＢＭＳではあＳＱＬ－９２の初級・中級レベルの言語仕様をサポート。

３．１．３ホスト言語方式

SQLのホスト言語方式にはモジュール言語と埋め込み方式（埋め込みSQL)がある。

モジュール言語

モジュール定義、カーソル定義、手続き定義、パラメタ定義、SQL文からなる。

• SQLー９２のモジュール定義　::=MODULE[モジュール名] [NAMES ARE 文字集合指定]…[SCHEMA スキーマ名][AUTHORIZATION モジュール認可識別子]

• モジュール要素::=カーソル定義｜動的カーソル定義…

• 手続き定義:: = PROCEDURE 手続き名

• パラメタ定義：パラメータデータ型｜SQLCODE｜SQLSTATE…

埋め込み方式（埋め込みSQL)

埋め込み方式はデータ操作SQLを直接親言語（ホスト言語）に埋め込んで使用。Ada、C、COBOL,Fortranなど。埋め込み変数、カーソル定義、埋め込みSQL文、SQLSTATE

３．１．４データ定義

SQL-92のデータ定義は、スキーマ定義、表定義、ビュー定義、権限定義、整合性制約、定義域（ドメイン）定義、表明定義、スキーマ操作からなる。

スキーマ定義

データベースは一つ以上のスキーマによって定義される。

CREATE SCHEMA … AUTHORIZATION

スキーマ削除

DROP SCHEMA … ｛RISTRICT | CASCADE｝

定義域定義

ドメイン制約を定義、ドメインが持つ規定値及び整合性制約を定義。CREATE DOMAIN。ニラディック関数（引数の関数）のことでUSER、CURRENT_USER、SESSOIN_USERなど

定義域変更

ALTER DOMAIN

定義域削除

DROP DOMAIN

表定義

CREATE TABLE [{GLOBAL | LOCAL} TEMPORARY] TABLE 表名 … [ON COMMIT { DELETE | PRESERVE } ROWS ] PRIMARY KEY , FOREIGN KEY… REFERENCES , DEFAULT

ビュー定義

CREATE VIEW … AS … [WITH [ CASCADED | LOCAL ] CHECK OPTION]

更新可能ビューの条件

• SELECT の選択リストは単純な列からなる（演算式や集計関数を含まない）
• FROM句は一つの表からなる（SQL-99では２つ以上の表参照が指定できるようになった。UNION ALLや結合表などが指定できるようになった）
• DISTINCTを含まない
• WHERE句に服問い合わせを含まない
• GROUP BY句やHAVING句がない

表変更

ALTER TALBE 表名 …

列変更操作：： = ADD [COLUMN]…

| ALTER [COLUMN] … {SET … | DROP DEFAULT}

| DROP [COLUMN] … {RESTRICT | CASCADE}

表制約変更操作：：＝ADD …

| DROP CONSTRAINT … {RESTRICE | CASCADE}

表削除

DROP TABLE … {RESTRICT | CASCADE}

DROPすべき表がビューや表制約定義などで参照されている場合、RESTRICTが指定されると削除は失敗する。CASCADEが指定されると、ビューや表制約定義なども削除される。

ビュー削除

DROP VIEW … {RESTRICT | CASCADE}

整合性制約

整合性制約には、定義域制約定義、定義域（ドメイン）定義、列制約定義、表名定義がある。SQL-92では新たに参照動作や制約属性（制約条件の検査時間指定）、表明定義が追加された。また検査制約や探索条件の制限も緩和された

定義域制約定義

… CHECK （探索条件）[制約属性]

制約名定義

… [CONSTRAINT 制約名]

表制約定義

… { PRIMARY KEY | UNIQUE}…

… FOREIGN KEY （列コンマリスト）参照指定

… CHECK （探索条件）

一意制約定義

UNIQUE 、PRIMARY KEY（同時にNOT NULL）

参照制約定義

FOREIGN KEY … REFERENCES句

検査制約定義

… CHECK句

列制約定義

… NOT NULL 制約

… {PRIMARY KEY | UNIQUE} 主キー、一意制約

… 参照指定　参照制約

… CHECK　検査制約　BETWEEN

参照指定

REFERENCES 表明（列カンマリスト）

MATCH(FULL | PARTIAL

ON DELETE 参照動作

ON UPDATE 参照動作

参照動作

NO ACTION　何もしない

| CASCADE　連鎖的に該当する外部キーの行を削除（更新）

| RESTRICT　削除（更新）を拒絶

| SET DEFAULT　規定値をセット

| SET NULL　NULLをセット

表明定義

CREATE ASSERTION 制約名 CHECK（探索条件）[制約属性]

制約属性

INITIALLY { DEFERRED | IMMEDIATE } [NOT] DEFERRABLE

即時（IMMEDIATE）：SQL実行終了時に

遅延（DEFERRED）：コミット時に

権限定義

GRANT

PUBLIC（全ユーザー）

ALL [PRIVILEGES] 権限付与者がこの表に対して持つすべての権限を権限受領者に与える

REFERENCES（参照）

WITH GRANT OPSTION（さらに別のユーザーに対して付与）

権限取り消し

REVOKE

標準SQLの仕様にないデータ定義文
CREATE DATABASE、CREATE INDEX、DROP INDEX

３．１．５データ操作

カーソルによらないデータ操作

行SELECT

行SELECT [ALL | DISTINCT] …

INTO …

FROM …

[WHERE …]

[GROUP BY …]

[HAVING …]

INSERT

INSERT INTO … [（列リスト）]

{ 問合せ式 | DEFAUTL VALUES }…

UPDATE

UPDATE … SET …

[WHERE …]

探索型DELETE

DELETE FROM …

[WHERE …]

カーソルによるデータ操作

カーソル定義（DECLARE CURSOR）

DECLARE .. [SENSITIVE | INSENSITIVE | ASENSITIVE ]

[SCROL | NOSCROL] CURSOR [WITH HOLD | WITHOUT HOLD]

[WITH RETURN | WITHOUT RETURN] FOR …

…

FOR { READ ONLY | UPDATE [ OF … ] } ]　→カーソルを介した更新が可

…

[ORDER BY … ] [ <FETCH FIRST> ] … [ASC | DESC]　→ソート指定

OPEN

カーソルを先頭行へ

FETCH

カーソルの次の行を取り出す。あるいは次、前、最初、最後、任意（絶対指定、早退指定）の行を取り出す。

FETCH [… FROM ] … INTO

NEXT | PRIOR | FIRST | LAST | ABSOLUTE 数値 | RELATIVE 数値

位置づけUPDATE

UPDATE … SET …

WHERE CURRENT OF …

位置づけDELETE

DELETE FROM …

WHERE CURRENT OF …

CLOSE

CLOSE …

問合せ式

SQL-92の問合せ式では、従来からの問合せ式（SELECT 文）、UNION（和演算）、INTERSECT（積）、EXCEPT（差）、JOIN（自然結合及び外結合）、直積がが明示的に表現できるようになった。またFROM句に問合せ式が記述できるようになり、直行性（独立性）が向上した。

NATURAL JOIN ON USING …

CROSS JOIN …

INNER

LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | UNION … UION JOIN

{UNION | EXCEPT } [ALL]

[CORRESPONDIGN [ BY (…)]

SELECT [ALL | DISTINCT]

…

FROM …

[ WHERE …]

[GROUP BY …]

[HAVING …]

結合表

SQL-92 の結合表では、自然結合、OUTER JOIN（外結合）、CROSS JOIN（直積）、UNION JOINが表現できる。

OUTER JOIN

LEFT OUTER JOIN
RIGH OUTER JOIN

FULL OUTER JOIN

UNION JOIN（和演算）

CROSS JOIN（直積）

非結合問合せ式

UNION（和）、INTERSECT（積）、EXCEPT（差）、問合せ指定（SELECT）

… {UNION | EXCEPT} [ALL]

[CORRESPONDING [ BY …]]

… {INTERSECT} [ALL]

[CORRESPONDING [ BY …]]

問合せ指定

SELECT [ALL | DISTINCT]…

FROM …

[WHERE …]

[GROUP BY …]

[HAVING …]

選択リスト

値式

列参照　表名．列名

集合関数参照　AVG MAX MIN SUM COUNT

CASE 演算子

COALESCE関数、NULLIF関数

表式

FROM …

[WHERE …]

[GROUP BY …]

[HAVING …]

表明の後の相関名（別名）は範囲変数と呼ぶ

相関名を使った自己結合（再帰結合）

探索条件

行SELECT、探索型UPDATE、探索型DELETE、問い合わせ指定のWHERE 句、FROM句、HAVING句

OR … AND … NOT … IS[NOT] { TRUE | FALSE | UNKNOWN } ]…

比較述語 | BETWEEN | LIKE | NULL | IN | EXISTS | UNIQUE | MATCH | OVERLAPS

比較述語

= | <> | < | > | <= | >=

副問い合わせ（行サブクエリ）

値式コンマリスト

真理値表

BETWEEN述語

[ NOT ] BETWEEN … AND …

LIKE 述語

[ NOT ] LIKE パターン [ ESCAPE …]

LIKE ’20__’

LIKE ‘山%’

LIKE ‘%熱%’

NULL述語

IS [ NOT ] NULL

IN 述語

[ NOT ] IN …

X IN 副問い合わせはX=ANY 副問い合わせと同じ

X NOT IN 副問い合わせは、X<>ALL 副問い合わせと同じ

限定述語

ANY (または SOME）限定述語

ALL 限定述語

EXISTS述語

EXISTS 副問い合わせ

ALL限定述語が、量記号に関するド・モルガンの法則

ALL 限定述語をNOT EXISTS へ変換

相関副問い合わせ

相関副問い合わせ（correlated sub-query）

商演算

→EXISTS を用いた相関副問い合わせ （NOT EXISTS … NOT EXISTS）

UNIQUE述語

UNIQUE （問い合わせ式）

MATCH述語

MATCH [ UNIQUE ]（問い合わせ式）

OVERLAPS述語

行値構成子 OVERLAPS 行値構成子

３．１．６クライアントサーバ機能と情報スキーマ

クライアントサーバー機能

コネクション管理

CONNECT、DISCONNECT、SET CONnECTION

セッション管理

SET SESSION AUTHORIZATION

SET CATALOG

SET SCHEMA

SET NAME

SET TIME ZONE

トランザクション管理

COMMIT / ROLLBACK

COMMIT WORK

ROLLBACK WORK

SET TRANSACTION

[ READ ONLY | READ WRITE]

[DIAGNOSTICS SIZE n ]

[ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} ]

SET CONSTRAINTS ( 制約名コンマリスト | ALL ) ( DEFERRED | IMMEDIATE )

情報スキーマ

情報スキーマ（infomation schema）メタデータ

INFORMATION_SCHEMA_CATALOG_NAME、DOMAINS、TABLES、VIEWS、COLUMNS、TABLE_PRIVILEGES、…

３．１．７動的SQLと静的SQL

静的SQL

挿入値、更新値、探索条件の値を埋め込み変数やパラメータによって実行時に与える。

動的SQL

動的SQLでは表明、列名、条件式、SQL文事態を実行時に与えることができる。必要なSQL文を実行時に動的にジェネレートし実行。

EXECUTE IMMEDIATE

PREPARE

EXECUTE

ALLOCATE CURSOR

DECLARE CURSOR

ALLOCATE CURSOR

OPEN

３．１．８ストアドプロシージャとトリガ

ストアドプロシージャは、クライアントサーバー環境におけうる通信量の削減や機密性の向上などに効果のある機能。トリガはイベント対応の更新処理を記述する機能で、データベースの更新制約条件の記述能力を大幅に高める。

ストアドプロシージャ

ストアドプロシージャ（stored procedure）とは、表やビューに対するまとまった処理をあらかじめコンパイルし、実行可能な状態としてサーバ側のデータベースに蓄積したモジュールのこと。クライアント型からは、ストアドプロシージャーの名前を呼ぶだけで、サーバーに処理を依頼できる（リモートプロシージャーコール：RPC）。ストアドプロシージャーには、IN、OUT、INOUTの３種類のパラメータが記述できる。またSQL文と手続き型言語で用いられる宣言文や制御文（BEGIN END、IF、WHILE等）が記述できる。

ストアドプロシージャを利用すると、次のよような効果がある。

1. 複数SQL文からな手続きを１回の呼び出しで実行できるのでクライアントとサーバー間の通信量を削減
2. 複数のプログラムが共通のプロシージャを共有、資源を節約。
3. システム全体に共通な処理をプロシージャとして格納、処理の標準化
4. 機密性の高いデータに対する処理を特定プロシージャ呼び出しに限定、セキュリティ向上

ただし、データベースへのアクセスを細かい単位でプロシージャ化しても性能（スループット）は出せない

トリガ

トリガ（trigger）とは、あらかじめデータベースに登録された任意の表に対する更新処理の手続きのこと。イベントの発生として特定の表に対する更新処理が行われる。それをトリガ（契機）として任意の表に対する更新処理を実行。トリガではイベントの種類（INSERT、DELETE、UPDATE）やデータ操作の時期（更新前、更新後）、操作対象の行の列の新旧の値（相関値）などを用いて更新処理を記述。

CREATE TRIGGER …

ON 表名

[ REFERENCING < … > ]

{ BEFORE | AFTER }

{ INSERT | DELETE | UPDATE [ OF 列名] }

[ FOR EACH { ROW | STATEMENT } ]

[ WHEN （探索条件）]

… { OLD [ ROW] [ AS …

| NEW [ ROW] [ AS ] …

…