qt数据库相关

在pro文件里 加上 QT += sql

GRANT ALL PRIVILEGES ON *.* TO'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

 在局域网内用mysql管理工具管理数据库的时候不能连接服务器上的mysql了，提示Can't connect to Mysql server on "IP" (10060)的错误。
在安装mysql的时候有个选项是允许远程访问mysql，重新设置了mysql，把允许远程访问的选项选上了，结果还是不行。最后考虑到防火墙，在防火墙的例外选项卡里面添加了3306的端口号就好用了。
数据源配置设置 set names gbk

数据库插入活更新的时候时间类型的字段不支持‘’可以用NULL代替 time =NULL

select Tenement_intra_addr from tenement group by Tenement_intra_addr having count(*)>1

SELECT a.Tenement_ip_addr from tenement a,middle b where a.Tenement_ip_addr = b.Middle_ip_addr 

#include "mainwindow.h"

#include <QMessageBox>

#include <QtSql>

#include <QDebug>

MainWindow::MainWindow(QWidget *parent)

    : QMainWindow(parent)

{

QSqlDatabase db = QSqlDatabase::addDatabase ("QODBC");

db.setHostName ("127.0.0.1");

db.setDatabaseName ("anenda");

db.setUserName ("root");

db.setPassword ("root");

if(db.open()){

//db.exec("SET NAMES ’utf8’");

QString str = "insert into `user`(ID,Pass) VALUES(1,MD5('wicom'))";

QString str1 = "insert into `user`(ID,Pass) VALUES(2,MD5('xjw'))";

QString ss = "xjw";

QString s = "Select * From user where ID = 1 and pass = MD5( '"+ss+"')";

QSqlQuery query;

query.exec(str);

query.exec(str1);

int i = query.exec(s);

int kk = query.isActive();

if(i){

    int ti = 0;

    QString id, fname,fCollege,fMajor,fGrade;

    //id = "sql = '"+s+"'";

    while(query.next()){ // 定位结果到下一条记录fromLocal8Bit

     id = query.value(0).toString();

     fname = query.value(1).toString();

     strcpy(tenement_device1.Addr,query.value(1).toByteArray().data());

    }

}

}

}

void Widget::on_pushButton_clicked(){    if(QSqlDatabase::database().driver()->hasFeature(QSqlDriver::Transactions))    { //先判断该数据库是否支持事务操作        if(QSqlDatabase::database().transaction()) //启动事务操作        {            //下面执行各种数据库操作
            QSqlQuery query;            query.exec("insert into student values (14, 'hello')");            query.exec("delete from student where id = 1");            if(!QSqlDatabase::database().commit())            {                qDebug() << QSqlDatabase::database().lastError(); //提交                if(!QSqlDatabase::database().rollback())                    qDebug() << QSqlDatabase::database().lastError(); //回滚             }        }    }}

SELECT *FROM repairs ORDER BY dtime,stime DESC  先按dtime排序，再按stime排序

    QString str= "M0002" ;
    QString SQL1 = "alter table card add "+str+" int default 0 ";//添加字段
    QSqlQuery query1;

    alter table card change M0001130  M1113121 int(20)//修改M0001130字段的名称

    alter table card DROP column M0001//删除指定列

    QString SQL1 = "SHOW FIELDS FROM card";//查询数据库的字段名
select count(*) from card//查询有多少条数据DESC card//显示字段信息select count(*) from information_schema.`COLUMNS` where TABLE_NAME='card';//查询表的字段数量
    QSqlQuery query1;
    int i = query1.exec(SQL1);
    qDebug()<<"i"<<i;
    while(query1.next())
    {
        QString name = query1.value(0).toString();
        qDebug()<<name;
    }

insert into `operator`(pass,user) VALUES(MD5('anednda'),'admin');//数据库加密

UPDATE operator SET pass = MD5('anenda') WHERE user = "admin"

Select * From operator where user = '"+user+"' and pass = MD5( '"+pass+"')";
delete from operator where pass = MD5('anenda')

SELECT a.ID,a.ip_addr FROM alarm a,tenement_equipment b WHERE a.mac_addr = b.mac_addr ORDER BY b.ID DESC//联合查询

SELECT count(*) FROM alarm a, tenement_equipment b WHERE alarm_stime > '"+stime+"' AND a.mac_addr = b.mac_addr //统计查询的几率条数

Select * FROM userWhere name LIKE '%三%'//模糊查询

access数据库连接

    QSqlDatabase db = QSqlDatabase::addDatabase ("QODBC");//通过ODBC数据源连接数据库
    //db.setDatabaseName ("info");//通过配置好的数据源连接
    //db.setDatabaseName("DRIVER={Microsoft Access Driver (*.mdb)};FIL={MS Access};DBQ=./ManagerBase.mdb");//mdb版access数据库直接不配置数据源连接
    QString sDbNm = "./info.accdb";//数据库文件
    QString dsn = QString("DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; FIL={MS Access};DBQ=%1;").arg(sDbNm);
//accdb版access数据库直接不配置数据源连接
    db.setDatabaseName(dsn);
    if(!db.open())