SQLite Commands and general usage

Reference: https://www.pantz.org/software/sqlite/sqlite_commands_and_general_usage.html

SQLite is an embedded open source relational database (db). It is the very portable, easy-to-use, compact, efficient, and reliable. Being an embedded database it becomes part of the program that hosts it. It's embedded in many popular programs used today. Most of the SQLite C API to interface with the SQLite db. Many scripting/programming languages use the API like the Perl module Dbi::sqlite, PHP's data objects with the SQLite Driv Er, or just straight C programs. Not only can these languages (and many more) with the SQLite C API to access the SQLite db but most OS ' s has a statically Linked native binary that can also is controlled completely from the command line. How cool was that?

Installing

SQLite can be installed by going to the SQLite website and downloading a copy and installing it. They has binaries for most major platforms (Windows, MAC, or Linux). If your using a, OS with a, package manager then there are a good chance you can just install it by using the your package manage R.

There is 2 versions of SQLite. Version 2 and version 3. Version 2 is (as guessed) is the older version. SQLite 3 databases is not compatible with SQLite 2 databases. Use SQLite 3 if at all possible. If your going to install the SQLite command, line version, and here's something to remember. The package for SQLite 2 is just called "SQLite". The package was called "SQLite3" for SQLite version 3. These is also the names for the command line binaries. You access a version 2 db with the ' SQLite ' binary (program). You access a version 3 db with the "SQLite3" binary.

Here is some examples of installing SQLite version 3 on some Linux and BSD ' s.

On Linux distros with the Apt-get Package Manager (Ex:debian or Ubuntu).

sudo apt-get install SQLite3

On Linux distros with the Yum Package Manager (Ex:redhat, CentOS, or Fedora).

Yum Install SQLite3

On OpenBSD 4.4.

Pkg_add-v ftp://ftp.openbsd.org/pub/OpenBSD/4.4/packages/i386/SQLite3-3.5.9p0.tgz

Get the idea. Just Install the SQLite3 package for whatever OS. If your OS does not has a package just go to the SQLite website then download and install their binary.

SQLite command Line

The following commands would be using the statically linked command-line program. This is a installed in the Install section above. You can completely control a SQLite database with this program and it's special built in shell. Some commands in the following examples would be invoked from a Linux shell and and from the SQLite shell. The SQLite Shell would consider any command with. (period) in front of it a SQLite command. You can get a complete list of valid SQLite commands by typing. "Help" at the SQLite shell. The commands that don ' t begin with a period is just regular SQL commands. They should work with the most databases.

The examples below is done by a Linux machine with version 3.5.9 of SQLite.

Below when you see [e-mail protected]:~$ It means execute the command from the shell. When you see the sqlite> it means from a sqlite prompt (shell) after selecting a db.

Making/accessing a SQLite database.

This command would not do the db (the file test.db) until you create something in it (ex:table or view) or until you run A few select commands from the SQLite prompt (like. database). This was so you can change things like page size or character encoding before the database structure is written to the file . After executing the command, you'll see a SQLite prompt. Make a table or a view to has the file actually written to disk.

If you already has a database file called test.db then you can select and access it with the same command. In summary if the DB file does does exist this command would create it and if it does exist then it would select it to be ACC Essed.

[Email protected]:~$ sqlite3 test.db

sqlite>. Open "Test.db"
Sqlite> SELECT * FROM table_name ...;

Make your output pretty

Before we start creating and looking at data we is going to change a few default settings to make the ouput of the Comman DS look better. We'll turn on column mode (. Mode col). There is many other modes and other than Col. See. Help for more output modes. We'll also turn on column headers (. headers on). Both stay on until your exit the SQLite shell or change them to something else.

sqlite>. Mode col
Sqlite>. Headers on
Creating tables

Let's make a table called test. After the table is made the database file would exist on disk. The table named Test and has 2 columns. One called IDs and one called value. The IDs column is an integer primary key, SQLite would make this an auto increment column. Which means if there is no number then SQLite would generate a number for the column on the first insert. After the first insert it would auto increment the numbers from there. All of the that is do with the SQL create command.

Sqlite> CREATE TABLE Test (IDs integer primary key, value text);

Creating views

Let ' s create a view. Views is like virtual tables. People also call them derived tables. This is because their contents was derived from other tables. Regular tables, but they is not. Views is dynamically generated. A view is made up of relational expressions and takes other tables to produce a new table.

The following example would create a view called TestView and would select everything from the test table we just created. It's a silly example of what is good for but it'll work. Most views is good for queries your keep using over and over again. You create the view to use as a temporary table and then query the view just like a table. The view eliminates the need to keep using that same redundant query.

Creating views like Creating tables are done with the SQL create command.

Sqlite> CREATE VIEW TestView as SELECT * from Test;
Creating indexes

