ORACLE Bronze SQL 勉強メモ
Sal
sql

ORACLE Bronze SQL 勉強メモ

  1. やばいかもしれないリスト
  2. 作戦
  3. 飛ばすもの
  4. 時間かけるもの
  5. BETWEEN演算子
  6. 複数の制約
  7. EMPLOYEES表を使用した次の句は
  8. INSERTとVALUES
  9. INSER INTOで副問い合わせ
  10. DEFAULTとNOT NULL
  11. ROLLBACK TO
  12. ROLLBACK
  13. ALTER TABLE
  14. 日付書式設定の暗黙変換
  15. 日付データの内部保存
  16. 正規系について
  17. 覚えた方がいい
  18. 覚えた方がいい2
  19. 世紀形
  20. 「&&置換」
  21. UNUSEDマーク
  22. 代替引用符(q)
  23. 列別名とダブルクオーテーション
  24. DISTINCTについて
  25. ESCAPEオプション
  26. 演算子の優先順位
  27. row_limiting_clause
  28. OFFSET句
  29. FETCH句
  30. SUBSTR
  31. INSTR
  32. TRIM関数
  33. REPLACE関数
  34. 文字列の結合
  35. TO_CHAR関数
  36. # 年関連
  37. # 月
  38. # 週
  39. # 日にち
  40. 時間
  41. # その他
  42. 大文字とお文字
  43. 日付書式の表示形式の変更
  44. FM要素
  45. 大文字と小文字
  46. TO_CHAR関数(数値から文字列)
  47. 適切に変換できない場合
  48. 小数点以下のTO_CHARと四捨五入
  49. TO_DATEの使用
  50. TODO:YYとRRの違い
  51. NULLチェック
  52. NVL2(式1, 式2, 式3)
  53. NULLIF(式1, 式2)
  54. DECODE関数
  55. COUNT関数
  56. MIN/MAX関数
  57. AVG/SUM
  58. LISTAGG関数
  59. NULLについて
  60. ネストについて
  61. GROPU関数が使用可能な場所
  62. # GROUP BYについて
  63. # HAVING句
  64. 列別名について
  65. 自然結合
  66. # 3つ以上の結合
  67. USING
  68. ONの使用
  69. 完全外部結合
  70. 複数列副問い合わせ
  71. UNION
  72. UNION ALL
  73. INTERSECt
  74. MINUS
  75. 集合演算子のガイドライン
  76. 集合演算子とORDER BYについて
  77. NULLについて
  78. UPDATEと副問い合わせ
  79. 読み取り一貫性
  80. 排他ロック
  81. CREATE TABLE
  82. DEFAULTオプション
  83. DEFAULTの値
  84. NULLの取扱
  85. VARCHAR2型
  86. CHAR型
  87. LONG型
  88. CLOB型
  89. NCLOB型
  90. RAW型
  91. LONG RAW型
  92. BLOB
  93. BFILE
  94. LOB型
  95. TIMESTAMP型
  96. TIMESTAMP WITH TIME ZONE
  97. TIMESTAMP WITH LOCAL TIMEZONE
  98. 制約一覧
  99. 制約の定義方法
  100. NOT NULL制約
  101. UNIQUE制約(一意キー制約)
  102. PRIMARY KEY制約
  103. FOREGN KEY(外部キー制約):REFERRENCES
  104. ON DELETE CASCADE
  105. ON DELETE SET NULL
  106. CHECK制約
  107. 副問い合わせを使用した表の作成
  108. 列の追加
  109. 制約について
  110. 列の変更
  111. 列の削除
  112. 表モードの変更
## やばいかもしれないリスト
- Union order byの位置 - Union とunion allの違い - 関数の使い方について TO_CHAR TO_DATE TO_NUMBERについて - Create文など - 制約について - Alter table - Alter table row define - Table add row 行のソート - INTERVAL型 曜日(月曜日,日曜日) - 画像データのデータ型 - 副問い合わせを使用した表の作成時 - RR指定の日付データを意図的に変えることができるか - オブジェクト指向のDBとリレーショナル型のDB(rdms) 解説 - REPLACE関数 - 最強のカンペ DECODE
## 作戦
120minで75Q 1Q:1.6min - 30秒-60秒考えて即答できないのを飛ばす。 その代わりチェックは付けておくこと。 - 自信がないものもチェックをつけておくこと。 0.5*75 = 37.5min 60*75 = 75min 結果として、25問/75まで間違えて良い
## 飛ばすもの
- クエリの長さが長いもの - 意味わからないもの この二つは瞬間でぶっ飛ばす 25問ぶっ飛ばしていい
## 時間かけるもの
知識問題
## BETWEEN演算子
A: BETWEEN演算子を指定した場合「列の値は加減以上かつ上限以下 B: NOT BETWEEN演算子を指定した場合「列の値は加減より小さい、または上限より大きい」 BETWEENが境界を含むことを覚えておく
## 複数の制約
CREATE TABLE product ( product_id INT NOT NULL PRIMARY KEY , name CHAR(16) UNIQUE, price INT DEFAULT 2000 ) ;
## EMPLOYEES表を使用した次の句は

select
  job_id,
  AVG(NVL(salary,0))
from
  employees
where
  job_id IN('ST_CLERK', 'ST_MAN')
select句の選択リストに
「「「グループ関数と同時に指定できるのは」」」
GROUP BYで指定された列だけ

よってエラー
## INSERTとVALUES
こんなコードでも正常に実行される

