62 lines
No EOL
1.7 KiB
Markdown
62 lines
No EOL
1.7 KiB
Markdown
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
|
|
```sql
|
|
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'))
|
|
);
|
|
```
|
|
|
|
```sql
|
|
insert into event_log (id,event_name, unix_timestamp)
|
|
values(1,'event1',unixepoch());
|
|
```
|
|
|
|
|
|
|
|
upsert
|
|
```sql
|
|
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
|
|
```sql
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT
|
|
``` |