如何使用SQL进行多表联合查询(SQLⅰte举例)
使用C++和SQLite进行多表联合查询的示例代码。假设有两个表： students 表和 scores 表， students 表包含学生的基本信息， scores 表包含学生的成绩信息，通过学生的 id 进行关联查询。
#include <iostream>
#include <sqlite3.h>
#include <string>
// 回调函数，用于处理查询结果
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::cout << std::endl;
return 0;
}
int main() {
sqlite3 *db;
char *zErrMsg = 0;
int rc;
// 打开数据库
rc = sqlite3_open("test.db", &db);
if (rc) {
std::cerr << "Can't open database: " << sqlite3_errmsg(db) << std::endl;
return 0;
} else {
std::cout << "Opened database successfully" << std::endl;
}
// 创建students表
std::string sqlCreateStudents = "CREATE TABLE IF NOT EXISTS students ("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"name TEXT NOT NULL,"
"age INTEGER NOT NULL);";
rc = sqlite3_exec(db, sqlCreateStudents.c_str(), 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
std::cerr << "SQL error: " << zErrMsg << std::endl;
sqlite3_free(zErrMsg);
}
// 创建scores表
std::string sqlCreateScores = "CREATE TABLE IF NOT EXISTS scores ("
"id INTEGER PRIMARY KEY AUTOINCREMENT,"
"student_id INTEGER NOT NULL,"
"math_score REAL NOT NULL,"
"english_score REAL NOT NULL,"
"FOREIGN KEY (student_id) REFERENCES students(id));";
rc = sqlite3_exec(db, sqlCreateScores.c_str(), 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
std::cerr << "SQL error: " << zErrMsg << std::endl;
sqlite3_free(zErrMsg);
}
// 插入学生数据
std::string sqlInsertStudents = "INSERT INTO students (name, age) VALUES ('Alice', 20); "
"INSERT INTO students (name, age) VALUES ('Bob', 21);";
rc = sqlite3_exec(db, sqlInsertStudents.c_str(), 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
std::cerr << "SQL error: " << zErrMsg << std::endl;
sqlite3_free(zErrMsg);
}
// 插入成绩数据
std::string sqlInsertScores = "INSERT INTO scores (student_id, math_score, english_score) VALUES (1, 90.5, 85.0); "
"INSERT INTO scores (student_id, math_score, english_score) VALUES (2, 80.0, 75.5);";
rc = sqlite3_exec(db, sqlInsertScores.c_str(), 0, 0, &zErrMsg);
if (rc != SQLITE_OK) {
std::cerr << "SQL error: " << zErrMsg << std::endl;
sqlite3_free(zErrMsg);
}
// 多表联合查询
std::string sqlQuery = "SELECT students.name, students.age, scores.math_score, scores.english_score "
"FROM students "
"JOIN scores ON students.id = scores.student_id;";
rc = sqlite3_exec(db, sqlQuery.c_str(), callback, 0, &zErrMsg);
if (rc != SQLITE_OK) {
std::cerr << "SQL error: " << zErrMsg << std::endl;
sqlite3_free(zErrMsg);
}
// 关闭数据库
sqlite3_close(db);
return 0;
}
上述代码先创建了 students 表和 scores 表，并插入了一些测试数据，然后通过 JOIN 关键字将两个表基于 students.id 和 scores.student_id 的关联关系进行联合查询，将学生的基本信息和成绩信息一起查询并输出。