ソフトウェアエンジニアの技術ブログ：Software engineer tech blog

.headers on
.mode column
update users set score = 0, name ='* ' || name where score < 60;
delete from users where score = 0;
select * from users;
&#91;/sql&#93;

transaction
&#91;sql&#93;
begin transaction;
update users set score = score - 10 where name = 'sato';
update users set score = score + 10 where name = 'kimura';
commit;
select * from users;
rollback;
&#91;/sql&#93;

trigger
&#91;sql&#93;
.headers on
.mode column
create table messages (message);

create trigger new_winner update of score on 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;

index

create index score_index on users(score);
create unique index name_index on users(name);

.indices users
.schema users

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

日時

select datetime('now', '+09:00:00');
select date('2016-12-01', '+3 months', 'start of month', '-1 day');

create table if not exists users (
  id integer primary key,
  name varchar(255) not null,
  score integer default 10 check(score >= 0),
  team text,
  created datetime default (datetime('now', '+09:00:00'))
);

複数テーブルの作成

drop table if exists posts;
create table if not exists posts (
  id integer primary key,
  title text,
  body text,
  created datetime default (datetime('now', '+09:00:00'))
);

insert into posts (id, title, body) values (1, 't1', 'b1');
insert into posts (id, title, body) values (2, 't2', 'b2');

drop table if exists comments;
create table if not exists comments (
    id integer primary key,
    post_id integer,
    comment text,
    created datetime default (datetime('now', '+09:00:00'))
  );
  insert into comments (id, post_id, comment) values (1, 1, 'c1');
  insert into comments (id, post_id, comment) values (2, 1, 'c2');

.headers on
.mode column

select * from posts;
select * from comments;

内部結合 inner join、外部結合　left outer join、交差結合 cross join

select * from posts inner join comments on posts.id = comments.post_id;
select * from posts join comments on posts.id = comments.post_id;
select posts.id, posts.title, comment from posts join comments on posts.id = comments.post_id;
select * from posts left outer join comments on posts.id = comments.post_id;
select * from posts cross join comments;

row id ※integer primary key -> row id

select rowid, * from users;

dump

.output users.dump.sql
.dump users

外部データの取り込み

drop table if exists users;
create table users (
  id integer primary key,
  name,
  score
);
insert into users (name, score) values ('a', 10);
insert into users (name, score) values ('b', 20);
insert into users (name, score) values ('c', 30);

create table temp(name, score);

.mode csv
.import users.csv temp

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;

drop table if exists users;
create table if not exists users (
  id integer primary key,
  name varchar(255),
  score integer,
  email text unique
);

insert into users (email) values ('yamada@gmail.com');
insert into users (email) values ('yamada@gmail.com');

select * from users;

データの制約

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

insert into users (name, score) values ('yamada', -30);

select * from users;

select文

.headers on 
select id, name as user_name from users;
.mode line
select * from users;
select * from users where score > 60;
select * from users where score is null;
select * from users where name = 'ito';
select * from users where name like '%a%';
select * from users where name like '%@%' escape '@';
select * from users where name glob '*i*';
select * from users where name glob '[ts]*';

mode .html, mode.columなどもあります。

order by, limit, offset, view

select * from users where score is not null order by score;
select * from users where score is not null order by score desc;
select * from users where score is not null order by score desc limit 3;
select * from users where score is not null order by score desc limit 3 offset 2;
create view hiscore as select * from users order by score desc limit 5;
select * from hiscore;
drop view if exists hiscore;

演算

.headers on
.mode column
select id, name, score + 10 from users;
select count(id), max(score), min(score), avg(score) from users;
select name, length(name), upper(name), substr(name, 2, 3) from users;
select last_insert_rowid();
select random();
select * from users order by random() limit 1;

insert into users (name, score, team) values ('yamada', 30, 'tema-A');
insert into users (name, score, team) values ('yoshimoto', 55, 'tema-B');
insert into users (name, score, team) values ('ito', 22, 'tema-D');
insert into users (name, score, team) values ('sato', 18, 'tema-D');
insert into users (name, score, team) values ('kimura', 66, 'tema-A');
insert into users (name, score, team) values ('hayashi', 88, 'tema-B');
insert into users (name, score, team) values ('seto', 22, 'tema-C');
insert into users (name, score, team) values ('mimura', null, 'tema-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) > 40;
select distinct team from users;

.headers on
.mode column
select
  id, name, score,
  case
   when score > 70 then 'A'
   when score > 50 then 'B'
   else 'C'
  end as rank
from users;

[vagrant@localhost sqlite]$ sqlite3 myapp.db
SQLite version 3.6.20
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .hlp

テーブルの作成

sqlite> create table posts (title, body);
sqlite> .exit
[vagrant@localhost sqlite]$ ls
myapp.db

ファイルの削除

[vagrant@localhost sqlite]$ rm myapp.db

外部ファイル読み込み

sqlite> .read commands.sql
[vagrant@localhost sqlite]$ sqlite3 myapp.db < commands.sql

テーブルの作成

create table if not exists posts (
  id integer primary key,
  title varchar(255),
  body varchar(255)
);

-- lists
.tables

-- table structure
.schema

データ型:integer, tex, real, blob, null

テーブル削除

drop table if exists posts;

テーブルの変更、カラムの追加

--list talbes
alter table posts rename to articles;

--add column
alter table articles add column email text;

レコードの挿入

insert into posts (title, body) values ('title1', 'body1');
insert into posts (id, title, body) values (null, 'title2', 'body2');
insert into posts (title, body) values ('title3', 'body3');
insert into posts (title, body) values ('title4', 'it''s body4');

select * from posts;