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
Number(p,s)/Number(p)
数値(小数点を含めた桁数)、小数点(桁指定)
p:1~38
s:-84~127
Number(3,1)
1234.5→エラー
123.45→134.5
Numeric(p,s)
数値(小数点を含めた桁数)、小数点(桁指定)
999.99
-99.99
日付/日時型
‘で囲む必要あり。’2014-04-01’
DATE型
内部で時刻を持っているが、デフォルトでは表示されない。
デフォルト書式:”RR-MM-DD”
TIMESTAMP型
内部で時刻を持っており、デフォルトで表示される。
デフォルト書式:”RR-MM-DD HH24:MI:SS”
INTERVAL YEAR TO MONTH型
年と月の期間
INTERVAL DAY TO SECOND型
日、時、分、秒の期間
Date型
YYYY-MM-DD
Time型
hh:mm:ss[. nnnnnnn]型
DateTime型
YYYY-MM-DD hh:mm:ss[. nnn]
RR年書式(2000年問題対応)
【2050年~2099年】
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型
【TYPE型】
=指定表指定列と同じ型
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;
ユーザ定義型
TYPE TESTRECORD IS RECORD (
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型
select
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’Aa’
→エラー
文字連結
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
※文字列に変換される