1. 分组与聚合

聚合（aggregate）：对一组数据进行计算、合并或汇总，最终得到一个单一结果的过程。在 SQLite 中，常用聚合函数有：

count ：计数（典型如：统计符合条件的记录有几条）
sum ：总和：忽略统计目标字段为空值（NULL）的记录，因此返回结果也有可能为NULL（比如，查无符合条件的待统计记录）
total : 求和：将目标字段为空值（NULL）的记录数据，视为浮点数：0.0 参与统计，因此返回结果不可能为NULL
avg ：求平均值，结果可为NULL
max/min : 求一组记录中指定数据中最大的最小的值（典型如：求时间的最早或最晚）
group_concat : 将一组记录的指定字段的值（通常为字符串类型）拼成更长的字符串

聚合操作通常基于按字段值分组后的数据。

2. 联合查询

联合（JOIN）查询也称为连接查询，其连接方式又包括：内连接（INNER JOIN）、左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）、交叉连接（CROSS JOIN）以及 sqlite 不直接支持的全连接（FULL JOIN）。

sqlite join分类

正如名字所表达，联合查询允许我们跨多张表查询，即结果数据包含来自多张表的字段。

3. 预处理 / prepare

想要查询名为 “Tom” 的用户所输入的密码（“1234”）是否正确（通常用于登录操作），SQL语句内容如下：

SELECT id, nick FROM users WHERE nick='Tom' AND pwd = '1234';

如果 Tom 的在系统中的密码不是 “1234”，则上述查询将查无结果。但入侵者可以输入密码内容为 'OR '1'='1，如果开发者在组织上述 SQL 语句时，仅是简单的拼接（比如使用 + 相加），将得到如下 SQL 语句：

SELECT id, nick FROM users WHERE nick='Tom' AND pwd = '' OR '1'='1'

查询条件变成：昵称是 Tom 并且密码是空串 ** 或者 ** 1=1 —— 这将永远成立！

这种入侵手段称为 “SQL 注入”， prepare 是用于解决这一安全问题的关键方法。在 sqlite-orm 中，prepare 也可以帮助我们实现对同一 SQL 语句进行一次 prepare（预备），然后多次 execute （执行），从而提升程序性能。

4. 建库配置

关于建库（表、字段），之前我们已经学习过：

设定某字段为主键且自增：primary_key().autoincrement()；
设定某字段的值，必须全表唯一： unique()；
设定某字段的值，可为NULL，或不可为NULL：not_null() / null()；
设定某字段在库中的默认值：default_value(默认值)。

本小节我们还将学习：① 如何创建索引（含 index 和 unique_index）、② 如何创建外键（foreign_key）、③ 从内存表切换为实体文件表。

创建索引及外键时，需注意两个和代码次序有关的“坑”——

在 make_storage() 中，要先写创建索引的代码，再写创建字段的代码；
在 make_table () 中，要先写完所有创建列的代码，再写创建外键的代码。

5. 完整代码

CMakeLists.txt

cmake_minimum_required(VERSION 3.15)

project(helloSQLiteORM) 

SET(CMAKE_CXX_STANDARD 17)

# 请将路径更换为你的路径
FIND_LIBRARY(LIBSQLite3 sqlite3 PATHS "C:/msys64/ucrt64/lib")

ADD_EXECUTABLE(${PROJECT_NAME}_2 main2.cpp)
TARGET_LINK_LIBRARIES(${PROJECT_NAME}_2 ${LIBSQLite3})
TARGET_INCLUDE_DIRECTORIES(${PROJECT_NAME}_2 PRIVATE "C:/msys64/ucrt64/include")\

main2.cpp

#include <iostream>
#include <iomanip>
#include <sstream>
#include <optional>
#include <string>
#include <exception>
#include <vector>

#include <sqlite_orm/sqlite_orm.h>

void line(int len = 80, char c = '-')
{
    std::cout << '\n' << std::string(len, c) << std::endl;
}

// 家庭成员
struct FamilyMember 
{
    int id = -1;  // 主键，自增长
    
    std::string role;  // 角色昵称，不可重复
    std::optional<std::string> tel;  // 手机，NULL，可以重复

    std::string birthday;  // 出生日期，不得为空
    std::string avatar = "0.png";  // 头像，NOT NULL，默认为 "0.png"
    
