Microsoft Word - SQL02-MS-Access.docx

Transcription

1 MS-Access の SQL クエリと SQL 基 本 構 文 ( 付. Oracle SQL) 1. はじめに MS-Access はリレーショナル データベース マネジメント システム(RDBMS)である ここでは,MS-Access を 使 って SQL(Structured Query Language, 構 造 化 照 会 言 語 )の 利 用 を 学 ぼう MS-Access が 扱 うデータベースはファイルとしては1つである 拡 張 子 には"accdb"がついている ファイルは1つだ がその 中 には, 複 数 のテーブルや, 複 数 のクエリ(query)や,その 他 の 管 理 操 作 情 報 がいっしょに 含 まれている クエリとは,データの 照 会 機 能 ( 検 索 機 能 )のことである 2とおりある (1) 照 会 画 面 クエリ: 照 会 画 面 からキーワードと 検 索 条 件 などを 入 力 してデータの 照 会 を 行 う Web ページや 図 書 の 検 索 画 面 などでおなじみのもの (2) SQL クエリ: SQL スクリプトを 直 接 書 いてデータ 照 会 を 行 うもの プログラミングにやや 近 い これらは 別 々のものではなく,ユーザからみたインタフェースの 違 いに 他 ならない 照 会 画 面 は,SQL に 送 る 検 索 文 字 や 条 件 を 所 定 のフォーマットで 入 力 できるようにした GUI である 2. MS-Access の 起 動 とサンプルデータの 概 要 (1) MS-Access のアイコンをダブルクリックして 起 動 する (2) Office ボタン 開 く サンプル データベース SQL03-lessons.accdb を 指 定 して 開 く (3) テーブル パネルが 表 示 される ここにはデータベースに 含 まれているテーブルが 一 覧 表 で 示 される (4) 見 たいテーブルをダブルクリックして 開 く 例 えばテーブル A1 は 次 のようになる 1

2 3. SQL クエリの 作 成 と 使 用 (1) 作 成 新 しいオブジェクト: クエリ (2) テーブルの 表 示 ウィンドウ 閉 じる (3) クエリツール デザイン SQL ビュー (4) SQL 入 力 画 面 になる SQL 文 を 入 力 する 2

3 (5) クエリツール デザイン 実 行 実 行 結 果 が 表 示 される (6) クエリ1 タブを 右 クリック 上 書 き 保 存 (7) 名 前 を 付 けて 保 存 例 題 01 と 名 前 を 付 ける OK (8) クエリ パネルにオブジェクトとして 追 加 される ダブルクリックすれば 開 く タブを 右 クリック 閉 じる (9) SQL 文 を 編 集 するには, 例 題 01 ダブルクリック ホーム 表 示 SQL ビュー (10) SQL 画 面 からテーブル 表 示 にするには, ホーム 表 示 テーブルシートビュー 3

