集合演算子

UNION ALLを除き、第1列の昇順で出力はソートされる。
ORDER BY句は集合演算子のSQLでも一番最後に記述する。

NOT NULL制約

制約には、列レベルで定義する列制約と表レベルで定義する表制約があるが、NOT NULL制約は列制約でのみ定義することができます。

小数の計算が整数値だった場合

フォーマットを指定いない限り、整数部だけで表示される。
(".00"のような出力にはならない。)


SELECT 0.01 * 100 FROM DUAL;
A.)
1

LONG

【特徴】
可変長の文字データ型
VARCHAR2よりも最大長が長い。(最大2Gバイト)

【制約】
1テーブルで1列しか指定できない。
制約は定義できない。
ORDER BY句またはGROUP BY句に含めることができない。
副問合せを使用して表を作成する場合、LONG列はコピーされない。

※VIEWにLONG型を含めることは可能。

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

年月の間隔を表す列データの型

TO_NUMBER

引数に日付を入れることはできない。

USING

・usingで指定する列は、表名で修飾するとエラーになる。
・usingで指定する列はselect句で指定する場合も同じで、表名で修飾してはならない。
※これは自然結合でも同じ。結合に使用される列が表名で修飾してはならない。
※もちろん、usingで指定しない列は表名で修飾することができる。

日付の演算

select sysdate + 7 from dual;
現在日時の7日後が表示される。

単一行関数

・ひとつの行に対してひとつの値を返す関数
(※一方、複数の行の値をもとにひとつの値を返すのはグループ関数)

・select,where,およびorder by句で使用できる。

表領域

【表領域とスキーマの関係について】
関連はない。
1つのスキーマに属したオブジェクトを複数の表領域で管理することも可能。

索引

【B*Tree索引について】
null値のデータは含まないため、「is null」による検索では索引を引用しないらしい。
対処法として以下がある。
・nullを特定の値に置き換える。
・ビットマップ索引を使用する。

【表領域について】
表と索引は別の表領域で管理することもできる。
分けることでI/Oの競合を低減させることができる。

ワイルドカード

『%』
0以上の任意の文字の連続
『_』
任意の一文字

※エスケープ方法
e.x
select empno from emp where job like '%sa¥_%' escape '¥';

CASCADE

【DROP時のCASCADE】
・表削除時に参照している別表の外部キー制約を削除することができる。
※子表データの削除ではないことに注意。
E.X.)
drop table テーブル名 cascade constraints

【外部キー生成時のCASCADE】
・on delete cascade
親表の行削除時、子表で外部キーで参照している行も削除される。
・on delete set null
親表名の行削除時、子表で参照している外部キー値がnullにまする。
E.X.)
alter table hoge add constraint fk_col foreign key(col) references parenttbl(col) on delete cascade.

【DELETE時のCASCADE】
ちょっとわからない。調査中

LITERAL

select句でリテラルを記述可能。
単一引用符が含まれる場合、代替引用符(q)を使用して、独自デリミタを指定できる。
※『代替引用符演算子』と呼ぶ。

E.X.)
select q'<'s product>' from dual.

q''の中を引用符デリミタで囲み、その中にリテラルを記述する。
上記の例文の場合、<>が引用符デリミタとなる。
引用符デリミタはほかにもシングルバイト,マルチバイト,(), [],{}がつかえる。

CHECK

テーブルの列に指定する制約

・制約条件にSysdate,uid,user,userenvを使用するこよは出来ない。
・一つの列に複数のcehck制約をつけることができる。

NULL

・Nullの比較は、『>』『<』どちらの場合でも、trueにならない。
E.X.)
select sysdate from dual where 1 < null;
select sysdate from dual where 1 > null;
A.)どちらも検索結果件数は0になる。

UNIQUE

留意事項)
Uniqueつけた列にnot null制約をつけることができる。
※PRIMARY KEYとの違いはわからない。

VIEW

・VIEWの削除権限
所有者またはdrop any view権限

留意事項)
・VIEWはパフォーマンス向上の目的で使用されることはない。

COALESCE

