【数据库】sqlite3数据库备份、导出方法汇总
一、准备工作
0、安装SQLite3
1）ubuntu命令安装（不是最新版本）
sudo apt install sqlite3
2）源码安装（可以安装最新版本）
下载：
/download.html
sqlite-autoconf-3300100.tar.gz(2.72 MiB)
编译：
./configuremakesudo make install
默认安装在/usr/local/bin下
1、创建数据库
sqlite3 <数据库文件名>
$ ls$ sqlite3 test.dbSQLite version 3.11.0 -02-15 17:29:24Enter ".help" for usage hints.sqlite>
2、创建表格
CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(50) NOT NULL,age INT);
注意：
1> 在MySQL中自增关键词是AUTO_INCREMENT，在sqlite3中使用无效2> 自增类型必须是 INTEGER，不能是 INT，否则报错：Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY3> 主键自增定义时注意关键词的顺序：INTEGER PRIMARY KEY AUTOINCREMENT，要按照这个顺序。
3、插入数据
INSERT INTO test (name, age) VALUES ('XiaoHong', 18);INSERT INTO test (name, age) VALUES ('XiaoMing', 19);
4、查询
SELECT * FROM test;1|XiaoHong|182|XiaoMing|19
二、sqlite3命令总结
1、命令列表
sqlite> .help.backup ?DB? FILEBackup DB (default "main") to FILE.bail on|off Stop after hitting an error. Default OFF.binary on|off Turn binary output on or off. Default OFF.changes on|off Show number of rows changed by SQL.clone NEWDB Clone data into NEWDB from the existing database.databases List names and files of attached databases.dbinfo ?DB? Show status information about the database.dump ?TABLE? ...Dump the database in an SQL text formatIf TABLE specified, only dump tables matchingLIKE pattern TABLE..echo on|off Turn command echo on or off.eqp on|off Enable or disable automatic EXPLAIN QUERY PLAN.exit Exit this program.explain ?on|off|auto? Turn EXPLAIN output mode on or off or to automatic.fullschema Show schema and the content of sqlite_stat tables.headers on|off Turn display of headers on or off.help Show this message.import FILE TABLEImport data from FILE into TABLE.indexes ?TABLE? Show names of all indexesIf TABLE specified, only show indexes for tablesmatching LIKE pattern TABLE..limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT.load FILE ?ENTRY?Load an extension library.log FILE|offTurn logging on or off. FILE can be stderr/stdout.mode MODE ?TABLE?Set output mode where MODE is one of:ascii Columns/rows delimited by 0x1F and 0x1EcsvComma-separated valuescolumn Left-aligned columns. (See .width)htmlHTML <table> codeinsert SQL insert statements for TABLElineOne value per linelistValues delimited by .separator stringstabsTab-separated valuestclTCL list elements.nullvalue STRINGUse STRING in place of NULL values.once FILENAME Output for the next SQL command only to FILENAME.open ?FILENAME? Close existing database and reopen FILENAME.output ?FILENAME?Send output to FILENAME or stdout.print STRING... Print literal STRING.prompt MAIN CONTINUE Replace the standard prompts.quit Exit this program.read FILENAME Execute SQL in FILENAME.restore ?DB? FILERestore content of DB (default "main") from FILE.save FILE Write in-memory database into FILE.scanstats on|offTurn sqlite3_stmt_scanstatus() metrics on or off.schema ?TABLE? Show the CREATE statementsIf TABLE specified, only show tables matchingLIKE pattern TABLE..separator COL ?ROW? Change the column separator and optionally the rowseparator for both the output mode and .import.shell CMD ARGS...Run CMD ARGS... in a system shell.show Show the current values for various settings.stats on|offTurn stats on or off.system CMD ARGS... Run CMD ARGS... in a system shell.tables ?TABLE? List names of tablesIf TABLE specified, only list tables matchingLIKE pattern TABLE..timeout MS Try opening locked tables for MS milliseconds.timer on|offTurn SQL timer on or off.trace FILE|off Output each SQL statement as it is run.vfsinfo ?AUX? Information about the top-level VFS.vfslistList all available VFSes.vfsname ?AUX? Print the name of the VFS stack.width NUM1 NUM2 ... Set column widths for "column" modeNegative values right-justifysqlite>
2、备份数据库
$ ls// 查看文件列表，只有test.dbtest.db$ sqlite3 test.db// 进入数据库sqlite> .databases// 查看数据库seq name file --- --------------- ------------------------0 main /home/laoer/Templates/test.db sqlite> .backup main test-backup.db// 备份数据库，main可以省略sqlite> .exit// 退出$ ls// 查看备份数据，确认备份成功test-backup.db test.db$ sqlite3 test-backup.db sqlite> select * from test;1|XiaoHong|182|XiaoMing|19
3、导出数据到csv格式文件中
sqlite> .headers on// 显示列表头，否则在csv中没有表头sqlite> .mode csv// 选择csv（逗号分隔值类型）sqlite> .once test.csv// 将下面的SQL语句输出保存到文件中，只保存一次，// 第二个SQL会恢复输出到终端。sqlite> SELECT * FROM test;sqlite> .exit;$ cat test.csv // 查看csv文件，导出成功id,name,age1,XiaoHong,182,XiaoMing,19
.once 只将下面的一个SQL语句输出保存到文件中；如果使用.output命令则会把下面所有SQL输出保存到文件中。
4、查看数据信息
sqlite> .databases// 列出数据库名称和文件seq name file --- --------------- ----------------------------------------------------------0 main /home/laoer/Templates/test.db sqlite> .tables// 列出数据表testsqlite> .schema// 打印数据表创建语句CREATE TABLE test (id INTEGER PRIMARY KEY AUTOINCREMENT,name VARCHAR(50) NOT NULL,age INT);sqlite> .show// 显示当前配置参数值echo: off// 回显eqp: off// 解释查询计划EXPLAIN QUERY PLAN，只用在交互式分析和排除故障explain: auto// 解释模式，只用在交互式分析和排除故障headers: off// 表格头mode: list// 输出模式nullvalue: ""output: stdout// 输出位置colseparator: "|"rowseparator: "\n"stats: offwidth: sqlite> .dbinfo// 显示数据库的状态信息database page size: 1024write format: 1read format: 1reserved bytes:0file change counter: 3database page count: 3freelist page count: 0schema cookie: 1schema format: 4default cache size: 0autovacuum top root: 0incremental vacuum: 0text encoding: 1 (utf8)user version: 0application id:0software version: 3011000number of tables: 2number of indexes: 0number of triggers: 0number of views:0schema size: 132sqlite> .limit// 显示或设置数据库限制信息：SQLITE_LIMITlength 1000000000// 字符串或BLOB的最大长度10亿，一行的最大长度sql_length 1000000000// sql语句最大长度column 2000// 列数，可以在编译时才可以将最大列出改为32767expr_depth 1000// 表达式树的最大深度，SQLite将表达式解析到树中进行处理。compound_select 500// 复合SELECT语句中的最大术语数vdbe_op 25000// 虚拟机程序中用于实现SQL语句的最大指令数function_arg 127// 一个函数的最大参数个数attached 10// ATTACH语句，附加数据库最大值为125like_pattern_length 50000// LIKE模式匹配算法或GLOB模式的最大长度variable_number 250000// 任何参数的索引号trigger_depth 1000// 触发递归的最大深度worker_threads 0// 可以启动的辅助工作线程的最大数量
常用查询SQL语句
1、查询头几条数据
SELECT * FROM table ORDER BY id LIMIT 10;
2、查询最后几条数据
SELECT * FROM table ORDER BY id DESC LIMIT 10;
DESC：降序排列
3、查询数据库中所有的数据表table
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
sqlite_master：里面存储着数据库的数据结构，只能对其查询（只读）
sqlite_temp_master：是记录临时表信息
4、查询表是否存在
SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'tablename';
或
SELECT * FROM sqlite_master WHERE type='table' AND name = 'tablename';
如果查询结果大于0，表示该表存在于数据库中，否则不存在。
5、创建引索
CREATE INDEX index_name ON table_name (column_name);CREATE INDEX index_name ON table_name (column_name DESC);//降序
在表中创建索引，可以更加快速高效地查询数据
6、获取搜索的数量，可以添加条件
SELECT COUNT() FROM table;SELECT COUNT() FROM table WHERE ***;