4 4. SQL によるデータ 照 会 の 例 題 (MS-Access) はじめに SQL(Structured Query Language, 構 造 化 照 会 言 語 )は,リレーショナル データベース を 操 作 するための 言 語 である つまり,データベースを 構 築 したり,データを 探 索 したり, データを 更 新 したりするのに 使 用 する 言 語 である ここでは,SQL によるデータ 探 索 の 分 野 を 学 ぶことにしよう リレーショナル データベースからデータを 探 索 するための 理 論 的 基 礎 を 与 えているのは, 集 合 演 算 としてのリレーショナル 代 数 である そしてリレーショナル 代 数 を 実 現 すること のできる 言 語 の1つが,SQL である しかしながら,SQL はリレーショナル 代 数 の8つの 演 算 をそれぞれ 直 接 に 実 現 するようなコ マンド 群 として 存 在 するわけではない つまり1 対 1に 対 応 するわけではない リレーショ ナル 代 数 を SQL で 実 現 するには, SQL の 構 文 にしたがってプログラミングをする 必 要 がある 以 下 は,SQL プログラミングの 事 例 を 示 したものである サンプルデータを 用 いて 練 習 でき るようになっている SQL の 基 本 構 文 SQL ステートメントによる 問 合 せ(データ 照 会 )の 基 本 構 文 は, 次 のような 形 式 である なお[ ] 内 は 必 要 に 応 じて 指 定 する 句 である なくてもよい SQL ステートメント 意 味 SELECT 列 名 リスト xxx 列 を 抜 き 出 して 表 示 しなさい FROM テーブル 名 リスト xxx テーブルから 探 索 して [WHERE 探 索 条 件 ] 探 索 条 件 が 真 (true)になる 行 を 選 択 して [GROUP BY 列 名 リスト] xxx 列 の 値 でグルーピングして [HAVING 探 索 条 件 ] 探 索 条 件 が 真 (true)であるグループを 選 択 して これらは SQL ステートメントの 基 本 要 素 で, SELECT 句,FROM 句,WHERE 句,GROUP BY 句, HAVING 句 という SQL による 問 合 せの 例 題 以 下 の 例 題 は,サンプル 用 のデータベース ファイルで 実 習 できる 例 題 01 すべての 列 を 表 示 する SELECT * FROM C1; テーブル C1 から,すべての 行 について,すべての 列 を 抜 き 出 して 表 示 する *は,すべての 列 を 意 味 する 総 称 文 字 である 文 の 終 わりには;(セミコロン)をつける 文 の 途 中 に 挿 入 する 空 白 は, 半 角 の 空 白 でなければならない 4

5 例 題 02 列 を 抜 き 出 して 表 示 する SELECT 顧 客, 担 当 社 員 FROM B1; テーブル B1 から,すべての 行 について, 列 顧 客 と 担 当 社 員 を 抜 き 出 して 表 示 する 列 名 が 複 数 あるときは,,(カンマ)で 区 切 る 例 題 03 マルチ 集 合 SELECT スーパー FROM C1; テーブル C1 から,すべての 行 について, 列 スーパー を 抜 き 出 して 表 示 する 列 スーパー の 値 であるスーパーの 名 称 が 表 示 されるが, このときいくつかの 行 で 値 が 重 複 していても, そのまま 重 複 した 行 のまま 表 示 される このように 重 複 した 値 をとる 集 合 をマルチ 集 合 (multi-set)という なお, 重 複 しているというのは, 行 を 集 合 の 元 とみて, 2つ 以 上 の 行 の 間 ですべての 列 の 値 が 等 しいことをいう 1つでも 値 の 違 う 列 があれば, それらの 行 は 重 複 しているとはいわない 例 題 04 DISTINCT による 修 飾 SELECT DISTINCT スーパー FROM C1; テーブル C1 から,すべての 行 について, 列 スーパー を 抜 き 出 して 表 示 する このとき 重 複 のないスーパー 名 の 集 合 として 抜 き 出 す SELECT 句 で DISTINCT という 修 飾 語 をつけると,マルチ 集 合 でなくなる 例 題 05 文 字 定 数 SELECT 商 品, 単 価 円, ' 円 ' FROM B2; テーブル B1 から,すべての 行 について, 列 商 品 と 単 価 円 を 抜 き 出 して 表 示 する このとき, 文 字 円 を 表 示 する 列 を 追 加 する このように 列 の 一 部 として, 一 定 の 文 字 列 ( 文 字 定 数 )を 表 示 させるには, 文 字 を'(single quotation)ではさんで, 列 リストに 加 える このとき 列 名 は 自 動 的 に 設 定 される 5

6 自 動 的 に 設 定 される 列 名 に 換 えて, 列 名 を 指 定 したいときは, 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 句 の 探 索 条 件 は,1つ 以 上 の 述 語 ( 条 件 式 のこと)を, AND,OR,NOT で 組 み 合 せてできた 論 理 式 である 6

