研修ノート　Oracle

5月8日

やったこと

• Oracle
• データベース

学んだこと

データ

名前や住所などの一度決まったらおおよそ変わらないデータを、マスタという。

貸出情報など、溜まっていくデータをトランザクションデータという。

データを管理するシステムをDBMS（データベースマネジメントシステム）という。

データベース

RDB

表	テーブル
列	カラム
行	レコード

Oracle

OracleやMicrosoft SQL Serverは商用RDBMS。データベースの使用は有償であり、ライセンス契約を結ぶ必要がある。これに対して、MySQLやPostgreSQLは無償で利用できる。

RDBMSの例

名前	概要
Oracle	商用RDBMSとして、世界で最も多く使われている
Microsoft SQL Server	Microsoft社の商用RDBMS
PostgreSQL	オープンソースRDBMS。日本では特に人気がある
MySQL	世界で最も有名なオープンソースRDBMS
Microsoft Access	Microsoft社のOfficeファミリーRDBMS
SQLite	サーバー不要の組み込みのRDBMS

Oracleのデータ管理構造

データベースオブジェクト

Oracleではテーブル、インデックス、ビュー、シーケンスなど、何らかの機能を持った情報の集まりで構成されている。これらの情報のことを「データベースオブジェクト（以下、オブジェクト）」という。

ユーザ

Oracleを利用する際には「ユーザ」と呼ばれるアカウント情報が必要。
あらかじめ作成したユーザにログインすることでテーブルの操作を行える。

ユーザ名	概要
SYS	データベースの管理上、最も強力な権限を持ち、すべての管理機能を実行できる
SYSTEM	通常の管理業務で使うユーザで、データベースのアップグレードやバックアップ・リカバリなどの一部の機能を除く全ての管理機能が実行できる

スキーマ

各ユーザが管理しているオブジェクトを集めた場所をスキーマ（schema）という。全てのオブジェクトは、データベース中に登録された、いずれかのユーザのスキーマに属している。

Oracleの操作

接続の流れ

1. systemユーザに接続（ログイン）
2. systemユーザのワークスペースでユーザを作成
3. 作成したユーザに表領域、権限を付与
4. 作成したユーザに接続

接続

CREATE USER ユーザ名 IDENTIFIED BY パスワード; -- ユーザの作成
SELECT username, password FROM DBA_USERS WHERE username = 'ユーザ名(大文字)'; -- ユーザネームとパスワードを表示
GRANT ALL PRIVILEGES TO ユーザ名; -- 作ったユーザに対して権限を付与

削除

DROP USER sssuser CASCADE; -- ユーザの削除

テーブルの作成

CREATE TABLE 文を使用してテーブルを作成する。

CREATE TABLE文

CREATE TABLE <テーブル名> 
(<列名 1><データ型><この列の制約>, -- カンマを付ける
<列名 2><データ型><この列の制約>, 
<列名 3><データ型><この列の制約>, 
<列名 4><データ型><この列の制約>, 
･ 
･ 
･ 
<このテーブルの制約 1>,<このテーブルの制約 2>,……); -- ここにはカンマはつけない

「列名」とは、表（テーブル）の項目名（見出し）
「データ型」とは項目に入る情報の形式、「制約」とはルールのようなもの

代表的なデータ型

データ型	制限	説明
VARCHAR2 (n)	最大 4000 バイト	可変長文字列型（n に上限サイズを指定） ※1 括弧内に「n BYTE」と記述した場合、最大バイト数を指定（省略した場合も同様） ※2 括弧内に「n CHAR」と記述した場合、最大文字数を指定（2バイト文字も1文字として換算）
NVARCHAR2 (n)	最大 4000 バイト	各国語キャラクタセットを使用する UNICODE データ型の可変長文字列型（n に最大サイズを指定）
CHAR (n)	最大 2000 バイト	固定長文字列型（n に格納サイズを指定）
NCHAR (n)	最大 2000 バイト	各国語キャラクタセットを使用する UNICODE データ型の固定長文字列型（n に格納サイズを指定）
NUMBER (n, m)	最大 38 桁	数値型（n に最大桁数、m に小数部の桁数を指定）
DATE	紀元前 4712年1月1日～ 紀元 9999年12月31日	日付型（年/月/日/時/分/秒を格納し、7 バイトの領域を使用）
TIMESTAMP (n)		日付型（年/月/日/時/分/秒/ミリ秒を格納し、n には小数部の桁数を指定）

制約

Oracleの主な制約一覧

