search
LoginSignup
125

More than 5 years have passed since last update.

posted at

updated at

SQLite3まとめ

基本コマンド

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

起動コマンド
sqlite3 データベース名.db
終了コマンド
.exit

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

ヘルプ表示コマンド
.help

外部ファイルの実行

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

外部ファイルの実行
.read ファイル名.sql

もしくは

外部ファイルの実行
データベース名.db < ファイル名.sql

テーブルの操作

テーブル一覧表示
.tables
テーブル構造表示
.schema テーブル名

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

データ型

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

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

主キー

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

主キーの宣言
 変数名 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() 乱数を発生させる

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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
125