    bool is_admin = false; // 是否管理员，默认 false
};

void print(FamilyMember const& m)
{
    std::cout << "#" << m.id << ' ' << std::setw(10) << std::left << m.role;
    std::cout << std::setw(16) << std::left << (m.is_admin ? "\t管理员" : "\t普通用户") << "\t";
    std::cout << m.birthday << "\t" << std::setw(11) << (m.tel? *m.tel : "~") << "\t" << m.avatar;

    line();
}

// 行为名称
std::string const ActionNames[12] = 
{
    "未知", "早起", "早睡", "大解", "小解", "洗澡", "正餐", "零食", "健身", "服药", "晚睡", "熬夜"
};

std::string const& GetActionName(int i)
{
    static std::string const unknow = "！非法！";

    if (i < 0 || i >= std::size(ActionNames))
    {
        return unknow;
    }

    return ActionNames[i];
}

// 家庭家庭成员活动记录
struct FamilyAction
{
    int id = -1;

    int member_id = 0; // 成员ID

    std::string role; // 角色，可重复

    int action = 0; // 活动类型
    int rating = 0; // 行为评分（分正负）

    std::string datetime; // 活动的日期时间
};

void print(FamilyAction const& a)
{
    auto role_with_member_id = a.role + "(" + std::to_string(a.member_id) + ")";

    std::cout << "#" << a.id << " " << std::setw(10) << std::left << role_with_member_id << "\t"        
        << GetActionName(a.action) << '\t' << a.rating << '\t' << a.datetime;

    line();
}

namespace orm = sqlite_orm;

auto create_storage(std::string db_file)
{
    return orm::make_storage(std::move(db_file)
        // “坑”：创建索引的代码，要写在（创建表）的代码前面 :(
        // 因为：sync_schema 里的代码被执行的次序，是从后往前
        , orm::make_unique_index("idx_family_members_role", &FamilyMember::role)
        , orm::make_index("idx_family_member_birthday", &FamilyMember::birthday)

        , orm::make_table("family_members"
            , orm::make_column("id", &FamilyMember::id, orm::primary_key().autoincrement())
            , orm::make_column("role", &FamilyMember::role, orm::unique())
            , orm::make_column("tel", &FamilyMember::tel)
            , orm::make_column("birthday", &FamilyMember::birthday, orm::not_null())
            , orm::make_column("avatar", &FamilyMember::avatar, orm::default_value("0.png"))
            , orm::make_column("is_admin", &FamilyMember::is_admin, orm::default_value(false))
         )  // end talbe: family_members

         , orm::make_index("idx_family_actions_member_id", &FamilyAction::member_id)
         , orm::make_index("idx_family_actions_role", &FamilyAction::role)
         , orm::make_index("idx_family_actions_create_time", &FamilyAction::datetime)

         , orm::make_table("family_actions"
            , orm::make_column("id", &FamilyAction::id, orm::primary_key().autoincrement())
            , orm::make_column("mid", &FamilyAction::member_id) // 对应的成员ID
            , orm::make_column("role", &FamilyAction::role, orm::not_null())
            , orm::make_column("action", &FamilyAction::action, orm::default_value(0))
            , orm::make_column("rating", &FamilyAction::rating, orm::default_value(0))
            , orm::make_column("create_time", &FamilyAction::datetime, orm::default_value(""))

            // 创建外键 （必须在所有列创建语句之后写）
            , orm::foreign_key(&FamilyAction::member_id).references(&FamilyMember::id)
		.on_delete.cascade()
            )
        );
}

using Storage = decltype(create_storage(""));

#define i_am_here std::cout << "\n[" << __FUNCTION__ << "]\n"

