QT + SQLite

QT + SQLite

SQLite基本使用

1.连接关闭数据库

	QSqlDatabase database;
    if(QSqlDatabase::contains("qt_sql_default_connection"))
      database = QSqlDatabase::database("qt_sql_default_connection");
    else
      database = QSqlDatabase::addDatabase("QSQLITE");

    database.setDatabaseName("PatientData.db");
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
	 //关闭数据库
    database.close();
	//查询表格
    QStringList tables = database.tables();
    qDebug()<<tables;

2.建表

    QString create_table(
        "CREATE TABLE IF NOT EXISTS patient("
        "   id INTEGER PRIMARY KEY AUTOINCREMENT, "
        "   name TEXT NOT NULL,"
        "   age TEXT NOT NULL,"
        "   gender TEXT NOT NULL,"
        "   s1 TEXT NOT NULL,"
        "   s2 TEXT NOT NULL,"
        "   s3 INTEGER NOT NULL,"
        "   s4 TEXT NOT NULL,"
        "   x1 TEXT,"
        "   x2 TEXT,"
        "   x3 TEXT"
        ")");
	//创建表格
    QSqlQuery sql_query;
    if(!sql_query.exec(create_table))
    {
        qDebug() << "Error: Fail to create table."<< sql_query.lastError();
    }
    else
    {
        qDebug() << "Table created!";
    }

3.插入

//1
sql_query.exec("INSERT INTO student VALUES(1, \"Wang\", 23)");
//2
QString insert_query(QString(
        "INSERT INTO patient(name,age,gender,s1,s2,s3,s4) VALUES"
        "(\"%1\",\"%2\", \"%3\", \"%4\", \"%5\", \"%6\", 0)"
    ).arg(strL[0]).arg(age).arg(strL[5]).arg(strL[4]).arg(strL[1]).arg(strL[2]));
sql_query.exec(insert_query);

4.查询

	 sql_query.exec("select * from patient where id = 1");
 	while(sql_query.next())
	{
            QVector<QString> str_temp;
            for(int i=0; sql_query.value(i).isValid(); i++){
                str_temp.append(sql_query.value(i).toString());
            }
            data.append(str_temp);
	}
	foreach(QVector<QString> temp, data){
		qDebug()<<temp.size();
	}

4.修改

sql_query.exec("update student set name = \"QT\" where id = 1");
sql_query.exec("update student set name = \"QT\" , id=2,gender=\"男\" where id = 1");

5.删除数据

 sql_query.exec("delete from patient where id = 4");

6.删除表格

sql_query.exec("drop table patient");