Fast SQLite Tutorial

here a short introduction to SQLite 3. Have Fun!

Open database, if database does not exist it will be created

C:\test> sqlite4 mydatabase.db

Show help

</em>
sqlite> .help

Show databases

sqlite> .databases

Show tables

sqlite> .tables

Show create statement for table

sqlite> .schema tablename

Quit SQLite

sqlite> .quit

or

sqlite> .exit

Create table as used to in SQL

C:\test> sqlite4 mydatabase.db
sqlite> CREATE TABLE username (id INTEGER PRIMARY KEY, username VARCHAR(120), password TEXT, firstname VARCHAR(120), lastname VARCHAR(120), email VARCHAR(250));
sqlite> .tables
username

Drop table

sqlite> DROP TABLE username;
sqlite> .tables
sqlite>

Insert

sqlite> CREATE TABLE username (id INTEGER PRIMARY KEY, username VARCHAR(120), password TEXT, firstname VARCHAR(120), lastname VARCHAR(120), email VARCHAR(250));
sqlite> INSERT INTO username (username, firstname, lastname, email) VALUES ("Christian.Koch", "Christian", "Koch", "blubb@blubbblubb.net");

Select

sqlite> SELECT * FROM username;
1|Christian.Koch||Christian|Koch|blubb@blubbblubb.net
sqlite>

You actually do not need to open the sqlite shell. All commands from SQLite and SQL can be send from comandline.

C:\test> sqlite3 mydatabase.db "SELECT * FROM username;"
1|Christian.Koch||Christian|Koch|blubb@blubbblubb.net

C:\test>

Creating a dump

C:\test> sqlite3 mydatabase.db ".dump"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE username (id INTEGER PRIMARY KEY, username VARCHAR(120), password TEXT, firstname VARCHAR(120), lastname VARCHAR(120), email VARCHAR(250)
);
INSERT INTO "username" VALUES(1,'Christian.Koch',NULL,'Christian','Koch','blubb@blubbblubb.net');
COMMIT;

C:\test>

Backup the dump

C:\test> sqlite3 mydatabase.db ".dump" > backup.sql

Create new database from dump

C:\test> sqlite3 newdatabase.db < backup.sql
C:\test> sqlite3 newdatabase.db ".dump"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE username (id INTEGER PRIMARY KEY, username VARCHAR(120), password TEXT, firstname VARCHAR(120), lastname VARCHAR(120), email VARCHAR(250)
);
INSERT INTO "username" VALUES(1,'Christian.Koch',NULL,'Christian','Koch','blubb@blubbblubb.net');
COMMIT;

C:\test>