SQLite

C

目次 †

概要 †

• C言語の簡易データベースライブラリ

サンプル †

• Github

参考資料 †

• Ubuntuへのインストール
• C++とSQLiteのサンプル
• SQL文
• タイムスタンプ（MySQLでの時間の扱いはtimestamp型を使う：タイムスタンプ 表記揺れ）

SQLite特有の注意点 †

• SQLiteは複数レコード抽出される場合はコールバックがレコード数回呼ばれる
• UPDATE対象がない場合はコールバックが一回も呼ばれない（エラー確認はコールバックで変更されるべき変数が変更されていないことによってしかできない）
• INSERTコマンドの前後にBEGIN, COMMIT命令を出さないと，INSERTが1500倍遅くなる（参考）

     sqlite3_exec(db, "BEGIN", NULL, NULL, NULL);
     int i;
     for( i = 0; i < 100000; i++ )
         rc = sqlite3_exec(db, insert_record[i], 0, 0, &zErrMsg);
     sqlite3_exec(db, "COMMIT", NULL, NULL, NULL);

• MySQLに比べてHDD故障に対して極めて脆弱
  • データベース壊れ対策として、定期的なバックアップが不可欠
  • セクタが1つ抜けるだけで死ぬ。

• Timestampはないので，Time Stringで代用する．
  • 正確にはあるはずなのだが，なぜか時間・分・秒が消失したのでTEXTにしている．

• SQLiteにはshowコマンドがない
  • テーブル一覧

Java †

• pom.xmlに以下を追加

<dependency>
   <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>3.25.2</version>
</dependency>

SQL文 †

基本文法 †

• SQLの文字列はシングルクオート
• 複数列に並列的に処理をしたい場合はコンマ区切り．全列を抽出する場合は*を使う．
• テーブルにはT_の接頭をつける慣習がある．
• 行のことを「レコード」という．

基本的な操作 †

• SELECT文基礎
  • 指定した「表」から，指定した「列達」のうち，指定した「条件」のレコードを抽出し，新たな表を出力する．

    SELECT 「列達」 FROM 「表」 WHERE 「条件」

要素操作 †

• 列の追加

  SELECT 購入ID, 顧客ID, 購入日, 品目ID, 個数, 単価, 個数 * 単価 AS 計 FROM T_購入 // 新しいコラムを継ぎ足せる

  • TOP句（上位）

    SELECT TOP(10) PERCENT 顧客ID ORDER BY ASC
    SELECT TOP(30) 顧客ID ORDER BY DESC

  • DISTINCT句（重複禁止）
• rowを指定（CONDITIONでROWも指定）

  WHERE column CONDITION

• 昇順ソート無題のドキュメント 1

  ORDER BY Table.Price ASC （降順はDESC）

• テーブル連結（INNER JOIN）

  // 担当者IDがテーブル間で共通
  SELECT   T_顧客.顧客ID, T_顧客.顧客名, T_顧客.担当者ID,T_担当者.担当者名
  FROM     T_顧客 INNER JOIN
           T_担当者 ON T_顧客.担当者ID = T_担当者.担当者ID

  • Right, Left OUTER JOINだと，右か左のテーブル情報をフルに保ちながら連結する．
• エイリアス

  FROM     T_顧客 as kk // 後置してもOK！

• あるColumnごとに集計（WHEREは使えないので注意！あまり推奨されず，サブクエリを使うべき？）

  SELECT     kn.顧客ID, kk.顧客名,SUM(kn.個数 * kn.単価) AS 合計 
  FROM     T_購入 AS kn INNER JOIN　T_顧客 AS kk ON kn.顧客ID = kk.顧客ID 
  GROUP BY      kn.顧客ID, kk.顧客名 
  HAVING kn.顧客ID IN(2, 3, 4)

• サブクエリ

  SELECT     顧客ID, 顧客名
  FROM     T_顧客
  WHERE     顧客ID IN(SELECT DISTINCT　顧客ID　FROM T_購入)

WHERE文の記法 †

• 空欄のマッチングはWHERE age IS NULL，IS NOT NULL
• 数値の等価演算子は=，非等価演算子は < >，比較演算子は>, >=, <, <=，範囲指定はBETWEEN 30 AND 45
• 論理演算子はAND, OR．非等価演算子は？
• いずれか一致はIN('赤','ピンク')，いずれでもないはNOT IN(2, 3, 5)
• 文字列の曖昧一致はLIKE '%区%'（%が*と同じ意味）

テーブル操作 †

• テーブルにレコードを新規追加（VALUE, SELECTの両方が使える）

  INSERT INTO StockTable(ID, Stock) VALUES(1001, 10)
  INSERT INTO T_顧客 SELECT 14,'顧客名','住所','性別',年齢,担当者ID FROM T_顧客 WHERE　顧客ID=12

• テーブルのレコードを変更

  UPDATE StockTable SET Stock = Stock + 10 WHERE StockTable.ID = 1001

• テーブル作成

  CREATE TABLE TableName (ID INTEGER PRIMARY KEY, Name TEXT NOT NULL)
  CREATE TABLE products (
     id integer,
     name text,
     price integer CHECK (price > 0) // データ制約
  );

• 削除

  Delete FROM T_顧客 WHERE 顧客ID=14 

• SELECT文から表作成

  SELECT     顧客名,住所
  INTO     T_女性客
  FROM     T_顧客
  WHERE     性別='女性'

データベース設計 †

• 設計が下手だと恐ろしいことになる
• データベース設計

• 考え方
  • 「データを表すベクトルは何か」「データを特定する情報は何か」

• IDEF
  • 角ばっている四角：非依存実体，丸い四角：依存実体
  • 上が主キー，下が非主キー（主キーはそれだけで実体を特定できるということ）
  • 外部キーには(FK=Foreign Key)をつける．
  • 多対1の多の方に黒丸（わさわさしている様子を表す）
  • 線には依存関係である実線と，非依存関係の破線がある
• 多対多の関連の間に実体を追加
  • 「顧客」と「注文」を1対多，「注文」と「製品」を多対1の関連として変換
  • リレーショナルモデルにできないので．
• 正規系
  1. 第1:リレーショナルモデル
  2. 第2:主キーである列の値によって主キーではない列の値が一意に特定できる
  3. 第3:主キーではない列が関数従属性をもたない
• 正規系変換
  1. 第1->第2：主キーに吸収できる列を全て吸収させる（吸収させたら新たに表ができる）
  2. 第2->第3：非主キーではない列同士で一意に定まる場合，ある非主キーに全て吸収させる（吸収させたら新たに表ができる）
• 制約
  1. 検査制約（非負等）
  2. 非NULL制約
  3. 一意性制約
  4. 主キー制約（非NULL制約＋一意性制約）
  5. 外部キー制約（Reference．AのBはこれ，みたいなデータ構造におけるAとB）

プロセシング †

• 元の生データプロセシングしたものの扱い方
• いつ（これをキーにする）どのデータからどういうパラメータで出力したものなのかを表すテーブルを作れば良い