创建数据库

通过sqlite3创建数据库。比如创建一个ex1的数据库(如果ex1数据库不存在，那么在当前目录会创建一个ex1的文件来储存数据):

  1. $ sqlite3 ex1
  2. SQLite version 3.28.0 2019-03-02 15:25:24
  3. Enter ".help" for usage hints.
  4. sqlite> create table tbl1(one varchar(10), two smallint);
  5. sqlite> insert into tbl1 values('hello!',10);
  6. sqlite> insert into tbl1 values('goodbye', 20);
  7. sqlite> select * from tbl1;
  8. hello!|10
  9. goodbye|20
  10. sqlite>

确保每个完整的sql语句结尾有个冒号；sqlite3 是通过查找冒号，来确认sql语句是完整的。如果没有冒号，那么sqlite3会显示一个继续输入的提示符...>，等待你继续输入。这个特性可以提供跨行输入的能力。例如：

  1. sqlite> CREATE TABLE tbl2 (
  2.    ...>   f1 varchar(30) primary key,
  3.    ...>   f2 text,
  4.    ...>   f3 real
  5.    ...> );
  6. sqlite>

内置的点命令（dot-commander）

输入 .help 可查看所有点命令。

  1. sqlite> .help
  2. .archive ...             Manage SQL archives
  3. .auth ON|OFF             Show authorizer callbacks
  4. .backup ?DB? FILE        Backup DB (default "main") to FILE
  5. .bail on|off             Stop after hitting an error.  Default OFF
  6. .binary on|off           Turn binary output on or off.  Default OFF
  7. .cd DIRECTORY            Change the working directory to DIRECTORY
  8. .changes on|off          Show number of rows changed by SQL
  9. .check GLOB              Fail if output since .testcase does not match
  10. .clone NEWDB             Clone data into NEWDB from the existing database
  11. .databases               List names and files of attached databases
  12. .dbconfig ?op? ?val?     List or change sqlite3_db_config() options
  13. .dbinfo ?DB?             Show status information about the database
  14. .dump ?TABLE? ...        Render all database content as SQL
  15. .echo on|off             Turn command echo on or off
  16. .eqp on|off|full|...     Enable or disable automatic EXPLAIN QUERY PLAN
  17. .excel                   Display the output of next command in a spreadsheet
  18. .exit ?CODE?             Exit this program with return-code CODE
  19. .expert                  EXPERIMENTAL. Suggest indexes for specified queries
  20. .fullschema ?--indent?   Show schema and the content of sqlite_stat tables
  21. .headers on|off          Turn display of headers on or off
  22. .help ?-all? ?PATTERN?   Show help text for PATTERN
  23. .import FILE TABLE       Import data from FILE into TABLE
  24. .imposter INDEX TABLE    Create imposter table TABLE on index INDEX
  25. .indexes ?TABLE?         Show names of indexes
  26. .iotrace FILE            Enable I/O diagnostic logging to FILE
  27. .limit ?LIMIT? ?VAL?     Display or change the value of an SQLITE_LIMIT
  28. .lint OPTIONS            Report potential schema issues.
  29. .load FILE ?ENTRY?       Load an extension library
  30. .log FILE|off            Turn logging on or off.  FILE can be stderr/stdout
  31. .mode MODE ?TABLE?       Set output mode
  32. .nullvalue STRING        Use STRING in place of NULL values
  33. .once (-e|-x|FILE)       Output for the next SQL command only to FILE
  34. .open ?OPTIONS? ?FILE?   Close existing database and reopen FILE
  35. .output ?FILE?           Send output to FILE or stdout if FILE is omitted
  36. .parameter CMD ...       Manage SQL parameter bindings
  37. .print STRING...         Print literal STRING
  38. .progress N              Invoke progress handler after every N opcodes
  39. .prompt MAIN CONTINUE    Replace the standard prompts
  40. .quit                    Exit this program
  41. .read FILE               Read input from FILE
  42. .restore ?DB? FILE       Restore content of DB (default "main") from FILE
  43. .save FILE               Write in-memory database into FILE
  44. .scanstats on|off        Turn sqlite3_stmt_scanstatus() metrics on or off
  45. .schema ?PATTERN?        Show the CREATE statements matching PATTERN
  46. .selftest ?OPTIONS?      Run tests defined in the SELFTEST table
  47. .separator COL ?ROW?     Change the column and row separators
  48. .session ?NAME? CMD ...  Create or control sessions
  49. .sha3sum ...             Compute a SHA3 hash of database content
  50. .shell CMD ARGS...       Run CMD ARGS... in a system shell
  51. .show                    Show the current values for various settings
  52. .stats ?on|off?          Show stats or turn stats on or off
  53. .system CMD ARGS...      Run CMD ARGS... in a system shell
  54. .tables ?TABLE?          List names of tables matching LIKE pattern TABLE
  55. .testcase NAME           Begin redirecting output to 'testcase-out.txt'
  56. .timeout MS              Try opening locked tables for MS milliseconds
  57. .timer on|off            Turn SQL timer on or off
  58. .trace ?OPTIONS?         Output each SQL statement as it is run
  59. .vfsinfo ?AUX?           Information about the top-level VFS
  60. .vfslist                 List all available VFSes
  61. .vfsname ?AUX?           Print the name of the VFS stack
  62. .width NUM1 NUM2 ...     Set column widths for "column" mode

点命令的一些使用限制：

  • 必须以后点开始，点前后不能有空格
  • dot命令必须完全包含在单个输入行中
  • 点命令不能出现在普通sql语句的中间
  • 点命令不能有注释

    参考

    sqlite3-cli