joplin-notes/joplin notion extension/Web Development/sql/sqlite.md

sqlite

• get list of databases : .database or .databases
• get list of tables : .tables
• get schemas : .schema , for particular table : schema <TABLE_NAME>
• to show field name when using select query, make sure to switch headers on by .headers on

ddl commands

alter tables

• ALTER TABLE old_table_name RENAME TO new_table_name;
• ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;
• ALTER TABLE table_name ADD COLUMN column_name datatype;
• ALTER TABLE table_name DROP COLUMN column_name;
• ALTER TABLE songdata add COLUMN is_bookmarked INTEGER DEFAULT 0
• ALTER TABLE Employees ALTER COLUMN Status SET DEFAULT 'Active' (requires new version)
• ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL (requires new version) create tables
• foreign key constraint FOREIGN KEY (lyrics_url) REFERENCES lyricsUrl(lyrics_url)  

dml ones

timestamp https://www.sqlite.org/lang_datefunc.html

CREATE TABLE event_log (
  id INTEGER PRIMARY KEY,
  event_name TEXT,
  
  -- Option A: Stores as human-readable TEXT (UTC time)
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  
  -- Option B: Stores as a Unix INTEGER timestamp (Seconds since 1970)
  unix_timestamp INTEGER DEFAULT (strftime('%s', 'now'))
);

insert into event_log (id,event_name, unix_timestamp)
values(1,'event1',unixepoch());

 

upsert

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON CONFLICT (conflict_column)
DO UPDATE SET column1 = excluded.column1, column2 = excluded.column2;   

 

auto increment only possible for integer columns declared as primary key

id INTEGER PRIMARY KEY AUTOINCREMENT