【Qt】三种操作sqlite3的方式及其三种多表连接
一、sqlite3与MySQL数据库区别：
1. 数据库类型
- SQLite3：是嵌入式数据库，它将整个数据库存储在单个文件中，不需要独立的服务器进程。这意味着它可以很方便地集成到各种应用程序中，如移动应用、桌面应用等。
- MySQL：是客户端 - 服务器型数据库，需要一个独立的服务器进程来管理数据库。客户端通过网络连接到服务器进行数据操作，适合多用户、多客户端并发访问的场景。
2. 数据处理规模
- SQLite3：由于其单文件存储和轻量级的特点，适合处理小型到中型的数据量。一般来说，它更适用于本地数据存储和简单的数据处理任务，如小型应用程序的配置数据存储、嵌入式设备的数据管理等。
- MySQL：能够处理大规模的数据和高并发的访问。它具有强大的性能和扩展性，可以支持企业级应用和大型网站的数据存储和管理需求。
3. 并发性能
- SQLite3：对并发写入的支持有限。同一时间只允许一个进程对数据库文件进行写操作，多个进程的写操作会被串行化，可能会导致性能瓶颈。不过，它对并发读取的支持较好。
- MySQL：具备良好的并发性能，支持多个客户端同时进行读写操作。通过事务、锁机制等技术，MySQL 可以有效地处理高并发场景下的数据一致性和完整性问题。
4. 功能特性
- SQLite3：功能相对简单，提供了基本的 SQL 支持，包括数据定义、数据操作和数据查询等功能。但它缺少一些高级特性，如存储过程、触发器等。
- MySQL：功能丰富，支持存储过程、触发器、视图、事务处理、外键约束等高级特性。这些特性可以帮助开发人员实现更复杂的业务逻辑和数据处理需求。
5. 安全性
- SQLite3：安全性相对较低，因为它没有内置的用户认证和授权机制。数据库文件的访问权限主要依赖于操作系统的文件权限设置。
- MySQL：提供了较为完善的用户认证和授权机制，可以对不同用户授予不同的数据库操作权限，从而保证数据的安全性。
二、连接多个表三种方式
总结：
- 等值连接和内连接：只返回两个表中匹配的行，不包含任何表中的不匹配行。
- 左外连接：返回左表的所有行，以及右表中匹配的行，右表不匹配的行用
NULL填充。 - 右外连接：返回右表的所有行，以及左表中匹配的行，左表不匹配的行用
NULL填充。 - 全外连接：返回左右两个表的所有行，不匹配的行用
NULL填充。
1. 等值连接
等值连接是一种基本的连接方式，它通过在连接条件中使用等号（=）来匹配两个表中指定列的值。通常情况下，等值连接用于关联两个表的主键和外键，以确保数据的一致性。
特点：
- 只返回两个表中匹配的行，即只有当
Orders.CustomerID和Customers.CustomerID的值相等时，才会将相应的行组合在一起。 - 通常使用
WHERE子句来指定连接条件。
-- 1.等值连接,主键与外键关联
SELECT *
FROM video as a,sq_channel as b,video_type as c
WHERE a.channel_id=b.channel_id
AND a.videoType_id=c.videoType_id
2. 内连接（JOIN ON）
内连接是一种更标准的连接方式，它使用 JOIN 关键字和 ON 子句来指定连接条件。内连接和等值连接的功能类似，都是只返回两个表中匹配的行。
特点：
- 语法更加清晰，将连接条件和查询条件分开，提高了代码的可读性。
- 是现代 SQL 中推荐使用的连接方式。
-- 2.内连接join on
SELECT *
FROM video
JOIN sq_channel
ON video.channel_id=sq_channel.channel_id
JOIN video_type
ON video.videoType_id=video_type.videoType_id
3. 外连接
注意：sqlite3无法右外连接与全外连接
外连接会返回一个表中的所有行，以及另一个表中匹配的行。如果另一个表中没有匹配的行，则用 NULL 填充。外连接分为左外连接、右外连接和全外连接。
（1）左外连接（LEFT JOIN 或 LEFT OUTER JOIN）
左外连接以左表为主，返回左表中的所有行，以及右表中匹配的行。如果右表中没有匹配的行，则用 NULL 填充。
-- （1）左外连接，以左表为主全部显示，右表根据条件去匹配
SELECT *
FROM video
LEFT JOIN sq_channel
on video.channel_id=sq_channel.channel_id;
（2）右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）
右外连接以右表为主，返回右表中的所有行，以及左表中匹配的行。如果左表中没有匹配的行，则用 NULL 填充。
（3）全外连接（FULL JOIN 或 FULL OUTER JOIN）
全外连接会返回左右两个表中的所有行，无论是否有匹配的行。如果某一行在另一个表中没有匹配的行，则用 NULL 填充。
三、三种操作 SQLite 数据库方式
总结：
- 若需要多次执行相同结构的 SQL 语句，并且对安全性和性能有较高要求，建议使用 SQL 预处理。
- 对于一次性执行的简单 SQL 语句，可选择
sqlite3_exec，它能让代码更简洁。 - 对于简单的查询操作，且希望方便地处理查询结果，
SQLite3_get_table是个不错的选择，但要注意内存管理问题。
方案一：SQL 预处理
通常用在DML（数据操作语言）
SQL 预处理是指先准备好 SQL 语句，之后再绑定参数并执行。在 SQLite 里，主要借助 sqlite3_prepare、sqlite3_bind_* 和 sqlite3_step 等函数来实现。
- 安全性高：能够有效防止 SQL 注入攻击。由于参数是通过绑定的方式传入，而非直接拼接进 SQL 语句，所以可避免恶意用户通过构造特殊输入来篡改 SQL 语句。
- 不适合一次性执行的简单 SQL 语句：若只执行一次 SQL 语句，使用预处理会引入额外的开销，还不如直接执行简单。
void demo3(sqlite3* psqlite3)
{
int res;
sqlite3_stmt *pstmt;
char user_name[20]="张三";
char video_name[20]="熊出没";
char sql[500]="SELECT record.record_id,record.user_id,record.video_id,record.time,record.data,sq_user.user_name,video.video_name\
FROM record\
JOIN sq_user on sq_user.user_id=record.user_id\
JOIN video ON video.video_id=record.video_id\
WHERE sq_user.user_name=? AND video.video_name=?;";
res=sqlite3_prepare(psqlite3,sql,strlen(sql),&pstmt,NULL);
if(res==SQLITE_OK)
{
sqlite3_bind_text(pstmt,1,user_name,strlen(user_name),NULL);
sqlite3_bind_text(pstmt,2,video_name,strlen(video_name),NULL);
while (sqlite3_step(pstmt)==SQLITE_ROW)
{
qDebug()<<"---------------------------";
qDebug()<<"record_id="<<sqlite3_column_int(pstmt,0);
qDebug()<<"user_id="<<sqlite3_column_int(pstmt,1);
qDebug()<<"video_id="<<sqlite3_column_int(pstmt,2);
qDebug()<<"time="<<sqlite3_column_int(pstmt,3);
qDebug()<<"data="<<(char *)sqlite3_column_text(pstmt,4);
qDebug()<<"user_name="<<(char *)sqlite3_column_text(pstmt,5);
qDebug()<<"video_name="<<(char *)sqlite3_column_text(pstmt,6);
}
}
else
{
qDebug()<<"操作数据错误码："<<sqlite3_errcode(psqlite3);
qDebug()<<"连接数据库失败的错误信息"<<sqlite3_errmsg(psqlite3);
}
}
方案二：通用数据库处理 sqlite3_exec
sqlite3_exec 是 SQLite 提供的一个便捷函数，可用于执行单条或多条 SQL 语句。
- 适用于简单场景：对于一次性执行的简单 SQL 语句，如创建表、插入少量数据等，使用
sqlite3_exec非常方便。 - 安全性差：直接将 SQL 语句作为字符串传入，容易遭受 SQL 注入攻击。若 SQL 语句包含用户输入，必须进行严格的输入验证和转义处理。
- 结果处理不够灵活：只能通过回调函数来处理查询结果，对于复杂的结果处理逻辑，回调函数的使用可能会使代码变得复杂。
*优势：可以用在DDL、DML
* 注意：sqlite3_exec操作的是查询语句需要回调函数，其他语句不需回调函数，可直接写nullptr
*/
//回调函数针对查询结果集返回的列数取值
int mycallback(void* pdata,int col,char** value,char** colname)
{
qDebug()<<"查询结果列数："<<col;
for(int i=0;i<col;i++)
{
qDebug()<<"colname["<<i<<"]"<<colname[i]<<"="<<value[i];
}
return 0;
}
void demo2(sqlite3* psqlite3)
{
int res=0;
char* errmsg;
char sql[100]={0};
char username[10]="张三";
sprintf(sql,"select * from sq_user where user_name='%s';",username);
qDebug()<<"sql="<<sql;
//第三个回调函数只有在查询语句写，如果不查询直接写nullptr
res=sqlite3_exec(psqlite3,sql,mycallback,nullptr,&errmsg);
if(res==SQLITE_OK)
{
qDebug()<<"sqlite3_exec success";
}
else
{
qDebug()<<"操作失败的错误码："<<sqlite3_errcode(psqlite3);
}
}
方案三：常用操作 SQLite3_get_table
SQLite3_get_table 是一个用于执行 SQL 查询语句并将结果存储在二维数组中的函数。
结果处理方便：将查询结果存储在一个二维数组中，方便进行遍历和处理，无需使用回调函数
功能有限：主要用于查询操作，对于更新、插入、删除等操作，使用起来不如 sqlite3_exec 方便。
- 适用于简单查询：对于简单的查询操作，能够快速获取结果，代码实现相对简单。
- 功能有限：主要用于查询操作，对于更新、插入、删除等操作，使用起来不如
sqlite3_exec方便。
//优势：可以直接获取查询结果字段、数据、行数、列数，也是最常用数据库操作方式
void demo3(sqlite3* psqlite3)
{
int res=0;
char *errmsg;//接收错误信息
char** datares;//接收查询结果集
int row;
int col;
char sql[100]={0};
char username[10]="张三";
//sprintf(sql,"select * from sq_user where user_name='%s';",username);
sprintf(sql,"select * from sq_user");
qDebug()<<"sql="<<sql;
res=sqlite3_get_table(psqlite3,sql,&datares,&row,&col,&errmsg);
qDebug()<<"查询结果 row="<<row;
qDebug()<<"查询结果 col="<<col;
//取表头
for(int i=0;i<col;i++)
{
qDebug()<<datares[i];
}
//取表数据
for(int j=col;j<(row+1)*col;j++)
{
qDebug()<<datares[j];
}
qDebug()<<datares[0]<<"-"<<datares[1]<<"-"<<datares[2]<<"-"<<datares[3];
qDebug()<<datares[4]<<"-"<<datares[5]<<"-"<<datares[6]<<"-"<<datares[7];
qDebug()<<datares[8]<<"-"<<datares[9]<<"-"<<datares[10]<<"-"<<datares[11];
qDebug()<<datares[12]<<"-"<<datares[13]<<"-"<<datares[14]<<"-"<<datares[15];
}