c-bata web

@c_bata_ のメモ。python多め

SQLite3勉強内容まとめ

学校の授業で関係データベースやSQLについては勉強したことがあるのですが,今まで実際にデータベースを扱ったことがなかったので,かなり遅い気がしますがそろそろ勉強始めます。

利用するデータベース

まずは,どのデータベースを利用していくかということですが,個人的に候補は2つ

色々調べてみたところ,↓の記事を読んでSQLiteを利用することに決定!

MysqlとSqliteを比較した。

MacBook Airでsqlite3をいじってみたMySQLユーザーの感想まとめ


SQLiteMacなら標準でインストールされているとのこと。 そのため,今回は開発環境の構築方法はなしです。

参考資料

基本的な用語

  • テーブル : excelでいう表。

  • フィールド : excelでいう列。「別名:列(カラム),属性(アトリビュート)」

  • レコード : excelでいう行。「別名:行(ロー),組(タプル)」

※なお,属性(アトリビュート)の取り得る範囲のことを定義域(ドメイン)ともいう。

データベースを作成してみる

今回はsqlite_lessonsというフォルダを作成して,その中で作業。

$ sqlite3 データベース名.sqlite3

sqlite3では,データベースというものは実際にはただのファイル。 ↑のコマンドを入力すると,すでにそのデータベースが存在する場合にはそれを開き,ない場合には新規作成します。

拡張子はなくてもいいが,分かりやすいように.sqlite3にするのが通例らしい。

データベースの操作方法

使えるコマンドの一覧を表示(help)

> .help

終了

> .exit

テーブルの作成

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

※最後の「;」忘れずに!!ちなみに「.」で始まるコマンドはセミコロン不要

テーブルの一覧を表示

> .tables

テーブルの構造を見る(スキーマ)

> .schema テーブル名

テーブルの削除

> drop table テーブル名;

テーブル名の変更

> alter table 古いテーブル名 rename to 新テーブル名

カラムの追加

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

データベースの削除

ファイルを削除するだけ。 バックアップを撮りたいなら,どっかにファイルをコピーしとくだけ。手軽!!

使用例(ドットインストールを見ながら操作してみました)

sqlite> create table users (name, email);
sqlite> .tables
users
sqlite> .schema users
CREATE TABLE users(name, email);
sqlite> create table lessons
   ...> (title);
sqlite> .title
Error: unknown command or invalid arguments:  "title". Enter ".help" for help
sqlite> .tables
lessons  users
sqlite> drop table lessons
sqlite> .tables
users
sqlite> alter table users rename to dotinstall_users;
sqlite> .tables
dotinstall_users
sqlite> alter table dotinstall_users add column pwd;
sqlite> .schema
CREATE TABLE "dotinstall_users"(name, email, pwd);

SQLiteにおけるデータ型の指定

NULL INTEGER :整数値 REAL :実数値 TEXT :文字列 BLOB :バイナリデータ(画像とか)を格納できる(Binary Large OBjectの略)

SQLiteではデータ型は指定できるけど,必須ではない! またデータ型を指定するとそれになるように努力はするけど,確約はしない!とのこと

例えば

create table users(name, email);

は,↓のようにデータ型を宣言することが出来る

create table users(name text, email text);

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

自動連番の設定(idとかに使える)

カラム名 integer primary key autoincrement

※primary keyは主キーの指定

null値の禁止(空になったらエラーになってくれる)

カラム名 not null

ユニークキーの設定(重複の禁止)

カラム名 unique

デフォルト値の設定

カラム名 default デフォルト値

値チェック

checkというオプションで指定できる。例えば正の値のみを格納したい場合は↓。負の値が来るとエラーで弾いてくれる。

カラム名 check(カラム名>0)

↑に示したオプションの使用例。

sqlite> create table users (id integer primary key autoincrement, name text not null,email text unique, age integer default 20);
sqlite> create table lessons (title, count_lessons check(count_lessons>0));

インデックス(索引)の作成

これをつけておくとそのカラムでの検索がかなり早くなるとのこと。 ちなみにprimary keyとかuniqとかのオプションもそれだけでインデックスの役割も果たしている。

↑の例に続いて
> create index age on users (age);

ユニークなインデックスを追加したい場合にはcreate unique indexとすればいいらしい。

データのバックアップ・復元

