sqlite3常用命令汇总
sqlite3常用命令：
|
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
68
69
70
71
72
|
# sqlite3
SQLite version 3.41.2 2023-03-22 11:56:21
Enter".help"forusage hints.
Connected to a transientin-memory database.
Use".open FILENAME"to reopen on a persistent database.
sqlite> .help
.archive ... Manage SQL archives
.auth ON|OFF Show authorizer callbacks
.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
.cdDIRECTORY Change the working directory to DIRECTORY
.changes on|off Show number of rows changed by SQL
.check GLOB Failifoutput since .testcase does not match
.clone NEWDB Clone data into NEWDB from the existing database
.connection [close] [#] Open or close an auxiliary database connection
.databases List names and files of attached databases
.dbconfig ?op? ?val? List or change sqlite3_db_config() options
.dbinfo ?DB? Show status information about the database
.dump ?OBJECTS? Render database content as SQL
.echoon|off Turncommandechoon or off
.eqp on|off|full|... Enable or disable automatic EXPLAIN QUERY PLAN
.excel Display the output of nextcommandinspreadsheet
.exit?CODE? Exit this program withreturn-code CODE
.expert EXPERIMENTAL. Suggest indexesforqueries
.explain ?on|off|auto? Change the EXPLAIN formatting mode. Default: auto
.filectrl CMD ... Run various sqlite3_file_control() operations
.fullschema ?--indent? Show schema and the content of sqlite_stat tables
.headers on|off Turn display of headers on or off
.help ?-all? ?PATTERN? Show help textforPATTERN
.importFILE TABLE Import data from FILE into TABLE
.imposter INDEX TABLE Create imposter table TABLE on index INDEX
.indexes ?TABLE? Show names of indexes
.limit ?LIMIT? ?VAL? Display or change the value of an SQLITE_LIMIT
.lint OPTIONS Report potential schema issues.
.load FILE ?ENTRY? Load an extension library
.log FILE|off Turn logging on or off. FILE can be stderr/stdout
.mode MODE ?OPTIONS? Set output mode
.nonce STRING Suspend safe modeforonecommandifnonce matches
.nullvalue STRING Use STRINGinplace of NULL values
.once ?OPTIONS? ?FILE? Outputforthe next SQLcommandonly to FILE
.open?OPTIONS? ?FILE? Close existing database and reopen FILE
.output ?FILE? Send output to FILE or stdoutifFILE is omitted
.parameter CMD ... Manage SQL parameter bindings
.print STRING... Print literal STRING
.progress N Invoke progress handler after every N opcodes
.prompt MAIN CONTINUE Replace the standard prompts
.quit Stop interpreting input stream,exitifprimary.
.readFILE Read input from FILE orcommandoutput
.recover Recover as much data as possible from corrupt db.
.restore ?DB? FILE Restore content of DB (default"main") from FILE
.save ?OPTIONS? FILE Write database to FILE (analiasfor.backup ...)
.scanstats on|off|est Turn sqlite3_stmt_scanstatus() metrics on or off
.schema ?PATTERN? Show the CREATE statements matching PATTERN
.selftest ?OPTIONS? Run tests definedinthe SELFTEST table
.separator COL ?ROW? Change the column and row separators
.sha3sum ... Compute a SHA3hashof database content
.shell CMD ARGS... Run CMD ARGS...ina system shell
.show Show the current valuesforvarious settings
.stats ?ARG? Show stats or turn stats on or off
.system CMD ARGS... Run CMD ARGS...ina system shell
.tables ?TABLE? List names of tables matching LIKE pattern TABLE
.testcase NAME Begin redirecting output to'testcase-out.txt'
.testctrl CMD ... Run various sqlite3_test_control() operations
.timeout MS Try opening locked tablesforMS milliseconds
.timer on|off Turn SQL timer on or off
.trace ?OPTIONS? Output each SQL statement as it is run
.version Showsource, library and compiler versions
.vfsinfo ?AUX? Information about thetop-level VFS
.vfslist List all available VFSes
.vfsname ?AUX? Print the name of the VFS stack
.width NUM1 NUM2 ... Set minimum column widthsforcolumnar output
|
.open filename - 打开或创建一个SQLite数据库文件
.tables - 列出当前数据库中的表
.schema tablename - 查看指定表的创建语句
.header on|off - 设置是否显示查询结果的列名
.mode csv|column|json - 设置查询结果的显示模式
.import filename tablename - 从文件导入数据到指定表
.quit - 退出sqlite3命令行工具
SELECT column1, column2… FROM table_name WHERE condition - 从表中选择数据
INSERT INTO table_name (column1, column2…) VALUES (value1, value2…) - 插入数据到表
UPDATE table_name SET column1 = value1, column2 = value2… WHERE condition - 更新表中的数据
DELETE FROM table_name WHERE condition - 从表中删除数据
ALTER TABLE table_name ADD column_name datatype - 向表中添加新列
DROP TABLE table_name - 删除表
CREATE INDEX index_name ON table_name (column1, column2…) - 在表中创建索引
PRAGMA table_info(table_name) - 显示表的列信息
PRAGMA database_list - 显示数据库中的表和索引
PRAGMA foreign_key_list(table_name) - 查看表的外键信息
PRAGMA index_list(table_name) - 显示表的索引信息
PRAGMA table_info(table_name) - 显示表的详细信息
.databases - 显示当前连接的所有数据库
.fullschema - 显示数据库的完整模式（包括索引、触发器等）
.backup filename - 备份数据库
.restore filename - 从备份文件中恢复数据库
.mode line - 设置查询结果的显示模式为每行一条记录
.mode list - 设置查询结果的显示模式为键值对形式
.explain - 显示查询计划
.timeout milliseconds - 设置查询超时时间
.echo on|off - 设置是否显示命令
.auth ON|OFF|username|password - 启用/禁用/更改访问控制
.nullvalue text - 设置显示空值的文本
.width num1 num2 … - 设置每列的宽度
.separator string - 设置导出文件的分隔符
.save filename - 保存输出结果到文件
.output filename - 重定向输出到文件
.read filename - 读取并执行SQL语句文件
.timer on|off - 设置是否显示查询时间
.shell cmd - 运行操作系统命令
.help - 显示帮助信息
.version - 显示SQLite版本信息
.selftest - 运行自检测试
.clone new_database - 克隆当前数据库到新的数据库
.mode insert table_name - 以INSERT语句模式导出结果
.clone :memory: - 将内存数据库克隆到文件数据库
.archive - 将当前数据库归档
.recover database - 修复损坏的数据库
.changequote ON|off|string - 更改标识符的引号字符
.analyze - 分析数据库以优化查询性能
.randomblob numbytes - 生成指定大小的随机二进制数据
.changes - 显示上一条命令影响的行数
.lint OPTIONS - 执行语法检查
.stats ON|off - 启用/禁用语句执行统计
.once filename - 将输出结果保存到文件（仅一次）
.print cmd - 打印输出结果
.batch filename - 运行批处理SQL文件
.open :memory: - 打开一个新的内存数据库
.exit - 退出sqlite3命令行工具
.check GLOB | REGEXP | LIKE - 执行数据完整性检查
.clone :memory: :memory: - 复制内存数据库
.dump - 以文本形式导出数据库
.show - 显示当前配置参数
.shell cmd args… - 使用shell运行操作系统命令
.reindex - 重新建立所有索引
.allnulls on|off - 设置是否考虑空值进行比较
.dbconfig config - 设置数据库配置参数
.exit - 退出sqlite3命令行工具
.clone new_database - 将当前数据库克隆到新的数据库
.dbinfo - 显示数据库信息
.lint cmd - 执行语法检查
.last_insert_rowid - 显示最后插入的行ID
.load filename sym - 加载扩展
.nullvalue text - 设置NULL值的表示文本
.read filename - 读取并执行SQL命令文件
.timeout ms - 设置命令运行的超时时间
.trace cmd - 执行跟踪命令
.vfsinfo ?DATABASE? - 显示或配置虚拟文件系统
.recover - 修复损坏的数据库
.vfslist ?PATTERN? - 显示可用的虚拟文件系统
.vfsname ?AUX? - 显示当前的虚拟文件系统名称
.vfsregister NAME SCRIPT - 注册自定义的虚拟文件系统
.vfsunregister NAME - 取消注册虚拟文件系统
.vdbecomment ON|off - 设置是否在生成代码时加注释
.vdbeinfo ?NEW|schema|execute? - 显示虚拟数据库引擎信息
.vdbe_trace on|off - 设置是否跟踪虚拟数据库引擎
.header on|off - 设置是否显示查询结果的列名
.mode line|column|… - 设置结果的显示模式
.output filename - 重定向输出到文件
.print - 打印输出
BEGIN [DEFERRED|IMMEDIATE|EXCLUSIVE] - 开始一个事务
COMMIT - 提交事务
ROLLBACK - 回滚事务
SAVEPOINT name - 设置保存点
RELEASE name - 释放保存点
ROLLBACK TO name - 回滚到保存点
TRANSACTION [READ UNCOMMITTED|READ COMMITTED|SERIALIZABLE|ISOLATION LEVEL …] - 设置事务隔离级别
PRAGMA encoding = “UTF-8” - 设置数据库编码
PRAGMA foreign_keys = ON|off - 启用/禁用外键约束
PRAGMA journal_mode = DELETE|TRUNCATE|PERSIST|MEMORY|WAL|OFF - 设置日志模式
PRAGMA synchronous = OFF|NORMAL|FULL - 设置同步模式
PRAGMA temp_store = DEFAULT|FILE|MEMORY - 设置临时存储方式
PRAGMA cache_size = 1000 - 设置缓存大小
sqlite3
|
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
|
# which sqlite3
/usr/local/sqlite3/bin/sqlite3
#
# sqlite3 --help
Usage: sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of an SQLite database. A new database is created
ifthefiledoes not previously exist.
OPTIONS include:
-A ARGS... run".archive ARGS"andexit
-append append the database to the end of thefile
-ascii setoutput mode to'ascii'
-bail stop after hitting an error
-batch force batch I/O
-box setoutput mode to'box'
-column setoutput mode to'column'
-cmd COMMAND run"COMMAND"before reading stdin
-csv setoutput mode to'csv'
-deserialize openthe database using sqlite3_deserialize()
-echo print inputs before execution
-init FILENAME read/processnamedfile
-[no]header turn headers on or off
-help show this message
-html setoutput mode to HTML
-interactive force interactive I/O
-json setoutput mode to'json'
-line setoutput mode to'line'
-list setoutput mode to'list'
-lookaside SIZE N use N entries of SZ bytesforlookaside memory
-markdown setoutput mode to'markdown'
-maxsize N maximum sizefora --deserialize database
-memtrace trace all memory allocations and deallocations
-mmap N default mmap sizesetto N
-newline SEP setoutput row separator. Default:'
'
-nofollow refuse toopensymbolic links to database files
-nonce STRING setthe safe-mode escape nonce
-nullvalue TEXT settext stringforNULL values. Default''
-pagecache SIZE N use N slots of SZ bytes eachforpage cache memory
-quote setoutput mode to'quote'
-readonly openthe databaseread-only
-safe enablesafe-mode
-separator SEP setoutput column separator. Default:'|'
-stats print memory stats before each finalize
-table setoutput mode to'table'
-tabs setoutput mode to'tabs'
-version show SQLite version
-vfs NAME use NAME as the default VFS
-zip openthefileas a ZIP Archive
|