Sqlite

提供:GizmoLabs - だいたい CAD LISP なサイト

sqlite のメモ

本家サイト:https://www.sqlite.org/

  • データベース : データが入ったもの
  • テーブル : ユーザテーブル、レッスンテーブルetc
  • フィールド/カラム:列
  • レコード:行



コマンドライン

・データベースの作成、接続

sqlite3 
sqlite3 dbname.拡張子 で入る(無ければ作られる)
ex.
sqlite3 mysample.sqlite3


・ヘルプの表示:

.help

・SQLiteの終了:

.exit

・データベースの削除:

ファイルを消せばOK

テーブルを作る

  • セミコロンまでが1命令

ex.

create table users (name);
create table users (name, email);

テーブルの構造を見る

.schema users;

テーブル削除

drop tabele users;

テーブル名変更

alter table users rename to dtusers;

カラムの変更

alter table dtuser add colum pwd;


データ型の種類

  • NULL
  • INTEGER
  • REAL
  • TEXT
  • BLOB(BINALY LARGE OBJECT)
  • データ型は指定可能だが必須でない

ex.

create table users (name text, email text);
  • データ型は指定するとそれになるように努力するが確約はされない
 (つまり別の型が入る可能性もある)

テーブルの操作関連


テーブル作成時のオプション例 IDを自動連番に ex.

create table users (id integer primmary key autoincrement);

名前の空を禁止

create table users (id integer primmary key autoincrement, name text not null);

emailをユニークに

create table users (id integer primmary key autoincrement, name text not null, email text unique);

age のデフォルト値を20に ex

create table users (id integer primmary key autoincrement, name text not null, email text unique, age integer default 20);


値チェック

create table lessons (title, count_lessons check(count_lesons>0));

インデックスの作成 ex. ege にインデックスを付ける

create index age on users (age);

データの挿入 ex.

  • 文字はシングルクォーテーションで囲む
insert into users (name, email, age) values ('taguche', 'a@a.com', 20);
  • 文字中にあるシングルクォーテーションは 2個続けて表現する
insert into users (name, email, age) values ('its a pen', 'a@a.com', 20);

データの抽出 ex. usersテーブルを全部見る

select * from users;

scoreの昇順で抽出

select * from users order by score;

scoreの降順で抽出

select * from users order by score desc;

scoreの降順の上位3つ

select * from users order by score desc limit 3;

score が 200以上

select * from users where score >= 200;

taguchi で抽出

select * from users where name = 'taguchi';

taguchi 以外で抽出

select * from users where name <> 'taguchi';

tagで始まる名前で抽出

select * from users where name like 'tag*';


組み込み関数(core function)を使う 件数の取得

select count(*) from users;

score の最大値レコード

select max(score) from users;

score の最小値レコード

select min(score) from users;

ランダム(数値)

select random();

ランダムに一個レコード

select * from users order by random() limit 1;

name を抽出して値の文字数を出す

select name, length(name) from users;

型を取得する

select name, typeof(name) from users;

データを集計する

name score team text

ユニークな値を週出

select distinct team from users;

チーム毎のスコア合計

select team , sum(score) from users group by team;

日付・時刻(date time function)

select current_time;
select current_date;
select current_timestamp;
select insert into users (name, created) value ('taguche', current_timestamp);

時間をフォーマット変えて表示

select strftime('%Y年', current_timestamp);
2012年

データの更新 ex. taguche 君を new taguche に

update users set name = 'new taguche' where name = 'taguche';

ex. new taguche 君を taguche にして、 score を 500 に

update users set name = 'taguche', score = 500 where name = 'new taguche';

データ削除

users 全削除
delete from users;

100 以下のものを削除

delete from users where score <=100;

内部のID付きで表示

select ROWID, * from users;

ROWIDが4のレコードを削除

delete from users where ROWID = 4;

複数テーブルからの抽出 ex.

users テーブル (id integer primary key autoincrement, name, team)
games テーブル(user_id, score)

上のものから合計を出してみる

users テーブルの id name team フィールドを選択スコアを合計する。
game テーブルで game.user_id を users.id = game.user_id で関連付けて id 毎に抽出
select id, name, team, sum(score) from users, games where users.id = game.user_id group by users.id

重複があったらテーブル名を明示する

select users.id 

外部ファイルからのデータ取り込み

.show
.sepalator ,		区切りを , に
.import users.txt users
.header on <<< カラム名を表示する

バックアップ

.dump 				全テーブル
.dump users			users テーブル
.output users.dump	ファイル名を設定。
.show で確認
.read users.dump ダンプを読み込む