INSERT INTO employees
VALUES(10035,
      'Brown',
      '06-FEB-14',
      (SELECT job_id FROM employees
      WHERE employee_id = 10030),
      72000,
      (SELECT department_id
      FROM
      departments
      WHERE
      department_name = (IT')))
## INSER INTOで副問い合わせ

INSER INTO
(
  SELECT
    employee_id, 
    last_name,
    hire_date,
    job_id
  FROM employees
)
VALUES( 301, 'Green',SYSDATE,
  (
    SELECT
      job_id
    FROM 
      employees
    where 
      employee_id = 101
  )
)
- 副問い合わせに対してINSERTもできる - しかもその結果はemployeesにまで響く
## DEFAULTとNOT NULL

DEFAULTとNOT NULLは

同じ列に定義できるが、
DEFAULTが先!!!
## ROLLBACK TO

トランザクションは

終了しない
## ROLLBACK

トランザクションが

終了する
## ALTER TABLE
別の列から参照されている列を削除するには

.

- 外部キー制約を削除するか(無効ではない)

- ALTER TABLE CASCADE CONSTRANINTS句
を使用する
## 日付書式設定の暗黙変換
+や-などの算術演算子を含む式の前後は、 明示的な型変換を持たない場合は数値への暗黙変換が試みられる。 この時、数値の場合は意味のある変換が可能だが、数値以外の文字が含まれる場合はエラーが発生する デフォルトの書式は「DD-MON-RR」だが MONに合わない場合はMONTHに置き換えられた変換が RRの形式に合わない場合はRRRRに置き換えられた変換が 実施される 例えば任意の英数字以外の1文字を使用して書式文字列の工藤店と一致されることもできる。 よって、「DD/MON/RR」や「DD.MON.RR」なども対応している
## 日付データの内部保存
日付データは内部的には数値形式で保存されている
## 正規系について
- 第一正規系は主キーに対して複数回現れるデータがないこと - 第二正規系は全ての属性が主キーに依存していること(部分関数従属性がない)こと - 第三正規系は主キーではない属性に依存する属性がないこと(推移関数従属性がない)こと
## 覚えた方がいい
SELECT
    TO_CHAR(123456.78, '99,999')
FROM
    dual;
A: 123456,78

B: 123,456.78

C: 指定した桁数よりも桁が多いのでエラーになる

D: 1234,56.78

E: ########
########。 TO_CHARの書式指定は桁数を超えると全て#になる ? 日付書式設定
## 覚えた方がいい2
HIREDATEの曜日順(月から始まり、に社員を並び替えるSQLを選べ

A: SELECT * FROM employees ORDER BY TO_CHAR(hiredate-1, 'DAY')

B: SELECT * FROM employees ORDER BY TO_CHAR(hiredate, 'DAY')

C: SELECT * FROM employees ORDER BY TO_CHAR(hiredate, 'D')

D: SELECT * FROM employees ORDER BY TO_CHAR(hiredate-1, 'D')

E: SELECT * FROM employees ORDER BY TO_CHAR(hiredate+1, 'D')

答え
D: SELECT * FROM employees ORDER BY TO_CHAR(hiredate-1, 'D')

曜日の日付書式は'DAY','DY','D'の三つがある

'DAY'は「日曜日,月曜日,」'DYは「日,月,,,」'D'は「1,2,3,,,,」 と増えていく

そこから一日増やさなければならないため、hiredate-1を指定する。

## 世紀形
正規形
## 「&&置換」
&&はユーザー変数として覚えてくれるバインド変数 &は覚えてくれないバインド変数
## UNUSEDマーク
UNUSEDマークの付け方

ALATER TABLE 表名
SET UNUSED [COLUMN](列名)
UNUSEDマークのついた列の消し方

LATER TABLE 表名
DROP UNUSED COLUMNS
- 削除された列と同様の列の扱いになり、同じ名前の列を作れる - ただし、LONG型はUNUSEDマークをつけることができない - 列の名前の確認や取り消しができない
## 代替引用符(q)
文字リテラルの一部に「'」を使用したい時 - 任意のシングルバイト文字列やダブルバイト文字列 - []{}()<>の組み合わせ 次の例は?を引用デリみたに指定する。

SELECT
  yomi || q'?'s Salary : ?' || sal "Monthly Salary"
FROM
  employees
他の引用符デリミタ「[]」

SELECT
  yomi || q'['s Salary : ]' || sal "Monthly Salary"
FROM
  employees
## 列別名とダブルクオーテーション

SELECT
  employee_id 社員番号
  , name "Name"
  , salary * 12 AS 年間給与
  , salary + 10000 AS "Bonus Money"
FROM
  test5;

覚えたほうがいいこと

スペースや特殊文字を使用する場合は"で囲む必要がある

## DISTINCTについて
DISTINCTは一個のSELECTで一回のみ
## ESCAPEオプション
LIKE演算子では%と_をワイルドカードとして扱うため 100% のような#を含む文字列を指定する場合はESCAPEオプションを設定する必要がある

WHERE 列名 LIKE '文字列パターン' ESCAPE 'エスケープ文字(一文字 |¥,$,#,aなど)'
- サンプルコード

WHERE pname LIKE '100¥%%' ESCAPE '¥'
- サンプルコード2(100%_で始まる文字列)

WHERE pname LIKE '100¥%¥_%' ESCAPE '¥'
## 演算子の優先順位

select
  *
form
  employees
where
  deptno = 10
or
  deptno = 30
and 
  sal >= 300000;
- 計算結果 - BETWEEN - <> - NOT - AND - OR の順で評価される よって、先にAND演算子が評価されるので (deptno = 30 and sal >= 30000)が先 その後にORが評価される(ORは最後)
## row_limiting_clause

書き方


select
  *
from
  表名
[where句]
[ORDER BY句]
[OFFSET offset { ROWS | ROWS } ]
[FETCH { FIRST | NEXT}
       { row_count | percent PERCENT }
       { ROW | ROWS }
       { ONLY | WITH TIES }
]

FETCHのあとは


NEXTかFIRSTの選択肢がある

ROWとROWSの違いは


ない。
どちらも同じだが、ないという選択肢はない
## OFFSET句
スキップする行数 指定しなければ0からスタートする

OFFSET N ROW | ROWS

Nに負の値を指定した場合

は0とみなされる

ROWもROWSも

違いはない
OFFSETが宣言されている時は省略不可
## FETCH句
返される行数か行の割合を表す FETCHを省略すると 最初からスタートする

FETCH { FIRST | NEXT (省略不可,違いはない)} { N | N PERCENT } { ROW | ROWS(省略不可) } { ONLY | WITH TIES }

ONLYは

行数を正確に返す

WITH TIESは

同着も返す

WITH TIESを指定するときは

ORDER BYが必須
- サンプル

ORDER BY sal DESC
OFFSET 5 ROWS
FETCH FIRST 3 ROWS ONLY;
先頭から5行をスキップし 6行目か3行ぶんを取り出している ONLY指定のためSALの値が8行目と同じであっても9行目のデータは表示されない(ROWS WITH TIESなら9行目も取り出す) - PERCENTのサンプル

ORDER BY sal DESC
OFFSET 5 ROWS
FETCH FIRST 50 PERCENT ROWS ONLY;
最初の5行をスキップした後、 全体の14行(50%)を取り出している - OFFSETの省略

ORDER BY sal DESC
FETCH FIRST 3 ROWS ONLY;
最初の3行を取得する - FETCHの省略

ORDER BY sal DESC
OFFSET 5 ROWS
最初の 5行を取得する
## SUBSTR

SUBSTR(文字列, m[,n])
m文字目からn文字分を取り出す関数

mは

負の値でも可能
- サンプルコード

select
  SUBSTR('Oracle Server', 2, 3),
  SUBSTR('Oracle Server', 2)
from
  dual
> rac, > racle Server

mに負の値を設定すると...

文字の後ろから数えてm文字目からn文字ぶんを取り出す

- サンプルコード2

select
  SUBSTR('Oracle Server', -6, 3),
  SUBSTR('Oracle Server', -6)
from
  dual
> Ser, Server
## INSTR
指定した文字パターンが現れる位置を戻す関数

INSTR(文字列1, 文字列2 [,m=1][,n=1])
m文字目から検索を行い,n回目に一致した文字列の位置を返す

最初から最後まで文字がない場合は

0を返す

select
  INSTR('Oracle Server', 'er',1 , 2),
  INSTR('Oracle Server', 'er')
from
  dual;
> 12, 9
## TRIM関数
前後にある削除文字を取り除いて返す関数

TRIM([LEADING | TRAILING | BOTH] [削除文字列] FROM 文字列)
or
TRIM(文字列)
削除文字列は 1文字だけ。 - LEADING 先頭にあるものを取り除く - サンプルコード

select
  TRIM(LEADING 'O' FROM 'Oracle Server')
from
  dual
## REPLACE関数

REPLACE(文字列, 変更前文字列)
- サンプルコード

select
  REPLACE('Oracle Server', 'Server', 'Master'),
  REPLACE('Oracle Server', 'Server')
from
  dual
> Oracle Master, Oracle
## 文字列の結合
文字列 string1 と string2 を連結した (CONCATenated) 文字列を戻す。

引数は常に

2つしかない。
3つ以上の文字列を結合するには関数の中に関数を埋め込んで表現する。

CONCAT ( string1 , string2 )
# 日付関数 日本語環境では「DD-MON-RR(日-月-年)」 英語環境では「RR-MM-DD(年-月-日)」 の順番 (ALTER SESSION SET nls_data_format='表示形式')で帰ることができる
## TO_CHAR関数

TO_CHAR(日付 [,'日付書式'][,NLSパラメータ])
- サンプルコード

select
  TO_CHAR(SYSDATE 'YYYY-MM-DD HH24:MI:SS')
from
  dual;
> 2014-01-24 19:51:46

/ - ( などの半角記号は

そのまま結果に表示される

年や月、「日」などの感じやひらがな、カタカナなどは

"で囲むと表示される(そうでなければエラー)
### 年関連
- SCC or CC 世紀。Sを指定すると紀元前に-がつく - SYYYY or YYYY 年。Sを指定すると日付の先頭に-がtく - YYY or YY or Y 年の下 n桁 - SYEAR or YEAR スペルによる年 - RR 年の下2桁。 YYとは「世紀」の扱いが異なり、 - Q 年の四半期
### 月
- MM 二桁の月 - MONTH 空白が埋め込まれた9文字の長さの名前 「January」など - MON 月の名前の3文字の略称 「Jan」など - RM ローマ数字で表した月
### 週
- WW or W 年または月における週 - IW ISOに基づく年間における週
### 日にち
- DDD or DD or D 年または月、週における日にち - DAY 空白がm埋め込まれた9文字の長さの曜日。 「TUERTHDAY」など - DY 曜日。3文字の略称系。「日」「月」など
## 時間
- AMorPM 午前か午後かを示す要素 - A.M. or P.M. ピリオドを使用したA.M.を示す要素。 日本語だと「午前」「午後」 - HH or HH12 or HH24 時間,時間(1-12),時間(1-24) - MI 分 - SS 秒 - SSSS 午前0時からの経過時間
### その他
- "of the" そのまま反映される二重引用ふ - FM 埋め込みモードの有効と無効を切り替えれる - サンプルコード2

select
  TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
  TO_CHAR(SYSDATE, 'YYY"年"MM"月"DD"日"(DAY)')
from
  dual
> 2014/01/24 2014年01月24日(金曜日) - サンプルコード3

select
  TO_CHAR(SYSDATE, 'YYYY/MM/DD'),
  TO_CHAR(SYSDATE, 'YYY年MM月DD日(DAY)')
from
  dual
> エラーが発生する(二重引用ふを使っていないので)
## 大文字とお文字

select
  sysdate,
  TO_CHAR(SYSDATE, 'Month:Mon:Day:Dy') 日本語環境,
  TO_CHAR(SYSDATE, 'Month:Mon:Day:Dy','nls_date_language = AMERICA') 先頭大文字,
  TO_CHAR(SYSDATE, 'month:mon:day:dy','nls_date_language = AMERICA') 先頭小文字
from
  dual
> 14-01-24 > , 1月 :1月 :金曜日:金 > , January :Jan:Friday :Fry > , javuary :jan:fryday :fri
## 日付書式の表示形式の変更
- サンプルコード

select
  TO_CHAR(hiredate, 'DDth "of" Month, YYYY','nls_data_language = AMERICA'),
from
  employees
where
  deptno = 10
> 25TH of February, 2001 > 02ND of May , 2004

DDthをddthに変えると

「25th」や「02nd」に変わる
- 02ndはsecondという意味か

TH

順序表記( DDTH : 4TH)

SP

スペル表記( DDSP : FOUR)

SPTH or THSP

スペル表記+順序表記

DDSPTH : FOURTH
## FM要素
埋め込みモードを無効にして「数値の先行0」や「文字列の前後に含まれるスペース」が取り除かれて表示される - サンプルコード

select
  ename
    TO_CHAR(hiredate
      'ddth "of" Month, YYYY',
      'nls_date_language = AMERICA')
    TO_CHAR(hiredate
      'fmddth "of" Month, 'YYYY'),
      'nls_data_language = AMERICA')
from
  employees
例一) > 25th of Feburary , 2001 > 25th of Feburary, 2001 Monthの後のスペースがなくなる 例2) > 02nd of May , 2001 > 2nd of May, 2004 先行の0が取り除かれている
## 大文字と小文字

select  
  TO_CHAR(SYSDATE, 'MONTH:MON:DAY:DY')
  TO_CHAR(SYSDATE, 'Month:Mon:Day:Dy'),
  TO_CHAR(SYSDATE, 'month:mon:day:dy'),
from
  dual
> JANUARY :JAN:FRIDAY :FRI > January :Jan:Friday :Fri > january :jan:friday :fri
## TO_CHAR関数(数値から文字列)

9

数値の位置
9の位置で最大表示桁数がわかる
先行0は表示しない

例)99999

結)1234

