数据库课程设计(民航售票子系统含数据库代码和前端代码）

于 2020-01-19 21:13:20 发布
阅读量2.9k 收藏 60
点赞数 9
分类专栏： 数据库课程设计 文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43730516/article/details/104045156
版权

我用的是Qt做界面的，先来看看效果吧：
用户登录界面
注册界面
主界面
用户信息界面
用户密码修改界面
表格按钮对应的模块
登录界面：

#include <QPushButton>
#include "regisered.h"
#include "maininterface.h"
#include "userpasswod.h"

//声明外部变量
extern QString usernamelineEdit;
extern QString useridlineEdit;
extern QString userstylelineEdit;
extern QString userphonelineEdit;



#define cout qDebug()<<'['<<_FILE_<<':'<<_LINE_<<']'
namespace Ui {
class Widget;
}

class Widget : public QWidget
{
    Q_OBJECT

public:
    explicit Widget(QWidget *parent = 0);
    ~Widget();
private:
    Ui::Widget *ui;
};

#endif // WIDGET_H

.cpp文件：

#include "widget.h"
#include "ui_widget.h"
#include <QPixmap>


 QString usernamelineEdit;
 QString useridlineEdit;
 QString userstylelineEdit;
 QString userphonelineEdit;


Widget::Widget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Widget)
{
        ui->setupUi(this);
        this->setWindowTitle("航空售票系统");
        //设置标题图片
        this->setWindowIcon(QIcon(QPixmap(":/icon/icon/show.png")));
        //设置登录界面图片
        ui->usericon->setPixmap(QPixmap(":/icon/icon/yonghu.png"));
        ui->usericon->setAlignment(Qt::AlignCenter);
        //设置控件间隙
        QHBoxLayout *layout=new QHBoxLayout(this);
        layout->setMargin(0);
        layout->setSpacing(0);
        //创建注册窗口的指针
        regisered *showregisered=new regisered();
        //创建主界面窗口的指针
        maininterface *showmaininterface=new maininterface();
        QSqlDatabase db = QSqlDatabase::addDatabase("QODBC");   //数据库驱动类型为SQL Server
        qDebug()<<"ODBC driver?"<<db.isValid();
        QString dsn=QString::fromLocal8Bit("QOBM");
        db.setHostName("localhost");                        //选择本地主机，127.0.1.1
        db.setDatabaseName(dsn);                            //设置数据源名称
        db.setUserName("sa");                               //登录用户
        db.setPassword("123456");                           //密码
        if(!db.open())                                      //打开数据库
        {
            qDebug()<<db.lastError().text();
            QMessageBox::critical(0, QObject::tr("Database error"), db.lastError().text());
            return;                                   //打开失败
        }
        //登录按钮
        connect(ui->loginbtn,&QPushButton::clicked,this,[=](){
            QString userid=ui->userid->text();
            QString userpassword=ui->userpassword->text();
            QString userinfo=QString("select *from userinfo "
            "where id=%1 and userpassword=%2").arg(userid).arg(userpassword);
            QSqlQuery query;
            if(userid==""||userpassword=="")
            {
                QMessageBox::information(this,"登录信息","用户名或密码不能为空！");
            }
            else if(query.exec(userinfo)==true&&query.next()==true)
            {
                 useridlineEdit=query.value(0).toString();
                 usernamelineEdit=query.value(1).toString();
                 userstylelineEdit=query.value(2).toString();
                 userphonelineEdit=query.value(3).toString();
                this->hide();
               showmaininterface->show();
            }
            else
                QMessageBox::information(this,"登录信息","密码或用户名错误！");
        });
        //注册按钮
        connect(ui->regstarbtn,&QPushButton::clicked,this,[=](){
            this->hide();
            showregisered->show();
        });
}

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

主界面：

#ifndef MAININTERFACE_H
#define MAININTERFACE_H

#include <QWidget>
#include <QToolButton>
#include "widget.h"
#include <QComboBox>
#include <QToolButton>
#include <QSqlQueryModel>
#include <QDateTime>
#include <QMessageBox>
#include "userinfowidget.h"
#include "userpasswod.h"
#include "userpasswod.h"
#include <QCloseEvent>


namespace Ui {
class maininterface;
}

class maininterface : public QWidget
{
    Q_OBJECT

public:
    explicit maininterface(QWidget *parent = 0);
    ~maininterface();
    void closeEvent(QCloseEvent *event);
private:
    Ui::maininterface *ui;
};

#endif // MAININTERFACE_H

.cpp文件：

#include "maininterface.h"
#include "ui_maininterface.h"
#include <QLayout>
#include <QHBoxLayout>
#include <QMovie>

maininterface::maininterface(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::maininterface)
{
    ui->setupUi(this);
    this->setWindowTitle("航空售票系统主界面窗口");
    //声明用户信息显示窗口的指针
    userinfoWidget *userinfowidget=new userinfoWidget();
    //声明用户修改密码的指针
    userpasswod *password=new userpasswod();
    //设置tableview整行被选中
    ui->buyticketstableView->setSelectionBehavior(QAbstractItemView::SelectRows);
    //设置奇偶航颜色不同
    ui->airlinetableView->setAlternatingRowColors(true);
    ui->airticketstableView->setAlternatingRowColors(true);
    ui->cortableView->setAlternatingRowColors(true);
    ui->routetableView->setAlternatingRowColors(true);
    ui->timetableView->setAlternatingRowColors(true);
    ui->buyticketstableView->setAlternatingRowColors(true);
    ui->ordertableView->setAlternatingRowColors(true);
    //设置下拉日期
    ui->routedateEdit->setCalendarPopup(true);
    ui->userdateEdit->setCalendarPopup(true);
    ui->airticketsdateEdit->setCalendarPopup(true);
    ui->buyticketsdateEdit->setCalendarPopup(true);
    //设置控件间隙
    QHBoxLayout *layout=new QHBoxLayout(this);
    layout->setMargin(0);
    layout->setSpacing(0);
    //选择栏
    //用户按钮
    connect(ui->userchoosebtn,&QToolButton::clicked,this,[=](){
        ui->infostackedWidget->setCurrentIndex(0);
    });
    //表格按钮
    connect(ui->formbtn,&QToolButton::clicked,this,[=](){
        ui->infostackedWidget->setCurrentIndex(1);
    });
    //设置标题图片
    this->setWindowIcon(QIcon(QPixmap(":/icon/icon/show.png")));
    //设置默认显示页
    ui->showWidget->setCurrentIndex(0);
    ui->infostackedWidget->setCurrentIndex(2);
    //接受注销用户按钮发来的信号
    connect(userinfowidget,&userinfoWidget::mainfacecolose,this,[=](){
        this->hide();
    });
    //退出系统按钮
    connect(ui->dropoutsystem,&QToolButton::clicked,this,[=](){
        int youchoose=QMessageBox::information(this,"信息窗口","你确定退出售票系统吗？",QMessageBox::Yes|QMessageBox::No);
        if(youchoose==QMessageBox::Yes)
        {
          exit(0);
        }
    });
    //查询代售地信息下拉框内容
    ui->daytimecombox->addItem("2020年01月份");
    ui->daytimecombox->addItem("2020年02月份");
    ui->daytimecombox->addItem("2020年03月份");
    ui->daytimecombox->addItem("2020年04月份");
    ui->daytimecombox->addItem("2020年05月份");
    ui->daytimecombox->addItem("2020年06月份");
    ui->daytimecombox->addItem("2020年07月份");
    ui->daytimecombox->addItem("2020年08月份");
    ui->daytimecombox->addItem("2020年09月份");
    ui->daytimecombox->addItem("2020年10月份");
    ui->daytimecombox->addItem("2020年11月份");
    ui->daytimecombox->addItem("2020年12月份");
    //设置座位类型下拉框内容
    ui->seatstylecombox->addItem("甲等座");
    ui->seatstylecombox->addItem("乙等座");
    //查询代售地信息按钮
    connect(ui->consignmentinfobtn,&QToolButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(1);
    });
    //查询航空公司信息按钮
    connect(ui->aviationinfobtn,&QPushButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(2);
    });
    //查询航班票数或座位按钮
    connect(ui->flightinfobtn,&QPushButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(3);
    });
    //查询机票信息按钮
    connect(ui->airticketinfobtn,&QPushButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(4);
    });
    //查询航线的售票数按钮
    connect(ui->routebtn,&QToolButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(5);
    });
    //获取下拉框
    connect(ui->inqirebtn,&QPushButton::clicked,this,[=](){
        QString daytime=ui->daytimecombox->currentText();
        int index=ui->daytimecombox->currentText().mid(5,2).toInt();
        QString yeartime=daytime.mid(0,4);
        QString addr=ui->addrtext->text();
        //查某代售地某月售出的票数及金额
        QString selectcorsignmentinfo=QString("select cast(year(daytime)as "
        "varchar(50))+'-'+cast(month(daytime)as varchar(50)) 时间,addr 地址, "
        "ticketnum 售出票数,ticketprice 售出总金额 from corsignmentinfo "
        "join airportinfo on airportinfo.airportid=corsignmentinfo.airportid "
        "where year(daytime)=%1 and month(daytime)=%2 "
        "and addr='%3'").arg(yeartime).arg(index).arg(addr);
        QSqlQueryModel *model=new QSqlQueryModel(ui->cortableView);
        //写入SQL语句
        model->setQuery(selectcorsignmentinfo);
        //将数据写道tableview中
        ui->cortableView->setModel(model);
        //隐藏序号
        ui->cortableView->verticalHeader()->hide();
        //设置列宽度自适应
        ui->cortableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    });
    //航空公司信息查询按钮
    connect(ui->airlinebtn,&QPushButton::clicked,this,[=](){
        QString airlinename=ui->airlinenametext->text();
        //查航空公司拥有多少航班
        QString selectairline=QString("select airlinename 公司名称,count(airlinename)/2 航班数量 "
       "from supplyinfo join airlineinfo "
       "on supplyinfo.airlineid=airlineinfo.airlineid "
       "group by airlinename "
       "having airlinename='%1'").arg(airlinename);
        QSqlQueryModel *airlinemodel=new QSqlQueryModel(ui->airlinetableView);
        airlinemodel->setQuery(selectairline);
        ui->airlinetableView->setModel(airlinemodel);
        ui->airlinetableView->verticalHeader()->hide();
        ui->airlinetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    });
    //航班票数或座位查询按钮
    connect(ui->flightbtn,&QPushButton::clicked,this,[=](){
        //获取系统时间
        QDateTime getsystemtime=QDateTime::currentDateTime();
        QString gettime=getsystemtime.toString("yyyy-MM-dd");
        //获取用户输入的航班编号
        QString flightid=ui->flightidtext->text();
        //获取用户输入的日期
        QString usertime=ui->userdateEdit->text();
        //查询航班座数量的SQL语句
        QString votenumtwo;
        QString votenum=QString("select sum(votesnum) from seatinfo where seatid "
        "in(select seatid from flightinfo where flightid='%1')").arg(flightid);
        QSqlQuery queryvotenum;
        if(queryvotenum.exec(votenum))
        {
         if(queryvotenum.next())
         {
             votenumtwo=queryvotenum.value(0).toString();
         }
        }
        //查某天某航空还剩多少票或座位
        QString selecttime=QString(
        "select distinct(flightid) 航班编号,%5 座位数量或票数 from flightinfo "
        "where flightid='%1' and year(begintime)='%2' and month(begintime)='%3' "
        "and day(begintime)='%4'")
        .arg(flightid).arg(usertime.mid(0,4)).arg(usertime.mid(5,2)).arg(usertime.mid(8,2))
        .arg(votenumtwo);
        if(gettime.mid(0,4).toInt()<usertime.mid(0,4).toInt())
        {
           QSqlQueryModel *timequeryone=new QSqlQueryModel(ui->timetableView);
           //执行SQL语句
           timequeryone->setQuery(selecttime);
           //向控件添加数据库内容
           ui->timetableView->setModel(timequeryone);
           //消去序号
           ui->timetableView->verticalHeader()->hide();
           //控件自适应宽度
           ui->timetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettime.mid(0,4).toInt()==usertime.mid(0,4).toInt()&&
                gettime.mid(5,2).toInt()<=usertime.mid(5,2).toInt())
        {
           QSqlQueryModel *timequerytwo=new QSqlQueryModel(ui->timetableView);
           //执行SQL语句
           timequerytwo->setQuery(selecttime);
           //向控件添加数据库内容
           ui->timetableView->setModel(timequerytwo);
           //消去序号
           ui->timetableView->verticalHeader()->hide();
           //控件自适应宽度
           ui->timetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettime.mid(0,4).toInt()==usertime.mid(0,4).toInt()&&
                gettime.mid(5,2).toInt()==usertime.mid(5,2).toInt()&&
                gettime.mid(8,2).toInt()<=usertime.mid(8,2).toInt())
        {
            QSqlQueryModel *timequerytwo=new QSqlQueryModel(ui->timetableView);
            //执行SQL语句
            timequerytwo->setQuery(selecttime);
            //向控件添加数据库内容
            ui->timetableView->setModel(timequerytwo);
            //消去序号
            ui->timetableView->verticalHeader()->hide();
            //控件自适应宽度
            ui->timetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else
            QMessageBox::information(this,"信息窗口","输入日期错误!");

    });
    //机票信息查询按钮
    connect(ui->airjicketsbtn,&QPushButton::clicked,this,[=](){
        //获取系统时间
        QDateTime getsystemtime=QDateTime::currentDateTime();
        QString airticketsgettime=getsystemtime.toString("yyyy-MM-dd");
        //获取用户输入的航班编号
        QString airticketsid=ui->airticketsidtext->text();
        //查询航班座数量的SQL语句
        QString Avotepricetwo;
        QString Bvotepricetwo;
        QString Avoteprice=QString("select votesprice from flightinfo "
        "join seatinfo on flightinfo.seatid=seatinfo.seatid where votestyle='甲等座' "
        " and flightid='%1'").arg(airticketsid);
        QSqlQuery queryvoteprice;
        if(queryvoteprice.exec(Avoteprice))
        {
         if(queryvoteprice.next())
         {
             Avotepricetwo=queryvoteprice.value(0).toString();
         }
        }
        QString Bvoteprice=QString("select votesprice from flightinfo "
        "join seatinfo on flightinfo.seatid=seatinfo.seatid where votestyle='乙等座' "
        "and flightid='%1'").arg(airticketsid);
        QSqlQuery queryvotepricetwo;
        if(queryvotepricetwo.exec(Bvoteprice))
        {
            if(queryvotepricetwo.next())
            {
                Bvotepricetwo=queryvotepricetwo.value(0).toString();
            }
        }
        //获取用户输入的日期
        QString usertimeinfo=ui->airticketsdateEdit->text();
        //查某天某航空还剩甲等座位以及乙等座位票价
        QString selectairtickets=QString(
        "select distinct(flightid) 航班编号,%5 甲等座票价,%6 乙等座票价 from flightinfo "
        "where flightid='%1' and "
        "year(begintime)='%2' "
        "and month(begintime)='%3' and day(begintime)='%4'").arg(airticketsid)
        .arg(usertimeinfo.mid(0,4)).arg(usertimeinfo.mid(5,2)).arg(usertimeinfo.mid(8,2))
        .arg(Avotepricetwo).arg(Bvotepricetwo);
        if(airticketsgettime.mid(0,4).toInt()<usertimeinfo.mid(0,4).toInt())
        {
            //qDebug()<<"执行第一个if语句";
            QSqlQueryModel *airticketsmodel=new QSqlQueryModel(ui->airticketstableView);
            airticketsmodel->setQuery(selectairtickets);
            ui->airticketstableView->setModel(airticketsmodel);
            ui->airticketstableView->verticalHeader()->hide();
            ui->airticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(airticketsgettime.mid(0,4).toInt()==usertimeinfo.mid(0,4).toInt()
                &&usertimeinfo.mid(5,2).toInt()>airticketsgettime.mid(5,2).toInt())
        {
            // qDebug()<<"执行第二个if语句";
            QSqlQueryModel *airticketsmodel=new QSqlQueryModel(ui->airticketstableView);
            airticketsmodel->setQuery(selectairtickets);
            ui->airticketstableView->setModel(airticketsmodel);
            ui->airticketstableView->verticalHeader()->hide();
            ui->airticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(airticketsgettime.mid(0,4).toInt()==usertimeinfo.mid(0,4).toInt()
                &&airticketsgettime.mid(5,2).toInt()==usertimeinfo.mid(5,2).toInt()
                &&airticketsgettime.mid(8,2).toInt()<=usertimeinfo.mid(8,2).toInt())
        {
            //qDebug()<<"执行第三个if语句";
            QSqlQueryModel *airticketsmodel=new QSqlQueryModel(ui->airticketstableView);
            airticketsmodel->setQuery(selectairtickets);
            ui->airticketstableView->setModel(airticketsmodel);
            ui->airticketstableView->verticalHeader()->hide();
            ui->airticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else
        {
            QMessageBox::information(this,"信息窗口","输入日期错误！");
        }
    });
    //航线的售票数查询按钮
    connect(ui->routeinquirebtn,&QPushButton::clicked,this,[=](){
        //获取系统时间
        QDateTime getsystemtime=QDateTime::currentDateTime();
        QString gettimeone=getsystemtime.toString("yyyy-MM-dd");
        QString airlinename=ui->airlinenametexttwo->text();
        //获取用户输入的日期
        QString usertime=ui->routedateEdit->text();
        QString beginaddr=ui->beginaddrtext->text();
        QString endaddr=ui->endaddrtext->text();
        QSqlQuery queryselectnuminfo;
        QString selectnuminfo;
        QString selectnuminfoone=QString("select count(distinct(airportid)) from corsignmentinfo "
        " where airlineid=(select airlineid from airlineinfo where airlinename='%1')").arg(airlinename);
        if(queryselectnuminfo.exec(selectnuminfoone))
        {
            if(queryselectnuminfo.next())
            {
                selectnuminfo=queryselectnuminfo.value(0).toString();
            }
        }
        //查某航天公司拥有多少售票点、某月售出总金额以及某航线售出票数
        QString selectroute=QString(" select distinct(airlinename) 公司名称,%6 售票点数量,"
        "cast(year(daytime) as varchar(50)) +'-'+cast(month(daytime) "
        "as varchar(50)) 时间,ticketprice 总金额,beginaddr+' - '+endaddr 航线,ticketnum 票数 "
        "from corsignmentinfo join airlineinfo on corsignmentinfo.numid=airlineinfo.airlineid "
        ",supplyinfo join flightinfo on supplyinfo.flightindex=flightinfo.flightindex "
        "where airlinename='%1' and "
        "year(daytime)='%2' and month(daytime)='%3' and beginaddr='%4' "
        "and endaddr='%5'").arg(airlinename).arg(usertime.mid(0,4)).arg(usertime.mid(5,2)).arg(beginaddr)
         .arg(endaddr).arg(selectnuminfo);
        if(ui->airlinenametexttwo->text()=="")
        {
            QMessageBox::information(this,"信息窗口","请输入航空公司的名称！");
        }
        if(gettimeone.mid(0,4).toInt()<usertime.mid(0,4).toInt())
        {
            QSqlQueryModel *routemodel=new QSqlQueryModel(ui->routetableView);
            routemodel->setQuery(selectroute);
            ui->routetableView->setModel(routemodel);
            ui->routetableView->verticalHeader()->hide();
            ui->routetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettimeone.mid(0,4).toInt()==usertime.mid(0,4).toInt()&&
                gettimeone.mid(5,2).toInt()<=usertime.mid(5,2).toInt())
        {
            QSqlQueryModel *routemodel=new QSqlQueryModel(ui->routetableView);
            routemodel->setQuery(selectroute);
            ui->routetableView->setModel(routemodel);
            ui->routetableView->verticalHeader()->hide();
            ui->routetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettimeone.mid(0,4).toInt()==usertime.mid(0,4).toInt()&&
                gettimeone.mid(5,2).toInt()==usertime.mid(5,2).toInt()&&
                gettimeone.mid(8,2).toInt()<=usertime.mid(8,2).toInt())
        {
            QSqlQueryModel *routemodel=new QSqlQueryModel(ui->routetableView);
            routemodel->setQuery(selectroute);
            ui->routetableView->setModel(routemodel);
            ui->routetableView->verticalHeader()->hide();
            ui->routetableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else
            QMessageBox::information(this,"信息窗口","输入日期错误！");
    });
    //用户信息按钮实现
    connect(ui->userinfoshowbtn,&QToolButton::clicked,this,[=](){
        password->hide();
        userinfowidget->hide();
       userinfowidget->show();
    });
    //修改密码按钮实现
    connect(ui->passwordmodifybtn,&QPushButton::clicked,this,[=](){
        userinfowidget->hide();
        password->hide();
        password->show();
    });
    //接受用户修改密码界面的信号
    connect(password,&userpasswod::mainfaceclose,this,[=](){
        this->hide();
    });
    //购票按钮
    connect(ui->buyticketstoolButton,&QToolButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(6);
    });
    //机票查询按钮
    connect(ui->buyticketsbtn,&QPushButton::clicked,this,[=](){
        //获取系统时间
        QDateTime getsystemtime=QDateTime::currentDateTime();
        QString gettimetwo=getsystemtime.toString("yyyy-MM-dd");
        //获取用户输入时间
        QString userbuytime=ui->buyticketsdateEdit->text();
        //获取下拉框文本
        QString seatsyletext=ui->seatstylecombox->currentText();
        //获取用户填写的地址
        QString beginaddr=ui->beginaddrlineEdit->text();
        QString endaddr=ui->endaddrlineEdit->text();
        //编写SQL语句
        //购票信息查询
        QString selecttickets=QString("select distinct(flightid) 航班编号,begintime 起飞时间,endtime 结束时间,beginaddr 出发城市,"
        "endaddr 到达城市,votesprice 价格,votestyle 座位类型 from flightinfo,seatinfo "
        "where votestyle='%4' and beginaddr='%5' and endaddr='%6' "
        "and year(begintime)='%1' and month(begintime)='%2' and day(begintime)='%3'")
         .arg(userbuytime.mid(0,4)).arg(userbuytime.mid(5,2)).arg(userbuytime.mid(8,2))
         .arg(seatsyletext).arg(beginaddr).arg(endaddr);
        if(gettimetwo.mid(0,4).toInt()<userbuytime.mid(0,4).toInt())
        {
            QSqlQueryModel *buytickets=new QSqlQueryModel(ui->buyticketstableView);
            buytickets->setQuery(selecttickets);
            ui->buyticketstableView->setModel(buytickets);
            ui->buyticketstableView->verticalHeader()->hide();
            ui->buyticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettimetwo.mid(0,4).toInt()==userbuytime.mid(0,4).toInt()&&
                gettimetwo.mid(5,2).toInt()<=userbuytime.mid(5,2).toInt())
        {
            QSqlQueryModel *buytickets=new QSqlQueryModel(ui->buyticketstableView);
            buytickets->setQuery(selecttickets);
            ui->buyticketstableView->setModel(buytickets);
            ui->buyticketstableView->verticalHeader()->hide();
            ui->buyticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else if(gettimetwo.mid(0,4).toInt()==userbuytime.mid(0,4).toInt()&&
                gettimetwo.mid(5,2).toInt()==userbuytime.mid(5,2).toInt()&&
                gettimetwo.mid(8,2).toInt()<=userbuytime.mid(8,2).toInt())
        {
            QSqlQueryModel *buytickets=new QSqlQueryModel(ui->buyticketstableView);
            buytickets->setQuery(selecttickets);
            ui->buyticketstableView->setModel(buytickets);
            ui->buyticketstableView->verticalHeader()->hide();
            ui->buyticketstableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        }
        else
        {
            QMessageBox::information(this,"信息窗口","输入日期错误！");
        }
    });
    //购买按钮
    connect(ui->buybtn,&QPushButton::clicked,this,[=](){
        //获取用户输入日期
        QString userbuytime=ui->buyticketsdateEdit->text();
        int index= ui->buyticketstableView->currentIndex().row();
        QAbstractItemModel *modessl = ui->buyticketstableView->model();
        //获取座位类型下拉框内容
        QString votestyle=ui->seatstylecombox->currentText();
        QString buyinfo[7]={0};
        int count=0;
        while(count<=6)
        {
            QModelIndex indexitem=modessl->index(index,count);
            QString basedate=modessl->data(indexitem).toString();
            buyinfo[count]=basedate;
            count++;
        }
        //调用外部变量
        extern QString useridlineEdit;
        QSqlQuery query;
        QString insertissueticketsinfo=QString("insert issueticketsinfo(userid,flightindex) "
        "values(%1,(select flightindex from flightinfo join "
        "seatinfo on flightinfo.seatid=seatinfo.seatid "
        "where votestyle='%3' and flightid='%2'))")
        .arg(useridlineEdit).arg(buyinfo[0]).arg(buyinfo[6]);
        query.exec(insertissueticketsinfo);
        //查询代售信息表是否有购买记录，如果有就执行更新操作，没有就执行插入操作
        QSqlQuery querycorsignmentinfo;
        QString selectcorsignmentinfo=QString("select distinct(cast(year(daytime) as varchar(50))+'-'+ cast(month(daytime) as varchar(50))),* "
        "from corsignmentinfo "
        "where  airportid= (select distinct(airportid)  from corsignmentinfo "
        "where airlineid=(select airlineid "
        "from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
        "where votestyle='%3' and flightid='%1' ))) "
        "and airlineid= (select airlineid "
        "from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
        "where votestyle='%3' and flightid='%1')) "
        "and year(daytime)=%2 "
        "and month(daytime)=%4 ").arg(buyinfo[0]).arg(userbuytime.mid(0,4)).arg(votestyle).arg(userbuytime.mid(5,2));
        if(querycorsignmentinfo.exec(selectcorsignmentinfo))
        {
            if(querycorsignmentinfo.next())
            {
                QSqlQuery queryupdateone;
                QString updatecorsignmentinfoone=QString("update corsignmentinfo set ticketnum=ticketnum+1 "
                "where  airportid= (select distinct(airportid)  from corsignmentinfo  "
                "where airlineid=(select airlineid "
                "from supplyinfo where flightindex=(select flightindex from "
                "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                "where votestyle='%3' and flightid='%1'))) "
                "and airlineid= (select airlineid "
                "from supplyinfo where flightindex=(select flightindex from "
                "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                "where votestyle='%3' and flightid='%1')) "
                " and year(daytime)=%2 "
                "and month(daytime)=%4").arg(buyinfo[0]).arg(userbuytime.mid(0,4)).arg(votestyle).arg(userbuytime.mid(5,2));
                queryupdateone.exec(updatecorsignmentinfoone);
                QSqlQuery queryupdatetwo;
                QString updatecorsignmentinfotwo=QString(" update corsignmentinfo  set ticketprice=ticketprice+%1 "
                "where  airportid= (select distinct(airportid)  from corsignmentinfo "
                "where airlineid=(select airlineid "
                "from supplyinfo where flightindex=(select flightindex from "
                "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                "where votestyle='%4' and flightid='%2'))) "
                "and airlineid= (select airlineid "
                "from supplyinfo where flightindex=(select flightindex from "
                "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                "where votestyle='%4' and flightid='%2')) "
                " and year(daytime)=%3 "
                "and month(daytime)=%5").arg(buyinfo[5]).arg(buyinfo[0]).arg(buyinfo[1].mid(0,4)).arg(votestyle).arg(buyinfo[1].mid(5,2));
                queryupdatetwo.exec(updatecorsignmentinfotwo);
                QSqlQuery queryvotenum;
                QString updatevotenum=QString("update seatinfo set votesnum=votesnum-1 where votestyle='%1' "
                "and seatid=(select seatinfo.seatid from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                "where votestyle='%1' and flightid='%2')")
                .arg(buyinfo[6]).arg(buyinfo[0]);
                if(!queryvotenum.exec(updatevotenum))
                    QMessageBox::information(this,"信息窗口","该机票已售完!");
                else
                    QMessageBox::information(this,"信息窗口","购买成功！");
            }
            else
            {
                 QSqlQuery queryselectone;
                 QString selectairportid=QString("select distinct(airportid)  from corsignmentinfo "
                 "where airlineid=(select airlineid "
                 "from supplyinfo where flightindex=(select flightindex from "
                 "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                 "where votestyle='%2' and flightid='%1'))").arg(buyinfo[0]).arg(votestyle);
                 QString airportid;
                 if(queryselectone.exec(selectairportid))
                 {
                     if(queryselectone.next())
                     {
                         airportid=queryselectone.value(0).toString();
                     }
                 }
                 QSqlQuery queryselecttwo;
                 QString selectairlineid=QString("select airlineid "
                 "from supplyinfo where flightindex=(select flightindex from "
                 "flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid "
                 "where votestyle='%2' and flightid='%1')").arg(buyinfo[0]).arg(votestyle);
                 QString airlineid;
                 if(queryselecttwo.exec(selectairlineid))
                 {
                     if(queryselecttwo.next())
                     {
                         airlineid=queryselecttwo.value(0).toString();
                     }
                 }
                 QSqlQuery queryinsert;
                 QString insertcorsinmentinfo=QString("insert corsignmentinfo(airportid, "
                 "airlineid,daytime,ticketnum,ticketprice) "
                 "values(%1,%2,'%3',1,%4)").arg(airportid).arg(airlineid).arg(userbuytime).arg(buyinfo[5]);
                 if(queryinsert.exec(insertcorsinmentinfo))
                 {
                     QMessageBox::information(this,"信息窗口","购买成功！");
                 }
            }
        }
    });
    //订单功能按钮实现
    connect(ui->ordertoolButton,&QToolButton::clicked,this,[=](){
        ui->showWidget->setCurrentIndex(7);
        //调用外部变量
        extern QString usernamelineEdit;
        extern QString useridlineEdit;
        extern QString userphonelineEdit;
        //编写SQL语句
        QString orderinfo=QString("select itid 订单编号,userid 身份证号码,'%1' 真实姓名,"
        "'%3' 联系电话,flightid 航班编号,"
        "begintime 出发时间,endtime 结束时间,beginaddr 出发城市,endaddr 到达城市 "
        ",votesprice 价格,votestyle 座位类型 from issueticketsinfo join (flightinfo join "
        "seatinfo on flightinfo.seatid=seatinfo.seatid )"
        "on issueticketsinfo.flightindex=flightinfo.flightindex  "
        "where userid='%2'").arg(usernamelineEdit).arg(useridlineEdit).arg(userphonelineEdit);
        //qDebug()<<useridlineEdit;
        QSqlQueryModel *orderquerymodel=new QSqlQueryModel(ui->ordertableView);
        //qDebug()<<usernamelineEdit;
        orderquerymodel->setQuery(orderinfo);
        ui->ordertableView->setModel(orderquerymodel);
        ui->ordertableView->verticalHeader()->hide();
        ui->ordertableView->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    });
}
void maininterface::closeEvent(QCloseEvent *event)
{
    int youchoose=QMessageBox::information(this,"信息窗口","你确定退出售票系统吗？",QMessageBox::Yes|QMessageBox::No);
    if(youchoose==QMessageBox::Yes)
    {
        event->accept();
    }
    else
    {
        event->ignore();
    }
}

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

注册界面：

#ifndef REGISERED_H
#define REGISERED_H

#include <QWidget>
#include "widget.h"

namespace Ui {
class regisered;
}

class regisered : public QWidget
{
    Q_OBJECT

public:
    explicit regisered(QWidget *parent = 0);
    ~regisered();

private:
    Ui::regisered *ui;
};

#endif // REGISERED_H

.cpp文件：

#include "regisered.h"
#include "ui_regisered.h"

regisered::regisered(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::regisered)
{
    ui->setupUi(this);
    this->setWindowTitle("注册信息窗口");
    //设置标题图片
    this->setWindowIcon(QIcon(QPixmap(":/icon/icon/show.png")));
    //注册按钮
    connect(ui->regiseredbtn,&QPushButton::clicked,this,[=](){
        QString id=ui->id->text();
        QString username=ui->username->text();
        QString userpassword1=ui->userpassword1->text();
        QString userpassword2=ui->userpassword2->text();
        QString userphone=ui->userphone->text();
        QSqlQuery query;
        QString insertuserinfo=QString("insert userinfo(id,username,userpassword,userphone)"
        "values('%1','%2','%3','%4')").arg(id).arg(username).arg(userpassword1).arg(userphone);
        QString selectuserinfo=QString("select*from userinfo where id=%1").arg(id);
        if(userpassword1!=userpassword2)
        {
            QMessageBox::information(this,"信息窗口","两次密码必须一致！");
        }
        else if(id=="")
        {
            QMessageBox::information(this,"信息窗口","请输入身份证号码！");
        }
        else if(ui->id->text().length()!=16)
        {
            QMessageBox::information(this,"信息窗口","输入的身份证号码必须为16位！");
        }
        else if(username=="")
        {
            QMessageBox::information(this,"信息窗口","请输入真实姓名！");
        }
        else if(userphone=="")
        {
            QMessageBox::information(this,"信息窗口","请输入你的手机号码！");
        }
        else if(query.exec(selectuserinfo))
        {
            if(query.next())
            {
            QMessageBox::information(this,"信息窗口","该身份证号码已注册！");
            }
            else
            {
                query.exec(insertuserinfo);
                QMessageBox::information(this,"信息窗口","注册成功！");
            }
        }
    });
    //取消按钮
    connect(ui->closebtn,&QPushButton::clicked,this,[=](){
        Widget *showmain=new Widget();
        this->hide();
        showmain->show();
    });
}

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

