SQLite のデータのバックアップと復元

SQLite のデータのバックアップと復元

#18 データのバックアップ・復元
http://dotinstall.com/lessons/basic_sqlite/6418
を参考に
テーブル構造、データを外部ファイルに出力し
それを取り込む方法を学習

今回使用するのは
.dump
これはDBの内容をSQL文で出力する
バックアップを取るのに便利

テーブルだけバックアップするなら
.dump テーブル名

すべてのテーブルをバックアップするなら
テーブル名を指定せず
.dump
とすればOK

これらを直接ファイルに出力するには
出力先を変更する

出力先の設定確認は
.show
で行える

現在の設定は
echo: off
explain: off
headers: on
mode: list
nullvalue: ""
output: stdout
separator: ","
stats: off
width:
で
出力先の設定部分は
output: stdout
になり、意味は画面表示

これを変更することで
ファイルに出力できるようになる

変更は
.output 出力するファイル名
今回は
.output data.dump
として
.dump data
と実行すると
data テーブルの中身が
SQL文でファイルに記述される

data.dump ファイルの中身を
cat data.dump で確認すると

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE data(name,score,team_text);
INSERT INTO "data" VALUES('hebereke',1000,'A');
INSERT INTO "data" VALUES('hiroshi',400,'C');
INSERT INTO "data" VALUES('tomonaga',400,'B');
COMMIT;

となっているのが確認できる

ちなみに、
.output の出力先を ファイルにした場合
.show などの結果もファイルに出力されてしまう
この変更は、一度
.exit で sqlite を抜けるか
もしくは
.output stdout
とすれば、元に戻る

ちなみに、ファイルに出力されたSQL文は
vim などで編集することが可能

変更後、再度読み込みしたいときには
.read
を使う

今回なら
.read data.dump
でOK
ただし、元のデータがあると、すでに存在していますという
エラーがでてしまうので、一度
drop table data;
.read data.dump
で読み込みする

SQL文にする使い道で一番大きいのは
カラムの編集
SQLite にはカラムを削除できないので
.dump で一度出力して
vim などでSQL文を修正し
失敗したテーブルを
drop table テーブル名
で削除
そして、テーブル作成のSQLを修正した dump ファイルを
.read で読み込んで修正というやりかたが使える

SQLite で外部ファイルからデータ読み込み

SQLite で外部ファイルからデータ読み込み

#17 外部ファイルからデータを取り込む
http://dotinstall.com/lessons/basic_sqlite/6417
を参考に、外部ファイルからデータの読み込みを学習

これにより、mysql で .sql ファイルからデータを
読み込んで実行するようなことができる

今回は、users.txt というテキストファイルに
データを記述
まず、vim でも gedit でもOKなので
データを書いて
users.txt というファイルで保存

中身は
taguchi,200
sasaki,400
yamashita,120
hebereke,800

また、新しいデータをとりこむため
練習テーブルも作成する

create table infile(name,score);
で infileテーブルと
name
score というカラムを作成

このテーブルに
users.txt の中身をいれるには
.import を使う

ただし、その前に区切り文字を指定しておく
デフォルトだと
データの区切り文字は | となっているけど
users.txt の中身のデータは ,　で区切ってあるので
これを変更する

現在の設定は
.show
でみることができる

ちなみに、現在は
echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: "|"
stats: off
width:

これをみると、区切り文字を意味する
separator: "|"
となっているので、これを変更する

| から , に変更するには
.separator ,
とすればOK

.show
で確認すると

echo: off
explain: off
headers: off
mode: list
nullvalue: ""
output: stdout
separator: ","
stats: off
width:
となり
separator: ","　に変更されている

この状態で
.import 読み込むファイル テーブル名
とすると
データを入れることができる

今回は users.txt を infile テーブルに入れるので
.import users.txt infile

select* from infile;
で確認すると

taguchi,200
sasaki,400
yamashita,120
hebereke,800
というように、反映されているのが確認できる

あと、設定で変更しておくと便利なのが
.header on
としておくと

デフォルトでは
select* from infile;
を実行すると
taguchi,200
sasaki,400
yamashita,120
hebereke,800
だけど

select* from infile;
name,score
taguchi,200
sasaki,400
yamashita,120
hebereke,800
というように、一番上にカラム名が表示されるようになる

SQLite での複数テーブルの取り扱い

#16 複数のテーブルを扱ってみよう
http://dotinstall.com/lessons/basic_sqlite/6416
を参考に
複数のテーブルからデータを抽出、集計する方法について学習

まずは、必要なテーブルなどを作成
create table games(user_id,score);
create table player(id integer primary key autoincrement,name,team);

