Zenn
Open13

【超基本】SQLiteを操作する

SQL
初心者
SQLite
natsunatsu

基本構文

はじめに

SQLiteの公式ドキュメントはこちら
これに全部書いてある。

テーブル作成

create_db.sql
DROP TABLE IF EXISTS users;

CREATE TABLE users (
    カラム名 データ型 追加情報(※任意)
);

抽出

SELECT 列名
FROM テーブル名
WHERE 条件式;

追加

INSERT INTO テーブル名(カラム名1, カラム名2...) VALUES(値1, 値2...)

削除

DELETE FROM テーブル名 WHERE 条件式

更新

UPDATE テーブル名
SET price = price - 100
WHERE 条件式
natsunatsu

抽出について

SELECT

SELECT カラム名
  • カラム名を「*」にすると、全てのカラムが抽出できる。
  • 複数のカラムを抽出したいときは、カンマ区切りで書く。

FROM

SELECT カラム名 FROM テーブル名

重複を抽出

SELECT *
FROM users
WHERE (name, password, gender, created_at) IN (
    SELECT name, password, gender, created_at
    FROM users
    GROUP BY name, password, gender, created_at
    HAVING COUNT(*) > 1
)
ORDER BY id;

重複を削除

id が最も小さいものだけ残す

Delete from users
where (name, password, gender, created_at) in (
  select name, password, gender, created_at
  from users
  GROUP BY name, password, gender, created_at
  HAVING COUNT(*)>1
  ORDER by id
  )
and id not in(
  select id
  from users
  GROUP BY name, password, gender, created_at
  HAVING COUNT(*)>1
  ORDER by id
)

並び替え

ORDER BY カラム名 (ASC（昇順） or DESC（降順）)

上限

LIMIT 数字

重複を表示させたくない場合

SELECT DISTINCT ...
natsunatsu

JOIN

内部結合

INNER JOIN テーブル名 ON 条件

外部結合

LEFT(or RIGHT) OUTER JOIN テーブル名
ON 条件
natsunatsu

datetime

表現方法

"2020-10-20 10:00:00"

現在時刻

GETDATE()
natsunatsu

デフォルト値の設定

MySQLでは以下のような構文がサポートされているが、SQLiteではサポートされていないのでとても苦労した。

-- テーブル作成時
CREATE TABLE テーブル名(...列名 DEFAULT 任意のデフォルト値)

-- 既存の列定義を変更（※列定義の変更時、デフォルト値は挿入されない）
ALTER TABLE テーブル名 ALTER COLUMN カラム名 SET DEFAULT 任意のデフォルト値;
 
-- 新規に列を追加（※列追加時、デフォルト値が挿入される）
ALTER TABLE テーブル名 ADD カラム名 DEFAULT 任意のデフォルト値;

SQLiteでは、以下のような手順でデフォルト値を設定するのが普通らしい。

  1. 新しいテーブルを作る。このときに、デフォルト値を設定する。
  2. 作ったテーブルにもとのテーブルの情報をコピーする。
  3. もとあるテーブルを削除する。
  4. 作ったテーブルの名前をもとのテーブルの名前に変更する。
-- 既存のテーブルでcreated_atカラムにデフォルト値を設定
CREATE TABLE new_memos (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  category_id INTEGER NOT NULL REFERENCES categories (id),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  created_at DEFAULT CURRENT_TIMESTAMP,
  updated_at DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO new_memos (category_id, title, content, created_at, updated_at)
SELECT category_id, title, content, created_at, updated_at
FROM memos;

DROP TABLE memos;

ALTER TABLE new_memos RENAME TO memos;
natsunatsu

datetime型の計算

datetime(?, "+1 days", "-2 hours", ...)
natsunatsu

プレースホルダーがNULLのときも対応できる

ifnull(X, Y)

XがNULLのとき、Yを出力する。

natsunatsu

比較演算子

大文字小文字の区別

=は大文字小文字を区別してしまう。

LIKEは区別しない。

natsunatsu

再帰

以下のテーブルがある

  • categoriesテーブル
id name parent_id
INTEGER TEXT INTEGER
  • memosテーブル
id category_id title content created_at updated_at
INTEGER INTEGER TEXT TEXT DATETIME DATETIME

memos.idを受け取って、category_idと一致するcategories.idを取得。
その後categories.parent_id→categories.idと再帰

WITH RECURSIVE temp_categories(id, name, parent_id) AS (
  --非再帰項
  SELECT  categories.id, categories.name, categories.parent_id
  FROM    categories
  INNER JOIN memos ON memos.category_id=categories.id
  WHERE ?=memos.id
  UNION ALL
  --再帰項
  SELECT  categories.id, categories.name, categories.parent_id
  FROM    categories, temp_categories
  WHERE   temp_categories.parent_id = categories.id
)
--メインクエリ
SELECT      id, name, parent_id
FROM        temp_categories
ORDER BY id DESC;
natsunatsu

あとで考えたい

正しい

SELECT  c.id AS character_id,
        IFNULL(uc.character_lv, "") AS character_lv,
        IFNULL(uc.skill_lv, "") AS skill_lv
FROM    characters AS c
LEFT JOIN user_own_characters AS uc ON c.id=uc.character_id AND uc.user_id=4
WHERE   c.id=23;

正しくない

SELECT  c.id AS character_id,
        IFNULL(uc.character_lv, "") AS character_lv,
        IFNULL(uc.skill_lv, "") AS skill_lv
FROM    characters AS c
LEFT JOIN user_own_characters AS uc ON c.id=uc.character_id
WHERE   uc.user_id=4
AND     c.id=23;

なんで？

natsunatsu

CROSS

SELECT テーブル名.カラム名, ... FROM テーブル名1
  CROSS JOIN テーブル名2
  ON テーブル名1.カラム名1 = テーブル名2.カラム名2;
natsunatsu
WITH RECURSIVE lv_vals(lv, val) AS (
  SELECT 1, 0
  UNION ALL
  SELECT  lv+1, val+(lv+2)/3
  FROM    lv_vals
  WHERE   lv < 100
)
SELECT  c.id AS character_id,
        lv_vals.lv AS character_lv,
        c.low_hp+((c.high_hp-c.low_hp)*lv_vals.val/1683) AS hp,
        c.low_atk+((c.high_atk-c.low_atk)*lv_vals.val/1683) AS atk,
        c.low_def+((c.high_def-c.low_def)*lv_vals.val/1683) AS def
FROM    characters as c
CROSS JOIN lv_vals
ORDER BY c.id, lv_vals.lv;