sqlite ざっと復習用

#スキーマとテーブル

データベースをOSに置き換えた場合、データベース内に作成したテーブルは、OSでの『ファイル』に相当します。そしてOSにおけるディレクトリに相当し、テーブルの目的や所有者に応じて格納場所を別にするのがスキーマです。

データベースでは格納する場所が『スキーマ』で、OSでは『ディレクトリ』と認識しておきましょう。

参照：
https://offers.jp/media/programming/a_786

#ファイルの実行

- .readファイルA（ファイルAを実行）
- sqlite3 ファイルA < ファイルB（ファイルAにファイルBを読み込む）

#各種テーブル操作
###テーブルの作成
- create table テーブルA（テーブルA作成）
 →create table if not exist　テーブルA(もし存在しなかったら作ってね)  

####テーブルの制約
- unique （重複した値を許さない）
- not null （null を許さない） 
- default　（何も値を設定しなかったときにデフォルト値を設定する）
- check　（値のチェックをする）

```sql

create table users (
id integer primary key,
name text not null,
score integer default 10 check (score >= 0),
email text unique
);

```
###テーブル削除
drop table if exists テーブルA（テーブルAを削除）

###テーブル名変更
- alter table テーブルA rename to テーブルB;（テーブルAをテーブルBという名前に変更）

###カラムの追加
- alter table テーブル名A add column カラムA データ型A;（データ型AのカラムAをテーブルAに追加）  
 注：SQLite ではフィールドの追加はできるが、フィールド（＝カラム）の名前を変更したり削除する方法は用意されていない。

###レコードの挿入
insert into posts (titleA,bodyA) values ('title1','body1');（titleA,bodyAというフィールドにtitle1,body1というデータをそれぞれ挿入）  
 注：文字列の中でシングルクオーテーションを使いたい時は''とする。
 例：

```
insert into posts (title,body) values ('title4',it''s body4');
```

#テーブルの表示
- select * from テーブルA（テーブルAを表示）
    - .headers on  
select id,name from users;  （カラム名も一緒に表示）

- .mode line
select id,name as user_name from users;  （データを縦に表示）
 - .modeには他にも、データを , 区切りにして表示してくれる csv, html のテーブル形式にしてくれる html, 幅を揃えて見やすくしてくれる column などがある。　（.help参照）
- || （文字の連結）  
例：

```terminal
select id,'Name:'|| name as name ,score + 10 as new_score from users;
```

```sql

id          name          new_score 
----------  ------------  ----------
1           Name:taguchi  53        
2           Name:fkoji    90        
3           Name:tashiro  75        
4           Name:hayashi  64        
5           Name:sato     84        
6           Name:ohashi   
```
###グループ化
- group by team（teamフィールド毎に集計）  
- group byの集計結果に対して抽出条件をつけたい場合は where ではなくて having を使う。

```sql
drop table if exists users;
CREATE table users (
  id integer primary key,
  name text,
  score integer,
  team text
);
insert into users (name, score, team) values ('taguchi', 43, 'team-A');
insert into users (name, score, team) values ('fkoji',   80, 'team-B');
insert into users (name, score, team) values ('tashiro', 65, 'team-B');
insert into users (name, score, team) values ('hayashi', 54, 'team-A');
insert into users (name, score, team) values ('sato',    74, 'team-C');

.headers on
.mode column

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

-- distinct
select distinct team from users;
```

