3/31 SQLite入門

個人的リマインド用

比較的軽いやつ

データベースの用語

データベースの中にテーブルを作る(幾つでも作れるよ)
テーブルは表のようになっている(title,body,idとかがある)
行(横)のことをRecord/Row,列(縦)のことをField/Columnという

こういう表を扱う言語をSQL(Structured Query Languageという)

データベースの操作

SQLite起動

sqlite3 [ファイル名]

その後.helpでいろいろ見れるし、
.exitやControl+dで強制終了

データベースに対して操作しないと、ファイルは作られない

テーブル作成

作成
create table posts(テーブル名) ();(例)
テクニック(もしpostsが既にあったらエラーになる)
create table if not exists posts (); こうしたら大丈夫

ちなsqliteでは作成時のコマンドで大文字小文字を区別しない

フィールド設定

create table if not exists posts (
    id,
    title,
    body
);

テーブルを見る

テーブルリスト
.tables

テーブル構成
.schema [テーブル名] テーブル名書かなかったら全てのテーブル

テーブル削除

削除
drop table [テーブル名];

作成同様のテクニック
drop table if exists posts;

テーブルの名前変更・フィールド追加

名称変更
alter table [元名称] rename to [新名称];

フィールド追加
alter table [テーブル名] add column [フィールド名] [型];

レコードの挿入

insert into [テーブル名] (フィールド名, フィールド名) values ('title', 'body1');

sqliteでは文字列は''で囲む
''の中で'を使いたい時は''2個書く
改行したい時はその場で改行すればOK

id integer primary keyに何も挿入しなかったら(nullを入れる場合も)、自動で連番がつく

外部からファイルを扱う
例えばcommands.sqlとかいうファイルを作る(ここにコードを書く)

ファイルの実行(読み取り)
.read commands.sql

コマンドラインから直接読み取る
sqlite3 [ファイル名] < commands.sql

制約について
unique 重複した値を許さない
not null nullを許さない
default 何も値を設定しなかった時のdefault値を指定する
check 値のチェックをする

create table if not exists users (
    id integer primary key,
    name text not null,
    score integer default 10 check (score >= 0),
    email text unique
);

selectでのデータの抽出

select * from users; すべてを抽出
select [id, name] from users; フィールドごと抽出

.headers on
select id, name from users;　フィールド名とかを上に出してくれる
select id, name as user_name from users; フィールド名変更

横に長くなって見にくい時
.mode line
select * from users; 縦にしてくれる
lineの他にもcsv(,区切り),html(html表記),column(幅を揃えて見やすくする)

whereで条件指定

60より大きいものを抽出したい時
select * from users where score > 60;

50以上80以下
select * from users where score >= 50 and score <= 80;
select * from users where score between 50 and 80; 上と同じ

名前がtanakaかsatoか
select * from users where name in ('tanaka', 'sato');

nullを抽出
select * from users where score is null; is not nullで逆に

likeで条件指定

まずは完全一致で抽出
select * from users where name = 'Tanaka';

ワイルドカード _任意の1文字 %0文字以上の文字列

satoを曖昧なまま抽出したい
select * from users where name like 's___';

前と後ろはなんでもいいがiを含んでいる
select * from users where name like '%i%';

simizu%を抽出したい
select * from users where name like '%@%' escape '@';

globで条件指定

likeとの違い
大文字小文字を区別する
ワイルドカード _→? %→* [abc]abcのどれか [a-z]aからzのどれか

tかsかで始まる文字列
select * from users where name like '[ts]*';

komiya*を抽出したい
select * from users where name like '*[*]';

並び替え・抽出制限

スコアの小さい順
select * from users order by score;
select * from users where score is not null order by score; nullを外す
select * from users order by score desc; 大きい順になる

抽出制限
上位3件
select * from users order by score desc limit 3;
最初の2件を飛ばして3件
select * from users order by score desc limit 3 offset 2;
select * from users order by score desc limit 2, 3;

viewで抽出条件を保存

create view hiscore as select * from users order by score desc limit 5;

select * from hiscore; 上の条件で抽出できる

viewの削除
drop view if exists hiscore;

簡単な演算

scoreに10づつ足していく
select id, name, score + 10 from users;

文字列の連結は||
select id, 'Name: ' || name, score + 10 from users;

組み込み関数
count 個数を数える
max 最大値を求める
min 最小値を求める
avg 平均を求める
length 文字数がわかる
upper 全て大文字にする
substr(name, 2, 3) nameの2文字目から3文字分を引っ張ってきてくれる

select count(id), max(score), min(score), avg(score) from users;
select name, length(name), upper(name), substr(name, 2, 3) from users;

