Sqlite：学习下
Sqlite 当前广泛应用的 开源（public domain）嵌入式RDBMS系统，基本遵循SQL-92的标准
特点：简单、0配置、高效、可靠、单文件、支持事务（ACID：数据库基本的锁）、完全跨平台、跨32\64、多读+1写
注意：弱列类型（null、integer、real、text、blob：虽然可以ddl时候声明，但rdbms系统不强制实施）
：弱列长度约束，你可以定义长度，但rdms不强制实施，处理自增列（int primary key）时
基本的sqlite3命令行接口的使用、管理
| 备份、还原 |
.backup ?DB? FILE Backup DB (default "main") to FILE .restore ?DB? FILE Restore content of DB (default "main") from FILE |
| 导入、导出 |
.output FILENAME Send output to FILENAME .import FILE TABLE Import data from FILE into TABLE .separator STRING Change separator used by output mode and .import .nullvalue STRING Use STRING in place of NULL values |
| 查询字典数据 |
.databases List names and files of attached databases .indices ?TABLE? Show names of all indices.If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .schema ?TABLE? Show the CREATE statements. If TABLE specified, only show tables matching LIKE pattern TABLE. .tables ?TABLE? List names of tables.If TABLE specified, only list tables matching LIKE pattern TABLE. 2个内部构架表： sqlite_master、sqlite_temp_master 列出数据接库中所有的表、索引、触发器等 |
| 交互界面：格式设置 |
.mode MODE ?TABLE? Set output mode where MODE is one of: .nullvalue STRING Use STRING in place of NULL values .separator STRING Change separator used by output mode and .import .width NUM1 NUM2 ... Set column widths for "column" mode .header(s) ON|OFF Turn display of headers on or off .echo ON|OFF Turn command echo on or off |
| 性能profile |
.stats ON|OFF Turn stats on or off .timer ON|OFF Turn the CPU timer measurement on or off .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. |
| 日志系统 |
.log FILE|off Turn logging on or off. FILE can be stderr/stdout：错误日志 .trace FILE|off Output each SQL statement as it is run：一般的sql语句跟踪日志 |
| 错误处理控制 | .bail ON|OFF Stop after hitting an error. Default OFF |
| 事务控制 | .timeout MS Try opening locked tables for MS milliseconds |
| 执行外部sql文件 | .read FILENAME Execute SQL in FILENAME |
| 其他辅助 |
.help Show this message .print STRING... Print literal STRING .prompt MAIN CONTINUE Replace the standard prompts .show Show the current values for various settings |
| 退出 |
.exit Exit this program .quit Exit this program |
sqlite3命令行接口的显示方式".mode"，很强大！
| 格式选项 | 说明 |
| tabs | tab 分割的文本格式 |
| list | .separator分割的文本格式，默认“|” |
| csv | 逗号分割的文本格式 |
| tcl | 双引号括起来的空个分割的文本格式 |
| insert | 直接生成insert的sql代码。该模式必须配合.mode命令的 table 参数 |
| html | 生成html页码，注意不是完整的web页码，而仅仅是行、列部分 |
| line | 类似于其他工具如mysql、sqlplus等的 垂直显示\G |
| column | 空格填充padding的左对齐left文本固定宽度的文本表格式。适合【组固定长度】格式数据 |
Sqlite3不支持的SQL-92主要特性
直至本人写作的时间：2013-1-11：这些特性，数字越小（即越早提及的特性）越有肯能尽早实现，但不一定奥！
- RIGHT and FULL OUTER JOIN ： LEFT OUTER JOIN is implemented, but not RIGHT OUTER JOIN or FULL OUTER JOIN.
- Complete ALTER TABLE support Only the RENAME TABLE and ADD COLUMN variants of the ALTER TABLE command are supported. Other kinds of ALTER TABLE operations such as DROP COLUMN, ALTER COLUMN, ADD CONSTRAINT, and so forth are omitted.
- Complete trigger support FOR EACH ROW triggers are supported but not FOR EACH STATEMENT triggers.
- Writing to VIEWs VIEWs in SQLite are read-only. You may not execute a DELETE, INSERT, or UPDATE statement on a view. But you can create a trigger that fires on an attempt to DELETE, INSERT, or UPDATE a view and do what you need in the body of the trigger.
- GRANT and REVOKE Since SQLite reads and writes an ordinary disk file, the only access permissions that can be applied are the normal file access permissions of the underlying operating system. The GRANT and REVOKE commands commonly found on client/server RDBMSes are not implemented because they would be meaningless for an embedded database engine.
