sqlite基礎コマンド - 社会不適合破壊的お味噌マン

Sqliteのデータ型

NULL
INTERGER (整数)
REAL (小数)
TEXT (文字列)
BLOB(Binary Large Object)

データベースの作成

$ sqlite3 blog.sqlite3

sqlite3（コマンド）データベース名.sqlite3(拡張子)

.help =>ヘルプを出す
.exit =>終了
.table =>テーブル確認
.schema =>テーブルの構成確認

テーブルを作成する

create table テーブル名 (カラム, カラム); <-

sqlite> create table users (name, email);

テーブルの確認

##テーブル確認
sqlite> .tables
users

##.schema　テーブル名指定
sqlite> .schema users
CREATE TABLE users (name, email);

##.schema　テーブル全表示
sqlite> .schema
CREATE TABLE users (name, email);
CREATE TABLE posts (title, content);

テーブルの削除

sqlite> drop table posts;
sqlite> .tables
users

テーブル名の変更

alter table users(テーブル名) rename(名前変更) to userss(変更したい名前);

sqlite> alter table users rename to userss;
sqlite> .tables
userss
sqlite> alter table userss rename to users;
sqlite> .tables
users

カラムの追加

sqlite> alter table users add column password;
sqlite> .schema users
CREATE TABLE "users" (name, email, password);

データ型の指定

sqlite>  create table category(id integer, category text);
sqlite> .schema category
CREATE TABLE category(id integer, category text);

データ挿入

sqlite>  insert into users (id, email) values (1, 'keioka@yahoo.com');
insert into テーブル名　カラム名　value 値

主キーとオートインクリメント

sqlite> create table users(id integer primary key autoincrement, name text);
sqlite> .schema users
CREATE TABLE users(id integer primary key autoincrement, name text);

Validation (unique と not null)

##引数にunique と not null
sqlite> create table users (id integer primary key autoincrement, name text not null, email text not null unique, password text not null);

sqlite> insert into users (name, email) values ('keioka', 'keioka0828@gmail.com');
Error: NOT NULL constraint failed: users.password

sqlite> insert into users (name, email, password) values ('keioka', 'keioka0828@gmail.com', '00000');
Error: UNIQUE constraint failed: users.email

##check(condition)
age check(age>20)

sqlite> create table users (id integer primary key autoincrement, name text default "Anonymous", age check(age>20), email text not null unique, password text not null);

sqlite> insert into users (age, email, password) values (18, 'a', '00000');
Error: CHECK constraint failed: users

Default値設定

##引数に default と default値
sqlite> create table users (id integer primary key autoincrement, name text default "Anonymous", email text not null unique, password text not null);

レコード確認

## * 全部
sqlite> select * from users;
1|kei|23|keioka@gmail.com|00000
2|Tom|32|Tom@mail|00000

##select カラム名
sqlite> select age from users;
23
32

##order by 

sqlite> select * from users order by age;

1|Anonymous|23|keioka|08000
4|Max|27|max@mail|00000
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
6|Jason|33|jason@mail|00000
5|Jack|43|jack@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000


sqlite> select * from users order by age desc;

7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
1|Anonymous|23|keioka|0000

limit

sqlite> select * from users limit 3;

1|Anonymous|23|keioka|00000
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000

where

sqlite> select * from users where age > 30 ;

2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000

等価記号

## カラム名　=   値
sqlite> select * from users where name = 'Tom';

2|Tom|32|Tom@mail|00000

## カラム名　<>   値　以外
sqlite> select * from users where name <> 'Tom';

1|Anonymous|23|keioka|082889
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000

##　カラム名 like 値　'k%' => kから始まる文字列 '%n'=>nで終わる文字列
sqlite> select * from users where name like '%n';

3|Son|32|ssss@mail|00000
6|Jason|33|jason@mail|00000

組み込み関数

#count
sqlite> select count (*) from users;
8

#max/min
sqlite> select max(age) from users;
65
sqlite> select min(age) from users;
23

#random
sqlite> select random();
-7218996632607330733

##ランダムで１ユーザーを取り出す
sqlite> select * from users order by random() limit 1;
4|Max|27|max@mail|00000

#length

##名前が長い順に並び変える
sqlite> select *  from users order by length(name) desc;
1|Anonymous|23|keioka|082889
6|Jason|33|jason@mail|00000
5|Jack|43|jack@mail|00000
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000


#typeof
sqlite> select typeof(name) from users;

text
text
text
text
text
text
text
text

SQLite Query Language: Core Functions

データの集計

#例
name| score | team
kei|89|A
Tom|59|B
Jason|49|A
Takeshi|67|B
Kob|43|A
Lok|83|B
sdsra|23|A
fasa|13|B

##distinct =>ユニークな値を返す。
sqlite> select distinct team from users;
A
B

##sum(カラム名)
sqlite> select sum(score) from users;
426

##group by (カラム名)　=>　ユニークな値ごとにまとめる
sqlite> select team, sum(score) from users group by team;
A|204
B|222

日付

##current_time
sqlite> select current_time;
23:50:45

##current_timestamp
sqlite> select current_timestamp;
2015-04-13 23:51:02

##strftime
sqlite> select strftime('%Y年%m月%d日%w曜日', current_timestamp);
2015年04月13日1曜日


[https://www.sqlite.org/lang_datefunc.html:title]

レコード更新

update users set name = 'kei' where id = 1;

update テーブル名 set カラム = 値, カラム = 値　（複数可） where カラム= 値;

テーブルのカラムを”値”にセットしてください。カラムが”値”のところ。

sqlite> select * from users;

1|Anonymous|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000

sqlite> update users set name = 'kei' where id = 1;

sqlite> select * from users;

1|kei|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000

レコード削除

##条件を指定して削除
sqlite> select * from users;

1|kei|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000
7|Bob|65|bob@mail|00000
8|Com|65|compm@mail|00000

sqlite> delete from users where age > 60;

sqlite> select * from users;
1|kei|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000

##ROWIDを使った削除
sqlite> select * from users;

1|kei|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
4|Max|27|max@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000

sqlite> delete from users where ROWID = 4;

sqlite> select * from users;

1|kei|23|keioka|082889
2|Tom|32|Tom@mail|00000
3|Son|32|ssss@mail|00000
5|Jack|43|jack@mail|00000
6|Jason|33|jason@mail|00000

複数テーブル

sqlite> select * from games;

1|70
1|50
1|56
1|76
1|96
2|66
2|64
2|34
2|14
2|84
3|94
3|64
3|84
3|87

sqlite> select * from users games;

1|keioka|A
2|Tom|A
3|Mof|B

sqlite> select id, name, team, sum(score) from users, games where users.id = games.user_id group by users.id;

1|keioka|A|348
2|Tom|A|262
3|Mof|B|418


sqlite> select score, name from users,games where users.id = games.user_id;

70|keioka
50|keioka
56|keioka
76|keioka
96|keioka
66|Tom
64|Tom
34|Tom
14|Tom
84|Tom
94|Mof
64|Mof
84|Mof
87|Mof
89|Mof