sqlite 指南pdf

[转]SQLite基本语法手册SQLite是一个软件库，用于实现自包含、非服务式、零配置、事务化的SQL数据库引擎。

SQLite是一个嵌入式SQL数据库引擎，与其它大多数SQL数据库不同的是，SQLite没有独立的服务进程。

SQLite直接读写原始的磁盘文件，一个拥有多个表、索引、触发器和视图的完整SQL数据库就包含在一个独立的磁盘文件中。

一.结构定义1.CREATE TABLE:创建新表。

语法：sql-command ::=CREATE[TEMP | TEMPORARY]TABLE table-name (column-def [, column-def]*[, constraint]*)sql-command ::=CREATE[TEMP | TEMPORARY]TABLE[database-name.]table-name AS select-statem entcolumn-def ::= name [type][[CONSTRAINT name]column-constraint]*type ::= typename |typename ( number ) |typename ( number , number )column-constraint ::=NOT NULL[ conflict-clause ]|PRIMARY KEY[sort-order][ conflict-clause ]|UNIQUE[ conflict-clause ]|CHECK ( expr ) [ conflict-clause ]|DEFAULT value |COLLATE collation-nameconstraint ::=PRIMARY KEY ( column-list ) [ conflict-clause ]|UNIQUE ( column-list ) [ conflict-clause ]|CHECK ( expr ) [ conflict-clause ]conflict-clause ::=ON CONFLICT conflict-algorithm2.CREATE VIEW:创建一个视图（虚拟表），该表以另一种方式表示一个或多个表中的数据。

sqlite手册SQLite是一种嵌入式关系型数据库管理系统，它被广泛应用于移动设备和小型应用程序中。

本手册将介绍如何创建和管理SQLite 数据库以及使用SQL命令进行数据查询和操作。

1. 安装SQLite在开始使用SQLite之前，您需要先安装SQLite。

您可以通过以下方式安装SQLite：- 下载SQLite二进制文件并安装- 使用包管理器（如apt、yum）安装SQLite- 使用SQLite源代码进行编译和安装2. 创建数据库要创建一个新的SQLite数据库，您可以使用以下命令：```sqlite3 <database_name>.db```这将创建一个名为`<database_name>.db`的新数据库，如果数据库不存在，则会自动创建它。

3. 创建表要在SQLite数据库中创建表，请使用以下命令：```CREATE TABLE <table_name> (<column_name> <data_type>,<column_name> <data_type>,...);```这将创建一个名为`<table_name>`的新表，并为每个列指定名称和数据类型。