直近で挿入されたレコードのidをもとめる
select last_insert_rowid();

select random() 乱数を返す
ランダムな人を当選させる
select * from users order by random() limit 1;

group byで集計

データではteamA,B,Cに分けられている状態

チームごとの平均スコアを出す
select team, avg(score) from users group by team;

その中でも平均スコアが50を超えているものを抽出(having)
select team, avg(score) from users group by team having avg(score) > 50;
whereを使うと集計前を抽出してしまう

重複しているデータを除外する(distinct)
select distinct team from users;

caseで値を設定

スコアを元にランク付け
select
    id,name,score
    case
        when score > 70 then 'A'
        when score > 50 then 'B'
        else 'C'
    end as rank
from users;

チームごとにマスコットを与える
select
    id,name,team
    case team
        when 'team-A' then 'Bears'
        when 'team-B' then 'Panda'
        when 'team-C' then 'Python'
        else 'nobody'
    end as mascot
from users;

レコードの更新と削除

scoreが60より小さい人のscoreを0にして、名前の前に印をつける
update users set score = 0, name = '*' || name where score < 60;
select * from users;

全てのレコードを削除
delete from users;
scoreが0の人のレコードを削除
delete from users where score = 0;
select * from users;

一連の処理をまとめて

tanakaから10点引いて、satoに10点足す
begin transaction;
update users set score = score - 10 where name = 'tanaka';
update users set score = score + 10 where name = 'sato';
commit;
間の処理はまとめて行われる
commitをrollbackに変えれば、処理は行われなくなる

トリガー

誰かの点が100点を超えたら、messageテーブルにメッセージが送信されるように
create table messages (message);

create trigger new_winner update of score on users when new(old).score > 100
begin
    insert into messages (messages) values (
        'name: ' || new.name ||
        '' || old.score ||
        ' → ' || new.score
    );
end;

.schema トリガーの構成を見る
drop trigger new_winner; トリガーを消す

indexの設定
索引が高速になるが、挿入や更新の際重くなる

create index score_index on users(score);
create unique index name_index on users(name); unique制約と同じ

.indices [テーブル名] indexの一覧を見る
.schema [テーブル名] indexの構造を見る

drop index if exists score_index; indexを消す

日時の取り扱い
データ型はないから、日時を表す数字や文字列を管理して、それに関数を組み合わせてやりくりする

select datetime('now', '+09:00:00'); 日本の現在の時間を返す
date()日付だけ取得、time()時間だけ取得、strftime()指定のフォーマットで取得

2023年3月1日に対して、+3ヶ月して、その月の初めをとり、されから1日引く
select date('2023-03-01', '+3 months', 'start of month', '-1 day');

create tableした時に、自動的にレコードの作成日時を入れる
//table追加//
created datetime(こんな型はないがわかりやすく) default date('new', '+09:00:00')

複数のテーブルからデータを引っ張ってくる

create table posts (
    id integer primary key,
    title text,
    body text
)
insert into posts (id, title, body) values (1, 't1', 'b1');
insert into posts (id, title, body) values (2, 't2', 'b2');

create table comments (
    id integer primary key,
    post_id integer,
    comment text
)
insert into posts (id, post_id, comment) values (1, '1', 'c1');
insert into posts (id, post_id, comment) values (2, '1', 'c2');

内部結合
上例で、postsのidとcommentsのpost_idを紐づけた時、両方にデータがあるものだけを結合する
(今回で言うとコメントがついているものだけもってくる)

select * from posts inner(省略可) join comments on posts.id = comments.post_id;
↓上から特定のカラムだけ持ってくる
select posts.id, title, comment from posts join comments on posts.id = comments.post_id;

外部結合
どちらかにしかないものを引っ張ってくる
SQLiteでは左側にしかないものを持ってくるleft outer joinしかない

select posts.id, title, comment from posts left join comments on posts.id = comments.post_id;

rowid
テーブル作ると勝手にrowid(連番)が設定される
id integer primary keyはrowidを指し示している

・問題点
例えば三番目を消して新しいものを追加した場合、それが3になってしまう
4から始めたい時はテーブル作成時、idのとこにautoincrementを追加

データベースの内容をSQLで出力

.output [出力先]
.dump [テーブル名]

CSVファイル
難しいからわからない時は別途確認

.mode csv
.import [ファイル名.csv] [どのテーブルに読み込むか]

データ型について

integer(整数), real(浮動小数点数), text(文字列), blob(そのままデータを保存), null(何もない)
sqliteは型制約が緩い

create table if not exists posts (
    id integer primary key(主キー,必ず整数に),
    title text,
    body text
);