SQLite學習筆記二(數據庫管理，命令行操作)

1.在下載一個windows下shell程序，下載地址：http://www.sqlite.com/sqlite-shell-win32-x86-3070900.zip

2.下載完成後解壓得到sqlite3.exe，放置在任意目錄；

3.使用方式：

a.打開數據庫

1. Microsoft Windows XP [版本 5.1.2600]
2. (C) 版權所有 1985-2001 Microsoft Corp.
4. C:\Documents and Settings\socrates.WINXP-DUANYX>cd /d E:\tmp\sqlite_stduy\db
6. E:\tmp\sqlite_stduy\db>sqlite3.exe sqlite_study.db --參數為要打開的數據庫名（存在目錄時請帶//訪問）
7. SQLite version 3.7.9 2011-11-01 00:52:41
8. Enter ".help" for instructions
9. Enter SQL statements terminated with a ";"
10. sqlite>

b. 查看命令行幫助：

1. sqlite> .help
2. .backup ?DB? FILE Backup DB (default "main") to FILE
3. .bail ON|OFF Stop after hitting an error. Default OFF
4. .databases List names and files of attached databases
5. .dump ?TABLE? ... Dump the database in an SQL text format
6. If TABLE specified, only dump tables matching
7. LIKE pattern TABLE.
8. .echo ON|OFF Turn command echo on or off
9. .exit Exit this program
10. .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off.
11. With no args, it turns EXPLAIN on.
12. .header(s) ON|OFF Turn display of headers on or off
13. .help Show this message
14. .import FILE TABLE Import data from FILE into TABLE
15. .indices ?TABLE? Show names of all indices
16. If TABLE specified, only show indices for tables
17. matching LIKE pattern TABLE.
18. .load FILE ?ENTRY? Load an extension library
19. .log FILE|off Turn logging on or off. FILE can be stderr/stdout
20. .mode MODE ?TABLE? Set output mode where MODE is one of:
21. csv Comma-separated values
22. column Left-aligned columns. (See .width)
23. html HTML <table> code
24. insert SQL insert statements for TABLE
25. line One value per line
26. list Values delimited by .separator string
27. tabs Tab-separated values
28. tcl TCL list elements
29. .nullvalue STRING Print STRING in place of NULL values
30. .output FILENAME Send output to FILENAME
31. .output stdout Send output to the screen
32. .prompt MAIN CONTINUE Replace the standard prompts
33. .quit Exit this program
34. .read FILENAME Execute SQL in FILENAME
35. .restore ?DB? FILE Restore content of DB (default "main") from FILE
36. .schema ?TABLE? Show the CREATE statements
37. If TABLE specified, only show tables matching
38. LIKE pattern TABLE.
39. .separator STRING Change separator used by output mode and .import
40. .show Show the current values for various settings
41. .stats ON|OFF Turn stats on or off
42. .tables ?TABLE? List names of tables
43. If TABLE specified, only list tables matching
44. LIKE pattern TABLE.
45. .timeout MS Try opening locked tables for MS milliseconds
46. .width NUM1 NUM2 ... Set column widths for "column" mode
47. .timer ON|OFF Turn the CPU timer measurement on or off
48. sqlite>

c.參考以上命令行幫助即可操作數據庫，舉例如下：

1. sqlite> .databases --查看數據庫的存放路徑
2. seq name file
4. --- --------------- ----------------------------------------------------------
6. 0 main E:\tmp\sqlite_stduy\db\sqlite_study.db
8. sqlite> .tables --查看當前數據庫中的表
9. tbl_product tbl_product1 tbl_product2 tbl_product3
10. sqlite> select * from tbl_product3; --執行SQL語句
11. 1|iphone4s
12. sqlite> insert into tbl_product3 values('nokia'); --SQL語句出錯提示
13. Error: table tbl_product3 has 2 columns but 1 values were supplied
14. sqlite> insert into tbl_product3 values(2, 'nokia');
15. sqlite> select * from tbl_product3;
16. 1|iphone4s
17. 2|nokia
18. sqlite>.mode tabs --設置顯示模式（以Tab鍵做為列間間隔符）
19. sqlite> select * from tbl_product3;
20. 1 iphone4s
21. 2 nokia
22. sqlite> .show --查看當前shell的環境變量
23. echo: off
24. explain: off
25. headers: off
26. mode: list
27. nullvalue: ""
28. output: stdout
29. separator: "\t"
30. stats: off
31. width:
32. sqlite>.quit --退出數據庫
34. E:\tmp\sqlite_stduy\db>

其他相關操作請參考.help進行。