SQLite

SQLite

• SQLite
  • ダウンロード
    • 二種類のファイルをダウンロードして、解凍して、全部、一つのフォルダーに入れる。
    • Windowsのプロンプトから、sqlite3.exeを実行
  • GUIで操作：DB Browser for SQLite
  • csvファイルを読み込む
  • コマンド入力
    • 終わりは ;
  • select
    • 条件は論理演算子を使う
  • 関数
    • avg()
    • sum()
    • count()
    • min()
    • max()
    • distinct()
  • Tips
    • ヘルプ
    • 終了
    • データベースの確認
    • テーブル一覧
    • 見出しを表示
    • 見出しの確認
    • 一覧表を見やすく
    • カラムごとに見やすく
    • 設定一覧
    • テーブル名の変更
    • カラム名の変更
    • selectした結果の見出しを変更
  • 正規化
  • 集計
    • group by
  • テーブル結合
    • inner join
    • left outer join
  • テーブル分割：selectした結果を別のテーブルに
  • 条件分岐 CASE
  • エクスポート
    • csvファイルとして保存
  • 参考

 ダウンロード

https://www.sqlite.org/download.html

二種類のファイルをダウンロードして、解凍して、全部、一つのフォルダーに入れる。

• sqlite-dll-win64-x64
• sqlite-tools-win32-x86

Windowsのプロンプトから、sqlite3.exeを実行

• ただフォルダー内でダブルクリックで起動

 GUIで操作：DB Browser for SQLite

https://sqlitebrowser.org/

 csvファイルを読み込む

• csvファイルは、見出しを一行目に書いておくこと

.mode csv でモードを変更しておく
.import ./ファイル名.csv テーブル名

 コマンド入力

終わりは ;

• 長いコマンドは途中で改行してもよい。
• 最後に ; を打ったところまでが一つのコマンドとなる。

 select

条件に合ったデータを選択する

select * from テーブル名 where 条件

条件は論理演算子を使う

select * from テーブル名 where mode = 'written' and ipsyn13 > 50;

• AND
• OR
• BETWEEN AND
• IN(項目, 項目)
• LIKE '表現'
  • %をワイルドカードとして使用　's%'
• 空の値は NULL

• LIMIT 数字
  • 出力行数の制限

• ORDER BY 項目
  • ORDER BY 項目 DESC

 関数

avg()

sum()

count()

• 該当する者の個数を数える
  • 条件を指定して、該当するものを数える

select count(id) from ipsyn13 where ipsyn13 > 60;

min()

max()

distinct()

• データ項目一覧作成の際に重複を除く(unique)

 Tips

ヘルプ

.help

終了

.exit

データベースの確認

.databases

テーブル一覧

.tables

見出しを表示

.headers on

見出しの確認

.schema

一覧表を見やすく

.explain on

カラムごとに見やすく

.mode column

• デフォルトは .mode list

設定一覧

.show

テーブル名の変更

alter table 古い名前 rename to 新しい名前;

カラム名の変更

• 通常のSQLでは、alter table でできるが、SQLiteにはその関数がない

selectした結果の見出しを変更

sqlite> select N as '名詞句', V as '動詞句' from ipsyn_spoken;
名詞句  動詞句
---  ---
16   14
19   17
18   15
19   15
19   17

 正規化

• 情報が重複しないようにテーブルを整理する

 集計

group by

• グループの種類ごとにまとめる

select year, count(id) from ipsyn13 group by year;

 テーブル結合

• テーブル間で共通する項目を軸にデータを結合する
• 結合した結果、新たなテーブルができる
• 該当するデータがない場合は削除される

• 「テーブル名.見出し」で指定する。
• テーブル名は、「as ニックネーム」でニックネームで使える。
  • テーブル名が長い場合に短いニックネームで表記できるようにすると便利
    • entranceyear as ey

inner join

select * from テーブルA inner join テーブルB on テーブルA.id = テーブルB.id;

• 表示する項目を限定するには、select * ではなく、* のところに、表示したい項目だけを列挙

 select テーブルA.name, テーブルB.score from テーブルA inner join テーブルB on テーブルA.id = テーブルB.id;

left outer join

• 条件にマッチしないものも残す
  • nullと表示される

 テーブル分割：selectした結果を別のテーブルに

create table 新しいテーブル名 as select * from 元のテーブル名 where 条件;

 条件分岐 CASE

select case
    when 条件 then 値
    when 条件 then 値
    else 値 end

 エクスポート

csvファイルとして保存

.header on
.mode csv
.once ファイル名
select文の実行

• .once は、その一回だけ、出力先をファイルにする。
• .output は、それ以降の出力先を継続的に変更する。

 参考

https://youtu.be/v-Mb2voyTbc