JLSN – SQLite

I am doing a bit of work with SQLite. I have heard about it but I have never actually used it until like a couple of weeks ago. So I am just documenting some of the things I had to search on the internet for.

$ sqlite3
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> 

# Some date manipulation
sqlite> .headers ON # Display fields names
sqlite> SELECT strftime('%Y-%m-%d', 'now','5 month');
strftime('%Y-%m-%d', 'now','5 month')
2013-05-11
sqlite> SELECT strftime('%Y-%m-%d', 'now','5 years');
strftime('%Y-%m-%d', 'now','5 years')
2017-12-11
sqlite> .headers OFF # Turn them off
sqlite> SELECT strftime('%Y-%m-%d', 'now','-5 years');
2007-12-11
sqlite> SELECT strftime('%Y-%m-%d', 'now','-5 month');
2012-07-11
sqlite> SELECT strftime('%Y-%m-%d', 'now','-5 day');
2012-12-06
sqlite> SELECT strftime('%Y-%m-%d', 'now');
2012-12-11

# I thought this was neat 🙂
sqlite> select "Model: " || model, "Available till: " || date from car_records;
Model: Corolla|Available till: 2012-12-16
Model: RunX|Available till: 2012-12-16


sqlite>.schema car_records # Same as mysql>show create table car_records;
CREATE TABLE car_records
(
id INTEGER PRIMARY KEY AUTOINCREMENT,
model TEXT UNIQUE CHECK(model != ''),
date TEXT);

sqlite> .database # Same as mysql>show databases;
seq  name             file                                                      
---  ---------------  -----------------------------------
0    main             /home/user/car.sql 
sqlite>.quit

Usage in script.

$ sqlite3 car.sql "Select * from car_records;" 
1|Corolla|2012-12-16
2|RunX|2012-12-16
$
$ sqlite3 --header car.sql "select * from car_records;"
id|model|date
1|Corolla|2012-12-16
2|RunX|2012-12-16
$
$ sqlite3 --header -separator "," car.sql "select * from car_records;"
id,model,date
1,Corolla,2012-12-16
2,RunX,2012-12-16
$

Sources:
https://www.sqlite.org/
https://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions
http://zetcode.com/databases/sqlitetutorial/sqlitefunctions/
http://www.thegeekstuff.com/2012/09/sqlite-command-examples/

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: