SQLのコア関数

コア関数

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【文字列の文字数を取得(length関数)】
書式
length(文字列)

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> create table product(id integer, namee text, namej text);
sqlite> insert into product values(1, 'Apple', 'りんご');
sqlite> insert into product values(2, 'Car', '車');
sqlite> insert into product values(3, 'Television', 'テレビ');
sqlite> insert into product values(4, 'Mobile', '携帯');
sqlite> select namee, length(namee), namej, length(namej) from product;
namee length(namee) namej length(namej)
---------- ------------- ---------- -------------
Apple 5 りんご 3
Car 3 車 1
Television 10 テレビ 3
Mobile 6 携帯 2
sqlite>

【文字列をすべて大文字または小文字に変換(lower関数, upper関数)】
書式：文字→小
lower(文字列)
または
書式：文字→小
upper(文字列)

【SQL文】
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table fruit(id integer, name text);
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> insert into fruit values(1, 'Apple');
sqlite> insert into fruit values(2, 'KiwiFruit');
sqlite> insert into fruit values(3, 'Peach');
sqlite> insert into fruit values(4, 'Strawberry');
sqlite> select * from fruit;
id name
---------- ----------
1 Apple
2 KiwiFruit
3 Peach
4 Strawberry
sqlite> select name, lower(name), upper(name) from fruit;
name lower(name) upper(name)
---------- ----------- -----------
Apple apple APPLE
KiwiFruit kiwifruit KIWIFRUIT
Peach peach PEACH
Strawberry strawberry STRAWBERRY
sqlite>

【文字列を別の文字列に置換(replace関数)】
書式
replace(対象の文字列, 置換される文字列, 置換する文字列)

replace 関数を使って name カラム に格納されている値のなかの 2018 をすべて 2019 に変更した値を取得
SQL文
sqlite> create table report(id integer, name text);
sqlite> insert into report values(1, 'report_2018_Tokyo');
sqlite> insert into report values(2, 'report_2018_Kyoto');
sqlite> insert into report values(3, 'report_2018_Nagoya');
sqlite> insert into report values(4, 'report_2017_Tokyo');
sqlite> .header on
sqlite> .mode column
sqlite> select * from report;
id name
---------- -----------------
1 report_2018_Tokyo
2 report_2018_Kyoto
3 report_2018_Nagoy
4 report_2017_Tokyo
sqlite> select name, replace(name, '2018', '2019') from report;
name replace(name, '2018', '2019')
----------------- -----------------------------
report_2018_Tokyo report_2019_Tokyo
report_2018_Kyoto report_2019_Kyoto
report_2018_Nagoy report_2019_Nagoya
report_2017_Tokyo report_2017_Tokyo
sqlite>

【substr関数の使い方】
書式
substr(文字列, 開始位置, 文字の長さ)
substr(文字列, 開始位置)
SQL文
sqlite> create table staff(id integer, name text, address text);
sqlite> insert into staff values(1, 'Yamada', '01:Tokyo');
sqlite> insert into staff values(2, 'Suzuki', '02:Osaka');
sqlite> insert into staff values(3, 'Hori', '03:Sapporo');
sqlite> insert into staff values(4, 'Kudou', '01:Tokyo');
sqlite> insert into staff values(5, 'Oota', '04:Nagoya');

substr 関数を使って address カラム に格納されている値の中から4番目の文字から3文字分だけ部分文字列を取得
sqlite> select id, name, substr(address, 4, 3) from staff;
id name substr(address, 4, 3)
---------- ---------- ---------------------
1 Yamada Tok
2 Suzuki Osa
3 Hori Sap
4 Kudou Tok
5 Oota Nag
sqlite>

【文字列の先頭と最後から空白文字を取り除く(trim関数, rtrim関数, ltrim関数)】
trim関数→指定した文字列の先頭や最後に付いている空白文字を取り除く
書式
trim(文字列)
trim(文字列, 文字)

rtrim 関数→文字列の最後から指定した文字を削除する
書式
rtrim(文字列)
rtrim(文字列, 文字)

