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 ダンプを読み込む