Sqliteのソースを表示

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