SQL DML：データ型
目次
一覧
| タイプ | データ型 | 最大 | 桁未指定時 | 備考 | 例 |
|---|---|---|---|---|---|
| 文字型 | CHAR | 2,000byte | 1byte | 固定長 | ‘AAA ‘ |
| VARCHAR2 | 4,000byte | エラー | 可変長 | ‘AAA’ | |
| LONG | 2G | 可変長 | ‘AAA’ | ||
| CLOB | 4G | 可変長 | ‘AAA’ | ||
| 数値型 | NUMBER | 38桁 | 整数38桁 | 111.11 | |
| 日付型 | DATE | 年月日 | 内部で時分秒を保持している | ‘2016-01-01’ | |
| TIMESTAMP | DATE ＋ 秒以下 | DATE ＋ 秒以下(6桁) | ‘2016-01-01 10:00:00.333000’ | ||
| INTERVAL YEAR TO MONTH | |||||
| INTERVAL DAY TO SECOND | |||||
| バイナリ型 | RAW | 2,000byte | |||
| LONG RAW | 2G | ||||
| BLOB | 4G | ||||
| BFILE | 4G | ||||
| ROWID型 | ROWID | 一位のBASE64文字列 | AAASRSAAEAAAASOAAA |
| データ型 | コード | 備考 |
|---|---|---|
| 整数型 | TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT | 論理型を含む |
| 浮動小数点型 | FLOAT/DOUBLE | |
| 固定小数点数型 | DECIMAL/NUMERIC | |
| BIT型 | BIT | |
| 日付/時刻型 | DATE/TIME/DATETIME/TIMESTAMP/YEAR | |
| 文字型 | CHAR/VARCHAR | |
| 文字列型 | BLOB/TEXGT | |
| バイナリ型 | BINARY/VARBINARY | |
| 列挙型 | ENUM | |
| セット型 | SET |
型の種類
CHAR
文字列型(固定長)
n：1～2000
省略可。省略した場合n：1
指定した桁未満の値を格納した場合、末尾にスペースが自動で割り当てられる。
VARCHAR2
文字列型(可変長)
n：1～2000
省略不可
指定した桁未満の値を格納した場合でも、その値で格納される。
末尾にスペースは割り当てられない
LONG/CLOB
Long型
文字列型(可変長)
最大2GB
表につき1列のみ使用可
CLOB型で代用可
Where/OrderBy/GroupBy/サブクエリー適用不可
CLOB型
LONG型の改良型：文字列型(可変長)
表につき1列のみ使用可→制限無し
NUMBER/NUMERIC
数値(小数点を含めた桁数)、小数点(桁指定)
p：1～38
s：-84～127
Number(3,1)
1234.5→エラー
123.45→134.5
数値(小数点を含めた桁数)、小数点(桁指定)
999.99
-99.99
日付/日時型
DATE型
内部で時刻を持っているが、デフォルトでは表示されない。
デフォルト書式：”RR-MM-DD”
TIMESTAMP型
内部で時刻を持っており、デフォルトで表示される。
デフォルト書式：”RR-MM-DD HH24:MI:SS”
INTERVAL YEAR TO MONTH型
年と月の期間
INTERVAL DAY TO SECOND型
日、時、分、秒の期間
YYYY-MM-DD
Time型
hh:mm:ss[. nnnnnnn]型
DateTime型
YYYY-MM-DD hh:mm:ss[. nnn]
RR年書式(2000年問題対応)
99/01/01(1999/01/01のつもり)
select to_date(’99-01-01′,’YY/MM/DD’) from mytable;
→99-01-01
99/01/01(1999/01/01のつもり)→4桁へ
select to_char(to_date(’99-01-01′,’YY-MM-DD’),’YYYY/MM/DD’) from mytable;
→2099/01/01 ※不足分はシステム日付(2015/～/～)の西暦(2000)から補われる
select to_char(to_date(’99-01-01′,’RR-MM-DD’),’YYYY/MM/DD’) from mytable;
→1999/01/01 ※不足分はシステム日付(2015/～/～)の西暦-1(1999)から補われる
【2000年～2049年】
00/01/01(2000/01/01のつもり)
select to_date(’00-01-01′,’YY/MM/DD’) from mytable;
→00-01-01
00/01/01(2000/01/01のつもり)→4桁へ
select to_char(to_date(’00-01-01′,’YY-MM-DD’),’YYYY/MM/DD’) from mytable;
→2000/01/01 ※不足分はシステム日付(2015/～/～)の西暦(2000)から補われる
select to_char(to_date(’00-01-01′,’RR-MM-DD’),’YYYY/MM/DD’) from mytable;
→2000/01/01 ※不足分はシステム日付(2015/～/～)の西暦(2015)から補われる
指定日と現在の年数の下二桁によって上二桁が変化する。
指定された年が 0～49、現在が 0～49 → 現在の年の上二桁
指定された年が50～99、現在が 0～49 → 現在の年の上二桁 － 1 (1900年代)
指定された年が 0～49、現在が50～99 → 現在の年の上二桁 ＋ 1 (2100年代)
指定された年が50～99、現在が50～99 → 現在の年の上二桁
※現在：2000年代の場合
OBJECT型
表とは別場所にデータを保存、表にはポインタ情報のみを格納する事も可能
1つの表に複数のObject型を定義可能
・CLOB Char Large Object
文字型データを格納
・BLOB Binary Large Object
バイナリ型データを格納
・BFILE Binary File
OS上のファイルへのポインタ情報を格納
TYPE・ROWTYPE型
＝指定表指定列と同じ型
DECLARE
value1 emp.ename1%TYPE;
emp表ename1列と同じ型に定義される
value2 emp.ename2%TYPE;
emp表ename2列と同じ型に定義される
BEGIN
SELECT結果を変数へ格納
SELECT ename1, ename2INTO value1, value2 FROM emp WHERE ～ = 1;
型が同じなので格納できる
value1 := ～
value2 := ～
END;
【ROWTYPE型】
＝指定表内の列と同じ型(複数列)
＝構造体
DECLARE
tmpTable emp%ROWTYPE;
emp表内の列と同じ列を持つ表を定義
BEGIN
tmpTable.Col1 := 10;
emp.Col1と同じ型
tmpTable.Col2 := ‘XXXXXXX’;
tmpTable.～ := ～;
empが持つ列と同じ列を操作できる
END;
ユーザ定義型
COL1 NUMBER,
COL2 VARCHAR(8)
COL3 emp.COL3%TYPE
);
TEST TESTRECORD;
TEST.COL1:=1
TEST.COL2:=’XXXXX’
SELECT結果の格納
SELECT * INTO TEST FROM (表名) WHERE ～
SELECT結果(複数列)をまとめて格納できる
JSONB型
col1,
col2,
data->’col3′,
data->’col4′
from
table
;
insert into 表名 valus(1, ‘aaa’, ‘{“col3”:1, “col4″:”aaa”}’);
update 表名 set JSON型列名 = ‘{“col3”:1, “col4″:”aaa”}’ where ～;
JSON項目の個別updateはSQL DML：基本操作(CRUD) 値を指定して更新参照
NULL値
NULL：「どこのリテラル領域も参照していない」の意味。
0でも空文字(”)でもない。
NULL値の計算、連結
「SQL DML：演算子」参照
WHERE句におけるNULL値の抽出方法
SELECT ～ FROM ～ WHERE 列名 IS NULL
SELECT ～ FROM ～ WHERE 列名 IS NOT NULL
SELECT ～ FROM ～ WHERE 列名 = NULL は×
SELECT ～ FROM ～ WHERE 列名 <> NULL は×
NULLの表示順については「SQL DML：基本操作(CRUD)」参照
NULLの集計については「SQL グループ関数/NULLの集計」参照
NULL値の判定・変換については「SQL 変換関数/NULL値の判定・変換」参照
固定長と可変長の違い
固定長
Char(8)：’a_______’
可変長
Varhar2(8)：’a’
SQLにおける文字表現
日付型、文字、文字列型は’で囲む
’01-JAN-15’、’AAA’
文字としての’を表現する場合は”と続ける。
‘Aを含む文字列を持つレコードを選択する場合
WHERE 列名 LIKE ‘%”A%’
代用引用符
代用引用符で囲まれた文字列は”もスペースもそのまま文字列として解釈される。
代用引用符に使用する文字列は何でも良い
Q’文字 ～ 文字‘
q’文字 ～ 文字’
Q’AB’B”B”’A’
→B’B”B”’
Q'(” ”)’
→” ”
Q’A～a’
→エラー
文字連結
「SQL DML：演算子」参照
WHERE句における文字指定
任意の文字列
SELECT ～ FROM ～
任意の文字列(空文字”含む)
WHERE ～ LIKE ‘検索文字%’
⇒検索文字AAA、検索文字AA、検索文字A、検索文字
WHERE ～ LIKE ‘%検索文字‘
⇒AAA検索文字、AA検索文字、A検索文字、検索文字
WHERE ～ LIKE ‘%検索文字%’
⇒AAA検索文字、AA検索文字、A検索文字、検索文字、検索文字A、検索文字AA、検索文字AAA
任意の一文字
任意の一文字(空文字”含まない)
WHERE ～ LIKE ‘検索文字_’
⇒検索文字A
WHERE ～ LIKE ‘_検索文字‘
⇒A検索文字
WHERE ～ LIKE ‘_検索文字_’
⇒A検索文字A
特殊文字のエスケープ
特殊文字を含む文字列を指定する場合に利用
例 ‘_A’ という文字列を含むデータを選択する場合
(この場合の’_’は任意の一文字ではなく、文字としての’_’)
WHERE ～ LIKE ‘%\_A%’ ESCAPE ‘\‘
⇒AAA_A、AA_A、AA_A、_A、_AA、_AAA、_AAAA
※\の後の文字は特殊文字(ワイルドカード)では無く、文字であるという宣言
※ESCAPE に続く一文字は何でも良い( _ % 以外)
‘100%_’
で始まる文字列を検索する場合
WHERE 列 LIKE ‘100\%\_%’ ESCAPE ‘\’;
\%\_%
||
\% \_ %
||
%(文字) _(文字) %(ワイルドカード)
暗黙の型変換
列(型：数値)←’数値’：OK
※数値に変換される
列(型：文字列型)←数値：OK
※文字列に変換される
