`
shappy1978
  • 浏览: 700170 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

SQLite FAQ

 
阅读更多
sqlite3官方的常见问题

随便看了看,留着存个档。如果大家看到有什么错误,欢迎大家提出,谢谢
1. 怎么创建一个自增字段
把字段声明成 interger primary key 就可以了
例子 
        create table t1 ( a integer primary key , b integer);
        insert into t1 values (NULL, 10);
sqlite3_last_insert_rowid() 这个函数获得最后插入的记录的id
2. sqlite支持哪些数据类型
integer, real, text, blob, null 
3. sqlite为什么能让我插入一个字符串到一个整形字段
这个特性不是bug. SQLite uses dynamic typing.
不做数据类型的强制转换,任何数据都能被插入到任何列。
除了primary key限定的字段做整形检查,其他字段都不检查插入数据类型.
insert into t1 values(NULL, 'lskdf');
4. 为什么不让我使用'0'和'0.0'在一张表中同时做主键
你肯定是用的integer做的主键类型,换成text类型做主键就行了
5. 多个应用程序或同一个应用程序的多个线程能同时访问一个数据库文件吗
可以。但是只能同时访问只能做select,只能有1个进程去修改数据
sqlite使用读写锁控制数据库的访问权限.
注意: nfs文件系统支持有问题
6. sqlite线程安全吗
是安全的.
编译的时候SQLITE_THREADSAFE的参数设置成1,就支持线程安全
如果不确定是否支持可以调用sqlite3_threadsafe() 函数来检测,返回0就是支持
7. 怎样列出数据库中所有的表和索引
在命令行模式使用 ".tables"列出所有的表和索引
也可以使用 ".shema" 列出所有的表的概要 类似于mysql中的(show create table xxx)
".schema tablename/LIKE patern" 可以直接跟表名字,也可以跟一个LIKE的匹配
例子".schema tab%"
在c或c++中,我们可以通过一个select语句来获取数据库中的所有的表
sqlite默认有一个sqlite_master表,里面存了所有的已经创建的表,这个表的定义如下:
sqlite> .schema sqlite_master
CREATE TABLE sqlite_master (
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
索引的类型是"index"
当type是索引的时, name字段是索引名, tbl_name是表名, sql字段是创建表/索引的语句
sqlite> select name from sqlite_master where type='table';
sqlite_sequence
t1
t2
8. sqlite数据库的大小限制
好长,看了下,反正肯定我用不完
9. VARCHAR类型的大小限制
就算你声明的是varchar(10), 你也可以存500个字符进去. 最多允许5000个字符.
10. 是否支持BLOB类型
3.0以及以上版本都支持, 不管你的列定义的是什么类型都能插入BLOB类型
11. 我怎么删除或添加表中的列
sqlite只能添加字段在表的最后或rename这个字段名
如果你要更多的修改,建议你建个新表 例子如下
如果你有一个表 "t1", 有"a", "b", "c" 3列, 然后你想删除列"c",就可以做这个操作
BEGIN TRANSACTION;
CREATE TEMPORARY TABLE t1_backup(a,b);
INSERT INTO t1_backup SELECT a,b FROM t1;
DROP TABLE t1;
CREATE TABLE t1(a,b);
INSERT INTO t1 SELECT a,b FROM t1_backup;
DROP TABLE t1_backup;
COMMIT;
12. 我删除了大量的数据, 但是数据库大小没有变小,这是bug吗?
这不是bug.  删除完后的空闲磁盘都被添加到内部的"free_list"中, 用来存储下次插入的数据.
如果你想释放空间可以用 "vacuum" 命令.
在3.1版本,有一个auto-vaccum模式
查看这个模式 "PRAGMA auto_vacuum" 
0 none        (default) 
1 full        每次删除都清空数据库的空间(必须在表被创建的之前打开这个选项才有效)
2 incremental  附加信息需要被存储到数据库中, 但是每次任务完成后是不会像 设置成 1那样自动执行. 需要单独的执行"PRAGMA incremental_vacuum" 这个命令来产生事件(不懂什么意思)
13. 是否可以免费商业化
可以.
14. 怎样逃逸单引号
2个单引号就可以转义一个单引号 例子
INSERT INTO xyz VALUES('5 O''clock');
 
15. SQLITE_SCHEMA错误是什么
当一个预备好的sql语句不再有效或不能执行就会返回这个错误. 
出现这个错误必须重新编译一下sql语句
这个错误只可能在调用sqlite3_prepare(), and sqlite3_step() 出现, 不可能从sqlite3_exec()函数中返回这个错误
如果你用sqlite3_prepare_v2()替换sqlite3_prepare(), 你也不会收到这个错误
(16) Why does ROUND(9.95,1) return 9.9 instead of 10.0? Shouldn't 9.95 round up?
    SQLite uses binary arithmetic and in binary, there is no way to write 9.95 in a finite number of bits. The closest to you can get to 9.95 in a 64-bit IEEE float (which is what SQLite uses) is 9.949999999999999289457264239899814128875732421875. So when you type "9.95", SQLite really understands the number to be the much longer value shown above. And that value rounds down.
    This kind of problem comes up all the time when dealing with floating point binary numbers. The general rule to remember is that most fractional numbers that have a finite representation in decimal (a.k.a "base-10") do not have a finite representation in binary (a.k.a "base-2"). And so they are approximated using the closest binary number available. That approximation is usually very close, but it will be slightly off and in some cases can cause your results to be a little different from what you might expect.
(17) I get hundreds of compiler warnings when I compile SQLite. Isn't this a problem? Doesn't it indicate poor code quality?
    Quality assurance in SQLite is done using full-coverage testing, not by compiler warnings or other static code analysis tools. In other words, we verify that SQLite actually gets the correct answer, not that it merely satisfies stylistic constraints. Most of the SQLite code base is devoted purely to testing. The SQLite test suite runs tens of thousands of separate test cases and many of those test cases are parameterized so that hundreds of millions of tests involving billions of SQL statements are run and evaluated for correctness prior to every release. The developers use code coverage tools to verify that all paths through the code are tested. Whenever a bug is found in SQLite, new test cases are written to exhibit the bug so that the bug cannot recur undetected in the future.
    During testing, the SQLite library is compiled with special instrumentation that allows the test scripts to simulate a wide variety of failures in order to verify that SQLite recovers correctly. Memory allocation is carefully tracked and no memory leaks occur, even following memory allocation failures. A custom VFS layer is used to simulate operating system crashes and power failures in order to ensure that transactions are atomic across these events. A mechanism for deliberately injecting I/O errors shows that SQLite is resilient to such malfunctions. (As an experiment, try inducing these kinds of errors on other SQL database engines and see what happens!)
    We also run SQLite using Valgrind on Linux and verify that it detects no problems.
    Some people say that we should eliminate all warnings because benign warnings mask real warnings that might arise in future changes. This is true enough. But in reply, the developers observe that all warnings have already been fixed in the compilers used for SQLite development (various versions of GCC). Compiler warnings only arise from compilers that the developers do not have access to.
18. 不能匹配unicode字符
19. 插入十分慢 - I can only do few dozen INSERTs per second( dozen == 累计到一定量再插入吗?)
实际上sqlite能够在普通的电脑上很容易的每秒插入50,000或更多的数据
But it will only do a few dozen transactions per second.
20. 如果我删除了很重要的数据,我怎么恢复?
如果你有有备份 可以从备份中恢复. 
如果你没有备份 恢复数据将非常困难. 你可以能从裸数据库文件中的dump中找到一些数据 云云 
(21) What is an SQLITE_CORRUPT error? What does it mean for the database to be "malformed"? Why am I getting this error?
    An SQLITE_CORRUPT error is returned when SQLite detects an error in the structure, format, or other control elements of the database file.
    SQLite does not corrupt database files, except in the case of very rare bugs (see DatabaseCorruption) and even then the bugs are normally difficult to reproduce. Even if your application crashes in the middle of an update, your database is safe. The database is safe even if your OS crashes or takes a power loss. The crash-resistance of SQLite has been extensively studied and tested and is attested by years of real-world experience by millions of users."
    That said, there are a number of things that external programs or bugs in your hardware or OS can do to corrupt a database file. Details can be found in the discussions on the atomic commit and locking support in SQLite as well as in the mailing list archives.
    Your can use PRAGMA integrity_check to do a thorough but time intensive test of the database integrity.
    Your can use PRAGMA quick_check to do a faster but less thorough test of the database integrity.
    Depending how badly your database is corrupted, you may be able to recover some of the data by using the CLI to dump the schema and contents to a file and then recreate. Unfortunately, once humpty-dumpty falls off the wall, it is generally not possible to put him back together again.
22. 外键支持
在3.6.19 就支持外键
(23) I get a compiler error if I use the SQLITE_OMIT_... compile-time options when building SQLite.
    The SQLITE_OMIT_... compile-time options only work when building from canonically source files. They do not work when you build from the SQLite amalgamation or from the pre-processed source files.
    It is possible to build a special amalgamation that will work with a predetermined set of SQLITE_OMIT_... options. Instructions for doing so can be found with the SQLITE_OMIT_... documentation. 
24. 我的where表达式 column1="column1" 不工作, 会返回表中的所有行
把双引号换成单引号, 
(25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated?
    The process is explained at http://wiki.tcl.tk/21708.
26没看明白, 反正sqlite能够在unique字段可以为NULL的时候插入多个NULL,mysql也可以
(26) The SQL standard requires that a UNIQUE constraint be enforced even of one or more of the columns in the constraint are NULL, but SQLite does not do this. Isn't that a bug?
    Perhaps you are referring to the following statement from SQL92:
        A unique constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns. 
    That statement is ambiguous, having at least two possible interpretations:
       1. A unique constraint is satisfied if and only if no two rows in a table have the same values and have non-null values in the unique columns.
       2. A unique constraint is satisfied if and only if no two rows in a table have the same values in the subset of unique columns that are not null. 
    SQLite follows interpretation (1), as does PostgreSQL, MySQL, Oracle, and Firebird. It is true that Informix and Microsoft SQL Server use interpretation (2), however we the SQLite developers hold that interpretation (1) is the most natural reading of the requirement and we also want to maximize compatibility with other SQL database engines, and most other database engines also go with (1), so that is what SQLite does. 

分享到:
评论

相关推荐

    SQLite 中文指南之FAQ第1/6页

    SQLite 是一个轻量级的、嵌入式的数据库引擎,广泛应用...这些只是SQLite使用中的一部分常见问题和解答,完整的SQLite FAQ可以提供更详尽的信息。在实际使用中,了解这些知识点可以帮助更好地管理和操作SQLite数据库。

    Sqlite环境和开发资料

    SQLite的官方文档非常全面,包括用户指南、编程指南、管理员指南和FAQ等部分,覆盖了所有核心功能和高级主题。这些文档可以帮助开发者深入理解SQLite的工作原理,解决实际问题。 五、开发资料 在你获取的压缩包中,...

    sqlite3_doc文档

    9. **附录**:可能包含了编码规范、常见问题解答(FAQ)以及与其他数据库系统的比较等。 通过这些文档,开发者可以深入理解SQLite3的工作原理,有效地使用其API进行数据库操作,解决开发过程中遇到的问题,并优化...

    sqlite-doc-3070900.zip

    8. **常见问题解答(FAQ)**:提供了解决常见问题的快速参考,帮助开发者解决使用过程中遇到的问题。 通过阅读`sqlite-doc-3070900`中的文档,开发者不仅可以学习到SQLite的基本用法,还可以深入了解其内部工作机制...

    dotnet_sqlite_winform

    “常遇问题的解决”这部分内容可能是一个常见问题解答(FAQ)或故障排除指南,旨在帮助开发者解决在构建、部署或运行程序时可能遇到的问题。这些问题可能涵盖从数据库连接错误到UI交互异常等各个方面。 标签“c#”...

    [★★★★★]全面解析SQLite.pdf

    SQLite是一种轻量级的关系型数据库管理...文档最后提供了SQLite常见问题解答(FAQ),这些解答可以帮助开发者解决在使用SQLite过程中可能遇到的问题,确保开发者能够更好地利用SQLite数据库管理系统来满足应用需求。

    SqLite的常见问题列表。(很有用的哦)

    本文严格整理自最新的:http://www.sqlite.org/faq.html, (3.7.8) (多说一句,看一个系统的发布,经常首先要看的就是,readme, 新特性,然后就是FAQ列表, 即所谓的常见问题列表)

    sqlite3安装包

    文档是学习和解决问题的关键资源,SQLite3提供了详尽的在线文档,包括用户指南、API参考、FAQ等。你提到的博客文章(http://blog.csdn.net/hudan2714/article/details/7762467)可能提供了更具体的安装和使用教程,...

    Qt4访问sqlite数据库.doc

    对于更详细的问题解答,可以参考SQLite的中文FAQ,它包含了从基本操作到高级特性的各种问题解答。 总结来说,SQLite是Qt4应用程序中理想的轻量级数据库选择,它的简单性、稳定性和高效性使得在各种项目中都能找到它...

    sqlite数据库

    9. **常见问题解答**(FAQ):解答用户在使用SQLite过程中可能遇到的问题。 10. **示例代码**:提供实际应用SQLite的代码示例,帮助开发者快速上手。 通过学习和理解SQLite的这些知识点,开发者能够有效地在项目中...

    sqlite3.7.5资料

    4. **FAQ和示例**:解答常见问题,并提供实用的代码示例,帮助快速上手。 5. **变更日志**:记录了从SQLite3.7.5之前的版本到此版本的改进和修复。 SQLite3.7.5的使用方法通常包括以下步骤: 1. **安装和配置**:...

    SQLite Viewer-crx插件

    语言:English 支持打开多个数据库 功能的表格sqlite查看器和编辑器:1....请访问FAQ页面以获取更多信息。您可以使用此扩展程序查看SQLite查询甚至创建新数据库或修改现有数据库。您可以更改并保存结果以供以后使用。

    下载-Android新手入门 FAQ.docx

    5. SQLite 数据库:轻量级的数据库系统,适用于存储结构化数据。 6. 多媒体支持:广泛支持多种音频、视频和静态图像格式。 7. 通信技术:包括GSM、蓝牙、EDGE、3G和WiFi(硬件支持)。 8. 多种传感器支持:如相机、...

    Android高手过招 FAQ.zip

    3. **数据存储**:SQLite数据库的使用,ContentResolver的概念,以及SharedPreferences的读写操作。 4. **网络编程**:使用HttpURLConnection或OkHttp进行网络请求,理解异步加载和缓存策略。 5. **多线程**:...

    Android高手过招 FAQ .zip

    3. **数据存储**:Android提供多种数据存储方式,如SharedPreferences、SQLite数据库、文件系统和ContentProvider。FAQ可能涉及如何选择合适的数据存储方案以及具体操作方法。 4. **网络编程**:Android应用经常...

    Android高手过招 FAQ.rar

    3. **数据存储**:Android中数据存储的方式有多种,如SQLite数据库、SharedPreferences、文件系统、ContentProvider等。FAQ可能涵盖了这些方式的使用场景、优缺点及操作技巧。 4. **网络编程**:Android应用经常...

Global site tag (gtag.js) - Google Analytics