【数据库】sqlite3常用命令及SQL语句
一、准备工作
0、安装SQLite3
1）ubuntu命令安装（不是最新版本）
sudo apt install sqlite3
1
2）源码安装（可以安装最新版本）
下载：
https://www.sqlite.org/download.html
sqlite-autoconf-3300100.tar.gz(2.72 MiB)
编译：
./configure
make
sudo make install
1
2
3
默认安装在/usr/local/bin下
1、创建数据库
sqlite3 <数据库文件名>
$ ls
$ sqlite3 test.db
SQLite version 3.11.0 2016-02-15 17:29:24
Enter ".help" for usage hints.
sqlite>
1
2
3
4
5
2、创建表格
CREATE TABLE test (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(50) NOT NULL,
age INT
);
1
2
3
4
5
注意：
1> 在MySQL中自增关键词是AUTO_INCREMENT，在sqlite3中使用无效
2> 自增类型必须是 INTEGER，不能是 INT，否则报错：
Error: AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY
3> 主键自增定义时注意关键词的顺序：INTEGER PRIMARY KEY AUTOINCREMENT，要按照这个顺序。
1
2
3
4
3、插入数据
INSERT INTO test (name, age) VALUES ('XiaoHong', 18);
INSERT INTO test (name, age) VALUES ('XiaoMing', 19);
1
2
4、查询
SELECT * FROM test;
1|XiaoHong|18
2|XiaoMing|19
1
2
3
二、sqlite3命令总结
1、命令列表
sqlite> .help
.backup ?DB? FILE Backup 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 format
If TABLE specified, only dump tables matching
LIKE 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 TABLE Import data from FILE into TABLE
.indexes ?TABLE? Show names of all indexes
If TABLE specified, only show indexes for tables
matching LIKE pattern TABLE.
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn 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 0x1E
csv Comma-separated values
column Left-aligned columns. (See .width)
html HTML <table> code
insert SQL insert statements for TABLE
line One value per line
list Values delimited by .separator strings
tabs Tab-separated values
tcl TCL list elements
.nullvalue STRING Use 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? FILE Restore content of DB (default "main") from FILE
.save FILE Write in-memory database into FILE
.scanstats on|off Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?TABLE? Show the CREATE statements
If TABLE specified, only show tables matching
LIKE pattern TABLE.
.separator COL ?ROW? Change the column separator and optionally the row
separator 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|off Turn stats on or off
.system CMD ARGS... Run CMD ARGS... in a system shell
.tables ?TABLE? List names of tables
If TABLE specified, only list tables matching
LIKE pattern TABLE.
.timeout MS Try opening locked tables for MS milliseconds
.timer on|off Turn SQL timer on or off
.trace FILE|off Output each SQL statement as it is run
.vfsinfo ?AUX? Information about the top-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set column widths for "column" mode
Negative values right-justify
sqlite>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
2、备份数据库
$ ls // 查看文件列表，只有test.db
test.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|18
2|XiaoMing|19
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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,age
1,XiaoHong,18
2,XiaoMing,19
1
2
3
4
5
6
7
8
9
10
.once 只将下面的一个SQL语句输出保存到文件中；如果使用.output命令则会把下面所有SQL输出保存到文件中。
4、查看数据信息
sqlite> .databases // 列出数据库名称和文件
seq name file
--- --------------- ----------------------------------------------------------
0 main /home/laoer/Templates/test.db
sqlite> .tables // 列出数据表
test
sqlite> .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: off
width:
sqlite> .dbinfo // 显示数据库的状态信息
database page size: 1024
write format: 1
read format: 1
reserved bytes: 0
file change counter: 3
database page count: 3
freelist page count: 0
schema cookie: 1
schema format: 4
default cache size: 0
autovacuum top root: 0
incremental vacuum: 0
text encoding: 1 (utf8)
user version: 0
application id: 0
software version: 3011000
number of tables: 2
number of indexes: 0
number of triggers: 0
number of views: 0
schema size: 132
sqlite> .limit // 显示或设置数据库限制信息：SQLITE_LIMIT
length 1000000000 // 字符串或BLOB的最大长度10亿，一行的最大长度
sql_length 1000000000 // sql语句最大长度
column 2000 // 列数，可以在编译时才可以将最大列出改为32767
expr_depth 1000 // 表达式树的最大深度，SQLite将表达式解析到树中进行处理。
compound_select 500 // 复合SELECT语句中的最大术语数
vdbe_op 25000 // 虚拟机程序中用于实现SQL语句的最大指令数
function_arg 127 // 一个函数的最大参数个数
attached 10 // ATTACH语句，附加数据库最大值为125
like_pattern_length 50000 // LIKE模式匹配算法或GLOB模式的最大长度
variable_number 250000 // 任何参数的索引号
trigger_depth 1000 // 触发递归的最大深度
worker_threads 0 // 可以启动的辅助工作线程的最大数量
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
常用查询SQL语句
1、查询头几条数据
SELECT * FROM table ORDER BY id LIMIT 10;
1
2、查询最后几条数据
SELECT * FROM table ORDER BY id DESC LIMIT 10;
1
DESC：降序排列
3、查询数据库中所有的数据表table
SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;
1
sqlite_master：里面存储着数据库的数据结构，只能对其查询（只读）
sqlite_temp_master：是记录临时表信息
4、查询表是否存在
SELECT count(*) FROM sqlite_master WHERE type='table' AND name = 'tablename';
1
或
SELECT * FROM sqlite_master WHERE type='table' AND name = 'tablename';
1
如果查询结果大于0，表示该表存在于数据库中，否则不存在。
5、创建引索
CREATE INDEX index_name ON table_name (column_name);
CREATE INDEX index_name ON table_name (column_name DESC); //降序
1
2
在表中创建索引，可以更加快速高效地查询数据
6、获取搜索的数量，可以添加条件
SELECT COUNT() FROM table;
SELECT COUNT() FROM table WHERE ***;
————————————————
版权声明：本文为CSDN博主「郭老二」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u010168781/article/details/88946151