Sqlite: Difference between revisions
From Leo's Notes
This page was last edited on 3 July 2020, at 21:41.
Created page with "== Quick Usage == Run <code>sqlite3</code>. Use .tables to show tables. Create a table {{Highlight | code = create table bandwidth ( mac character(12), time datetime, tx_b..." |
scripting |
||
| Line 1: | Line 1: | ||
== Quick Usage == | ==Quick Usage== | ||
Run <code>sqlite3</code>. Use .tables to show tables. | Run <code>sqlite3</code>. At the prompt, you can open (or create a new) database with .open. Use .tables to show tables. | ||
You can then begin entering SQL queries. For scripting, you can also pipe these queries to the sqlite3 program. | |||
Create a table | Create a table | ||
| Line 29: | Line 31: | ||
}} | }} | ||
== See Also == | === Scripting === | ||
There are two ways to pipe queries for execution. Either pipe it directly with <code>|</code> or by piping a file into stdin with <code>< filename</code>. | |||
{{Highlight | |||
| code = # echo "INSERT ..." {{!}} sqlite3 database.db | |||
# sqlite3 database.db < queries.sql | |||
| lang = terminal | |||
}} | |||
==See Also== | |||
* CLI: https://sqlite.org/cli.html | *CLI: https://sqlite.org/cli.html | ||
* Types: https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm | *Types: https://www.tutorialspoint.com/sqlite/sqlite_data_types.htm | ||
Revision as of 21:41, 3 July 2020
Quick Usage
Run sqlite3. At the prompt, you can open (or create a new) database with .open. Use .tables to show tables.
You can then begin entering SQL queries. For scripting, you can also pipe these queries to the sqlite3 program.
Create a table
create table bandwidth (
mac character(12),
time datetime,
tx_bytes unsigned int,
rx_bytes unsigned int
);
Create a composite key
CREATE UNIQUE INDEX pk_index ON "bandwidth" ("mac", "time");
Find the current hour as a timestamp, which you can insert as a datetime field?
select strftime('%s', 'now') - (strftime('%s','now')%3600);
To insert a record:
insert into bandwidth ('mac', 'time', 'tx_bytes', 'rx_bytes')
VALUES ('000c2958db2f', strftime('%s', 'now') - (strftime('%s','now')%3600), 100, 100);
Scripting
There are two ways to pipe queries for execution. Either pipe it directly with | or by piping a file into stdin with < filename.
# echo "INSERT ..." | sqlite3 database.db
# sqlite3 database.db < queries.sql