制約名	説明
一意制約 (UNIQUE)	重複するフィールドを禁止する。
NOT NULL 制約 (NOT NULL)	NULL 値を禁止する。 ※NULL とは、「何も値が入っていない状態」を表す特殊なデータ
主キー制約 (PRIMARY KEY)	テーブルに格納されているレコードを識別するための列。 一意制約と NOT NULL 制約の両機能を与える。
外部参照制約 (REFERENCES)	他のテーブルの一意キーを参照する。参照先に存在しないデータを入れることができなくなる。「一意キー」とは、一意制約、もしくは主キー制約が付いた列のことを指す。

列制約の記述方法

列の定義と同時に、その列に対して制約を設定する方法。

制約名	構文
一意制約	列名 データ型 UNIQUE
NOT NULL 制約	列名 データ型 NOT NULL
主キー制約	列名 データ型 PRIMARY KEY ※この方法は、主キーが1つの列だけで構成されている場合のみ記述可能
外部参照制約	列名 データ型 REFERENCES 参照先のテーブル名(参照先の一意キー)

表制約の記述方法

列の定義とは別に、テーブル全体の定義の最後に制約を記述する方法です。複数の列を組み合わせた制約（複合キーなど）を設定する場合に必要。

制約名	構文
一意制約	CONSTRAINT 制約名 UNIQUE (列名, 列名, ...)
NOT NULL 制約	記述不可
主キー制約	CONSTRAINT 制約名 PRIMARY KEY (列名, 列名, ...) ※複合主キーの設定が可能
外部参照制約	CONSTRAINT 制約名 FOREIGN KEY(列名, 列名, ...) REFERENCES 参照先のテーブル名(参照先の一意キー, 参照先の一意キー, ...)

列制約 記述例

CREATE TABLE employee( 
    emp_id VARCHAR2(10) PRIMARY KEY, -- 主キー制約
    emp_name VARCHAR2(10) NOT NULL,  -- NULLはNG
    tel VARCHAR2(10) UNIQUE, -- 重複はNG
    age NUMBER(2), 
    dept_id CHAR(2) REFERENCES dept(dept_id) -- deptというテーブルを参照
); 

-- 参照
REFERENCES テーブル名(主キー)

表制約 記述例

CREATE TABLE emp( 
    emp_id_1 VARCHAR2(5), 
    emp_id_2 VARCHAR2(10), 
    emp_name VARCHAR2(10), 
    tel VARCHAR2(10), 
    age NUMBER(2), 
    dept_id CHAR(2), 
    PRIMARY KEY(emp_id_1, emp_id_2), 
    UNIQUE(tel), 
    FOREIGN KEY(dept_id) REFERENCES dept(dept_id) 
);

テーブル定義

テーブル定義の例

No	論理名称	物理名称	データ型	桁数	制約	備考
1	社員 ID	emp_id	NUMBER	5	PRIMARY KEY	—
2	パスワード	emp_pass	VARCHAR2	10	NOT NULL	—
3	社員名	emp_name	VARCHAR2	20	NOT NULL	—
4	性別	gender	NUMBER	1	NOT NULL	1：男 2：女
5	住所	address	VARCHAR2	30	—	—
6	生年月日	birthday	DATE	—	—	—
7	部署 ID	dept_id	NUMBER	2	NOT NULL FOREIGN KEY	1：総務部 2：営業部 3：経理部 4：資材部

• 論理名称
  • プログラマ以外でもわかる名前
• 物理名称
  • 実際に書く名前

テーブルの変更

テーブルの定義を変更したい場合は、ALTER TABLE文を使う。ALTERは「変える」という意味。

ALTER TABLE文

ALTER TABLE テーブル名 動作の指定 列の定義; 
 
-- 例…（列の追加を意味する「ADD」を実行する場合） 
ALTER TABLE dept ADD note VARCHAR2(500); 

ALTER TABLE文の各種機能

キーワード	説明
ADD	列の追加。ADDの後に列の定義を記述する。
MODIFY	列の変更。MODIFYの後に変更後の内容を記述する。
RENAME COLUMN	列の名前変更。<変更前> TO <変更後>という形で記述する。
RENAME TO	テーブル名その変更。TO <新テーブル名>という形で記述する。
DROP COLUMN	列の削除。 指定した列を削除する。

---

5月11日

やったこと

• CRUD
• トランザクションとロック
• 演算子
• 集約関数
• レコードのグループ化

学んだこと

CRUD

CRUDとは、データの基本の操作を指す。

CRUD

C	登録、作成 (Create)	INSERT
R	検索、抽出 (Read)	SELECT
U	更新 (Update)	UPDATE
D	削除 (Delate)	DELETE

CRUD文

-- 基本構文
INSERT INTO テーブル名 列名 VALUES(値);

