SQLite3 Tutorial

SQLite3 TutorialWelcome to my SQLite3 Tutorial! This tutorial is part of my Android Development Tutorial, so my Android tutorial is not over. I have many more videos on Android coming.

In this video I explain how to install SQLite3 on both Mac and Windows. I also cover how to use SQLite3 using the console. SQLite is an embedded relational database that doesn’t require a dedicated database management system. The database is part of your code and not an outside resource. The reason for creating SQLite was to provide a self contained database that was easy to use, could travel with the program using it and run on any machine with no other required software.

If you like videos like this, it helps to tell Google+ with a click here

SQLite3 Cheat Sheet

sqlite3 test.db // open sqlite and provide a database name

// Creates a table in the database
// Primary Key automatically generates values that start at 1 and increase by 1
// name is a text field that will hold employee names

create table employees (id integer primary key, name text); 

// Insert some employees

insert into employees (id, name) values(1, 'Max Eisenhardt');
insert into employees (name) values('Pietro Maximoff');
insert into employees (name) values('Wanda Maximoff');
insert into employees (name) values('Mortimer Toynbee');
insert into employees (name) values('Jason Wyngarde');

// In column mode, each record is shown on a separate line with the data aligned in columns

// headers on shows the column names, if off they wouldn't show

.mode column
.headers on
select * from employees; // Show all employees

// Changes the width of the columns

.width 15 20

.exit // Closes the database

sqlite3 test.db // Reopen database

.tables // Displays the tables

// Displays every value on its own line

.mode line
select * from employees;

// Shows the statements used to create the database. You could also provide a table name to see how that single table was made

.schema OR .schema employees

// You can get a more detailed database view

.mode column
.headers on
select type, name, tbl_name, sql from sqlite_master order by type;

// Used to show the current settings

.show

// Set NULL to 'NULL'

.nullvalue 'NULL'
.show

// Change the prompt for SQLite

.prompt 'sqlite3> '
.show

// Used to export database into SQL format on the screen

.dump

// Used to output to a file

.output ./Documents/sqlite3Files/employees.sql
.dump
.output stdout // Restores output to the screen

// You don't delete a database with any command. You have to delete the file itself

// You can delete a table however

drop table employees;

// You can import the table then with

.read ./Documents/sqlite3Files/employees.sql

// .mode is used to change the formatting of the output
// OPTIONS FOR MODE : column, csv
// html: html table
// insert: insert commands used 
// list: List without commas
// tabs: Tab separated list

// How to output a CSV list to a file

.mode csv // You could define the output should be csv
.separator ,  // OR define the separator for the columns
.output ./Documents/sqlite3Files/employees.csv
.separator ,
select * from employees;
.output stdout

// Output html table

.mode html 
select * from employees;
.output stdout

// line outputs column name and value

.mode line
select * from employees;
.output stdout

// Items with double quotes

.mode tcl
select * from employees;
.output stdout