// 准备数据
void prepare_data(Storage& storage)
{
    FamilyMember members [] = 
    {
        {.role="爸爸", .tel = "13900001234", .birthday = "1980-04-11"}
        , {.role="妈妈", .tel = "13411118888", .birthday = "1984-11-21", .is_admin = true}
        , {.role="大宝", .tel = "19901111111", .birthday = "2011-05-20", .avatar = "rabbit.png"}
        , {.role="二宝",   .birthday = "2020-04-07", .avatar = "baby.png"}
        , {.role="二大爷", .birthday = "2025-09-01", .avatar = "robot.jpg"}
    };

    for (auto& m : members)
    {
        m.id = storage.insert(m);
    }

    auto getMemberID = [&](std::string const& role) -> int
    {
        auto it = std::find_if(std::cbegin(members), std::cend(members), [&](auto const& m)
        {
            return m.role == role;
        });

        return (it != std::cend(members)? it->id : 0);
    };

    FamilyAction actions [] =
    {
        {.role="爸爸", .action=1, .rating=1, .datetime="2025-09-10 06:30:56"}, // 早起
        {.role="爸爸", .action=3, .rating=-1, .datetime="2025-09-10 06:55:08"},  // 大解
        {.role="妈妈", .action=1, .rating=1, .datetime="2025-09-10 06:45:12"},  // 早起
        {.role="大宝", .action=1, .rating=1, .datetime="2025-09-10 07:15:44"},  // 早起
        {.role="大宝", .action=6, .rating=0, .datetime="2025-09-10 07:50:11"},  // 正餐（早餐）
        {.role="妈妈", .action=4, .rating=0, .datetime="2025-09-10 08:15:00"},  // 小解
        {.role="爸爸", .action=6, .rating=1, .datetime="2025-09-10 08:00:00"},  // 正餐（早餐）
        {.role="妈妈", .action=6, .rating=0, .datetime="2025-09-10 08:15:00"},  // 正餐（早餐）
        {.role="二宝", .action=3, .rating=0, .datetime="2025-09-10 08:30:40"},  // 大解
        {.role="二宝", .action=6, .rating=1, .datetime="2025-09-10 08:45:00"},  // 正餐（早餐）
        {.role="爸爸", .action=8, .rating=1, .datetime="2025-09-10 09:30:00"},  // 健身
        {.role="妈妈", .action=8, .rating=2, .datetime="2025-09-10 10:00:00"},  // 健身
        {.role="大宝", .action=7, .rating=-1, .datetime="2025-09-10 10:30:00"},  // 零食
        {.role="爸爸", .action=6, .rating=0, .datetime="2025-09-10 12:00:00"},  // 正餐（午餐）
        {.role="妈妈", .action=6, .rating=1, .datetime="2025-09-10 12:15:00"},  // 正餐（午餐）
        {.role="二宝", .action=6, .rating=1, .datetime="2025-09-10 12:45:00"},  // 正餐（午餐）
        {.role="二大爷", .action=6, .rating=0, .datetime="2025-09-10 13:00:00"}, // 正餐（充电）
        {.role="二宝", .action=7, .rating=-2, .datetime="2025-09-10 15:00:00"},  // 零食
        {.role="爸爸", .action=5, .rating=0, .datetime="2025-09-10 18:00:00"},  // 洗澡
        {.role="妈妈", .action=5, .rating=0, .datetime="2025-09-10 18:30:00"},  // 洗澡
        {.role="大宝", .action=5, .rating=1, .datetime="2025-09-10 19:00:00"},  // 洗澡
        {.role="二宝", .action=5, .rating=2, .datetime="2025-09-10 19:30:00"},  // 洗澡
        {.role="爸爸", .action=6, .rating=0, .datetime="2025-09-10 19:00:00"},  // 正餐（晚餐）
        {.role="妈妈", .action=6, .rating=1, .datetime="2025-09-10 19:15:00"},  // 正餐（晚餐）
        {.role="大宝", .action=6, .rating=1, .datetime="2025-09-10 19:30:00"},  // 正餐（晚餐）
        {.role="二宝", .action=6, .rating=1, .datetime="2025-09-10 19:45:00"},  // 正餐（晚餐）
        {.role="爸爸", .action=9, .rating=-1, .datetime="2025-09-10 22:00:00"},  // 服药
        {.role="爸爸", .action=2, .rating=2, .datetime="2025-09-10 22:00:00"},  // 早睡
        {.role="妈妈", .action=2, .rating=1, .datetime="2025-09-10 22:15:00"},  // 早睡
        {.role="二宝", .action=2, .rating=0, .datetime="2025-09-10 21:00:00"},  // 早睡
        {.role="大宝", .action=11, .rating=-3, .datetime="2025-09-11 00:25:30"}, // 熬夜
    };

    for(auto& a : actions)
    {
        a.member_id = getMemberID(a.role);
        a.id = storage.insert(a);        
    }

    for (auto const& a : actions)
    {
        std::cout << storage.dump(a) << std::endl;
    }
}

