SQLite入門【データベース作成、テーブル作成、データ抽出他】

この記事は入門記事となっていますが筆者はエンジニアではないので構文の内容までは保証しません。Sqliteの構文はsqlという言語を使っているので実はmysqlとほとんどかわりません。（これも実際個人的な見解なのでほんとかどうかは自分で確かめられることをおすすめします。_

sqliteはmysqlよりも手軽に使うことができます。データベース作成時にパスワードを設定することもありませんし、作ったデータベースのファイルは現在の作業ディレクトリに作成されます。mysqlも実際はディレクトリが作られているのですが、mysqlの場合は現在の作業ディレクトリではなくて専用のディレクトリが特定の場所に作られます。

またsqliteにはユーザーという概念は存在しません。

Sqlite構文の概要

コメント —
複数行コメント /**/
エスケープ ””
改行文字なし

データベース

データベースの作成 ⇀ 拡張子.dbファイルが作成される
接続データベースの確認

.databases

データベースの削除
終了

.q -- もしくは
.exit

テーブル

テーブルの作成

create table tablename(col, col);

なかったら作成

create table if not exists posts();

テーブルの一覧を確認

.tables

テーブルの構造を確認

.schema posts

すべてのテーブルの構造 .schema

テーブルの削除

drop table if exists tablename;

テーブルの名前変更

alter table oldtname rename to newtname;

カラムの追加

alter table tname add column cname type;

レコードの挿入

insert into posts (title, body) values ('title 1', 'body 1');

制約

unisqe⇀ 値の重複を許さない
not null⇀ 空は許さない
default⇀ デフォルト
check⇀ check score >= 0

外部ファイルからの実行

外部ファイルからの実行 (sqlのシェルで実行)

.read hoge.sql

外部ファイルからの実行 (unixのシェルで実行)

データ型

integer
real
text
blob
null

integerはintやtinuintでも表現が可能。realはdouble、textはvarchar(255)などでも表現可能

※注意 integerのかわりにintを使った場合 insert時にidを指定しないとidカラムは作成されません

テーブルデータの抽出

全件抽出

select * from users;

カラムを指定して抽出

.headers on -- カラム名も一緒に表示
select id, name from users; -- カラム名を変える
select id, name as user_name from users;

抽出結果の表示の変更

.mode line
.mode csv
.mode html
.mode columtn

レコードの更新

update users set name = '□' || name;
update users set score = 0 where score > 90;

レコードの削除

delete from users;
delete from users　where score = 0;

where

select * from users where score >= 50 and score <= 80;
select * from users where name in ('taguti', 'fkoji'); -- inは含まれているもの
select * from users where score is null; -- nullを抽出するにはisを使う
select * from users where name = 'Hello'; -- 'hello'は抽出されない

like

_⇀ 任意の一文字
%⇀ ワイルドカード

select * from users where name like '%s%';
select * from users where name like '%S%'; -- like句においては大文字小文字判定は起こアナわれない
select * from users where name like '%\%' escape '\'; -- escape文字は自分で設定する

order by, limit

select * from users order by score desc;
select * from users order by score;
select * from users order by score desc limit 3;
select * from users order by score desc limit 3 offset 3;
select * from users order by score desc limit 3, 3;

view

viewは抽出条件をまとめておくことができる

create view hiscore as select * from users order by score desc limit 5;
.table -- viewを確認
.schema viewname -- viewの構造を確認
drop view viewname -- viewの削除

計算

|| に関しては文字列の連結で使用します
+–*/%||

select id, name, score + 10 from users;
select id, 'Name: ' || name, score from users;

組込

max()⇀ 最大値
min()⇀ 最小値
avg()⇀ 平均値
count()⇀ カラムのカウント

length()⇀ 文字数
upper()⇀ 大文字に変換
lower()⇀ 小文字に変換
substr()⇀ 指定した文字文から指定した文字数ぶんをひっぱってくる
random()⇀ 乱数を生成する

一例

select max(score), min(score) from users;

直近で挿入されたレコードを抽出

select last_insert_rowid();

randomにひとつ抽出する

