データベース言語

last update 2007.4.7

◆ＳＱＬ

表の作成 CREATE TABLE/ ビューの作成 CREATE VIEW/ 問合せ SELECT/ 挿入 INSERT/ 更新 UPDATE/ 権限 GRANT

◇作成(CREATE TABLE)
まずは表を作成する必要がある。単純な表は、列名と属性を書き連ねていくだけで作成できる。

/* 表の作成例 */
CREATE TABLE 社員表
  ( 社員コード NUMBER(7),
    社員氏名   CHAR(20),
    住所       CHAR(40),
    電話番号   CHAR(12),
    生年月日   CHAR(8) )

各項目について、データーベース特有の属性、制約を追加することが出来る。

制約の書き方
制約は各項目の直後に書いたり、表の最後に制約名毎にまとめても書ける。

CREATE TABLE 学生表
    ( 学生コード    NUMBER(7) NOT NULL, 
      氏名          CHAR(20),
      住所          CHAR(40),
      クラスコード  CHAR(12)
      PRIMARY KEY(学生コード)
      FOREIGN KEY(学生コード) REFERENCES 成績表 ON DELETE CASCADE )

学生コードは、主キーで、NULL値を許さない。
成績表を親表とする。参照キーの列を省略しているので、この場合は成績表の主キーを参照する。
REFERENCES 成績表(学生コード) のように明示的に列名指定も可能。
ON DELETE CASCADE指定により、親表(学生表)の参照キー(学生コード)の値を削除できるようになり、また、参照整合性を維持するために、子表(成績表)の依存行も自動的に削除する。

整合性制約

整合性制約	説明
CONSTRAINT const	整合性制約の定義と制約名の格納
NULL	列にNULL値を含むことが出来る。[default]
NOT NULL	列にNULL値を含むことは出来ない。
UNIQUE	列または列の組み合わせを一意キーとして指定する。
PRIMARY KEY	列または列の組み合わせを表の主キーとして指定する。
FOREIGN KEY	列または列の組み合わせを参照整合性制約の外部キーとして指定する。
REFERENCES	参照整合制約の外部キーによって参照「される」主キーまたは一意キーとして指定する。
ON DELETE CASCADE	参照主キーまたは参照一意キーを削除する時に、依存外部キーを自動削除する。(参照整合性が維持される。)
DISABLE	整合性制約を使用禁止にする。

◇作成(CREATE VIEW)
「ビュー VIEW」とは、表の実体はそのままにして、自分の見たい情報を切り出したり計算して取り出して作成した一時的・論理的な表。

/* ビューの作成例 */
CREATE VIEW 試験平均点ビュー
    AS SELECT 科目コード, AVG(点数)
    FROM 試験成績表
    GROUP BY 科目コード

・列に別名を付与してビューを作成

CREATE VIEW ＳＥビュー(ＳＥ番号,氏名,部署,級)
    AS SELECT 社員番号,社員氏名,部署コード,職級
      FROM 全従業員表
      WHERE 職種='ＳＥ'
    WITH READ ONLY

◇カーソル宣言(DECLARE CURSOR)

カーソルとは、SELECT文によるクエリの結果集合を一時的に蓄え、１行ずつ取り出すための仕組み。結果集合から１行を読み出すことを「フェッチ fetch」する、と言う。

◇問合せ(SELECT)
表を作って莫大な量のデータを突っ込んだら、次は効率的に自分の欲しい情報だけを選んでくる、という機能が必要。データベースを作る以上、どのように情報選択（SELECT）が出来るか、ということが最も重要であり、問い合わせ機能こそがＳＱＬの命であり核心である。

/* 条件合致行数のカウント */
SELECT COUNT(*) FROM <表名> WHERE A=1 AND B=1

/* 指定項目の降順出力 */
ORDER BY <項目名> DESC

/* GROUP BY による平均値の算出 */
SELECT 発注番号, AVG(発注数量) FROM 発注明細
    GROUP BY 発注番号

