当前位置：首页 > 行业动态 > 正文

c数据库两张表关联创建

admin
行业动态
2025-02-21
6

在C语言中，通过使用结构体和指针可以实现两张表的关联创建。

在C语言中，操作数据库通常需要借助于一些数据库库，如MySQL的C API、SQLite等，这里以SQLite为例，展示如何在C语言中创建两张表并进行关联。

SQLite简介

SQLite是一个轻量级的嵌入式关系型数据库，它不需要独立的服务器进程，可以直接嵌入到应用程序中。

安装SQLite

在使用SQLite之前，需要确保已经安装了SQLite库，可以通过以下命令安装（以Ubuntu为例）：

sudo apt-get install libsqlite3-dev

创建数据库和表

以下是一个简单的示例，展示如何在C语言中创建两张表并进行关联。

代码示例

#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
// 回调函数，用于处理查询结果
static int callback(void *NotUsed, int argc, charargv, charazColName) {
    for (int i = 0; i < argc; i++) {
        printf("%s = %s
", azColName[i], argv[i] ? argv[i] : "NULL");
    }
    printf("
");
    return 0;
}
int main() {
    sqlite3 *db;
    char *errMsg = 0;
    int rc;
    // 打开数据库（如果不存在则创建）
    rc = sqlite3_open("test.db", &db);
    if (rc) {
        fprintf(stderr, "Can't open database: %s
", sqlite3_errmsg(db));
        exit(0);
    } else {
        fprintf(stderr, "Opened database successfully
");
    }
    // 创建表1：users
    const char *sqlCreateTable1 = "CREATE TABLE IF NOT EXISTS users (" 
                                  "id INTEGER PRIMARY KEY AUTOINCREMENT," 
                                  "name TEXT NOT NULL," 
                                  "age INTEGER NOT NULL);";
    rc = sqlite3_exec(db, sqlCreateTable1, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s
", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "Table 'users' created successfully
");
    }
    // 创建表2：orders
    const char *sqlCreateTable2 = "CREATE TABLE IF NOT EXISTS orders (" 
                                  "id INTEGER PRIMARY KEY AUTOINCREMENT," 
                                  "userId INTEGER NOT NULL," 
                                  "product TEXT NOT NULL," 
                                  "quantity INTEGER NOT NULL," 
                                  "FOREIGN KEY(userId) REFERENCES users(id));";
    rc = sqlite3_exec(db, sqlCreateTable2, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s
", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "Table 'orders' created successfully
");
    }
    // 插入数据到users表
    const char *sqlInsertUser = "INSERT INTO users (name, age) VALUES ('Alice', 30);" 
                                "INSERT INTO users (name, age) VALUES ('Bob', 25);";
    rc = sqlite3_exec(db, sqlInsertUser, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s
", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "Users inserted successfully
");
    }
    // 插入数据到orders表
    const char *sqlInsertOrder = "INSERT INTO orders (userId, product, quantity) VALUES (1, 'Apple', 10);" 
                                 "INSERT INTO orders (userId, product, quantity) VALUES (2, 'Banana', 5);";
    rc = sqlite3_exec(db, sqlInsertOrder, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s
", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "Orders inserted successfully
");
    }
    // 查询数据并显示结果
    const char *sqlSelect = "SELECT * FROM users INNER JOIN orders ON users.id = orders.userId;";
    rc = sqlite3_exec(db, sqlSelect, callback, 0, &errMsg);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "Failed to select data
");
        fprintf(stderr, "SQL error: %s
", errMsg);
        sqlite3_free(errMsg);
    } else {
        fprintf(stdout, "Operation done successfully
");
    }
    // 关闭数据库连接
    sqlite3_close(db);
    return 0;
}