データベース単位でのバックアップは,ファイルをそのままバックアップすればいいんですが,テーブルやデータ単位でバックアップするにはdumpコマンドを使用する。またdumpコマンドによって出力したファイルをreadコマンドでインポートすることも出来るという便利なもの。

必要となった時にこちらを見れば簡単に扱える。

データの挿入

> insert into テーブル名 (カラム名) vallues (データ);

↓の例のように,nullを指定することもできる。

sqlite> insert into users (name, email, age) values ('taguchi', 'taguchi@dotinstall.com', null);

データの抽出

sqlite> select 見たいカラム名(*で全部) from テーブル名

select文のオプション

select分のオプションは↓の例を見たほうが早いです。 下の例には,「並べ替え(昇順,降順)」,「上位~件だけ表示」,「データの範囲を指定」,「データが〜だったら表示しない(する)」などが有ります。

データの抽出と挿入の具体例

sqlite> create table shop (name, value);
sqlite> .schema
CREATE TABLE shop (name, value);
sqlite> insert into shop (name, value) values ('apple', '100');
sqlite> insert into shop (name, value) values ('orange', '120');
sqlite> insert into shop (name, value) values ('banana', '150');
sqlite> select * from shop;
apple|100
orange|120
banana|150
sqlite> select value from shop;
100
120
150
sqlite> select * from shop order by value;
apple|100
orange|120
banana|150
sqlite> select * from shop order by value desc;
banana|150
orange|120
apple|100
sqlite> select * from shop order by value desc limit 2;
banana|150
orange|120
sqlite> select * from shop where value >= 110;
apple|100
orange|120
banana|150
sqlite> select * from shop where name <> 'orange';
apple|100
banana|150
sqlite> select * from shop where name = 'orange';
orange|120
sqlite> select * from shop where name like 'ora%';
orange|120

ROWID

SQLite3ではROWIDというIDが連番で割り当てられている。

sqlite> select ROWID, * from shop;
1|apple|100
2|orange|120
3|banana|150

組み込み関数

組み込み関数はたくさんあり(http://www.sqlite.org/lang_corefunc.html),select文やorder byと組み合わせると様々なことが出来る。 ここでは↓に示す組み込み関数の使用例を使ってみる。

  • count
  • max
  • random
  • length
  • typeof

使用例は↓。

sqlite> select count(*) from shop;
3
sqlite> select max(value) from shop;
150
sqlite> select random();
2407329132967656194
sqlite> select * from shop order by random () limit 1;
orange|120
sqlite> select * from shop order by random () limit 1;
apple|100
sqlite> select name, length (name) from shop;
apple|5
orange|6
banana|6
sqlite> select name, typeof(name) from shop;
apple|text
orange|text
banana|text

データの集計

これについてはまとめるのが難しかった。こちらが分かりやすいので,必要となった時に確認すれば問題無さそう。一応↓にまとめてみる。

  • distinct

ユニークな値を抽出してくれる。

> select distinct カラム名 from テーブル名;
  • group by

集計の関数。

> select 表示するカラム名 from テーブル名 group by 集計するカラム名;

日付・時刻

↓を見れば分かる。詳しく知りたいならドキュメント(http://www.sqlite.org/lang_datefunc.html)を見れば分かる。

sqlite> select current_time;
11:53:26
sqlite> select current_date;
2013-10-26
sqlite> select current_timestamp;
2013-10-26 11:53:40
sqlite> select strftime('%Y年', current_timestamp);

日本語が入力できない問題について

Mac OS Xのターミナルからsqliteに接続し,日本語の情報をテーブルに登録しようとすると,日本語が入力できなかった。 調べてみるとこちらに同じ症状の方がいた。どうやら対応策としては、CSVファイルにして.importコマンドで取り込むと行けるらしいです。

外部のテキストファイルからデータを取り込む

必要となった時にこちらを見るのが一番早い。

データの更新

> update テーブル名 set カラム名 = '新しい名前' where name = '古い名前'

データの削除

全てのデータを削除するには

> delete from テーブル名

↓の例のように条件を指定する事もできる。

> delete from users where score <=100;

複数のテーブルを扱う

これについても記事にはまとめづらいので,必要となった時にこちらを見たほうが早い。

> select 表示するカラム名 from テーブル名1, テーブル名2 where 条件 group by カラム名;

重複のあるカラム名テーブル名.カラム名で指定する必要がある。