sqlite3常用命令：

# sqlite3 
SQLite version 3.41.2 2023-03-22 11:56:21
Enter ".help" for usage hints.
Connected to a transient in-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
.cd DIRECTORY            Change the working directory to DIRECTORY
.changes on|off          Show number of rows changed by SQL
.check GLOB              Fail if output 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
.echo on|off             Turn command echo on or off
.eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
.excel                   Display the output of next command in spreadsheet
.exit ?CODE?             Exit this program with return-code CODE
.expert                  EXPERIMENTAL. Suggest indexes for queries
.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 text for PATTERN
.import FILE 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 mode for one command if nonce matches
.nullvalue STRING        Use STRING in place of NULL values
.once ?OPTIONS? ?FILE?   Output for the next SQL command only to FILE
.open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
.output ?FILE?           Send output to FILE or stdout if FILE 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, exit if primary.
.read FILE               Read input from FILE or command output
.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 (an alias for .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 defined in the SELFTEST table
.separator COL ?ROW?     Change the column and row separators
.sha3sum ...             Compute a SHA3 hash of database content
.shell CMD ARGS...       Run CMD ARGS... in a system shell
.show                    Show the current values for various settings
.stats ?ARG?             Show stats or turn stats on or off
.system CMD ARGS...      Run CMD ARGS... in a 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 tables for MS milliseconds
.timer on|off            Turn SQL timer on or off
.trace ?OPTIONS?         Output each SQL statement as it is run
.version                 Show source, library and compiler versions
.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 minimum column widths for columnar 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

# 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
if the file does not previously exist.
OPTIONS include:
   -A ARGS...           run ".archive ARGS" and exit
   -append              append the database to the end of the file
   -ascii               set output mode to 'ascii'
   -bail                stop after hitting an error
   -batch               force batch I/O
   -box                 set output mode to 'box'
   -column              set output mode to 'column'
   -cmd COMMAND         run "COMMAND" before reading stdin
   -csv                 set output mode to 'csv'
   -deserialize         open the database using sqlite3_deserialize()
   -echo                print inputs before execution
   -init FILENAME       read/process named file
   -[no]header          turn headers on or off
   -help                show this message
   -html                set output mode to HTML
   -interactive         force interactive I/O
   -json                set output mode to 'json'
   -line                set output mode to 'line'
   -list                set output mode to 'list'
   -lookaside SIZE N    use N entries of SZ bytes for lookaside memory
   -markdown            set output mode to 'markdown'
   -maxsize N           maximum size for a --deserialize database
   -memtrace            trace all memory allocations and deallocations
   -mmap N              default mmap size set to N
   -newline SEP         set output row separator. Default: '\n'
   -nofollow            refuse to open symbolic links to database files
   -nonce STRING        set the safe-mode escape nonce
   -nullvalue TEXT      set text string for NULL values. Default ''
   -pagecache SIZE N    use N slots of SZ bytes each for page cache memory
   -quote               set output mode to 'quote'
   -readonly            open the database read-only
   -safe                enable safe-mode
   -separator SEP       set output column separator. Default: '|'
   -stats               print memory stats before each finalize
   -table               set output mode to 'table'
   -tabs                set output mode to 'tabs'
   -version             show SQLite version
   -vfs NAME            use NAME as the default VFS
   -zip                 open the file as a ZIP Archive

参考推荐：

sqlite3数据库命令操作

sqlite数据类型