0

9と同じだが、先行0を表示するという意味
桁数を合わせてくれる

例)099999

結)001234
$:ドル記号

L

ローカル通過の表示

例)L999999

結)¥1234

D(or 「.」)

Dot(小数点)
指定した位置に小数点を表示する

例)999D999
例)999.999

結)1.234

G(or「,」)

Dot(小数点)
指定した位置に小数点を表示する

例)999G999
例)999,999

結)1,234

MI

右に-を表示

例)999999MI

結)1234-

PR

負の値は<>で囲む

例)999999PR

結)<1234>

EEEE

数学的記法で表示

例)99.999EEEE

結)1.234E+03

V

10のn条

例)9999V99

結)123400

S

Sign
符号つき

例)S999999

結)+1234
結)-1234
## 適切に変換できない場合
値を変換できない場合は「#」が出てくる 例)指定した桁数が実際のデータよりも少ない時

select
  TO_CHAR(500000, 'L99,990')
> ##########
## 小数点以下のTO_CHARと四捨五入

先行0の指定は

は小数点でも影響する


select
  TO_CHAR('0.12345', '0,9999')
  TO_CHAR('0.12345', '9.9999')
>0.1235 > .1235
## TO_DATEの使用
日付は暗黙の変換はできない

select
  sysdate - '00-01-01'
