07-03.テーブル【テーブル管理】

■テーブルコラムの管理
　テーブルのコラムは　ADD, MODIFY, DROP演算子により管理できる。

■ADD
　テーブルへ新しいコラムを追加

-- VARCHAR2データタイプのaddrコラムをempテーブルへ追加

SQL> ALTER TABLE emp ADD (addr VARCHAR2(50));

表が変更されました。

SQL>

■MODIFIY
　テーブルのコラムを修正及びNOT NULLコラムに変更できる。
　コラムが既にデーターを持っている場合、他のデータータイプに変更できない。

-- enameコラムをVARCHAR2、サイズ50に修正した例

SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50));

表が変更されました。

SQL> ALTER TABLE emp MODIFY (ename VARCHAR2(50) NOT NULL);

表が変更されました。

SQL>

■DROP
　テーブルコラムを削除またはテーブル制約条件を削除できる。

-- コラムの削除文法
ALTER TABLE table_name DROP COLUMN column_name


-- 制約条件の削除例

SQL> ALTER TABLE emp2 DROP PRIMARY KEY;

表が変更されました。


-- CASCADEと一緒に使用すると外部キーにより参照されている主キーも削除できる。

SQL> ALTER TABLE dept DROP CONSTRAINT dept_pk_depno CASCADE;

表が変更されました。

SQL>

■既存テーブルのコピー
　-既存テーブルを一部及び全て複製するときはサブクエリを持つCREATE TABLEコマンドで簡単にコピーできる。
　-制約条件、トリガー、テーブル権限はコピーできない。
　-制約条件はNOT NULL条件のみコピーできる。

-- テーブル複製文法

CREATE TABLE [schema.]table_name 
 [LOGGING | NOLOGGING]
 [....]
AS
 subquery


-- テーブル複製例
-- empテーブルを複製してemp6テーブルを作成


SQL> SELECT * FROM emp;

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH                                              CLERK           7902 13-08-13        800                    20
      7499 ALLEN                                              SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD                                               SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES                                              MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN                                             SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE                                              MANAGER         7839 13-08-13       2850                    30
      7782 CLARK                                              MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT                                              ANALYST         7566 13-08-13       3000                    20
      7839 KING                                               PRESIDENT            13-08-13       5000                    10
      7844 TURNER                                             SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS                                              CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7900 JAMES                                              CLERK           7698 13-08-13        950                    30
      7902 FORD                                               ANALYST         7566 13-08-13       3000                    20
      7934 MILLER                                             CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL> SELECT * FROM emp6;
SELECT * FROM emp6
              *
行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。


SQL> CREATE TABLE emp6
  2  AS SELECT * FROM emp;

表が作成されました。

SQL> SELECT * FROM emp6;

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7369 SMITH                                              CLERK           7902 13-08-13        800                    20
      7499 ALLEN                                              SALESMAN        7698 13-08-13       1600        300         30
      7521 WARD                                               SALESMAN        7698 13-08-13       1250        500         30
      7566 JONES                                              MANAGER         7839 13-08-13       2975                    20
      7654 MARTIN                                             SALESMAN        7698 13-08-13       1250       1400         30
      7698 BLAKE                                              MANAGER         7839 13-08-13       2850                    30
      7782 CLARK                                              MANAGER         7839 13-08-13       2450                    10
      7788 SCOTT                                              ANALYST         7566 13-08-13       3000                    20
      7839 KING                                               PRESIDENT            13-08-13       5000                    10
      7844 TURNER                                             SALESMAN        7698 13-08-13       1500          0         30
      7876 ADAMS                                              CLERK           7788 13-08-13       1100                    20

     EMPNO ENAME                                              JOB              MGR HIREDATE        SAL       COMM     DEPTNO ADDR
---------- -------------------------------------------------- --------- ---------- -------- ---------- ---------- ---------- ----------
      7900 JAMES                                              CLERK           7698 13-08-13        950                    30
      7902 FORD                                               ANALYST         7566 13-08-13       3000                    20
      7934 MILLER                                             CLERK           7782 13-08-13       1300                    10

14行が選択されました。

SQL>

