はじめに
データベース設計してるとデータ型で頭がいっぱいになったので、
ここに書き出しておく
過去にまとめた記事の補強です
※MySQLを基準としています
SQL server と Oracle でもそんな変わらん
※すべてのデータ型や操作関数を列挙しているわけではありません
よく使うらしいものをピックアップしています
数値型
種類
M:桁数、D:小数点以下の桁数
種類 | バイト長 | 意味 |
---|---|---|
TINYINT[(M)] | 1 | 整数 |
SMALLINT[(M)] | 2 | 整数 |
MEDIUMINT[(M)] | 3 | 整数 |
BIGINT[(M)] | 8 | 整数 |
FLOAT[(M,D)] | 4 | 浮動少数点 |
REAL[(M,D)] | 8 | 浮動少数点 |
DECIMAL[(M,[D])] | - | 固定小数点数(最大65桁) 小数点以下の最大桁数は30 |
BOOL | - | 0はfalse、それ以外はtrue |
BIT[(M)] | - | ビット列(最大64桁) |
整数
INTEGER = int
タイプ | サイズ(byte) | 最小値 | 最大値 |
---|---|---|---|
TINYINT | 1 | -128, 0 | 127, 255 |
SMALLINT | 2 | -32768, 0 | 32767, 65535 |
MEDIUMINT | 3 | -8388608, 0 | 8388607, 16777215 |
INT | 4 | -2147483648, 0 | 2147483647, 4294967295 |
BIGINT | 8 | -9223372036854775808, 0 | 9223372036854775807, 18446744073709551615 |
Mysql(MariaDB) では、 INT(4) のように型指定の後に整数値を指定できるが、これは実は「バイト数」ではなく「表示桁数」である。
よって、カラムに格納できる値の範囲が制限されたりすることはなく、実際には11桁(INT型の最大値)まで登録できる。
では、 (?) のカッコ内の数字は何なのか?
オプション属性の ZEROFILL を使用すると、足りない桁は 0(ゼロ) が埋め込まれる(ゼロパディングされる)。 (?) のカッコ内の数字は、その際の桁数である。
したがって、INT(4) ZEROFILL に 9 という数字を格納すると 0009 と表示される。
boolean
内部的には int(11) となる。
tiny(1) を利用するとよい。
完全に0or1にする場合は、bit(1) を利用する。
DECIMAL
必要に応じて0でパディングされ、指定小数点桁以下は切り捨てるので丸め誤差がほぼ生じない
科学計算や金融取引など、高精度な数値計算が必要な場合に適している
最大桁数の DECIMAL(65, 30) は「精度(Precision): 65桁」「スケール(Scale): 30桁」の特性をもつ
これは、整数部分は最大で35桁まで格納でき、小数部分は最大で30桁まで格納できる。
操作関数
関数 | 意味 |
---|---|
ROUND(X) | 四捨五入 |
CEIL(X) | 切り上げ |
FLOOR(X) | 切り下げ |
TRUNCATE(X,D) | 小数点以下D桁まで表示(切り捨て) |
POW(X,Y) | べき乗 |
SQRT(X) | 平方根 |
RAND() | 乱数(0~1.0) |
ABS(X) | 絶対値 |
MOD(X,Y) | 剰余 |
X MOD Y | 剰余 |
X DIV Y | 整数除算 なぜか DIV(X,Y) はない |
日付型と時刻型
種類
種類 | 意味 |
---|---|
DATE | 年月日 |
DATETIME | 年月日時刻 |
TIME | 時刻 |
TIMESTAMP | タイムスタンプ |
YEAR | 年 |
DATE:日付
範囲 | `1000-01-01`から`9999-12-31` |
フォーマット | `YYYY-MM-DD` |
入力可能フォーマット | `YY-MM-DD`, `YYYYMMDD`, `YYMMDD`, `YYYY/MM/DD`, `YYYY.MM.DD` |
DATETIME:日時
範囲 | `1000-01-01 00:00:00.000000`から`9999-12-31 23:59:59.999999` |
フォーマット | `YYYY-MM-DD HH:ii:ss` |
入力可能フォーマット: | `YY-MM-DD HH:ii:ss`, `YYYYMMDDHHiiss`, `YYMMDDHHiiss`, `YYYY/MM/DD HH*ii*ss`, `YYYY.MM.DD HH+ii+ss` |
TIMESTAMP:タイムスタンプ
意味 | エポック(`1970-01-01 00:00:00` UCT)からの秒数 |
範囲 | `1970-01-01 00:00:00.000000` UCTから `2038-01-19 03:14:07.999999` UCT |
TIME:時間
範囲 | `-838:59:59.000000` から `838:59:59.000000` |
フォーマット | `HH-ii-ss` |
YEAR:2桁または4桁の年
範囲 | `1901` から `2155` と `0000` |
`70` から `69` (1970から2069に相当) | |
フォーマット | `YYYY` or `YY` |
表示フォーマット変換
date型やdatetime型の値をフォーマットで指定した文字列に整形することができる
DATE_FORMAT(date, format)
TIME_FORMAT(date, format)
SELECT DATE_FORMAT(purchase_datetime,'%Y年%m月%d日 %H時%i分') FROM events;
SELECT DATE_FORMAT('2035-11-11 03:42:09','%p%l時%i分%s秒');
操作関数(計算)
関数 | 意味 |
---|---|
ADDDATE() | 日付を加減算 |
ADDTIME() | 時刻を加減算 |
DATEDIFF() | 2つの日付の差分 |
DAYOFWEEK() | 曜日インデックス(1:日曜) |
WEEKDAY() | 曜日インデックス(0:月曜) |
select ADDDATE('2020-04-01',25)
select ADDDATE('2020-04-01',-25)
select ADDTIME('03:26:59',1);
select ADDTIME('03:26:59','1:00');
select DATEDIFF('2020-04-01','2024-04-01');
select DAYOFWEEK('2024-06-28');
select WEEKDAY('2024-06-28');
現在日時の取得方法
-- 現在時刻
-- 16:48:41
select current_time();
select curtime();
-- 現在日付
-- 2024-06-14
select current_date();
select curdate();
-- 現在日時
-- 2024-06-14 16:48:42
select now();
文字列型
種類
L:文字列、M:バイト数
種類 | 意味 |
---|---|
char(L) | 固定長文字列 Lは0~255 |
national char(L) | char(L)と同じ |
nchar(L) | char(L)と同じ |
character(L) | char(L)と同じ |
varchar(L) | 可変長文字列 Lは0~255 |
text[(M)] | テキストデータを格納するための可変長文字列型 最大M(デフォルトは65,535)バイト |
tinytext | 最大255バイトのテキストデータ |
mediumtext | 最大16,777,215バイトのテキストデータ |
longtext | 最大4GBのテキストデータ |
blob[(M)] | バイナリデータを格納するための可変長バイナリ型 最大M(デフォルトは65,535)バイト |
tinyblob | 最大255バイトのバイナリデータ |
mediumblob | 最大16,777,215バイトのバイナリデータ |
longblob | 最大4GBのバイナリデータ |
charとvarcharのバイト数
-
char
は固定長なので、空文字でも固定バイト数保持する -
varchar
は可変長なので、「格納するデータのバイト数+1(末尾の'\0')」が必要となる - どちらも最大バイト数(L)を超える文字は切り捨てられる
操作関数
長さ
関数 | 意味 |
---|---|
length(str) | バイト数 |
char_length(str) | 文字数 |
部分文字列取得
関数 | 意味 |
---|---|
left(str,n) | 左端からn文字取得 |
right(ste,n) | 右端からn文字取得 |
mid(ste,i,n) | インデックスiからn文字取得 |
substr() | mid()と同じ(substring()のシノニム) |
substring_index(str,delim,count) | 区切り文字 delim が count 回出現する前の部分文字列を返す |
位置検索
関数 | 意味 |
---|---|
instr(str,substr) | 最初に出現するインデックスを返す |
locate(substr,str[,pos]) | [インデックスpos以降で]最初に出現するインデックスを返す |
position() | locate()のシノニム |
変更
関数 | 意味 |
---|---|
insert(str,pos,len,newstr) | posからlen文字消してnewstrを挿入 実際置換なのでlenを0にすると純粋な挿入 |
concat(str1,str2,...) | 文字列結合 |
format(X,D) | 数値の書式設定 数値Xを3桁区切りの文字列にし、 小数点以下D桁に丸める |
lower(str) | 小文字化 |
upper(str) | 大文字化 |
replace(str,from_str,to_str) | 置換 |
空白の削除
関数 | 意味 |
---|---|
ltrim(str) | 先頭空白を削除 |
rtrim(str) | 末尾空白を削除 |
trim(str) | 先頭と末尾の空白を削除 |
さいごに
他のSQLまとめた記事たちはこちら