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;
