SQLite Basic commands:
1. Create table
CREATE TABLE abctable (id integer primary key, name text);
2. Insert into table
INSERT INTO "abctable" VALUES(1,'Kuldeep');
3. Create a view
CREATE VIEW abctable AS select * from testtable;
4. Add index
CREATE INDEX abctable on testtable(name);
5. Select command
6. Removing tables, triggers, views, and indexes
drop index indexname
drop table tablename
drop trigger triggername
drop view viewname
7. 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
8. You could also compress the output:
user@host:~$ sqlite3 test.db '.dump' | gzip -c > dbbackup.gz
9. This command will use a file called abc.sql and import them into our test.db file:
user@host:~$ sqlite3 test.db < abc.sql
10. SQLite Data types
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.