Starting off with sqlite

I am crawling up to speed on SQLite. Luckily it's easy!

The really easy on-a-Mac-way to create a SQLite database is to use SqliteQuery (freeware). It uses sqlite3 already installed on a Mac, and it gives you a window to enter SQL commands. However, I quickly noticed that SqliteQuery converts everything to lowercase. So, in order to maintain case-sensitivity, I recreated my test database from the command line.

The command sqlite3 is in /usr/bin. That was in my path, so I just needed to give it the SQLite file.

sqlite3 ~/Documents/SQLite/Birthdays.sqlite

To see the table names,

sqlite> .tables ''

To see the existing structure,

sqlite> .schema birthdays

That gives me the SQL code to create the table. The data are

sqlite> .dump birthdays

I ran some awk reality checks, and converted between SQL dump and CSV with sed.

The key concept to sqlite3 today is that the commands that start with a dot (like .help and .quit) are sqlite-specific, while the rest is just SQL (commands that don't start with a dot and that must end with a semi-colon).

Other than creating tables and inserting values (you can see this SQL code when you use .dump table-name), the other useful SQL command is select * from table-name; to see what's in the table.