Sqlite
sqlite のメモ
本家サイト：https://www.sqlite.org/
- データベース : データが入ったもの
- テーブル : ユーザテーブル、レッスンテーブル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 ダンプを読み込む