SQLite Basics

Wed, 07/07/2010 - 02:34 | by jozsi

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file.

Basic SQLite commands:

1, Create table

CREATE TABLE testtable (id integer primary key, name text);

2, Insert into table

INSERT INTO "testtable" VALUES(1,'Joseph');

3, Create a view

CREATE VIEW testview AS select * from testtable;

4, Add index

CREATE INDEX testindex on testtable(name);

5, Select command

SELECT * from testtable;

6, Removing tables, triggers, views, and indexes

drop index indexname
drop table tablename
drop trigger triggername
drop view viewname

Export a database

You can export (backup) your database using the command-line program and .dump. It works the same way it does from the SQLite shell. But we are going to want the output to go to a file and not the screen so we will redirect it to a file called dbbackup.

user@host:~$ sqlite3 test.db '.dump' > dbbackup

You could also compress the output:

user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz

The following example will use a file called statements.sql and import them into our test.db file:

user@host:~$ sqlite3 test.db < statements.sql

Data types in SQLite v3

NULL - The value is a NULL value.
INTEGER - The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL - The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT - The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB - The value is a blob of data, stored exactly as it was input.