cmdref.net is command references/cheat sheets/examples for system engineers.
| Cloud |
|---|
| Etc |
|---|
| Reference |
|---|
| 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; |
| 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
);
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’))
);
sqlite> alter table TABLE1 rename to TABLE1NEW;
sqlite> .tables <- check table name sqlite> alter table TABLE1 rename to TABLE1NEW; sqlite> .tables <- check table name
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
$ sqlite3 test.db < test.sql
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
sqlite> .tables <- check table name sqlite> .show sqlite> .separator , sqlite> .import sample.csv TABLE
sqlite> .separator "\t" sqlite> .import data.tsv table_name
sqlite> .header off /* Don't print headers */ sqlite> .mode tabs /* with TAB */ sqlite> .output hoge3.tsv /* Out put file name */ sqlite> select * from TABLE;
$ sqlite3 test.sqlite3 "select id, login from users;"
SQLite> DELETE FROM COMPANY; SQLite> VACUUM;
sqlite> select * from test; -- Comments sqlite> select * from /* Comments */ test;
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