SELECT 部署ID，部署名，SUM(売上金額)
   FROM 部署表，売上表             /* 表を２つ指定→結合 */
   WHERE 部署表.部署ID = 売上表.部署ID       /* 等価結合 */
        AND 職種 = '営業'   /* 集計対象部署をWHEREで絞る */
   GROUP BY 部署ID                  /* グループ(要約)化 */
   HAVING SUM(売上金額) > 1000000 /* 要約結果を更に絞る */

順番:WHERE句で絞り込み→グループ形成→HAVING句で絞り込む
結合条件が無い場合は直積演算が戻され、常に大量の行を生成し、そのほとんどは役に立たない。100行の表２つの直積演算の結果は10000行になる。まずはWHEREで絞り込むこと。通常、HAVING句はWHERE句に記述できない条件を書くときだけ使う。

・ＳＱＬ関数

グループ関数

関数名	構文	説明
AVG	AVG([DISTINCT\|ALL] n)	nの平均値を戻す
COUNT	COUNT({*\|[DISTINCT\|ALL] expr})	exprを指定した場合、NULLでない行の数を返す。 *を指定した場合、重複値,NULL値も含めた全行数を返す。
MAX	MAX([DISTINCT\|ALL] expr})	exprの最大値を返す。
MIN	MIN([DISTINCT\|ALL] expr})	exprの最小値を返す。
SUM	SUM([DISTINCT\|ALL] n})	nの合計値を返す。
VARIANCE	VARIANCE([DISTINCT\|ALL] x})	xの分散を返す。
STDDEV	STDDEV([DISTINCT\|ALL] x})	xの標準偏差を返す。

・GROUP BY

特定の項目について集計する機能を提供する。

表Ｔ

A	B	C
リンゴ	青森	1
リンゴ	青森	2
リンゴ	青森	4
リンゴ	秋田	3
みかん	静岡	5
みかん	静岡	1
みかん	静岡	3
みかん	愛媛	7
みかん	愛媛	6
なし	山梨	4
なし	山梨	3

問合せ(１)

  SELECT A,SUM(C)
      FROM Ｔ
      GROUP BY A

問合せ(２)

  SELECT A,B,SUM(C)
      FROM Ｔ
      GROUP BY A,B

問合せ(３)

  SELECT A,B,SUM(C)
      FROM Ｔ
      GROUP BY A

検索結果(１)

A	SUM(C)
リンゴ	10
みかん	22
なし	7

検索結果(２)

A	B	SUM(C)
リンゴ	青森	7
リンゴ	秋田	3
みかん	静岡	9
みかん	愛媛	13
なし	山梨	7

検索結果(３)？

A	B	SUM(C)
リンゴ	？	10
みかん	？	22
なし	山梨？	7

問合せ(1)の場合は、「項目A」についてグループ化されるので、項目Aの列で同じものがまとめられて、1行の要約行が出力される。
問合せ(2)の場合は、「項目A」及び「項目B」について、同じものがまとめられる。項目A,B両方が同じ行が集められて 1行に要約される、つまり、どちらか一方が違えば、違う要約行として出力される。
では、問合せ(3)の場合は、どうなるんだろう？「項目A」について同じものだけをまとめている。ところが、問合せ項目ではBの内容も出力するように指定されている。 Aについて同じものを1行にまとめてしまうので、Bは一意に定まらない。一体、こーゆー時に、Bについては、どんな結果が出るのか。答えは簡単、「この問合せ自体が、エラーになります。」このように、GROUP BYを使用する場合、問合せ項目の全てが、集約化された時に一意に定まる構文になっているか、注意する必要がある。

・副問合せ subquery

副問合せとは、問合せに使われるWHERE句の検索条件に、ほかの問合せを使用すること。
以下の例では、注文表に存在する顧客番号を問合せ、その結果を含まない顧客を、顧客表に問合せている。従って、この問合せ全体では「注文を受けていない顧客」を選択することができる。

  SLECT * FROM 顧客表
      WHERE 顧客番号 NOT IN (
         SELECT Distinct(顧客番号) FROM 注文表)

