Blog : SQLite Basic commands:

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

SELECT * from abctable;

6.  Removing tables, triggers, views, and indexes

  1. drop index indexname
  2. drop table tablename
  3. drop trigger triggername
  4. 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

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