SQLite Basic Commands

Related Examples

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.