sqlite

最後更新: 2018-06-22

介紹

sqlite 是一個超輕量級的資料庫來, 它沒有網絡及用戶管理功能

亦即是一切以檔案操作來, 所以它沒有 GRANT, REVOKE, CREATE USER 指令

 


安裝

apt-get install sqlite3

# 在 debian squeeze 上的 sqlite default 是 version 2 來

# 不同版本的 DB 要用不同 version 的 tool 打開

查看版本

軟件: #sqlite -version

3.7.3

DB: #file production.sqlite3

fail2ban.sqlite3: SQLite 3.x database

應用

sqlite3 [options] [databasefile] [SQL]

尤如 MySQL 有 ~/.my.cnf,

sqlite3 有  ~/.sqliterc , 不過它只可以有 "dot commands"

 

.sqliterc

.header on               <-- result 會有 header
.mode column             <-- line, column, insert, list, html

 

Options

比較有用 options 不多, 會用到的如下

-init file 在啟動前執行 file 內的sql 及 dot commands (initialize display settings.)

-[no]header

-separator <separator> (default: |)

 


SQLITE META-COMMANDS

 

它是 sqlite shell 內自身的功能, 與 SQL  Statement 無關.

.databases                        # List names and files of attached databases
.tables ?PATTERN?             # List names of tables matching a LIKE pattern
.schema ?TABLE?               # Show the CREATE statements

.output FILE.sql / stdout     # Send output to FILENAME
.read FILE.sql                    # Execute SQL in FILENAME

.width NUM NUM ...            Set column widths for "column" mode
.separator STRING
.header(s) ON|OFF

.dump [?TABLE?] ...       Dump the database in an SQL text format
.import FILE TABLE       Import data from FILE into TABLE
.restore ?DB? FILE
.backup ?DB? FILE         Backup DB (default "main") to FILE (sqlite format)

.quit                              Exit this program
.help

查看 Output 格式: .show

sqlite> .show
     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    width:

.stats on

Memory Used:                         60128 (max 63840) bytes
Number of Allocations:               128 (max 131)
Number of Pcache Overflow Bytes:     3344 (max 3344) bytes
Number of Scratch Overflow Bytes:    0 (max 0) bytes
Largest Allocation:                  48000 bytes
Largest Pcache Allocation:           1160 bytes
Largest Scratch Allocation:          0 bytes
Lookaside Slots Used:                37 (max 56)
Pager Heap Usage:                    3808 bytes
Schema Heap Usage:                   2360 bytes
Statement Heap/Lookaside Usage:      6112 bytes
Fullscan Steps:                      0
Sort Operations:                     0
Autoindex Inserts:                   0

被打開的 Database 的 name 會叫 main: .database

sqlite> .database
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /root/test/mydb
1    temp             /var/tmp/sqlite_zuEhEt09VIkG4bm

設定 Output 形式: .mode

格式有: column(.width), list(.separator), tabs, csv, html

list:

first|second
hello|10
test|11

col:

.mode column

first       second
----------  ----------
hello       10
test        11

 


DB 的特牲

 

select * from main.sqlite_master;

Output 的資料:

type|name|tbl_name|rootpage|sql

type:

  • table
  • index
  • trigger

Example

table|fail2banDb|fail2banDb|2|CREATE TABLE fail2banDb(version INTEGER)
table|jails|jails|3|CREATE TABLE jails(name TEXT NOT NULL UNIQUE, enabled INTEGER NOT NULL DEFAULT 1)
index|sqlite_autoindex_jails_1|jails|4|
index|jails_name|jails|5|CREATE INDEX jails_name ON jails(name)
table|logs|logs|6|CREATE TABLE logs ...
index|sqlite_autoindex_logs_1|logs|7|
index|sqlite_autoindex_logs_2|logs|8|
index|logs_path|logs|9|CREATE INDEX logs_path ON logs(path)
index|logs_jail_path|logs|10|CREATE INDEX logs_jail_path ON logs(jail, path)
table|bans|bans|11|CREATE TABLE bans ...
index|bans_jail_timeofban_ip|bans|13|CREATE INDEX bans_jail_timeofban_ip ON bans(jail, timeofban)
index|bans_jail_ip|bans|15|CREATE INDEX bans_jail_ip ON bans(jail, ip)
index|bans_ip|bans|16|CREATE INDEX bans_ip ON bans(ip)

 