ltrim 関数→文字列の先頭から指定した文字を削除する
書式
ltrim(文字列)
ltrim(文字列, 文字)

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table msgdb(id integer, msg text);
sqlite> insert into msgdb values(1, ' Hello! ');
sqlite> insert into msgdb values(2, ' Good Bye ');
sqlite> insert into msgdb values(3, '__Thank you__');
sqlite> .header on
sqlite> .mode column
sqlite> select id, quote(trim(msg)), quote(ltrim(msg)), quote(rtrim(msg)) from msgdb;
id quote(trim(msg)) quote(ltrim(msg)) quote(rtrim(msg))
---------- ---------------- ----------------- -----------------
1 'Hello!' 'Hello! ' ' Hello!'
2 'Good Bye' 'Good Bye ' ' Good Bye'
3 '__Thank you__' '__Thank you__' '__Thank you__'
sqlite>
msg カラムに格納されている値の中から先頭と最後にあるアンダーバー(_)を取り除いた文字列を取得してみる。
sqlite> select id, trim(msg, '_'), ltrim(msg, '_'), rtrim(msg, '_') from msgdb;
id trim(msg, '_') ltrim(msg, '_') rtrim(msg, '_')
---------- -------------- --------------- ---------------
1 Hello! Hello! Hello!
2 Good Bye Good Bye Good Bye
3 Thank you Thank you__ __Thank you
sqlite>
【文字列をシングルクォーテーションで囲った値を取得(quote関数)】
書式
quote(値)

SQL文
sqlite> create table datadb(id, data);
sqlite>
sqlite> insert into datadb values(1, 'Melon'); insert into datadb values(2, 18); insert into datadb values(3, 3.52); insert into datadb values(4, 'I''am a student');
sqlite> select * from datadb;
id data
---------- ----------
1 Melon
2 18
3 3.52
4 I'am a stu
sqlite> select id, data, quote(data) from datadb;
id data quote(data)
---------- ---------- -----------
1 Melon 'Melon'
2 18 18
3 3.52 3.52
4 I'am a stu 'I''am a st
sqlite>
カラムに格納されている値に対して数値はそのままで文字列についてはシングルクォーテーションで囲った値を取得する

【数値を四捨五入した値を取得する(round関数)】
書式
round(数値)
round(数値, 桁)

SQL文
sqlite> create table point(id integer, point real);
sqlite> insert into point values(1, 15.4853);
sqlite> insert into point values(2, 27.143);
sqlite> insert into point values(3, 38.902);
sqlite> insert into point values(4, 26.5521);
sqlite> insert into point values(5, 30.36);
sqlite> select * from point;
id point
---------- ----------
1 15.4853
2 27.143
3 38.902
4 26.5521
5 30.36
sqlite> select id, point, round(point) from point;
id point round(point)
---------- ---------- ------------
1 15.4853 15.0
2 27.143 27.0
3 38.902 39.0
4 26.5521 27.0
5 30.36 30.0
sqlite>
sqlite> select id, point, round(point, 1), round(point, 2) from point;
id point round(point, 1) round(point, 2)
---------- ---------- --------------- ---------------
1 15.4853 15.5 15.49
2 27.143 27.1 27.14
3 38.902 38.9 38.9
4 26.5521 26.6 26.55
5 30.36 30.4 30.36
sqlite>
【数値の絶対値を取得する(abs関数)】
書式
abs(数値)
例文
abs(0.47); /* 0.47 */
abs(-19); /* 19 */
abs(NULL); /* NULL */
abs('-18.5'); /* 18.5 */
abs('pen'); /* 0.0 */

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> create table test(id, data);
sqlite>
sqlite> insert into test values(1, 18);
sqlite> insert into test values(2, -7.4);
sqlite> insert into test values(3, NULL);
sqlite> insert into test values(4, 'Flower');
sqlite> insert into test values(5, '-16');
sqlite>
sqlite> select id, data, abs(data) from test;
id data abs(data)
---------- ---------- ----------
1 18 18
2 -7.4 7.4
3
4 Flower 0.0
5 -16 16.0
sqlite>

【整数の乱数を生成(random関数)】
書式
random()

SQL文
sqlite> select random() % 10, abs(random()) % 10;
random() % 10 abs(random()) % 10
------------- ------------------
9 1
sqlite>

SQLの日付と時刻を取得する関数

SQLの日付と時刻を取得する関数

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【日付と時刻の取得方法】
それぞれの関数は日付と時刻を取得する為に使用する

書式
date(timestring, modifier, modifier, ...)
time(timestring, modifier, modifier, ...)
datetime(timestring, modifier, modifier, ...)
julianday(timestring, modifier, modifier, ...)
strftime(format, timestring, modifier, modifier, ...)

各関数のフォーマット
date関数 'YYYY-MM-DD'
time関数 'HH:MM:SS'
datetime関数 'YYYY-MM-DD HH:MM:SS'
julianday関数 'J'

■date関数、time関数、datetime関数を使った日付と時刻の取得
SQL文
sqlite> .header on
sqlite> .mode column
sqlite> select date('2018-05-12 13:15:42') as date,
...> time('2019-08-04 08:42:19') as time,
...> datetime('2019-11-30 21:16') as datetime;
date time datetime
---------- ---------- -------------------
2018-05-12 08:42:19 2019-11-30 21:16:00
sqlite>
現在の日付と時刻を取得するには
SQL文
sqlite> select date('now') as date,
...> time('now') as time,
...> datetime('now') as datetime;
date time datetime
---------- ---------- -------------------
2020-07-30 02:28:08 2020-07-30 02:28:08

