SQLite is a C-language library that implements a small, fast, self-contained, high-reliability, full-featured, SQL database engine. SQLite is the most used database engine in the world. SQLite is built into all mobile phones and most computers and comes bundled inside countless other applications that people use every day. about (opens new window)

help

sqlite> .help
.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
.hex-rekey OLD NEW NEW   Change the encryption key using hexadecimal
.import FILE TABLE       Import data from FILE into TABLE
.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.
.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                    Exit this program
.read FILE               Read input from FILE or command output
.rekey OLD NEW NEW     Change the encryption key
.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        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
.session ?NAME? CMD ...  Create or control sessions
.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
.text-rekey OLD NEW NEW  Change the encryption key using hexadecimal
.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
.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

command

• sqlite3 <db> <command>
• .help: help, 内容如上
• .exit

trick

导出数据到 mysql

如果你想将 SQLite 数据库的数据迁移到 MySQL 中, 你需要执行以下步骤:

1.导出 SQLite 数据:

使用 SQLite 提供的工具, 如 sqlite3 命令行工具, 导出 SQLite 数据库的内容为 SQL 文件。例如, 可以使用以下命令: sqlite3 your_database.db .dump > export.sql 这将导出 SQLite 数据库的结构和数据到一个 SQL 文件中

2.创建 MySQL 数据库

在 MySQL 中创建一个新的数据库, 你可以使用 MySQL 的命令行工具或 MySQL 客户端应用程序来完成这个任务

3.导入数据到 MySQL:

使用 MySQL 的命令行工具或 MySQL 客户端应用程序, 导入之前导出的 SQL 文件到 MySQL 数据库中。例如: mysql -u username -p new_database < export.sql 这将执行 SQL 文件中的命令, 将数据导入到 MySQL 数据库中

这个过程将迁移 SQLite 数据到 MySQL 数据库中。请确保你已经安装了 SQLite 和 MySQL 客户端, 并且拥有足够的权限来执行这些操作。另外, 请备份数据, 以防在迁移过程中发生意外情况

---

注意点

在你导出 SQLite 数据库并尝试导入到 MySQL 数据库时, 你需要注意以下几点:

• 不同的 SQL 方言: SQLite 和 MySQL 使用不同的 SQL 方言, 因此导出的 SQL 语句可能不直接适用于 MySQL。你可能需要修改导出的 SQL 文件以适应 MySQL 的语法

• PRAGMA 语句: SQLite 中使用 PRAGMA 语句来配置数据库参数, 但 MySQL 不支持这种方式。你需要删除或替换所有的 PRAGMA 语句

• 数据类型: SQLite 和 MySQL 支持的数据类型可能不完全相同。检查导出的 SQL 文件中的数据类型, 确保它们与 MySQL 兼容

• 自增字段: MySQL 使用 AUTO_INCREMENT 来定义自增字段, 而 SQLite 使用 INTEGER PRIMARY KEY。确保你的导出文件中的自增字段在 MySQL 中正确定义

• 外键约束: SQLite 和 MySQL 在外键约束方面也有不同的语法。你可能需要修改外键约束以适应 MySQL 的语法

export

---

导出数据到 CSV 文件

.mode csv
.headers on
.output data.csv
SELECT * FROM your_table;
.exit

这将导出 your_table 表中的数据到名为 data.csv 的 CSV 文件中。你可以根据需要自定义查询和输出文件名

link

• docs (opens new window)