-- 基本構文 + where句 （つけないと全件処理）
UPDATE テーブル名 SET 列名 = 値 WHERE 条件;
DELETE FROM テーブル名;

-- 基本構文 + α
SELECT * FROM テーブル名;
SELECT 列名 FROM テーブル名;

INSERT UPDATE DELETE	更新系SQL	COMMITで完了する （COMMITしないと他の人に適用されない）
SELECT	参照系SQL	COMMITは必要ない

AS句

-- 別名を付ける
SELECT 列名 AS 別名 FROM テーブル名;
SELECT 列名 AS "日本語" FROM テーブル名; -- 日本語は"XXX"で囲む
-- 別名は、エイリアスとも呼ばれる

DISTINCT句

-- 重複を省く
SELECT DISTINCT 列名 FROM テーブル名;

WHERE句

-- データの抽出を行う
SELECT 列名 FROM テーブル名 WHERE 条件;

-- 条件
WHERE 列名 = 値 AND 列名 = 値; -- 論理積
WHERE 列名 = 値 OR 列名 = 値; -- 論理和
WHERE 列名 BETWEEN 数値a AND 数値b; -- aからbを指定
WHERE 列名 IN (値1, 値2, 値3); -- 同じ列に対してORができる
WHERE 列名 LIKE '%値%'; -- 文字列に対するあいまい検索

ROLLBACK文

ROLLBACK; -- 更新前の状態に戻す

トランザクションとロック

複数のSQLの処理をひとまとめにしたものを、トランザクションという。
複数の処理の途中でエラーが起きたときには、ロールバックする。

同じのデータに複数がアクセスした際に、どちらかのデータのみを反映し、もう一方を反映しないことをロックという。

結果を反映する場合はCOMMIT、反映しない場合はROLLBACKをする。

ロック

Oracleの場合、INSERT、UPDATE、DELETEが実行されると、該当レコードにロックがかかり、他トランザクションから変更できなくなる。
行（レコード）単位で操作を制限することを「行レベルロック」という。

テーブル全体をロックする「テーブルロック」をすると、デットロックが起こりやすくなる。

一般的にロックされている状態では、次のような機能が働く。

• INSERT、UPDATE、DELETEを実行した結果は、COMMIT、ROLLBACKをするまで他のユーザのSELECTの結果に反映されない。
• INSERT、UPDATE、DELETEを実行した行は、COMMIT、ROLLBACKをするまで他のユーザは変更できない

セーブポイント

ROLLBACKで処理を戻す際に、どの状態まで戻すかをSAVEPOINTで指定することができる。

セーブポイント文

-- セーブポイントの設定
SAVEPOINT セーブポイント名;

-- 指定したセーブポイントまでロールバック
ROLLBACK TO セーブポイント名;

演算子

SQLでは四則演算ができる。

NULLを含んだ計算は、必ずNULLになる。NULLを0で割るとエラーにならずNULLになる。

比較演算子

演算子	意味
=	〜と等しい
<>	〜と等しくない
>=	〜以上
>	〜より大きい
<=	〜以下
<	〜より小さい

NULLとの比較

演算子	意味	使用例
IS NULL	〜は NULL	<列名> IS NULL;
IS NOT NULL	〜は NULL ではない	<列名> IS NOT NULL;

LIKE句 あいまい検索

LIKE 演算子を使用すると、文字列のパターンマッチング（あいまい検索）を行うことができる。

1. 基本的なワイルドカード

記号	意味	使用例	説明
%	0文字以上の任意の文字列	LIKE 'J%'	"J" で始まるすべての文字列（"J", "Java", "JSON" など）
_	任意の1文字	LIKE 'A_'	"A" で始まる2文字の文字列（"AI", "An" など）

2. 検索パターンの組み合わせ

パターン	意味	説明
'%文字列%'	部分一致	指定した文字列がどこかに含まれていれば一致
'文字列%'	前方一致	指定した文字列で始まれば一致
'%文字列'	後方一致	指定した文字列で終われば一致
'_A%'	2文字目がA	1文字目は何でもよく、2文字目が "A" で、その後は0文字以上続く

3. 特殊な文字（% や _）自体を検索する場合（ESCAPE）

検索したい文字の中に % や _ が含まれている場合は、ESCAPE キーワードを使ってそれらを「ただの文字」として扱う。

例：末尾が "_5" という文字列を検索する場合

SELECT * FROM table WHERE column LIKE '%#_5' ESCAPE '#';
-- #のあとの文字は普通の文字として扱う

SELECT * FROM table WHERE column LIKE '%1_5' ESCAPE '1';
-- エスケープ用の文字は自由に決められる

