SQLite简介与安装环境配置分享（附多方案连接测试）

.help -- 获取帮助
.exit -- 退出SQLite

.open database_name.db -- 打开或创建数据库
.close -- 关闭当前数据库

.tables -- 显示所有表
.schema table_name -- 查看表结构

.headers on/off -- 开/关列名显示
.dbinfo -- 查看当前数据库大小

CREATE TABLE table_name (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    column1 TEXT
);

INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

CREATE TABLE example (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
) WITHOUT ROWID;

我的目录在D盘下，故需要先切换盘符，再切换到对应的解压目录
> D:
> cd D:\Real_Path\sqlite-dll-win-x64-3460000
> lib /def:sqlite3.def /machine:x64

#include <iostream>
#include "sqlite3.h"

// 回调函数，用于处理查询结果
static int callback(void* NotUsed, int argc, char** argv, char** azColName) {
    for (int i = 0; i < argc; i++) {
        std::cout << azColName[i] << ": " << (argv[i] ? argv[i] : "NULL") << std::endl;
    }
    std::cout << std::endl;
    return 0;
}

int main() {
    sqlite3* db;
    char* zErrMsg = nullptr;
    int rc;

    // 打开数据库连接，如果不存在则创建一个
    rc = sqlite3_open("test.db", &db);
    if (rc) {
        std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
        return rc;
    }
    else {
        std::cout << "Opened database successfully" << std::endl;
    }

    // 创建表
    const char* sqlCreateTable = "CREATE TABLE IF NOT EXISTS COMPANY("
        "ID INT PRIMARY KEY NOT NULL,"
        "NAME TEXT NOT NULL,"
        "AGE INT NOT NULL,"
        "ADDRESS CHAR(50),"
        "SALARY REAL);";

    rc = sqlite3_exec(db, sqlCreateTable, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    else {
        std::cout << "Table created successfully" << std::endl;
    }

    // 插入数据
    const char* sqlInsertData = "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (1, 'Paul', 32, 'California', 20000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (2, 'Allen', 25, 'Texas', 15000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (3, 'Teddy', 23, 'Norway', 20000.00); "
        "INSERT INTO COMPANY (ID, NAME, AGE, ADDRESS, SALARY) "
        "VALUES (4, 'Mark', 25, 'Rich-Mond', 65000.00);";

    rc = sqlite3_exec(db, sqlInsertData, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    else {
        std::cout << "Records inserted successfully" << std::endl;
    }

    // 查询数据
    const char* sqlSelectData = "SELECT * FROM COMPANY;";
    rc = sqlite3_exec(db, sqlSelectData, callback, 0, &zErrMsg);
    if (rc != SQLITE_OK) {
        std::cerr << "SQL error: " << zErrMsg << std::endl;
        sqlite3_free(zErrMsg);
    }
    else {
        std::cout << "Operation done successfully" << std::endl;
    }

    // 关闭数据库连接
    sqlite3_close(db);
    return 0;
}

import sqlite3  
  
# 连接到SQLite数据库（如果数据库文件不存在，会自动创建一个）  
conn = sqlite3.connect('example.db')  
  
# 创建一个游标对象，用于执行SQL语句  
cursor = conn.cursor()  
  
# 创建表  
cursor.execute('''  
CREATE TABLE IF NOT EXISTS users (  
id INTEGER PRIMARY KEY,  
name TEXT NOT NULL,  
age INTEGER NOT NULL  
)  
''')  
  
# 插入数据  
cursor.execute('''  
INSERT INTO users (name, age)  
VALUES ('Alice', 30)  
''')  
  
cursor.execute('''  
INSERT INTO users (name, age)  
VALUES ('Bob', 25)  
''')  
  
# 提交事务  
conn.commit()  
  
# 查询数据  
cursor.execute('SELECT * FROM users')  
rows = cursor.fetchall()  
  
# 打印查询结果  
for row in rows:  
print(row)  
  
# 关闭游标和连接  
cursor.close()  
conn.close()