一般 SQL Statement

 

詳見: http://www.sqlite.org/lang.html

打開 DB:

ATTACH <db>

       OR

sqlite3 mydata.db

 *當 mydata.db 不存在時, sqlite 會自動建立它

注解:

sqlite> -- i am a sql style comment

sqlite> /* C style comment */

建立 table:

sqlite> create table tbl1(first varchar(10), second smallint);

insert:

sqlite> insert into tbl1 values('admin',0000);                                        // 必須填上所有 Colume

insert into user (username, password) values ( "test1", "1234" );

P.S.

sqlite 是不能想 Mysql 似的一次 insert 多項 Record

select:

sqlite> select * from tbl1;

update:

sqlite> update tbl1 set second = '30' where first = 'world!';

update multiple columns

UPDATE table SET
  column1 = value1,
  column2 = value2
WHERE condition

delete:

delete from tbl1 where second = "10";

drop:

sqlite> drop tbl1;

# DROP TABLE 同時會 drop 它的 TRIGGER, 所以要

 

載入另一個 DB:

sqlite> attach database mydb as salve;

sqlite> .database

seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             /root/test/mydb2
1    temp             /var/tmp/sqlite_gvEQroM06d7JSRD
2    another        /root/test/mydb

detach:

sqlite> detach database another ;

P.S.

main 是不能 detach 的

 


修改 table schema

 

查看:

sqlite> .schema tbl1

現在:

create table tbl1(first varchar(10), second varchar(10));

version: 3.1.3:

ALTER TABLE db.tbl RENAME TO new_name

ALTER TABLE db.tbl ADD COLUMN {column-def}

column-def: col_name type

P.S.

  • 不可以為 column rename 及 remove
  • new column 永遠在尾, 而且不能是 PRIMARY KEY or UNIQUE
  • 份改 table 的時間與 資料數量無關

在 sqlite 2 修改 table 是很煩的 .....

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;

# 重組 DB

sqlite> vacuum;

 


Table 的設定:

 

  • primary key
  • autoincrement
  • unique
  • not null
  • default (...)   |  default 1
create table user(
        uid integer primary key,
        gid integer default 2,
        username text unique,
        password text not null,
        last_modify text default (datetime('now','localtime')),
        enable boolean default 1,
        remark text default null
);

default:

  • null
  • current_time
  • current_data
  • current_timestamp   <--- default 是 utc 時間, 不方便的話, 可以用 (datetime('now','localtime'))

autoincrement

On an INSERT, if the INTEGER PRIMARY KEY column is not explicitly given a value, then it will be filled automatically with an unused integer

AUTOINCREMENT 的作用 => automatic ROWID assignment algorithm to prevent the reuse of ROWIDs over the lifetime of the database.

 


TRIGGER

CREATE TRIGGER insert_create_time after insert on mytable
begin
update mytable set create_time = datetime('now')
where rowid = new.rowid;
end;

before, after

INSERT     NEW references are valid
UPDATE     NEW and OLD references are valid
DELETE     OLD references are valid

Trigger 是放在 table 的 schema 上的 !!

.schema user

CREATE TRIGGER update_mtime after update on user
begin
update user set last_modify = datetime('now')
where rowid = new.rowid;
end;

 


TRANSACTION

 

BEGIN (BEGIN TRANSACTION)

lock 的情度

  • deferred, (default)
  • immediate,
  • exclusive

COMMIT (END TRANSACTION)

ROLLBACK (ROLLBACK TRANSACTION)

Example:

begin;
sql statemant 1;
sql statemant 2;
commit;