select * from users order by random() limit 1; -- order by randomを使うとランダムにレコードを取り出すことができる

length()⇀ 文字数
upper()⇀ 大文字に変換
lower()⇀ 小文字に変換
substr()⇀ 指定した文字文から指定した文字数ぶんをひっぱってくる
random()⇀ 乱数を生成する
round()⇀ 四捨五入

sqliteには切り捨ての組み込み関数はないようですが、cast()でint型に変換をすると切り捨てがおこなわれるようです。

update users set score = cast(score as double);
update users set score = cast(score as int);

group by, having, distinct

select team, avg(score) from users group by team;

グループ化されたデータの条件付き抽出を行う場合はwhereではなくhavingを使用します

select team, avg(score) from users group by team having avg(score) > 50;

distinctはレコードの重複を取り除いた状態で出力します

select distinct team from users;

case when

sqlite> select id, name, score,
   ...> case
   ...> when score > 70 then 'A'
   ...> when score > 40 then 'B'
   ...> else 'C'
   ...> end as rank from users;

when句に条件式を書かないやり方もあります。

select id, name, score, team,
case team
when 'team-a' then 'php'
when 'team-b' then 'ruby'
when 'team-c' then 'javascript'
end as program from users;

そのほかの機能

transaction

一連の処理が必ず行われるようにする仕組み

begin transaction;
update users set score = score - 10 where name = 'hoge';
update users set score = score + 10 where name = 'fuga';
commit;

begin transaction;
update users set score = score - 10 where name = 'hoge';
update users set score = score + 10 where name = 'fuga';
rollback; -- 変更しない

trigger

なにか変更があったときにsql文を実行できる仕組み。begin endのなかに行いたい処理を書く

create trigger new_winner update of score on users when new.score > 100
  begin
  insert into messages (message) values(
    'name: ' || new.name ||
    ' ' || old.score ||
    ' -> ' || new.score
  );
  end;

index

indexを使用すると検索がはやくなったりする。そのかわりデータの挿入が重くなる

create index score_index on users(score);
create unique index name_index on users(name);
.indices users -- indexの一覧を見る
.schema users -- indexの構造を見る
drop index indexname;

日付

sqliteには日付型はないが、日付を文字列で取得する関数がいくつか存在している

select datetime('now', '+09:00:00');
select date('now', '+09:00:00');
select time('now', '+09:00:00');
select date('now', '+1 months', '-1 day'); -- 1カ月後の一日前

テーブルを作成する際は以下のようにします

created datetime default (datetime('now', '+09:00:00'))

結合

内部結合

内部結合は両方のテーブルにデータがあるものだけをひっぱってきます。こう書くとわかりにくくなりますが、

下記の例だと実際はpost.idとcomments.idがをひもづけるので一致した行のデータはすべて紐づけされます

select * from posts inner join comments on posts.id = comments.post_id;

innerは省略可能

select * from posts join comments on posts.id = comments.post_id;

結合したテーブルのカラムを指定する

select posts.id, posts.title, comments.comment from posts inner join comments on posts.id = comments.post_id;

postsやcommentsは一応省略可能。両方のテーブルにあるidは省略できない

select posts.id, title, comment from posts inner join comments on posts.id = comments.post_id;

外部結合

left outer join 〇
right outer join -> sqliteにはない
full outer join -> sqliteにはない

select posts.id, title, comment from posts left outer join comments on posts.id = comments.post_id;

outerは省略可能

select posts.id, title, comment from posts left join comments on posts.id = comments.post_id;

— 交差結合というものもあります

select posts.id, title, comment from posts cross join comments;

rowid auto_increment

テーブルを作成するとrowidというものが内部的に作成される。

idを指定してないがrowidで連番を取得することができる。primary keyを指定したカラムは自動的にrowidが設定される

drop table if exists users;
create table users (
id integer primary key autoincrement,
name
);
insert into users (name) values ('a');
insert into users (name) values ('b');
insert into users (name) values ('c');
select * from users;

データを挿入したときにまた最初から連番を設定しないようにするためのauto_incrementというものもある