■julianday関数を使った日付と時刻の取得
指定した日時からユリウス日を取得する。

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> select julianday('2019-08-04 08:42:19') as julianday;
julianday
----------------
2458699.86271991
sqlite>
■strftime関数を使った日付と時刻の取得
フォーマット
%Y 年: 0000-9999
%m 月: 01-12
%d 日
%W 年の初めからの週数: 00-53
%j 年の初めからの経過日数: 001-366
%w 曜日: 0-6 (日曜日が0、土曜日が6)
%H 時: 00-24
%M 分: 00-59
%S 秒: 00-59
%f 秒＋ミリ秒: SS.SSS
%s 1970-01-01からの経過秒数
%J ユリウス日
%% %文字そのもの

SQL文
sqlite> select strftime('%H:%M:%f', 'now') as strftime;
strftime
------------
07:08:21.984
sqlite>
sqlite> select strftime('経過秒数 %s秒', 'now') as strftime;
strftime
----------------
経過秒数 1596179369秒
sqlite>

■Modifiers(修飾子)の使い方
sqlite> select datetime('2020-07-04', '+1 days') as datetime;
datetime
-------------------
2020-07-05 00:00:00
sqlite>
sqlite> select datetime('2020-07-04', '-4 hours') as datetime;
datetime
-------------------
2020-07-03 20:00:00
sqlite>
sqlite> select datetime('2020-07-04','+2 days', '-4 hours') as datetime;
datetime
-------------------
2020-07-05 20:00:00
sqlite>
最初の日と最初の時間
sqlite> select datetime('2019-05-04 21:16', 'start of year') as datetime;
datetime
-------------------
2019-01-01 00:00:00
sqlite> select datetime('2019-05-04 21:16', 'start of month') as datetime;
datetime
-------------------
2019-05-01 00:00:00
sqlite> select datetime('2019-05-04 21:16', 'start of day') as datetime;
datetime
-------------------
2019-05-04 00:00:00
sqlite>
指定した曜日の日
sqlite> select datetime('2019-05-04', 'weekday 0') as datetime;
datetime
-------------------
2019-05-05 00:00:00
sqlite> select datetime('2019-05-04', 'weekday 3') as datetime;
datetime
-------------------
2019-05-08 00:00:00
sqlite>

SQLite集計関数--2

SQLite集計関数--2

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【指定したカラムに含まれる値の最大値と最小値を取得(max関数, min関数)】
書式；最大値
max(カラム名)
書式；最小値
min(カラム名)
【SQL文】
sqlite> select * from user;
name point gender
---------- ---------- ----------
Yamada 72 Man
Suzuki 94 Woman
Honda 58 Woman
Nishi 82 Man
sqlite> select max(point), min(point) from user;
max(point) min(point)
---------- ----------
94 58
sqlite>
【指定したカラムに含まれる値を連結して表示(group_concat関数)】
書式
group_concat(カラム名)

【SQL文】
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> create table user(id integer, name text, gender text);
データ入力
sqlite> insert into user values(1, 'Honda', 'Man'); insert into user values(2, 'Suzuki', 'Woman'); insert into user values(3, 'Kojima', 'Man'); insert into user values(4, NULL, 'Man'); insert into user values(5, 'Sakai', 'Woman');
データ出力
sqlite> select * from user;
id name gender
---------- ---------- ----------
1 Honda Man
2 Suzuki Woman
3 Kojima Man
4 Man
5 Sakai Woman
sqlite>
・group_concat 関数を使って name カラムの値を連結
sqlite> select group_concat(name) from user;
group_concat(name)
-------------------------
Honda,Suzuki,Kojima,Sakai
sqlite>
・gender カラム毎にグループ化して連結
sqlite> select gender, group_concat(name) from user group by gender;
gender group_concat(name)
---------- ------------------
Man Honda,Kojima
Woman Suzuki,Sakai
sqlite>
・連結するときの区切り文字を指定する
sqlite> select group_concat(name, '+') from user;
group_concat(name, '+')
-------------------------
Honda+Suzuki+Kojima+Sakai
sqlite>

SQLiteの集計関数--1

