![首頁 Logo Keith ]p !!](/themes/mytheme/logo.png){kind=logo}
最後更新: 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
help
sqlite3 -help
Usage
sqlite3 [options] [databasefile] [CMD]
Import SQL File
cat db.sql | sqlite3 database.db
sqlite3 database.sqlite3 < db.sql
sqlite3 database.sqlite3 ".read db.sql"
Configure
尤如 MySQL 有 ~/.my.cnf,
sqlite3 有 ~/.sqliterc , 不過它只可以有 "dot commands"
.sqliterc
.header on <-- result 會有 header .mode list <-- line, column, insert, list, html
* 用 colume 顯示長 Value 時有機會被裁剪了 !!
Options
比較有用 options 不多, 會用到的如下
-init file 在啟動前執行 file 內的sql 及 dot commands (initialize display settings.)
-cmd command Run command before reading stdin.
-[no]header
-column / -line / -list / -html / -csv
-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
查看 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
storage classes
- NULL
- TEXT # UTF-8
- INTEGER
- REAL # 8-byte IEEE floating point number
- BLOB
data type:
- Boolean # INTEGER: 0 (false) and 1 (true)
- DATE / DATETIME # INTEGER
Remark
Boolean
- SQLite does not have a separate Boolean storage class.
- Boolean values are stored as integers 0 (false) and 1 (true).
- SQLite recognizes the keywords "TRUE" and "FALSE", as of version 3.23.0 (2018-04-02)
DATETIME
- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
- INTEGER as Unix Time
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:
# DROP TABLE 同時會 DROP 了它的 TRIGGER !!
DROP TABLE [IF EXISTS] [schema_name.]table_name;
i.e.
sqlite> drop table tbl1;
載入另一個 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 的
AS
SELECT column_name AS alias_name FROM table_name WHERE [condition];
Output header 時會顯示 alias_name
修改 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
i.e.
sqlite> ALTER TABLE waf_settings ADD COLUMN description TEXT;
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
Format:
column-name data-type constraint
Storage classes:
- NULL
- INTEGER <- 8 bytes
- REAL <- 8 bytes floating point
- TEXT <- UTF-8
- BLOB
constraint:
- PRIMARY KEY
- UNIQUE
- NOT NULL
- AUTOINCREMENT
* SQLite does not have a separate Boolean storage class.
- Boolean values are stored as integers 0 (false) and 1 (true).
* SQLite 本身是沒有 date 及 time 這兩個類型的, 它是用 TEXT 及 INT 保存.
- TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS")
- INTEGER as Unix Time(seconds since 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
);typeof
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
time 的 Output Format:
-
date(timestring, modifier1 modifier2)
YYYY-MM-DD <- 相當於 strftime('%Y-%m-%d', ...)
-
time(timestring, modifier ... )
HH:MM:SS <- 相當於 strftime('%H:%M:%S', ...)
-
datetime(timestring, modifier ...)
YYYY-MM-DD HH:MM:SS <- strftime('%Y-%m-%d %H:%M:%S', ...)
timestring:
- 'now' <-- 當沒有填 timestring 時, Default
- YYYY-MM-DD
- YYYY-MM-DD HH:MM
- YYYY-MM-DD HH:MM:SS
- HH:MM
- HH:MM:SS
- DDDDDDDDDD <-- seconds since 1970-01-01
對於 date(), time() 及 datetime() 來說, timestring 係 input (時間)來
Example
SELECT time();
SELECT time('now');
modifier:
- utc <-- Default
- localtime
- unixepoch <- timestring in the DDDDDDDDDD format
Example:
# 現在時間
SELECT datetime("now", 'localtime');
2012-11-11 22:47:02
SELECT datetime(1601960267, 'unixepoch');
2020-10-06 04:57:47
strftime(format, timestring)
strftime() takes a format string as its first argument.
%d day of month: 00 %f fractional seconds: SS.SSS %H hour: 00-24 %j day of year: 001-366 %J Julian day number %m month: 01-12 %M minute: 00-59 %s seconds since 1970-01-01 %S seconds: 00-59 %w day of week 0-6 with Sunday==0 %W week of year: 00-53 %Y year: 0000-9999 %% %
# unix timestamp
SELECT strftime('%s','now');
1433345253
LIMIT 與 OFFSET
LIMIT <count> OFFSET <skip>
* 在 SQLite no LIMIT for UPDATE
Backup & Restore DB
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
Colume Name `default`
# You should use backtips
select * from table_name where `default` = 1
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.
GUI
