sql commands

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;