Indexes designed to speed up queries under certain conditions. If We test table had 100,000 entries and we wanted to find a small group of entries the database would has to scan all 1 00,000 entries looking for our small group. If we make a index of all these entries then we'll be able to scan all the entries much faster.

The bad thing was that indexes increase the size of the database because they keep a copy of the the columns they index. One other bad thing is so if you insert, UPDATE or delete a record the database have to modify the record and the index O F that table. This means is indexes can slow down inserts, updates, deletes, etc. If used wisely indexes can give you a huge performance boost. You'll just has to test them for yourself. Here are how to make a index for our table test on column value.

Creating indexes like Creating tables and views are done with the SQL create command.

Sqlite> CREATE index Testindex on test (value);
Listing views, tables, and indexes

The tables and views in the database we use the SQLite Tables command. You can also use the SQL like operator to find tables and views with a pattern. Using the% symbol in the example below we show all the tables and views beginning with the letter T.

Sqlite>. Tables

Test TestView
sqlite>. Tables t%
Test
To view any indexes a table have we can use the SQLite. Indices command. To see them for our test table, we would do the following.

sqlite>. Indices test

Testindex
Listing schema Information

To see database schema information we use the SQLite. Schema command. This give your create command that is used to create the tables. If you created a index on any columns then this would also show up.

sqlite>. Schema test

CREATE TABLE Test (IDs integer primary key, value text);
CREATE INDEX testindex on test (value);
Inserting data into tables

Now let's put in some data on the db with some SQL INSERT statements.

sqlite> INSERT into test (value) VALUES (' value1 ');
sqlite> INSERT into test (value) VALUES (' value2 ');
sqlite> INSERT into test (value) VALUES (' Value3 ');
sqlite> INSERT into test (value) VALUES (' value4 ');
Seeing the data

Let's use the SQL Select command to look at the data we inserted. Remember we turned on column mode (. Mode col) and column headers (. headers on) at the beginning of this page so it ' s Easie R to read the output.

Sqlite> select * from test;

IDS value
---------- ----------
1 value1
2 value2
3 Value3
4 Value4
Seeing your SQLite databases

The databases that is currently open use the SQLite. Databases command. It'll show the main and temp databases and where they is on the system.

sqlite>. Databases

Seq Name file
--- --------------- ------------------------------------
0 main/home/user/test.db
1 Temp/var/tmp/etilqs_ocuxllbzo4tjuni
Exporting your data

You can export any of your database objects to a file for backup using the SQLite. Output and. Dump Commands. These commands would output all of the data from the database. The first output line directs any output from now on to the File/tmp/test.sql. The second line. Dump would export all of the data in the objects in the database. If The file is already there it would overwrite that file. The last command would set the output back to the screens like we had it.

Sqlite>. Output/tmp/test.sql
sqlite>. Dump
sqlite>. Output stdout
"To" at "What" file looks like open a new shell and cat "that file.

[Email protected]:~$ cat/tmp/test.sql

BEGIN TRANSACTION;
CREATE TABLE Test (IDs integer primary key, value text);
INSERT into "Test" VALUES (1, ' value1 ');
INSERT into "Test" VALUES (2, ' value2 ');
INSERT into "Test" VALUES (3, ' value3 ');
INSERT into "Test" VALUES (4, ' value4 ');
CREATE VIEW TestView as SELECT * from Test;
CREATE INDEX testindex on test (value);
COMMIT;
The file contains all the information your need to re-create your database. From creating the tables to inserting all the data. This is used mostly to backup databases or to export the data from one database type to another. For backing up purposes it was usually easier to just copy the SQLite3 db file to another location.

Importing external Data

If you would like to import the data exported using the SQLite. Dump command It's as easy as using the SQLite. Read Co Mmand. The example below the first command makes a new database from the system command line (shell). The second command imports the data we exported in the previous example. The last command shows all the data in the new database.

[Email protected]:~$ sqlite3 testrecover.db
Sqlite>. Read/tmp/test.sql
Sqlite> select * from test;

IDS value
---------- ----------
1 value1
2 value2
3 Value3
4 Value4
If you want to import data, in CSV (comma separated value), format into a table you can do it using the SQLite. Impo RT command. The CSV file has a to use separators that SQLite understands. To see the current separators you can use the. Show command. SQLite uses the pipe | As the default separator.

Sqlite>. Show

Echo:off
Explain:off
Headers:on
Mode:column
Nullvalue: ""
Output:stdout
Separator: "|"
Width
The separator type use the. Separator command. This would change it for output mode and for. Import.

sqlite>. Separator,
Sqlite>. Show