用户信息界面：

#ifndef USERINFOWIDGET_H
#define USERINFOWIDGET_H

#include <QWidget>
#include "maininterface.h"
#include <QPushButton>
#include "widget.h"
#include "userpasswod.h"

namespace Ui {
class userinfoWidget;
}

class userinfoWidget : public QWidget
{
    Q_OBJECT

public:
    explicit userinfoWidget(QWidget *parent = 0);
    ~userinfoWidget();
signals:
    mainfacecolose();
private:
    Ui::userinfoWidget *ui;
};

#endif // USERINFOWIDGET_H

.cpp文件：

#include "userinfowidget.h"
#include "ui_userinfowidget.h"

userinfoWidget::userinfoWidget(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::userinfoWidget)
{
    ui->setupUi(this);
    this->setWindowIcon(QIcon(QPixmap(":/icon/icon/show.png")));
    this->setWindowTitle("用户信息窗口");
    connect(ui->userinfotoolbtn,&QToolButton::clicked,this,[=](){
        extern QString usernamelineEdit;
        extern QString useridlineEdit;
        extern QString userstylelineEdit;
        extern QString userphonelineEdit;
        ui->usernamelineEdit->setText(usernamelineEdit);
        ui->useridlineEdit->setText(useridlineEdit);
        ui->userstylelineEdit->setText(userstylelineEdit);
        ui->userphonelineEdit->setText(userphonelineEdit);
    });
    //注销用户按钮
    connect(ui->loginoutbtn,&QPushButton::clicked,this,[=](){
        //声明登录窗口的指针
        Widget *login=new Widget();
        //将信号发送到主界面窗口
        emit mainfacecolose();
        this->hide();
        login->show();
    });
    //取消按钮
    connect(ui->returnbtn,&QToolButton::clicked,this,[=](){
        this->close();
    });
}

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

