SQLite Reminders

This will come in handy...

 

sqlite3 some.db - open a db, or create one and open it if id doesn't exist

.tables - show all tables in the db

.exit - quit sqlite3

.help - show help

.show - lists various config settings

 

.headers ON - displays column names

.mode columns - puts results in columns

.width 22, 18 - sets column widths

Note: normally you see .headers ON and .mode columns used together

 

pragma table_info(some_table) - gets info about a table

select pragma user_version - gets the db version

pragma user_version = 2 - sets db version to 2

 

.dump sometable - lists all sql to recreate the table (create statements and inserts)

.output sometable.sql - dumps sometable to a file named sometable.sql

.read sometable.sql - dumps the file onto the screen

 

sqlite3 -html some.db - now all your select queries will be returned as html tables

 

There is nothing stopping you from using any data type for you columns. It could be 'blah' for all sqlite cares. If you do this, you might not get the results you expect in queries when using order by (and probably other stuff too). In one case we noticed that we couldn't sort our datetime column properly. Later we discovered that there was no datetime type in sqlite, and it treated the values as strings.

 

Add a Comment...

 

Comments

Great blog here! Also your site loads up fast! What web host are you using? Can I get your affiliate link to your host? I wish my web site loaded up as fast as yours lol fotbollströjor - 4/26/2017

BertiebzO Polen PGJFaecrs LouisPoin Atletico Madrid DorotheaP YJRSadye Mexico MartinaMc AlyciaSpa Belgien WalterBus MattClift Colombia SusannaSt CareyGrub BVB Borussia Dortmund DeniceHar LeandroCh Chelsea Jaclynrtg StephenRo England MerrillPr AlicaWmge Rumanien BirgitTal BuddyCann Leicester City BrettMccl - 3/07/2017