SQLiteの集計関数

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【指定したカラムまたはテーブル全体の行数をカウント(count関数)】
書式
count(カラム名)
・例文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .table
color earn kantou product test user1
dept foods olduser staff user
テーブル
sqlite> drop table user;
sqlite>
テーブル作成
create table user(id integer, name text, address text, gender text);
sqlite>
sqlite> insert into user values(1, 'Honda', 'Tokyo', 'Man');
sqlite> insert into user values(2, 'Suzuki', 'Nagoya', 'Woman');
sqlite> insert into user values(3, 'Kojima', 'Kyoto', 'Man');
sqlite> insert into user values(4, 'Utada', NULL, 'Man');
sqlite> insert into user values(5, 'Sakai', 'Tokyo', 'Woman');
sqlite>
count 関数を使って name カラムと address カラムの行数を取得
sqlite> .header on
sqlite> .mode column
sqlite>
sqlite> select count(name), count(address) from user;
count(name) count(address)
----------- --------------
5 4
sqlite>
【指定したカラムに含まれる値の合計を取得(sum関数, total関数)】
書式
sum(カラム名)
または
total(カラム名)

SQL文
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> create table uriage(shiten text, sales);
sqlite> .header on
sqlite> .mode column
sqlite> insert into uriage values('Osaka', 450);
sqlite> insert into uriage values('Nagoya', 380);
sqlite> insert into uriage values('Ciba', 95);
sqlite> select * from uriage;
shiten sales
---------- ----------
Osaka 450
Nagoya 380
Ciba 95
sqlite> select sum(sales), total(sales) from uriage;
sum(sales) total(sales)
---------- ------------
925 925.0
sqlite>
【指定したカラムに含まれる値の平均を取得(avg関数)】
avg 関数の書式
avg(カラム名)

SQL文
テーブル作成
sqlite> create table user(name text, point integer, gender text);
データインス
sqlite> insert into user values('Yamada', 72, 'Man');
sqlite> insert into user values('Suzuki', 94, 'Woman');
sqlite> insert into user values('Honda', 58, 'Woman');
sqlite> insert into user values('Nishi', 82, 'Man');
表示
sqlite> select * from user;
name point gender
---------- ---------- ----------
Yamada 72 Man
Suzuki 94 Woman
Honda 58 Woman
Nishi 82 Man
avg 関数
sqlite> select avg(point) from user;
avg(point)
----------
76.5
sqlite>
・平均を計算するカラムにNULLおよび文字列が含まれていた場合
sqlite> insert into user values('Togawa', NULL, 'Man');
sqlite> insert into user values('Furuta', 'NODATA', 'Woman');
sqlite> select * from user;
name point gender
---------- ---------- ----------
Yamada 72 Man
Suzuki 94 Woman
Honda 58 Woman
Nishi 82 Man
Togawa Man　←NULLは除外
Furuta NODATA Woman
sqlite> select avg(point) from user;
avg(point)
----------
61.2＝(72＋94＋58＋82)÷5
sqlite>

SQLIteの自己結合

同じテーブルを対象に結合する(自己結合)

【開発環境】
OS：Win10（64ビット）
データベース：SQLite3
コマンドプロンプト入力

【自己結合】
内部結合や外部結合では通常複数のテーブルを結合しますが、自分自身のテーブルと結合をすることも可能です。このような結合を自己結合と呼んでいます。

・外部結合の書式
SELECT 別名1.カラム名, ...
FROM テーブル名 別名1 LEFT OUTER JOIN テーブル名 別名2
ON 別名1.カラム名 = 別名2.カラム名;
実践
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> .tables
color earn kantou product user
dept foods olduser test user1
sqlite> create table staff(id integer, name text, bossid integer);
sqlite> insert into staff values(1, 'Yamada', 3);
sqlite> insert into staff values(2, 'Itou', 4);
sqlite> insert into staff values(3, 'Suzuki', 5);
sqlite> insert into staff values(4, 'Ueda', 3);
sqlite> insert into staff values(5, 'Kuroda', 0);
sqlite> .header on
sqlite> .mode column
sqlite> select person.id, person.name, boss.name from staff person
...> left outer join staff boss
...> on person.bossid = boss.id;
id name name
---------- ---------- ----------
1 Yamada Suzuki
2 Itou Ueda
3 Suzuki Kuroda
4 Ueda Suzuki
5 Kuroda
sqlite>
赤い所はテーブルが存在してない

・内部結合の書式
SELECT 別名1.カラム名, ...
FROM テーブル名 別名1 INNER JOIN テーブル名 別名2
ON 別名1.カラム名 = 別名2.カラム名;
実践
sqlite> select person.id, person.name, boss.name from staff person
...> INNER join staff boss
...> on person.bossid = boss.id;
id name name
---------- ---------- ----------
1 Yamada Suzuki
2 Itou Ueda
3 Suzuki Kuroda
4 Ueda Suzuki
sqlite>