22 Responses to “SQLite3 Tutorial”

  1. Punit says:

    Hi Darek,

    i need some of your help.
    i had given a task in my office, in which i need to write one utility class to create and load some data in our product, i wrote that one class which create huge amount of complex data, i just need to run that class and it creates and loads all the data.
    now scenario is changed, we may need to load this when our product gets installed, but the problem is during product installation data can be loaded from xml loaders, but i created all data using java classes, and now i don’t want to waste all my efforts, is there any way for this?

    one way which hit my mind is, if i can call that java class in one xml file, and load data using that xml file, but i am not sure if this would be possible or not.

    please let me know, if there is a way

  2. Maggi says:

    Hi Derek,

    Sorry for deviating from topic.

    How to stop the concurrent data read from the database. Basically i want to stop the concurrent usage and do not want to use row lock. How to achieve this in my application.

    Please give a solution.

  3. Mahesh says:

    Hi Derek,

    Are you thinking to cover anything about performance analysis, front end , back-en, transactions and all.. if not in your list can you add it to your list a request ?
    ( Performance tuning : production application )
    Thanks.

  4. Abhishek Sinha says:

    Hi Derek,

    Thank you very much for your great tutorials.

    Can you please create a tutorial on storing/retrieving images in SQLite.

    Thanks.

    • Derek Banas says:

      You’re very welcome :) I will be covering that topic soon. I’ll store pictures, video, sound, etc.

      • ali aslam says:

        Mr. Banas, is sqlite3 good for an android app that will be using a large dataset? I am building a fitness app and my data will include various exercises along with other “text” info but then i will also be including pictures and videos to along with the exercise. Does sqlite3 good for that? i have been reading sqlite should be used. What is the difference between sqlite3 and sqlite?

  5. Robin says:

    Thanks for an amazingly clear, concise set of instructional videos. These are my go-to resource to teach myself Android programming.
    One query is there anyplace a comprehensive Table of Contents or Index for which video covers which topics? I’ve reached the point where I don’t need to understand feature ‘X’ this minute., but a few days later I hit it, and have to bounce through the videos to find out which covered what content.

  6. Crystal says:

    I am LOVING these tutorials. I pick things up really quickly and you’re one of the fastest video instructors I’ve ever seen, I’m so grateful! I am enjoying not being bored while watching a video tutorial for the first time, in a very long time!

    Quick question.
    I’m watching all these in preparation of building an app that would manage contacts and invoices, etc. I want to be able to store my data on the cloud or on a server, so that I can access is from my nexus phone and my nexus tablet. Is that plausible with SQLite3?

    Keep it up! I’m sucking it up like a dry sponge in the desert suddenly confronted with a flash flood; and I love it!

    • Derek Banas says:

      Thank you :) Yes I may not be the best, but I’m probably the fastest. As per storing large amounts of information you’ll probably want to store that data on a web server. I talk about how to read from a web service. I’ll soon cover how to set a web service up.

      SQLite is used when you want to store a rather small amount of information, but very often that is all you’ll need.

      • Crystal says:

        This is where I start to feel the holes in my self taught education. What constitutes a large amount of data vs a small amount?

        At any given time I have from sixty (if I’m not really working hard) to three or four hundred clients and contacts; Then I have invoices for each, typically between three to seven new records a year; and anywhere from just three, total, to five hundred team members on my sales force (again depending on if I’ve been healthy enough to remember to do my sales calls and team coaching… High turn over, what can you do? Sales is tough) I have sales reports for them, and goal progress and personal notes, and then my product inventory.

        Personally it feels like a lot of information, which is why I’m trying to find a way to manage it better. However, my husband’s a database engineer, and I know it’s very common for him to work on databases with terabytes and more of data and several million (if not hundred-million) records. (which you would think would answer my questions, but he just shrugs and says he doesn’t know what I should use because he doesn’t work with mobile asps/hardware and/or sqlite.) I won’t be using nearly that much data. I’ll be lucky (making LOTS of sales ;-D) if I can get over 700M.

        But would that be “large” on a database using sqlite? There would only be two instances running at any given time (tablet and phone) both of which I’d be the one using, and an average individual tends to use one or the other, so I don’t think concurrence and record locking would be an issue (I know you mentioned it doesn’t handle that well, if it’s gonna be hit from several users at once) Or is 500M to 700M or so going to be large for sqlite?

        (And wow…I just realized I’m having a day where my posts turn into monologues… Sorry about that)

        Crystal

        • Derek Banas says:

          If you have over 100 contacts I’d use MySQL and have your web server handle all the calculations and data retrieval. That is just my rule of thumb though. I tend to only use SQLite when I’m working with unimportant data.

  7. Roland says:

    Hi Derek, do you have an video about how to use Intent sniffer Tool. If if you have any, please share the link.

    Thanks,
    Roland.

  8. Ahmed Ali says:

    hay Derek, Really you are an awesome man who help us learning free tutorials and i ask if you can make some tutorials on ORMLite i’ll be appreciate that :)

  9. Hello Derek,

    I love your tutorials they are all really educative in sense that you change lives. I watched your sqlite3 tutorials in order to create an interactive dictionary. The idea is to have three tables. (1) language1, (2) universal language, (3) language2.. It is a dictionary and not a translation tool. I will give a user a choice to selection a language from and a destionation language. What my universal language will do is to facilitate. Each word will have an ID like a social security including (country or language id, word id and may be additional identificatong). The universal language will have a relation with all languages. Do you have tips or suggestions? I would like to hear from you. Thank you very much.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title="" rel=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Google+