SQLite3まとめ

#基本コマンド
SQLiteの起動や終了に必要な基本的なコマンド

起動コマンド

sqlite3 データベース名.db

終了コマンド

.exit

control+Dで終了することもできる

ヘルプ表示コマンド

.help

###外部ファイルの実行
処理内容を外部のsqlファイルに記述し、実行することができる

外部ファイルの実行

.read ファイル名.sql

もしくは

外部ファイルの実行

データベース名.db < ファイル名.sql

###テーブルの操作

テーブル一覧表示

.tables

テーブル構造表示

.schema テーブル名

テーブル名を指定すると指定したテーブルの構造を表示。省略可能

#データ型

データ型	説明
integer	整数
real	浮動点小数
text	文字列
blob	データの保存
null	カラ

データ格納時に動的にデータ型が動的に決まる
intやdoubleなどでも型指定をできる

###主キー
テーブルにつき１つしかつけることのできないキー
主キーは検索のキーとして利用されたり、他の関係に参照のために格納されたりする可能性が高いため、できる限りデータ量の小さい方がよい

主キーの宣言

 変数名 integer primary key

#create

テーブル作成

create table if not exists テーブル名 (
 フィールド名 データ型,
 フィールド名 データ型,
 フィールド名 データ型
);

if not exists：テーブル名が存在しない場合、テーブルを作成。省略可能

###フィールドの制約設定
データ型の後ろに記述することでフィールドに制約を設けることができる

データ型	説明
unique	データが重複する場合、データを挿入しない
not null	nullをとらない
default 値	データ挿入がない場合のデフォルト値を挿入
check（条件式）	値のチェックを行う

#drop
データを削除するコマンド
テーブル以外にもフィールドやレコード、ビューの値も指定可能

データの削除

 drop if exists table テーブル名;

if exists：テーブル名が存在する場合、テーブルを作成。省略可能

#alter
テーブル名の変更や、フィールドの追加など
フィールド名は変更できない
###rename to

テーブルの名称変更

 alter table テーブル名 rename to テーブル名;

###add column

フィールドの追加

 alter table テーブル名 add column フィールド名 データ型;

フィールドの追加はできるが修正や削除はできないので注意が必要
誤ってフィールドを追加した場合、別テーブルにデータを移行するといった面倒な作業が必要となってくる
特殊文字を入力する際は特殊文字の前に'を付ける

#insert
データの挿入

データの挿入

 insert into テーブル名 (フィールド名, フィールド名) values ('データ', 'データ');

#select
データの抽出を行う

データの抽出

 select フィールド名 from テーブル名;

抽出したいフィールド名を指定する

###headerの表示

headerの表示

 .headers on

フィールド表示名の変更

 select フィールド名 as 表示名 from テーブル名;

##抽出modeの変更

レコードの縦表示

 .mode 表示形式

表示形式	説明
line	レコード単位でデータを表示する
csv	カンマ区切りでデータを表示
html	htmlのテーブル形式でデータを表示
column	幅を揃えて見やすくデータを表示

##where
データの抽出条件を指定する

データ抽出条件の指定

 select フィールド名 from テーブル名 where 条件式;

条件式	説明
＜	より大きい
＞	より小さい
＜＝	以上
＞＝	以下
＝（＝＝）	等しい
＜＞（！＝）	等しくない
and	かつ
or	または
not	否定
between A and B	AからBの間
in ('A', 'B')	AかBかどれか
is null	nullのもの
is not null	nullではないもの

大文字小文字を区別する

###like
文字列の部分一致でデータを抽出する
大文字小文字を区別しない

likeを用いた条件指定

 select フィールド名 from テーブル名 where フィールド名 like '条件';

ワイルドカード	説明
＿（アンダーバー）	任意の1文字
%	0文字以上の任意の文字列

特殊文字を文字列として抽出する

 select フィールド名 from テーブル名 where フィールド名 like '%@%' escape '@';

エスケープするための文字を指定し、特殊文字の前に記述する（今回は@を利用）

###globe
文字列の部分一致でデータを抽出する
likeと異なり大文字小文字を区別する

likeを用いた条件指定

 select フィールド名 from テーブル名 where フィールド名 glob '条件';

条件	説明
?	任意の1文字
*	0文字以上の任意の文字列
[abc]	a,b,cのどれかと一致
[a-z]	a-zのどれかと一致

特殊文字を文字列として抽出する

 select フィールド名 from テーブル名 where フィールド名 glob '*[*]'; 

特殊文字を[]で囲む

#order by
並び替え順を指定する

データを並び替えて抽出

 select フィールド名 from テーブル名 order by フィールド名; 

フィールド名の後ろにdescと記述することで逆順にデータを並び替えることができる

##limit
抽出件数を指定する

抽出件数を指定

 select フィールド名 from テーブル名 order by フィールド名 limit 抽出件数; 

最初の何件かを飛ばしてデータを抽出する

最初の何件かを飛ばす

 select フィールド名 from テーブル名 order by フィールド名 limit 抽出件数 offset 飛ばす件数; 

最初の何件かを飛ばす

 select フィールド名 from テーブル名 order by フィールド名 limit 飛ばす件数, 抽出件数; 

#viewに抽出条件を保存する

Viewに抽出条件を保存する

 create view ビュー名 as select フィールド名 from テーブル名 order by フィールド名 limit 抽出件数; 

データではなく条件を保存する。データベースが更新されると抽出内容も変化することがある。

Viewの利用方法

 select フィールド名 from ビュー名; 

#演算も可能

演算の例

 select フィールド名　+ 10 from テーブル名; 

演算子	説明
+	加算
-	減算
*	乗算
/	除算
%	余りの算出
｜｜	文字の連結

#組み込み関数

関数	説明
count(フィールド名)	個数を数える
max(フィールド名)	最大値を求める
min(フィールド名)	最小値を求める
avg(フィールド名)	平均を求める
length(フィールド名)	文字数のカウント
upper(フィールド名)	文字列を大文字に変換
substr(フィールド名, 始点, 終点)	文字列の始点から終点の文字を抜き出す
last_insert_rowid()	直近で挿入されたコードのidを求める
rundom()	乱数を発生させる

他にもたくさんあるので公式サイトを参照