SQLiteコマンド一覧

先日PostgreSQLのコマンド一覧をまとめましたが、今回はSQLiteのコマンド一覧をまとめてみた。

SQLはStructured Query Languageの略。これがなかなか覚えられない。

それでは、さっそく一覧を書いていきます。（コマンドラインおよびエディタで分けてみます）

コマンドライン

テーブルの作り方１	create table+  テーブル名（中身）
sqliteのDBに移動	sqlite3 myapp.db
ヘルプを見る	.help
sqliteを終了させる	.exit
外部ファイルの読み込み1	sqlite3 データベース名　実行 .read ファイル名
外部ファイルの読み込み2	sqlite3 データベース名 < ファイル名
テーブルの一覧を見る .tables	.talbes
テーブルの構造を見る .schema	.schema テーブル名 ※テーブル名を入力しないとすべてのテーブルになる
データ型	integer / int / tinyint　 整数 real / double　　 浮動小数点数 text / verchar(255)　　 テキスト blob(バイナリラージオブジェクト）※小さい画像やファイルをそのまま保存できる null 何もない
カラムのフィールド名の表示 .headers on	.headers on
表示の仕方の変更 .mode	.mode line select * from テーブル名; .mode csv データをカンマ区切りに表示 .mode html htmlの表示形式で表示 .mode column　幅をそろえて表示 など
indexの一覧を見る .indices	.indices インデックス名
.dump	データベースの内容をSQLで出力する方法 .dump テーブル名（指定しないとすべてのテーブルを表示）
.output	出力先を決める .output ファイル名
.import	読み込み .import CSVファイル名 読み込む先のテーブル名 ※読み込んだものはすべて文字列になってしまう

エディタ

テーブルの作り方２ create table	create table + if not exists テーブル名（ 　　データ名, 　　データ名 ） ※大文字、小文字を区別しない
primary key	idのようなレコードを一意に特定することができるフィールドには主キーをつける ※主キーはテーブルにつき一つしか設定できない 例） create table + if not exists posts ( 　id integer primary key,     title text,     body text  );
テーブルの削除 drop table	drop table if exists テーブル名
テーブルの名前の変更 alter rename	alter table テーブル名 rename to 新しい名前
カラムの追加 alter add	alter table テーブル名 add column 追加するもの データ型
レコードの挿入 insert	insert into テーブル名 (挿入するデータ名, 挿入するデータ名, ...) values ('値', '値',...)
レコードに入っているデータの表示 select from ’＊’すべてのデータという意味	※すべてのデータを表示する際 select * from テーブル名 ※あるデータにしか興味がないとき select データ名, データ名 from テーブル名
文字列	シングルクォーテーションで囲む　’ 文字列 ' ※シングルクォーテーション内で ' を使いたい場合は '' と二つ重ねる
制約のつけ方	unique 　　　　　　　重複する値を許さない not null 　　　　　　　nullを許さない default 　　　　　　　何も値を設定していないときにdefault値を与える check 　　　　　　　　値のチェックをする 例） create table + if not exists posts ( 　id integer primary key,     name text not null,     score integer default 10 check (score >= 0),     email text unique  );
カラムのフィールド名の変更 as	※セレクトする際に select データ名, データ名 as 変更したいフィールド名 from テーブル名;
条件を付けて表示 where	select * from テーブル名 where 条件; ※..と等しくない → <> or !=
完全一致 =	select * from テーブル名 where データ名 = 値;（大文字小文字を区別する）
部分一致 like	※_ 任意の一文字に引っかかるものを抽出 select * from テーブル名 where データ名 like 文字___;(大文字、小文字は区別しない） ※% 任意の０文字以上の文字に引っかかるものを抽出 select * from テーブル名 where データ名 like %文字%; ※文字の中に％や_が入っている場合をひっかけるには文字として%や_をescapeする select * from テーブル名 where データ名 like %＠% escape '@'; 　こうすると＠％の％は文字として扱われる
部分一致 glob	※大文字、小文字を区別する ※? 任意の位置文字に引っかかるものを抽出 ※* 任意の０文字以上の文字に引っかかるものを抽出 ※[ ] パターンマッチ　中で指定したもののどれか 例） select * from users where name glob '*i*'; select * from users where name glob '[ts]*' tかsかで始まる文字列 select * from users where name glob '[a-m]*' a-mの間の文字で始まる文字列 select * from users where name glob '*[*]'  *が文字列の中に含まれる場合[]で囲む
並べ替え order by desc	※小さい順に並べ替え select * from テーブル名 order by データ名; select * from テーブル名 where score is not null order by データ名; 　※nullを外す場合 ※大きい順に並べ替え select * from テーブル名 order by データ名 desc;
抽出件数の指定 limit offset	※大きい順に並べ替え上位から何件かを抽出する場合 select * from テーブル名 order by データ名 desc limit 件数; ※大きい順に並べ替え上位から何件かを飛ばして何件かを抽出する場合 select * from テーブル名 order by データ名 desc limit 抽出する件数 offset 飛ばす件数; select * from テーブル名 order by データ名 desc limit 飛ばす件数, 抽出する件数;
複雑な条件に名前を付けて呼び出す view	create view view名 as 抽出条件 →その値そのものではなく抽出方法を保存するだけ ※データの引っ張り方 select * from view名 ※viewの削除 drop view if exists view名
演算して抽出	select データ名, データ名 演算 from テーブル名 ※文字列の演算 || 例） select id, 'Name: ' || name as name, score + 10 as new_score from users;
様々なデータの抽出方法	count(データ名) 　個数を数える max(データ名） 最大値を抽出 min(データ名) 最小値を抽出 avg(データ名) 平均値を抽出 length(データ名) 文字列の文字数を抽出 upper(データ名) 文字列を大文字にしてくれる substr(データ名, 何番目の文字, 何番目の文字) 何番目の文字～何番目の文字までを抽出 last_insert _rowid() 　直近で挿入されたレコードのidを抽出 random() 乱数を発生させる関数 例） select count(id), max(score), min(score), avg(score) from users; select name, length(name), upper(name), substr(name, 2, 3) from users; select last_insert_rowid(); select * from users order by random() limit 1; ※抽選で一名を当てる
グループで集計する group by	group by データ名 ※グループで抽出した結果からさらに条件を付けて抽出する場合 having 条件 例） select team, avg(score) from users group by team; select team, avg(score) from users group by team having avg(score) > 50; ※whereを使うと集計する前のデータで条件を適用してしまう
重複するデータを除外して集計してくれる distinct	distinct データ名 例） select distinct team from users;
条件について値を返す case	select データ名, データ名, データ名 　case when 条件 then ’値' when 条件 then ’値' else '値' end as 表示名 from テーブル名;
レコードの更新 update	update テーブル名 set update後, update後 where 条件 select * from テーブル名
レコードの削除 delete	delete from テーブル名 where 条件 select * from テーブル名
一連の処理を必ずまとめて行いたい場合 transaction	begin transaction まとめて行いたい一連の処理 commit; ※間にある処理は必ずまとめて行われる
トランザクションを終了して処理を破棄する rollback	begin transaction まとめて行いたい一連の処理 rollback;
あるテーブルで何らかの変更があったときにそれをTriggerにして他のテーブルも変更する trigger	create trigger トリガー名 update of データ名 on テーブル名 when 条件 begin 処理 end;
更新前のテーブルにアクセス new 更新後のテーブルにアクセス old	new.データ名 old.データ名
検索を高速にする index	create index インデックス名 on テーブル名(データ名); ※重複した値を許さないインデックス create unique index インデックス名 on テーブル名(データ名);
日時を扱う	※日時を表す文字列を取得したい場合　datatime() 現在を表す特殊なもの　'now', '+09:00:00 ' ※日付だけ取得する場合　date() ※時間だけ取得する場合　time() ※指定のフォーマットで時間を取得　strftime() ※日時演算もできる
テーブルを作るときに自動的にcreate日時を入れる	create table テーブル名 ( created datetime default (datetime('now', '+09:00:00') );
複数のテーブルからデータを引っ張ってくる	※結び付けたいデータをテーブルを作る際に設定 create table テーブル名( 結び付けたいテーブル名_結び付けたいデータ名　データ型 );
内部結合によるデータの抽出 ※両方のテーブルにデータがあるものだけ引っ張ってくる inner join	select * from テーブル1 inner join テーブル2 on 条件 ※innerは省くこともできる ※*の部分に表示したいテーブル名を入れると表示したいものだけ抽出
外部結合 ※どちらかにしかないものを引っ張ってくる left outer join	left outer join 左側に記述したテーブルにしか存在しないデータを取り出す　←SQLLiteではこれだけ right outer join 右側に記述したテーブルにしか存在しないデータを取り出す full outer join どちらにも存在するータを取り出す
交差結合 ※すべての組み合わせを抽出してくれる cross join	cross join 例） select posts.id, title, comment from posts cross join comments;
rowid	内部的に作られるcolumn 一意に決まる連番が降られる 表示するには select rowid, * from テーブル名 ※integer primary key を指定したidは自動的にrowidを割り当てるようになっているが、削除しても過去に設定した連番を再び使ってしまう可能性があり ※過去に使った連番を使いたくない場合はinteger primary key の後にautoincrementをつける

今回、コマンドラインとエディタで分けて表にしましたが、実質、エディタではなくてコマンドラインでも直接打ち込むことはあると思う。ひとまず、分けているだけなので気にしないでください。

以上、ちょっと思い出したい時用のメモでした。