// 查询全家活动记录中，最早和最晚的两条
void min_max_time_actions(Storage& storage)
{
    i_am_here;

    auto first = storage.min(&FamilyAction::datetime);
    auto last = storage.max(&FamilyAction::datetime);

    if (first && last)
    {
        std::cout << "家庭行为最早发生于：" << *first 
		<< "，最晚结束于：" << *last << std::endl;
    }
}

// 查询总记录数，以及爸爸的活动次数
void count_on_daddy_actions(Storage& storage)
{
    i_am_here;

    int all = storage.count<FamilyAction>(); // select count(*)

    // select count(id) from family_actions where role = "爸爸"
    int c = storage.count(&FamilyAction::id
	, orm::where(orm::c(&FamilyAction::role) == "爸爸"));

    std::cout << "在 " << all << " 条记录中，爸爸的活动记录有 " 
		<< c << " 条" << std::endl;
}

// 查询每个角色的活动记录数
void select_count_on_memebers_actions(Storage& storage)
{
    i_am_here;

    auto rows = storage.select(orm::columns(&FamilyAction::role, orm::count(&FamilyAction::id))
        , orm::group_by(&FamilyAction::role));

    std::cout << "角色\t活动记录数";
    line();

    for (auto const&  row : rows)
    {
        std::cout << std::get<0>(row) << '\t' << std::get<1>(row);
        line();
    }
}

// 得分排行榜：累计每个角色的得分，并且从高到低排列
void rates_ranking(Storage& storage)
{
    i_am_here;

    auto rating_sum = orm::sum(&FamilyAction::rating);

    auto rows = storage.select(orm::columns(&FamilyAction::role, rating_sum)
        , orm::group_by(&FamilyAction::role)
        , orm::order_by(rating_sum).desc());
    
    std::cout << "名次\t角色\t得分";
    line();

    int o = 1;
    for (auto const& row : rows)
    {                
        std::cout << o << '\t' << std::get<0>(row) << '\t' << *std::get<1>(row);
        line();
        ++o;
    }
}

// 连接成员与活动表，通过 role 得到带头像的活动信息记录
void join_action_on_role_with_avatar(Storage& storage)
{
    i_am_here;

    auto rows = storage.select(
        orm::columns(
            &FamilyMember::id, &FamilyMember::role, &FamilyMember::tel, &FamilyMember::avatar // 0,1,2,3
                , &FamilyAction::id, &FamilyAction::action, &FamilyAction::rating) //4,5,6
            , orm::join<FamilyAction>(orm::on(orm::c(&FamilyMember::role) == &FamilyAction::role))
            , orm::where(orm::c(&FamilyAction::rating) > 0)
        ); 

    std::cout << "AID\tMID\t角色\t活动\t评分\t联系电话\t头像"; // 4,0,1,5,6,2,3
    line();

    for (auto r : rows)
    {
        std::cout << std::get<4>(r) << '\t' 
		<< std::get<0>(r) << '\t' << std::get<1>(r); // 4,0,1
        std::cout << '\t' << GetActionName(std::get<5>(r)) 
		<< '\t' << std::get<6>(r) << '\t'; // 5, 6
        std::cout << std::setw(11) << std::get<2>(r).value_or("~") 
		<< '\t' << std::get<3>(r);
        line();
    }
}

