Sqlite

SQLite3 安装及开发

Install

wget https://sqlite.org/2018/sqlite-autoconf-3220000.tar.gz

tar xzvf sqlite-autoconf-3220000.tar.gz

./configure --prefix=/usr/sqlite3

make

make install

cd /usr/sqlite3/bin/

sqlite3

DBA

• 收缩文件

1. vacuum
2. 在建表语句中，PRAGMA auto_vacuum = 1

• 表结构

1. select * from sqlite_master where type="table" and name="test";

导入数据

.separator ","

.import tw1_bht.txt tw1_bht

分页

Sqlite通过关键字limit限制

select * from tw1_bht limit 10; --显示10行

select * from tw1_bht limit 1 offset 2; --过滤2行，显示1行

select * from tw1_bht limit 1, 2; --过滤1行，显示2行

select * from tw1_bht limit 1000000, 10;

select count(*) from tw1_bht;

select company_id, count(*) from tw1_bht group by company_id;

C++编译

g++ t1.cpp -o t1 -lsqlite3 -L/usr/sqlite3/lib

ERROR

# Error: database disk image is malformed

PRAGMA integrity_check;

Sample

select

id||'|'||

company_id||'|'||

group_id||'|'||

shop_id||'|'||

staff_team||'|'||

house_id||'|'||

trace_type||'|'||

translate(trace_memo, '|'||chr(10)||chr(13), '/ ')||'|'||

to_char(ct, 'yyyymmddhh24miss') ||'|'||

staff_id ||'|'||

is_to_a ||'|'||

(house_id - trunc(house_id/100)*100)

from dw.tw1_buy_house_trace

where 1=1

and house_id - trunc(house_id/100)*100 = 0

create table tw1_bht

(

id number(22),

company_id number(22),

group_id number(22),

shop_id number(22),

staff_team number(22),

house_id number(22),

trace_type number(22),

trace_memo varchar2(4000),

ct number(14),

staff_id number(22),

is_to_a number(22),

part_id number(2)

);