集約関数

集約関数の例

関数名	意味	内容
COUNT	レコード数（行数）	テーブルのレコード数（行数）を数える
SUM	合計値	テーブルの数値列のデータの合計を求める
AVG	平均値	テーブルの数値列のデータの平均を求める
MAX	最大値	テーブルの任意の列のデータの最大値を求める
MIN	最小値	テーブルの任意の列のデータの最小値を求める

集積関数 記述例

-- COUNT関数
SELECT COUNT(*) FROM テーブル名; -- *が使えるのは、COUNT関数のみ
SELECT COUNT(列名) FROM テーブル名;

-- SUM関数
SELECT SUM(列名) FROM テーブル名; -- 扱えるデータ型はNUMBER型のみ

-- AVG関数
SELECT AVG(列名) FROM テーブル名; -- 扱えるデータ型はNUMBER型のみ
SELECT ROUND(AVG(列名)) FROM テーブル名; -- 小数点以下切り捨て

-- WHERE句に関数を直接記述することはできない

レコードのグループ化

レコードをいくつかのグループにまとめて集約するとき、GROUP BY句を使用する。

GROUP BY句

SELECT 列名1, 列名2,・・・ FROM テーブル名 GROUP BY 列名1, 列名2,・・・;

-- GROUP BY句に指定した列の値が同じレコードを、1つの塊にまとめる

グループの絞り込み

WHERE句ではグループの絞り込みができない。そこで、グループに対する絞り込みは HAVING句で行う。

HAVING句

SELECT 列名1, 列名2,・・・
FROM テーブル名
WHERE 条件 -- 2
GROUP BY 列名1, 列名2,・・・
HAVING 値に対する条件;

-- HAVING句は、必ずGROUP BYの後ろに記述する。

SQLの実行順

SELECT 列名1, 列名2,・・・ -- 5
FROM テーブル名  -- 1
WHERE 条件 -- 2
GROUP BY 列名1, 列名2,・・・ -- 3
HAVING 値に対する条件; -- 4

-- WHEREとGROUP BYでは、WHEREから実行される

並び替え

SELECT文の検索結果を並び替えるとき、ORDER BY句を使用する。

ORDER BY句

ORDER BY 列名 ASC; -- 昇順
ORDER BY 列名 DESC; -- 降順

-- ORDER BY句は、一番最後に記述する
-- 実行順も一番最後

-- OracleではNULLは最も大きい値として扱う

-- 拡張
ORDER BY 列名1 ASC, 列名2 DESC; -- 列名1で並び替えたあと、列名1で同値のものを列名2で並び替える

SELECT 列名 AS 別名
FROM テーブル名
ORDER BY 別名 ASC;
-- ORDER BY句は別名を利用できる

---

5月12日

やったこと

• 結合
• 集合演算
• サブクエリ
• シーケンス
• ビュー
• インデックス
• 正規化

学んだこと

結合

複数のテーブルのレコードを組み合わせて、1つの仮想的なテーブルを一時的に作成する演算のことを、 結合(JOIN) という。

結合の方法は大きく分けて、内部結合と外部結合に分かれる。

2つのテーブルに存在する同じデータを、結合キーという。

内部結合

内部結合では、結合キーの値が両方のテーブルに存在する行を取得する。

内部結合 構文

SELECT 列名
FROM テーブル名1
INNER JOIN テーブル名2
  ON テーブル名1.結合キー = テーブル名2.結合キー;

内部結合の出力

外部結合

結合キーの値の有無にかかわらず、基準となっているテーブルの全レコードが結合、検索される。

外部結合 構文

-- 左外部結合
SELECT 列名
FROM テーブル名1
LEFT OUTER JOIN テーブル名2
ON テーブル名1.結合キー = テーブル名2.結合キー;

-- 右外部結合
SELECT 列名
FROM テーブル名1
RIGHT OUTER JOIN テーブル名2
ON テーブル名1.結合キー = テーブル名2.結合キー;

左外部結合の出力

右外部結合の出力

別名を付ける

-- 別名を付ける例
SELECT si.shop_id, i.item -- FROMでつけた別名を使える
FROM shop_item si INNER JOIN item i
  ON si.item_id = i.item_id;

その他の結合

3つ以上の結合

3つ以上のテーブルの結合も行うことができる。結合できるテーブルの数に制限はない。
また、内部結合と外部結合を組み合わせることも可能。

3 つのテーブルを全て内部結合するSELECT文

SELECT 列名
FROM テーブル名1 INNER JOIN テーブル名2
  ON テーブル名1.結合キー1 = テーブル名2.結合キー2