修改密码界面：

#ifndef USERPASSWOD_H
#define USERPASSWOD_H

#include <QWidget>
#include <QMessageBox>
#include "widget.h"
#include <QTimer>

namespace Ui {
class userpasswod;
}

class userpasswod : public QWidget
{
    Q_OBJECT

public:
    explicit userpasswod(QWidget *parent = 0);
    ~userpasswod();
signals:
    mainfaceclose();
private:
    Ui::userpasswod *ui;
};

#endif // USERPASSWOD_H

.cpp文件：

#include "userpasswod.h"
#include "ui_userpasswod.h"
#include "maininterface.h"

userpasswod::userpasswod(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::userpasswod)
{
    ui->setupUi(this);
    this->setWindowIcon(QIcon(QPixmap(":/icon/icon/show.png")));
    this->setWindowTitle("修改密码窗口");
    connect(ui->returntoolButton,&QToolButton::clicked,this,[=](){
        this->close();
    });
    extern QString useridlineEdit;
    //修改密码按钮
    connect(ui->passwordtoolButton,&QToolButton::clicked,this,[=](){
        QString originalpassword=ui->originalpasswordlineEdit->text();
        QString newpassword=ui->newpasswordlineEdit->text();
        QString definepassword=ui->definepasswordlineEdit->text();
        if(originalpassword=="")
        {
            QMessageBox::information(this,"信息窗口","请输入原来密码！");
        }
        else{
            QSqlQuery query;
            QString selectpassword=QString("select *from userinfo "
            "where id=%1 and userpassword=%2").arg(useridlineEdit).arg(originalpassword);
            if(query.exec(selectpassword))
            {
              if(!query.next())
              {
                  QMessageBox::information(this,"信息窗口","输入原密码错误！");
              }
              else if(newpassword!=definepassword){
                  QMessageBox::information(this,"信息窗口","输入两次密码不一致！");
              }
              else if(originalpassword==newpassword)
              {
                  QMessageBox::information(this,"信息窗口","修改密码与原密码一致！");
              }
              else{
                  QSqlQuery query;
                  QString updatepassword=QString("update userinfo set "
                  " userpassword=%1 where id=%2").arg(newpassword).arg(useridlineEdit);
                  if(query.exec(updatepassword))
                  {
                      QMessageBox::information(this,"信息窗口","修改密码成功！");
                      this->hide();
                      //发送信号
                      emit mainfaceclose();
                      //qDebug()<<"主界面关闭信号已发送！";
                      QTimer::singleShot(100,this,[=](){
                          Widget *loginshow=new Widget();
                          loginshow->show();
                          //qDebug()<<"登录界面已显示！";
                      });
                  }
              }
            }
        }
    });
}

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

