Table of Contents

Middleware

SQLite


Table of Contents


About SQLite

https://www.sqlite.org/


How to use SQLite

Operation SQL
check version $sqlite3 -version
Create database
Connect database 		$ sqlite3 sqlite.sqlite3
logout sqlite> .exit
Help sqlite> .help
Check SQLite sqlite> .show
Check tables list sqlite> .table
Check tables schema sqlite> .schema TABLE_NAME
sqlite> .schema --indent
Size Maintenance sqlite> vacuum;


How to create table

Datatypes In SQLite Version 3

integer
int 		The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
text The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
real The value is a floating point value, stored as an 8-byte IEEE floating point number.
null The value is a NULL value.
blog Binary Large OBject
The value is a blob of data, stored exactly as it was input.

https://sqlite.org/datatype3.html 


sqlite>create table users(
    id integer primary key autoincrement,
    name text,
    age integer,
    email text unique,
    status integer default 1
);
Example Memo
sqlite> create table user(name text not null, address text);
sqlite> create table user(id integer primary key autoincrement, name text);
sqlite> create table user(id integer, name text, ts default CURRENT_TIMESTAMP);

-- no autoincrement for speed up
CREATE TABLE RowIdTable (
    Id    INTEGER PRIMARY KEY,
    Value INTEGER NOT NULL
);

CREATE TABLE TEST ( 
    id INTEGER PRIMARY KEY AUTOINCREMENT, 
    data TEXT, 
    ts_update TIMESTAMP DEFAULT (DATETIME(‘now’,’localtime’)) 
);


How to change table name.

sqlite> alter table TABLE1 rename to TABLE1NEW;
Example
sqlite> .tables    <- check table name
sqlite> alter table TABLE1 rename to TABLE1NEW;
sqlite> .tables    <- check table name


How to delete column.

sqlite> .tables

sqlite> .schema person
CREATE TABLE person(
 id INTEGER PRIMARY KEY, 
 first_name TEXT,
 last_name TEXT, 
 age INTEGER, 
 height INTEGER
);

sqlite> CREATE TABLE new_person(
   ...>  id INTEGER PRIMARY KEY, 
   ...>  first_name TEXT, 
   ...>  last_name TEXT, 
   ...>  age INTEGER 
   ...> ) ; 

sqlite> .tables

sqlite> INSERT INTO new_person
   ...> SELECT id, first_name, last_name, age FROM person ;

sqlite> DROP TABLE IF EXISTS person ; 
sqlite> ALTER TABLE new_person RENAME TO person ;

sqlite3 - How to delete or add column in SQLITE? - Stack Overflow


How to use a SQL File

$ sqlite3 test.db < test.sql
Fast Insert
echo "BEGIN; -- or BEGIN TRANSACTION;"  > begin.txt
echo "END; -- can be COMMIT TRANSACTION; also" > end.txt
cat begin.txt insert.txt end.txt > test.txt

sqlite3 test.db < test.sql


Import CSV File

sqlite> .tables    <- check table name
sqlite> .show
sqlite> .separator ,
sqlite> .import sample.csv TABLE


Import / Export with TSV File

Import
sqlite> .separator "\t"
sqlite> .import data.tsv table_name
Export
sqlite> .header off        /* Don't print headers */
sqlite> .mode tabs         /* with TAB */
sqlite> .output hoge3.tsv  /* Out put file name */
sqlite> select * from TABLE;


How to use SQL wihtout dialogue

$ sqlite3 test.sqlite3 "select id, login from users;"


TRUNCATE TABLE Command

SQLite> DELETE FROM COMPANY;
SQLite> VACUUM;




TIPS


Comment

sqlite> select * from test; -- Comments

sqlite> select * from /* Comments */ test;


order by ip address

select * from networks ORDER BY CAST(substr(networks,1,instr(networks,'.')) AS NUMERIC), CAST(substr(networks,instr(networks,'.'), instr(substr(networks,instr(networks,'.')))) AS NUMERIC);

SQLite INSTR is from version 3.7.15.
https://stackoverflow.com/questions/23092783/best-way-to-sort-by-ip-addresses-in-sql



Middleware