・相関副問合せ correlation subquery

相関副問合せでは、副問合せの選択条件の中で、主問合せが参照されている。
（相関の無い）副問合せでは、副問合せが最初に纏めて実行されてから主問合せが実行されるのに対して、相関副問合せでは、主問合せの１行毎に副問合せが実行される。
以下の例では、副問合せの中で、FROM句に無い、主問合せの表 A を参照している点に注目する。主問合せで A のある商品を参照する時、副問合せで、その商品と異なる商品の全ての単価を問合せ、主問合せではその全てよりも単価が高いことを条件としている。つまり、この相関副問合せでは、最も単価の高い商品が抽出される。
ALLは、比較演算子の後に置かれ、副問合せ結果の全てについて比較が成立する時に TRUE を返す。

  SELECT * FROM 商品表 A
      WHERE A.単価 > ALL(
         SELECT 単価 FROM 商品表 B
                 WHERE A.商品番号 <> WHERE B.商品番号)

以下の例では、副問合せのWHERE句の中で、主問合せの商品表を参照している。主問合せの商品表を検索する都度、その商品が副問合せで売上明細にあるかチェックされる。つまり、この相関副問合せでは、売上明細に存在する商品だけが抽出される。
EXISTSは、副問合せの結果が空でない時に TRUE を返す。

  SELECT 商品番号 FROM 商品表
      WHERE EXISTS(
         SELECT * FROM 売上明細
                 WHERE 売上明細.商品番号 = 商品表.商品番号)

◇挿入(INSERT)
・VALUES句で値を指定し、単一行を挿入する。

INSERT INTO 部署表
    VALUES (18, '営業部', '三田地区')

・副問合せが戻す行の全てを挿入する。

INSERT INTO 賞与表
    SELECT 氏名, 職級, 基本給与, 成績点 FROM 売上表
       WHERE 成績点 > 75
             OR 職級 IN ('社長', '取締役', '管理職')

◇更新(UPDATE)

UPDATE文は、条件を満たす行の指定された項目の情報を更新する。

UPDATE 試験成績表
    SET 追試フラグ = 1
    WHERE 点数 < 平均点数 - 30

UPDATE 雇用表
    SET 職級='主任', 給料=給料+1050, 部署コード=17
    WHERE 氏名='HIJK'

◇権限(GRANT)

GRANT文は、対象となる表やビューへの、利用者への権限を指定する。
GRANT <権限> ON <表/ビュー> TO <利用者> [WITH GRANT OPTION]

<権限>

権限	許可される操作	表	ビュー
DELETE	DELETEコマンドでの行削除	○	○
INSERT	INSERTでの新規行追加	○	○
SELECT	SELECTによる問い合わせ	○	○
UPDATE	UPDATEによるデータ変更	○	○
ALTER	ALTERTABLEでの表定義の変更	○
INDEX	CREATE INDEXによる索引の作成	○
REFERENCES	表の参照制約の作成	○

ALL PRIVILEGES を指定すると全ての権限を付与できる。

<利用者>

権限付与対象説明
user ユーザ

role ロール。ユーザまたは他のロールに付与できる権限の集合

PUBLIC 全てのユーザ
WITH GRANT OPTION指定
権限受領者(ユーザまたはPUBLIC)が、他のユーザまたはロールにオブジェクト権限を付与できるようにする。

権限付与対象	説明
user	ユーザ
role	ロール。ユーザまたは他のロールに付与できる権限の集合
PUBLIC	全てのユーザ

GRANT SELECT, UPDATE ON 教育受講履歴表 TO PUBLIC

GRANT REFERENCES(雇用者コード), UPDATE(雇用者コード,給料,職級)
    ON 雇用表 TO 人事部員