其实可以简化代码的，不过当时由于时间关系所以我没改，下面是数据库的代码 ：

--创建民航售票系统数据库
create database CATSys
on
(
  Name=CATSys,
  Filename='D:\SQL Server Date\CATSys.mdf',
  Size=10,
  filegrowth=1
)
log on
(
   Name=CATSys_log,
   FileName='D:\SQL Server Date\CATSys_log.ldf',
   Size=3,
   MaxSize=20,
   FileGrowth=10%
)
use CATSys

--用户信息表
create table userinfo
(
  id char(20) not null check(len(id)=16),--身份证号码(主键)
  username char(20),--真实姓名
  usertype char(8) default('普通用户') 
  check(usertype='普通用户'or usertype='经常旅客'),--用户类型
  userphone varchar(20),--用户联系方式
  userpassword varchar(16)--用户密码
  primary key(id)
)

--机场信息
create table airportinfo
(
  airportid int not null,--机场编号(主键)
  addr char(50)--机场地址
  primary key(airportid)
)
--航班座位信息表
create table seatinfo
(
   seatid int not null,--航班座位表编号
   votesnum int check(votesnum>=0),--座位数量
   votesprice int check(votesprice>=0),--座位价格
   votestyle char(10) default('甲等座') check(votestyle='甲等座'or votestyle='乙等座'),--座位类型
   primary key(seatid)
)
--航班信息表
create table flightinfo
(
 flightindex int not null identity(1,1),--主键
 flightid char(20) not null,--航班编号
 seatid int,--座位编号(外键)
 begintime datetime,--起飞时间
 endtime datetime,--降落时间
 beginaddr varchar(30),--起飞地址
 endaddr  varchar(30),--降落地址
 primary key(flightindex),
 foreign key(seatid) references seatinfo(seatid),
)


