QSQLITE使用整理，sql常用语句

效果图

源码
注意：在.pro中添加QT += sql

#include <QCoreApplication>
#include <QTextCodec>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QTime>
#include <QSqlError>
#include <QtDebug>
#include <QSqlDriver>
#include <QSqlRecord>

int main(int argc, char *argv[])
{
    QCoreApplication a(argc, argv);
    QTextCodec::setCodecForLocale(QTextCodec::codecForLocale());
    QSqlDatabase db =QSqlDatabase::addDatabase("QSQLITE");
/*    db.setHostName("easybook-3313b0");          //设置数据库主机名
    db.setDatabaseName("qtDB.db");              //设置数据库名
    db.setUserName("aa");                //设置数据库用户名
    db.setPassword("123456"); */                  //设置数据库密码
    db.open();                             		//打开连接

    //创建数据库表
    QSqlQuery query;
    bool success=query.exec("create table automobil(id int primary key,attribute varchar,type varchar,kind varchar,nation int,carnumber int,elevaltor int,distance int,oil int,temperature int)");
    if(success)
        qDebug()<<QObject::tr("数据库表创建成功！\n");
    else
        qDebug()<<QObject::tr("数据库表创建失败！\n");

    //查询
    query.exec("select * from automobil");
    QSqlRecord rec = query.record();
    qDebug() << QObject::tr("automobil表字段数：" )<< rec.count();

    //插入记录
    QTime t;
    t.start();
    query.prepare("insert into automobil values(?,?,?,?,?,?,?,?,?,?)");

    long records=100;
    for(int i=0;i<records;i++)
    {
        query.bindValue(0,i);
        query.bindValue(1,"四轮");
        query.bindValue(2,"轿车");
        query.bindValue(3,"富康");
        query.bindValue(4,rand()%100);
        query.bindValue(5,rand()%10000);
        query.bindValue(6,rand()%300);
        query.bindValue(7,rand()%200000);
        query.bindValue(8,rand()%52);
        query.bindValue(9,rand()%100);

        success=query.exec();
        if(!success)
        {
            QSqlError lastError=query.lastError();
            qDebug()<<lastError.driverText()<<QString(QObject::tr("插入失败"));
        }
    }
    qDebug()<<QObject::tr("插入 %1 条记录，耗时：%2 ms").arg(records).arg(t.elapsed());

    //排序
    t.restart();
    success=query.exec("select * from automobil order by id desc");
    if(success)
        qDebug()<<QObject::tr("排序 %1 条记录，耗时：%2 ms").arg(records).arg(t.elapsed());
    else
        qDebug()<<QObject::tr("排序失败！");

    //更新记录
    t.restart();
    for(int i=0;i<records;i++)
    {
       query.clear();
       query.prepare(QString("update automobil set attribute=?,type=?,"
                             "kind=?,nation=?,"
                             "carnumber=?,elevaltor=?,"
                             "distance=?,oil=?,"
                             "temperature=? where id=%1").arg(i));

       query.bindValue(0,"四轮");
       query.bindValue(1,"轿车");
       query.bindValue(2,"富康");
       query.bindValue(3,rand()%100);
       query.bindValue(4,rand()%10000);
       query.bindValue(5,rand()%300);
       query.bindValue(6,rand()%200000);
       query.bindValue(7,rand()%52);
       query.bindValue(8,rand()%100);

       success=query.exec();
       if(!success)
       {
           QSqlError lastError=query.lastError();
           qDebug()<<lastError.driverText()<<QString(QObject::tr("更新失败"));
       }
    }
    qDebug()<<QObject::tr("更新 %1 条记录，耗时：%2 ms").arg(records).arg(t.elapsed());

    //删除
    t.restart();
    query.exec("delete from automobil where id=15");
    qDebug()<<QObject::tr("删除一条记录，耗时：%1 ms").arg(t.elapsed());

    return 0;
    //return a.exec();
}

sql常用语句

#include "mainwindow.h"
#include <QApplication>

#include <QCoreApplication>
#include <QTextCodec>
#include <QtSql/QSqlDatabase>
#include <QtSql/QSqlQuery>
#include <QtSql/QSqlError>
#include <QtSql/QSqlDriver>
#include <QtSql/QSqlRecord>


#include <QTime>
#include <QDebug>

int main(int argc, char *argv[])
{
    QApplication a(argc, argv);

    //设置编码格式--
    //QTextCodec::setCodecForTr(QTextCodec::codecForName("UTF-8"));
    QTextCodec::setCodecForLocale(QTextCodec::codecForLocale());
    //添加Sqllite数据库驱动
    QSqlDatabase db =QSqlDatabase::addDatabase("QSQLITE");
    //设置要打开或创建的数据库名称
    db.setDatabaseName("D:/MyDBTest/MyDB.db");
    //如果没有打开，返回-1
    if(!db.open()){
        qDebug() << db.lastError();//打印未打开的错误信息
        return -1;
    }

    QSqlQuery query;
    //创建表格--crete table 表名(id varchar,name varchar,age int)
    query.exec("create table mytable(id varchar primary key,name varchar)");
    //从已有表格中取出几列创建新的表格--
    //create table tableNew as select name,address from tableOld

    //更改表格名称
    //ALTER TABLE mytable RENAME TO mytablenew

    //删除表格
    //drop table 表格名称


    //为表格插入新的一列
    //ALTER TABLE table1 ADD COLUMN other varchar

    //插入数据--insert into 表名 values('id1','name1',25)
    //insert into table1(name,address) values('name2','address2')
    query.exec(QString("insert into mytable values('id1','name1')"));

    //更新数据--update table1 set name='new_name',age=20 where id='id1'
    //更新表格中一整列的内容--update table age=10;将table1中的age列全部改为10

    //删除一条记录
    //delete from table1 where id='id1'
    //delete from table2--删除表中所有的数据


    //查询--select 字段名（用，隔开）from 表名 where 查询条件
    //group by 分组的字段 having 筛选条件 order by 排序字段

    //select * from table2 where id<>'id1'--<>表示不等于，为空的is null
    //select * from table1 where name like "%热点%"--查找name中包含热点的数据(包含在首末)
    // %表示0或者多个，_表格一个,   desc从大到小，asc从小到大
    //select * from table1 where age between 10 and 20 order by desc


    return a.exec();
}