from
  dual
> 数値が無効です

select
  sysdate - TO_DATE('00-01-01')
from
  dual
> 4157.81799(日数)
## TODO:YYとRRの違い

YY

受け入れた値を「常に現在の世紀」として扱う

RR

受け入れた値を「現在の年に近い世紀」として扱う

select
  TO_CHAR(TO_DATE('20-10-10', 'YY-MM-DD'), 'YYYY') YY20,
  TO_CHAR(TO_DATE('20-10-10', 'RR-MM-DD'), 'YYYY') RR20,
  TO_CHAR(TO_DATE('95-10-10', 'YY-MM-DD'), 'YYYY') YY95,
  TO_CHAR(TO_DATE('95-10-10', 'RR-MM-DD'), 'YYYY') RR95
from
  dual

実行結果

> YY20 : 2020

> RR20 : 2020

> YY95 : 2095

> RR95 : 1995

YY

受け入れた値を「常に現在の世紀」として扱う
## NULLチェック
- NVL(式1, 式2)

戻り値のデータ型は

式1と同じになる
>引数expr1およびexpr2は任意のデータ型にすることができます。 >2つの引数のデータ型が異なる場合、一方のデータ型が他方のデータ型に暗黙的に変換されます。 >暗黙的に変換できない場合、データベースはエラーを戻します。 NVL(式一, 式2)
## NVL2(式1, 式2, 式3)
式1がNULL以外なら式2を 式1がNULLならば式3を返す

常に式2と同じデータ型と同じになるようにデータが戻り

それができない場合はエラーになる
引数expr1は、任意のデータ型を持つことができます。 

引数expr2およびexpr3は、LONG以外の任意のデータ型を持つことができます。

expr2とexpr3のデータ型が異なる場合、一方のデータ型が他方のデータ型に暗黙的に変換されます。 

暗黙的に変換できない場合、データベースはエラーを戻します。
NVL2
## NULLIF(式1, 式2)
二つの値を比較して、 等しい場合は「NULL」を戻し そうでない場合は式1を戻す。

式1には

NULL以外なら設定できる
- COALESCE(式1, 式2 [,式n]) 式を左からチェックして最初に式1と一致したものを選択する

引数は

全て同じデータ型である必要がある

select
  comm,
  mgr,
  ename,
  COALESCE(comm, mgr, ename)
from
  employees
> データ型が一致しません: NUMBERが予想されましたがCHARです。

select
  comm,
  mgr,
  ename,
  COALESCE(TO_CHAR(comm), TO_CHAR(mgr), ename)
from
  employees
> 正常に実行可能 COALESCE公式リファレンス # IF-THEN-ELSEロジック
## DECODE関数
switch文みたいな感じ

DECODE(式
        ,条件1, 戻り値1
        [,条件2, 戻り値1]
        [, デフォルトの戻り値])

select
  dept, ename, sal
  DECODE(deptno
               , 10, sal * 1.1
               , 20, sal * 1.2
               , sal*1.5) NEW_SAL
from
  employees
# グループ関数 SELECT句とORDER BY句, HAVING句で使用可能。 WHERE句では使用できない
## COUNT関数
COUNTの中身は*とDISTINCTとALLを選べる - * NULLも含む全てのデータの件数 - ALL 重複した値でもそれぞれ1としてカウント。 NULLは無視される - DISTINCT 重複した値を1回だけカウント NULLは無視される
## MIN/MAX関数

引数は

数値型,文字列型, 日付型を指定可能
文字を指定した場合はアルファベット順に並べ替えられる
## AVG/SUM
数値型飲みの列と式のみ指定できる
## LISTAGG関数

LISTAGG( 連結する列名 [, デリミタ] ) WITHIN GROUP( ORDER BY ソート列名)

select
  deptno,
  LISTAGG(ename, ':')
  WITHIN GROUP(order by sal desc) menber_list
from
  employees
GROUP BY deptno;
> 10, 32500, 佐藤:中村:佐々木
## NULLについて
NULLは基本無視される AVGでヌルが入っていたら、MULLを無視したカウントで割る
## ネストについて

ネストは

2レベルまで可能
## GROPU関数が使用可能な場所

GROPU BYは

WHERE句では使用できない

HAVING句で使用可能
### GROUP BYについて

列別名は

指定できない

SELECT句の選択可能な列は

- GROUP BYで指定した列と
- グループ関数のみ指定可能

ORDERBYの指定可能な列は

GROUP BYで指定した列と
グループ関数のみ
指定可能
- サンプル

select
  deptno, job, COUNT(*), AVG(sal)
from
  employees
group by
  deptno;
