Qt学习笔记之SQLITE数据库

1. SQLite数据库介绍

SQLite，是一款轻型的数据库，是遵守ACID的关系型数据库管理系统，它包含在一个相对小的C库中。它是D.RichardHipp建立的公有领域项目。它的设计目标是嵌入式的，而且已经在很多嵌入式产品中使用了它，它占用资源非常的低，在嵌入式设备中，可能只需要几百K的内存就够了。它能够支持Windows/Linux/Unix等等主流的操作系统，同时能够跟很多程序语言相结合，比如 Tcl、C#、PHP、Java等，还有ODBC接口，同样比起Mysql、PostgreSQL这两款开源的世界著名数据库管理系统来讲，它的处理速度比他们都快。SQLite第一个Alpha版本诞生于2000年5月。 至2019年已经有19个年头，SQLite也迎来了一个版本 SQLite 3已经发布。

Qt提供了一种进程内数据库——SQLite，它小巧灵活，无须额外安装配置且支持大部分ANSI SQL92标准，是一个轻量级的数据库，概括起来它具有以下优点：

（1）SQlite的设计目的是嵌入式SQL数据库引擎，它基于纯C语言代码，应用非常广泛。

（2）SQlite在需要持久存储时，可以直接读写硬盘上的数据文件，在无须持久存储时，也可以将整个数据库置于内存中，两者均不需要额外的server服务端进程，即SQlite是无须独立运行的数据库引擎。

（3）开放源代码，整个代码少于三万行，有良好的注释和90%以上的测试覆盖率。

（4）少于250K的内存占用（gcc编译下）。

（5）支持视图、触发器、事务，支持嵌套SQL功能。

（6）提供虚拟机用于处理SQL语句。

（7）不需要配置，不需要安装，也不需要管理员。

（8）支持大部分ANSI SQL92标准。

（9）大部分应用的速度比目前常见的客户端/服务器结构的数据库快。

（10）编程接口简单易用。

在持久存储的情况下，一个完整的数据库就对应磁盘上面的一个文件，它是一种具备了基本数据库特性的数据文件，同一个数据文件可以在不同机器上面使用，可以在不同字节序的机器间自由共享，最大支持2TB数据容量，而且性能仅受限于系统的可用内存，没有其他依赖，可以应用于多种操作系统平台。

SQLite可视化管理工具(SQLite Expert Pro)

2. Qt操作SQLite数据库

2.1 创建一个数据库

 QSqlDatabase类提供了一个接口，用于通过连接访问数据。

    //! 添加数据库驱动
    QSqlDatabase mySqlLiteDB = QSqlDatabase::addDatabase("QSQLITE");
    //! 设置数据库名称
    mySqlLiteDB.setDatabaseName("E:/work/qe/mytest1/database.db");
    //! 打开数据库
    if(!mySqlLiteDB.open())
        return false;

2.2 创建一个表

QSqlQuery类可以使用SQL语句来实现与数据库交互。

    //! 以下执行相关sql语句
    QSqlQuery mySqlQuery;
    //! 清空student表
    mySqlQuery.exec("drop table student");
    //! 新建student表，id设置为主键，还有一个name项
    mySqlQuery.exec("create table student (id int primary key, name vchar,course int)");

主键：表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键，通过它可强制表的实体完整性。当创建或更改表时可通过定义 PRIMARY KEY 约束来创建主键。一个表只能有一个 PRIMARY KEY 约束，而且 PRIMARY KEY 约束中的列不能接受空值。由于 PRIMARY KEY 约束确保唯一数据，所以经常用来定义标识列。

作用:

1）保证实体的完整性;

2）加快数据库的操作速度

3） 在表中添加新记录时，DBMS会自动检查新记录的主键值，不允许该值与其他记录的主键值重复。

4) DBMS自动按主键值的顺序显示表中的记录。如果没有定义主键，则按输入记录的顺序显示表中的记录。

2.3 插入数据 

2.3.1 批量插入

