Back to: unix commands

SQLite3 Database Commands / Setup

CREATE TABLE tbl_dInventory(

programID INTEGER PRIMARY KEY AUTOINCREMENT,

programTitle TEXT NOT NULL,

programDesc TEXT NOT NULL,

createdBy TEXT NOT NULL,

createdOn TEXT NOT NULL,

updatedOn TEXT,

programCollege TEXT,

programDivision TEXT,

programURL TEXT,

contactName TEXT,

contactTitle TEXT,

contactEmail TEXT,

programSize TEXT,

programPrimPop TEXT,

programScope TEXT,

programDuration TEXT,

programFrequency TEXT,

programDOI TEXT,

programKeywords TEXT,

programDOorC TEXT

);

CREATE TABLE tbl_targetCommunity(

programID INT,

programTargetType TEXT,

FOREIGN KEY(programID) REFERENCES tbl_diversityInventory(programID) ON DELETE CASCADE ON UPDATE CASCADE

);

CREATE TABLE tbl_dUsers(

uid TEXT,

upw TEXT,

ucn TEXT,

typ TEXT

);

NOTE: Cascade Delete: in order for this to work, every time you connect to the database either command line or programmatically you need to issue: PRAGMA foreign_keys = on; If you don €™t issue that, deletes will work on the main table, but you €™ll have orphaned data in the tbl_targetCommunity.

SQL COMMANDS:

INSERT VALUES:

tbl_dInventory:

insert into tbl_dInventory (programTitle, programDesc, createdBy,createdOn,programCollege,programDivision,programURL,contactName,contactTitle,contactEmail,programSize,programPrimPop,programScope,programDuration,programFrequency,programDOI)

VALUES ('Test Title','Test Description','MJeffers','03-14-2014','College of Sciences','Undergraduate Division','www.schools.edu','Mickey Mouse','CIO of Mickey World','mmouse@schools.edu','100-500','Student-First Generation','Research','On-going','Monthly','03-01-2014');

tbl_targetCommunity:

sqlite> insert into tbl_targetCommunity values (1,"Disabled");

Get the last inserted rowID:

SELECT last_insert_rowid();

View RowID in selects:

select rowid,* from tbl_targetCommunity;

Delete a row:

delete from tbl_targetCommunity where rowid=6;

Delete All Data in a Table:

delete from <tablename>

Reclaim space: vacuum;

Rename a Table:

alter table <tablename> rename to <new_tablename>

Delete a Table:

drop table <tablename>

Delete a Column:

NOT supported in sqllite3: alter table tbl_diversityinventory DROP COLUMN <column_name>

Update a row:

update tbl_diversityInventory set programURL='http://go.sdsu.edu/lead/' where rowid=1;

Backup a specific table:

sqlite3 divdb/diversity.db ".dump 'tbl_targetCommunity'" > tbl.sql

Restore table:

sqlite3 divdb/diversity.db < tbl1.sql

Update sqlite RowID sequence number:

update sqlite_sequence set seq=3 where name='tbl_diversityInventory';

NOTE: so the next record will have rowed of 4

Dump all table schema:

select * from sqlite_master where type="table"

Add a Column to an Existing Table:

Alter table tbl_diversityinventory ADD COLUMN <name> {type};

Ex. alter table tbl_diversityinventory add column programKeywords TEXT;

A DESC-like command for a table:

pragma table_info (tbl_targetcommunity);

Command Line SQLITE3 Control:

Ex: sqlite3 divdb/diversity.db "drop table 'tbl_targetcommunity'"

How to display a list of sqlite database tables, showing sqlite_sequence:

select name from sqlite_master where type='table'

Without:

select name from sqlite_master where type = 'table' and name <> 'sqlite_sequence'

Explain Query Plan:

EXPLAIN QUERY PLAN SELECT * from <table_name> where x €¦;

SQLITE3 COMMANDS:

HOW TO:

Open / Work with the Database: sqlite3 <database_name>.db

Show Tables: .tables

Show the current Indexes / Indices: .indices <table_name>

Show table schema along with any Indexes: .schema <table_name>

List all Tables: select * from sqlite_master where type="table"

Show | Hide Query Timer: .timer on|off

Show | Hide Explain Query Plan Formatting: .explain on|off

Defragment / Vacuum database or table: vacuum; or vacuum <table_name>;

Exit / Quit: .exit

External Helpful Links:

inserting, updating and deleting data:

http://zetcode.com/db/sqlite/datamanipulation/

Administration Tools:

phpLiteAdmin: https://code.google.com/p/phpliteadmin/