■テーブルスペース変更
　ORACLE 8iからは　ALTER TABLE ～ MOVE TABLESPACEコマンドで簡単にテーブルスペースを変更できる。

--テーブルスペース変更文
ALTER TABLE table_name Move TABLESPACE tablespace_name;

--テーブルスペース変更例
--scottユーザーへ権限付与
SQL> GRANT CREATE TABLESPACE, ALTER TABLESPACE, DROP TABLESPACE TO scott WITH ADMIN OPTION;

権限付与が成功しました。

--scottユーザーで接続
SQL> conn scott/tiger
接続されました。
SQL> show user
ユーザーは"SCOTT"です。

--test01テーブルスペース作成
SQL> CREATE TABLESPACE test01 DATAFILE 'test01.dbf' SIZE 20M;

表領域が作成されました。

--テーブルスペースの変更
SQL> ALTER TABLE emp
  2  MOVE TABLESPACE test01;

表が変更されました。

SQL>

■テーブルのTRUNCATE
　-テーブルをTruncateするとテーブルの安部手の行が削除され、使用された領域が解除される
　-TRUNCATE TABLEはDDLなのでロールバックデーターは作成されない。
　-DELETEコマンドでデーターを削除するとROLL BACKコマンドで復旧できるが、TRUNCATEデーターを削除すると復旧できない。
　-外部キー参照中のテーブルはTRUNCATEできない。
　-TRUNCATEコマンドを使用すると削除トリガーが実行できない。

--TRUNCATE文法

TRUNCATE TABLE [schema.]table_name;

■テーブル削除（DROP TABLE）

--テーブル削除文法
DROP TABLE [schema.]table_name [CASCADE CONSTRAINTS];

--empテーブル削除

SQL> DROP TABLE emp;

表が削除されました。

--CASCADE CONSTRAINTは外部キーにより参照している主キーを含むテーブルの場合
--主キーを参照してる外部キー条件も一緒に削除する。

SQL> DROP TABLE emp CASCADE CONSTRAINT;

07-02.テーブル【テーブル制約条件(Constraint)】

■制約条件（Constraint）とは？

　制約条件とはテーブルへ不適切なデーターが入力されるのを防ぐため、

　規則を適用しているのと考えればよい。

　テーブル内でデーターの性格を定義するのが制約条件だ。

　-制約条件はデーターの整合性維持のためユーザーが指定するもの

　-すべての制約はDATA DICTIONARYへ保存される。

　-制約はテーブル作成時または作成後ALTERコマンドで定義可能だ。

　-NOT NULL制約条件は必ずコラムレベルのみで定義可能だ。

■NOT NULL条件

　コラムを必須フィールド化する時、定義する。

--NOt NULL制約条件を設定すると、enameコラムには必ずデーターを入力しなければいけない。
--emp_nn_enameを制約条件名と設定した。

SQL> CREATE TABLE emp3(
  2  ename VARCHAR2(20) CONSTRAINT emp_nn_ename NOT NULL );

表が作成されました。


--制約条件はUSER_CONSTRAINTSビューで確認できる。

SQL> SELECT CONSTRAINT_NAME
  2  FROM USER_CONSTRAINTS
  3  WHERE TABLE_NAME = 'EMP3';

CONSTRAINT_NAME
------------------------------
EMP_NN_ENAME

SQL>

■UNIQUE条件
　同じ値を持つ行（レコード）を許さない。自動でインデックスが指定される。

--deptnoコラムへUNIQUE制約条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_uk_deptno UNIQUE (deptno);

表が変更されました。


--制約条件を削除

SQL> ALTER TABLE emp2
  2  DROP CONSTRAINT emp2_uk_deptno;

表が変更されました。

SQL>

■CHECK条件
　コラムの値を特定の範囲に制限できる。

--comnコラムへ1～100の値のみ入れるCHECK条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_ck_comn
  3  CHECK (comn >=1 AND comn <= 100);

表が変更されました。

SQL>


--制約条件の削除

SQL> ALTER TABLE emp2
  2  DROP CONSTRAINT emp2_ck_comn;

表が変更されました。

SQL>


--10000,20000,30000,40000,50000の値のみ入れるCHECK条件を定義

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_ck_comn
  3  CHECK (comn IN (10000, 20000, 30000, 40000, 50000));