可以用prepare()来绑值,然后再通过bindValue()向绑值加入数据。

       //!批量导入
    QStringList strListStdName;
    strListStdName<<"wang bin"<<"xiao yu"<<"ye mei";

    QStringList strListStdCourse;
    strListStdCourse<<"Math"<<"English"<<"Computer";

    mySqlQuery.prepare("INSERT INTO student (id,name, course) "
                      "VALUES (:id,:name, :course)");

    for (int i = 0; i < strListStdName.size(); i++)
    {
        mySqlQuery.bindValue(":id",i);                         //! 加入主键
        mySqlQuery.bindValue(":name", strListStdName[i]);      //! 向绑定值里加入名字
        mySqlQuery.bindValue(":course", strListStdCourse[i]);  //! 课程
        mySqlQuery.exec();                                     //! 加入库中
    }
    //! 向表中插入1条数据
    mySqlQuery.bindValue(0,3);
    mySqlQuery.bindValue(1,"wang han");
    mySqlQuery.bindValue(2,"Chinese");
    mySqlQuery.exec();

 这里在student表的最后又添加了一条记录。然后我们先使用了prepare()函数，在其中利用了“:id”和“:name”来代替具体的数据，而后又利用bindValue()函数给id和name两个属性赋值，这称为绑定操作。其中编号0和1分别代表“:id”和“:name”，就是说按照prepare()函数中出现的属性从左到右编号，最左边是0 。

特别注意，在最后一定要执行exec()函数，所做的操作才能被真正执行。运行程序，点击查询按钮，可以看到前面添加的记录的信息。这里的“:id”和“:name”，叫做占位符，这是ODBC数据库的表示方法，还有一种Oracle的表示方法就是全部用“？”号。例如：

query.prepare("insert into student(id, name) "
                  "values (?, ?)");
query.bindValue(0, 5);
query.bindValue(1, "sixth");
query.exec();

也可以利用addBindValue()函数，这样就可以省去编号，它是按顺序给属性赋值的，如下：

query.prepare("insert into student(id, name) "
                  "values (?, ?)");
query.addBindValue(5);
query.addBindValue("sixth");
query.exec();

当用ODBC的表示方法时，我们也可以将编号用实际的占位符代替，如下：

query.prepare("insert into student(id, name) "
                      "values (:id, :name)");
query.bindValue(":id", 5);
query.bindValue(":name", "sixth");
query.exec();

以上各种形式的表示方式效果是一样的。

2.3.2 用insert into语句

    //! 单条导入
    mySqlQuery.exec("insert into student values(3,'wang han','Chinese')");

2.4  查询表内容 

 可以用select语句查询表内容

   //! 查找表中id >=1 的记录的id项和name项的值
    mySqlQuery.exec("select * from student where id >= 1");

    //! query.next()指向查找到的第一条记录，然后每次后移一条记录
    QSqlRecord mySqlRec = mySqlQuery.record();
    while(mySqlQuery.next())
    {
        //! 获取query所指向的记录在结果集中的编号
        int nRowNum = mySqlQuery.at();
        //! 获取每条记录中属性（即列）的个数
        int nColumnNum = mySqlQuery.record().count();
        //! 获取"name"属性所在列的编号，列从左向右编号，最左边的编号为0
        int nFieldNo = mySqlQuery.record().indexOf("name");
        nFieldNo     = mySqlRec.indexOf("course");
        //! 获取id属性的值，并转换为int型
        int stud_id = mySqlQuery.value(0).toInt();
        //! 获取name属性的值
        QString stud_name = mySqlQuery.value(1).toString();
        //! 获取course属性的值
        QString stud_course = mySqlQuery.value(2).toString();
        //! 输出结果
        qDebug() << nRowNum << nColumnNum << nFieldNo << stud_id << stud_name << stud_course;
    }

    //! 定位到结果集中编号为2的记录，即第三条记录，因为第一条记录的编号为0
    qDebug() << "exec seek(2) :";
    if(mySqlQuery.seek(2))
    {
        qDebug() << "rowNum is : " << mySqlQuery.at()
                 << " id is : " << mySqlQuery.value(0).toInt()
                 << " name is : " << mySqlQuery.value(1).toString();
    }
    //! 定位到结果集中最后一条记录
    qDebug() << "exec last() :";
    if(mySqlQuery.last())
    {
        qDebug() << "rowNum is : " << mySqlQuery.at()
                 << " id is : " << mySqlQuery.value(0).toInt()
                 << " name is : " << mySqlQuery.value(1).toString();
    }

