QT与Sql Server联动

一、Sql Server 创建

参考这篇Sql Server 创建（感谢博主）

二、Sql Server 数据库设计 ER模型

IF EXISTS (SELECT * FROM sysobjects  where name='BOOK') 
drop database BOOK_MS    --删除已有的BOOK_MS库

CREATE database BOOK_MS  --创建BOOK_MS库

---------------------------------------------
--***********SQL语句创建数据表***********----
---------------------------------------------
GO            --go不是SQL语句，它只是MS SQL Server的一个命令 go可以把一个SQL脚本文件上的众多SQL语句，分为多个批次发送给数据库引擎去执行
USE BOOK;  --使用BOOK_MS库，作为当前查询的库

IF EXISTS (SELECT * FROM sysobjects  where name='MANAGEMENTOR') --管理员
drop table MANAGEMENTOR
CREATE table MANAGEMENTOR
(
	ID varchar(20)not null,
	NAME varchar(10)not null,
	CODE varchar(10)not null,
	primary key(ID)
);


IF EXISTS (SELECT * FROM sysobjects  where name='TEACHER')  --老师
drop table TEACHER
CREATE table TEACHER
(
	ID_TEACHER varchar(20)not null,
	NAME_TEACHER varchar(10)not null,
	CODE varchar(10)not null,
	primary key(ID_TEACHER)
);

IF EXISTS (SELECT * FROM sysobjects  where name='STUDENT') --学生
drop table STUDENT
CREATE table STUDENT
(
	ID_STUDENT varchar(20)not null,
	NAME_STUDENT varchar(10)not null,
	CODE varchar(10)not null,
	primary key(ID_STUDENT)
);

IF EXISTS (SELECT * FROM sysobjects  where name='CLASS') --班级
drop table CLASS
CREATE table CLASS
(
	NAME_CLASS varchar(10)not null,
	NUM varchar(10)not null,
	primary key(NAME_CLASS)
);

IF EXISTS (SELECT * FROM sysobjects  where name='COURSE') --课程
drop table COURSE
CREATE table COURSE
(
	NAME_COURSE varchar(10)not null,
	NAME_CLASS varchar(10) references CLASS(NAME_CLASS)not null,
	ID_TEACHER varchar(20) references TEACHER(ID_TEACHER)not null,
	foreign key(NAME_CLASS)REFERENCES CLASS,
	foreign key(ID_TEACHER)REFERENCES TEACHER,
);

IF EXISTS (SELECT * FROM sysobjects  where name='LAB') --实验室表
drop table LAB
CREATE table LAB
(
	ID_LAB varchar(20)not null,
	NAME_LAB varchar(10)not null,  --实验室门牌号
	NUM_LAB varchar(10)not null,  --实验室容纳人数
	primary key(NAME_LAB)
);

GO            --go不是SQL语句，它只是MS SQL Server的一个命令 go可以把一个SQL脚本文件上的众多SQL语句，分为多个批次发送给数据库引擎去执行
USE BOOK;  --使用BOOK_MS库，作为当前查询的库

IF EXISTS (SELECT * FROM sysobjects  where name='COURSE') --课程
drop table COURSE
CREATE table COURSE
(
	NAME_COURSE varchar(10)not null,
	NAME_CLASS varchar(10) references CLASS(NAME_CLASS)not null,
	ID_TEACHER varchar(20) references TEACHER(ID_TEACHER)not null,
	NAME_LAB varchar(10)references LAB(NAME_LAB)not null,
	foreign key(NAME_LAB)REFERENCES LAB,
	foreign key(NAME_CLASS)REFERENCES CLASS,
	foreign key(ID_TEACHER)REFERENCES TEACHER
);

IF EXISTS (SELECT * FROM sysobjects  where name='EXPERIMENT') --实验室（老师）
drop table EXPERIMENT
CREATE table EXPERIMENT
(
	WEEK varchar(5)not null check(WEEK<=5 AND WEEK>0),
	DAY varchar(5)not null check(DAY<=5 AND DAY>0),
	JIE varchar(5)not null check(jie<=5 AND jie>0),
	NAME_COURSE varchar(10)not null,
	ID_TEACHER varchar(20)references TEACHER(ID_TEACHER)not null,
	NAME_CLASS varchar(10)references CLASS(NAME_CLASS)not null,
	NAME_LAB varchar(10)references LAB(NAME_LAB)not null,
	foreign key(NAME_LAB)REFERENCES LAB,
	foreign key(ID_TEACHER)REFERENCES TEACHER,
	foreign key(NAME_CLASS)REFERENCES CLASS
);

IF EXISTS (SELECT * FROM sysobjects  where name='STATE') --实验室状态（管理员）
drop table STATE
CREATE table STATE
(
	WEEK varchar(5)not null check(WEEK<=5 AND WEEK>0),
	DAY varchar(5)not null check(DAY<=5 AND DAY>0),
	JIE varchar(5)not null check(jie<=5 AND jie>0),
	FLAG varchar(10)not null,
	NAME_LAB varchar(10)references LAB(NAME_LAB)not null,
	foreign key(NAME_LAB)REFERENCES LAB,
);

IF EXISTS (SELECT * FROM sysobjects  where name='EXPERIMENT_NUM')  --学生实验室容量（学生）
drop table EXPERIMENT_NUM
CREATE table EXPERIMENT_NUM
(
	WEEK varchar(5)not null check(WEEK<=5 AND WEEK>0),
	DAY varchar(5)not null check(DAY<=5 AND DAY>0),
	JIE varchar(5)not null check(jie<=5 AND jie>0),
	NUM varchar(5)not null, check(NUM>=0 AND NUM<=100),
	NAME_LAB varchar(10)references LAB(NAME_LAB)not null,
	foreign key(NAME_LAB)REFERENCES LAB,
);
IF EXISTS (SELECT * FROM sysobjects  where name='EXPERIMENT_STUDENT') --实验室预约（学生）
drop table EXPERIMENT_STUDENT
CREATE table EXPERIMENT_STUDENT
(
	WEEK varchar(5)not null check(WEEK<=5 AND WEEK>0),
	DAY varchar(5)not null check(DAY<=5 AND DAY>0),
	JIE varchar(5)not null check(jie<=5 AND jie>0),
	ID_STUDENT varchar(20)references STUDENT(ID_STUDENT)not null,
	NAME_LAB varchar(10)references LAB(NAME_LAB)not null,
	foreign key(NAME_LAB)REFERENCES LAB,
);

三、QT连接数据库

1.模块、头文件、QT数据库对象包含创建

por工程文件里：
QT += sql
mianwindow.h里
#include <QtDebug> #include <QSqlQuery> #include <QSqlDatabase>

2.QT连接数据库

在其 构造函数 直接连接

MainWindow::MainWindow(QWidget *parent) :
    QMainWindow(parent),
    ui(new Ui::MainWindow)
{
    ui->setupUi(this);
    db = QSqlDatabase::addDatabase("QODBC"); //默认
    db.setHostName("LAPTOP-0NTOAEE3");//服务器名
    db.setDatabaseName("HXY_DB");//数据源名
    db.setUserName("YU");//连接数据库用户名
    db.setPassword("1230abcd");//连接数据库密码
    if(!db.open())
    {
        qDebug() << "数据库打开失败";
    }
    else
    {
        qDebug()<<"数据库打开成功";
    }
}

显示连接成功则可进行下一步

四、QT操作数据库

	QSqlQuery query;
    query.exec("select * from MANAGEMENTOR where ID = " + a + "and CODE =  " + b);
    query.next();
    qDebug() << query.value(0).toString()  << query.value(1).toString() << query.value(2).toString();