4. 插入数据要向SQLite表中插入数据，请使用以下命令：INSERT INTO <table_name> (<column_name1>, <column_name2>, ...) VALUES (<value1>, <value2>, ...);```这将向`<table_name>`中插入新行，并为每个指定列指定值。

5. 查询数据要从SQLite表中查询数据，请使用以下命令：```SELECT <column_name1>, <column_name2>, ... FROM <table_name> WHERE <condition>;```这将从`<table_name>`中选择指定列，并根据指定的条件筛选行。

sqlite 入门至精通sqlite入门至精通2019年04月09日星期五19：07关键字：sqlite入门至精通菜鸟入门1。

从下载SQLite 3.3.4的版本为了方便，我把它解压了，就一个SQLite3.exe，放入Windows目录下。

Cmd进入命令行1)创建数据库文件：SQLite3 d：\test.db回车就生成了一个test.db在d盘。

这样同时也SQLite3挂上了这个test.db 2)用.help可以看看有什么命令.help回车即可3)可以在这里直接输入SQL语句创建表格用；结束，然后回车就可以看到了4)看看有创建了多少表.tables 5)看表结构.schema表名6)看看目前的数据库.database 7)如果要把查询输出到文件.output文件名查询语句；查询结果就输出到了文件c：\query.txt把查询结果用屏幕输出.output stdout 8)把表结构输出，同时索引也会输出.dump表名9)退出.exit或者.quit 2。

从.phxsoftware/下载Ado驱动。

下载了安装，在安装目录中存在System.Data.SQLite.dll我们只需要拷贝这个文件到引用目录，并添加引用即可对SQLite数据库操作了所有的Ado对象都是以SQLite开头的，比如SQLiteConnection连接串只需要如下方式Data Source=d：\test.db或者DataSource=test.db--应用在和应用程序或者能够自动找到的目录剩下的就很简单了~~3。

SQL语法由于以前用SQLServer或者ISeries，所以DDL的语法很汗颜1)创建一个单个Primary Key的table CREATETABLE[Admin]([UserName][nvarchar](20)PRIMARY KEY NOTNULL,[Password][nvarchar](50)NOT NULL,[Rank][smallint]NOTNULL,[MailServer][nvarchar](50)NOT NULL,[MailUser][nvarchar](50)NOT NULL,[MailPassword][nvarchar](50)NOT NULL,[Mail][nvarchar](50)NOT NULL)；2)创建一个多个Primary Key的table CREATETABLE[CodeDetail]([CdType][nvarchar](10)NOTNULL,[CdCode][nvarchar](20)NOT NULL,[CdString1][ntext]NOTNULL,[CdString2][ntext]NOT NULL,[CdString3][ntext]NOT NULL,PRIMARY KEY(CdType,CdCode))；3)创建索引CREATEINDEX[IX_Account]ON[Account]([IsCheck],[UserName])；还可以视图等等。

SQLite学习手册内容收集自网络整理:zhoushuangsheng@新浪微博:@_Nicky开篇一、简介：SQLite是目前最流行的开源嵌入式数据库，和很多其他嵌入式存储引擎相比(NoSQL)，如BerkeleyDB、MemBASE等，SQLite可以很好的支持关系型数据库所具备的一些基本特征，如标准SQL语法、事务、数据表和索引等。

事实上，尽管SQLite拥有诸多关系型数据库的基本特征，然而由于应用场景的不同，它们之间并没有更多的可比性。

下面我们将列举一下SQLite的主要特征：1. 管理简单，甚至可以认为无需管理。

2. 操作方便，SQLite生成的数据库文件可以在各个平台无缝移植。

3. 可以非常方便的以多种形式嵌入到其他应用程序中，如静态库、动态库等。

4. 易于维护。

综上所述，SQLite的主要优势在于灵巧、快速和可靠性高。

SQLite的设计者们为了达到这一目标，在功能上作出了很多关键性的取舍，与此同时，也失去了一些对RDBMS关键性功能的支持，如高并发、细粒度访问控制(如行级锁)、丰富的内置函数、存储过程和复杂的SQL语句等。

正是因为这些功能的牺牲才换来了简单，而简单又换来了高效性和高可靠性。

二、SQLite的主要优点：1. 一致性的文件格式：在SQLite的官方文档中是这样解释的，我们不要将SQLite与Oracle或PostgreSQL去比较，而是应该将它看做fopen和fwrite。

与我们自定义格式的数据文件相比，SQLite不仅提供了很好的移植性，如大端小端、32/64位等平台相关问题，而且还提供了数据访问的高效性，如基于某些信息建立索引，从而提高访问或排序该类数据的性能，SQLite提供的事务功能，也是在操作普通文件时无法有效保证的。

2. 在嵌入式或移动设备上的应用：由于SQLite在运行时占用的资源较少，而且无需任何管理开销，因此对于PDA、智能手机等移动设备来说，SQLite的优势毋庸置疑。

INTEGER PRIMARY KEY数据存储2010-06-1813:38:56阅读46评论0字号：大中小订阅.Sqlite中INTEGER PRIMARY KEY AUTOINCREMENT和rowid/INTEGER PRIMARY KEY的使用在用sqlite设计表时，每个表都有一个自己的整形id值作为主键，插入后能不能直接得到该主键呢？还有可不可以指定这么一个id值，因为sqlite内部本来就会为每个表加上一个rowid，这个rowid可以当成一个隐含的字段使用，但是由sqlite引擎来维护的，在3.0以前rowid是32位的整数，3.0以后是64位的整数，为什么不直接使用这个内部的rowid作为每个表的id主键呢。

查了下文档。

参照/c3ref/last_insert_rowid.html。

Each entry in an SQLite table has a unique64-bit signed integer key called the"rowid".The rowid is always available as an undeclared column named ROWID,OID,or_ROWID_as long as those names are not also used by explicitly declared columns.If the table has a column of type INTEGER PRIMARY KEY then that column is another alias for the rowid.如果表中有个INTEGER PRIMARY KEY字段，那么它只是rowid的别名。

This routine returns the rowid of the most recent successful INSERT into the database from the database connection in the first argument.If no successful INSERTs have ever occurred on that database connection,zero is returned.如果成功插入一条数据，会返回刚刚插入的数据的rowid.如果失败返回0.Android中如果发生错误返回的是-1参照/faq.htmlShort answer:A column declared INTEGER PRIMARY KEY will autoincrement.Here is the long answer:If you declare a column of a table to be INTEGER PRIMARY KEY,then whenever you insert a NULL into that column of the table,the NULL is automatically converted into an integer which is one greater than the largest value of that column overall other rows in the table,or1if the table is empty.(If the largest possible integer key, 9223372036854775807,then an unused key value is chosen at random.)For example,suppose you have a table like this:CREATE TABLE t1(a INTEGER PRIMARY KEY,b INTEGER);With this table,the statementINSERT INTO t1VALUES(NULL,123);is logically equivalent to saying:INSERT INTO t1VALUES((SELECT max(a)FROM t1)+1,123);There is a function named sqlite3_last_insert_rowid()which will return the integer key for the most recent insert operation.Note that the integer key is one greater than the largest key that was in the table just prior to the insert.The new key will be unique over all keys currently in the table,but it might overlap with keys that have been previously deleted from the table.To create keys that are unique over the lifetime of the table,add the AUTOINCREMENT keyword to the INTEGER PRIMARY KEY declaration.Then the key chosen will be one more than than the largest key that has ever existed in that table.If the largest possible key has previously existed in that table,then the INSERT will fail with an SQLITE_FULL error code.把一个列申明为INTEGER PRIMARY KEY，那么在向它插入NULL,该列就由系统指定。

Compiling SQLite from SourceCompiling SQLite from source on POSIX systems follows very closely the MinGW instructionsgiven earlier for the Windows platform (actually it is more the other way around; MinGWinstallation apes Linux source installation!). To build SQLite on POSIX systems, you need toensure that you have the GNU Compiler Collection (GCC) installed, including Autoconf,Automake, and Libtool. Most of the systems already discussed include all of these by default.With this software in place, you can build SQLite by doing the following:1. Download the Linux/Unix SQLite tarball (source code) from the SQLite website. At thetime of this writing, the current version is sqlite-3.3.4.tar.gz. Place it in a directory(e.g., /tmp).2. Navigate to your build directory:cd /tmp3. Unpack the SQLite tarball:tar -xzvf sqlite-3.3.4.tar.gz4. Move into the unpacked directory:cd sqlite-3.3.45. Create the Makefile:./configure6. If you want to create a multithreaded shared library, run./configure -–enable-threads –disable-tcl –prefix=/home/temp 7. Other options, such as the installation directory, are also available. For a complete list ofconfigure options, run./configure --help8. Build the source:make9. As root, install:make installYou now have a functional SQLite installation on your system that includes both the SQLiteshared library and a dynamically linked CLP (which uses the SQLite shared library). If you haveGNU Readline installed on you system, the CLP should be compiled with Readline support.Test it out by running it from the command line:root@linux # sqlite3This will invoke the CLP using an in-memory database. Type .help for a list of shell commands.Type .exit to close the application, or press Ctrl+D.You can just as easily type .h for short. Many of the commands can be similarly abbreviated,such as .e—short for .exit—to exit the shell.Let’s start by creating a database that we will call test.db. From the command line, openthe CLP in shell mode by typing the following:sqlite3 test.dbEven though we have provided a database name, SQLite does not actually create the database (yet) if it doesn’t already exist. SQLite will defer creating the database until you actually create something inside it, such as a table or view. The reason for this is so that you have the opportunityto set various permanent database settings (such as page size) before the database structure is committed to disk. Some settings such as page size and character encoding (UTF-8, UTF-16, etc.) cannot be changed once the database is created, so this interim is where you have a chanceto specify them. We will go with the default settings here, so to actually create the database on disk, we need only to create a table. Issue the following statement from the shell:sqlite> create table test (id integer primary key, value text); Now you have a database file on disk called test.db, which contains one table called test. This table, as you can see, has two columns:• A primary key column called id, which has an autoincrement attribute. Wherever youdefine a column of type integer primary key, SQLite will apply an autoincrement functionfor the column. That is, if no value is provided for the column in an INSERT statement,SQLite will automatically generate one by finding the next integer value specific to thatcolumn.• A simple text field called value.Let’s add a few rows to the table:sqlite> insert into test (value) values('eenie');sqlite> insert into test (value) values('meenie');sqlite> insert into test (value) values('miny');sqlite> insert into test (value) values('mo');Now fetch them back:sqlite> .mode colsqlite> .headers onsqlite> SELECT * FROM test;id value---------- ----------1 eenie2 meenie3 miny4 moThe two commands preceding the SELECT statement (.headers and .mode) are used to improvethe formatting a little (both of which are covered later). We can see that SQLite provided sequential integer values for the id column, which we did not provide in the INSERT statements. While on the topic of autoincrement columns, you might be interested to know that the value of the last inserted autoincrement value can be obtained using the SQL function last_insert_rowid():sqlite> select last_insert_rowid();last_insert_rowid()-------------------4Before we quit, let’s add an index and a view to the database. These will come in handy inthe illustrations that follow:sqlite> create index test_idx on test (value);sqlite> create view schema as select * from sqlite_master; To exit the shell, issue the .exit command:sqlite> .exitC:\Temp>On Windows, you can also terminate the shell by using the key sequence Ctrl+C. On Unix, youcan use Ctrl+D.Getting Database Schema InformationThere are several shell commands for obtaining information about the contents of a database.You can retrieve a list of tables (and views) using .tables [pattern], where [pattern] can beany pattern that the SQL LIKE operator understands (we cover LIKE in Chapter 4 if you are unfamiliar with it). All tables and views matching the given pattern will be returned. If nopattern is supplied, all tables and views are returned:sqlite> .tablestest_idxHere we see the index we created earlier on test, called test_idx. The SQL definition or data definition language (DDL) for a table or view can be obtained using .schema [table name].If no table name is provided, the SQL definitions of all database objects (tables, indexes, views,and indexes) are returned:sqlite> .schema testCREATE TABLE test (id integer primary key, value text); CREATE INDEX test_idx on test (value);sqlite> .schemaCREATE TABLE test (id integer primary key, value text);CREATE VIEW schema as select * from sqlite_master;CREATE INDEX test_idx on test (value);1.创建一个data.txt文件id, name,gender, age1,dq,male,242,jz,female,273,pp,male,264,cj,male,285,zc,male,252. 创建一个数据库test.db和表employee。

SQLite3命令⾏操作指南.help查看帮助信息.backup ?DB? FILE备份数据库, ⽅法:.backup [main|...] filename, 数据库名可以通过.databases 命令得到,⼀般为main, 可以省略, filename为磁盘⽂件名..bail ON|OFF遇到错误时不再继续, 默认为OFF.databases列出附加到数据库的数据库和⽂件.dump ?TABLE? ...保存表到SQL格式的⽂件中, 没有指定表名, 则保存所有. 如果要保存到磁盘上需要结合 .output 命令..echo ON|OFF打开/关闭命令⾏回显.exit退出该命令⾏.explain ?ON|OFF?以合适的⽅式显⽰表头, 不带参数则为开启.header<s> ON;OFF是否显⽰表头, 和 .explain 差别不是很⼤.help显⽰帮助信息.import FILE TABLE从⽂件中导⼊表.indices ?TABLE?显⽰索引.load FILE ?ENTRY?加载⼀个扩展库.log FILE|off是否记录⽇志,⽂件可以是标准输出/输⼊.mode MODE ?TABLE?设置输出模式, 模式可以是以下⼏种:csv 以逗号分隔的值column 表头左对齐(参见 .width)html 显⽰ HTML 代码insert SQL插⼊语句line ⼀⾏⼀个值list 值⽤ string 分隔tabs 以 tab 分隔的值tcl TCL 列表元素.nullvalue STRING以 STRING 代替 NULL 值的输出.output FILENAME输出到⽂件, ⽽不是显⽰在屏幕上.output stdout输出到屏幕上.prompt MAIN CONTINUE替换默认的命令提⽰信息, 默认就是 sqlite> .quit退出命令⾏.read FILENAME执⾏ FILENAME 中的 SQL.restore ?DB? FILE从⽂件中还原数据到表, 默认表为 main.schema ?TABLE?显⽰ CREATE 语句.timeout MS在 MS 时间内尝试打开被锁定的表.vfsname ?AUX?显⽰ VFS 栈信息.width NUM1 NUM2 ...设置 column 模式中的列的宽度.timer ON|OFF显⽰CPU时间其它:参见 SQLITE3 官⽅命令⼿册⽐如 cacuum 可以释放磁盘空间...再其它:执⾏该 SQL 语句--End of File--。