If you want to search a field in SQLite you might use a query like this:

SELECT MyID FROM Table WHERE Name = 'Doolis';

This performs a case-sensitive search on the Name field. If you want to search the name field in a case-insensitive manner do this:

SELECT MyID FROM Table WHERE Name = 'Doolis' COLLATE NOCASE;

If you want to import a CSV file into a SQLite database it's quite simple. First create a table with the same number of columns.

CREATE TABLE Foo (Col1, Col2, Col3);

Then change the default separator to a comma and run .import against the CSV.

.separator ,
.import /tmp/foo.csv Foo

Every so often it's a good idea to clean up the SQLite databases that Firefox uses.

for i in $(find ~/.mozilla/ -name *.sqlite); do echo "VACUUMING $i"; echo "VACUUM;" | sqlite3 $i; done;

Borrowed from here.

To check if your SQLite query is using an index or not do:

EXPLAIN QUERY PLAN SELECT * FROM Table WHERE ID = 12;

and look for USING [IndexName]

SQLite handles dates quite strangely. If you create a table and put a date in it, the only way SQLite will take the input is "2004-06-01 12:00:00" anything else it won't recognize as a date and thus none of the date functions will work.

Assuming you have a date entered propely (like above) you get date information from the db like so.

SELECT date(DateField) FROM Table;
SELECT time(DateField) FROM Table;
SELECT datetime(DateField) FROM Table;

If you want unix time (seconds since the epoch) you have to format the output.

SELECT strftime("%s", DateField) FROM Table

However that will return the time in UTC which is probably not what you want (it's not what I wanted). I want it to compensate for my local timezone and thus you have to tell it to use your timezone.

SELECT strftime("%s", DateField, 'localtime') FROM Table

This will go the other way. Take a unixtime, and convert it to a SQLite date format.

SELECT datetime(1092941466, 'unixepoch', 'localtime');

Some semi-official SQLite documentation on how it handles dates is availble on their wiki page.

Not quite ALTER TABLE syntax, but it will work in a pinch. Hopefully they'll get ALTER TABLE into the 3.x release some time in the future. Until then here is syntax to do it now. Using transactions it's mostly safe and fairly quick. Probably a good idea to do a VACUUM after you're done.

BEGIN TRANSACTION;
CREATE TEMPORARY TABLE Bar(a,b);
INSERT INTO Bar SELECT a,b FROM Foo;
DROP TABLE Foo;
CREATE TABLE Foo(a,b);
INSERT INTO Foo SELECT a,b FROM Bar;
DROP TABLE Bar;
COMMIT;

Update: SQLite now support some limited ALTER TABLE commands.