カテゴリー: 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 で外部ファイルからデータ読み込み
#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 のデータ削除
#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 のデータ更新方法
#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へ格納することが
可能になる

SQLite での日付、時刻の取り扱い
#13 日付・時刻を扱ってみよう
http://dotinstall.com/lessons/basic_sqlite/6413
を参考に
日付、時刻について学習
SQLite では、いくつかの定数で時刻を得ることができる
select current_time;
で現在時刻の取得
select current_date;
で現在の日付
select current_timestamp;
で現在の日付と時刻が表示できる
試しに
create table date(name,crated);
でテーブルを作成して
insert into date(name,crated) values(‘higuchi’,current_timestamp);
でデータ挿入
select * from date;
higuchi|2013-06-03 10:52:36
というように、現在時刻が入っていることが確認できる
日付関連リファレンスは
http://www.sqlite.org/lang_datefunc.html
を参考に
この中で一番使われるのが
strftime(format, timestring, modifier, modifier, …)
というタイプ
指定できるフォーマットは
%d
day of month: 00
%f
fractional seconds: SS.SSS
%H
hour: 00-24
%j
day of year: 001-366
%J
Julian day number
%m
month: 01-12
%M
minute: 00-59
%s
seconds since 1970-01-01
%S
seconds: 00-59
%w
day of week 0-6 with Sunday==0
%W
week of year: 00-53
%Y
year: 0000-9999
%% %
これらのフォーマットを利用して
current_timestamp を日本語表記にするには
strftime(‘%Y年’,current_timestamp)
というようにする
select current_timestamp;
の場合
2013-06-03 11:01:44
となるけど
select strftime(‘%Y年’,current_timestamp);
とすると
2013年
となる
時刻の取得関連はゲーム関連で使うことが多いので
覚えておくと便利

SQLite のデータ集計
#12 データを集計してみよう
http://dotinstall.com/lessons/basic_sqlite/6412
を参考に、データ集計に使う関数を学習
すでに作成してある user テーブルに
team test
というカラムを追加したいので
alter table user add column team text;
として
alter table テーブル名 add column カラム名;
としてカラムを追加できる
あとは、追加したカラムに値を追加する
データ追加は insert を使う
しかし、
insert into user(team text) values(‘A’);
とすると
Error: near “text”: syntax error
となるので、別のテーブルを作成し、データを追加
create table data(name,score,team_text);
でテーブルを作成し
insert into data(name,score,team_text) values(‘taguchi’,200,’A’);
insert into data(name,score,team_text) values(‘sasaki’,100,’B’);
insert into data(name,score,team_text) values(‘hiroshi’,400,’C’);
insert into data(name,score,team_text) values(‘hiyano’,500,’A’);
insert into data(name,score,team_text) values(‘tomonaga’,400,’B’);
でデータ挿入
確認のため
select * from data;
で中身を確認
taguchi|200|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B
これで準備はOK
これで実践開
select distinct とすると、ユニークな値を抽出する
select distinct team_text from data;
とすると、team_text の中から
A
B
C
というように、チームをわけて出力することができる
つまり、被らないように出力する
使い道としては識別するときに使う
次にグループごとのデータの集計
集計には Excel とおなじように
sum() で行うことができる
また、グループわけして行いたいのなら
group by
を使う
今回は
select team_text ,sum(score) from data group by team_text;
内容は
select team_text ,sum(score) from data
で
data テーブルにある
team_text の score の値を合計する
group by team_text;
で、team_text ごとに
という意味
group by は
～ごとに
というかんじで覚えるとわかりやすい
もともとのデータが
taguchi|200|A
sasaki|100|B
hiroshi|400|C
hiyano|500|A
tomonaga|400|B
なので、
select team_text ,sum(score) from data group by team_text;
でチームごとの合計点にすると
A|700
B|500
C|400
となる
ゲームや、成績表など、いろいろと用途はあるので
覚えておくと便利

