Category Archives: Databases - Page 2

My SQLite3 cheat sheet

Start SQLite

sqlite3 <filename>

Show all tables

.tables

Show whole database as SQL (kind of a “describe” function in MySQL)

.schema

Create new table example

CREATE TABLE users (id INTEGER PRIMARY KEY,login TEXT,pass TEXT);

The ‘id’ column is here what you call autoincrement in MySQL – if you assign NULL to this column the value will be incremented by 1 from last value

Valid columntypes:
TEXT
INTEGER
REAL
BLOB

Rename table example

ALTER TABLE users RENAME TO client_users

This renames table users to client_users

SELECT example

SELECT * FROM table_name WHERE column_name = value

Selects all rows in table table_name where column column_name is equal to value

UPDATE example

UPDATE table_name SET column_name = update_value WHERE some_column = some_value

Updates column_name with the value update_value in table table_name, on row/s where some_column is equal to some_value

DELETE example

DELETE FROM table_name WHERE column_name < 4

Deletes all rows from table_name where column_name is less than 4

INSERT example

INSERT INTO table_name (column1, column4, column7) VALUES (value1, 'value2', value3)

Inserts a new row into table table_name with values value1 in column1, value2 in column4 and value3 in column7 (other columns in table are left null or with default values, if set)

Read sql from file

sqlite3 my.db < filewithsql

This will read and execute the sql statements from the file filewithsql.

My PostgreSQL cheat sheet

Here I have collected all common commands and SQL for PostgreSQL that I use in my work

Help on psql slash commands
\?

Help on SQL commands
\h

List databases
\l

Connect to different database
\c database

Eqivavent to DESCRIBE table
\d+ tablename

List database settings
\set

List all tables
\dt

List users
\du

Quit psql command line
\q

Create user

CREATE USER niklas WITH PASSWORD 'myPassword';

Grant all privilegies on database to a user

GRANT ALL PRIVILEGES ON DATABASE myDatabase to niklas;

Grant alla privilegies on table to a user

GRANT ALL PRIVILEGES ON TABLE myTable to niklas;

Create table syntax (standard)

CREATE TABLE myTable (id serial, time integer);

Insert statement

INSERT INTO public.user (id, login, password) VALUES (1, 'test','test');# NOTE the single quotes!

Update statement

UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';# NOTE the single quotes!

Delete statement

DELETE FROM films WHERE producer_id = 1;

I hope to come back to this sheet to add stuff as time goes by

How to drop a column in SQLite 3

As you might know, SQLite 3 does not support dropping columns from tables with ALTER TABLE command so we have to do a workaround instead. The idea is to create a new table with all the columns (and data) that we want to keep and then drop the old table and rename the new table with the old name.
Here is an example:

First we create a new table with all the columns and data we want to save

CREATE TABLE my_table_temp AS (SELECT id, user_id, latitude, longitude FROM my_table);

Then we drop the old table

DROP my_table;

And finally we rename our new table with the old name

ALTER TABLE my_table_temp RENAME TO my_table

Done!

Tested on OSX 10.7.5 and SQLite v3.7.11