SQLite Tutorial is one of the most visited article, best on the visitor’s demand I made some changes in SQLite Tutorial. This SQLite Tutorial is very big, so I am giving option to download the tutorial !SQLiteTutorial
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:
$ sqlite3 test.db SQLite version 3.0.8 Enter ".help" for instructions sqlite> .quit $
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 three ways to safely exit this environment: .q, .quit, and .exit.
You do not have to enter the sqlite3 interactive environment. Instead, you could perform all commands at the shell prompt, which is ideal when running bash scripts and commands in an ssh string. Here is an example of how you would create a simple table from the command prompt:
ow, back to the problem of creating a pivot table for displaying exam scores in a spreadsheet-like format. First, more data is is needed. By the way, if have not added any data, the following script will create the necessary tables and insert the data.
$ sqlite3 test.db "create table t1 (t1key INTEGER PRIMARY KEY,data TEXT,num double,timeEnter DATE);"
After table t1 has been created, data can be inserted as follows:
$ 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);"
As expected, doing a
select returns the data in the table. Note that 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. Note that you should not use the abbreviation “INT” when working with the PRIMARY KEY. You must use “INTEGER” for the primary key to update.
$ sqlite3 test.db "select * from t1 limit 2"; 1|This is sample data|3| 2|More sample data|6|
In the statement above, the limit clause is used, and only two rows are displayed. There is an offset option for the limit clause. For instance, the third row is equal to the following: “limit 1 offset 2”.
$ sqlite3 test.db "select * from t1 order by t1key limit 1 offset 2"; 3|And a little more|9|
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.
$ 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)
All SQL information and data inserted into a database can be extracted with the “.dump” command. Also, you might want to look for the “~/.sqlite_history” file.
$ 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;
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.
$ sqlite3 test.db ".dump"|sed -e s/t1/t2/|sqlite3 test2.db