SQLite

# archlinux install sqlite
sudo pacman -S sqlite

# ubuntu
sudo apt install sqlite3

# 打开一个已经存在的数据库
sqlite3 /var/lib/enx-api/enx.db

# 启动 sqlite
sqlite3

# 列出数据库文件和名字
.databases

# query schema, 列出所有的表
.table

# 查看表结构, 注意, 表名后面没有分号
.schema table0

# 创建数据库
sqlite3 /data/rssx/rssx.db


# insert data
insert into table_0 (name) values ('foo');

CREATE TABLE if not exists table_1 (name varchar(50) collate nocase PRIMARY KEY, create_time timestamp DEFAULT NULL);
insert into table_1 (name) values ('foo');
insert into table_1 (name) values ('Foo'); -- Runtime error: UNIQUE constraint failed: table_1.name (19)

# help 
.help

# show tables
.tables

# insert

# alter table, rename
alter table feed
    rename to feeds;

-- query that returns the size of a table in a SQLite database
-- 空表的 size 是 4096
select sum("pgsize") from "dbstat" where name='table0';

-- drop table
drop table table0;

-- SQLite: 基础 SQL 支持
SELECT * FROM users WHERE age > 18;

-- DuckDB: 强大的分析函数
SELECT 
    date_trunc('month', create_time) as month,
    count(*) as user_count,
    percentile_cont(0.95) WITHIN GROUP (ORDER BY age) as p95_age
FROM users
GROUP BY month
WINDOW w AS (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW);

-- SQLite: 有限的导入导出
.mode csv
.import file.csv table_name

-- DuckDB: 原生支持多种格式
COPY users TO 'output.parquet' (FORMAT PARQUET);
SELECT * FROM 'data.parquet';
SELECT * FROM 'data.json';
SELECT * FROM read_csv_auto('*.csv');

-- 查询: 按月统计用户数并计算平均值
SELECT 
    strftime('%Y-%m', create_time) as month,
    COUNT(*) as total,
    AVG(score) as avg_score
FROM users
GROUP BY month;

INSERT INTO users VALUES ('uuid', 'name', 'email', 'password', NOW());

import sqlite3
import duckdb

# SQLite 用于事务数据
sqlite_conn = sqlite3.connect('app.db')
sqlite_conn.execute("INSERT INTO orders VALUES (...)")

# DuckDB 用于分析
duck_conn = duckdb.connect()
duck_conn.execute("""
    SELECT date_trunc('day', order_time) as day,
           SUM(amount) as revenue
    FROM sqlite_scan('app.db', 'orders')
    GROUP BY day
""")

sql-statement ::= CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name
ON table-name ( column-name [, column-name]* )

CREATE TABLE IF NOT EXISTS users (
  id char(36) PRIMARY KEY NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  password VARCHAR(100) NOT NULL,
  create_time timestamp NOT NULL
);

-- 建表
CREATE TABLE if not exists table_0 (id char(36) PRIMARY KEY NOT NULL, name varchar(50) DEFAULT NULL, create_time timestamp DEFAULT NULL);
CREATE TABLE if not exists table_0 (name varchar(50) PRIMARY KEY, create_time timestamp DEFAULT NULL);

CREATE UNIQUE INDEX IF NOT EXISTS index_user_name on users (name);
CREATE INDEX IF NOT EXISTS index_user_email ON users (email);

CREATE TABLE IF NOT EXISTS comment(
  id INTEGER AUTO_INCREMENT NOT NULL,
  content VARCHAR(300) NOT NULL,
  user_id char(36)  NOT NULL,
  create_time timestamp NOT NULL,
  update_time timestamp NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS tree_path(
  parent INTEGER NOT NULL,
  child INTEGER NOT NULL,
  PRIMARY KEY (parent, child)
);

INSERT INTO users VALUES ('c31f5e0e-0e0c-4731-97dc-9c6675a0068c','admin','admin@admin.com','$2a$10$AR7t1b/kgGS2oiTrlrW2C.JkVAOT3ZviKj.2zvWZIm0lBnsOrTuX2', now())