7 述 語 ( 条 件 式 )は, 探 索 条 件 を 構 成 する 最 小 の 単 位 で, 真 (true)か 偽 (false)か 不 定 (Null,ナル)のどれかの 値 をとる 1つ 以 上 の 述 語 が AND,OR,NOT で 結 合 された 探 索 条 件 全 体 は, 論 理 式 として 評 価 され 最 終 的 に 真 か 偽 か 不 定 のどれかの 値 をとる この 例 では, 探 索 条 件 は 通 勤 手 段 = ' 鉄 道 ' という1つの 述 語 だけからできている この 述 語 は, ある 行 の 列 通 勤 手 段 の 値 が' 鉄 道 'という 値 に 等 しいとき, 真 (true)の 値 をとる 2つの 間 の 大 小 関 係 を 比 較 する 述 語 を 比 較 述 語 という 述 語 の 種 類 は 他 にもある 比 較 述 語 で 使 用 される=のような 演 算 子 を 比 較 演 算 子 という 比 較 演 算 子 には 次 のものがある 比 較 演 算 子 比 較 述 語 意 味 (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 通 勤 手 当 円 >= AND 通 勤 手 当 円 <= ; テーブル 通 勤 手 当 早 見 表 から, 列 通 勤 手 当 円 の 値 が 以 上 でかつ 以 下 の 行 を 選 択 して, 列 最 寄 り 駅 と 通 勤 手 当 円 と 文 字 定 数 ' 円 'を 表 示 する この 例 では,2つの 述 語 ( 条 件 式 )が AND で 結 合 して 探 索 条 件 になっている AND の 書 き 方 は, 述 語 1 AND 述 語 2 である 述 語 1 が 真 でかつ 述 語 2 が 真 のとき, 真 の 値 をとる OR の 書 き 方 は, 述 語 1 OR 述 語 2 である 述 語 1 が 真 かまたは 述 語 2 が 真 のとき, 真 の 値 をとる NOT の 書 き 方 は, NOT( 述 語 1) 7

8 である 述 語 1 が 真 なら 偽, 述 語 1 が 偽 なら 真 の 値 をとる AND,OR,NOT による 論 理 演 算 の 値 AND 真 偽 不 定 OR 真 偽 不 定 NOT 真 真 偽 不 定 真 真 真 真 真 偽 偽 偽 偽 偽 偽 真 偽 不 定 偽 真 不 定 不 定 偽 不 定 不 定 真 不 定 不 定 不 定 不 定 例 題 09 BETWEEN 述 語 SELECT 最 寄 り 駅, 通 勤 手 当 円, ' 円 ' AS 単 位 FROM 通 勤 手 当 早 見 表 WHERE 通 勤 手 当 円 BETWEEN AND ; これは, 上 の 例 題 ex08 の WHERE 句 を BETWEEN 述 語 を 用 いて 書 き 換 えたものである 通 勤 手 当 円 BETWEEN AND の 部 分 を BETWEEN 述 語 という BETWEEN 述 語 は 次 のような 書 き 方 をする 値 式 1 BETWEEN 値 式 2 AND 値 式 3 値 式 には, 列 名, 数 値,または 計 算 式 がくる BETWEEN 述 語 は, 値 式 1 が 値 式 2 以 上 でかつ 値 式 3 以 下 のとき 真 の 値 をとる それ 以 外 は 偽 の 値 をとる BETWEEN 述 語 の 中 の AND は, 述 語 を 結 合 するときの 論 理 演 算 子 としての AND とは 違 って, BETWEEN 述 語 の 一 部 である また 通 勤 手 当 円 NOT BETWEEN AND と 書 くと, 否 定 形 の BETWEEN 述 語 になる 通 勤 手 当 円 が 以 上 かつ 以 下 でないとき, 真 の 値 をとる 否 定 形 の 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

9 列 社 員 名 を 表 示 する キーワード LIKE で 構 成 される 述 語 を LIKE 述 語 という LIKE 述 語 は, 次 のような 書 き 方 をして 文 字 列 のパターン マッチングを 行 う 列 名 LIKE ' 文 字 パターン' 列 がとる 値 ( 文 字 列 )が, ある 文 字 パターンに 一 致 しているとき 真 の 値 をとる 文 字 パターンでは, 次 のような 特 殊 文 字 を 使 用 して 特 定 の 文 字 パターンを 意 味 することができる ( 注 )JIS 規 格 SQL はこれとは 全 く 異 なる 特 殊 文 字 意 味 ? 任 意 の 1 文 字 * 任 意 の 数 の 文 字 # 任 意 の 1 文 字 の 数 字 (0-9) [charlist] 文 字 リスト charlist に 指 定 した 文 字 の 中 の 1 文 字 ( 例 )[a-z]とするとアルファベット 小 文 字 のどれか 1 つを 意 味 する [ 高 鷹 隆 ]とすると 高, 鷹, 隆 のどれか 1 つを 意 味 する [!charlist] 文 字 リスト 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

10 テーブル 通 勤 手 当 早 見 表 から, 列 住 所 CODE の 値 が,'J108'か'J116'か'J123'のどれかをとる 行 を 選 択 して, 列 住 所 CODE と 最 寄 り 駅 を 表 示 する IN 述 語 は, 離 散 的 に 指 定 した 複 数 の 値 のうちどれかに 一 致 するものを 選 ぶとき 使 用 する IN 述 語 の 書 き 方 値 式 IN ( 値 1, 値 2,...) 値 式 とは, 列 名 か 計 算 式 である 値 1 や 値 2 とは, 数 値 か 文 字 定 数 である IN 述 語 は, 値 式 が, 値 1, 値 2,...のどれかに 一 致 するとき 真 の 値 をとる ( 例 ) 住 所 CODE IN ( 'J108', 'J116', 'J123') 通 勤 手 当 IN (100000, , 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

11 社 員 名 のうち 重 複 を 除 いた 名 称 の 件 数 が 返 る MS-Access ではこの 表 現 は 使 用 できない 例 題 14 GROUP BY 句 によるグルーピング SELECT 売 上 商 品, SUM( 数 量 ) AS 数 量 合 計, COUNT(*) AS 売 上 回 数 FROM B1 GROUP BY 売 上 商 品 ; テーブル B1 から, 売 上 商 品 の 名 称 でグルーピングして 数 量 合 計 と 売 上 回 数 とを 求 める GROUP BY 句 は, 指 定 した 列 の 値 が 同 じ 行 どうしを 集 めてグルーピングする ここでは 売 上 商 品 の 列 の 商 品 名 でグルーピングしている グルーピングされた 行 全 体 は, あたかも1つの 行 であるかのように,いくつかの 属 性 を 持 つことになる その 場 合 の 属 性 とは, もとの 行 の 列 の 値 か,またはそれから 集 合 関 数 で 計 算 され 得 るもので, グループの 属 性 として 意 味 のあるものである SELECT 句 の 列 リストには,そうしたグループの 属 性 が 並 ぶ 上 の 例 のように 列 売 上 商 品 の 場 合 は, グループの 属 性 としてグループ 内 で 共 通 している 商 品 名 が 表 示 される 数 量 の 合 計 もグループの 属 性 として 意 味 がある しかし, 単 に 列 数 量 を 指 定 してもグループとしての 属 性 にならないので 実 行 できない 例 題 15 HAVING 句 によるグループの 選 択 SELECT 売 上 商 品, SUM( 数 量 ) AS 数 量 合 計, COUNT(*) AS 売 上 回 数 FROM B1 GROUP BY 売 上 商 品 HAVING COUNT(*) >= 2; テーブル B1 から, 売 上 商 品 の 名 称 でグルーピングして, 売 上 回 数 が 2 以 上 のグループを 選 択 して, 数 量 合 計 と 売 上 回 数 とを 求 める HAVING 句 は,GROUP BY 句 でできるグループのうちから, 特 定 のグループを 選 択 するための 探 索 条 件 を 指 定 する このときの 探 索 条 件 の 書 き 方 は,WHERE 句 の 探 索 条 件 の 書 き 方 と 同 じである 例 題 16 結 合 (join):2つのテーブルを 結 合 する(1) SELECT 社 員 通 勤 表. 社 員 名, 通 勤 手 当 早 見 表. 最 寄 り 駅, 通 勤 手 当 早 見 表. 通 勤 手 当 円 FROM 社 員 通 勤 表, 通 勤 手 当 早 見 表 WHERE 社 員 通 勤 表. 住 所 CODE = 通 勤 手 当 早 見 表. 住 所 CODE; テーブル 社 員 通 勤 表 とテーブル 通 勤 手 当 早 見 表 とから, 2つのテーブルの 間 で 住 所 CODE が 一 致 する 行 を 選 択 して, 11

12 列 社 員 名, 最 寄 り 駅, 通 勤 手 当 円 を 表 示 する このように2つ 以 上 のテーブルから 行 と 列 とを 選 択 して 1つのリレーションを 作 成 することを 結 合 という リレーショナル データベースの 最 も 特 徴 的 な 利 用 方 法 である SELECT 句 の 列 リストで 列 名 を 書 くときは, 社 員 通 勤 表. 社 員 名 のように, 列 名 社 員 名 の 前 にテーブル 名 社 員 通 勤 表 をつけて 修 飾 する 間 には 区 切 りの.(ピリオド)を 挿 入 する これは, 複 数 のテーブルを 参 照 するので, どのテーブルの 列 を 指 すのかはっきりさせるためである FROM 句 に 書 くテーブル 名 は,(カンマ)で 区 切 る WHERE 句 の 探 索 条 件 のところでも, 列 名 の 前 にはテーブル 名 をつけて 修 飾 する この 結 合 処 理 の 内 部 的 な 手 順 は,リレーショナル 代 数 の 表 現 でいうと, 直 積, 選 択, 射 影 をやっていることになる つまり,まずテーブル 社 員 通 勤 表 と 通 勤 手 当 早 見 表 との 直 積 を 求 める つまり2つのテーブルの 間 のすべての 行 の 組 合 わせを 作 成 し, それぞれの 組 合 わせごとに 列 を 横 方 向 につなげて1つの 行 にする できた 直 積 から, 探 索 条 件 社 員 通 勤 表. 住 所 CODE = 通 勤 手 当 早 見 表. 住 所 CODE を 満 たす 行 を 選 択 する 選 択 した 行 から, 必 要 な 列 を 抜 き 取 る こうしたことをやっている 例 題 17 結 合 (join):2つのテーブルを 結 合 する(2) SELECT B1. 顧 客, B1. 売 上 商 品, B1. 数 量, B2. 単 価 円, B1. 数 量 * B2. 単 価 円 AS 売 上 高 FROM B1, B2 WHERE B1. 売 上 商 品 = B2. 商 品 ; 2つのテーブル B1 と B2 とから, 売 上 商 品 と 商 品 に 記 された 商 品 名 が 等 しい 行 を 選 択 して, 列 顧 客, 売 上 商 品, 数 量, 単 価 円 を 表 示 するとともに, さらに 数 量 単 価 円 で 売 上 高 を 計 算 して それに 売 上 高 という 列 名 をつけて 表 示 する 例 題 18 直 積 (Cartesian product):2つのテーブルのペア SELECT B1.*, B2.* FROM B1, B2; テーブル B1 とテーブル B2 とから,すべての 行 を 対 象 に 直 積 を 作 成 し, すべての 列 を 表 示 する B1.*という 表 現 は,テーブル B1 のすべての 列 という 意 味 である 例 題 19 相 関 名 ( 表 別 名 )によるテーブルの 略 記 SELECT X. 社 員 名, 12

13 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 の 橋 本 龍 子 の 数 量 と, 同 じテーブルの 亀 井 勇 の 数 量 との 差 を 計 算 して 表 示 する 相 関 名 を 使 うと, 同 一 テーブルに 異 なる 相 関 名 をつけて, あたかも 異 なるテーブルであるかのように 扱 うことができる 1つのテーブルの 中 の 異 なる 行 の 間 で 比 較 したり 計 算 したりするのに 有 効 である 例 題 21 副 問 合 せ(1) SELECT 最 寄 り 駅, 通 勤 手 当 円 FROM 通 勤 手 当 早 見 表 WHERE 通 勤 手 当 円 >= (SELECT 通 勤 手 当 円 FROM 通 勤 手 当 早 見 表 WHERE 最 寄 り 駅 = ' 調 布 ' ); テーブル 通 勤 手 当 早 見 表 の 中 から, 最 寄 り 駅 が 調 布 のときの 通 勤 手 当 と 比 べて それ 以 上 の 金 額 になる 最 寄 り 駅 を 表 示 する WHERE 句 の 探 索 条 件 で, 述 語 ( 条 件 式 )の 値 式 ( 列 名, 定 数, 計 算 式 )として 問 合 せ(SELECT...FROM...WHERE...)を 使 用 することができる これを 副 問 合 せという 副 問 合 せは, 基 本 的 に 単 一 の 列 についての 値 を 返 すような 問 合 せである 返 ってくる 値 は 複 数 あってもよいが, 単 一 列 についてのものであるという 点 がポイントである また 複 数 の 値 が 返 るときは, 述 語 ( 条 件 式 )が IN 述 語 のように, 述 語 として 整 合 のとれたものでなければならない 上 の 例 では, 最 寄 り 駅 が 調 布 のときの 通 勤 手 当 円 の 値 を1つだけ 返 している 副 問 合 せは,( )でくくる データ 処 理 的 には, 主 問 合 せでテーブルの1 行 ごとに WHERE 句 の 探 索 条 件 が 真 かどうかの 判 定 が 行 われる 点 はこれまでと 同 じである ただ 探 索 条 件 の 判 定 のステップで,そのつど 副 問 合 せが 発 生 している 13

14 例 題 22 副 問 合 せ(2) SELECT 最 寄 り 駅, 通 勤 手 当 円 FROM 通 勤 手 当 早 見 表 WHERE 住 所 CODE IN (SELECT 住 所 CODE FROM 社 員 通 勤 表 WHERE 社 員 名 IN (' 橋 本 龍 子 ',' 亀 井 勇 ') ); 橋 本 龍 子 と 亀 井 勇 の 住 所 CODE を テーブル 社 員 通 勤 表 から 副 問 合 せで 探 索 して, 探 索 した 住 所 CODE に 該 当 する 最 寄 り 駅 と 通 勤 手 当 円 を テーブル 通 勤 手 当 早 見 表 から 抜 き 出 して 表 示 する この 副 問 合 せでは, 橋 本 龍 子 と 亀 井 勇 の 住 所 CODE が2つ 返 る 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

15 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)を 求 めている 重 複 する 行 (すべての 列 の 値 が 等 しい 行 どうし) は 除 かれて1つの 行 だけが 選 択 される 例 題 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

16 AND B1. 数 量 *B2. 単 価 円 > ANY (SELECT X. 数 量 *Y. 単 価 円 FROM B1 X, B2 Y WHERE X. 売 上 商 品 =Y. 商 品 ); 少 なくとも 他 の1 人 の 社 員 よりは 大 きな 売 上 高 をあげた 社 員 を 選 択 する つまり 最 小 の 売 上 高 ではない 社 員 を 選 択 する SOME 述 語 (ANY を 用 いても 同 じ)は, a 比 較 演 算 子 ANY(SELECT b FROM c WHERE d) という 形 式 をとる a は 値 式,b は 列,c はテーブル,d は 探 索 条 件 である 副 問 合 せで 返 ってくる 値 のうち 少 なくとも1つに 対 して 比 較 演 算 が 真 (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 を 求 めている 商 は, Q=C1[スーパー]-(C1[スーパー] C2-C1)[スーパー] で 求 められる なおここで,[スーパー]とは スーパー の 列 を 取 出 す 射 影 である また, は 直 積 で, -は 差 である もっとスマートな 表 現 があるかもしれない 16

17 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

18 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

19 ンで 検 索 開 始 位 置 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( ' ', 'YYYYMMDDHH24MISS' ) (2) 米 語 書 式 の 場 合 TO_DATE( ' 文 字 列 ', ' 表 示 書 式 ', 'NLS_DATE_LANGUAGE=American' ) ( 例 ) TO_DATE( '03/OCT/ ', 'DD/MON/YYYY HH24MISS', 'NLS_DATE_LANGUAGE=American' ) 日 付 文 字 列 の 変 換 : TO_CHAR 関 数 19

20 (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 YYYY YEAR TWO THOUSAND SEVEN TWO THOUSAND SEVEN MM MONTH 9 月 SEPTEMBER MON 9 月 SEP DD 3 または 03 ( 注 1) 3 または 03 ( 注 2) DDD (1 月 1 日 を1としたとき 何 日 目 かを 表 す) DAY 月 曜 日 MONDAY DY 月 MON D 2 2 ( 日 曜 を 1 としたとき 週 の 何 日 目 かを 表 す) BC 西 暦 AD AD 西 暦 AD HH HH AM 午 後 PM PM 午 後 PM MI SS 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

21 それ 以 外 だと 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

22 - 列 名 に 表 名 をつける 修 飾 は 不 可 - 例 : 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

23 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 ) Oracle SQL: 副 問 合 せの 要 点 (Subquery) 副 問 合 せが 使 える SQL 句 - WHERE 句 - HAVING 句 - FROM 句 WHERE 句 での 単 一 行 副 問 合 せ - 使 用 できる 単 一 行 演 算 子 ( 比 較 演 算 子 ) > >= = < <= <> 23

24 - 例 Abel さんの salary より 多 い salary の 人 FROM employees WHERE salary > ( SELECT salary FROM employees WHERE last_name = 'Abel' ) WHERE 句 での 複 数 行 副 問 合 せ - 使 用 できる 複 数 行 演 算 子 ( 比 較 演 算 子 ) IN ( 副 問 合 せ)... 複 数 の 値 のどれかと 等 しい ANY ( 副 問 合 せ)... 複 数 の 値 の 少 なくともどれか1つ (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( 複 数 )のうち 少 なくともどれか1つより 少 ない 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

25 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 句 での 副 問 合 せ - 例 副 問 合 せを1つの 表 のように 扱 う SELECT * FROM ( SELECT first_name, last_name FROM employees) ( 注 ) この 例 はたいした 意 味 はない 例 のための 例 11. Oracle SQL: EXISTS / NOT EXISTS - 意 味 EXISTS ( 副 問 合 せ)... 副 問 合 せの 結 果 が 空 集 合 なら false,それ 以 外 は true を 返 す NOT EXISTS ( 副 問 合 せ)... 副 問 合 せの 結 果 が 空 集 合 なら true,それ 以 外 は false を 返 す - EXISTS の 基 本 構 文 25

26 x が,tab 表 の expression を 満 たす y のどれか 少 なくとも 1つと 比 べて 比 較 演 算 子 が 成 立 すること 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 のどれか 少 なくとも1つと 一 致 する ( 注 ) 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 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

27 Beijing 2000 Bombay 2100 Sydney 2200 Singapore 2300 Stretford 2600 Sao Paulo 2800 Geneva 2900 Bern 3000 Utrecht 3100 Mexico City ガイドライン p 副 問 合 せは( )で 囲 む - 副 問 合 せは 比 較 条 件 の 右 側 に 書 く - 単 一 行 副 問 合 せの 構 文 のとき, 複 数 行 が 返 るとエラーになる - 副 問 合 せの 結 果, 行 がなければ NULL が 返 る NULL が 返 ったとき,>=<による 比 較 演 算 の 結 果 は NULL である NULL かどうかを 判 断 するのは,IS NULL 演 算 子 NULL が 返 ったとき,AND,OR,NOT による 論 理 演 算 は 次 のルールに 従 う AND 真 偽 NULL OR 真 偽 NULL NOT 真 真 偽 NULL 真 真 真 真 真 偽 偽 偽 偽 偽 偽 真 偽 NULL 偽 真 NULL NULL 偽 NULL NULL NULL NULL NULL NULL NULL 12. Oracle SQL: 集 合 演 算 子 次 の 2 種 類 の 従 業 員 の 集 合 を 考 える a={salary>=12000 の 従 業 員 } b={ より 前 に 採 用 された 従 業 員 } これを 表 示 するには 次 のようにすればよい SELECT last_name, salary, hire_date, (CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a, (CASE WHEN hire_date < TO_DATE(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE salary>=12000 OR hire_date < TO_DATE(' ','YYYY-MM-DD') ORDER BY a, b DESC; 結 果 は 次 のようになる LAST_NAME SALARY HIRE_DAT A B Russell Good 27

28 Hartstein Good Partners Good Errazuriz Good Greenberg Good Senior De Haan Good Senior King Good Senior Kochhar Good Senior Higgins Good Senior Ernst Senior Hunold Senior Whalen Senior Gietz Senior Mavris Senior Baer Senior Faviet Senior Raphaely Senior 行 が 選 択 されました これをもとに, 集 合 演 算 子 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(' ','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(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE hire_date < TO_DATE(' ','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(' ','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

29 (CASE WHEN hire_date < TO_DATE(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE hire_date < TO_DATE(' ','YYYY-MM-DD') ORDER BY a, b DESC; MINUS a={salary>=12000 の 従 業 員 } - b={ より 前 に 採 用 された 従 業 員 } SELECT last_name, salary, hire_date, (CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a, (CASE WHEN hire_date < TO_DATE(' ','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(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE hire_date < TO_DATE(' ','YYYY-MM-DD') ORDER BY a, b DESC; MINUS b={ より 前 に 採 用 された 従 業 員 } - a={salary>=12000 の 従 業 員 } SELECT last_name, salary, hire_date, (CASE WHEN salary>=12000 THEN 'Good' ELSE '' END) a, (CASE WHEN hire_date < TO_DATE(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE hire_date < TO_DATE(' ','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(' ','YYYY-MM-DD') THEN 'Senior' ELSE '' END) b FROM hr.employees WHERE salary>=12000 ORDER BY a, b DESC; Oracle SQL: データ 操 作 言 語 (DML) (ROLLBACK 可 能 ) 行 の 挿 入 ( 列 名 を 指 定 する 方 式 ) p.8-6 INSERT INTO 表 名 ( 列 名, 列 名,...) VALUES ( 値, 値, NULL, NULL,...); 行 の 挿 入 ( 列 名 を 省 略 する 方 式 ) p

30 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

31 トランザクション 処 理 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

32 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; 表 の 変 更 ( 列 追 加, 列 変 更, 列 削 除,デフォルト 値 設 定 ) p

33 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 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

34 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; 順 序 の 使 用 p ( 例 ) 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 ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE NOCACHE NOCYCLE; 順 序 の 削 除 p DROP SEQUENCE dept_deptid_seq; 索 引 の 作 成 p CREATE INDEX 索 引 名 ON 表 名 ( 列 名, 列 名,...); ( 例 ) CREATE INDEX emp_last_name_idx ON employees(last_name); 索 引 の 削 除 DROP INDEX 索 引 名 ; シノニム( 別 名 )の 作 成 p CREATE [PUBLIC] SYNONYM シノニム 名 FOR オブジェクト 名 ; 34

35 ( 例 ) CREATE SYNONYM d_sum FOR dept_sum_vu; シノニムの 削 除 DROP SYNONYM d_sum; 16. Oracle SQL: ディクショナリの 利 用 DESCRIBE dictionary DESCRIBE user_objects ユーザ オブジェクト DESCRIBE user_tables 表 情 報 DESCRIBE user_tab_columns 列 情 報 DESCRIBE user_constraints 制 約 情 報 DESCRIBE user_cons_columns 列 制 約 情 報 DESCRIBE user_views ビュー 情 報 DESCRIBE user_sequences 順 序 情 報 DESCRIBE user_synonyms シノニム 情 報 以 上 35