In the previous article we have discussed about how to install SQLite. Whether you have installed it for Linux, Windows, or Mac OS X, there should be a working SQLite. If you don’t have it yet, please proceed to installation first depend on you system.

In this article we will discuss about SQLite and cover following topics:

  • Create a new SQLite database called “xathrya.db”.
  • Create “projects” table with three fields 1) Project ID 2) Name and 3) Deadline
  • Insert 5 records into the project tables.
  • Verify the records
  • Delete a record
  • Update a record
  • Exit SQLite3
  • Reopen database

Actually the operating system is not the matter here, means you can use SQLite on any operating system (whether Linux, Windows, or Mac OS X). This article should be generic enough to cover all of them.

Create a New SQLite Database

Like any SQL DBMS, data is stored on a database. However, SQLite will store all the information (tables, and metadatas) on a single file instead of creating differet files.

In this section we will create a new database with filename xathrya.db. Use following command:

You would be prompted by something like this:

At this point, we have entered the SQLite’s command shell. Using this shell we can enter SQL command and manage our database.

Create Table

Creating a table in SQLite is like using any Relational DMBS. The underlying language is similiar, SQL.

Now let’s create a table “projects” having three fields:

  1. Project ID, denoted by field id as integer.
  2. Name, denoted by field name as varchar
  3. Deadline, denoted as deadline as datetime

The command (within SQLite shell) would be:

If you see on current working directory, we have newly created file xathrya.db as result of our command.

Insert Record

To insert a record we use familiar SQL command

Which in our case the the tableT would be projects and the list of values will be the value of id,name,deadline respectively. Let’s insert 5 records there.

Verify Record

Let’s see whether our records has been saved by SQLite by invoking following command:

And you should be given following result.

Delete a Record

To delete a record, for example on with id 105, use following command:

Now let’s verify the tables. At this point, SQLite should give you following result:

Update a Record

SQLite can be used to update an individual record. For example we want to change project with id 102 and set the deadline to 2013-07-02. Use following command;

Now verify the table. The output should be like this:

Exit

To exit, press CTRL+D (CTRL and D at same time).

Reopen Database

When we create a database, it is nothing but a file. To access an existing database and query the records, do the following. i.e When you do “sqlite3 xathrya.db”, if the database doesn’t exist it’ll create it. If it already exists, it’ll open it. As simple as that 😀

If you dont believe it, try to see the tables projects.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">