// prepare & execute 语句
void prepare_and_execute(Storage& storage)
{
    // 使用 ORM 方式，取得家庭活动记录中，create_time 的时间部分:
    // substr : 从指定字符位置开始，取指定长度的片段，注意：下标从1开始
    // -8 : HH:MM:SS （正向取的话，相当从 12 开始取）
    auto times = storage.select(orm::substr(&FamilyAction::datetime, -8)
	, orm::from<FamilyAction>(), orm::limit(4));
    for (auto const& t : times)
    {
        std::cout << t << '\n';
    }

    line(80, '=');

    // 使用预备（预处理）语句，取指定时间（小时）以后，到次日 0 点前的活动记录
    auto selectStatement = storage.prepare(orm::select(
            orm::columns(&FamilyAction::id, &FamilyAction::role, &FamilyAction::datetime), // columns
                orm::where(orm::substr(&FamilyAction::datetime, -8) >= "12:00:00")
        ) // select
    ); // prepare

    std::cout << "\nORM 自行组装的 SQL 语句：\n";
    std::cout << storage.dump(selectStatement) << "\n";
    
    std::cout << "\n底层数据库 sqlite 生成的 SQL 语句：\n";
    std::cout << selectStatement.sql() << "\n";

    std::cout << "\nPreare 语句存在的参数：\n";
    std::cout << orm::get<0>(selectStatement) << "\n";
    std::cout << orm::get<1>(selectStatement) << "\n";

    auto rows = storage.execute(selectStatement);

    std::cout << "\n12点之后的活动：\n";
    for (auto const& row : rows)
    {
        std::cout << std::get<0>(row) << '\t' << std::get<1>(row) << '\t' << std::get<2>(row);
        line();
    }

    std::cout << "\n已经执行：\n";
    std::cout << selectStatement.expanded_sql() << std::endl;

    orm::get<1>(selectStatement) = "18:00:00";
    rows = storage.execute(selectStatement);

    std::cout << "\n16点之后的活动：\n";
    for (auto const& row : rows)
    {
        std::cout << std::get<0>(row) << '\t' << std::get<1>(row) << '\t' << std::get<2>(row);
        line();
    }
}

// 级联打印
void cascade_print(Storage& storage)
{
    std::vector<std::string> roles_in_members; // 还在 family_members 中的角色

    std::cout << "\n存在关联的成员活动记录：\n";

    for (auto m : storage.iterate<FamilyMember>())
    {
        roles_in_members.push_back(m.role);
        print(m);

        for (auto a : storage.iterate<FamilyAction>(orm::where(orm::c(&FamilyAction::member_id) == m.id)))
        {
            std::cout << '\t';
            print(a);
        }
    }

    std::cout << "\n失去角色关联的活动记录：\n";

    int lost = 0;
    for (auto a : storage.iterate<FamilyAction>(orm::where(not orm::in(&FamilyAction::role, roles_in_members))))
    {
        ++lost;
        print(a);
    }

    if (lost == 0)
    {
        std::cout << "无\n";
    }
}

// 级联删除
void cascade_delete(Storage& storage)
{
    std::cout << "当前库中数据：\n";
    cascade_print(storage);

    std::string role_would_delete;
    std::cout << "你希望删除哪位成员（角色）： ";
    std::cin >> role_would_delete;
    std::cin.ignore(); // 吃掉回车

    std::cout << "确认要删除 " << role_would_delete << " 及其所有活动记录吗？本操作不可恢复！（N/y） ：";
    int r = std::cin.get();

    if (r != 'Y' && r != 'y')
    {
        return;
    }

    storage.remove_all<FamilyMember>(orm::where(orm::c(&FamilyMember::role) == role_would_delete));

    std::cout << "删除指定角色后，库中数据：\n";
    cascade_print(storage);
}

int main()
{
    auto storage = create_storage("we_are_family.db");

    try
    {
        storage.drop_table_if_exists("family_members");
        storage.drop_table_if_exists("family_actions");

        storage.sync_schema();
        prepare_data(storage);

        // 示例 15 ：最早最晚的活动时间
        min_max_time_actions(storage);

        // 示例 16: 爸爸的活动记录数信息
        count_on_daddy_actions(storage);

        // 示例 17: 展现每个角色的活动记录数
        select_count_on_memebers_actions(storage);

        // 示例 18 : 家庭活动得分排行榜
        rates_ranking(storage);

        // 示例 19： 列出得正分的活动记录，附电话和头像
        join_action_on_role_with_avatar(storage);

        // 示例 20:  prepare 语句（顺带：偷窥 orm 和 db 各自生成的 SQL 字符串）
        prepare_and_execute(storage);

        // 示例 21: 以树形方式，分组打印各个成员的活动记录
        cascade_print(storage);

        // 示例 22: 级联删除
        cascade_delete(storage);
    }
    catch(const std::exception& e)
    {
        std::cerr << e.what() << '\n';
    }
}