SQLite3でよく使うコマンドまとめ

SQLite3でよく使うコマンドまとめ 自分がコピペで使うよう。

CRUD系

/* CRUD */
-- C: Create
-- R: Read
-- U: Update
-- D: Delete

/* Create系 */
-- テーブル作成（＋よく使う「制約」を一通り）
create table student (
  id integer primary key, 
  name text not null,
  gender integer default 9, 
  grade integer 
    check(grade between 1 and 3), 
  club text 
    check(club in ('tennis', 'soccer', 'baseball')),
  avg real default 0.0 
    check(avg between 0.0 and 100.0)
);

-- insert
insert into 
  student(name, gender, grade, club) 
  values('Alice', 0, 1, 'tennis');
insert into 
  student(name, gender, grade, club) 
  values('Bob', 1, 2, 'baseball');
insert into 
  student(name, grade, club) 
  values('Charlie', 1, 'soccer');

/* Read系 */
-- データ取得
select * from student;

-- マッチ
-- 名前の末尾がeのひと
select * from student where name like '%e';

-- 正規表現
-- 連結、和集合、クリーネスターと太古の正規表現のみ
-- 頭文字がAかBのひと
select * from student where name glob '[AB]*';

-- rowid 勝手に設定されるinteger primary key
select rowid, * from student;

-- 最後にinsertしたrowid
-- 複数のテーブルがある場合
-- 最後にinsertしたテーブルのrowid
-- どのテーブルかという情報はついてない
select last_insert_rowid();

-- 取得数指定（limit）
select * from student limit 1;

-- ユニーク値（distinct）
select distinct club from student;

-- 条件で書き換え（case）
select
  name,
  case
    when gender=0 then '女性'
    when gender=1 then '男性'
    else '不明'
  end
from
  student;

-- NULL値を書き換え（coalesce）
-- 毎回スペルを忘れる
select
  name,
  coalesce(grade, "NULLです")
from
  student;

/* Update系 */
-- 条件に一致したデータをupdate
update student set grade=2 where name='Charlie';

-- 前の値を利用できる
update student set grade=grade+1;

-- delete and insert
-- primary keyが一致するデータを削除して、新しくinsert
replace into 
  student(id, name, club) 
  values(2, 'Bob', 'tennis');

-- delete対象の行がないと、ただのinsert
replace into 
  student(id, name, gender, grade, club) 
  values(4, 'Dave', 1, 3, 'baseball');

/* Delete系 */
-- テーブルごと削除
drop table student;

-- 条件に一致したデータ削除
delete from student where name='Alice';

-- データ全消し（テーブルは残る）
delete from student;

集約関数系

/* 集約関数など */

-- 外部キー有効化
PRAGMA foreign_keys=1;

-- テスト結果テーブル
-- foreign keys: 
-- student(id)にない値はinsertできない
create table exam (
  student_id integer, 
  subject text 
    check(subject in ('eng','math')),
  score integer 
    check(score between 0 and 100),
  foreign key(student_id) references student(id)
);

-- テスト結果
-- random()は
-- ーめっちゃでかい数
-- 〜＋めっちゃでかい数までの整数乱数
insert into 
  exam(student_id, subject, score) 
  values(1, 'eng',  abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(1, 'math', abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(2, 'eng',  abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(2, 'math', abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(3, 'eng',  abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(3, 'math', abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(4, 'eng',  abs(random())%100);
insert into 
  exam(student_id, subject, score) 
  values(4, 'math', abs(random())%100);

-- 合計、平均値、最大値、最小値、レコード数
select
  total(score) as m_total,
  avg(score) as m_avg,
  max(score) as m_max,
  min(score) as m_min,
  count(score) as m_cnt
from
  exam
where
  subject=='math';

-- group byとhaving
select
  student_id,
  avg(score) as s_avg
from
  exam
group by
  student_id
having
  s_avg>70;

テーブル結合

/* テーブル結合 */
-- 内部結合さんと左外部結合さんで9割戦える

-- 内部結合（inner join）
-- onの値同士が一致したレコードのみ
select
  s.name,
  s.gender,
  e.subject,
  e.score
from
  student as s
inner join 
  exam as e
on
  s.id==e.student_id;

-- 新しい生徒を追加する（テスト結果はない）
insert into 
  student(id, name, gender, grade) 
  values(5, 'Eve', 0, 1);
insert into 
  student(id, name, gender, grade) 
  values(6, 'Frank', 1, 1);

-- 左外部結合（left outer join）
-- onの値が左tblにはあるけど
-- 右tblにはないレコードも表示
select
  s.name,
  s.gender,
  e.subject,
  e.score
from
  student as s
left outer join 
  exam as e
on
  s.id==e.student_id;

ビュー

/* ビュー */
-- よく使うselect文を関数化する的な

-- 生徒ごとの平均点のビューを定義
create view student_avg as
  select
    student_id,
    avg(score) as s_avg
  from
    exam
  group by
    student_id;

-- ビューを使う
-- 関数でデータを別に持ってるわけではない
-- 元のテーブルが変わるとビューの値も変わる
select * from student_avg;

-- 最後にexamにinsertされた生徒ID
create view last_student_id as
  select
    student_id as id
  from
    exam
  where
    rowid=last_insert_rowid()
  limit 1;

-- ビュー削除
drop view student_avg;

サブクエリ

/* サブクエリ（副問合せ）*/
-- 使い捨てのビュー的な
-- with句を使うと良さげ

-- テスト結果の分散を求める
-- サブクエリで平均を求めてから
-- メインクエリで分散を計算
with math as (
  select
    avg(score) as avg_score
  from
    exam
  where
    subject='math'
)
select
  avg((e.score-m.avg_score)
     *(e.score-m.avg_score))
from
  exam as e, 
  math as m
where
  e.subject='math';

トリガー

/* トリガー */
-- あるテーブルにCかUかDがあると
-- 自動で別のsqlを実行してくれる神

-- 新しいテスト結果がexamに追加されると
-- 平均点を計算してstudentのavgを更新

-- トリガー名の後ろのINSERTとか
-- 大文字じゃないと発動しないっぽい
-- update文ではwith句使えないよ
create trigger 
  update_avg 
  INSERT on exam
begin
  update
    student
  set
    avg=(
      select
        avg(score)
      from
        exam
      where
        student_id=
        (select id from last_student_id)
    )
  where
    id=
    (select id from last_student_id);
end;

-- やってみる
-- 交互に連打するとstudent.avgが
-- 自動で変わっているのがわかる
insert into 
  exam(student_id, subject, score) 
  values(1, 'math', abs(random())%100);
select * from student where id=1;

-- トリガー削除
drop trigger student_avg;

SQLite独自

/* SQLite独自コマンドなど */

-- SQLiteのバージョン
select sqlite_version();

-- 今使っているdbファイルのパス
.database

-- テーブル名一覧
.table

-- テーブルの定義（型とか）
pragma table_info(student)

-- 定義したテーブル、ビュー、トリガー
.schema student

-- 定義したテーブル、ビュー、トリガー
select * from sqlite_master;

@youmounlp