MS-Access の SQL クエリと SQL 基本構文 (付. Oracle SQL

MS-Access の SQL クエリと SQL 基本構文 (付. Oracle SQL)
1. はじめに
MS-Access はリレーショナル・データベース・マネジメント・システム（RDBMS）である。ここでは，MS-Access を使って
SQL（Structured Query Language，構造化照会言語）の利用を学ぼう。
MS-Access が扱うデータベースはファイルとしては１つである。拡張子には"accdb"がついている。ファイルは１つだ
がその中には，複数のテーブルや，複数のクエリ（query）や，その他の管理操作情報がいっしょに含まれている。
クエリとは，データの照会機能(検索機能)のことである。２とおりある。
(1) 照会画面クエリ: 照会画面からキーワードと検索条件などを入力してデータの照会を行う。Web ページや図書
の検索画面などでおなじみのもの。
(2) SQL クエリ: SQL スクリプトを直接書いてデータ照会を行うもの。プログラミングにやや近い。
これらは別々のものではなく，ユーザからみたインタフェースの違いに他ならない。照会画面は，SQL に送る検索文
字や条件を所定のフォーマットで入力できるようにした GUI である。
2. MS-Access の起動とサンプルデータの概要
(1) MS-Access のアイコンをダブルクリックして起動する。
(2) Office ボタン → 「開く」 → サンプル・データベース”SQL03-lessons.accdb”を指定して開く。
(3) テーブル・パネルが表示される。ここにはデータベースに含まれているテーブルが一覧表で示される。
(4) 見たいテーブルをダブルクリックして開く。例えばテーブル A1 は次のようになる。
1
3. SQL クエリの作成と使用
(1) 「作成」 → 「新しいオブジェクト: クエリ」
(2) 「テーブルの表示」ウィンドウ → 「閉じる」
(3) 「クエリツール」 → 「デザイン」 → 「SQL ビュー」
(4) SQL 入力画面になる → SQL 文を入力する
2
(5)
「クエリツール」 → 「デザイン」 → 「実行」 → 実行結果が表示される
(6) 「クエリ 1」タブを右クリック → 「上書き保存」
(7) 「名前を付けて保存」 → 「例題 01」と名前を付ける → 「OK」
(8) クエリ・パネルにオブジェクトとして追加される。ダブルクリックすれば開く。タブを右クリック → 「閉じる」
(9) SQL 文を編集するには，「例題 01」ダブルクリック → 「ホーム」 → 「表示」 → 「SQL ビュー」
(10) SQL 画面からテーブル表示にするには，「ホーム」 → 「表示」 → 「テーブルシートビュー」
3
4.
SQL によるデータ照会の例題 (MS-Access)
はじめに
SQL（Structured Query Language，構造化照会言語）は，リレーショナル・データベース
を操作するための言語である。つまり，データベースを構築したり，データを探索したり，
データを更新したりするのに使用する言語である。
ここでは，SQL によるデータ探索の分野を学ぶことにしよう。
リレーショナル・データベースからデータを探索するための理論的基礎を与えているのは，
集合演算としてのリレーショナル代数である。そしてリレーショナル代数を実現すること
のできる言語の１つが，SQL である。
しかしながら，SQL はリレーショナル代数の８つの演算をそれぞれ直接に実現するようなコ
マンド群として存在するわけではない。つまり１対１に対応するわけではない。リレーショ
ナル代数を SQL で実現するには， SQL の構文にしたがってプログラミングをする必要がある。
以下は，SQL プログラミングの事例を示したものである。サンプルデータを用いて練習でき
るようになっている。
SQL の基本構文
SQL ステートメントによる問合せ（データ照会）の基本構文は，次のような形式である。
なお[ ]内は必要に応じて指定する句である。なくてもよい。
--------------------------SQL ステートメント
--------------------------SELECT
列名リスト
FROM
テーブル名リスト
[WHERE
探索条件]
[GROUP BY 列名リスト]
[HAVING 探索条件]
---------------------------
---------------------------------意味
---------------------------------xxx 列を抜き出して表示しなさい
xxx テーブルから探索して
探索条件が真(true)になる行を選択して
xxx 列の値でグルーピングして
探索条件が真(true)であるグループを選択して
----------------------------------
これらは SQL ステートメントの基本要素で， SELECT 句，FROM 句，WHERE 句，GROUP BY 句，
HAVING 句という。
SQL による問合せの例題
以下の例題は，サンプル用のデータベース・ファイルで実習できる。
例題 01 すべての列を表示する
------------------------------------------------------------------------------SELECT *
FROM
C1;
------------------------------------------------------------------------------テーブル C1 から，すべての行について，すべての列を抜き出して表示する。
*は，すべての列を意味する総称文字である。
文の終わりには;(セミコロン)をつける。
文の途中に挿入する空白は，半角の空白でなければならない。
4
例題 02 列を抜き出して表示する
------------------------------------------------------------------------------SELECT 顧客, 担当社員
FROM
B1;
------------------------------------------------------------------------------テーブル B1 から，すべての行について，
列「顧客」と「担当社員」を抜き出して表示する。
列名が複数あるときは，,(カンマ)で区切る。
例題 03 マルチ集合
------------------------------------------------------------------------------SELECT スーパー
FROM
C1;
------------------------------------------------------------------------------テーブル C1 から，すべての行について，
列「スーパー」を抜き出して表示する。
列「スーパー」の値であるスーパーの名称が表示されるが，
このときいくつかの行で値が重複していても，
そのまま重複した行のまま表示される。
このように重複した値をとる集合をマルチ集合(multi-set)という。
なお，重複しているというのは，行を集合の元とみて，
２つ以上の行の間ですべての列の値が等しいことをいう。
１つでも値の違う列があれば，
それらの行は重複しているとはいわない。
例題 04 DISTINCT による修飾
------------------------------------------------------------------------------SELECT DISTINCT スーパー
FROM
C1;
------------------------------------------------------------------------------テーブル C1 から，すべての行について，
列「スーパー」を抜き出して表示する。
このとき重複のないスーパー名の集合として抜き出す。
SELECT 句で DISTINCT という修飾語をつけると，マルチ集合でなくなる。
例題 05 文字定数
------------------------------------------------------------------------------SELECT 商品, 単価円, '円'
FROM
B2;
------------------------------------------------------------------------------テーブル B1 から，すべての行について，
列「商品」と「単価円」を抜き出して表示する。
このとき，文字「円」を表示する列を追加する。
このように列の一部として，一定の文字列（文字定数）を表示させるには，
文字を'(single quotation)ではさんで，列リストに加える。
このとき列名は自動的に設定される。
5
自動的に設定される列名に換えて，列名を指定したいときは，
------------------------------------------------------------------------------SELECT 商品, 単価円, '円' AS 単位
FROM
B2;
------------------------------------------------------------------------------のように，
AS 新列名
を付け加えればよい。
もし新列名が空白文字列を含むときは，全体を" "(double quotation)ではさむ。
例題 06 計算式
------------------------------------------------------------------------------SELECT 最寄り駅, 通勤手当円/1000 AS 通勤手当, '千円' AS 単位
FROM
通勤手当早見表;
------------------------------------------------------------------------------テーブル「通勤手当早見表」から，すべての行について，
列「最寄り駅」を抜き出して表示するとともに，
「通勤手当円」を 1000 で割った値と，文字定数「千円」を表示する。
この例のように SELECT 句の列名リストに計算式を書くことができる。
計算式で使用することのできる主な算術演算子として次のものがある。
-----------------------------------------------------算術演算子
使用例
意味（X と Y は列名か数値である）
---------------- -----------------------------* 掛算
X * Y
X に Y を掛ける。
/ 割算
X / Y
X を Y で割る。
+ 足算
X + Y
X に Y を足す。
- 引算
X - Y
X から Y を引く。
^ べき乗
X ^ Y
X を Y 乗する。
Mod 余り
X Mod Y X を Y で割った余り。
\ 商
X \ Y
X を Y で割った商。
-----------------------------------------------------（注）JIS 規格 SQL では，^，Mod，\の演算子はない。
例題 07 WHERE 句と探索条件及び比較述語
------------------------------------------------------------------------------SELECT 社員名
FROM
社員通勤表
WHERE 通勤手段 = '鉄道';
------------------------------------------------------------------------------テーブル「社員通勤表」から，
通勤手段が「鉄道」である行を選択して，
その行の社員名を表示する。
つまり鉄道で通勤している社員をリストアップする。
行を選択するための探索条件を定義している 3 行目を WHERE 句という。
一般に WHERE 句の探索条件は，１つ以上の述語（条件式のこと）を，
AND，OR，NOT で組み合せてできた論理式である。
6
述語（条件式）は，探索条件を構成する最小の単位で，
真(true)か偽(false)か不定(Null，ナル)のどれかの値をとる。
１つ以上の述語が AND，OR，NOT で結合された探索条件全体は，
論理式として評価され最終的に真か偽か不定のどれかの値をとる。
この例では，
探索条件は「通勤手段 = '鉄道'」という１つの述語だけからできている。
この述語は，
ある行の列「通勤手段」の値が'鉄道'という値に等しいとき，
真(true)の値をとる。
２つの間の大小関係を比較する述語を比較述語という。
述語の種類は他にもある。
比較述語で使用される=のような演算子を比較演算子という。
比較演算子には次のものがある。
-----------------------------------------------------------------------比較演算子
比較述語 意味（X と Y は列名か数値か文字定数である）
----------------- -------- ------------------------------------------=
等号演算子
X = Y
X と Y が等しい。
<
未満演算子
X < Y
X が Y より小さい。
>
超演算子
X > Y
X が Y より大きい
<> 不等号演算子
X <> Y
X と Y とが等しくない。
>= 以上演算子
X >= Y
X が Y 以上である。
<= 以下演算子
X <= Y
X が Y 以下である。
-----------------------------------------------------------------------(注)文字定数は'(single quotation)ではさまなければならない。
データ処理の流れとしていえば，
テーブルの行ごとに探索条件が評価され，
真の値をとる行だけが選択される。
例題 08 AND，OR，NOT
------------------------------------------------------------------------------SELECT 最寄り駅, 通勤手当円, '円' AS 単位
FROM
通勤手当早見表
WHERE 通勤手当円 >= 100000
AND
通勤手当円 <= 150000;
------------------------------------------------------------------------------テーブル「通勤手当早見表」から，
列「通勤手当円」の値が 100000 以上でかつ 150000 以下の行を選択して，
列「最寄り駅」と「通勤手当円」と文字定数'円'を表示する。
この例では，２つの述語（条件式）が AND で結合して探索条件になっている。
AND の書き方は，
述語 1 AND 述語 2
である。述語 1 が真でかつ述語 2 が真のとき，真の値をとる。
OR の書き方は，
述語 1 OR 述語 2
である。述語 1 が真かまたは述語 2 が真のとき，真の値をとる。
NOT の書き方は，
NOT(述語 1)
7
である。述語 1 が真なら偽，述語 1 が偽なら真の値をとる。
AND，OR，NOT による論理演算の値
AND | 真
偽 不定
----+-------------真 | 真
偽 不定
偽 | 偽
偽 偽
不定| 不定 偽 不定
OR | 真
偽
不定
----+--------------真 | 真
真
真
偽 | 真
偽
不定
不定| 真
不定 不定
NOT |
----+----真 | 偽
偽 | 真
不定| 不定
例題 09 BETWEEN 述語
------------------------------------------------------------------------------SELECT 最寄り駅, 通勤手当円, '円' AS 単位
FROM
通勤手当早見表
WHERE 通勤手当円 BETWEEN 100000 AND 150000;
------------------------------------------------------------------------------これは，上の例題 ex08 の WHERE 句を BETWEEN 述語を用いて書き換えたものである。
「通勤手当円 BETWEEN 100000 AND 150000」の部分を BETWEEN 述語という。
BETWEEN 述語は次のような書き方をする。
値式 1 BETWEEN 値式 2 AND 値式 3
値式には，列名，数値，または計算式がくる。
BETWEEN 述語は，値式 1 が値式 2 以上でかつ値式 3 以下のとき真の値をとる。
それ以外は偽の値をとる。
BETWEEN 述語の中の AND は，
述語を結合するときの論理演算子としての AND とは違って，
BETWEEN 述語の一部である。
また「通勤手当円 NOT BETWEEN 100000 AND 150000」と書くと，
否定形の BETWEEN 述語になる。
「通勤手当円」が「100000 以上かつ 150000 以下」でないとき，真の値をとる。
否定形の BETWEEN 述語は次のような書き方をする。
値式 1 NOT BETWEEN 値式 2 AND 値式 3
値式 1 が「値式 2 以上でかつ値式 3 以下」でないとき真の値をとる。
このときの NOT は，BETWEEN 述語の一部としての NOT である。
否定形の BETWEEN 述語は，
NOT ( 値式 1 BETWEEN 値式 2 AND 値式 3 )
と書いたのと結果は同じである。
このときの NOT は，
述語を結合して探索条件を作るときの論理演算子としての NOT である。
例題 10 LIKE 述語と文字列のパターン・マッチング
------------------------------------------------------------------------------SELECT 社員名
FROM
社員通勤表
WHERE 社員名 LIKE '*子';
------------------------------------------------------------------------------テーブル「社員通勤表」から，
列「社員名」の値が'子'で終わる行を選択して，
8
列「社員名」を表示する。
キーワード LIKE で構成される述語を LIKE 述語という。
LIKE 述語は，次のような書き方をして文字列のパターン・マッチングを行う。
列名 LIKE '文字パターン'
列がとる値（文字列）が，
ある文字パターンに一致しているとき真の値をとる。
文字パターンでは，
次のような特殊文字を使用して特定の文字パターンを意味することができる。
（注）JIS 規格 SQL はこれとは全く異なる。
特殊文字
-------?
*
#
[charlist]
[!charlist]
意味
------------------------------------------------------------任意の 1 文字
任意の数の文字
任意の 1 文字の数字 (0-9)
文字リスト charlist に指定した文字の中の 1 文字
（例）[a-z]とするとアルファベット小文字のどれか 1 つを意味する。
[高鷹隆]とすると高，鷹，隆のどれか 1 つを意味する。
文字リスト charlist に指定した文字以外の任意の 1 文字
（例）[!a-c]とすると，a,b,c 以外の 1 文字を意味する。
これらのうち，"#" を除くすべての文字パターンでは，
2 バイト文字 (全角文字) も 1 文字と数えて文字列比較を行う。
"#" には，1 バイト (半角) の数字だけが一致する。
1 個以上の文字のリスト (charlist) を角かっこ ([ ])
で囲んで文字パターンに指定すると，
その中のいずれかの文字と一致するかどうかを比較することができる。
例
--------------------社員名 LIKE '*子'
説明
--------------------------------------------------「社員名」が'子'で終わるとき真。'鳩山麗子'や'橋本龍子'は真。
'加藤子羊'は偽。
社員 CODE LIKE 'S4???' 「社員 CODE」が'S4'で始まる 5 桁の文字のとき真。
'S4'で始まっていても 5 桁でないときは偽。
社員名 LIKE '*山*'
「社員名」の最初でも途中でも最後でも，
どこかに'山'を含んでいれば真。
社員 CODE LIKE 'S[4-6][1-3]??'
「社員 CODE」が'S'で始まっていて，
かつ左 2 桁目が 4～6 の数字の 1 つで，
かつ左 3 桁目が 1～3 の数字の 1 つで，
かつ全体が 5 桁の文字なら真。
例題 11 IN 述語
------------------------------------------------------------------------------SELECT 住所 CODE, 最寄り駅
FROM
通勤手当早見表
WHERE 住所 CODE IN ( 'J108', 'J116', 'J123');
-------------------------------------------------------------------------------
9
テーブル「通勤手当早見表」から，
列「住所 CODE」の値が，'J108'か'J116'か'J123'のどれかをとる行を選択して，
列「住所 CODE」と「最寄り駅」を表示する。
IN 述語は，離散的に指定した複数の値のうちどれかに一致するものを選ぶとき使用する。
IN 述語の書き方
値式 IN (値 1, 値 2, ...)
値式とは，列名か計算式である。値 1 や値 2 とは，数値か文字定数である。
IN 述語は，値式が，値 1，値 2，...のどれかに一致するとき真の値をとる。
（例）
住所 CODE IN ( 'J108', 'J116', 'J123')
通勤手当 IN (100000, 125000, 56000)
文字定数は，'(single quotation)ではさまなければならない。
数値は，そのまま記述する。
IN 述語の否定形は，
値式 NOT IN (値 1, 値 2, ...)
である。これは，
NOT(値式 IN (値 1, 値 2, ...))
としても同じ結果になる。
例題 12 集合関数 SUM
------------------------------------------------------------------------------SELECT SUM(数量) AS 売上総数量
FROM
B1
WHERE
売上商品='柏クッキー';
------------------------------------------------------------------------------テーブル B1 から，
売上商品が'柏クッキー'の行を選択して，
選択した行全体について，
列「数量」の値の合計を表示する。
つまり'柏クッキー'の売上数量の合計を求める。
なお合計を表示する列には「柏クッキー売上総数量」という列名を設定している。
集合関数は，選択した行全体について，
引数で指定した列を用いて何か計算を行う関数である。
この例の SUM 関数は，WHERE 句で選択された行の列「数量」の値の合計をとっている。
例題 13 集合関数 COUNT
------------------------------------------------------------------------------SELECT COUNT(*) AS 自動車通勤者数
FROM
社員通勤表
WHERE 通勤手段 = '車';
------------------------------------------------------------------------------テーブル「社員通勤表」から，
車で通勤している社員の数を求める。
COUNT 関数は，選択された行の件数をカウントする。
引数には列名を指定しないで，単に*を指定することが多い。
（注）JIS 規格 SQL では，COUNT(DISTINCT 社員名)のように
DISTINCT を付けた列名を指定すると，
10
社員名のうち重複を除いた名称の件数が返る。
MS-Access ではこの表現は使用できない。
例題 14 GROUP BY 句によるグルーピング
------------------------------------------------------------------------------SELECT
売上商品, SUM(数量) AS 数量合計, COUNT(*) AS 売上回数
FROM
B1
GROUP BY 売上商品;
------------------------------------------------------------------------------テーブル B1 から，売上商品の名称でグルーピングして数量合計と売上回数とを求める。
GROUP BY 句は，指定した列の値が同じ行どうしを集めてグルーピングする。
ここでは売上商品の列の商品名でグルーピングしている。
グルーピングされた行全体は，
あたかも１つの行であるかのように，いくつかの属性を持つことになる。
その場合の属性とは，
もとの行の列の値か，またはそれから集合関数で計算され得るもので，
グループの属性として意味のあるものである。
SELECT 句の列リストには，そうしたグループの属性が並ぶ。
上の例のように列「売上商品」の場合は，
グループの属性としてグループ内で共通している商品名が表示される。
数量の合計もグループの属性として意味がある。しかし，
単に列「数量」を指定してもグループとしての属性にならないので実行できない。
例題 15 HAVING 句によるグループの選択
------------------------------------------------------------------------------SELECT
売上商品, SUM(数量) AS 数量合計, COUNT(*) AS 売上回数
FROM
B1
GROUP BY 売上商品
HAVING
COUNT(*) >= 2;
------------------------------------------------------------------------------テーブル B1 から，売上商品の名称でグルーピングして，
売上回数が 2 以上のグループを選択して，
数量合計と売上回数とを求める。
HAVING 句は，GROUP BY 句でできるグループのうちから，
特定のグループを選択するための探索条件を指定する。
このときの探索条件の書き方は，WHERE 句の探索条件の書き方と同じである。
例題 16 結合（join）：２つのテーブルを結合する(1)
------------------------------------------------------------------------------SELECT 社員通勤表.社員名,
通勤手当早見表.最寄り駅,
通勤手当早見表.通勤手当円
FROM
社員通勤表, 通勤手当早見表
WHERE 社員通勤表.住所 CODE = 通勤手当早見表.住所 CODE;
------------------------------------------------------------------------------テーブル「社員通勤表」とテーブル「通勤手当早見表」とから，
２つのテーブルの間で住所 CODE が一致する行を選択して，
11
列「社員名」，「最寄り駅」，「通勤手当円」を表示する。
このように２つ以上のテーブルから行と列とを選択して
１つのリレーションを作成することを結合という。
リレーショナル・データベースの最も特徴的な利用方法である。
SELECT 句の列リストで列名を書くときは，
「社員通勤表.社員名」のように，
列名「社員名」の前にテーブル名「社員通勤表」をつけて修飾する。
間には区切りの.(ピリオド)を挿入する。
これは，複数のテーブルを参照するので，
どのテーブルの列を指すのかはっきりさせるためである。
FROM 句に書くテーブル名は,(カンマ)で区切る。
WHERE 句の探索条件のところでも，列名の前にはテーブル名をつけて修飾する。
この結合処理の内部的な手順は，リレーショナル代数の表現でいうと，
直積，選択，射影をやっていることになる。
つまり，まずテーブル「社員通勤表」と「通勤手当早見表」との直積を求める。
つまり２つのテーブルの間のすべての行の組合わせを作成し，
それぞれの組合わせごとに列を横方向につなげて１つの行にする。
できた直積から，
探索条件「社員通勤表.住所 CODE = 通勤手当早見表.住所 CODE」を満たす行を選択する。
選択した行から，必要な列を抜き取る。
こうしたことをやっている。
例題 17 結合（join）：２つのテーブルを結合する(2)
------------------------------------------------------------------------------SELECT B1.顧客, B1.売上商品,
B1.数量, B2.単価円,
B1.数量 * B2.単価円 AS 売上高
FROM
B1, B2
WHERE B1.売上商品 = B2.商品;
------------------------------------------------------------------------------２つのテーブル B1 と B2 とから，
「売上商品」と「商品」に記された商品名が等しい行を選択して，
列「顧客」，「売上商品」，「数量」，「単価円」を表示するとともに，
さらに「数量×単価円」で売上高を計算して
それに「売上高」という列名をつけて表示する。
例題 18 直積（Cartesian product）：２つのテーブルのペア
------------------------------------------------------------------------------SELECT B1.*, B2.*
FROM B1, B2;
------------------------------------------------------------------------------テーブル B1 とテーブル B2 とから，すべての行を対象に直積を作成し，
すべての列を表示する。
B1.*という表現は，テーブル B1 のすべての列という意味である。
例題 19 相関名(表別名)によるテーブルの略記
------------------------------------------------------------------------------SELECT X.社員名,
12
Y.最寄り駅,
Y.通勤手当円
FROM
社員通勤表 X, 通勤手当早見表 Y
WHERE X.住所 CODE = Y.住所 CODE;
------------------------------------------------------------------------------テーブル名が長いと，それを列名の前につけて修飾するのが煩雑である。
そこで FROM 句で，
「社員通勤表 X」（間にスペースを入れる）と書くと，
テーブル「社員通勤表」を単に X というテーブル名で参照することができるようになる。
これを相関名またはエイリアス(alias，別名)という。
なお，この SQL 文では通勤手段が「鉄道」でないものも含まれる。
例題 20 相関名(表別名)による同一テーブルの参照
------------------------------------------------------------------------------SELECT TAB1.数量－TAB2.数量
FROM
B1 TAB1, B1 TAB2
WHERE TAB1.担当社員='橋本 龍子'
AND
TAB2.担当社員='亀井 勇';
------------------------------------------------------------------------------テーブル B1 の「橋本 龍子」の数量と，
同じテーブルの「亀井 勇」の数量との差を計算して表示する。
相関名を使うと，同一テーブルに異なる相関名をつけて，
あたかも異なるテーブルであるかのように扱うことができる。
１つのテーブルの中の異なる行の間で比較したり計算したりするのに有効である。
例題 21 副問合せ(1)
------------------------------------------------------------------------------SELECT 最寄り駅, 通勤手当円
FROM
通勤手当早見表
WHERE
通勤手当円>=
(SELECT 通勤手当円
FROM
通勤手当早見表
WHERE
最寄り駅 = '調布'
);
------------------------------------------------------------------------------テーブル「通勤手当早見表」の中から，
最寄り駅が調布のときの通勤手当と比べて
それ以上の金額になる最寄り駅を表示する。
WHERE 句の探索条件で，述語（条件式）の値式（列名，定数，計算式）として
問合せ（SELECT...FROM...WHERE...）を使用することができる。これを副問合せという。
副問合せは，基本的に単一の列についての値を返すような問合せである。
返ってくる値は複数あってもよいが，
単一列についてのものであるという点がポイントである。
また複数の値が返るときは，述語（条件式）が IN 述語のように，
述語として整合のとれたものでなければならない。
上の例では，最寄り駅が調布のときの「通勤手当円」の値を１つだけ返している。
副問合せは，（ ）でくくる。
データ処理的には，主問合せでテーブルの１行ごとに
WHERE 句の探索条件が真かどうかの判定が行われる点はこれまでと同じである。
ただ探索条件の判定のステップで，そのつど副問合せが発生している。
13
例題 22 副問合せ(2)
------------------------------------------------------------------------------SELECT 最寄り駅, 通勤手当円
FROM
通勤手当早見表
WHERE 住所 CODE IN
(SELECT 住所 CODE
FROM
社員通勤表
WHERE 社員名 IN ('橋本 龍子','亀井 勇')
);
------------------------------------------------------------------------------「橋本 龍子」と「亀井 勇」の住所 CODE を
テーブル「社員通勤表」から副問合せで探索して，
探索した住所 CODE に該当する「最寄り駅」と「通勤手当円」を
テーブル「通勤手当早見表」から抜き出して表示する。
この副問合せでは，「橋本 龍子」と「亀井 勇」の住所 CODE が２つ返る。
IN 述語でそれを参照している。
例題 23 EXISTS 述語(1)
------------------------------------------------------------------------------SELECT 社員名
FROM
A1
WHERE EXISTS
(SELECT *
FROM
A2
WHERE A1.社員 CODE=A2.社員 CODE
);
------------------------------------------------------------------------------テーブル A1 と A2 の間で，社員 CODE が等しいものがあれば，
その社員名を表示する。A1 と A2 の「共通（intersection）
」を求めている。
EXISTS 述語は，
EXISTS ( SELECT...FROM...WHERE... )
という形式をとり，
副問合せ SELECT...FROM...WHERE...で探索された要素が 1 つ以上あれば
（つまり空集合でなければ）真(true)の値をとる。空集合であれば，偽(false)となる。
この EXISTS 述語で使用する副問合せに限って，
SELECT 句で抜き出す列は単一でなくてよい。ふつうは
SELECT * FROM ... WHERE ...
という形式をとる。
例題 24 EXISTS 述語(2)：NOT EXISTS
------------------------------------------------------------------------------SELECT 社員名
FROM A1
WHERE NOT EXISTS
(SELECT *
FROM
A2
14
WHERE A1.社員 CODE=A2.社員 CODE
);
------------------------------------------------------------------------------テーブル A1 にあって A2 に存在しない社員の情報を抜き出している。
つまり，A1－A2（difference，差）を求めている。
NOT EXISTS 述語は，
NOT EXISTS ( SELECT...FROM...WHERE... )
という形式をとり，
副問合せ SELECT...FROM...WHERE...で探索された要素が 1 つもなければ
（つまり空集合であれば）真(true)の値をとる。空集合でなければ，偽(false)となる。
例題 25 UNION
------------------------------------------------------------------------------(SELECT * FROM A1)
UNION
(SELECT * FROM A2);
------------------------------------------------------------------------------テーブル A1 とテーブル A2 の和（union）を求めている。重複する行（すべての列の値が等しい行どうし）
は除かれて１つの行だけが選択される。
例題 26 ALL 述語
------------------------------------------------------------------------------SELECT B1.担当社員, B1.数量*B2.単価円 AS 売上高
FROM
B1, B2
WHERE B1.売上商品=B2.商品
AND
B1.数量*B2.単価円 >= ALL
(SELECT X.数量*Y.単価円
FROM
B1 X, B2 Y
WHERE X.売上商品=Y.商品
);
------------------------------------------------------------------------------他のすべての担当社員の売上高以上の売上高をもつ担当社員の情報を表示する。
つまり最大の売上高をあげた社員を選択している。
ALL 述語は，
a 比較演算子 ALL(SELECT b FROM c WHERE d)
という形式をとる。a は値式，b は列，c はテーブル，d は探索条件である。
副問合せで返ってくるすべての値に対して比較演算が真(true)のとき，
ALL 述語は真(true)の値をとる。
また上の形式の ALL 述語は，EXISTS 述語を用いて次のように書きかえることができる。
NOT EXISTS (SELECT * FROM c WHERE (d) AND NOT( a 比較演算子 c.b) )
例題 27 ANY 述語 ( SOME 述語 )
------------------------------------------------------------------------------SELECT B1.担当社員, B1.数量*B2.単価円 AS 売上高
FROM
B1, B2
WHERE B1.売上商品=B2.商品
15
AND
B1.数量*B2.単価円 > ANY
(SELECT X.数量*Y.単価円
FROM
B1 X, B2 Y
WHERE X.売上商品=Y.商品
);
------------------------------------------------------------------------------少なくとも他の１人の社員よりは大きな売上高をあげた社員を選択する。
つまり最小の売上高ではない社員を選択する
SOME 述語（ANY を用いても同じ）は，
a 比較演算子 ANY(SELECT b FROM c WHERE d)
という形式をとる。a は値式，b は列，c はテーブル，d は探索条件である。
副問合せで返ってくる値のうち少なくとも１つに対して比較演算が真(true)のとき，
SOME 述語は真(true)の値をとる。
また上の形式の ANY 述語は，EXISTS 述語を用いて次のように書きかえることができる。
EXISTS (SELECT * FROM c WHERE (d) AND ( a 比較演算子 c.b) )
例題 28 商（division）
------------------------------------------------------------------------------SELECT DISTINCT スーパー
FROM C1
WHERE NOT EXISTS
( SELECT DISTINCT X1.スーパー, X2.商品種類,X2.メーカー
FROM C1 X1, C2 X2
WHERE NOT EXISTS
( SELECT *
FROM C1 Z
WHERE X1.スーパー=Z.スーパー
AND
X2.商品種類=Z.商品種類
AND
X2.メーカー=Z.メーカー
)
AND
C1.スーパー=X1.スーパー
);
------------------------------------------------------------------------------商 C1÷C2 を求めている。
商は，
Ｑ＝Ｃ1[スーパー]－（Ｃ1[スーパー]×Ｃ2－Ｃ1）[スーパー]
で求められる。
なおここで，[スーパー]とは「スーパー」の列を取出す射影である。また，×は直積で，
－は差である。
もっとスマートな表現があるかもしれない。
16
5. Oracle SQL 要点メモ
----------------------------------------------------------列別名
(ex) 式 AS "列別名"
----------------------------------------------------------WHERE 句
(1) 列別名を使用できない。
(2) 条件式で使用する文字列と日付は一重引用符（‘’）で囲む。
----------------------------------------------------------「等しくない」の表現として可能なもの
(1) <>
(2) !=
(3) ^=
----------------------------------------------------------BETWEEN 述語で文字列の範囲指定ができる
(ex) BETWEEN 'King' AND 'Smith'
----------------------------------------------------------IN ( .... )
(ex) IN ('Hartstein', 'Vargas')
(1) 文字または日付をリストで使用するとき一重引用符（''）で囲む。
----------------------------------------------------------LIKE
(ex) LIKE '%_'
(1) % ゼロ個以上の文字
(2) _ 1 個の文字
(ex) WHERE hire_date LIKE '95%';
----------------------------------------------------------date 書式のデフォルト: RR-MM-DD
(1) RR: 西暦年の末尾 2 桁
(2) MM: 月 2 桁
(3) DD: 日 2 桁
----------------------------------------------------------ESCAPE 文字の指定
(ex) LIKE '%SA\_%' ESCAPE '\';
----------------------------------------------------------IS NULL
IS NOT NULL
----------------------------------------------------------NULL を含む比較演算
(ex) ( salary>=10000 ) AND ( job_id LIKE '%MAN%' )
salary が NULL ならば，salary>=10000 の値は NUL である。
job_id が NULL ならば，job_id LIKE '%MAN%' の値は NUL である。
true AND NULL --> NULL
false AND NULL --> false
true AND NULL --> true
false AND NULL --> NULL
----------------------------------------------------------同値な表現
17
a NOT IN ( .... )
<==> NOT (a IN ( ... ) )
a NOT BETWEEN x AND y <==> a<x OR y<a, NOT ( x<=a AND a<=y )
a
BETWEEN x AND y <==> x<=a AND a<=y
a NOT BETWEEN x AND y <==> NOT ( a BETWEEN x AND y )
a NOT LIKE '...'
<==> NOT ( a LIKE '...' )
----------------------------------------------------------演算の優先順位
()
*/
+||
< > = != ^=
IS NULL, LIKE, IN
BETWEEN
不等?
NOT
AND
OR
----------------------------------------------------------ORDER BY 句
(ex) ORDER BY {列名, 式, 列別名, 列位置} [ASC|DESC]
(ex) ORDER BY hire_date ASC
(ex) ORDER BY hire_date DESC
(ex) ORDER BY salary DESC
(ex) ORDER BY salary + commission_pct DESC
(ex) ORDER BY 句では，列別名を使用できる。
(ex) ORDER BY salary DESC, hire_date ASC;
(ex) NULL 値は、昇順の順序では最後に、降順の順序では最初に表示される。
6. Oracle SQL 関数
文字関数
●LOWER(列名|式)
英字値を小文字に変換する。
●UPPER(列名|式)
英字値を大文字に変換する。
●INITCAP(列名|式) 英字値の各語(word)の先頭文字を大文字に、
残りをすべて小文字に変換する。
●CONCAT(列名 1|式 1,列名 2|式 2) 最初の文字列と 2 番目の文字列を連結する。
連結演算子（||）と同じ機能。
●SUBSTR(列名|式,m[,n]) 文字値の文字位置 m から n 文字分の文字を戻す
（m が負の場合、文字値の末尾から数える。n を省略
すると、開始位置から文字列の末尾までのすべての
文字が戻される）。漢字は 1 文字と数える。
(ex) SELECT SUBSTR('麗澤大学', 2, 2) FROM dual;
--> 澤大
●LENGTH(列名|式) 式内の文字数を戻す。漢字は 1 文字と数える。
●INSTR(列名|式, ’文字列’, [,m],[n] )
指定した名前の文字列の位置を数値で戻す。オプショ
18
ンで、検索開始位置 m、および文字列における出現数
n を指定できる。m と n のデフォルトは 1 で、これは先頭か
ら検索を開始し、最初の出現位置を報告することを示す。
●LPAD(列名|式, n, '文字列')
●RPAD(列名|式, n, '文字列')
文字値を右揃えにし、合計の文字列幅が n バイトになる
まで文字列を埋めていく。
文字値を左揃えにし、合計の文字列幅が n バイトになる
まで文字列を埋めていく。
●TRIM([LEADING|TRAILING|BOTH], [trim_character FROM]trim_source)
文字列から先頭文字または末尾文字（あるいはその両
方）を切り捨てる。trim_character または trim_source が
文字リテラルの場合、一重引用符で囲む必要がある。
この機能が使用できるのは、Oracle8i 以降のバージョン
に限られる。
(ex) TRIM( LEADING '麗' FROM '麗澤
--> '澤 太郎'
(ex) TRIM( ' 麗澤 太郎
' )
--> '麗澤 太郎'
太郎
' )
●REPLACE(text, search_string,replacement_string)
text から search_string を検索し、検出した場合、
replacement_string に置換する。
数値関数
●ROUND(列名|式, n)
列、式または値を小数第 n 位までに四捨五入する。n が
省略された場合、整数までに四捨五入する（n が負の
場合、小数点の左側の桁で四捨五入される）。
符号は無視して四捨五入する。
●TRUNC(列名|式, n)
列、式または値を小数第 n 位までに切り捨てる。
n が省略された場合、小数点以下が切り捨てられる。
符号は無視して四捨五入する。
●MOD(m,n)
m を n で除したときの剰余を戻す。
7.
Oracle SQL 日付書式について
文字列→日付の変換: TO_DATE 関数
(1) 日本語書式の場合
TO_DATE( '文字列', '表示書式', 'NLS_DATE_LANGUAGE=Japanese' )
(注) デフォルト。
(例) TO_DATE( '20071003134324', 'YYYYMMDDHH24MISS' )
(2) 米語書式の場合
TO_DATE( '文字列', '表示書式', 'NLS_DATE_LANGUAGE=American' )
(例) TO_DATE( '03/OCT/2007 134324', 'DD/MON/YYYY HH24MISS', 'NLS_DATE_LANGUAGE=American' )
日付→文字列の変換: TO_CHAR 関数
19
(1) 日本語書式の場合
TO_CHAR( 日付, '表示書式', 'NLS_DATE_LANGUAGE=Japanese' )
(注) デフォルト。
(例) TO_CHAR(SYSDATE,'YYYY"年"MM"月"DD"日" HH24"時"MI"分"SS"秒"')
(2) 米語書式の場合
TO_CHAR( 日付, '表示書式', 'NLS_DATE_LANGUAGE=American' )
(例) TO_CHAR(SYSDATE,'MONTH DD, YYYY, DAY, HH24:MI:SS')
(3) 表示書式では，日付要素のキーワード以外の文字列は原則として""(二重引用符)で
囲む。ただし，- / ( ) _ . , :はそのまま使用できる。
日付書式における日付要素の意味
現在の日付が「2007 年 09 月 03 日月曜日 13 時 43 分 24 秒」だとする。
TO_CHAR 関数の表示書式で日付要素を使うと次のような結果になる。
----------------------------- ----------------------------------要素
NLS_DATE_LANGUAGE=Japanese NLS_DATE_LANGUAGE=American
----------------------------- ----------------------------------RR
07
07
YYYY
2007
2007
YEAR
TWO THOUSAND SEVEN
TWO THOUSAND SEVEN
MM
09
09
MONTH
9月
SEPTEMBER
MON
9月
SEP
DD
3 または 03 (注 1)
3 または 03 (注 2)
DDD
246
246 (1 月 1 日を 1 としたとき何日目かを表す)
DAY
月曜日
MONDAY
DY
月
MON
D
2
2
(日曜を 1 としたとき週の何日目かを表す)
BC
西暦
AD
AD
西暦
AD
HH
01
01
HH24
13
13
AM
午後
PM
PM
午後
PM
MI
43
43
SS
24
24
DDTH
03RD
03RD
DDSP
THREE
THREE
DDSPTH THIRD
THIRD
fmYear Two Thousand Seven
Two Thousand Seven
fmMonth 9 月
September
fmMon
9月
Sep
fmDay
月曜日
Monday
fmDy
月
Mon
fmam
午後
pm
----------------------------- ----------------------------------(注 1) 'fmMonth DD'と書式指定すると「9 月 3」と表記される。前ゼロなし。
それ以外だと「03」という表記になる。前ゼロあり。
(注 2) 'fmMonth DD'と書式指定すると「September 3」と表記される。前ゼロなし。
20
それ以外だと「03」という表記になる。前ゼロあり。
8.
Oracle SQL:
GROUP BY の要点
GROUP BY の要点
SELECT 句で，グループ関数以外の列名を指定した場合，同じ列名をすべて
GROUP BY 句で指定しなければならない。
(例)
+--------------------------------------------+
| SELECT col1, co2, COUNT(*)
|
| FROM tab
|
| GROUP BY col1, col2;
|
+--------------------------------------------+
SELECT 句で col1,col2 を指定しているので，GROUP BY 句でも
同じく col1,col2 を指定する。
GROUP BY 句で指定できるものは，列名，式である。列別名は指定できない。
ORDER BY 句で指定できるものは，列名，式，列別名である。
9.
Oracle SQL: 結合の要点( SQL:1999 構文)
--- 等価結合 --自然結合(内部結合): 2 つの表のキーの値が一致する行だけを戻す結合( tab1 ∩ tab2)
NATURAL JOIN
- 列名とデータ型が同一のすべての列を自動的に使って結合する
- 列名に表名をつける修飾は不可
- 列名が同一でデータ型が異なるとエラーになる
- 例 1: tab1 に col1 がある，tab2 に col1 がある，col1 で結合する
+--------------------------------------------------------+
| FROM tab1
|
| NATURAL JOIN tab2
|
+--------------------------------------------------------+
- 例 1: WHERE 句による実現
+--------------------------------------------------------+
| FROM tab1, tab2
|
| WHERE tab1.col1=tab2.col1
|
+--------------------------------------------------------+
- 例 2: tab1 に col1 と col2 がある，tab2 に col1 と col2 がある，col1 と coll2 で結合する
+--------------------------------------------------------+
| FROM tab1
|
| NATURAL JOIN tab2
|
+--------------------------------------------------------+
- 例 2: WHERE 句による実現
+--------------------------------------------------------+
| FROM tab1, tab2
|
| WHERE tab1.col1=tab2.col1 AND tab1.col2=tab2.col2
|
+--------------------------------------------------------+
JOIN ... USING
- 列名とデータ型が同一の列を指定して結合する
21
- 列名に表名をつける修飾は不可
- 例: tab1 に col1 と col2 がある，tab2 に col1 と col2 がある，col1 だけで結合したい
+--------------------------------------------------------+
| FROM tab1
|
| JOIN tab2 USING ( col1 )
|
+--------------------------------------------------------+
- 例: WHERE 句による実現
+--------------------------------------------------------+
| FROM tab1, tab2
|
| WHERE tab1.col1=tab2.col1
|
+--------------------------------------------------------+
JOIN ... ON
- 任意の条件で結合する
- 例: tab1 に xxx がある，tab2 に yyy がある，xxx と yyy で結合する
+--------------------------------------------------------+
| FROM tab1
|
| JOIN tab2 ON ( tab1.xxx=tab2.yyy )
|
+--------------------------------------------------------+
- 例: WHERE 句による実現
+--------------------------------------------------------+
| FROM tab1, tab2
|
| WHERE tab1.xxx=tab2.yyy
|
+--------------------------------------------------------+
JOIN ... ON ... JOIN ... ON
- 3 つの表の結合
- 左から順に結合する
- 例: tab1 に aaa と bbb がある，tab2 に xxx がある，tab3 に yyy がある，
「aaa と xxx」および「bbb と yyy」で結合する
+--------------------------------------------------------+
| FROM tab1
|
| JOIN tab2 ON ( tab1.aaa=tab2.xxx )
|
| JOIN tab3 ON ( tab1.bbb=tab3.yyy )
|
+--------------------------------------------------------+
- WHERE 句による実現
+--------------------------------------------------------+
| FROM tab1, tab2, tab3
|
| WHERE tab1.aaa=tab2.xxx AND tab1.bbb=tab3.yyy
|
+--------------------------------------------------------+
左外部結合: 2 つの表の「キーの値が一致する行(内部結合)」＋「不一致の左表の行」を戻す結合( tab1 )
LEFT OUTER JOIN
- 例: tab1 に xxx がある，tab2 に yyy がある，xxx と yyy で結合する，不一致の xxx を残す
+--------------------------------------------------------+
| FROM tab1
|
| LEFT OUTER JOIN tab2 ON ( tab1.xxx=tab2.yyy )
|
+--------------------------------------------------------+
右外部結合: 2 つの表の「キーの値が一致する行(内部結合)」＋「不一致の右表の行」を戻す結合( tab2 )
RIGHT OUTER JOIN
- 例: tab1 に xxx がある，tab2 に yyy がある，xxx と yyy で結合する，不一致の yyy を残す
+--------------------------------------------------------+
| FROM tab1
|
22
| RIGHT OUTER JOIN tab2 ON ( tab1.xxx=tab2.yyy )
|
+--------------------------------------------------------+
完全外部結合: 2 つの表の「キーの値が一致する行(内部結合)」
＋「不一致の左表の行」＋「不一致の右表の行」を戻す結合( tab1 ∪ tab2)
FULL OUTER JOIN
- 例: tab1 に xxx がある，tab2 に yyy がある，xxx と yyy で結合する，不一致の xxx と yyy を残す
+--------------------------------------------------------+
| FROM tab1
|
| FULL OUTER JOIN tab2 ON ( tab1.xxx=tab2.yyy )
|
+--------------------------------------------------------+
--- 自己結合: 表へ表自体を結合する ( tab1 ∩ tab1 ) --JOIN ... ON
- 例: tab に aaa と bbb がある，aaa と bbb で結合する
+--------------------------------------------------------+
| FROM tab1 u
|
| JOIN tab1 v ON ( u.aaa=v.bbb )
|
+--------------------------------------------------------+
--- クロス結合: デカルト積(カルテシアン積，直積)を戻す ( tab1 × tab2 ) --CROSS JOIN
- 例: tab1 と tab2 がある，デカルト積を作りたい
+--------------------------------------------------------+
| FROM tab1 u
|
| JOIN tab1 v ON ( u.aaa=v.bbb )
|
+--------------------------------------------------------+
--- 非等価結合 --JOIN ... ON
- 例: tab1 に aaa がある，tab2 に xxx と yyy がある，xxx<=aaa<=yyyy を満たす結合をしたい
+----------------------------------------------------------+
| FROM tab1
|
| JOIN tab2 ON ( tab1.aaa BETWEEN tab2.xxx AND tab2.yyy ) |
+-----------------------------------------------------------+
10.
Oracle SQL: 副問合せの要点(Subquery)
副問合せが使える SQL 句
- WHERE 句
- HAVING 句
- FROM 句
WHERE 句での単一行副問合せ
- 使用できる単一行演算子(比較演算子)
>
>=
=
<
<=
<>
23
- 例 「Abel さんの salary より多い salary の人」
+---------------------------------------------------+
FROM employees
WHERE salary > ( SELECT salary FROM employees
WHERE last_name = 'Abel'
)
+---------------------------------------------------+
WHERE 句での複数行副問合せ
- 使用できる複数行演算子(比較演算子)
IN (副問合せ) .... 複数の値のどれかと等しい
ANY (副問合せ) ... 複数の値の少なくともどれか１つ (SOME を用いても同じ)
ALL (副問合せ) ... 複数の値のどれでもすべて
(注) ANY，ALL を限定述語という。
- 例 IN 「各部門の最低 salary に等しい salary の人」
+---------------------------------------------------+
FROM
employees
WHERE salary IN (SELECT MIN(salary)
FROM
employees
GROUP BY department_id)
+---------------------------------------------------+
- 例
ANY 「ジョブ ID が'IT_PROG'の人の salary(複数)のうち
少なくともどれか１つより少ない salary の人」
+---------------------------------------------------+
FROM
employees
WHERE salary < ANY (SELECT salary
FROM
employees
WHERE job_id = 'IT_PROG')
+---------------------------------------------------+
(注) ANY の代わりに SOME を用いてもよい。同じ意味である。
- 例
ALL 「ジョブ ID が'IT_PROG'の人の salary(複数)の
どれよりも少ない salary の人」
+---------------------------------------------------+
FROM
employees
WHERE salary < ANY (SELECT salary
FROM
employees
WHERE job_id = 'IT_PROG')
+---------------------------------------------------+
- 限定述語を用いたのと同等の表現
WHERE x < ANY (SELECT y ... )
WHERE x < (SELECT MAX(y) ... )
WHERE x > ANY (SELECT y ... )
WHERE x > (SELECT MIN(y) ... )
24
WHERE x < ALL (SELECT y ... )
WHERE x < (SELECT MIN(y) ... )
WHERE x > ALL (SELECT y ... )
WHERE x > (SELECT MAX(y) ... )
WHERE 句での副問合せにおけるグループ関数の使用
- 例 「全従業員のうちの最低 salary と等しい salary の人」
+--------------------------------------------------------+
FROM employees
WHERE salary = ( SELECT MIN(salary) FROM employees )
+--------------------------------------------------------+
HAVING 句での副問合せ
- 例 「部門 50 の最低 salary より多い最低 salary の部門」
+--------------------------------------------------------+
SELECT
department_id, MIN(salary)
FROM
employees
GROUP BY department_id
HAVING
MIN(salary) > ( SELECT MIN(salary)
FROM
employees
WHERE department_id = 50);
+--------------------------------------------------------+
- 例 「平均 salary が最低のジョブ ID」
+--------------------------------------------------------+
SELECT
job_id, AVG(salary)
FROM
employees
GROUP BY job_id
HAVING
AVG(salary) = ( SELECT MIN( AVG(salary) )
FROM
employees
GROUP BY job_id );
+--------------------------------------------------------+
FROM 句での副問合せ
- 例 「副問合せを１つの表のように扱う」
+--------------------------------------------------------+
SELECT * FROM ( SELECT first_name, last_name
FROM employees)
+--------------------------------------------------------+
(注) この例はたいした意味はない。例のための例。
11.
Oracle SQL: EXISTS / NOT EXISTS
- 意味
EXISTS (副問合せ) ....... 副問合せの結果が空集合なら false，それ以外は true を返す。
NOT EXISTS (副問合せ) ... 副問合せの結果が空集合なら true，それ以外は false を返す。
- EXISTS の基本構文
25
「x が，tab 表の expression を満たす y のどれか少なくとも
１つと比べて『比較演算子』が成立すること」
+----------------------------------------------------------+
WHERE
EXISTS (SELECT * FROM tab
WHERE expression AND ( x 比較演算子 tab.y ) )
+----------------------------------------------------------+
これは限定述語を使った次の表現に置き換えることができる。
+----------------------------------------------------------+
WHERE
x 比較演算子 ANY (SELECT y FROM tab WHERE expression)
+----------------------------------------------------------+
- EXISTS の例
「locations 表の location_id が，departments 表の部門名が'Sales'
である部門の location_id のどれか少なくとも１つと一致する」
(注) Sales 部門が立地する都市
+----------------------------------------------------------+
SELECT city, location_id
FROM
hr.locations l
WHERE EXISTS (SELECT * FROM hr.departments d
WHERE department_name = 'Sales'
AND
l.location_id = d.location_id );
+----------------------------------------------------------+
結果
------------------CITY
LOCATION_ID
------- ---------Oxford
2500
------------------- NOT EXISTS の例
「locations 表の location_id のうち，departments 表にないもの」
+------------------------------------------------------------+
SELECT city, l.location_id
FROM
hr.locations l
WHERE NOT EXISTS (SELECT * FROM hr.departments d
WHERE l.location_id = d.location_id );
+------------------------------------------------------------+
結果
-----------------------------CITY
LOCATION_ID
---------------- -----------Roma
1000
Venice
1100
Tokyo
1200
Hiroshima
1300
South Brunswick
1600
Whitehorse
1900
26
Beijing
2000
Bombay
2100
Sydney
2200
Singapore
2300
Stretford
2600
Sao Paulo
2800
Geneva
2900
Bern
3000
Utrecht
3100
Mexico City
3200
-----------------------------ガイドライン p.6-6
-
副問合せは( )で囲む。
副問合せは比較条件の右側に書く。
単一行副問合せの構文のとき，複数行が返るとエラーになる。
副問合せの結果，行がなければ NULL が返る。
NULL が返ったとき，>=<による比較演算の結果は NULL である。
NULL かどうかを判断するのは，IS NULL 演算子。
NULL が返ったとき，AND，OR，NOT による論理演算は次のルールに従う。
AND | 真 偽 NULL
----+-------------真 | 真 偽 NULL
偽 | 偽 偽 偽
NULL| NULL 偽 NULL
12.
OR | 真 偽 NULL
----+--------------真 | 真 真
真
偽 | 真 偽 NULL
NULL| NULL NULL NULL
NOT |
----+----真 | 偽
偽 | 真
NULL| NULL
Oracle SQL: 集合演算子
次の 2 種類の従業員の集合を考える。
a={salary>=12000 の従業員}
b={1995-01-01 より前に採用された従業員}
これを表示するには次のようにすればよい。
--------------------------------------------------------------------------------------------SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE salary>=12000 OR hire_date < TO_DATE('1995-01-01','YYYY-MM-DD')
ORDER BY a, b DESC;
--------------------------------------------------------------------------------------------結果は次のようになる。
-----------------------------------------LAST_NAME SALARY HIRE_DAT A
B
--------- ------ -------- ---- -------Russell
14000 96-10-01 Good
27
Hartstein 13000 96-02-17 Good
Partners
13500 97-01-05 Good
Errazuriz 12000 97-03-10 Good
Greenberg 12000 94-08-17 Good Senior
De Haan
17000 93-01-13 Good Senior
King
24000 87-06-17 Good Senior
Kochhar
17000 89-09-21 Good Senior
Higgins
12000 94-06-07 Good Senior
Ernst
6000 91-05-21
Senior
Hunold
9000 90-01-03
Senior
Whalen
4400 87-09-17
Senior
Gietz
8300 94-06-07
Senior
Mavris
6500 94-06-07
Senior
Baer
10000 94-06-07
Senior
Faviet
9000 94-08-16
Senior
Raphaely
11000 94-12-07
Senior
-----------------------------------------17 行が選択されました。
これをもとに，集合演算子 UNION，INTERSECT，MINUS の動作を見ることにする。
集合演算子を使用するとき，SELECT 句の項目数とデータ型はすべて一致していなければならない。
UNION
a∪b
--------------------------------------------------------------------------------------------SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE salary>=12000
UNION
SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE hire_date < TO_DATE('1995-01-01','YYYY-MM-DD')
ORDER BY a, b DESC;
--------------------------------------------------------------------------------------------INTERSECT
a∩b
--------------------------------------------------------------------------------------------SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE salary>=12000
INTERSECT
SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
28
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE hire_date < TO_DATE('1995-01-01','YYYY-MM-DD')
ORDER BY a, b DESC;
--------------------------------------------------------------------------------------------MINUS
a={salary>=12000 の従業員} - b={1995-01-01 より前に採用された従業員}
--------------------------------------------------------------------------------------------SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE salary>=12000
MINUS
SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE hire_date < TO_DATE('1995-01-01','YYYY-MM-DD')
ORDER BY a, b DESC;
--------------------------------------------------------------------------------------------MINUS
b={1995-01-01 より前に採用された従業員} - a={salary>=12000 の従業員}
--------------------------------------------------------------------------------------------SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE hire_date < TO_DATE('1995-01-01','YYYY-MM-DD')
MINUS
SELECT last_name, salary, hire_date,
(CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a,
(CASE WHEN hire_date < TO_DATE('1995-01-01','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b
FROM
hr.employees
WHERE salary>=12000
ORDER BY a, b DESC;
---------------------------------------------------------------------------------------------
13.
Oracle SQL: データ操作言語(DML)
(ROLLBACK 可能)
行の挿入( 列名を指定する方式 ) p.8-6
-----------------------------------------------------------------------------INSERT INTO 表名 (列名, 列名, ...)
VALUES (値, 値, NULL, NULL, ...);
-----------------------------------------------------------------------------行の挿入( 列名を省略する方式 )
p.8-7
29
-----------------------------------------------------------------------------INSERT INTO 表名
VALUES (値, 値, NULL, NULL, ...);
-----------------------------------------------------------------------------行の挿入( 日付などの入力 ) p.8-8, 8-9
-----------------------------------------------------------------------------INSERT INTO 表名
VALUES (値, SYSDATE, TO_DATE(...), ...);
-----------------------------------------------------------------------------行の挿入( 副問合せによる ) p.8-11
-----------------------------------------------------------------------------INSERT INTO 表名 (列名, 列名, ...)
SELECT 列名, 列名, ...
FROM
表名
WHERE 条件式;
-----------------------------------------------------------------------------データの更新 p.8-14
-----------------------------------------------------------------------------UPDATE 表名
SET
列名 = 値, 列名 = 値, ...
WHERE 条件式;
-----------------------------------------------------------------------------データの更新( 副問合せによる ) p.8-15
-----------------------------------------------------------------------------UPDATE 表名
SET
列名 = (SELECT ....),
列名 = (SELECT ....)
WHERE 条件式;
-----------------------------------------------------------------------------行の削除 p.8-19
-----------------------------------------------------------------------------DELETE FROM 表名
WHERE 条件式;
-----------------------------------------------------------------------------行の削除( WHERE を省略するとすべての行が削除される。表構造は変更ナシ。 ) p.8-19
-----------------------------------------------------------------------------DELETE FROM 表名;
-----------------------------------------------------------------------------行の削除( 副問合せによる ) p.8-20
-----------------------------------------------------------------------------DELETE FROM 表名
WHERE 列名 = (SELECT ....);
------------------------------------------------------------------------------
30
トランザクション処理 p.8-27
-----------------------------------------------------------------------------COMMIT
処理を確定する
ROLLBACK
処理の前に戻る
SAVEPOINT xxxx
セーブポイントを設定する
ROLLBACK TO SAVEPOINT xxxx
セーブポイント xxxx に戻る
------------------------------------------------------------------------------
14.
Oracle SQL: データ定義言語(DDL)
すべての行の削除( ROLLBACK 不可。表構造は変更ナシ。) p.8-21
-----------------------------------------------------------------------------TRUNCATE TABLE 表名;
-----------------------------------------------------------------------------表作成( 列レベルで制約を指定する方式 ) p.9-5, -20, -29
-----------------------------------------------------------------------------CREATE TABLE 表名 (
列名 データ型 [制約],
列名 データ型 [制約],
...
);
(例)
CREATE TABLE tabsample (
col1 NUMBER(6)
NOT NULL PRIMARY KEY
, 主キー制約
col2 CHAR(6)
NOT NULL UNIQUE
, 一意制約
col3 VARCHAR2(20) NOT NULL
, NULL 制約
col4 NUMBER(6)
NOT NULL REFERENCES ftab(fcol) , 外部キー制約
col5 NUMBER(6)
NOT NULL CHECK( col5 > 0 )
, チェック制約
);
-----------------------------------------------------------------------------表作成( 表レベルで制約を指定する方式 ) p.9-5, -20, -29
-----------------------------------------------------------------------------CREATE TABLE 表名 (
列名 データ型,
列名 データ型,
...
制約(列名,列名),
制約(列名),
...
);
(例)
CREATE TABLE tabsample (
col1 NUMBER(6)
NOT NULL
col2 CHAR(6)
NOT NULL UNIQUE
col3 VARCHAR2(20) NOT NULL
,
,
,
31
col4 NUMBER(6)
NOT NULL
,
col5 NUMBER(6)
NOT NULL CHECK( col5 > 0 ) ,
PRIMARY KEY( col1, col2 ),
2 つの列からなる主キーを指定
FOREIGN KEY( col4) REFERENCES ftab(fcol)
外部キーの指定
);
-----------------------------------------------------------------------------データ整合性制約 p.9-17
-----------------------------------------------------------------------------NOT NULL
NOT NULL 制約。列に NULL 値を含めないように指定します。
UNIQUE
一意制約。表のすべての行で値が一意である必要がある列または列の組合せ
を指定します。
PRIMARY KEY 主キー制約。表の各行を一意に識別します。
FOREIGN KEY 外部キー制約。列と参照表の列との間に外部キー関係を確立し、適用します。
CHECK
チェック制約。TRUE が必要な条件を指定します。
-----------------------------------------------------------------------------データ型 p.9-9
-----------------------------------------------------------------------------データ型
説明
-------------- -------------------------------------------------------------VARCHAR2(size) 可変長の文字データ（size に最大サイズを指定する必要があります。
最小サイズは 1、最大サイズは 4,000 です。
）
CHAR [(size)]
長さが size バイトの固定長文字データ（デフォルトおよび最小の
サイズは 1、最大サイズは 2,000 です。）
NUMBER [(p,s)] 精度が p、位取りが s の数値データ（精度とは十進法の合計桁数、位取り
とは小数点以下の桁数です。精度は 1～38 の範囲、
位取りは-84～127 の範囲です。
）
DATE
紀元前 4712 年 1 月 1 日から西暦 9999 年 12 月 31 日までの範囲の最も近い秒の
日付と時刻の値
LONG
可変長の文字データ（最大 2GB）
CLOB
文字データ（最大 4GB）
RAW(size)
長さ size の RAW バイナリ・データ（最大サイズを指定する必要があります。
最大サイズは 2,000 です。）
LONG RAW
可変長の RAW バイナリ・データ（最大 2GB）
BLOB
バイナリ・データ（最大 4GB）
BFILE
外部ファイルに格納されるバイナリ・データ（最大 4GB）
ROWID
表内の行の一意のアドレスを表す BASE64 文字列
-----------------------------------------------------------------------------表作成( 副問合せによる ) p.9-33
-----------------------------------------------------------------------------CREATE TABLE dept80
AS
SELECT employee_id, last_name, salary*12 ANNSAL, hire_date
FROM
employees
WHERE department_id = 80;
-----------------------------------------------------------------------------表の変更( 列追加，列変更，列削除，デフォルト値設定 )
32
p.9-34
-----------------------------------------------------------------------------ALTER TABLE 表名
....
-----------------------------------------------------------------------------表の削除( ROLLBACK 不可。表構造削除。 ) p.9-35
-----------------------------------------------------------------------------DROP TABLE 表名;
------------------------------------------------------------------------------
15.
Oracle SQL: データベース・オブジェクトの作成と利用
p.10-3
ビューの作成 p.10-7
-----------------------------------------------------------------------------CREATE VIEW ビュー名
AS 副問合せ;
(例)
CREATE VIEW salvu50
AS
SELECT employee_id ID_NUMBER, last_name NAME, salary*12 ANN_SALARY
FROM
employees
WHERE department_id = 50;
-----------------------------------------------------------------------------順序の作成 p.10-22
-----------------------------------------------------------------------------CREATE SEQUENCE 順序名
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
(注)
INCREMENT BY n
START WITH n
MAXVALUE n
NOMAXVALUE
MINVALUE n
NOMINVALUE
順序番号の間隔を指定。n は整数
（省略すると、順序は 1 ずつ増加する）
生成される最初の順序番号を指定
（省略すると、順序は 1 から開始される）
順序が生成できる最大値を指定
昇順の場合に最大値 10^27 を、降順の場合に-1 を指定
（デフォルト・オプション）
順序の最小値を指定
昇順の場合に最小値-1 を、降順の場合に 10^26 を指定
（デフォルト・オプション）
(例)
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
33
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
-----------------------------------------------------------------------------順序の使用 p.10-26
-----------------------------------------------------------------------------(例) NEXTVAL で順序を生成して入力する
INSERT INTO departments( department_id, department_name, location_id )
VALUES ( dept_deptid_seq.NEXTVAL, 'Support', 2500);
(例) CURRVAL で現在の順序を参照する
SELECT dept_deptid_seq.CURRVAL
FROM
dual;
-----------------------------------------------------------------------------順序の変更 p.10-28
-----------------------------------------------------------------------------ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
-----------------------------------------------------------------------------順序の削除 p.10-29
-----------------------------------------------------------------------------DROP SEQUENCE dept_deptid_seq;
-----------------------------------------------------------------------------索引の作成 p.10-33
-----------------------------------------------------------------------------CREATE INDEX 索引名
ON 表名( 列名, 列名, ...);
(例)
CREATE INDEX emp_last_name_idx
ON employees(last_name);
-----------------------------------------------------------------------------索引の削除
-----------------------------------------------------------------------------DROP INDEX 索引名;
-----------------------------------------------------------------------------シノニム(別名)の作成 p.10-37
-----------------------------------------------------------------------------CREATE [PUBLIC] SYNONYM シノニム名
FOR オブジェクト名;
34
(例)
CREATE SYNONYM d_sum
FOR dept_sum_vu;
-----------------------------------------------------------------------------シノニムの削除
-----------------------------------------------------------------------------DROP SYNONYM d_sum;
------------------------------------------------------------------------------
16.
Oracle SQL: ディクショナリの利用
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
DESCRIBE
dictionary
user_objects
user_tables
user_tab_columns
user_constraints
user_cons_columns
user_views
user_sequences
user_synonyms
ユーザ・オブジェクト
表情報
列情報
制約情報
列制約情報
ビュー情報
順序情報
シノニム情報
以上
35