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
- 收缩文件
- vacuum
- 在建表语句中，PRAGMA auto_vacuum = 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)
);