SQLite Basic tutorial

Share

sqliteSQLite is a relational database management system contained in a C programming library. In contrast to other database management systems, SQLite is not implemented as a separate process that a client program running in another process accesses. Rather, it is part of the using program.

SQLite is ACID-compliant and implements most of the SQL standard, using a dynamically and weakly typed SQL syntax that does not guarantee the domain integrity.

SQLite is a popular choice as embedded database for local/client storage in application software such as web browsers. SQLite has bindings to many programming languages.

Create new database,inventory.sqlt, of datacentre inventory with two sample tables hosts and datacentre.

-bash-3.2$ sqlite3 inventory.sqlt
SQLite version 3.3.6
Enter “.help” for instructions
sqlite> CREATE TABLE hosts (IP CHAR(18), hostname CHAR(255), datacentre CHAR(32), vendormodel CHAR(255));
sqlite> CREATE TABLE datacentre (datacentre CHAR(255), location CHAR(255));
sqlite>

See database

sqlite> .databases
seq  name             file
—  —————  ———————————————————-
0    main             /home/marya/sqlite/inventory.sqlt
sqlite>

See tables

sqlite> .table
datacentre  hosts
sqlite>

Check table structure (schema)

sqlite> .schema hosts
CREATE TABLE hosts (IP CHAR(18), hostname CHAR(255), datacentre CHAR(32), vendormodel CHAR(255));
sqlite>

sqlite> .schema datacentre
CREATE TABLE datacentre (datacentre CHAR(255), location CHAR(255));
sqlite>

Insert data in tables

sqlite>
sqlite> insert into “datacentre” values(‘DC1′,’CITY1’);
sqlite> insert into “datacentre” values(‘DC2′,’CITY1’);
sqlite> insert into “datacentre” values(‘DC3′,’CITY2’);
sqlite>

You can also create a batch file and pull data from there

create a file with insert statements

-bash-3.2$ cat hosts.txt
insert into “hosts” values(‘192.168.1.1′,’router1.dc1.city1′,’DC1′,’Cisco’);
insert into “hosts” values(‘192.168.1.2′,’server1.dc1.city1′,’DC1′,’HP’);
insert into “hosts” values(‘192.168.2.3′,’server2.dc2.city1′,’DC2′,’Sun’);
insert into “hosts” values(‘192.168.2.2′,’firewall.dc2.city1′,’DC2′,’F5’);
insert into “hosts” values(‘192.168.2.4′,’backup.dc2.city1′,’DC2′,’netbackup’);
-bash-3.2$
-bash-3.2$ sqlite3 inventory.sqlt < datacentre.txt
-bash-3.2$

Select queries

Show all data in hosts table

sqlite> select * from hosts;
192.168.1.1|router1.dc1.city1|DC1|Cisco
192.168.1.2|server1.dc1.city1|DC1|HP
192.168.2.3|server2.dc2.city1|DC2|Sun
192.168.2.2|firewall.dc2.city1|DC2|F5
192.168.2.4|backup.dc2.city1|DC2|netbackup

Show hosts in DC2

sqlite> select * from hosts where datacentre like ‘DC2’;
192.168.2.3|server2.dc2.city1|DC2|Sun
192.168.2.2|firewall.dc2.city1|DC2|F5
192.168.2.4|backup.dc2.city1|DC2|netbackup

Show location of 192.168.2.2 IP/Host

sqlite>
sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
CITY1

Update city information of DC2 from city1 to city2

sqlite>
sqlite> update datacentre set location=’city2′ where datacentre=’DC2′;
sqlite>

Now again check city information for IP/Host 192.168.2.2

sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
city2
sqlite>

You can dump output to a file as well

sqlite>
sqlite> .output test.txt
sqlite> select location from datacentre where datacentre like (select datacentre from hosts where IP like ‘%192.168.2.2%’);
sqlite> .exit
-bash-3.2$ cat test.txt
city2

Delete operation

sqlite> delete from hosts where ip=’192.168.2.2′;

Create index

sqlite>
sqlite> CREATE INDEX ip_name ON hosts (ip);
sqlite>

Show indexes on a table

sqlite> .indices hosts
ip_name
sqlite>

Show index details

sqlite> SELECT * FROM sqlite_master WHERE type = ‘index’;
index|ip_name|hosts|4|CREATE INDEX ip_name ON hosts (ip)

Remove indexing

sqlite> drop index ip_name;
sqlite>

Import data from CSV file

.separator “delimiter”
.import csvfile

Take a break, come out of sqlite !

sqlite>
sqlite> .exit
-bash-3.2$