表が変更されました。

SQL>

■DEFAULT(コラム基本値)指定
　データーを入力しなくても指定された値が基本的に入力される。

--hiredateコラムに値を入力しなくても今日の日付が入る。

SQL> CREATE TABLE emp4(
  2  EMPNO NUMBER CONSTRAINT emp4_pk_empno PRIMARY KEY,
  3  ENAME VARCHAR2(20),
  4  JOB VARCHAR2(40),
  5  MGR NUMBER,
  6  HIREDATE DATE DEFAULT SYSDATE );

表が作成されました。

SQL>

■PRIMARY KEY指定
　-主キーはUNIQUEとNOT NULLの結合と言える。
　-主キーはそのデーター行を代表するコラムの役割を果たし、他テーブルから外部キーとして参照できる。
　-UNIQUE条件同様、主キーを定義すると自動的にINDEXが作られ、その名前は主キー制約条件の名前と同じだ。
　-INDEX検索キーで検索速度を向上させる
　　（UNIQUE、PRIMARY KEY作成時、自動的に作成される）

--PRIMARY KEY作成例文

SQL> CREATE TABLE emp5(
  2  empno NUMBER CONSTRAINT emp5_pk_empno PRIMARY KEY );

表が作成されました。

SQL>

--ALTER TABLEコマンドでPRIMARY KEY作成例文

SQL> ALTER TABLE emp2
  2  ADD CONSTRAINT emp2_pk_empno  PRIMARY KEY (empno) ;

■FOREIGN KEY(外部キー)指定
　-主キーを参照するコラムまたはコラムの集まり。
　-外部キーを持つコラムのデーター形は外部キーが参照する主キーのコラムとデーター形と一致しなければならない。
　-外部キーにより参照する主キーは削除できない。
　-ON DELETE CASCADEで定義された外部キーのデーターはその主キーが削除される時一緒に削除される。

--deptテーブルのdeptnoコラムを主キーに設定する。

SQL> ALTER TABLE dept
  2  ADD CONSTRAINT dept_pk_depno PRIMARY KEY (deptno);

表が変更されました。


--empテーブルのdeptnoコラムがdeptテーブルのdeptnoコラムを参照する外部キーを作成

SQL> ALTER TABLE emp2 ADD CONSTRAINT emp2_fk_deptno
  2  FOREIGN KEY (deptno)  REFERENCES dept(deptno);

表が変更されました。

SQL>

■制約条件の確認
　-USER_CONS_COLUMNS : コラムに設定された制約条件確認
　-USER_CONSTRAINTS : ユーザーが持つすべての制約条件確認

SQL> SELECT SUBSTR(A.COLUMN_NAME,1,15) COLUMN_NAME,
  2  DECODE(B.CONSTRAINT_TYPE,
  3  'P','PRIMARY KEY',
  4  'U','UNIQUE KEY',
  5  'C','CHECK OR NOT NULL',
  6  'R','FOREIGN KEY') CONSTRAINT_TYPE,
  7  A.CONSTRAINT_NAME
  8  FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
  9  WHERE A.TABLE_NAME = UPPER('&table_name')
 10  AND A.TABLE_NAME = B.TABLE_NAME
 11  AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
 12  ORDER BY 1;


--テーブル名を入力する

table_nameに値を入力してください: emp2
旧   9: WHERE A.TABLE_NAME = UPPER('&table_name')
新   9: WHERE A.TABLE_NAME = UPPER('emp2')

COLUMN_NAME                    CONSTRAINT_TYPE   CONSTRAINT_NAME
------------------------------ ----------------- ------------------------------
COMN                           CHECK OR NOT NULL EMP2_CK_COMN
DEPTNO                         FOREIGN KEY       EMP2_FK_DEPTNO
EMPNO                          PRIMARY KEY       EMP_PK_EMPNO

SQL>

07-01.テーブル【テーブル（TABLE）作成】

■テーブルとは？
　テーブルは実際にデーターが保存される場所でCREATE TABLEコマンドで作成できる。