--航空公司信息表
create table airlineinfo
(
  airlineid int,--公司编号(主键)
  airlinename char(30),--名称
  addr char(30)--公司地址
   primary key(airlineid)
)

--订票信息表
create table issueticketsinfo
(
 itid int not null identity(201901010,1),--订单编号(主键)
 userid char(20) not null,--用户身份证
 flightindex int not null--航班编号
  primary key(itid)
  foreign key (flightindex) references flightinfo(flightindex)
)

--航空公司提供航班信息表
create table supplyinfo
(
  airlineid int not null,--公司编号(主键、航空公司信息表的外键)
  flightindex int not null,--航班编号(主键、航班信息表的外键)
  primary key(airlineid,flightindex),
  foreign key(airlineid) references airlineinfo(airlineid),
  foreign key(flightindex) references flightinfo(flightindex)
)

--代售信息表
create table corsignmentinfo
(
  numid int not null identity(1,1),
  airportid int not null,--机场编号(主键、机场信息表的外键)
  airlineid int not null,--公司编号(主键、航空公司信息表的外键)
  daytime date,--时间
  ticketnum int check(ticketnum>=0),--售出的票数
  ticketprice int check(ticketprice>=0),--售出的总金额
  primary key(numid),
  foreign key(airportid) references airportinfo(airportid),
  foreign key(airlineid) references airlineinfo(airlineid)
)
--插入数据
--航空公司信息表数据
insert  airlineinfo(airlineid,airlinename,addr)
values (1,'春秋航空有限公司','广西梧州航空市')
insert  airlineinfo(airlineid,airlinename,addr)
values (2,'北海航空有限公司','广西北海市')

--用户信息表数据
insert userinfo(id,username,userpassword,userphone)
values('1111111111111111','张三','123456','17877380638')
insert userinfo(id,username,userpassword,userphone)
values('4504812341102010','李四','123456','24151616161')
insert userinfo(id,username,userpassword,userphone)
values('4504812341102011','黄五','123456','15325125325')
insert userinfo(id,username,userpassword,userphone)
values('4504812341102012','王一','123456','24251561621')

--机场信息数据
insert airportinfo(airportid,addr)
values(001,'北海成福机场')
insert airportinfo(airportid,addr)
values(002,'北京福成机场')



--航班信息表数据
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8990','2020-01-06 16:00:00','2020-01-07 16:00:00','扬州泰州','天津',1)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8990','2020-01-06 16:00:00','2020-01-07 16:00:00','扬州泰州','天津',2)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8991','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',3)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8991','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',4)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8992','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',5)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8992','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',6)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8993','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',7)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8993','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',8)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8994','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',9)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8994','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',10)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8995','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',11)
insert flightinfo(flightid,begintime,endtime,beginaddr,endaddr,seatid)
values('9C8995','2020-01-06 5:00:00','2020-01-07 5:00:00','扬州泰州','天津',12)
select *from flightinfo
--代售信息表数据
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(001,1,'2020-1-31',0,0)
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(001,1,'2020-2-29',0,0)
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(001,1,'2020-3-31',0,0)
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(002,2,'2020-1-31',0,0)
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(002,2,'2020-2-29',0,0)
insert corsignmentinfo(airlineid,airportid,daytime,ticketnum,ticketprice)
values(002,2,'2020-3-31',0,0)



