備忘録

DBの操作

[作成]

# sqlite3 mydata.db
sqlite> create table memos(text, priority INTEGER);
sqlite> insert into memos values ('deliver project description', 10);
sqlite> insert into memos values ('lunch with Christine', 100);
sqlite>
sqlite> select * from memos;
deliver project description|10
lunch with Christine|100
sqlite> .exit

[テーブル表示]

$ sqlite3 mydata.db 
sqlite> .tables 
memos
sqlite> .schema memos

[テーブル削除]

$ sqlite3 mydata.db 
sqlite> .tables 
memos
sqlite> drop tables memos;
sqlite> .tables

[テーブル削除]

$ ls -l mydata.db
$ rm mydata.db


環境設定

[セパレータ変更]

デフォルトのセパレータが"|(パイプ)"なので",(カンマ)"に変更

sqlite> .separator ","
sqlite> select * from memos;
deliver project description,10
lunch with Christine,100

[整形]

sqlite> .mode column
sqlite> select * from memos;
deliver project description  10
lunch with Christine         100
|>
**[ヘッダー出力あり]
>|
sqlite> .header on
sqlite> select * from memos;
text                         priority
---------------------------  ----------
deliver project description  10
lunch with Christine         100

[列幅を変更]

任意の列幅に変更
sqlite> .width 10 5
sqlite> select * from memos;
text        prior
----------  -----
deliver pr  10
lunch with  100

[インポート]

$ vi import.db
create table posts (
    id integer primary key,
    title text,
    body text
);

$ sqlite3 dialy.db
sqlite> .read import.db
sqlite> .tables


データ型

SQLiteでは型を指定しなくてもテーブル作成可能。型指定も厳密性は無い。

  • NULL. The value is a NULL value.
  • TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
  • INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
  • REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
  • BLOB. The value is a blob of data, stored exactly as it was input.


参考

http://www.sqlite.org/docs.html