　-テーブルはデーターベースの基本データー保存単位
　-データーベースはユーザーがアクセス可能なすべてのデーターを保有し、レコードとコラムで構成されている
　-テーブルはシステム内で独立的に使用されたいエンティティを表現できる。
　　例えば、会社で社員や製品に関するオーダーはテーブルで表現可能だ
　-テーブルはエンティティ間の関係を表現できる、
　　つまり、テーブルは社員と作業スキルまたは製品と注文との関係を表現するのに使用できる。
　-テーブル内の外部キー（Forelgn Key）はエンティティ間の関係を表す。
　-コラム：テーブルの各コラムはエンティティの属性を表す。
　-行（ROW,レコード）：テーブルのデーターは行に保存される。

※テーブル作成時の制限と注意点
　-テーブル名とコラムは英文字で始まりA～Zの文字、0～9の数字、$,#,_(under bar)を使用できる。（空白使用不可）
　-テーブルコラム名は30文字以内、予約語は使用不可
　-オラクルテーブル１アカウント内でテーブル名は他テーブル名とかぶってはいけない。
　-１テーブル内で同名のコラムは存在できない、他テーブルのコラム名は同名可能。

■テーブル作成文

CREATE TABLE [schema.]table_name
(column datatype
 [, column datatype ...]
)
 [TABLESPACE tablespace]
 [PCTFREE integer]
 [PCTUSED integer]
 [INITRANS integer ]
 [MAXTRANS integer]
 [STORAGE storage-clause]
 [LOGGING | NOLOGGING]
 [CACHE | NOCACHE];

-schema : テーブルの所有者
-table_name : テーブル名
-column : コラム名
-datatype : コラムのデータータイプ
-TABLESPACE : テーブルがデーターを保存するテーブルスペース
-PCTFREE : ブロック内に存在しているRowへUpdateできるように予約させるブロックの％値を指定
　　※例えば"PCTFREE 20"で設定すると、データー風呂kkの20%を使用可能な空領域として維持し、各ブロック行の更新に使用するって意味！
-PCTUSED : オラクルサーバがテーブルの各データーブロックに対して維持しようとする使用領域の最小サイズを％値で指定する。
　　※例えば"PCTUSED 40"で設定すると、データーブロックの使用領域が39%より小さくならないと新しい行を追加できないって意味！
-INITRANS : １つのデーターブロックに指定できる初期トランザクション値を指定する。
-MAXTRANS : １つのデーターブロックに指定できる最大トランザクション値を指定する。
-STORAGE : エクステントストレージに関する値を指定する。
-LOGGING : テーブルに対して全ての作業がREDOログファイルに記録されるように指定する。
-NOLOGGING : REDOログファイルにテーブル作成と特定のデーターロードを記録しないように指定する。

■テーブル作成例文
　テーブル作成時の注意事項
　-テーブル名を指定して各コラムは"()"で閉じる。
　-コラムの次のデータータイプは必ず指定しなければいけない。
　-各コラムは","で区別、最後は";"で閉める。
　-１つのテーブルで同じコラム名は×、別テーブルでの同じコラム名は○。

--emp2とdept2テーブルを作成する

SQL> CREATE TABLE EMP2(
  2  EMPNO    NUMBER            CONSTRAINT emp_pk_empno PRIMARY KEY,
--   (コラム) (データータイプ)  (制約条件)
  3  ENAME VARCHAR2(20),
  4  JOB   VARCHAR2(40),
  5  MGR   NUMBER,
  6  HIREDATE DATE,
  7  SAL   NUMBER,
  8  COMN  NUMBER,
  9  DEPTNO NUMBER);

表が作成されました。

SQL> CREATE TABLE DEPT2(
  2  DEPTNO NUMBER CONSTRAINT dept_pk_deptno PRIMARY KEY,
  3  DNAME VARCHAR2(40),
  4  LOC VARCHAR2(50));

表が作成されました。

SQL>

■USERが所有しているすべてのテーブル確認

--USER_TABLESデーターライブラリを確認するとユーザーが所有しているテーブルを確認できる。

SQL> SELECT table_name FROM USER_TABLES;

TABLE_NAME
------------------------------
EMP
DEPT
BONUS
SALGRADE
DUMMY
EMP2
DEPT2

7行が選択されました。

SQL>