Niklas Tech Blog

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.