#条件の設定（where）
- where between 50 and 80（50以上80以下）
- where name in ('taguchi', 'fkoji'）  
→ 名前が taguchi か fkoji のいずれか
- like（前方一致、部分一致）  
  →_が任意の一文字、％が〇文字以上の任意の文字列。  
例：select * from users where name like 's___';（s から始まる 4 文字の名前）  
例：where name like %i%　（前と後ろは何でも良いけれど i を含んでいないといけない）  
- where name like '%@%' escape '@'（＠をエスケープ文字とする）→％を最後に含む文字列を表示
- order by score （scoreの小さいものから順番に並び替える）
    - order by desc（scoreの大きいものから順に並び替える）
- limit 3（3件まで抽出）  
例：select * from users order by score desc limit 3（usersテーブルからscoreの大きいものを順に3つ抽出）

- offset 2（2件飛ばす）  
例：select * from users order by score desc limit 3 offset 2（usersテーブルからscoreの大きいものを順に3つ抽出（上位2件は飛ばす））

#view
- create view hiscore as select * from users order by score desc limit 5;（hiscoreという名前でviewを作成）
- drop view if exists hiscore（hiscoreという名前のviewを削除）

#データの更新、削除
- データの更新

```sql
update users set score = 0,name = '*'|| name where score < 60;
```
```sql
id          name        score       team      
----------  ----------  ----------  ----------
1           *taguchi    0           team-A    
2           fkoji       80          team-B    
3           tashiro     65          team-B    
4           *hayashi    0           team-A    
5           sato        74          team-C    
```
- データの削除
delete from テーブルA（テーブルAを削除）

#transaction（一連の処理として行う場合）
```sql
begin transaction
-- ここに処理を書く
-- rollback（transaction 中に処理の影響で何らかの不具合が起きた場合、transaction を終了して変更内容を破棄する。）
commit;
```

#trigger
あるテーブルで何らかの変更がされたときに、それをトリガーにして他のテーブルを合わせて変更する。

```sql
create table messages (message);

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

update users set score = score + 30;
select * from messages;
```
←usersテーブルのscoreを更新した際に、scoreが100を超えたらmessageテーブルにメッセージを挿入。

#index（索引）
```sql
create index score_index on users(score);
create unique index name_index on users(name);
```
 
- .indices（indexの一覧を見る）
- .schema（構造確認）
- indexの削除

```sql

drop index if exists score_index;
drop index if exists name_index;
```

#内部結合
2つ以上のテーブルがある時に、両方にデータがあるものだけを結合する方法。
テーブルpostのidとテーブルcommentsのpost_idを結合。

```
select * from posts inner join comments on posts.id = comments.post_id;
```                     
```sql
id          title       body        id          post_id     comment   
----------  ----------  ----------  ----------  ----------  ----------
1           t1          b1          1           1           c1        
1           t1          b1          2           1           c2     
```

#外部結合
2つのテーブルの内どちらかのテーブルにしか存在しないものに関しても取得する。

```
select posts.id,title,body,comment from posts left outer join comments on posts.id = comments.post_id;
```
```sql
id          title       body        comment   
----------  ----------  ----------  ----------
1           t1          b1          c1        
1           t1          b1          c2        
2           t2          b1       
```



#交差結合
総当り戦的に全てを抽出する。

```
select posts.id,title,body,comment from posts cross join comments;
```
```
id          title       body        comment   
----------  ----------  ----------  ----------
1           t1          b1          c1        
1           t1          b1          c2        
2           t2          b1          c1        
2           t2          b1          c2   
```

#.dump
データベースの内容を SQL で出力する.


```sql
-- rowid

drop table if exists users;
create table users (
  -- id integer primary key, -- => rowid
  id integer primary key autoincrement,
  name
);
insert into users (name) values ('a');
insert into users (name) values ('b');
insert into users (name) values ('c');


.output users.dump.sql --出力先を決める
.dump users　-- usersテーブルを他のファイルに出力する。
```

#csvファイルからデータを読みこんだり、csvファイルにデータを書き出す

```sql
drop table if exists users;
CREATE table users (
id integer primary key,
name text,
score integer
);

insert into users (name,score) values ('a',10);
insert into users (name,score) values ('b',20);
insert into users (name,score) values ('c',20);

create table temp (name,score);

.mode csv
.import users.csv temp　--users.csvをtempテーブルにimport。

insert into users (name,score) select name,score from temp;
select * from users;
drop table temp;
 
.mode csv
.output users_out.csv
select * from users;-- この実行結果がusers_out.csvに出力される。
```

#各種コマンド

- .table（テーブル一覧）
- .schema（テーブル構造見る）
- .schema テーブルA（テーブルAの構造見る）



#忘れがちなデータ型
- integer primary key（主キー）(自動で連番振ってくれる)
- real（浮動小数点数）
- double（浮動小数点数）
- blob（Binary　Large　Object）（そのままデータを保存）（画像やファイルをそのまま保存）

#その他
rowid = id integer primary key（自動で連番振ってくれる）（特に設定しなくてもrowidは使える）
auto increment →　連番が被らないようにしてくれる。