> GROUP BYの式ではありません ※ GROUP BYに指定している列をselect句に必ず指定する必要はない 知ってるよね。

select
  COUNT(*), AVG(sal)
FROM employees
GROUP BY deptno;
>エラーは出ず、カウントと平均が出てくる - ORDER BYでは必ずGROUP BYで指定している列とグループ関数しか定義できない

select
  deptno 部門番号, MAX(sal) 最高給与
from
  employees
GROUP BY deptno
ORDER BY empno
> GROPU BYの式ではありません:empno
### HAVING句
グループごとに条件を指定したい場合

HAVING句の位置は

WHERE句の後ろ
ORDER BY句の前
## 列別名について
SQLの実行順序

FROM
ON
JOIN
WHERE
GROUP BY
HAVING
SELECT(列別名)
DISTINCT
ORDER BY(列別名可能)
TOP
# 表の結合 表接頭辞を使用した列

両方の列に存在する列を

SELECTで指定するとエラーになる

回避するためには表接頭辞を付けなければならない

select
  deptno, ename, deptno
from
  employees, departments
> 列の定義が未確定です:deptno
## 自然結合
- 明示的に結合条件を指定する必要はない - 共通する列が複数ある場合は、全ての列が結合条件になる

データ型の異なる同名の列があると

エラーになる

自然結合の結合列には

表接頭辞を設定できない。where句でも同様

select
  e.empno, e.name, d.depth, d.name
from
  employees e
natural join
  departments d
> NATURAL結合で使用される列は表接頭辞を指定できません > エラー

select
  e.empno, e.name, depth/* d.depthがないのでOK*/ d.name
from
  employees e
natural join
  departments d
### 3つ以上の結合
NATURAL JOINもUSINGでも3つ以上の表を結合することは可能 - NATURAL JOINを使用する例

sleect
  ordno,
  o.data_orderd
  ,od.quantity
  ,product_name
FROM
  customer c
NATURAL JOIN order o
NATURAL JOIN ord_details od
NATURAL JOIN product p
ORDER BY ordno,prodno;
- USINGを使用する例

sleect
  ordno,
  o.data_orderd
  ,od.quantity
  ,product_name
FROM
  customer c
JOIN order o USING(custno)
JOIN ord_details od USING(ordno)
JOIN product p USING(prodno)
ORDER BY ordno,prodno;
- 2つのキメラも可能

sleect
  ordno,
  o.data_orderd
  ,od.quantity
  ,product_name
FROM
  customer c
NATURAL JOIN order o
JOIN ord_details od USING(ordno)
JOIN product p ON od.prodno = p.prodno
ORDER BY ordno,prodno;
## USING
二つの列に共通する列を指定できる

NAtURAL JOINとUSINGは

同時に使用できない

USING句もNATURAL JOINでも

結合列に表接頭辞をつけるとエラーになる

select
  e.name,
  e.ename,
  d.dname
FROM
  employees e 
JOIN 
  departs d
USING (deptno)
WHERE
  e.deptno IN (10,20)
> エラー
## ONの使用
異なる名前の列を結合したい時など ONは「悲透過結合」や「自己結合」などでも使える USINGは透過結合のみしか使えない - 接頭辞は同じ名前の列を使用する際は必須
## 完全外部結合
JOINの左右に指定された表データを全て取り出すための結合 結合条件を満たさないものも含めて FULL OUTER JOINを使う # 副問い合わせについて

一見も戻さない副問い合わせ

NULLが戻されるので結果も0件になる

単一行用の演算子を使って複数行帰ってきたとき

