gotagota日記

「面白きことは良きことなり」

sqlite3について

SQLite ドットインストール

ドットインストールでsqlite3について学んだので自分用メモ。

データベース用語

データベース: アプリごとに作るデータがはいったもの
↓
テーブル: ユーザーテーブル、 レッスンテーブル
↓
フィールド/カラム: 列
レコード: 行

データベースの作成

sqlite3 [データベース名].sqlite3

データベースの削除

ファイル自体を削除すればおk

sqliteの基本的な使い方

終了の仕方

.exit

helpの出し方

.help

外部ファイルの読み込み

.read [ファイル名]

テーブルの作成

create table [テーブル名] ([カラム名1], [カラム名2]);

作成済みテーブルの確認

.tables

テーブルの構造を見る

.schema [テーブル名]

テーブルの削除

drop table [テーブル名];

テーブル名の変更

alter table [旧テーブル名] rename to [新テーブル名];

カラムの追加

alter table [テーブル名] add column [カラム名];

データ型の指定について

NULL
INTEGER 整数値
REAL 少数の入った数値(浮動小数点型)
TEXT 文字列
BLOB (Binary Large OBject)

create table users (name text, email text);

  • データ型は指定できるが必須ではない。
  • データ型を指定するとそれになるように努力するが、確約はしない。

テーブル作成時のオプション

  • 自動連番の整数値をつける
    id integer primary key autoincrement

  • null値を拒否する
    not null

  • 重複した値を拒否する
    unique

  • デフォルト値を指定する
    default [値]

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

  • チェック機能を含める
    create table lessons (title, count_lessons check(count_lessons>0));
    -> count_lessonsが1以上であることをチェックする

  • インデックスを付ける
    create index [インデックス名] on [テーブル名] (カラム名);

テーブルにデータを挿入する

insert into [テーブル名] ([カラム名]) values ([値])

例
insert into users (name, email, age) values ('example', 'example@example. com', 20)

  • シングルクォーテーションのエスケープ
    insert into users (name, email, age) values ('it''s a pen', 'example@example.com', 20) -> '' と連続で打つ

  • null値で指定
    insert into users (name, email, age) values ('it''s a pen', 'example@example.com', null) -> null とそのまま打つ

データを更新する

update [テーブル名] set [カラム名] = [新データ] where [カラム名] = [旧データ];

データを削除する

delete from [テーブル名] where [場所の指定];

ROWIDについて

内部的に行番号を振ってくれているので便利だよ
-例
-- select ROWID, * from users;

組み込み関数

全件取得

select * from [デーブル名];

件数の取得

select count(*) from [テーブル名];

カラムの最大値の取得

select max([カラム名]) from [テーブル名];

カラムの最小値の取得

select min([カラム名]]) from [テーブル名];

乱数の表示

select random();

  • 例
    -- select * form [テーブル名] order by random() limit1;
    --> ランダムに1つのデータを選び出すときに使える

文字列の長さを表示する

select length([カラム名]) from [テーブル名];

型を明示的に表示する

select typeof(カラム名) from [テーブル名];

ユニークな値を取得する

select distinct [カラム名] from [テーブル名];

値を集計する

select sum([カラム名1]) from [テーブル名] group by [カラム名2];

現在時刻を取得する

select current_time;

現在日付を取得する

select current_date;

現在日付時刻を取得する

select current_timestamp;

-例
-- insert into users (name, created) values ('example', current_timestamp) ; --> 作成時刻を追加できる

日付に関する出力フォーマットを変更する

--例) 日本語で年を出力する
select strftime('%Y年', current_timestamp);
詳しくはリファレンスをば。