Little sqlite reference

Description:
A little sqlite reference. This article is not intended to be are complete. But almost basic informations are here

Quick links in this article

• Introduction
• Installing sqlite
  • On a Debian box
  • On a MS Windows box
• Checking the docs
• Datatypes
• sqlite in the cli
  • Creating a database
  • Creating a table
  • Insert data into a table
  • Select data from a table
  • Delete data from a table
  • Updata data from a table
  • Drop a table
  • Alter a table
  • Getting some info
• sqlite and python

Introduction

If we not need a sophisticated client/server database system, no multiple clients access, sqlite is the thing. It's easy to use, easy to setup, easy to manage, it's really not to sophisticated.

Ideal if you want to integrate a database system in your own application. There are several programming language that have modules to use sqlite.

Installing sqlite

On a Debian box

We first always update the list of packages and then upgrade all the packages before installing new software. We also install the additional documentation that are available. Maybe, once you have read the documentation and no more need it. You can easy remove the sqlite3-doc package.

apt-get update && apt-get upgrade && install sqlite3 sqlite3-doc

On a Windows box

It's just a matter to fetch the zip archieve of the website, extract it somewhere and execute the sqlite application from a command prompt.

Probably you want to get the installer, so that it install the stuff on the right place, modify the $PATH variable and such.

Checking the docs

Like we have installed the sqlite3-doc packages. It's best that we first read the doc. The docs are located in /usr/share/doc/sqlite3-doc/index.html. You can find this out with dpkg -L sqlite3-doc.

It's also good to check the website of sqlite. Probably you get there up to date news about the product. Also are there are bit more informations than provided in the additional 'sqlite3-doc' package.

Datatypes

• 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-16-LE).
• BLOB The value is a blob of data, stored exactly as it was input.

sqlite in the cli

We first always update the list of packages and then upgrade all the packages before installing new software.

Creating a database

sqlite3 test.db

Or for the personalData.db example:

sqlite3 personalData.db

Creating a table

CREATE TABLE examp(
    one text,
    two int);

Or for the personalData.db example:

CREATE TABLE contacts (
    id int,
    name text,
    forname text,
    address text,
    city text,
    tel text,
    gsm text);

Insert data in the table

INSERT INTO examp VALUES('Hello', World',99);

We insert again an record in the database, so we have a bit more data that we can play with.

INSERT INTO examp VALUES('Python Flying Cyrcus',99);

Or for the personalData.db example:

INSERT INTO contacts VALUES ('1', 'Bart', 'Simpson', 'Springfield street 5', 'Springfield', '000-000.00.00', '000-00.00.00');
INSERT INTO contacts VALUES ('2', 'Lisa', 'Simpson', 'Springfield street 5', 'Springfield', '000-000.00.00', '000-00.00.00');
INSERT INTO contacts VALUES ('1', 'Magie', 'Simpson', 'Springfield street 5', 'Springfield', '000-000.00.00', '000-00.00.00');
INSERT INTO contacts VALUES ('1', 'Magie', 'Simpson', 'Springfield street 5', 'Springfield', '000-000.00.00', '000-00.00.00');
INSERT INTO contacts VALUES ('1', 'Homer', 'Simpson', 'Springfield street 5', 'Springfield', '000-000.00.00', '000-00.00.00');

Select data from an table

SELECT * FROM examp;

But we can also take the fields we want:

SELECT one FROM examp;

Delete data from a table

DELETE FROM examp WHERE two<50;

Update data from a table

UPDATE examp SET one = 'ONE' WHERE one == 'one';

Drop a table

If you may need to delete a table.

DROP TABLE companies_company;

Alter a table

I'm not yet sure about it:

.table ALTER TABLE companies_company;

Getting some info

Get help:

.help

.explain

EXPLAIN INSERT INTO examp VALUES('Hello, World!',99);

EXPLAIN SELECT * FROM examp;

See also:

• http://sqlite.org/lang.html
• http://www.sqlite.org/cvstrac/wiki

sqlite and Python

There are a Python module to access the content of a sqlite database. So that you easy can program an application that do some things with the sqlite database.