( < (副問い合わせ, <>(副問い合わせなど))

エラーになる

NOT IN演算子とNULL

NOT IN で指定したリストやサブクエリーの値に NULL が存在すると、常に空の結果セットが返ってきてしまいます。

理由:IN は「=」であるためNOT INは「<>」となる。
ところが、<> NULL の結果は常にFalseになるので、
NULLが存在するだけでどれもFalseになる。
## 複数列副問い合わせ

select
  empno,
  ename,
  sal,
  deptno
from
  employees
where
  (sal, deptno) 
  = (
    select 
      sal, deptno
    from
      employees
    where
      empno = 1013)
  and
    ename <> '山田'
# 集合演算子 A : B : C
## UNION
二つの問い合わせを連結し、重複は一度のみで戻す。 こちらの方が一般的。 A : B : C

内部的にソートした上で重複を廃城するので

実行結果は(ORDER BYを使わずとも)selectの先頭で昇順にソートされる。
同一の値は二つ目の列でソートされる。
## UNION ALL
二つの問い合わせを連結し、重複したものはその数だけ戻す。 A : B : B : C

内部的に重複の削除を行わないので

唯一実行結果もソートされない
## INTERSECt
二つの問い合わせの結果のうち、共通するものを戻す。 : B :

内部的にソートした上で共通部分を取り出すので

実行結果はselectの先頭で昇順にソートされる。
## MINUS
一つ目の問い合わせの結果のうち、二つ目にないものを返す。 a :

内部的にソートした上で

実行結果はselectの先頭で昇順にソートされる。

Aだけ取り出すというその性質上、

一つ目と二つ目の列を入れ替えた時に

MINUSのみが表示されるデータが異なる。
## 集合演算子のガイドライン

個数は

同数に

データ型は

1つ目の問い合わせのデータ型と同じか
同じデータ型グループ(CHARとVARCHAR2など)である必要がある
- サンプルコード

select
  empno from employees
union
select
  deptno from employees
> 正常に実行される

select
  empno from employees
union
select
  ename from employees
> エラーになる(NUMBERとCHAR)
## 集合演算子とORDER BYについて

ORDER BYの位置は

- 最後の集合の後に指定する必要がある

ORDER BYの列指定では

最初のselect句の列と列別名のみ使用可能
## NULLについて
NULLは無視されない

UNION ALLでは

重複したNULLも戻す

UNIONでは

NULLがあれば一件だけ出てくる
# データ操作,トランザクション - INSERT文(想像通り)

INSERT INTO 表名
[( 列名1[,列名2, ...] )]

VALUES( 値1 [,値2...] )
列名は省略できるが、その場合は表の定義と同じ順番で、 全ての値を指定する必要がある。 列の値を明示的にした場合、 列名とVALUESの一対一関係が同じである必要がある。 NULLについては列の名前を省略するか、NULLキーワードを指定すれば良い ただし、NULL?が「NOT NULL」では指定できずエラーになる 関数や副問い合わせの利用も可能 - 副問い合わせを利用したINSERT文

INSERT INTO 表名1 (列名 [,列名2 ...] )
SELECT
  列名 [,列名2 ...] 
FROM
  表名2
...

VALUES句は

指定しない

副問い合わせの()は

必須ではない
INSERTの省略も可能だが、同じ位置に同じ数ではあること。 副問い合わせのSELECTの*の 使用可能不可能のみ不明。多分できない。⇨可能だった

定義が同じテーブルで全件 INSERT する場合

INSERT INTO テーブルA
SELECT * 
FROM   テーブルB
## UPDATEと副問い合わせ
- サンプルコード1

UPDATE
  emp_compy
SET
  job = (
      SELECT
        job
      FROM
        employees
      WHERE
        empno = 1010
  ),
  sal = (
      SELECT
        sal
      FROM
        employees
      WHERE
        empno = 1010
  )
WHERE
  empno = (
    SELECT
      empno
    FROM
      employees
    WHERE
      ename = '加藤'
  )
- サンプルコード2

UPDATE
  emp_compy
SET
  (job, sal) = (
      SELECT
        job, sal
      FROM
        employees
      WHERE
        empno = 1010
  )
WHERE
  empno = (
    SELECT
      empno
    FROM
      employees
    WHERE
      ename = '加藤'
  )
# トランザクション DMLでエラーが出たときは 自動コミットはされず、自動ロールバックされる
## 読み取り一貫性
:ロールバックされる可能性のある未確定のデータは他のセッションからは参照できない
## 排他ロック
FOR UPDATE句を使用すると、SELECTの実行中に行レベルの排他ロックを取得することができる 排他ロックはトランザクションの終了(COMMITされるまで)続く。
## CREATE TABLE

CREATE TABLE スキーマ名.表名
(
    列名 データ型
    [, 列名 データ型
    ...]
)
サンプル

CREATE TABLE empl
(
    empno NUMBER(4),
    ename VARCHAR(10)
)
## DEFAULTオプション
表の作成時に列の定義にデフォルトオプションを追加できる。 INSERT文で値を省略した際にはこの時に設定した値が適応される。 構文

CREATE TABLE [スキーマ名].表名
(
    列名 データ型 DEFAULT デフォルト値
    [,列名 データ型
     ,列名 データ型
     ...
    ]
)
- サンプルコード

CREATE TABLE employees
(
    empno NUMBER(4),
    ename VARCHAR2(20),
    hiredate DATE DEFAULT SYSDATE
)
- INSERT時の挙動

INSERT INTO emp2(empno, ename)
values (10, 'tarou');

SELECT * FROM emp2;
## DEFAULTの値
リテラル値、式またはSQL関数を指定できる。(SYSDATE式やUSER式など)
## NULLの取扱
NULLは無視される # DDLについて 以下の3つのコマンドはDDLである

DDLコマンド3つ

ALTERコマンド(オブジェクトの定義情報を変更する)

DROPコマンド

TRUNCATEコマンド(表が使用しているデータ領域を丸ごと削除)

# DROP TABLE - 表内の全てのデータが削除されう

表に定義されている

索引も削除される

- ただし、完全に削除したわけではなく、ゴミ箱に移動されるのみ - また、シノニムやビューは削除されない - 完全に削除したい場合はPURGE句を使うと良い。

PUERGRのサンプルコード


DROP TABLE empl PURGE;

(削除されたごめ箱を復元するにはFLASHBACK TABLEコマンドを使用する) # データ型
## VARCHAR2型
最大4000バイトまで格納可能。可変長のデータ型

覚えた方がいいこと

サイズの指定が必須

可変長とは、格納するデータのサイズに応じてサイズが変わる。 VARCHAR(10)の列にabcを代入すると「abc」が格納される
## CHAR型
最大2000バイトまで格納可能。固定長のデータ型

覚えた方がいいこと

サイズの指定を省略すると、サイズが「1」になる

固定長とは、表作成時に作成したサイズで一定という意味。 CHAR(10)の列にabcを代入すると「abc_______」が代入される。

覚えた方がいいこと

よって、CHAR(10)の列をLENGTHで測ると、格納したサイズにかかわらず必ず10になる

## LONG型
最大2Gまでの文字データを格納可能

サイズ指定

不可

副問い合わせを使用した表の作成時に

LONG列はコピーできない

GROPU BYとORDER BY句に

指定できない

一つの表に

一つだけ(LONG列またはLONG RAW列)しか定義できない

LONG列には

制約を定義できない

# 以下LOB(Large Object )だが

LOBは全て

サイズ指定不可

## CLOB型
最大4Gまでの文字列を格納できるデータ型
## NCLOB型
最大4GまでのUnicode文字列を格納できるデータ型 # 数値型 NUMBER型を使う

列名 NUMBER[(最大精度[,位取り])]
最大精度は最大桁数:最大38桁 位取り:格納する数値データの小数点以下の桁数を指定する

覚えた方がいいこと

NUMBER(5,2)が指定されている場合
実質的に整数部分は5-2で3桁が上限となる。

123.456を代入すると
データに四捨五入がされて「123.46」が代入される

1234.567を代入すると
整数部分が5-2=3より大きい4桁であるため、エラーになる。
# 日付型 固定長で7バイトのデータ

列名 DATE
世紀、年、月、日、時間、分、秒が内部的な数値形式で格納される。 # バイナリデータ型
## RAW型
バイナリデータ(最大2000)を指定可能。 - サイズの指定はできない
## LONG RAW型
最大2Gまでのバイナリデータを格納可能 - LONGと同等の制約がある
## BLOB
4Gまでのバイナリデータを格納できる。
## BFILE
4Gまでのバイナリデータを格納できる、読み取り専用のデータ型 OS上のファイルイメージに保存されている。
## LOB型
CLOB,BLOB,BFILEはLOB型と呼ばれる。

覚えた方がいいこと

実際には表とは異なる場所に実際のデータを格納し、
費用の中には実際にデータを格納した場所へのポインタ
情報のみを格納して使うことができる。

LONG型に当てはまるような制約はない
(一つの列に複数のLOBを定義できる)

CLOBとBLOBはOracleサーバーのデータファイルにデータとポインタ情報を格納するが、 BFILEはサーバー上のポインタ情報のみを格納する。 # ROWID型

覚えた方がいいこと

全ての表に自動的に登録されている列

どの表も、それぞれの行を一意に識別するROWIDが存在する。(表作成時に宣言しなくとも)
# そのほかのデータ型
## TIMESTAMP型
DATE型を拡張子たデータ型

TIMESTAMP[小数点以下桁数]

秒の使用数点以下の値も格納可能
TIMESTAMPではSYSTIMESTAMPの実行で容易に格納可能
## TIMESTAMP WITH TIME ZONE
タイムゾーンの時差を含むことができる。タイムゾーンの時差は列の一部として格納される alter sessionでローカル日時を変えても変化しない

> CREATE TABLE time3
  (
    timeA INTERVAL YEAR TO MONTH,
    timeB INTERVAL DAY TO SECOND
  );

> INSERT INTO time3
    VALUES(
    INTERVAL '1-2' YEAR TO MONTH,
    INTERVAL '10 12:30' DAY TO MINUTW);

> SELECT 
> TO_CHAR(SYSDATE, 'YYY-MM-DD HH24:MI'),
> TO_CHAR(SYSDATE + timeA, 'YYY-MM-DD')
## TIMESTAMP WITH LOCAL TIMEZONE
タイムゾーンの時差を含むことができる。

覚えた方がいいこと

タイムゾーンの時差は列の一部として格納されず、

データ取得時にローカルセッションのタイムゾーンの値で
表示される

alter sessionでローカル日時を変えても変化しない

# INTERVAL関連 - INTERVAL YEAR TO MONTH 二つの自国の感覚を、年、または月の単位で保存する - INTERVAL DAY TO SECOND 二つの時間の差を、日付から秒単位で格納する サンプルコード

CREATE TABLE time3
(
    timeA INTERVAL YEAR TO MONTH,
    timeB INTERVAL DAY  TO SECOND
);

サンプルコード

INSERT INTO time3
VALUES(
    INTERVAL '1-2'      YEAR TO MONTH,
    INTERVAL '10 12:30' DAY TO MINUTE
);

SELECT
    TO_CHAR( SYSDATE, 'YYY-MM-DD HH24:MI'),
    TO_CHAR( SYSDATE + timeA, 'YYY-MM-DD'),
    TO_CHAR( SYSDATE + timeB, 'YYY-MM-DD HH24:MI')
from
    time3;

# 制約
## 制約一覧
- NOT NULL制約 NULLを許可しない - UNIQUE制約 重複値を許可しない。 NULLは許可する - PRIMARY KEY制約 表内の各業を一意に識別できる値を許可する。 - FOREGN KEY制約 参照先の列にある値、またはNULLのみ許可する - CHECK制約 自前の制約をつけることができる
## 制約の定義方法
制約は

覚えた方がいいこと

列レベル or 表レベルで定義できる

列レベル制約、表レベル制約と呼ぶ
- サンプルコード

CREATE TABLE [スキーマ名].表名
(
    列名 データ型 [列レベル制約 [列レベル制約 ...]],
    ...
    [, 表レベル制約 [,表レベル制約]]
)
- 列レベル制約の基本構造

[CONSTRAINT 制約名] 制約の種類

CREATE TABLE emp1
(
    empno NUMBER(4) CONSTRAINT emp1_empno_pk PRIMARY KEY,
    ename CARCHAR2(20)
)

覚えた方がいいこと

- CONSTRAINT 制約名を省略した場合、
「SYS_Cn」の名前が自動的につけられる

- NOT NULL制約は列レベルでのみ定義可能

- 複数の制約をつける場合は改行かスペースで区切る
- 表レベルの制約の基本構文

[CONSTRAINT 制約名] 制約の種類 (列名,[列名...])
サンプルコード

CREATE TABLE emp2
(
    empno NUMBER(4) ,
    ename VARCHAR2(20) ,
    CONSTRAINT emp2_empno_pk PRIMARY KEY (empno)
)

覚えた方がいいこと

- ()内に制約を定義する列を1つ以上指定すること

- 複数の制約を定義するときは「,」を使う

- 複数の列からなる制約は表レベルで制約が可能
- そのほかのルール

覚えた方がいいこと

- 表作成後にもルールを定義することができる

- その場合は、格納されているデータが追加する制約のルールにしたがっている場合に限る。
## NOT NULL制約
NULLの値を設定できなくなる

覚えた方がいいこと

列レベルでのみ設定可能
## UNIQUE制約(一意キー制約)
重複した値を格納できなくなる制約。

覚えた方がいいこと

- NULLは格納可能

- 複数行にNULLを含めることも可能(NULLは重複しても良い)

- 自動的に制約と同じ名前の一意索引(重複が許可されない索引)が作成される

- 列の組み合わせについての索引は表レベルでなければいけない
- 列の組み合わせについての索引は表レベルでなければいけない、 その場合は()に複数の列を追加する

CREATE TABLE emp6
(
    empno NUMBER(4) CONSTRAINT emp6_empno_nn NOT_NULL,
    ename VARCHAR2(20) CONSTRAINT emp6_ename_nn NOT NULL,
    job VARCHAR2(20),
    deptno NUMBER(3),
    CONSTRAINT emp6_dept_no_job_uk UNIQUE(deptno, job)
)
- 「,」で区切ることが大事
## PRIMARY KEY制約
重複とNULLを許可しなくなる制約

覚えた方がいいこと

- NULLも許可しないことに注意(一意に定まらなければならない)

- 

- 名前の一意索引が作成される

- 組み合わせに対してもPRIMARY KEYは作成できる

PRIMARY KEYの数は

表に一つのみ定義できる
## FOREGN KEY(外部キー制約):REFERRENCES
参照先の列に存在する値しか格納できなくなる

覚えた方がいいこと

- NULLは格納可能である

- 参照先の列は「UNIQUE制約」か「PRIMARY KEY制約」がついていないといけない

- 表レベルの構文と列レベルの構文で異なる
- 列レベルの構文

[CONSTRAINT 制約名] REFERENCES 親表名( 参照する列名 [, 参照する列名])
サンプルコード

CREATE TABLE emp7
(
    empno NUMBER(4),
    ename VARCHAR2(10),
    deptno NUMBER(4) CONSTRAINT emp7_dept1_deptno_fk REFERENCES dept1(deptno)
)
- 表レベルの構文

[CONSTRAINT 制約名] FOREGN KEY(列名[,列名])
REFERENCES 親表名(参照する列名 [,参照する列名])

覚えた方がいいこと

- FOREGN KEYは表レベルでのみ使用
サンプルコード

CREATE TABLE emp8
(
    empno NUMBER(4)
    ename VARCHAR(10),
    deptno NUMBER(4)
    CONSTRAINT emp8_dept1_deptno_fk FOREGN KEY(deptno) REFERENCES dept1(deptno)
)
- 親表とは 参照先の列名のこと - そのほか注意

- 列の組み合わせに対してFOREIGN KEY制約を定義する場合は表レベルの制約にする必要がある
その場合は()を使用する必要がある

覚えた方がいいこと


- 親表で依存されている行を削除する場合は「子」の表から
行を削除する必要がある。

- FOREGIN KEYを定義した表は依存する行がなくても、子の表があるかぎり削除できない。

## ON DELETE CASCADE
子の表に親表を参照する行が存在する場合に 親表の依存されている行を削除すると 「子の表の行も同時に削除される」 親が倒れると子も倒れる

CREATE TABLE emp9
(
    empno NUMBER(4),
    ename VARCHAR2(20)
    deptno NUMBER(4) CONSTANT emp9_dept2_deptno_fk
    REFERENCES dept2(deptno)
    ON DELETE CASCADE
)
## ON DELETE SET NULL
子の表に親表を参照する行が存在する場合に 親表の依存されている行を削除すると 子の参照している行にNULLが設定される

CREATE TABLE emp9
(
    empno NUMBER(4),
    ename VARCHAR2(20)
    deptno NUMBER(4) CONSTANT emp9_dept2_deptno_fk
    REFERENCES dept2(deptno)
    ON DELETE SET NULL
)
## CHECK制約
地震で指定した条件に対してTRUEまたはNULLを戻す値しか格納できなくなる (FALSEのみNG) 基本的に where句と同じ条件を指定できるが、以下の行為は禁止


- ほかの行の値を参照すること

- SYSDATE
  
- USER

- CURRVAL,NWXTVAL,ROWNUMの呼び出し

サンプルコード

CREATE TABLE emp11
(
    empno NUMBER(4),
    ename VARCHAR2(20)
    sal NUMBER(7) CONSTRUCT
    emp11_sal_ck
    CHECK(sal > 0 AND sal < 1500000)
)

複数の列でCHECK制約をする場合は、表レベルである必要がある

CREATE TABLE emp11
(
    sal NUMBER(4),
    comm NUMBER(20),
    CONSTRAINT emp12_sal_ck CHECK(sal > 0 and sal < 1500000),
    CONSTRAINT emp12_com_ck CHECK(comm < sal)
)

## 副問い合わせを使用した表の作成
データ型は指定できない。 元々のがコピーされる

CREATE TABLE 表名
AS
副問い合わせ

列名の指定は省略できる。

その場合、select句で指定した列名または列別名と同じ名前の列が作成される。
ただし、副問い合わせのselectで計算式や関数を使用している場合は、

列名を指定するか

列別名を指定する必要がある。

列名を指定すると、その名前が表に定義される

ただし、その場合はselect句のリストと同じ数にする必要がある

制約については

NOT NULL以外はコピーされない。(PRIMARY KEYもコピーされない)
- サンプルコード

CREATE TABLE dept_copy
AS
SELECT * FROM departments;
上記もNOT NULL制約以外はコピーされない - エラーが出るサンプルコード

CREATE TABLE emp10
AS
SELECT empno, sal, sal*12
FROM employees
WHERE deptno = 10
> 式に列の別名を指定する必要があります

CREATE TABLE emp10(empno, sal, annsal)
AS
SELECT empno, sal, sal*12
FROM employees
WHERE deptno = 10
- 表構造だけのコピー

CREATE TABLE emp_copy2
AS
SELECT * FROM employees
WHERE 1 = 2;
絶対にTRUEにならない条件を指定することで表構造だけをコピーすることもできる。 # 表構造の変更 ALTER TABLEを使用することで表を後から変更できる - 新しい列を追加する - 列のデータ型を変更する - デフォルト値を設置絵する - 列を削除する - 列の名前を変更する - 読み取り、書き込みモードに変更する - 読み取り専用モードにする
## 列の追加
列の追加もALTER TABLEコマンドを使う 新しく追加された列にはNULLが含まれている

ALTER TABLE 表名
ADD (
    列名 データ型 [DEFAULT 式][列レベル制約]
    [,列名 データ型 [DEFAULT 式][列レベル制約]]
);
- サンプルコード

CREATE TABLE emp14
(
    empno number(4),
    ename varchar2(10)
);
## 制約について

既存の行が制約のルール

既存の行が制約のルールにしたがっている場合のみ、
列レベルの制約を使用して制約を追加できる。
- NOT NULL制約の付け加え方 「新しく追加された列にはNULLが含まれている」という性質から NOTNULL制約をつけるには次の方法がある

覚えた方がいいこと

表自体がからの場合
DEFAULT オプションを指定してデフォルトにNULL以外を設定すること
サンプルコード

ALTER TABLE emp14
(
    sal number(8) NOT NULL
);
> (既存のデータがあると)エラーになる

ALTER TABLE emp14
(
    sal number(8) DEFAULT  200000 NOT NULL
);
> 既存の行は200000が設定される
## 列の変更

変更可能な要素

データ型
サイズ
デフォルト値

ALTER TABLE MODIFIY 
(
    列名 [データ型] [DEFAULT 式]
)

サイズ,精度については

いつでも増加できる

減少は次の場合に可能

データがない
NULLしかない
既存の列の最大値未満にはできない

NULLだけの場合

データ型を変更できる

NULL以外でも

サイズを変更しない場合は
CHARとVARCHAR2の行き来はできる

デフォルトの設定は

以降の表への挿入に適応される
## 列の削除

ALTER TABLE 表名 DROP (列名 [,列名])

対象の列にデータが

存在する場合も存在しない場合も可能

列が0になるにように削除することは

できない

列の削除は

元に戻せない

依存される主キーは

CASCADEオプションを指定しない限り削除できない
## 表モードの変更
「読み取り/書き込みモード」と「読み取り専用モード」の2種類がある

ALTER TABLE 表名 (READ WRITE | READ ONLY);

ただし

表の削除はできる



覚えた方がいいこと



覚えた方がいいこと

title:ORACLE Bronze SQL 勉強メモ