NVLを一般化した関数。
引数の最初のNULLではない値を返す。
引数の数は可変長だが2つ以上から。

E.X.)
select coalesce(null,'hoge',null) from dual;
A.) ''hoge

複合キー

複合キー
・複合キーの一部の列に対してNULLを入れることはできない。
・一部のキー列に対して、新規に索引をつけることはできる。

SYNONYM

『プライベート・シノニム』と『パブリック・シノニム』が存在する。

●プライベート・シノニム
・スキーマ内でのみ有効なシノニム。
・ユーザが作成し、そのユーザ自身が使う。

●パブリック・シノニム
・oracleデータベース全体で有効なシノニム。
・DBAユーザもしくはcreate public synonym権限を持つユーザが作成できる。
・シノニムのもととなるオブジェクトの利用権限を持つユーザが利用できる。
※スキーマ指定は不要。オブジェクトの所有者を隠すことができる。

※create public synonym権限はシステム権限
オブジェクト所有者は所有するオブジェクトのすべての権限を持ってる
取り消しはできない。

●ビューとの違い
所有者以外のビューへのアクセスはビューに対するオブジェト権限が必要。
シノニムの場合、基礎表へのアクセス権限が必要。

NULLIF

引数は2つ
第１引数=第２引数が同じ場合：nullを返す。
第１引数<>第２引数の場合：第一引数値を返す。
第１引数がnullの場合、エラーとなる。

select nullif(1,1) ansa, nullif(1,2) ansb from dual;

ansa : null
ansb : 1

DECODE

・ORACLE独自のCASE WHEN
※もちろんSQL標準のCASE WHENも存在する。
select decode(3,1,'a1',2,'a2','default') from dual;
上記の場合、答えはdefault

ちなみにdefaultが定義されていない場合、答えはnullとなる。

各種データ型の特徴

・date, long,clob型
桁数を指定できない。

MAX、MIN

・グループ関数の一つ。詳細は略。

Select age, max(*) from student;
一見うまくいきそうだが、エラーになる。
『グループ関数と他の列は指定できない。』という掟がある。
※上記の場合、group byでageを指定すればうまくいく。が期待結果は異なるな。

グループ関数同士であれば、正常に処理され、SQL結果が出力される。
Select min(*), max(*) from student;

NULLS FIRST, NULLS LAST

・ソートがかかるとき、NULL値は『最大値』として扱われる。
・『NULLS FIRST』『NULLS LAST』で明示的にソート時のNULL値の出力結果を変えることができる。

order by emp_no asc nulls first

DEFAULT

【概要】
表作成時に指定する。（表作成後につけることも可能)

DEFAULTには、リテラル、式、SQL関数などを使用することができる。
※他の列名や擬似列は使用できない。

e.x.)
create table emp
(
empno varchar2(10) not null,
registdate date default sysdate
)


【DEFAULT値を指定してDMLを実行する】
INSERT、UPDATE時に明示的に指定することができる。

E.X.)
※UPDATE
update テーブル set 項目 = default;
※INSERT
INSERT INTO USER_MASTER (
USER_ID, DEPT_NO, USER_NAME, CREATED_ON, MODIFIED_ON
) VALUES (
'0020', '1001',
'小泉 純一',
DEFAULT,
NULL
) ;

副問い合わせ

・GROUP BY句では副問い合わせできない。
※イメージ沸かないし。
GROUP BY句以外なら、どの句でもできる。

※『複数列副問合せ』というのがある。
Select deptno,ename,Sal from emp
where (deptno,Sal) in
(select deptno,max(Sal) from emp group by dept no);

With check option

VIEWの定義時に指定することができる。
ビューに対するupdate, insert文が検査される。
条件に合わない場合は、エラーが生じて操作は拒否される。

e.x.)
create view sales_employee
as
select emp_id,emp_fname,emp_place,dept_id
from employee
where dept_id = 200
with check option;

VIEWからVIEWを作成することもできるのだが、もちろんwith check optionの内容は
継承される。

with read only

VIEWの定義時に指定する。
VIEWを通じての操作は、読み取りだけ。(読み取り専用)

