データ表示のコマンド2
まず、最初に表示方法を変える。
sqlite> select * from earn;
Mouse|3|2400
SmartPhone|12|32000
Pc|5|68000
sqlite>
sqlite> .headers on→ヘッダ表示
sqlite> .mode csv→カンマ区切りで出力
sqlite>.mode column→カラム毎に左揃えで出力
sqlite> select * from earn;
name num price
---------- ---------- ----------
Mouse 3 2400
SmartPhone 12 32000
Pc 5 68000
■AS句
取得データのカラムに別名を付ける。
sqlite> select name, num, price as cost from earn;
name num cost
---------- ---------- ----------
Mouse 3 2400
SmartPhone 12 32000
Pc 5 68000
sqlite>
演算子を使ってカラムの値とカラムの値の演算を行った結果を表示する
sqlite> select name, num, price, num * price as earn from earn;
name num price earn
---------- ---------- ---------- ----------
Mouse 3 2400 7200
SmartPhone 12 32000 384000
Pc 5 68000 340000
■DISTINCT句
重複するデータを除外したい時
sqlite> select * from product2;
name size color
---------- ---------- ----------
word table big black
mouse small white
desk watch big green
glasses samll black
tv big black
telephone samll white
sqlite> select distinct size from product2;
size
----------
big
small
samll
sqlite>
■LIMIT句
先頭のデータから4つのデータを取得
sqlite> select * from user1;
id name address
---------- ---------- ----------
1 Sasaki Osaka
2 Furta Chiba
3 Yamane Tokyo
4 Uesugi Tokyo
5 Yoshida Nagoya
6 Endoh Osaka
7 Mukai Tokyo
8 Tada Yokohama
sqlite> select * from user1 limit 4;
id name address
---------- ---------- ----------
1 Sasaki Osaka
2 Furta Chiba
3 Yamane Tokyo
4 Uesugi Tokyo
sqlite>
5番目から三つデータを取り出す
sqlite> select * from user1 limit 3 offset 4;
id name address
---------- ---------- ----------
5 Yoshida Nagoya
6 Endoh Osaka
7 Mukai Tokyo
limitだけでも出来る。
sqlite> select * from user1 limit 4, 3;
id name address
---------- ---------- ----------
5 Yoshida Nagoya
6 Endoh Osaka
7 Mukai Tokyo
sqlite>
sqlite> select * from user1 order by address limit 5;
id name address
---------- ---------- ----------
2 Furta Chiba
5 Yoshida Nagoya
1 Sasaki Osaka
6 Endoh Osaka
3 Yamane Tokyo
■四則演算
sqlite> select *, num * price - discount from earn;
name num price discount num * price - discount
---------- ---------- ---------- ---------- ----------------------
PC 1 45000 0 45000
Desk 2 38000 3000 73000
Mouse 3 3000 0 9000
Watch 2 8000 1000 15000
Printer 1 7000 0 7000
sqlite> select *, num * price - discount as result from earn;
name num price discount result
---------- ---------- ---------- ---------- ----------
PC 1 45000 0 45000
Desk 2 38000 3000 73000
Mouse 3 3000 0 9000
Watch 2 8000 1000 15000
Printer 1 7000 0 7000
■CASE句
設定した条件に応じた値を取得
sqlite> select * from test;
name result
---------- ----------
Kondo 85
Yasuda 53
Suzuki 78
Hori 91
Yamada 69
Okuda 42
sqlite> select
...> name, result,
...> case
...> when result > 80 then 'Pass' //resultが80を超えた時、judgmentはPass
...> when result > 60 then 'ReTest'//resultが60～80までの時、judgmentはReTest
...> else 'Fail' //60以下時、judgmentはFail
...> end as judgment
...> from test;
name result judgment
---------- ---------- ----------
Kondo 85 Pass
Yasuda 53 Fail
Suzuki 78 ReTest
Hori 91 Pass
Yamada 69 ReTest
Okuda 42 Fail
sqlite>
■count関数
全体の行数を得たい時
sqlite> select count(*) from user;
count(*)
----------
9
sqlite>
■グループ化
sqlite> select address, count(*) from user group by address;
address count(*)
---------- ----------
2
Kanagawa 1
Osaka 1
Tokyo 4
kanagawa 1
sqlite>
■HAVING句
グループ化した後のデータに対して条件式を設定することが出来る様に成る。
sqlite> select address, count(*) from user group by address having count(*) >= 2;
address count(*)
---------- ----------
2
Tokyo 4
sqlite>
