sqlite 使用

下载

sqlite jdbc下载http://www.zentus.com/sqlitejdbc/

The current version is v056,based on SQLite 3.6.14.2.

语句

1)创建一个单个Primary Key的table
CREATE TABLE [Admin] (
[UserName] [nvarchar] (20) PRIMARY KEY NOT NULL,
[Password] [nvarchar] (50) NOT NULL,
[Rank] [smallint] NOT NULL,
[MailServer] [nvarchar] (50) NOT NULL,
[MailUser] [nvarchar] (50) NOT NULL,
[MailPassword] [nvarchar] (50) NOT NULL,
[Mail] [nvarchar] (50) NOT NULL
) ;
2)创建一个多个Primary Key的table
CREATE TABLE [CodeDetail] (
[CdType] [nvarchar] (10) NOT NULL,
[CdCode] [nvarchar] (20) NOT NULL,
[CdString1] [ntext] NOT NULL,
[CdString2] [ntext] NOT NULL,
[CdString3] [ntext] NOT NULL,
PRIMARY KEY (CdType,CdCode)

) ;
3)创建索引
CREATE INDEX [IX_Account] ON [Account]([IsCheck],[UserName]);

sqlite可以在shell/dos command底下直接执行命令：

sqlite3 film.db "select * from film;"

输出 HTML 表格：

sqlite3 -html film.db "select * from film;"

将数据库「倒出来」：

sqlite3 film.db ".dump" > output.sql

利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：

sqlite3 film.db < output.sql

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;

常用sql

--返回UTC日期和时间
select CURRENT_TIMESTAMP;

--返回当前UTC时间

select CURRENT_TIME;

--返回当前UTC日期

select CURRENT_DATE;

--返回本地当前时间

SELECT time('now','localtime');

SELECT date('now','localtime')；

--日期比较

select julianday('2008-12-22') - julianday('2008-12-21');

--返回当前日期
SELECT date('now');
--返回本月最后一天
SELECT date('now','start of month','+1 month','-1 day');

--返回本年10月第一个星期二
SELECT date('now','start of year','+9 months','weekday 2');

--SQLite分页显示,表示跳过10行,再取5行
Select rowid,* From keys order by rowid asc Limit 10 Offset 5 ;

数据类型

一般数据采用的固定的静态数据类型，而SQLite采用的是动态数据类型，会根据存入值自动判断。

SQLite具有以下五种数据类型：

1）NULL：空值。
2）INTEGER：带符号的整型，具体取决有存入数字的范围大小。
3）REAL：浮点数字，存储为8-byte，IEEE浮点数。
4）TEXT：字符串文本。
5）BLOB：二进制对象。

但实际上，SQLite3也接受如下的数据类型：

1）smallint：16位元的整数。
2）interger：32位元的整数。
3）decimal(p,s)：p精确值和s大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0。
4）float：32位元的实数。
5）double：64位元的实数。
6）char(n)：n长度的字串，n不能超过254。
7）varchar(n)：长度不固定且其最大长度为n的字串，n不能超过 4000。
8）graphic(n)：和char(n)一样，不过其单位是两个字元double-bytes，n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
9）vargraphic(n)：可变长度且其最大长度为n的双字元字串，n不能超过 2000
10）date：包含了年份、月份、日期。
11）time：包含了小时、分钟、秒。
12）timestamp：包含了年、月、日、时、分、秒、千分之一秒。
13）datetime：包含日期时间格式，必须写成'2010-08-05'不能写为'2010-8-5'，否则在读取时会产生错误。

SQL数据类型描述

CREATE TABLE ex2(
VARCHAR(10),
NVARCHAR(15),
TEXT,
INTEGER,
FLOAT,
BOOLEAN,
CLOB,
BLOB,
TIMESTAMP,
NUMERIC(10,5),
VARYING CHARACTER (24),
NATIONAL VARYING CHARACTER(16)
14);

char、varchar、text和nchar、nvarchar、ntext的区别

