Qt操作SQLite数据库——创建、打开、关闭、增加、删除和修改操作

if(!database.open())
{  
qDeBUG()<<database.lastError();
qFatal("Failed to connect.") ;
}
else
{
//Qsqlquery类提供执行和操作的sql语句的方法。
//可以用来执行DML（数据操作语言）语句，如SELECT、INSERT、updatE、deletE， 
//以及DDL（数据定义语言）语句，例如create table。
//也可以用来执行那些不是标准的sql的数据库特定的命令。
Qsqlquery sql_query; 

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";

sql_query.prepare(create_sql);
if(!sql_query.exec())
{
qDeBUG()<<sql_query.lastError();
}
else
{
qDeBUG()<<"table created!";
}

//查询最大ID
int max_ID = 0;
sql_query.prepare(SELEct_max_sql);
if(!sql_query.exec())
{
qDeBUG()<<sql_query.lastError();
}
else
{
while(sql_query.next())
{
max_ID = sql_query.value(0).toInt();
qDeBUG()<<QString("max ID:%1").arg(max_ID);
}
}
//插入数据
sql_query.prepare(insert_sql);
sql_query.addBindValue(max_ID+1);
sql_query.addBindValue("name");
sql_query.addBindValue(25);
if(!sql_query.exec())
{
qDeBUG()<<sql_query.lastError();
}
else
{
qDeBUG()<<"inserted!";
}

//更新数据
sql_query.prepare(update_sql);
sql_query.bindValue(":name","Qt");
sql_query.bindValue(":ID",1);
if(!sql_query.exec())
{
qDeBUG()<<sql_query.lastError();
}
else
{
qDeBUG()<<"updated!";
}

//查询部分数据
if(!sql_query.exec(SELEct_sql))
{
qDeBUG()<<sql_query.lastError();
}
else
{
while(sql_query.next())
{
int id = sql_query.value("ID").toInt();
QString name = sql_query.value("name").toString();

qDeBUG()<<QString("ID:%1    name:%2").arg(ID).arg(Name);
}
}

//查询所有数据
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();

qDeBUG()<<QString("ID:%1    name:%2    age:%3").arg(ID).arg(Name).arg(agE);
}
}

//删除数据
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";
}
}

//关闭数据库
database.close();

//删除数据库
Qfile::remove("database.db");