--航班座位信息表数据
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(1,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(2,200,200,'乙等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(3,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(4,200,200,'乙等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(5,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(6,200,200,'乙等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(7,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(8,200,200,'乙等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(9,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(10,200,200,'乙等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(11,189,200,'甲等座')
insert seatinfo(seatid,votesprice,votesnum,votestyle)
values(12,200,200,'乙等座')

--订票信息表数据


--航空公司提供航班信息表数据
insert supplyinfo(airlineid,flightindex)
values(1,1)
insert supplyinfo(airlineid,flightindex)
values(1,2)
insert supplyinfo(airlineid,flightindex)
values(1,3)
insert supplyinfo(airlineid,flightindex)
values(1,4)
insert supplyinfo(airlineid,flightindex)
values(1,5)
insert supplyinfo(airlineid,flightindex)
values(1,6)

insert supplyinfo(airlineid,flightindex)
values(2,7)
insert supplyinfo(airlineid,flightindex)
values(2,8)
insert supplyinfo(airlineid,flightindex)
values(2,9)
insert supplyinfo(airlineid,flightindex)
values(2,10)
insert supplyinfo(airlineid,flightindex)
values(2,11)
insert supplyinfo(airlineid,flightindex)
values(2,12)



--查某代售地某月售出的票数及金额（存储过程）
create proc selectconginmentinfo(@year int,@month int,@airportaddr varchar(50))
as
select cast(year(daytime)as varchar(50))+'-'+cast(month(daytime)as varchar(50)) 时间,addr 地址,ticketnum 售出票数,ticketprice 售出总金额 from corsignmentinfo
join airportinfo on airportinfo.airportid=corsignmentinfo.airportid
where year(daytime)=@year and month(daytime)=@month and addr=@airportaddr

exec selectconginmentinfo 2020,1,'北海成福机场'

--查航空公司拥有多少航班(已修改)3:33（存储过程）
create proc selectairlineinfo(@airlinename varchar(50))
as
select airlinename 公司名称,count(airlinename)/2 航班数量 from supplyinfo join airlineinfo 
on supplyinfo.airlineid=airlineinfo.airlineid
group by airlinename
having airlinename=@airlinename

exec selectairlineinfo '北海航空有限公司'


--查某天某航空还剩多少票或座位（已修改）3：37（存储过程）
create proc selectvotenum(@flightid varchar(10),@year int,@month int,@day int)
as
declare @Avotenum int
set @Avotenum=(select sum(votesnum) from seatinfo where seatid in(select seatid from flightinfo where flightid=@flightid))
select distinct(flightid) 航班编号,@Avotenum 座位数量或票数 from flightinfo
where flightid=@flightid and year(begintime)=@year and month(begintime)=@month and day(begintime)=@day

exec selectvotenum '9C8990',2020,1,6
--查某天某航空还剩甲等座位以及乙等座位票价（已修改）3：45（存储过程）
create proc selectvoteprice(@flightid varchar(10),@year int,@month int,@day int)
as
declare @Avoteprice int
declare @Bvoteprice int
set @Avoteprice=(select votesprice from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid where votestyle='甲等座' 
and flightid=@flightid)
set @Bvoteprice=(select votesprice from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid where votestyle='乙等座' 
and flightid=@flightid)
select distinct(flightid) 航班编号,@Avoteprice 甲等座票价,@Bvoteprice 乙等座票价 from flightinfo
where flightid=@flightid and year(begintime)=@year and month(begintime)=@month and day(begintime)=@day

exec selectvoteprice '9C8990',2020,1,6

--查某航天公司拥有多少售票点、某月售出总金额以及某航线售出票数0:42（存储过程）
create proc selectairlineconsignmentnum(@airlinename varchar(50),@year int,@month int,@enginaddr varchar(20),@endaddr varchar(20))
as
 declare @num int
set @num=(select count(distinct(airportid)) from corsignmentinfo where airlineid=(select airlineid from airlineinfo where airlinename=@airlinename))
 select distinct(airlinename) 公司名称,@num 售票点数量,cast(year(daytime) as varchar(50)) +'-'+cast(month(daytime) as varchar(50)) 时间,ticketprice 总金额,beginaddr+' - '+endaddr 航线,ticketnum 票数 
 from corsignmentinfo join airlineinfo on corsignmentinfo.numid=airlineinfo.airlineid
,supplyinfo join flightinfo on supplyinfo.flightindex=flightinfo.flightindex
where airlinename=@airlinename and year(daytime)=@year and month(daytime)=@month and beginaddr=@enginaddr
and endaddr=@endaddr


exec selectairlineconsignmentnum '春秋航空有限公司',2020,1,'扬州泰州','天津'


---------------------------------------分割线--------------------------------------------------------------
select*from userinfo
--购票信息4:08

select distinct(flightid) 航班编号,begintime 起飞时间,endtime 结束时间,beginaddr 出发城市,endaddr 到达城市,votesprice 价格,votestyle 座位类型 from flightinfo,seatinfo
where votestyle='甲等座' and beginaddr='扬州泰州' and endaddr='天津'
and year(begintime)='2020' and month(begintime)='1' and day(begintime)='6'

select*from issueticketsinfo

--购买执行的语句21:09
insert issueticketsinfo(userid,flightid)
values(,)
update corsignmentinfo set ticketnum=ticketnum+1 
where  airportid= (select distinct(airportid)  from corsignmentinfo 
 where airlineid=(select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990')))
 and airlineid= (select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990'))
 and year(daytime)=2020
 and month(daytime)=1

 update corsignmentinfo  set ticketprice=ticketprice+189
where  airportid= (select distinct(airportid)  from corsignmentinfo 
 where airlineid=(select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990')))
 and airlineid= (select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990'))
 and year(daytime)=2020
 and month(daytime)=1

update seatinfo set votesnum=votesnum-1 where votestyle='甲等座'
and seatid=(select seatinfo.seatid from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990')

--22:05
 select distinct(cast(year(daytime) as varchar(50))+'-'+ cast(month(daytime) as varchar(50))),*
 from corsignmentinfo
 where  airportid= (select distinct(airportid)  from corsignmentinfo 
 where airlineid=(select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990' )))
 and airlineid= (select airlineid 
 from supplyinfo where flightindex=(select flightindex from flightinfo join seatinfo on flightinfo.seatid=seatinfo.seatid
where votestyle='甲等座' and flightid='9C8990'))
 and year(daytime)=2020
 and month(daytime)=1


 --订单功能执行SQL语句7：56  


select itid 订单编号,userid 身份证号码,'李四' 真实姓名,
        '24151616161' 联系电话,flightid 航班编号,
        begintime 出发时间,endtime 结束时间,beginaddr 出发城市,endaddr 到达城市 
        ,votesprice 价格,votestyle 座位类型 from issueticketsinfo join (flightinfo join 
        seatinfo on flightinfo.seatid=seatinfo.seatid )
        on issueticketsinfo.flightindex=flightinfo.flightindex 
        where userid=4504812341102010

因为数据库是我自己设计的，所以有很多不合理的地方，而且我还多次删库又重新创建，非常麻烦

少年与荆棘
关注
专栏目录
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值