Linux & Android Dialy

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

SQLite でテーブル作成のオプションその２
#08 テーブル作成時のオプション (2)
http://dotinstall.com/lessons/basic_sqlite/6408
を参考に、オプションについて学習
今回はチェック機能
入力される値が０以上になるようにチェックするには
カラム名 check(カラム名>0)
というようにする
create table lessons(title,count_lessons check(count_lessons>0));
というようになる
これを
.schema で確認すると
CREATE TABLE lessons(title, count_lessons check(count_lessons>0));
となっていて
count_lessons > 0 というように０以上になるように
設定されているのが確認できる
次に、カラムにインデックスを付ける方法
これにより、検索の高速化ができる
primary key
unique
などもインデックスの役割をもつ
インデックス作成するにいは
create index カラム名 on テーブル名(対象カラム）;
となる
今回は
create index age on users(age);
これで、 index ageを作成して
対象は
users テーブルの age というカラム
ということになる
あとは、実行結果を確認するため
.schema
で確認すると
CREATE INDEX age on users(age);
となっているのが確認できる

SQLite テーブル作成時のオプション
#07 テーブル作成時のオプション (1)
http://dotinstall.com/lessons/basic_sqlite/6407
を参考に
テーブル作成時のオプションについて学習
テーブルのカラムに型を指定したいなら
カラムの後にデータ型を指定する
create table users(name text, email text);
テーブルに自動連番を付けるID役のカラムをいれるには
id integer primary key autoincrement
というように
カラム id に　整数型の integer を指定して
primary key をつけることで主キーになり
さらに
autoincrement を付けることで
自動連番になる
また、名前を入力するカラム name が空なら
エラーにするというようにするには
テーブル作成のときに not null をつける
name text not null
というようになる
これは
name がカラム名
text がTEXT型
not null は空になったらエラー
という意味
あと、Web サービスなどではEメールがログインにつかったりするように重複しないようにするので
こんなときには
unique をつける
これで、被らないようにできる
email text unique
というようになる
email は　カラム名
text はTEXT型
unique はユニーク（一意）であるという意味
デフォルト値の設定をしたいときには
default 値
というようにする
age integer default 20
age はカラム名
integer は整数型
default 20 はデフォルト値を２０
という意味
ゲームとかで
初期値を設定するのに使える
全部つなげて、テーブルを作成すると
create table users(id integer primary key autoincrement, name text not null, email text unique, age integer default 20);
となる
id integer primary key autoincrement,
で登録ID
name text not null,
でユーザ名
email text unique,
でメールアドレス
age integer default 20
で年齢
という顧客データのようなものが作れる