SQLite简介
SQLite,是一款轻型的数据库,是遵守ACID的关系型数据库管理系统,它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的,而且目前已经在很多嵌入式产品中使用了它,它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统,同时能够跟很多程序语言相结合,比如 Tcl、C#、PHP、Java等,还有ODBC接口,同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲,它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。
效果图
Qt5使用SQLite
Qt5版本已经自带了SQLite的驱动,不必再进行安装了,数据库最主要的功能就是增、删、查、改
常用指令:
QString create_sql = "create table student (id int primary key, name varchar(30), age int)"; QString select_max_sql = "select max(id) from student"; QString insert_sql = "insert into student values (?, ?, ?)"; QString update_sql = "update student set name = :name where id = :id"; QString select_sql = "select id, name from student"; QString select_all_sql = "select * from student"; QString delete_sql = "delete from student where id = ?"; QString clear_sql = "delete from student";
1、新建Qt Widgets应用程序,修改.pro文件,添加SQL模块
QT += sql
2、在main.cpp文件中添加如下代码
实现功能:添加一个数据库、创建2个表格star和student、增、删、查;MyDataBase.db数据库文件在E:\Qt_Project\build-SQLiteTest-Desktop_Qt_5_11_1_MinGW_32bit-Debug\MyDataBase.db,使用Navicat For SQLite可以查看
#include "mainwindow.h"
#include <QApplication>
//添加头文件
#include <QSql>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QString>
#include <QFile>
#include <QDebug>
#include <QVariantList>
int main(int argc, char *argv[])
{
QApplication a(argc, argv);
MainWindow w;
QSqlDatabase database = QSqlDatabase::addDatabase("QSQLITE");//添加数据库驱动
database.setDatabaseName("MyDataBase.db");//设置数据库名称
database.setUserName("root"); //设置数据库登录用户名
database.setPassword("123456");//设置数据库登录密码
//打开数据库
if(database.open())
{
qDebug()<<"Database Opened";
/*
QString create_sql = "create table student (id int primary key, name varchar(30), age int)";
QString select_max_sql = "select max(id) from student";
QString insert_sql = "insert into student values (?, ?, ?)";
QString update_sql = "update student set name = :name where id = :id";
QString select_sql = "select id, name from student";
QString select_all_sql = "select * from student";
QString delete_sql = "delete from student where id = ?";
QString clear_sql = "delete from student";
*/
//创建表成员
QString create_sql = "create table star (id int primary key, name varchar(30), age int,address varchar(30))"; //创建数据表
//插入数据
QString insert_sql = "insert into star values(?,?,?,?)";
//查询全部数据
QString select_all_sql = "select * from star";
QSqlQuery sql_query;//QSqlQuery类提供执行和操作的SQL语句的方法
sql_query.prepare(create_sql); //创建表
if(!sql_query.exec()) //查看创建表是否成功
{
qDebug()<<QObject::tr("Table Create failed");
qDebug()<<sql_query.lastError();
}
else
{
qDebug()<< "Table Created" ;
//插入数据
sql_query.prepare(insert_sql);
QVariantList GroupIDs;
GroupIDs.append(0);
GroupIDs.append(1);
GroupIDs.append(2);
GroupIDs.append(3);
GroupIDs.append(4);
QVariantList GroupNames;
GroupNames.append("赵丽颖");
GroupNames.append("杨幂");
GroupNames.append("郑爽");
GroupNames.append("可乐");
GroupNames.append("孙耀威");
QVariantList GroupAges;
GroupAges.append(33);
GroupAges.append(35);
GroupAges.append(25);
GroupAges.append(1);
GroupAges.append(42);
QVariantList GroupAddress;
GroupAddress.append("成都");
GroupAddress.append("北京");
GroupAddress.append("天津");
GroupAddress.append("上海");
GroupAddress.append("深圳");
sql_query.addBindValue(GroupIDs);
sql_query.addBindValue(GroupNames);
sql_query.addBindValue(GroupAges);
sql_query.addBindValue(GroupAddress);
if(!sql_query.execBatch())
{
qDebug()<<sql_query.lastError();
}
else
{
qDebug()<<"插入记录成功";
}
//查询所有记录
sql_query.prepare(select_all_sql);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();//查询失败
}
else
{
//检索下一个
while(sql_query.next())
{
int id = sql_query.value(0).toInt();
QString name = sql_query.value(1).toString();
int age = sql_query.value(2).toInt();
QString address = sql_query.value(3).toString();
qDebug()<<QString("ID:%1 Name:%2 Age:%3 Address:%4").arg(id).arg(name).arg(age).arg(address);
//删除数据
/*sql_query.prepare(delete_sql);
sql_query.addBindValue(max_id);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();
}
else
{
qDebug()<<"deleted!";
}
//清空表
sql_query.prepare(clear_sql);
if(!sql_query.exec())
{
qDebug()<<sql_query.lastError();
}
else
{
qDebug()<<"cleared";
}*/
}
}
}
QSqlQuery query;//QSqlQuery类提供执行和操作的SQL语句的方法
QString createStudent = "create table student (id int primary key, name varchar(30), sex varchar(30),score int)"; //创建数据表
//插入数据
QString insertStudent = "insert into student values(?,?,?,?)";
//查询全部数据
QString selectAllStudent = "select * from student";
//按条件查询
QString selectStudent = "select id, name from student";
query.prepare(createStudent); //创建表.prepare(create_sql); //创建表
if(!query.exec()) //查看创建表是否成功
{
qDebug()<<QObject::tr("Table Create failed");
qDebug()<<query.lastError();
}
else
{
qDebug()<< "Table Created" ;
//插入数据
//query.prepare(insertStudent);
query.prepare("INSERT INTO student (id, name, sex , score) "
"VALUES (?, ?, ?, ?)");
QVariantList GroupIDs;
GroupIDs.append(0);
GroupIDs.append(1);
GroupIDs.append(2);
GroupIDs.append(3);
GroupIDs.append(4);
QVariantList GroupNames;
GroupNames.append("赵丽颖");
GroupNames.append("杨幂");
GroupNames.append("郑爽");
GroupNames.append("可乐");
GroupNames.append("孙耀威");
QVariantList GroupSex;
GroupSex.append("女");
GroupSex.append("女");
GroupSex.append("女");
GroupSex.append("母");
GroupSex.append("男");
QVariantList GroupScore;
GroupScore.append(90);
GroupScore.append(80);
GroupScore.append(70);
GroupScore.append(60);
GroupScore.append(50);
query.addBindValue(GroupIDs);
query.addBindValue(GroupNames);
query.addBindValue(GroupSex);
query.addBindValue(GroupScore);
if(!query.execBatch())
{
qDebug()<<query.lastError();
}
else
{
qDebug()<<"插入记录成功";
}
//查询所有记录
query.prepare(selectAllStudent);
if(!query.exec())
{
qDebug()<<query.lastError();//查询失败
}
else
{
qDebug()<<"查询所有";
//检索下一个
while(query.next())
{
int id = query.value(0).toInt();
QString name = query.value(1).toString();
QString sex = query.value(2).toString();
int score = query.value(3).toInt();
qDebug()<<QString("ID:%1 Name:%2 Sex:%3 Score:%4").arg(id).arg(name).arg(sex).arg(score);
}
}
//按条件查询id,name
query.prepare(selectStudent);
if(!query.exec())
{
qDebug()<<query.lastError();//查询失败
}
else
{
qDebug()<<"按条件查询id,name";
//检索下一个
while(query.next())
{
int id = query.value(0).toInt();
QString name = query.value(1).toString();
//QString sex = query.value(2).toString();
//int score = query.value(3).toInt();
//qDebug()<<QString("ID:%1 Name:%2 Sex:%3 Score:%4").arg(id).arg(name).arg(sex).arg(score);
qDebug()<<QString("ID:%1 Name:%2").arg(id).arg(name);
}
}
}
}
database.close();
//删除数据库
//QFile::remove("database.db");
w.show();
return a.exec();
}
输出结果
Database Opened
Table Created
插入记录成功
"ID:0 Name:赵丽颖 Age:33 Address:成都"
"ID:1 Name:杨幂 Age:35 Address:北京"
"ID:2 Name:郑爽 Age:25 Address:天津"
"ID:3 Name:可乐 Age:1 Address:上海"
"ID:4 Name:孙耀威 Age:42 Address:深圳"
Table Created
插入记录成功
查询所有
"ID:0 Name:赵丽颖 Sex:女 Score:90"
"ID:1 Name:杨幂 Sex:女 Score:80"
"ID:2 Name:郑爽 Sex:女 Score:70"
"ID:3 Name:可乐 Sex:母 Score:60"
"ID:4 Name:孙耀威 Sex:男 Score:50"
按条件查询id,name
"ID:0 Name:赵丽颖"
"ID:1 Name:杨幂"
"ID:2 Name:郑爽"
"ID:3 Name:可乐"
"ID:4 Name:孙耀威"
Navicat For SQLite是收费的,只有14天的试用期,不用白不用
Navicat For SQLite下载链接:http://www.navicat.com.cn/products
完整代码
百度网盘链接:https://pan.baidu.com/s/1UX7RlEVcscOrskaVepGwAQ
提取码:1g5l