输出结果 

在前面的程序中，我们使用

mySqlQuery.exec("select * from student where id >= 1");

查询出表中所有的内容。其中的SQL语句“select * from student”中“*”号表明查询表中记录的所有属性。而当mySqlQuery.exec("select * from student");这条语句执行完后，我们便获得了相应的执行结果，因为获得的结果可能不止一条记录，所以称之为结果集。

结果集其实就是查询到的所有记录的集合，在QSqlQuery类中提供了多个函数来操作这个集合，需要注意这个集合中的记录是从0开始编号的。最常用的操作有：

• seek(int n) ：query指向结果集的第n条记录；
• first() ：query指向结果集的第一条记录；
• last() ：query指向结果集的最后一条记录；
• next() ：query指向下一条记录，每执行一次该函数，便指向相邻的下一条记录；
• previous() ：query指向上一条记录，每执行一次该函数，便指向相邻的上一条记录；
• record() ：获得现在指向的记录；
• value(int n) ：获得属性的值。其中n表示你查询的第n个属性，比方上面我们使用“select * from student”就相当于“select id, name from student”，那么value(0)返回id属性的值，value(1)返回name属性的值。该函数返回QVariant类型的数据，关于该类型与其他类型的对应关系，可以在帮助中查看QVariant。
• at() ：获得现在query指向的记录在结果集中的编号。

需要特别注意，刚执行完mySqlQuery.exec("select *from student");这行代码时，mySqlQuery是指向结果集以外的，我们可以利用mySqlQuery.next()使得mySqlQuery指向结果集的第一条记录。当然我们也可以利用seek(0)函数或者first()函数使mySqlQuery指向结果集的第一条记录。但是为了节省内存开销，推荐的方法是，在mySqlQuery.exec("select * from student");这行代码前加上mySqlQuery.setForwardOnly(true);这条代码，此后只能使用next()和seek()函数。

2.5 删除表内容 

2.5.1 DROP 语句        

用来删除整表,并且连表结构也会删除,删除后则只能使用CREATE TABLE来重新创建表

    //!　删除表内容
    mySqlQuery.exec("drop table student");

2.5.2 TRUNCATE语句

 在SQLite中没有该语句,在MySQL中有该语句,用来清楚表内数据,但是表结构不会删除.

2.5.3 DELETE语句      

删除部分记录,并且表结构不会删除,删除的速度比上面两个语句慢,可以配合WHERE来删除指定的某行

1） 删除整个表 

mySqlQuery.exec("delete table student");

 

 2） 删除某行

mySqlQuery.exec("delete from student where id = 1");

 

2.6 修改表内容 

2.6.1 update

UPDATE 语句用于更新表中已存在的记录。

SQL UPDATE 语法

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

    //! 修改表内容
    mySqlQuery.exec("update student set course = 'Chinese' where id = 1");

2.6.2 alter语句 

1) 如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name ADD column_name datatype

mySqlQuery.exec("alter table student add score int");

2)  如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式，该行在SQlite中不能用，SQlite不支持drop）：

ALTER TABLE table_name DROP COLUMN column_name

3） 要改变表中列的数据类型，请使用下面的语法：

ALTER TABLE table_name ALTER COLUMN column_name datatype

 参考资料：

1. SQlite在已创建的表中删除一列

2.42.QT-QSqlQuery类操作SQLite数据库(创建、查询、删除、修改)详解