次に
insert into player(name,team) values('taguchi','A');
insert into player(name,team) values('sasaki','B');
insert into player(name,team) values('himura','B');
insert into player(name,team) values('yoshino','B');
でデータ入力

確認は
select * from player;

次に、ゲームデータも挿入しておく
insert into games(user_id,score) values(1,200);
insert into games(user_id,score) values(1,300);
insert into games(user_id,score) values(1,20);
insert into games(user_id,score) values(1,220);
insert into games(user_id,score) values(1,120);
insert into games(user_id,score) values(2,50);
insert into games(user_id,score) values(2,40);
insert into games(user_id,score) values(3,40);
insert into games(user_id,score) values(3,120);
insert into games(user_id,score) values(3,140);
insert into games(user_id,score) values(4,140);
insert into games(user_id,score) values(4,10);
insert into games(user_id,score) values(4,100);

最後に、select * from games;
で内容を確認

これで準備OK

これで、ユーザごとに合計点数を集計する

複数テーブルにまたがる場合

select id, name, team, sum(score) from player, games where player.id = games.user_id group by player.id;
というように実行する

構文としては
select 選択するカラム名を,で区切って複数指定
from テーブル名を,で区切って複数指定
where 検索条件
というかんじになる


複数テーブルにまたがるときには
まずカラム名を指定するけど
重複する場合は
テーブル名.カラム名というように書いて指定する

今回の場合なら
games.user_id
とか
games.name
というように、
テーブル名とカラムを . でつなげて書く

そして、今回の where の条件は
player テーブルのid
と
games テーブルの user_id
が同じものを集計したいので

where
player.id = games.user_id

今回は user_id で集計をしたいので
group by player.id;
となる
group by は　～でという意味


select id, name, team, sum(score) from player, games where player.id = games.user_id group by player.id;
の
実行結果は

1|taguchi|A|860
2|sasaki|B|90
3|himura|B|300
4|yoshino|B|250

となる
これは、player のIDが人の番号で
games の user_id も人の番号となっているんで
連携しているので簡単に集計ができている

SQLite のデータ削除

SQLite のデータ削除

#15 データを削除してみよう
http://dotinstall.com/lessons/basic_sqlite/6415
を参考に
SQLite でのデータ削除方法を学習

データ削除には
delete を使う

delete from data where score <=100;
というように
score が 100 以下のものを削除するように実行すると

hebereke|1000|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B

が

hebereke|1000|A
hiroshi|400|C
hiyano|500|A
tomonaga|400|B

というように
消えているのが確認できます

書式にすると
delete from テーブル名 where 消す条件

where をつけずに実行すると
テーブルの中身がすべて消えるので注意

例えば
select * from user;
で
taguchi|200|||
sasaki|100|||
hiroshi|400|||
hiyano|500|||
||A||
||B||
higuchi||||2013-06-03 10:48:19
となっているのを

delete from user ;
とすると
すべて消えます

select * from user;
を実行しても、空なので何もでてきません


あと、SQLite で覚えておくと便利なのが
ROWID
これは、中のデータを連番で管理している番号を表示するもの

select ROWID, * from テーブル名;
とすると、番号つきで表示される

select ROWID, * from data;
1|hebereke|1000|A
3|hiroshi|400|C
4|hiyano|500|A
5|tomonaga|400|B
というかんじ

この ROWID の使い道は、where で消したいものが
うまく指定できないときに使う

delete from data where ROWID=4;
とすると
1|hebereke|1000|A
3|hiroshi|400|C
5|tomonaga|400|B
となり
ROWID=4 のところだけ消える

SQLite のデータ更新方法

SQLite のデータ更新方法

#14 データを更新してみよう
http://dotinstall.com/lessons/basic_sqlite/6414
を参考に
SQLite のデータ更新方法を学習

更新には update を使う

update data set name ='dotinstall_taguchi' where name='taguchi';
というようにすれば
name='taguchi'
の部分だけ
name ='dotinstall_taguchi'
となる

このときに、where で条件を指定しないと
名前がみなdotinstall_taguchi になってしまうので注意

構文にすると
update テーブル名 set カラム名 =新しい値 where 対象のカラムとデータ
となる

これにより
taguchi|200|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B

から
dotinstalltaguchi|200|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B

に変更される

name だけでなく
name と score というように、複数のカラムの変更も可能
その場合には
カラムを , で区切って実行する

update data set name='hebereke',score=1000 where name='dotinstalltaguchi';
というように、
dotinstalltaguchi のところを
name=hebereke
score=1000
に修正してみた

select * from data;
で結果を確認してみると

hebereke|1000|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B

となっていて、修正できているのが確認できる

これにより、値の修正が可能なので
間違えて入力したり
ゲームに使う時に値を変更しながらDBへ格納することが
可能になる