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