Echo:off
Explain:off
Headers:on
Mode:column
Nullvalue: ""
Output:stdout
Separator: ","
Width
Now, the separator is changed to a comma we can import data from a external CSV file. We'll import more data to our current table (test) with the. Import command. Make sure your CSV file sections match up with your tables and so you have the same number of columns. Also, note that if you export a file now it is exported with a comma as the separator.

Here's what's the Test.csv file looks like.

5,value5
6,value6
7,value7
The import from the CSV file and the "test table looks like after import.

sqlite>. Import/tmp/test.csv Test
Sqlite> select * from test;

IDS value
---------- ----------
1 value1
2 value2
3 Value3
4 Value4
5 Value5
6 Value6
7 Value7
Removing tables, triggers, views, and indexes

To remove any index, table, trigger, or view that has made use the SQL drop command. The syntax is the word "drop then" and then the "want to drop" and then the "the name of the thing you want to drop." Below is and example of dropping one of each kind.

Drop INDEX IndexName
DROP TABLE TableName
Drop Trigger Triggername
Drop View ViewName
Exiting

To exit out of the SQLite prompt use the SQLite. Exit or. Quit command. Both work the same.

Sqlite>. Exit
[Email protected]:~$
SQLite from the shell

Intro

The following commands'll be using the statically linked SQLite command-line program. This is a installed in the Install section above. You can completely control a SQLite database with the "right from the" Linux shell. No need to invoke the SQLite shell we introduced above. Being able to access SQLite databases from a shell lets your incorporate SQLite into your shell scripts without the need fo R External Libraries.

We'll use the same test.db database, we made in the examples above for this demo.

Usage Options

The SQLite binary is for version 3 is called SQLite3. You can use the "this program" from the command line just. If you want to look at the options the binary has just issue the--HELP option.

[Email protected]:~$ sqlite3--help

Usage:sqlite3 [OPTIONS] FILENAME [SQL]
FILENAME is the name of the SQLite database. A New Database is created
If the file does not previously exist.
OPTIONS include:
-init filename read/process named file
-echo Print commands before execution
-[no]header turn headers on or off
-bail stop after hitting an error
-interactive Force Interactive I/O
-batch force Batch I/O
-column set output mode to ' column '
-csv set output mode to ' CSV '
-html Set output mode to HTML
-line set output mode to ' line '
-list set Output mode to ' list '
-separator ' x ' Set output field separator (|)
-nullvalue ' text ' Set text string for NULL values
-version Show SQLite version
You can see syntax need to use and the different options available.

NOTE the Above:if the database does not exist it would be is created on any command given. Even on commands this fail.

Execute SQL Statements

You can execute SQL statements right from the shell. Let's look at the data on our test database test.db. We is going to turn on headers and also turn on column mode so the ouput looks nice.

[Email protected]:~$ sqlite3-header-column test.db ' select * from Test '

IDS value
---------- ----------
1 value1
2 value2
3 Value3
4 Value4
5 Value5
6 Value6
7 Value7
You can execute any SQL statement against a database like this.

Execute SQLite Commands

You can execute SQLite commands the same-on-the-you-execute SQL commands. The example below uses the. Schema SQLite command on the test.db file.

[Email protected]:~$ sqlite3-header-column test.db '. Schema '

CREATE TABLE Test (IDs integer primary key, value text);
CREATE VIEW TestView as SELECT * from Test;
CREATE INDEX testindex on test (value);
Export a Database

You can export (backup) your database using the command-line program and. Dump. It works the same it does from the SQLite shell. But we is going to want the output to go to a file and not the screens so we'll redirect it to a file called Dbbackup.

[Email protected]:~$ sqlite3 test.db '. Dump ' > Dbbackup
You could also compress the output before writing it disk by running. Dump ' s ouput through gzip, bzip2, or whatever. The following is an example using bZIP. It's highly recommended that compress your dumps.

[Email protected]:~$ sqlite3 test.db '. Dump ' | Gzip-c > dbbackup.gz
Import a Database

If you had used the SQLite. Dump command We mentioned earlier to create a file with SQL statements you can input those St Atements into your database from the command line. This can is done with any SQL statements not just the ones from the. Dump command. The following example would use a file called Statements.sql and import them to our test.db file.

[Email protected]:~$ sqlite3 test.db < Statements.sql
To import that gzip compressed dump (backup) of the SQL statements from above.

[Email protected]:~$ gunzip-c dbbackup.gz | SQLite3 test.db
As a speed note. If your importing a lot of inserts statements wrap those in a transaction to get a major speedup.

More information

Here is some great links from the SQLite website to help you further.

SQL commands understood by SQLite

SQLite understands most of the standard SQL language. But there is some syntax differences sometimes. There is also some SQL commands that SQLite have added that is unique. These is commands like VACUUM or PRAGMA. Make sure your look at the PRAGMA page as it's used to tweak settings in your SQLite database.

SQLite Commands and general usage