e.x.)
create view emp_view
as select detno,count() cnt from emp
group by deptno
with read only.

INSERT

・INSERT文では通常、列を指定するが、指定しない場合は『全部の値』を指定する必要がある。

e.x.テーブル構成が以下の場合
empno number(4)
ename varchar2(15)
job. varchar2(10)
deptno. Number(2)

以下のSQLはエラーとなる。全部の列の値を指定しなければならない。
Insert into emp values(1000,'scott','clerk')

TRUNCATE

・表の所有者かdrop any table権限を持っている必要がある。

DELETEとの違い
・DDLなのでロールバック不可
・データの領域を解放する。
・削除トリガーを起動しない。
・参照整合性制約の親表は削除不可。

TRANSACTION

●TRANSACTIONの終了条件
・commitの実行
・rollbackの実行
・DDL文(create,alter,truncate,dropなど)の実行(暗黙のコミット)
・DCL文(GRANT,REVOKEなど)の実行(暗黙のコミット)
・SQL*Plus SQL Developerの終了(暗黙のコミット)
・マシン障害やシステムクラッシュの発生

FOREIGN KEY

・親キーは主キーか一意キーであること。

集合演算子

・Union intersect minus
いずれも重複行は除外する。

・union all
例外で重複値もそのまま表示する。デフォルトでソートはされない。

なぜかというとUnion intersect minusは重複除外するために必然的にソートしてしまうのだ。

Purge

10gからゴミ箱機能が追加された。
ゴミ箱に捨ててから取り出すことができる。(※フラッシュバックテーブル機能)
ゴミ箱に入れずに直接削除したい場合、『purge』をつける。
drop table hoge purge;

USING

usingは自然結合と異なる部分がある。
・結合列の名前は同じだが、データ型が異なる場合に使用する。
※自然結合でやるとエラーになってしまう。
・複数の列名が一致するが、その一部だけを結合列として使用する。

SEQUENCE

【構文】
create sequence 順序名
[start with 開始番号]
[increment by 増分値]
[minvalue 最小値 | nominvalue]
[maxvalue 最大値 | nomaxvalue]
[cache キャッシュ数 | nocachr]
[cycle | nocycle];

【留意事項】
・sequenceは、順序名以外はデフォルト設定があるため、順序名だけでも作成できる。
E.X.)
create sequence seq_no1;
※Startwithのデフォルト値は1。なので一回目のnextvalの値は2。

【留意事項(その2)】
・順序の変更は順序の所有者またはALTER権限を持つユーザができる。
・ALTER SEQUENCE文で変更した値は、次の生成時から有効
・START WITHオプションは変更できない。
・現在の採番済みの順序番号よりも小さいMAXVALUEに変更することはできない。

※cacheはパフォーマンスに影響するらしいが、bronzeでは、細かく問われないので無視する。

order by句

order by句は絶対に最後。

集合演算子の場合も、selectが二つあるが、一番最後にしか記述できない。
※副問い合わせの場合は副問い合わせ一つでSQLが完結してるので記述可能。

having句

Having句はgroup byの前後どちらでも構わない。
またhaving句はgroup byがなくても使用できる。

列別名

【列別名の構文】
・列名の後に続けて空白後、列別名を記述する。
select sysdate today from dual;

・長いSQL構文の場合、わかりにくくなるため、ASをつけてもよい。
select sysdate as today from dual;

・列別名の命名規則に違反した特殊記号や空白、先頭が数字で始まる名前を使用したい場合
select sysdate "Hello World" from dual;
select sysdate as "Hello World" from dual;
※二重引用符であることに気をつける！！


留意事項）
・列別名はhaving, where ,group by では指定できない。
理由）
from句、where句、group by句、having句、select句、order by句の順に評価される。
なので列別名はorder byでしか指定できない。
※しかし、副問合わせで生成されたテーブルの場合は、主問い合わせでは指定できる。

留意事項(その2)
表結合時、列名を表名で修飾するが、1つの表にしかない列も修飾することで、
どの表から取り出すかを解析する負荷が軽減されるため、パフォーマンスがアップする。