【数据库】sqlite3常用命令及SQL语句
目录
1、直接拷贝数据库2、使用.backup .clone1）交互式2）脚本3、导出到csv文件中（其它格式类似）1）交互式2）脚本3）导出成其它格式汇总a> .mode asciib> .mode columnc> .mode htmld> .mode inserte> .mode linef> .mode listg> .mode tabsh> .mode tcl4、使用SQL语句：VACUUM INTO1）交互式2）脚本3）在SQLite提供的C接口中使用SQL语句：vacuum into5、使用SQLite C接口sqlite3_backup*1、直接拷贝数据库
这是最直接、最简单的方法：在linux下用cp直接拷贝数据库，win下直接复制粘贴。
2、使用.backup .clone
1）交互式
在终端上执行[ sqlite3 数据库文件名]，进入sqlite环境，然后可以使用.backup .clone备份或克隆数据库。（注：如果新数据库已经存在，克隆时会报错，而备份不会）
$ sqlite3sqlite> .help.backup ?DB? FILE Backup DB (default "main") to FILE....clone NEWDB Clone data into NEWDB from the existing database...
2）脚本
可以使用shell脚本执行，这样就能在程序中调用脚本，自动执行。
$ cat sqlte3Backup.sh #!/bin/basholdDB=$1newDB=$2echo ".backup $newDB" | sqlite3 $oldDB
3、导出到csv文件中（其它格式类似）
可以使用select筛选出指定信息，保存到csv中
1）交互式
$ sqlite3 数据库文件名sqlite> .headers on// 显示列表头，否则在csv中没有表头sqlite> .mode csv// 选择csv（逗号分隔值类型）sqlite> .once test.csv// 将下面的SQL语句输出保存到文件中，只保存一次，// 第二个SQL会恢复输出到终端。sqlite> SELECT * FROM 表;sqlite> .exit;
2）脚本
$ cat sqlte3ToCSV.sh #!/bin/bashfileName=$1dbName=$2echo ".headers on.mode csv.once $fileNameselect * from test;.exit" | sqlite3 $dbName
3）导出成其它格式汇总
a> .mode ascii
id^_name^_age^^1^_XiaoHong^_18^^2^_XiaoMing^_19^^
b> .mode column
idname age ---------- ---------- ----------1 XiaoHong 18 2 XiaoMing 19
c> .mode html
<TR><TH>id</TH><TH>name</TH><TH>age</TH></TR><TR><TD>1</TD><TD>XiaoHong</TD><TD>18</TD></TR><TR><TD>2</TD><TD>XiaoMing</TD><TD>19</TD></TR>
d> .mode insert
INSERT INTO "table"(id,name,age) VALUES(1,'XiaoHong',18);INSERT INTO "table"(id,name,age) VALUES(2,'XiaoMing',19);
e> .mode line
id = 1name = XiaoHongage = 18id = 2name = XiaoMingage = 19
f> .mode list
id|name|age1|XiaoHong|182|XiaoMing|19
g> .mode tabs
idnameage1XiaoHong182XiaoMing19
h> .mode tcl
"id" "name" "age""1" "XiaoHong" "18""2" "XiaoMing" "19"
4、使用SQL语句：VACUUM INTO
注意：这种方法对SQLite版本有要求，SQLite 版本至少3.27.0 (-02-07)
1）交互式
$ sqlite3 数据库文件名sqlite> VACUUM INTO 新数据库文件名 // VACUUM 后面省略了数据库名，默认是main， 使用.database查看sqlite> .exit;
2）脚本
$ cat sqlte3Vacuum.sh#!/bin/basholdDB=$1newDB=$2echo "vacuum into '$newDB'" | sqlite3 $oldDB
3）在SQLite提供的C接口中使用SQL语句：vacuum into
注意：如果新数据库存在，使用VACUUM INTO会报错
error: output file already exists!
测试程序如下：
int main(){sqlite3 *pDB;int rc = sqlite3_open("test.db", &pDB);if( rc==SQLITE_OK ){printf("open test.db OK!\n");char *sqlVacuum = "VACUUM INTO 'new.db';";char *errMsg = 0;rc = sqlite3_exec(pDB, sqlVacuum, NULL, 0, &errMsg);if( rc!=SQLITE_OK ){printf("VACUUM INTO new.db error: %s!\n", errMsg);sqlite3_free(errMsg);} else {printf("VACUUM INTO new.db OK!\n");}} else {printf("open test.db error!\n");}sqlite3_close(pDB);return 0;}
5、使用SQLite C接口sqlite3_backup*
涉及的接口如下：
sqlite3_backup_init()sqlite3_backup_step()sqlite3_backup_remaining()sqlite3_backup_pagecount()sqlite3_backup_finish()
测试程序如下：
#include <stdio.h>#include <sqlite3.h>void progress(int left, int total){printf("total = %d; left = %d\n", total, left);}int backupDb(sqlite3 *pDb, const char *zFilename, void(*xProgress)(int, int) ){int rc;sqlite3 *pFile;sqlite3_backup *pBackup;rc = sqlite3_open(zFilename, &pFile);if( rc==SQLITE_OK ){pBackup = sqlite3_backup_init(pFile, "main", pDb, "main");if( pBackup ){do {rc = sqlite3_backup_step(pBackup, 5);xProgress(sqlite3_backup_remaining(pBackup),sqlite3_backup_pagecount(pBackup));//if( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED ){// sqlite3_sleep(250);//}} while( rc==SQLITE_OK || rc==SQLITE_BUSY || rc==SQLITE_LOCKED );(void)sqlite3_backup_finish(pBackup);}rc = sqlite3_errcode(pFile);}(void)sqlite3_close(pFile);return rc;}int main(){sqlite3 *pDB;int rc = sqlite3_open("test.db", &pDB);if( rc==SQLITE_OK ){printf("open test.db OK!\n");rc = backupDb(pDB, "new.db", progress);if( rc==SQLITE_OK ){printf("backupDb new.db OK!\n");} else {printf("backupDb new.db error!\n");}} else {printf("open test.db error!\n");}sqlite3_close(pDB);return 0;}