INNER JOIN テーブル名3
  ON テーブル名2.結合キー2 = テーブル名3.結合キー3;

自己結合

同じテーブル同士を結合することもできる

その他の機能

集合演算

複数のテーブルの検索結果を足したり引いたりする演算のことを、集合演算という。

テーブルの足し算

-- UNION演算子
SELECT 文
UNION
SELECT 文

テーブルの共通部分の検索

-- INTERSECT演算子
SELECT 文
INTERSECT
SELECT 文

テーブルの引き算

-- MINUS演算子
SELECT 文
MINUS
SELECT 文

サブクエリ

クエリとは、SELECT文のこと。

サブクエリとは、SELECT文の中に入れ子構造で記述されたSELECT文のこと。

サブクエリ 構文

SELECT 列名
FROM (SELECT 文)
[WHERE JOIN など]

サブクエリを利用したSELECT文では、まずサブクエリ内の処理が実行される。

IN句を使用したサブクエリ

IN句を使用したサブクエリがよく使われる。

IN句を使用したサブクエリ 構文

SELECT 列名
FROM テーブル名
WHERE 列名
IN (SELECT 文);

スカラ・サブクエリ

スカラ・サブクエリとは、検索結果が必ず1行1列のサブクエリのこと。
スカラとは、単一のという意味。

例外的に、WHERE句に集約関数を書くことができる。

WHERE句にスカラ・サブクエリ

-- 記述例
SELECT ...
...
WHERE sel_price > (SELECT AVG(sel_price) FROM item);

エラーになるスカラ・サブクエリ

-- スカラ(単一)ではない、通常のサブクエリを使用
SELECT ...
...
WHERE sel_price > (SELECT AVG(sel_price)
                    FROM item
                    GROUP BY category_id);

-- 複数行返すサブクエリを使用するとエラーになる

シーケンス

列に値を登録する際に、自動で入力させたいときにシーケンスを使用する。
自動的に1ずつ増加した数値を登録させる処理を自動採番とよぶ。

シーケンスの作成

CREATE SEQUENCE シーケンス名 
[START WITH 初期値] -- オプション ↓↓↓
[INCREMENT BY 増減値] 
[MAXVALUE 最大値 | NOMAXVALUE] 
[MINVALUE 最小値 | NOMINVALUE] 
[CYCLE | NOCYCLE] 
[CACHE キャッシュ数 | NOCACHE]; -- オブション ↑↑↑

シーケンスのオプション

オプション名	説明
START WITH	START WITH は順序の初期値を指定（省略した場合、昇順では順序の最小値、降順では順序の最大値になる）
INCREMENT BY	INCREMENT BY は順序の増減値を指定（省略した場合、1 になる）
MAXVALUE	MAXVALUE は順序の最大値を指定（省略または NOMAXVALUE とした場合、昇順では 10²⁸-1、降順では -1 となる）
MINVALUE	MINVALUE は順序の最小値を指定（省略または NOMINVALUE とした場合、昇順では 1、降順では -(10²⁷-1) となる）
CYCLE	CYCLE は順序が最大値または最小値に達しても、値を初期値に戻して生成できる指定（省略または NOCYCLE を指定すると値は生成しなくなる）
CACHE	CACHE は高速に順序番号にアクセスできるように、事前にメモリー上に割り当て保持しておく順序番号の数を指定 （省略した場合、連番が急に 20 という飛び番号になる。飛び番号を防ぐためには、NOCACHE とオプションに指定すること）

シーケンスの利用

シーケンスの利用構文

INSERT INTO テーブル名 VALUES(シーケンス名.NEXTVAL, ...);

シーケンスの疑似列

|疑似列名|説明| |CURRVAL|| |NEXTVAL||

ビュー

ビューとは、任意のSELECT文を一つのオブジェクトとして保存したもの。
テンプレートや辞書登録のように、名前をつけて保存することができる。

ビュー作成

CREATE VIEW ビュー名 AS
SELECT 列名 FROM テーブル名
[WHERE、JOIN など]；

-- このASはエイリアスとは別の意味
-- = に近いイメージ
-- 「この『ビューの名前』は、右側の『SELECT文』と同じものとして扱ってね」と定義している

インデックス

インデックスとは、テーブル内を検索する際の「索引」として使用できるオブジェクトのこと。

検索が高速化する。

インデックスの作成

CREATE INDEX インデックス名 ON テーブル名(列名);

-- 1つのテーブルに複数のインデックスを作成できる

正規化

使いやすいデータベースを作るルールを正規化という。

正規化の方法は、第1正規化、第2、第3...と段階的に用意されている。