SQLのコア関数--2
【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力
コア関数
【BLOB型の値を16進表記に変換(hex関数)】
書式
hex(BLOB型の値)
SQL文
sqlite> .header on
sqlite> .mode column
sqlite> select hex('Aa');
hex('Aa')
----------
4161→16 進表記
sqlite> select hex('ABC');
hex('ABC')
----------
414243
sqlite>
【BLOB型の値の乱数を生成(randomblob関数)】
・randomblob 関数は BLOB 型の値の乱数を生成するために使用
書式
randomblob(バイト数)
SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> select hex(randomblob(2)), hex(randomblob(3));
hex(randomblob(2)) hex(randomblob(3))
------------------ ------------------
EC0D 21B400
sqlite>
【指定バイト数分のBLOB型の0x00の値を生成(zeroblob関数)】
zeroblob 関数は BLOB 型の値の 0x00 を生成するために使用
書式
zeroblob(バイト数)
SQL文
sqlite> select hex(zeroblob(2)), hex(zeroblob(3));
hex(zeroblob(2)) hex(zeroblob(3))
---------------- ----------------
0000 000000
sqlite>
【直近に実行されたSQL文により更新/削除/追加された行数を取得(changes関数)】
・changes 関数は直近の SQL 文が行の追加/削除/更新を行った場合に処理された行数を取得するために使用
sqlite> insert into staff values(1, 'Yamada', 'Tokyo');
sqlite> insert into staff values(2, 'Suzuki', 'osaka');
sqlite> insert into staff values(3, 'Endou', 'Tokyo');
sqlite> insert into staff values(4, 'Honda', 'Nagoya');
sqlite> insert into staff values(5, 'Nishi', 'osaka');
sqlite> insert into staff values(6, 'Harada', 'Tokyo');
sqlite> insert into staff values(7, 'Egawa', 'Nagoya');
sqlite>
sqlite> select changes();
changes()
----------
1
sqlite>
更新
sqlite> update staff set address = 'Osaka' where address = 'osaka';
sqlite> select changes();
changes()
----------
2
削除
sqlite> delete from staff where address = 'Tokyo';
sqlite> select changes();
changes()
----------
3
sqlite>
【データベース接続後に実行されたSQL文により更新/削除/追加された行数のトータルを取得(total_changes関数)】
・total_changes 関数はデータベースに接続後に追加/削除/更新のSQL文によって処理された行数のトータル数を取得するために使用
書式
total_changes()
sqlite> select total_changes();
total_changes()
---------------
12
これまで処理されたコマンドの行数
【値がNULLだった場合は指定した別の値を返す(ifnull関数, coalesce関数)】
・ifnull 関数は 1 番目に指定した引数の値が NULL だった場合に 2 番目の引数の値を返します
書式
ifnull(値1, 値2)
・coalesce 関数は 1 番目から n - 1 番目の引数の値の中で最初に NULL でない値を返します
書式
coalesce(値1, 値2, ..., 値n)
SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
datadb msgdb point report staff test
sqlite> create table user(id integer, name text, handle text);
sqlite> insert into user values(1, 'Yamada', 'Mars');
sqlite> insert into user values(2, 'Suzuki', NULL);
sqlite> insert into user values(3, NULL, 'Octopus');
sqlite> insert into user values(4, 'Honda', 'Shooter');
sqlite> insert into user values(5, NULL, NULL);
sqlite> .header on
sqlite> .mode column
sqlite> select id, ifnull(name, 'NoName') from user;
id ifnull(name, 'NoName')
---------- ----------------------
1 Yamada
2 Suzuki
3 NoName
4 Honda
5 NoName
sqlite>
ifnull 関数を使って name カラム に格納されている値が NULL 以外だった場合は name カラムの値を返し、 NULL だった場合に 'NoName' を返すようにしてみます
sqlite> select id, coalesce(name, handle, 'NoName') from user;
id coalesce(name, handle, 'NoName')
---------- --------------------------------
1 Yamada
2 Suzuki
3 Octopus
4 Honda
5 NoName
sqlite>
【指定した値と等しければNULLを返す(nullif関数)】
nullif 関数は2つの値を比較し、同じであれば NULL を返し異なっていれば最初の値を返します
書式
nullif(値1, 値2)
・SQL文
sqlite> select * from tokuten;
name point
---------- ----------
Yamada 84
Suzuki 73
Hori -1
Endou 91
sqlite> select avg(point) from tokuten;
avg(point)
----------
61.75
sqlite> select avg(nullif(point, -1)) from tokuten;
数値が -1 のカラムは何らかの理由で除外したい場合の平均値
avg(nullif(point, -1))
----------------------
82.6666666666667
sqlite>
【最後に追加されたデータのROWIDを取得(last_insert_rowid関数)】
・last_insert_rowid 関数は最後に追加した行の ROWID を取得するために使用します
書式
last_insert_rowid()
・SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table staff(id integer, name text);
sqlite>
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> insert into staff values(1, 'Honda');
sqlite> insert into staff values(2, 'Suzuki');
sqlite> insert into staff values(3, 'Maeda');
sqlite>
sqlite> select * from staff where rowid = last_insert_rowid();
id name
---------- ----------
3 Maeda
sqlite>
【テーブルに格納されている値のデータ型を取得(typeof関数)】
typeof 関数は値のデータ型を取得するために使用します。
書式
typeof(値)
SQL文
sqlite> create table test1(id integer, data none);
sqlite> insert into test1 values(1, 3);
sqlite> insert into test1 values(2, 15.24);
sqlite> insert into test1 values(3, 'Peach');
sqlite> insert into test1 values(4, NULL);
sqlite> insert into test1 values(5, zeroblob(2));
sqlite> select * from test1;
id data
---------- ----------
1 3
2 15.24
3 Peach
4
5
typeof 関数を使って data カラム に格納されている値のデータ型を取得してみます
sqlite> select data, typeof(data) from test1;
data typeof(data)
---------- ------------
3 integer
15.24 real
Peach text
null
blob
sqlite>
【SQLiteのバージョン情報を取得(sqlite_version関数)】
sqlite_version関数は実行中のSQLiteのバージョン情報を返します
書式
sqlite_version()
SQL文
sqlite> select sqlite_version();
sqlite_version()
----------------
3.31.1
sqlite>
