SQLite3 Snippets
~/.sqliterc
customizations
The default sqlite3
shell can read from a dotfile! Here are some nice customizations for it
This file can reloaded into the shell with the command .read <filename>
. This .read
command is also useful for reading sql scripts.
Simple table example
Cause I forget the syntax every once in a while :)
DROP TABLE my_table;
CREATE TABLE my_table
(
my_variable INT,
my_other VARCHAR(20)
);
INSERT INTO my_table VALUES (1, 'one');
INSERT INTO my_table VALUES (2, 'two');
SELECT * FROM my_table;
Find duplicate columns in a table
I want to write somewhere an easy inner join to find records with duplicate columns.
SELECT mt1.name
FROM
my_table AS mt1
JOIN my_table as mt2 ON mt1.name = mt2.name AND mt1.id != mt2.id
See contents of a table
From StackOverflow:
PRAGMA table_info([tablename]);