Introduction to database : SQLite

bawltherapistΛογισμικό & κατασκευή λογ/κού

13 Δεκ 2013 (πριν από 3 χρόνια και 10 μήνες)

82 εμφανίσεις

Introduction to database : SQLite
Design for Inter
action and Mo
ving Image
INTRO
SQLite is a free and simple database system where the file is store locally
. It

s similar to
m
ySQL which store data on a serv
er (Flickr
, Y
outube, …). T
ry the following examples b
y
opening the terminal application (utilities folder) and t
ype
sqlite3
(
ctrl-d
to exit)
EXAMPLE - 1 (simple math)
SELECT 1+2;
// result : 3
SELECT ROUND(9.8);
// result : 10.0
SELECT CURRENT_TIMESTAMP;
// result : date and time
EXAMPLE - 2
CREATE TABLE tbl1(one char(10), two int);
// see also varchar and smallint
INSERT INTO tbl1 VALUES('hello!',10);
INSERT INTO tbl1 VALUES('goodbye', 20);
SELECT * from tbl1;
// show all data from tbl1
hello!|10
goodbye|20
drop table tbl1;
// erase table tbl1
EXAMPLE - 3
CREATE TABLE tbl_one(a smallint, b smallint,c smallint);
INSERT INTO tbl_one (a,b,c) VALUES (1,2,3);
INSERT INTO tbl_one (a,b,c) VALUES (4,5,6);
INSERT INTO tbl_one (a,b,c) VALUES (4,17,19);
INSERT INTO tbl_one (a,b,c) VALUES (42,8,72);
SELECT * FROM tbl_one;
// show all from tbl_one
SELECT a,c FROM tbl_one;
// show columns a, c
SELECT * FROM tbl_one LIMIT 2;
// show first two rows
SELECT * FROM tbl_one WHERE a=4;
// show every rows where a=4
SELECT * FROM tbl_one WHERE a=4 AND b=5;
SELECT * FROM tbl_one WHERE a=1 OR b=47;
SELECT * FROM tbl_one WHERE b >= 8;
// see also <, >, <>
SELECT c,b FROM tbl_one WHERE c IN (3, 19, 72);
// show c, b where c = 3, 19, 72
SELECT * FROM tbl_one ORDER BY b;
// show all and sort by b value
SELECT COUNT(a) FROM tbl_one;
// count how many item in column a
SELECT MIN(a),MAX(a), AVG(a), TOTAL(a) FROM tbl_one;
DELETE FROM tbl_one WHERE a=4;
// delete where a=4 (result : del row 2 and 4)
EXAMPLE - 4
CREATE TABLE tbl_two (
an_int INTEGER,
a_key INTEGER PRIMARY KEY,
a_char CHAR,
a_name VARCHAR(25),
a_text TEXT,
a
1
4
4
42
b
2
5
17
8
c
3
6
19
72
a_dec DECIMAL(4,1),
a_real REAL,
a_bool BOOLEAN,
a_bit BIT,
a_stamp TIMESTAMP,
a_xml XML
);
INSERT INTO tbl_two (a_name) VALUES (42);
INSERT INTO tbl_two (a_char) VALUES ('z');
INSERT INTO tbl_two (a_name) VALUES ('up to 25 characters');
INSERT INTO tbl_two (a_text) VALUES ('yada yada...');
INSERT INTO tbl_two (a_dec) VALUES (2.5);
INSERT INTO tbl_two (a_bool) VALUES ('TRUE');
INSERT INTO tbl_two (a_bool) VALUES ('FALSE');
INSERT INTO tbl_two (a_stamp) VALUES (CURRENT_TIMESTAMP);
INSERT INTO tbl_two (a_stamp) VALUES ('2009-03-15');
INSERT INTO tbl_two (a_stamp) VALUES ('9:02:15');
INSERT INTO tbl_two (a_stamp) VALUES ('2009-03-15 9:02:15');
INSERT INTO tbl_two (a_stamp) VALUES (datetime(1092941466, 'unixepoch'));
INSERT INTO tbl_two (a_stamp) VALUES (strftime('%Y-%m-%d %H:%M', '2009-03-15
14:02'));
SELECT * FROM tbl_two;
SELECT a_key,a_stamp from tbl_two WHERE a_stamp NOT NULL;
(search for v
alues where a_stamp is not nul and show a_k
ey and a_stamp)
EXAMPLE - 5
CREATE TABLE tbl_three (
a_key INTEGER PRIMARY KEY,
a_val REAL NOT NULL,
a_code INTEGER UNIQUE,
a_str TEXT DEFAULT 'I donno',
tbl_two INTEGER,
FOREIGN KEY(tbl_two) REFERENCES tbl_two(a_key)
);
INSERT INTO tbl_three (a_val,a_code,tbl_two) VALUES (11.2,2,1);
INSERT INTO tbl_three (a_val,a_code,tbl_two) VALUES (12.9,4,3);
INSERT INTO tbl_three (a_code) VALUES (2);
// error
INSERT INTO tbl_three (a_code) VALUES (3);
// error
SELECT * FROM tbl_three;
Join two a_code in table three together :
SELECT tbl_three.a_code,tbl_two.* FROM tbl_two,tbl_three WHERE tbl_two.a_key ==
tbl_three.tbl_two;
an_int
a_k
ey
1
2
3
4
5
5
6
7
a_char
z
a_name
42
up to
...
a_text
y
ada
a_dec
2.5
a_bool
TRUE
F
ALSE
a_stamp
date
+time
APPLICATIONS
Base (menial.co
.uk) is simple and inexpensiv
e (£10) SQLite browser/editorto start with.
Na
vicat is the professional standard, it

s therefore more expensiv
e ($79 for an EDU
license and $129 if not). There are a lot of *free* ja
v
a made applications but they are
not v
ery stable or easy to use in m
y opinion. With big database lik
e Mail or the Address
book, it

s much easier to test y
our queries inside an application or to ev
entually export
into another format (CSV
, XML, …).
MAIL
Y
ou can find the mail database as a SQLite document in
/Users/~/Library/Mail/
Envelope\ Index
(the
\
slash is necessary when y
ou open the db in the terminal).
Mak
e a backup and put it on y
our desktop
, y
ou also can rename it. Use the application
Base
to open it. Y
ou’ll find all of the addresses that y
ou ha
v
e used, the name of the
attachments, mailbo
x
es, messages status (but not the messages itself
), subjects, ...
Select SQL and type the following queries :
SELECT COUNT(*) FROM messages;
// how many messages ?
SELECT COUNT(*) FROM addresses;
// how many addresses ?
How many messages where you were the sender ?
SELECT COUNT(*) FROM messages WHERE messages.sender=1;
How many messages where sent per sender ?
SELECT messages.sender, COUNT(*) FROM messages GROUP BY messages.sender;
Create a table, group by senders and count :
CREATE TABLE senders (
sender INTEGER PRIMARY KEY,
count INTEGER
);
INSERT INTO senders SELECT messages.sender, COUNT(*) FROM messages GROUP BY
messages.sender;
ADDRESS BOOK
The address book database as a SQLite database in
/Users/~/Library/Application\
Support/AddressBook/AddressBook-v22.abcddb
SELECT * FROM ZABCDRECORD
SELECT * FROM ZABCDEMAILADDRESS
http://krypted.com/mac
-os-
x/querying-
address-bookapp-from-the-command-line/
TERMINAL
Y
ou can terminate the sqlite3 progr
am b
y t
yping y
our systems End-Of
-File char
acter
(usually a Control-D).
.mode list
: outpout as a list with | sepr
ator
other modes : line, column
.separator ", "
: change the separ
ator to comma
.separator "\t"
: change the separ
ator to tab
.width 12 6
: column width of 12 and 6 (default 10)
.header off :
removes the header
.mode insert new_table
: output is formatted as SQL INSERT
CONVER
T DA
T
ABA
SE T
O A
SCII
echo '.dump' | sqlite3 ex1 | gzip -c >ex1.dump.gz
CONVER
T BACK T
O DA
T
ABA
SE
$
zcat ex1.dump.gz | sqlite3 ex2
TYPE OF VARIABLES
TUTORIALS
http://www
.sqlite.org/sqlite.html
http://z
etcode.com/databases/sqlitetutorial/
http://souptonuts.sourceforge.net/readme_sqlite_tutorial.html
http://jena.sourceforge.net/ARQ/T
utorial/ (SP
ARQL tutorial)
NM 02/2011