📄 readme_sqlite_tutorial.html
字号:
<html>
<head>
<style type="text/css">
<!--
pre {
white-space: pre-wrap;
white-space: -moz-pre-wrap;
white-space: -o-pre-wrap;
white-space: -pre-wrap;
}
pre {
font-size: 12px;
background-color: #ddd;
}
-->
</style>
</head>
<body>
<table width="80%" border="0" cellspacing="5" cellpadding="5">
<tr><td>
<p>
<h1>SQLite Tutorial</h1>
<p></p>
<p>
Copyright (c) 2004 by Mike Chirico mchirico@users.sourceforge.net.<br>
This material may be distributed only subject to the terms and conditions set forth in the
Open Publication <a href=http://www.opencontent.org/openpub/>License</a> v1.0, 8 June 1999 or later <br>
Download: <a href="http://prdownloads.sourceforge.net/souptonuts/README_sqlite_tutorial.html?download">
http://prdownloads.sourceforge.net/souptonuts/README_sqlite_tutorial.html?download</a><br>
<br>
Updated: Thu Nov 25 11:25:31 EST 2004
</p>
<p>
This article explores the power and simplicity of sqlite3, first by starting
with common commands and triggers, then the attach statement
with the union operation is introduced in a way that allows multiple tables,
in separate databases, to be combined as one virtual table, without the overhead
of copying or moving data. Next, the simple sign function and the amazingly
powerful trick of using this function in SQL select statements to solve complex queries
with a single pass through the data is demonstrated, after making a brief
mathematical case for how the sign function defines the absolute value and
IF conditions.
</p>
<p>
Although the sign function currently does not exist in
sqlite3, it is very easy to create in the "/src/func.c" file so that this function
will be permanently available to all sqlite applications. Normally, user functions
are created in C, Perl, or C++ which is also documented in this article. sqlite3
has the ability to store "blob", binary data. The sample program in the download
"eatblob.c" reads a binary file of any size into memory and stores the data into
a user specified field.
</p>
<p>
All examples can be found in
<a href=http://prdownloads.sourceforge.net/souptonuts/sqlite_examples.tar.gz?download>
sqlite_examples.tar.gz </a>, and I would encourage you to download and run these
examples as you read through this document.
</p>
<p> The homepage for sqlite3 is www.sqlite.org and the source for sqlite3 can
be downloaded at <a href=http://www.sqlite.org/download.html> www.sqlite.org/download.htm.</a>
This tutorial was done with the source version 3.0.8 </p>
<p>
<h3>Getting Started - Common Commands</h3>
<p></p>
<p>
To create a database file, run the command "sqlite3" followed by the
database name. For example, to create the database "test.db"
run the sqlite3 command as follows:
</p>
<pre>
$ sqlite3 test.db
SQLite version 3.0.8
Enter ".help" for instructions
sqlite> .quit
$
</pre>
<p>
The database file test.db will be created, if it does not already
exist. Running this command will leave you in the sqlite3 environment.
There are 3 ways to safely exit this environment (.q, .quit, or .exit).
</p>
<p>
You do not have to enter the sqlite3 interactive environent.
Instead, you could perform all commands at the shell prompt, which
is ideal when running bash scripts and comands in an ssh string. Below
is an example of how you would create a simple table from the
command prompt.
</p>
<pre>
$ sqlite3 test.db "create table t1 (t1key INTEGER
PRIMARY KEY,data TEXT,num double,timeEnter DATE);"
</pre>
<p>
After table t1 has been created, data can be inserted
as follows:
</p>
<pre>
$ sqlite3 test.db "insert into t1 (data,num) values ('This is sample data',3);"
$ sqlite3 test.db "insert into t1 (data,num) values ('More sample data',6);"
$ sqlite3 test.db "insert into t1 (data,num) values ('And a little more',9);"
</pre>
<p>
As expected, doing a select returns the data in the table. Note, the primary key "t1key"
auto increments; however, there are no default values for timeEnter.
To populate the timeEnter field with the time, an update trigger is needed. An important
note on the PRIMARY KET: do not use the abbreviated "INT" when working with the PRIMARY KEY.
You must use "INTEGER", for the primary key to update.
</p>
<pre>
$ sqlite3 test.db "select * from t1 limit 2";
1|This is sample data|3|
2|More sample data|6|
</pre>
<p>
In the statement above, the limit clause is used and only 2 rows
are displayed. For a quick reference of SQL syntax statements avaliable
with SQLite, see the link <a href=http://www.sqlite.org/lang.html>syntax</a>.
There is an offset option to the limit clause. For instance, the third row is
equal to the following: "limit 1 offset 2".
</p>
<pre>
$ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2";
3|And a little more|9|
</pre>
<p>
The ".table" command shows the table names. For a more comprehensive list
of tables, triggers, and indexes created in the database, query the
master table "sqlite_master" as shown below.
</p>
<pre>
$ sqlite3 test.db ".table"
t1
$ sqlite3 test.db "select * from sqlite_master"
table|t1|t1|2|CREATE TABLE t1 (t1key INTEGER
PRIMARY KEY,data TEXT,num double,timeEnter DATE)
</pre>
<p>
All SQL information and data inserted into a database can be extracted
with the ".dump" command.
</p>
<pre>
$ sqlite3 test.db ".dump"
BEGIN TRANSACTION;
CREATE TABLE t1 (t1key INTEGER
PRIMARY KEY,data TEXT,num double,timeEnter DATE);
INSERT INTO "t1" VALUES(1, 'This is sample data', 3, NULL);
INSERT INTO "t1" VALUES(2, 'More sample data', 6, NULL);
INSERT INTO "t1" VALUES(3, 'And a little more', 9, NULL);
COMMIT;
</pre>
<p>
The contents of the ".dump" can be filtered and piped to another
database. Below table t1 is changed to t2 with the sed command,
and it is piped into the test2.db database.
</p>
<pre>
$ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db
</pre>
<p>
<h3>Triggers</h3>
<p></p>
<p>
An insert trigger is created below in the file "trigger1". The Coordinated
Universal Time (UTC) will be entered into the field "timeEnter", and this
trigger will fire after a row has been inserted into the table t1. Again,
this trigger will fire after the row has been inserted.
</p>
<pre>
-- ********************************************************************
-- Creating a trigger for timeEnter
-- Run as follows:
-- $ sqlite3 test.db < trigger1
-- ********************************************************************
CREATE TRIGGER insert_t1_timeEnter AFTER INSERT ON t1
BEGIN
UPDATE t1 SET timeEnter = DATETIME('NOW') WHERE rowid = new.rowid;
END;
-- ********************************************************************
</pre>
<p>
The AFTER specification in
..."insert_t1_timeEnter AFTER..." is necessary. Without the AFTER keyword, the rowid
would not have been generated. This is a common source of
errors with triggers, since AFTER is NOT the default, so it must
be specified. In summary, if your trigger depends on newly created data,
in any of the fields from the created row, which was the case for us in this
example since we need the rowid, then, the AFTER specification is needed. Otherwise,
the trigger is a BEFORE trigger, and will fire before rowid or other pertinent
data is entered into the field.
</p>
<p>
Comments are preceeded by "--". If this script was created in the
file "trigger1", you could easily execute this script
as follows.
</p>
<pre>
$ sqlite3 test.db < trigger1
</pre>
<p>
Now try entering a new record as before, and you should see the
time in the field timeEnter.
</p>
<pre>
sqlite3 test.db "insert into t1 (data,num) values ('First entry with timeEnter',19);"
</pre>
<p>
Doing a select reveals the following data:
</p>
<pre>
$ sqlite3 test.db "select * from t1";
1|This is sample data|3|
2|More sample data|6|
3|And a little more|9|
4|First entry with timeEnter|19|2004-10-02 15:12:19
</pre>
<p>
If you look at the statement above, the last value has the timeEnter filled in
automatically with Coordinated Universal Time - or (UTC). If you want localtime,
then, use select datetime('now','localtime'). See the note at the end of
this section regarding UTC and localtime.
</p>
<p>
For examples that follow the table "exam" and the database
"examScript" will be used. The table and trigger are defined
below. Just like the trigger above, UTC time will be used.
</p>
<pre>
-- *******************************************************************
-- examScript: Script for creating exam table
-- Usage:
-- $ sqlite3 examdatabase < examScript
--
-- Note: The trigger insert_exam_timeEnter
-- updates timeEnter in exam
-- *******************************************************************
-- *******************************************************************
CREATE TABLE exam (ekey INTEGER PRIMARY KEY,
fn VARCHAR(15),
ln VARCHAR(30),
exam INTEGER,
score DOUBLE,
timeEnter DATE);
CREATE TRIGGER insert_exam_timeEnter AFTER INSERT ON exam
BEGIN
UPDATE exam SET timeEnter = DATETIME('NOW')
WHERE rowid = new.rowid;
END;
-- *******************************************************************
-- *******************************************************************
</pre>
<p>
After the script file, it can be executed, by redirecting the contents
of the script file into the sqlite3 command,
followed by the database name. See the example below:
</p>
<pre>
$ sqlite3 examdatabase < examScript
$ sqlite3 examdatabase "insert into exam (ln,fn,exam,score)
values ('Anderson','Bob',1,75)"
$ sqlite3 examdatabase "select * from exam"
1|Bob|Anderson|1|75|2004-10-02 15:25:00
</pre>
<p>
And, as a check, the PRIMARY KEY and current UTC time
have been updated correctly, as seen from the above example.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -