sqlite のメモ

データベース : データが入ったものテーブル : ユーザテーブル、レッスンテーブルetc

=

フィールド/カラム:列レコード:行

=

コマンドラインでの

・データベースの作成、接続 sqlite3 sqlite3 dbname.拡張子で入る(無ければ作られる) ex. sqlite3 mysample.sqlite3

・ヘルプの表示： .help

・SQLiteの終了： .exit

・データベースの削除：ファイルを消せばOK

テーブルを作る

セミコロンまでが１命令

ex. create table users (name); create table users (name, email);

テーブルの構造を見る .schema users;

テーブル削除 drop tabele users;

テーブル名変更 alter table users rename to dtusers;

カラムの変更 alter table dtuser add colum pwd;

データ型の種類 NULL INTEGER REAL TEXT BLOB(BINALY LARGE OBJECT)

データ型は指定可能だが必須でない

ex. create table users (name text, email text);

データ型は指定するとそれになるように努力するが確約はされない

 （つまり別の型が入る可能性もある）

テーブルの操作関連

テーブル作成時のオプション例 IDを自動連番に ex. create table users (id integer primmary key autoincrement);

名前の空を禁止 create table users (id integer primmary key autoincrement, name text not null);

emailをユニークに create table users (id integer primmary key autoincrement, name text not null, email text unique);

age のデフォルト値を20に ex create table users (id integer primmary key autoincrement, name text not null, email text unique, age integer default 20);

値チェック create table lessons (title, count_lessons check(count_lesons>0));

インデックスの作成 ex. ege にインデックスを付ける create index age on users (age);

データの挿入 ex.

文字はシングルクォーテーションで囲む

insert into users (name, email, age) values ('taguche', 'a@a.com', 20);

文字中にあるシングルクォーテーションは ２個続けて表現する

insert into users (name, email, age) values ('its a pen', 'a@a.com', 20);

データの抽出 ex. usersテーブルを全部見る select * from users;

scoreの昇順で抽出 select * from users order by score;

scoreの降順で抽出 select * from users order by score desc;

scoreの降順の上位３つ select * from users order by score desc limit 3;

score が 200以上 select * from users where score >= 200;

taguchi で抽出 select * from users where name = 'taguchi';

taguchi 以外で抽出 select * from users where name <> 'taguchi';

tagで始まる名前で抽出 select * from users where name like 'tag*';

組み込み関数（core function）を使う件数の取得 select count(*) from users;

score の最大値レコード select max(score) from users;

score の最小値レコード select min(score) from users;

ランダム（数値） select random();

ランダムに一個レコード select * from users order by random() limit 1;

name を抽出して値の文字数を出す select name, length(name) from users;

型を取得する select name, typeof(name) from users;

データを集計する name score team text ユニークな値を週出 select distinct team from users;

チーム毎のスコア合計 select team , sum(score) from users group by team;

日付・時刻(date time function) select current_time; select current_date; select current_timestamp; select insert into users (name, created) value ('taguche', current_timestamp);

時間をフォーマット変えて表示 select strftime('%Y年', current_timestamp); 2012年

データの更新 ex. taguche 君を new taguche に update users set name = 'new taguche' where name = 'taguche';

ex. new taguche 君を taguche にして、 score を 500 に update users set name = 'taguche', score = 500 where name = 'new taguche';

データ削除 users 全削除 delete from users;

100 以下のものを削除 delete from users where score <=100;

内部のID付きで表示 select ROWID, * from users;

ROWIDが4のレコードを削除 delete from users where ROWID = 4;

複数テーブルからの抽出 ex. users テーブル (id integer primary key autoincrement, name, team)

   games テーブル（user_id, score）

上のものから合計を出してみる usersテーブルの id name team フィールドを選択スコアを合計する。 game テーブルで game.user_id を users.id = game.user_id で関連付けて id 毎に抽出

select id, name, team, sum(score) from users, games where users.id = game.user_id group by users.id

重複があったらテーブル名を明示する select users.id

外部ファイルからのデータ取り込み .show .sepalator , 区切りを , に

.import users.txt users .header on <<< カラム名を表示する

バックアップ .dump 全テーブル .dump users users テーブル .output users.dump ファイル名を設定。 .show で確認 .read users.dump ダンプを読み込む