1、CHAR。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。
2、VARCHAR。存储变长数据，但存储效率没有CHAR高。如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。为什么“+1”呢？这一个字节用于保存实际使用了多大的长度。从空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。
3、TEXT。text存储可变长度的非Unicode数据，最大长度为2^31-1(2,147,483,647)个字符。
4、NCHAR、NVARCHAR、NTEXT。这三种从名字上看比前面三种多了个“N”。它表示存储的是Unicode数据类型的字符。我们知道字符中，英文字符只需要一个字节存储就足够了，但汉字众多，需要两个字节存储，英文与汉字同时存在时容易造成混乱，Unicode字符集就是为了解决字符集这种不兼容的问题而产生的，它所有的字符都用两个字节表示，即英文字符也是用两个字节表示。nchar、nvarchar的长度是在1到4000之间。和char、varchar比较起来，nchar、nvarchar则最多存储4000个字符，不论是英文还是汉字；而char、varchar最多能存储8000个英文，4000个汉字。可以看出使用nchar、nvarchar数据类型时不用担心输入的字符是英文还是汉字，较为方便，但在存储英文时数量上有些损失。

所以一般来说，如果含有中文字符，用nchar/nvarchar，如果纯英文和数字，用char/varchar。

其他资料

1) 如何创建自增字段？ (2) SQLite 支持哪些数据类型？ (3) 为什么能向 SQLite 数据库的整型字段中插入字符串？ (4) 为什么 SQLite 认为表达式 '0'=='00' 为真？ (5) 为什么 SQLite 不允许在同一张表里使用 '0' 和 '0.0' 作为两个不同的行的主键？ (6) 为什么不能在 Linux box 中读取在 SparcStation 中创建的 SQLite 数据库？ (7) 多个应用程序或者同一个应用程序的多个例程能同时存取同一个数据库文件吗？ (8) Is SQLite threadsafe? (9) 如何列出一个 SQLite 数据库中的所有的表/索引？ (10) 有对 SQLite 数据库的任何已知的大小的限制吗？ (11) 在 SQLite 中 VARCHAR 的最大长度是多少？ (12) SQLite 是否支持 BLOB 类型？ (13) 如何从一个已存在的 SQLite 数据表中添加/删除字段？ (14) 我删除了很多数据但是数据库文件并没有减小，是不是 Bug？ (15) 是否能将 SQLite 用于商业用途而不用交版税？ -------------------------------------------------------------------------------- (1) 如何创建自增字段？ 简单的回答：一个声明为 INTEGER PRIMARY KEY 的字段将自动增加。 这里是详细的答案： 从 SQLite 的 2.3.4 版本开始，如果你将一个表中的一个字段声明为 INTEGER PRIMARY KEY，那么无论你何时向该表的该字段插入一个 NULL 值，这个 NULL 值将自动被更换为比表中该字段所有行的最大值大 1 的整数；如果表为空，那么将被更换为 1。比如，假设你有这样的一张数据表: CREATE TABLE t1( a INTEGER PRIMARY KEY,b INTEGER ); 在这张数据表里，声明 INSERT INTO t1 valueS(NULL,123); 在逻辑意义上等价于： INSERT INTO t1 valueS((SELECT max(a) FROM t1)+1,123); 至于 SQLite 的 2.2.0 到 2.3.3 版本，如果你向一个 INTEGER PRIMARY KEY 字段插入 NULL 值,它将被替换为一个唯一的整数，但它是个半随机的整数。通过这种方式产生的唯一键将不是连续的。而 SQLite 的 2.3.4 及其以后版本，唯一键将是连续的直到其最大值超过 2147483647。这是 32 位整型的最大值因而不能继续增加。因此在 SQLite 的 2.3.3 及其早期版本，并发的插入尝试将返回一个半随机的键生成算法。 从 2.2.3 版本开始，一个新的名为 sqlite_last_insert_rowid() 的 API 函数将为最近的插入操作返回一个整型键。详细资料请查看 API 手册 -------------------------------------------------------------------------------- (2) SQLite 支持哪些数据类型？ SQLite 是弱类型的。所有数据以无终止的字符串存储。在 CREATE TABLE 中，字段名后面的数据类型信息将被忽略（大部分），你可以往任何字段插入你想要的数据类型，而不用管那个字段被声明为什么类型。 这个规则的一个例外是字段为 INTEGER PRIMARY KEY 类型。这样的字段强制一个整型。往 INTEGER PRIMARY KEY 字段插入任何非整型数据将产生一个错误。 这是一个在 SQLite 的数据类型 中更深入解释这一概念的页面。 -------------------------------------------------------------------------------- (3) 为什么能向 SQLite 数据库的整型字段中插入字符串？ 这是一个特点,不是错误。SQLite 是弱类型的。任何数据都能够插入任何字段中。你可以往整型字段中插入任意长度的字符串，或者往布尔字段中插入浮点数，或者往字符字段中插入日期。在 CREATE TABLE 命令中你指定给这个字段的数据类型不会限制插入这个字段的数据。所有的字段可以插入任意长度的字符串。（有一个例外：以 INTEGER PRIMARY KEY 为类型的字段只允许整数。如果你尝试往一个 INTEGER PRIMARY KEY 字段插入非整型数据，将产生一个错误。） 然而数据类型是影响值的比较的。因为在一个数字类型（比如 "integer"）字段中，任何字符串在进行比较和排序时是被看成处理成数值的数字。考虑这两个命令序列： CREATE TABLE t1(a INTEGER UNIQUE); CREATE TABLE t2(b TEXT UNIQUE); INSERT INTO t1 valueS('0'); INSERT INTO t2 valueS(0); INSERT INTO t1 valueS('0.0'); INSERT INTO t2 valueS(0.0); 在左边的序列中，第二条插入语句将失败。这种情况下，当字符串 '0' 和 '0.0' 插入一个数字类型字段中时将被处理成数字而 0==0.0 违反了唯一性约束。但是右边序列中的第二条插入语句正常执行。这种情况下，常量 0 和 0.0 被处理成字符串意味着它们是截然不同的。 这是一个在 SQLite 的数据类型 中更深入解释这一概念的页面。 -------------------------------------------------------------------------------- (4) 为什么 SQLite 认为表达式 '0'=='00' 为真？ 在 2.7.0 之后，表达式不成立。 但是如果两个值之一在一个数字类型字段中比较，另一个将被转化为数字而不是字符串，而且结果成立。例如： CREATE TABLE t3(a INTEGER,b TEXT); INSERT INTO t3 valueS(0,0); SELECT count(*) FROM t3 WHERE a=='00'; 上面序列中的 SELECT 语句返回 1。字段 "a" 是数字类型所以在 WHERE 子句中字符串 '00' 被转换成一个数字作为和 'a' 的比较。0==00 所以这个测试返回 TRUE。现在考虑一个不同的 SELECT 语句： SELECT count(*) FROM t3 WHERE b=='00'; 在这种情况下答案是 0。'b' 是一个 text 字段，文本不匹配 '00'。'0'!='00' 因此 WHERE 子句返回 FALSE 而返回行数为 0。 这是一个在 SQLite 的数据类型 中更深入解释这一概念的页面。 -------------------------------------------------------------------------------- (5) 为什么 SQLite 不允许在同一张表里使用 '0' 和 '0.0' 作为两个不同的行的主键？ 你的主键必定是数字类型。将数据类型改成 TEXT 后即可正常运行。 每一行必须有一个唯一的主键。作为一个数字类型的字段，SQLite 认为 '0' 和 '0.0' 的值是相同的，因为他们在数字上的比较是相等的（看前面的问题）因此值不是唯一的。 -------------------------------------------------------------------------------- (6) 为什么不能在 Linux box 中读取在 SparcStation 中创建的 SQLite 数据库？ 你需要升级你的 SQLite 库到 2.6.3 或更新版本。 你的 linux box 上的 x86 处理器是 little-endian （意味着整数中的最低位的字节排在前面），但是 Sparc 是 big-endian （最高位的字节排在前面）。在 SQLite 低于 2.6.3 版本的时候，创建于 little-endian 体系中的 SQLite 数据库不能运行于 big-endian 的机器上。从 2.6.3 版本开始，SQLite 应该能够读写数据库文件而不管创建数据库的机器的字节顺序如何。 -------------------------------------------------------------------------------- (7) 多个应用程序或者同一个应用程序的多个例程能同时存取同一个数据库文件吗？ 多个程序能够在同一时间打开同一个数据库，能够同时执行 SELECT 命令。但是一次只能有一个进程改变数据库。 Win95/98/ME 操作系统缺乏读/写锁定支持。在 2.7.0 版本之前，这意味着在 Windows 下你一次只能对数据库做单一的读处理。这个问题在 2.7.0 版本中已经通过一个 Windows 下的用户空间读写概率统计策略（implementing a user-space probabilistic reader/writer locking strategy）Windows 现在和 UNIX 一样允许多重的读操作。 The locking mechanism used to control simultaneous access might not work correctly if the database file is kept on an NFS filesystem. This is because file locking is broken on some NFS implementations. You should avoid putting SQLite database files on NFS if multiple processes might try to access the file at the same time. On Windows,Microsoft's documentation says that locking may not work under FAT filesystems if you are not running the Share.exe daemon. People who have a lot of experience with Windows tell me that file locking of network files is very buggy and is not dependable. If what they say is true,sharing an SQLite ourse-grained. SQLite locks the entire database. Big database servers (PostgreSQL,Oracle,etc.) generally have finer grained locking,such as locking on a single table or a single row within a table. If you have a massively parallel database application,you should consider using a big database server instead of SQLite. database between two or more Windows machines might cause unexpected problems. Locking in SQLite is very c When SQLite tries to access a file that is locked by another process,the default behavior is to return SQLITE_BUSY. You can adjust this behavior from C code using the sqlite_busy_handler() or sqlite_busy_timeout() API functions. See the API documentation for details. If two or more processes have the same database open and one process creates a new table or index,the other processes might not be able to see the new table right away. You might have to get the other processes to close and reopen their connection to the database before they will be able to see the new table. -------------------------------------------------------------------------------- (8) Is SQLite threadsafe? Yes. Sometimes. In order to be thread-safe,SQLite must be compiled with the THREADSAFE preprocessor macro set to 1. In the default distribution,the windows binaries are compiled to be threadsafe but the linux binaries are not. If you want to change this,you'll have to recompile. "Threadsafe" in the previous paragraph means that two or more threads can run SQLite at the same time on different "sqlite" structures returned from separate calls to sqlite_open(). It is never safe to use the same sqlite structure pointer simultaneously in two or more threads. Note that if two or more threads have the same database open and one thread creates a new table or index,the other threads might not be able to see the new table right away. You might have to get the other threads to close and reopen their connection to the database before they will be able to see the new table. Under UNIX,you should not carry an open SQLite database across a fork() system call into the child process. Problems will result if you do. -------------------------------------------------------------------------------- (9) 如何列出一个 SQLite 数据库中的所有的表/索引？ If you are running the sqlite command-line access program you can type ".tables" to get a list of all tables. Or you can type ".schema" to see the complete database schema including all tables and indices. Either of these commands can be followed by a LIKE pattern that will restrict the tables that are displayed. From within a C/C++ program (or a script using Tcl/Ruby/Perl/Python bindings) you can get access to table and index names by doing a SELECT on a special table named "SQLITE_MASTER". Every SQLite database has an SQLITE_MASTER table that defines the schema for the database. The SQLITE_MASTER table looks like this: CREATE TABLE sqlite_master ( type TEXT,name TEXT,tbl_name TEXT,rootpage INTEGER,sql TEXT ); For tables,the type field will always be 'table' and the name field will be the name of the table. So to get a list of all tables in the database,use the following SELECT command: SELECT name FROM sqlite_master WHERE type='table' ORDER BY name; For indices,type is equal to 'index',name is the name of the index and tbl_name is the name of the table to which the index belongs. For both tables and indices,the sql field is the text of the original CREATE TABLE or CREATE INDEX statement that created the table or index. For automatically created indices (used to implement the PRIMARY KEY or UNIQUE constraints) the sql field is NULL. The SQLITE_MASTER table is read-only. You cannot change this table using UPDATE,INSERT,or DELETE. The table is automatically updated by CREATE TABLE,CREATE INDEX,DROP TABLE,and DROP INDEX commands. Temporary tables do not appear in the SQLITE_MASTER table. Temporary tables and their indices and triggers occur in another special table named SQLITE_TEMP_MASTER. SQLITE_TEMP_MASTER works just like SQLITE_MASTER except that it is only visible to the application that created the temporary tables. To get a list of all tables,both permanent and temporary,one can use a command similar to the following: SELECT name FROM (SELECT * FROM sqlite_master UNION ALL SELECT * FROM sqlite_temp_master) WHERE type='table' ORDER BY name -------------------------------------------------------------------------------- (10) 有对 SQLite 数据库的任何已知的大小的限制吗？ As of version 2.7.4,SQLite can handle databases up to 2^41 bytes (2 terabytes) in size on both Windows and Unix. Older version of SQLite were limited to databases of 2^31 bytes (2 gigabytes). SQLite arbitrarily limits the amount of data in one row to 1 megabyte. There is a single #define in the source code that can be changed to raise this limit as high as 16 megabytes if desired. There is a theoretical limit of about 2^32 (4 billion) rows in a single table,but this limit has never been tested. There is also a theoretical limit of about 2^32 tables and indices. The name and "CREATE TABLE" statement for a table must fit entirely within a 1-megabyte row of the SQLITE_MASTER table. Other than this,there are no constraints on the length of the name of a table,or on the number of columns,etc. Indices are similarly unconstrained. The names of tables,indices,view,triggers,and columns can be as long as desired. However,the names of SQL functions (as created by the sqlite_create_function() API) may not exceed 255 characters in length. -------------------------------------------------------------------------------- (11) 在 SQLite 中 VARCHAR 的最大长度是多少？ Remember,SQLite is typeless. A VARCHAR column can hold as much data as any other column. The total amount of data in a single row of the database is limited to 1 megabyte. You can increase this limit to 16 megabytes,if you need to,by adjusting a single #define in the source tree and recompiling. For maximum speed and space efficiency,you should try to keep the amount of data in a single row below about 230 bytes. -------------------------------------------------------------------------------- (12) SQLite 是否支持 BLOB 类型？ You can declare a table column to be of type "BLOB" but it will still only store null-terminated strings. This is because the only way to insert information into an SQLite database is using an INSERT SQL statement,and you can not include binary data in the middle of the ASCII text string of an INSERT statement. SQLite is 8-bit clean with regard to the data it stores as long as the data does not contain any '/000' characters. If you want to store binary data,consider encoding your data in such a way that it contains no NUL characters and inserting it that way. You might use URL-style encoding: encode NUL as "%00" and "%" as "%25". Or,you might consider encoding your binary data using base-64. There is a source file named "src/encode.c" in the SQLite distribution that contains implementations of functions named "sqlite_encode_binary() and sqlite_decode_binary() that can be used for converting binary data to ASCII and back again,if you like. -------------------------------------------------------------------------------- (13) 如何从一个已存在的 SQLite 数据表中添加/删除字段？ SQLite does not support the "ALTER TABLE" SQL command. If you what to change the structure of a table,you have to recreate the table. You can save existing data to a temporary table,drop the old table,create the new table,then copy the data back in from the temporary table. For example,suppose you have a table named "t1" with columns names "a","b",and "c" and that you want to delete column "c" from this table. The following steps illustrate how this could be done: 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; -------------------------------------------------------------------------------- (14) 我删除了很多数据但是数据库文件并没有减小，是不是 Bug？ 不是的。当你从 SQLite 删除数据之后，未使用的磁盘空间被添加到一个内在的“空闲列表”中用于存储你下次插入的数据。磁盘空间并没有丢失，但是也不向操作系统返回磁盘空间。 如果你删除了大量的数据且想要减小数据库文件，执行 VACUUM 命令（2.8.1 或更新版本)。VACUUM 命令将重建数据库will reconstruct the database from scratch. This will leave the database with an empty free-list and a file that is minimal in size. Note,however,that the VACUUM can take some time to run (around a half second per megabyte on the Linux box where SQLite is developed) and it can use up to twice as much temporary disk space as the original file while it is running.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!