データベースの教育例１（Oracle基礎編）
データベースの学習方法
以前の記事にも書いたように、開発者の視点でデータベース（RDBMS）の重要分野を整理すると、以下のようになります。
実業務で使用するデータベースには、クラスタだったり、リモートデータベースとの同期だったり、テキスト検索だったり、様々な機能があります。上記に示したのは、20年以上前から存在する普遍的な機能ですので、これからRDBMSを学習する方には必須の知識といえます。
この記事では、RDBMSのシェアNo.1、デファクトスタンダートのオラクルデータベースのSQLについて学習方法を書いていきます。
SQL
SQLとは、Structured Query Languageの略で、直訳すると”構造化問い合わせ言語”となります。
SQLは、主にISOで標準化が行われていますが、完全に統合されているわけではなく、RDBMS製品による方言が残っています。
[blogcard url=”https://ja.wikipedia.org/wiki/SQL”]
Oracle LiveSQLを使う
SQLの学習には、オラクル社が提供するLiveSQL（無償）を使うと便利です。
オラクルは、マシンのリソース（HDDやCPU、メモリ）を多く消費しますので、インストールなしでSQLを検証できるLiveSQLは有用です。後述するストアードプロシージャ（PL/SQL）も使えます。
[blogcard url=”https://www.oracle.com/technetwork/jp/database/application-development/livesql/index.html”]
なお、LiveSQLなどオラクル社の提供する無償サービスを受ける場合には、アカウントの作成が必要です。
アカウント作成すると、データベースエンジンなど最新の製品をダウンロードして個人の範囲で使用することもできます。
では早速使ってみましょう。
LiveSQLにログイン後、画面上部のSQL入力欄にSQLを入力すると、画面下部に結果が表示されます。
上記例は、scott.empのすべてのレコードを表示するSQL（select文）を実行しています。
DML
DMLとは、Data Manipulation Languageの略で、データ操作言語と訳せます。
具体的には、下記4種類の文で構成されます。
- データを検索するselect文
- データを更新するupdate文
- データを挿入するinsert文
- データを削除するdelete文
SELECT
select文を確認しましょう。
オラクルでは伝統のあるユーザーSCOTTのEMP表とDEPT表の内容から確認しましょう。
EMP表は、従業員（Employee）を示す表、DEPT（Department）は部門を示す表です。
EMP表は8カラム14レコード、DEPT表は3カラム4レコードが存在します。（図の”レコード”は筆者が説明用に追記したものです。）
以下にselect文を4つ例示します。
例1> select * from emp;
例2> select ename,job from emp;
例3> select ename,job from emp where sal >1000;
例4> select * from emp.dept where emp.deptno=dept.deptno;
例１は、emp表のすべてのレコードの表示です。
注意しなければいけないのは、RDBMSはカラムやレコードの順番は管理していないので、カラムやレコードの表示順は保証されません。つまり、SQLを投入するごとに表示順が異なる可能性があります。
例2は、関係演算でいうところの射影です。簡単に言うと、列の選択表示です。
例3は、行の選択です。sal（給料）が1000ドル以上のレコードを選択表示しています。
例4は、表同士の結合です。deptnoでemp表とdept表を結合していて、結果は以下のようになります。
DEPTNOが2回表示されているのは、emp表とdept表それぞれが表示されているためです。
INSERT
次にレコードの追加を行うinsert文です。
例1> insert into dept (deptno,dname,loc) values( 50,’SUPPORT’,’TOKYO’ );
例2> insert into dept values( 50,’SUPPORT’,’TOKYO’ );
例3> insert into dept2 select * from dept;
例１は、カラムを指定した正しいデータの挿入方法です。
例２も動作はしますが、おすすめできない方法です。後述するCREATE TABLEで指定した順番に値を指定していますが、カラムを指定していないので、例えば、LOCカラムに値’SUPPORT’が設定されてしまう可能性があります。
例３は、データの丸コピーです。こでもカラムは指定した方がよいです。
UPDATE
レコードの内容を更新するUPDATE文です。
例1> update emp set JOB=’SALESMAN’;
例2> update emp set JOB=’SALESMAN’ where empno=7782;
例3> update emp set HIREDATE=SYSDATE where empno=7782;
例１は、EMP表のすべてのレコードのJOBカラムを’SALESMAN’に変更します。
例２は、empno=7782のレコード（CLARKさん）だけJOBカラムを’SALESMAN’に変更します。
例３は、empno=7782のレコード（CLARKさん）だけHIREDATEカラムを現在日時に変更します。
DELETE
レコードを削除するDELETE文です。
例1> delete from emp;
例2> delete from emp where empno=7782;
例3> delete from emp where job like ‘%MA%’;
例１は、EMP表のすべてのレコードを削除します。
例２は、empno=7782のレコード（CLARKさん）だけを削除します。
例３は、JOBに’MA’が含まれるレコード（’SALESMAN’と’MANAGER’の合計7件）を削除します。
COMMITとROLLBACK
オラクルの場合は、明示的にトランザクションを指定しなくてもトランザクション処理となっています（設定により変更は可能です）ので、INSERT、UPDATE、DELETE文発行後は必ずトランザクションを終了させるためのCOMMITまたはROLLBACK文の発行が必要です。
>commit; 変更内容を適用する。
>rollback; 変更内容を破棄し以前の状態に戻す。
DDL
次に、DDLです。
DDLとは、Data Definition Languageの略で、データ定義言語と訳せます。
DDLは、データベースオブジェクトを定義・作成しますが、データベースに新機能が追加されると新種のオブジェクトが増えますので、たくさんの種類があります。
基本的なDDLには、以下があります。
- CREATE TABLE ：テーブルの作成
- CREATE INDEX ：索引の作成
- CREATE VIEW ：ビューの作成
CREATE TABLE
CREATE TABLE文は、テーブルを作成します。
先ほどまで使用していたEMP表の作成は、以下の構文となります。
CREATE TABLE EMP
( EMPNO NUMBER(4,0),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4,0),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2,0)
);
また、他のテーブルのコピーを作成したい場合は、以下のCREATE TABLE文を発行します。
create table emp as select * from scott.emp where 1<>1;
上記は、SCOTTユーザーのEMP表をカレントユーザーにコピーしますが、select文のwhere条件が成立しないためデータはコピーしません。
データのコピーも行う場合は、where条件を外します。
実際の運用では、上記に加えて作成する表領域の指定や格納領域の拡張方法などのオプションを指定します。
表の削除は、以下のようにDROP TABLE文にて行います。
DROP TABLE EMP;
CREATE INDEX
CREATE INDEX文は、索引を作成します。
EMP表のカラムJOBに索引を作成する構文を、以下に示します。
CREATE INDEX IDX_JOB ON EMP( JOB ) TABLESPACE USER;
索引は、表とは別のオブジェクト（表領域上に実体を持ちます）が作成され、オブジェクトは索引（今回はJOB）の値を持ちます。
よって、索引を作成すると、その分表領域（＝ハードディスク）を消費しますので、注意が必要です。
CREATE VIEW
CREATE VIEWは、参照用のビューを作成します。
SELECT文に名前を付けるようなイメージで、索引のようにデータの実体は持ちません。
> CREATE VIEW EMP_SUBSET AS SELECT ENAME,SAL FROM EMP;
> SELECT * FROM EMP_SUBSET;
上記は、EMP表のENAMEとSQLだけで構成されるビューEMP_SUBSETを作成しています。
作成したビューは、SELECT文で参照ができます。
拡張機能として更新可能なビューやデータのコピーを持つマテリアライズドビューがあります。
