Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@FreePrgrmr(ざみさん)

sqlite ざっと復習用

Posted at

#スキーマとテーブル

データベースを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　（値のチェックをする）

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参照）

  • || （文字の連結）
    例：

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


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 を使う。
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を削除）

#データの更新、削除

  • データの更新
update users set score = 0,name = '*'|| name where score < 60;

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（一連の処理として行う場合）

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

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

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（索引）

create index score_index on users(score);
create unique index name_index on users(name);
  • .indices（indexの一覧を見る）
  • .schema（構造確認）
  • indexの削除

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;

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;

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 で出力する.

-- 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ファイルにデータを書き出す

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 →　連番が被らないようにしてくれる。

0
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?