注意:

  • transaction 內不可以再有 transaction
  • sqlite 的 rollback 是有可能會失敗的 !

 


Aggregate Functions

 

count(*)
max()
min()
total() is a floating point
sum() is an integer

 


Datatypes

 

column-name type constraint

storage classes:

  • NULL
  • INTEGER <- 8 bytes
  • REAL       <- 8-byte
  • TEXT       <- UTF-8
  • BLOB

constraint:

  • PRIMARY KEY
  • UNIQUE
  • NOT NULL
  • AUTOINCREMENT

* SQLite does not have a separate Boolean storage class.

  • ( 0: false, 1: true )

* SQLite 本身是沒有 data 及 time 這兩個類型的, 所以佢地會用 TEXT 及 INT 保存.

  • TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
  • INTEGER as Unix Time(1970-01-01 00:00:00 UTC)

Example:

CREATE TABLE t1(
    t  TEXT,     -- text affinity by rule 2
    nu NUMERIC,  -- numeric affinity by rule 5
    i  INTEGER,  -- integer affinity by rule 1
    r  REAL,     -- real affinity by rule 4
    no BLOB      -- no affinity by rule 3
);

 

SELECT typeof(t), typeof(nu), typeof(i), typeof(r), typeof(no) FROM t1;

每 table 都有一個 64bit 的 rowid (integer 來, 由 1 開始)

If a table contains a column of type INTEGER PRIMARY KEY,

then that column becomes an alias for the ROWID

 


時間 function

 

用法:

date(timestring, modifier, modifier, ...)

縮寫:

date()                        YYYY-MM-DD                        <-- 相當於 strftime('%Y-%m-%d', ...)
time()                        HH:MM:SS                           <-- 相當於 strftime('%H:%M:%S', ...)
datetime()                  YYYY-MM-DD HH:MM:SS

strftime(format, timestring)

timestring:

  • YYYY-MM-DD                 <-- string 來 !!
  • HH:MM:SS
  • YYYY-MM-DD HH:MM
  • now                              <-- Default
  • %s                               <-- seconds since 1970-01-01

對於 date(), time() 及 datetime() 來說, timestring 係 input (指定時間)來

modifier:

  • utc                               <-- Default
  • unixepoch
  • localtime

Example:

# 現在時間

SELECT datetime("now", 'localtime');

2012-11-11 22:47:02

# unix timestamp

SELECT strftime('%s','now');

1433345253

 


LIMIT 與 OFFSET

 

LIMIT <count> OFFSET <skip>

 


Backup

 

backup

$ echo '.dump' | sqlite3 dbname | gzip -c > dbname.dump.gz

restore

$ zcat ex1.dump.gz | sqlite3 dbname

 


DB file 結構

 

select * from main.sqlite_master;

Output 的資料:

type|name|tbl_name|rootpage|sql

rootpage

Each table and each index in SQLite is stored in a separate b-tree in the database file.

Each b-tree is identified by its root page number.

Rootpage is the page number of the root b-tree page.

For rows that define views, triggers, and virtual tables, the rootpage column is zero or NULL.

A table b-tree contains a 64-bit integer key and arbitrary data.

The 64-bit integer key is the ROWID. Index b-trees contain an arbitrary binary key and no data.

File Format

https://www.sqlite.org/fileformat2.html
 

 


Cleanup

 

sqlite truncate table

在 SQLite 中 沒有 TRUNCATE TABLE,

所以只能用 DELETE / DROP TABLE 去完成 TRUNCATE TABLE.

但建議使用 DROP TABLE 命令刪除整個表, 然後再重新創建一遍.

因為 DELETE 好花時間.

 * 小心有 index 及 ref

sqlite> DELETE FROM table_name;
 

VACUUM

rebuilds the database file, repacking it into a minimal amount of disk space.

First, when you drop database objects such as tables, views, indexes,
and triggers or delete data from tables, the database file size remains unchanged.

Because SQLite just marks the deleted objects as free and reserves it for the future uses.