SQLite の組み込み関数
#11 組み込み関数を使おう
http://dotinstall.com/lessons/basic_sqlite/6411
を参考に、組み込み関数の使い方を学習
まず、件数を取得する cout
select cout(*) from user;
()の中身にカラムを指定する
• はすべてを意味するワイルドカード
次に、最大値の取得をする max()
select max(score) from user;
こうすることで、score の最大値を取得できる
逆に、最小値を取得したいのなら min()
select min(score) from user;
これらは、ゲーム作成とかに使える
次に、乱数を作成する random()
これはテーブルとか関係なく出来るので
select random();
でOK
もちろん、これも select をつかった通常のSQLとして
利用も可能
select name from user order by random() limit 1;
このようにすれば
抽選でだれか１人だけ選ぶというような仕組みがつくれる
応募者リストを作成し、そこからランダムで
選ぶときなどに有効
構文が長いのでわけてメモすると
select name from user
user テーブルの name の中から選択
order by random()
ランダムで
limit 1
1人だけ
という意味のSQLになる
length() を使うことで、文字列の長さの取得が可能
select name, length(name) from user;
選択したカラムの後に , で一度区切っていることに注意
ちなみに、配列の数え方ではないので
通常の１から数えるやりかたになる
また、どんなデータ型なのか確認するには
typeof() を使う
こちらも カラム名の後 , で区切っているので注意
select name, typeof(name) from user;
としている
seect カラム名 typeof(カラム名） from テーブル名;
という構文
関数に関するリファレンスは
http://www.sqlite.org/lang_corefunc.html
に掲載されているので
こちらを参考にすると効率的

SQLite でデータ抽出
#10 データを抽出してみよう
http://dotinstall.com/lessons/basic_sqlite/6410
を参考に、データの抽出
select 見たいカラム名 from テーブル名
でみることができる
見たいカラム名には * とすると
指定したテーブルの中のカラム全部が対象になる
mysql とかでもよくみかける構文はこの意味
動画を参考にやろうとしても
データがないので、まずは必要なテーブルとかを作成
動画の .schema を元に作成してデータ挿入しておく
create table user(name,score);
insert into user(name,score) values(‘taguchi’,200);
insert into user(name,score) values(‘sasaki’,100);
insert into user(name,score) values(‘hiroshi’,400);
insert into user(name,score) values(‘hiyano’,500);
すでに users というテーブルは使っているので
user というテーブルを作成
select score from user;
で同じ結果をみることができる
構文は
select カラム名 from テーブル名;
ただ引き出すだけだと意味があまりないので
並び替えと合わせて使うことで効率的にデータ管理参照ができる
select * from user order by score;
このように
order by をつけることで
score の小さい順に並び替えが可能
select * from user order by score desc;
というように、 選択したカラム名の後に
desc
をつけると、今度は大きい順にならべることができる
今回なら
score の大きい順に並び替えで表示している
また、検索結果の表示を絞り込むことも可能
select * from user order by score desc limit 3;
とすれば
score の値の大きい順上位３つからの表示となる
ちょっと長い構文なので
メモ
order by score desc
でsocre の中から大きい順に表示
desc をつけると大きい順になる
limit 3
で３つ表示
となっている
select * from user order by score limit 3;
とすれば、score の小さい順に表示になる
よく使うものとして
条件を指定するため where を使うやりかた
select * from user where score >= 200;
とすることで、
score >= 200 の条件に該当するものだけ
表示するようになる
構文は
select カラム名 from テーブル名 where 条件
あと、条件を指定するときに
java script とか PHP などでは
if のところの条件で
～でないという意味で
!=
を使ったけど
SQLite の場合には
<>
と書く
select * from user where name <> ‘taguchi’;
とすれば、名前が taguchi 以外の人の名前が
表示される
あと、name が taguchi のみ表示とするなら
select * from user where name = ‘taguchi’;
となる
java script や PHP の場合
name == ‘taguchi’
と条件式に書くけど、SQLite の場合は
name = ‘taguch’
となるので注意
ちなみに、文字列の場合は = の代わりに like でもOK
select * from user where name = ‘taguchi’;
と
select * from user where name like ‘taguchi’;
は同じ結果になる
あと、SQLite では、ワイルドカードが使えるけど
通常、 Linux コマンドとかで
ワイルドカードを示すのは * だけど
SQLite では % になる
select * from user where name like ‘tag%’;
で
name の中で
tag を含む、すべての名前ということになります

SQLite でデータ挿入
#09 データを挿入してみよう
http://dotinstall.com/lessons/basic_sqlite/6409
を参考に
SQLite でのデータ挿入について学習
データ挿入には
insert into テーブル名(カラム名,カラム名) values(挿入するデータ, 挿入するデータ）
といっようになります
挿入するデータが文字列なら
” でかこむのを忘れずに
人の名前とかメールアドレスは文字列なので
‘hebereke@gmail.com’
とか
‘heberekemaru’
というようにします
文字列の中に’ を入れたいときには
”というように、続けていれればOK
”Uaaaaaa”
というかんじです
これで、挿入されるときには
‘Uaaaaa’
となります
ちなみに、null はデータなしという意味なので
これは文字列ではないので
null
だけでOK