QT odbc mysql

#include "mainwindow.h"
#include "ui_mainwindow.h"
#include <QDebug>
#include <QSqlDatabase>
#include <QSqlError>
#include <QSqlQuery>
#include <QMessageBox>
MainWindow::MainWindow(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    //显示可用驱动
    //qDebug()<<"available drivers:";
    //QStringList drivers = QSqlDatabase::drivers();
    //foreach(QString driver, drivers)
    //    qDebug()<<driver;

    //使用ODBC连接数据库
    QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");
    db.setHostName("127.0.0.1");
    db.setPort(3306);
    db.setDatabaseName("mysql");
    db.setUserName("root");
    db.setPassword("123456");
    bool ok = db.open();
    if (ok){
       qDebug()<<"open databease sucess";
    }
    else {
        qDebug()<<"error open database because"<<db.lastError().text();
    }


    QSqlQuery query1 = QSqlQuery(db);
    //创建数据库
    //query1.exec("create database if not exists tmysql");
    //删除数据库
    //if(query1.exec("drop database if exists tmysql;")) qDebug()<<"drop database success";
    //使用数据库
    query1.exec("use tmysql;");

    //创建表
    //if(query1.exec("create table if not exists stu \
    //            (stuid int primary key,\
    //            stuname varchar(50) not null,\
    //            studept varchar(20));"))
    //{
    //    qDebug()<<"create table success";
    //}

    //删除表
    //if(query1.exec("drop table student;"))
    //{
    //   qDebug()<<"drop table success";
    //}

    //修改表名
    //query1.exec("alter table stu rename student;");

    //添加属性列
    //query1.exec("alter table student add sex varchar(10) not null;");

    //更改属性列名
    //query1.exec("alter table student change sex stusex varchar(10) not null;");

    //修改属性列的数据类型
    //query1.exec("alter table student modify stusex varchar(20) not null;");

    //删除表的属性
    //query1.exec("alter table student drop stusex;");

    //删除表
    //query1.exec("drop table student;");

    //插入表数据
    //if(query1.exec("insert into stu (stuid,stuname,studept) values (20211,'小赵','cs');"))
    //{
    //    qDebug()<<"insert table data sucess";
    //}

    //if(query1.exec("insert into stu (stuid,stuname,studept) values (20212,'小王','kj');"))
    //{
    //    qDebug()<<"insert table data sucess";
    //}

    //if(query1.exec("insert into stu (stuid,stuname,studept) values (20213,'小李','cf');"))
    //{
    //    qDebug()<<"insert table data sucess";
    //}

    //if(query1.exec("insert into stu (stuid,stuname,studept) values (20214,'小刘','gc');"))
    //{
    //    qDebug()<<"insert table data sucess";
    //}

    //查找表数据
    //if(query1.exec("select * from stu where stuid = 20211"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString();
    //    }
    //}

    //like语句
    //if(query1.exec("select * from stu where studept like '%c';"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString();
    //    }
    //}

    //union语句
    //if(query1.exec("select * from stu where stuId=20211 union select * from stu where stuId=20214;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString();
    //    }
    //}


    //if(query1.exec("select * from stu;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString();
    //    }
    //}

    //修改表数据
    //if(query1.exec("update stu set stuname = '小魏' where stuid = 20211;"))
    //{
    //    qDebug()<<"update table success";
    //}

    //删除表数据
    //if(query1.exec("delete from stu where stuid = 20213;"))
    //{
    //     qDebug()<<"delete sucess";
    //}

    //order by
    //if(query1.exec("select * from stu order by stuId desc;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString();
    //    }
    //}

    //if(query1.exec("select studept, count(*) from stu group by studept;" ))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toString()
    //                <<query1.value(1).toInt();
    //    }
    //
    //}

    //内连接
    //if(query1.exec("select stuId,deptId,stu.studept,dept.deptname from stu inner join dept on stu.studept = dept.deptname;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString()
    //                <<query1.value(3).toString();
    //    }
    //}

    //右外连接
    //if(query1.exec("select stuId,deptId,stu.studept,dept.deptname from stu right join dept on stu.studept = dept.deptname;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString()
    //                <<query1.value(3).toString();
    //    }
    //}

    //右外连接
    //if(query1.exec("select stuId,deptId,stu.studept,dept.deptname from stu right join dept on stu.studept = dept.deptname;"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toInt()
    //                <<query1.value(1).toString()
    //                <<query1.value(2).toString()
    //                <<query1.value(3).toString();
    //    }
    //}

    //值为NULL
    //if(query1.exec("select * from record where netCount is null"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toString()
    //                <<query1.value(1).toInt();
    //    }
    //}


    //值不为NULL
    //if(query1.exec("select * from record where netCount is not null"))
    //{
    //    while(query1.next())
    //    {
    //        qDebug()<<query1.value(0).toString()
    //                <<query1.value(1).toInt();
    //    }
    //}

    //事务
    //query1.exec("begin;");

    //query1.exec("insert into record (netName,netCount) values ('www.motus.com',5);");

    //query1.exec("rollback;");

    //query1.exec("commit;");

    db.close();
}

MainWindow::~MainWindow()
{
    delete ui;
}