SQLのデータ型全部まとめた

# はじめに
データベース設計してるとデータ型で頭がいっぱいになったので、
ここに書き出しておく

~~過去にまとめた記事の補強です~~

※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桁）<br>小数点以下の最大桁数は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 と表示される。

[参考サイト(old)](https://www.mk-mode.com/blog/2014/02/04/mysql-type-of-integer/)
[参考サイトその２](https://pentan.info/sql/mysql/mysql_type_num.html)
[公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/numeric-types.html)

### boolean
内部的には int(11) となる。
tiny(1) を利用するとよい。
完全に0or1にする場合は、bit(1) を利用する。

### DECIMAL
必要に応じて0でパディングされ、指定小数点桁以下は切り捨てるので丸め誤差がほぼ生じない
科学計算や金融取引など、高精度な数値計算が必要な場合に適している

`DECIMAL(M, D)` の形式で定義される
M: 総桁数（整数部と小数部の合計桁数）。最大値は65
D: 小数点以下の桁数。最大値は30
例）`DECIMAL(10, 2)` : 整数部が10桁、小数部が2桁であることを意味する

### 操作関数
|関数|意味|
|-|-|
|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|整数除算<br>なぜか`DIV(X,Y)`はない|

[公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/numeric-functions.html)

## 日付型と時刻型
### 種類
|種類|意味|
|---|---|
|DATE|年月日|
|DATETIME|年月日時刻|
|TIME|時刻|
|TIMESTAMP|タイムスタンプ|
|YEAR|年|

### DATE：日付
<table>
<tr><td>範囲</td><td>`1000-01-01`から`9999-12-31`</td></tr>
<tr><td>フォーマット</td><td>`YYYY-MM-DD`</td></tr>
<tr><td>入力可能フォーマット</td><td>`YY-MM-DD`, `YYYYMMDD`, `YYMMDD`, `YYYY/MM/DD`, `YYYY.MM.DD`</td></tr>
</table>

### DATETIME：日時
<table>
<tr><td>範囲</td><td>`1000-01-01 00:00:00.000000`から`9999-12-31 23:59:59.999999`</td></tr>
<tr><td>フォーマット</td><td>`YYYY-MM-DD HH:ii:ss`</td></tr>
<tr><td>入力可能フォーマット：</td><td>`YY-MM-DD HH:ii:ss`, `YYYYMMDDHHiiss`, `YYMMDDHHiiss`, `YYYY/MM/DD HH*ii*ss`, `YYYY.MM.DD HH+ii+ss`</td></tr>
</table>

### TIMESTAMP：タイムスタンプ
<table>
<tr><td>意味</td><td>エポック（`1970-01-01 00:00:00` UCT）からの秒数</td></tr>
<tr><td>範囲</td><td>`1970-01-01 00:00:00.000000` UCTから `2038-01-19 03:14:07.999999` UCT</td></tr>
</table>

### TIME：時間
<table>
<tr><td>範囲</td><td>`-838:59:59.000000` から `838:59:59.000000`</td></tr>
<tr><td>フォーマット</td><td>`HH-ii-ss`</td></tr>
</table>

### YEAR：2桁または4桁の年
<table>
<tr><td rowspan="2">範囲</td><td>`1901` から `2155` と `0000`</td></tr>
<tr><td>`70` から `69` （1970から2069に相当）</td></tr>
<tr><td>フォーマット</td><td>`YYYY` or `YY`</td></tr>
</table>

### 表示フォーマット変換
date型やdatetime型の値をフォーマットで指定した文字列に整形することができる

`DATE_FORMAT(date, format)`
`TIME_FORMAT(date, format)`

```sql
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秒');
```

[参考サイト](https://www.javadrive.jp/mysql/function/index49.html)

### 操作関数（計算）
|関数|意味|
|-|-|
|ADDDATE()|日付を加減算|
|ADDTIME()|時刻を加減算|
|DATEDIFF()|２つの日付の差分|
|DAYOFWEEK()|曜日インデックス（１：日曜）|
|WEEKDAY()|曜日インデックス（０：月曜）|

```sql:sample.sql
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');
```

[公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/date-and-time-functions.html)

### 現在日時の取得方法
```sql
-- 現在時刻
-- 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)|固定長文字列<br>Lは0~255|
|national char(L)|char(L)と同じ|
|nchar(L)|char(L)と同じ|
|character(L)|char(L)と同じ|
|varchar(L)|可変長文字列<br>Lは0~255|
|text[(M)]|テキストデータを格納するための可変長文字列型<br>最大M(デフォルトは65,535)バイト|
|tinytext|最大255バイトのテキストデータ|
|mediumtext|最大16,777,215バイトのテキストデータ|
|longtext|最大4GBのテキストデータ|
|blob[(M)]|バイナリデータを格納するための可変長バイナリ型<br>最大M(デフォルトは65,535)バイト|
|tinyblob|最大255バイトのバイナリデータ|
|mediumblob|最大16,777,215バイトのバイナリデータ|
|longblob|最大4GBのバイナリデータ|

### charとvarcharのバイト数
* `char` は固定長なので、空文字でも固定バイト数保持する
* `varchar` は可変長なので、「格納するデータのバイト数＋１(末尾の'\0')」が必要となる
* どちらも最大バイト数（L）を超える文字は切り捨てられる

[公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/char.html)

### 操作関数
#### 長さ
|関数|意味|
|-|-|
|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を挿入<br>実際置換なのでlenを0にすると純粋な挿入|
|concat(str1,str2,...)|文字列結合|
|format(X,D)|数値の書式設定<br>数値Xを３桁区切りの文字列にし、<br>小数点以下D桁に丸める|
|lower(str)|小文字化|
|upper(str)|大文字化|
|replace(str,from_str,to_str)|置換|

#### 空白の削除
|関数|意味|
|-|-|
|ltrim(str)|先頭空白を削除|
|rtrim(str)|末尾空白を削除|
|trim(str)|先頭と末尾の空白を削除|

[公式ドキュメント](https://dev.mysql.com/doc/refman/8.0/ja/string-functions.html)
[参考サイト](https://www.wakuwakubank.com/posts/332-mysql-sql-function-string/)
[参考記事](https://qiita.com/n-i-e/items/f0b7652cbda3b9d02f60)

# さいごに
他のSQLまとめた記事たちはこちら

https://qiita.com/takekota/items/8c6a1a346d382529a9ba

https://qiita.com/takekota/items/884ae36790f5fa465475

https://qiita.com/takekota/items/47e57625e15d4f570b95