SQLITE3TUTORIAL

howtouseSQLite3usingtheconsole.SQLiteisanembeddedrelationaldatabasethatdoesn’trequireadedicateddatabasemanagementsystem.Thedatabaseispartofyourcodeandnotanoutsideresource.ThereasonforcreatingSQLitewastoprovideaselfcontaineddatabasethatwase

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. SQLite

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