• MySQL学生成绩管理系统based on C++ and Clion

    mysql_free_result()函数的作用是释放结果集的内存，是同步的，也就是要中断一下

    该实验使用了MySQL链接数据库的基本使用方法，具体使用了

    MYSQL_RES 数据库的mysql_store_result()函数的返回值是一个结果集，该函数的作用是检索比并存储整个结果集
    MYSQL_ROW 数据库的mysql_fetch_row()函数的返回值是一个集行，搭配上一个mysql_res使用的，一个一个读他，函数的作用是获取下一个结果集行

    mysql_query()函数是执行语句的意思，也就是发给数据库让他执行这条语句，函数传递语句的使用如果是std::string 类型的话，需要调用.c_str()函数进行转化成字符才能进行传递

    1. std::ifstream  infile("../src/student2.txt",std::ios::in);
    2. infile>>temp[j];
    1. std::ofstream outfile("../src/student2.txt",std::ios::out);
    2. outfile<<"学号\t"<<"名字\t"<<"得分\n"<

    该方法分别对应了流对于文件的读入和写入操作的执行，使用完毕之后要执行close()操作

    本实验我认为最重要的是对于mysql的链接建立问题

    1.     MYSQL mysql;
    2.     const char db_Username[]="root";
    3.     const char db_Password[]="123456";
    4.     const char db_Host[]="localhost";
    5.     const char db_Name[]="sp";
    6.     unsigned long db_Port=3306;
    7.     mysql_init(&mysql);
    8.     mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"gbk");
    9.     if(mysql_real_connect(&mysql,db_Host,db_Username,db_Password,db_Name,db_Port,NULL,0)==NULL){
    10.         std::cout<<"connect error!!!"<
    11.         return false;
    12.     }
    13.     std::cout<<"connect success!!!"<
    1. build
    2. docs
    3. include
      1. Main.h
      2. Student.h
    4. src
      1. main.cpp
      2. Methods.cpp
      3. student.cpp
      4. student2.txt
    5. CMakeLists.txt

    Main.h

    1.  
    2. #ifndef NEW_PROJECTS_MAIN_H
    3. #define NEW_PROJECTS_MAIN_H
    4.  
    5. #include
    6. #include
    7. #include 
    8.  
    9.  
    10.  
    11.  
    12.  
    13. void show_mean();//显示菜单
    14.  
    15. bool connect_mysql(MYSQL& mysql);//链接数据库
    16.  
    17. void query(MYSQL& mysql);//查询学生数据
    18.  
    19. void modify(MYSQL& mysql);//修改学生数据
    20.  
    21. void insert(MYSQL& mysql);//添加学生数据
    22.  
    23. void Delete(MYSQL& mysql);//删除学生数据
    24.  
    25. void filein(MYSQL& mysql);//导入文件数据
    26.  
    27. int getline();//获取文件行数
    28.  
    29. void fileout(MYSQL& mysql);//将数据库中的文件导出到另外一个文件中
    30.  
    31. void createTable(MYSQL& mysql);//创建表格
    32.  
    33. #endif //NEW_PROJECTS_MAIN_H

    Student.h

    1. #ifndef NEW_PROJECTS_STUDENT_H
    2. #define NEW_PROJECTS_STUDENT_H
    3. #include"main.h"
    4. class Student{
    5. private:
    6.     int Id;
    7.     std::string name;
    8.     int score;
    9. public:
    10.     int getId();
    11.     std::string getName();
    12.     int getScore();
    13.     void setId(int& Id);
    14.     void setName(std::string& name);
    15.     void setScore(int& score);
    16. };
    17. #endif //NEW_PROJECTS_STUDENT_H

    main.cpp

    1. #include "Main.h"
    2.  
    3. int main() {
    4.     MYSQL mysql;
    5.     mysql_init(&mysql);
    6.     mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"gbk");
    7.     system("chcp 65001");
    8.     if(connect_mysql(mysql)==false){
    9.         return 0;
    10.     }
    11.     else{
    12.         int choices=1;
    13.         while(choices!=0){
    14.             show_mean();
    15.             std::cin>>choices;
    16.             switch (choices) {
    17.                 case 1:
    18.                     filein(mysql);
    19.                     break;
    20.                 case 2:
    21.                     query(mysql);
    22.                     break;
    23.                 case 3:
    24.                     modify(mysql);
    25.                     break;
    26.                 case 4:
    27.                     insert(mysql);
    28.                     break;
    29.                 case 5:
    30.                     Delete(mysql);
    31.                     break;
    32.                 case 0:
    33.                     fileout(mysql);
    34.                     break;
    35.                 default:
    36.                     std::cout<<"Input error,please again!!!"<
    37.                     break;
    38.             }
    39.         }
    40.         std::cout<<"success"<
    41.         system("pause");
    42.         system("cls");
    43.     }
    44.  
    45. }

    Methods.cpp

    1. #include"Main.h"
    2. #include"Student.h"
    3. void show_mean() // 展示功能菜单并让用户选择功能
    4. {
    5.  
    6.  
    7.  
    8.     std::cout << std::endl << "=========================================欢迎使用学生管理系统========================================" << std::endl << std::endl;;
    9.     std::cout << "请选择您要进行的操作" << std::endl;
    10.  
    11.     std::cout << "\t 1. 读取学生数据并显示" << std::endl;
    12.     std::cout << "\t 2. 查询学生数据" << std::endl;
    13.     std::cout << "\t 3. 修改学生数据" << std::endl;
    14.     std::cout << "\t 4. 添加学生数据" << std::endl;
    15.     std::cout << "\t 5. 删除学生数据" << std::endl;
    16.     std::cout << "\t 0. 退出管理系统" << std::endl;
    17.  
    18.  
    19.  
    20.  
    21. }
    22. void create_table(MYSQL& mysql){
    23.     char Quest[]= "CREATE TABLE IF NOT EXISTS `Student1`(\
    24.     `Id` int AUTO_INCREMENT,\
    25.     `name` varchar(20),\
    26.     `score` double,\
    27.     PRIMARY KEY (`Id`)\
    28.     )ENGINE=InnoDB DEFAULT CHARSET=utf8;";
    29.     int re=mysql_query(&mysql,Quest);
    30.     if(re==0)
    31.         std::cout<<"table already"<
    32. }
    33.  
    34. bool connect_mysql(MYSQL& mysql){
    35.     const char db_Username[]="root";
    36.     const char db_Password[]="123456";
    37.     const char db_Host[]="localhost";
    38.     const char db_Name[]="sp";
    39.     unsigned long db_Port=3306;
    40.     mysql_init(&mysql);
    41.     mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"gbk");
    42.     if(mysql_real_connect(&mysql,db_Host,db_Username,db_Password,db_Name,db_Port,NULL,0)==NULL){
    43.         std::cout<<"connect error!!!"<
    44.         return false;
    45.     }
    46.     std::cout<<"connect success!!!"<
    47.     create_table(mysql);
    48.     return true;
    49. }
    50. int getline(){
    51.     int len=0;
    52.     std::ifstream infile("../src/student2.txt",std::ios::in);
    53.     char c;
    54. //    if (!infile.is_open()) {
    55. //        std::cerr << "Error: Unable to open file. ";
    56. //        std::cerr << "Error code: " << errno << std::endl;
    57. //        return -1; // 或者其他适当的错误码
    58. //    }
    59.     while(infile.get(c)){
    60. //        if(c=='\n')len++;
    61.         len++;
    62.         std::cout<
    63.     }
    64.     infile.close();
    65.     std::cout<<"data_len is "<-1<
    66.     return len;
    67. }
    68.  
    69. void filein(MYSQL& mysql){
    70.     int n=getline();
    71.     std::cout<<"already student data:"<
    72.     mysql_options(&mysql,MYSQL_SET_CHARSET_NAME,"gbk");
    73.     std::ifstream  infile("../src/student2.txt",std::ios::in);
    74.     const int N=300;
    75.     const int N2=100;
    76.     std::string temp[N];
    77.     Student* student=new Student[N2];
    78.     int x=0;
    79.     int tempId;
    80.     int tempScore;
    81.     if(!infile.is_open()){
    82.         std::cout<<"file open errr!!!"<
    83.         exit(0);
    84.     }
    85.     else{
    86.         char line[256]={0};
    87.         int i=0;
    88.         while(!infile.eof()&&i
    89.             for(int j=i;j3;j++){
    90.                 infile>>temp[j];
    91.                 std::cout<<"\t\t"<"\t";
    92.             }
    93.             std::cout<
    94.             tempId=atoi(temp[i].c_str());
    95.             tempScore=atoi(temp[i+2].c_str());
    96.             student[x].setId(tempId);
    97.             student[x].setName(temp[i+1]);
    98.             student[x].setScore(tempScore);
    99.             x++;
    100.             i=i+3;
    101.         }
    102.         infile.close();
    103.     }
    104.     std::cout<<"insert into student1 table"<
    105.     for(int i=1;i
    106.         std::string sql = "insert into Student1 values (" + std::to_string(student[i].getId()) + "," + "'" + student[i].getName() + "'" + "," + std::to_string(student[i].getScore()) + ")";
    107.         std::cout<
    108.         mysql_query(&mysql,sql.c_str());
    109.     }
    110.     delete[] student;
    111. }
    112.  
    113. void fileout(MYSQL& mysql){
    114.     MYSQL_RES* res;
    115.     MYSQL_ROW row;
    116.     std::ofstream outfile("../src/student2.txt",std::ios::out);
    117.     mysql_query(&mysql,"select * from student1 order by Id");
    118.     res= mysql_store_result(&mysql);
    119.     std::cout<<"data:"<
    120.     std::cout<<"学号\t"<<"名字\t"<<"得分\n"<
    121.     outfile<<"学号\t"<<"名字\t"<<"得分\n"<
    122.     while(row= mysql_fetch_row(res)){
    123.         std::cout<<"\t"<0]<<"\t\t"<1]<<"\t\t"<2]<
    124.         outfile << row[0] << "\t" << row[1] << "\t" << row[2] << std::endl;
    125.     }
    126.     std::cout << "他们将被保存到../src/student2.txt中" << std::endl;
    127.     outfile.close();
    128.     mysql_free_result(res);
    129. }
    130.  
    131. void query(MYSQL& mysql) // 查询学生数据
    132. {
    133.     MYSQL_RES* res; // 创建一个结果集
    134.     MYSQL_ROW row; // 二维数组存放记录
    135.  
    136.     std::cout << "您选择了查询学生信息" << std::endl;
    137.     std::cout << "请输入您要查询的选项" << std::endl;
    138.     std::cout << "\t 1. 查询全部学生" << std::endl;
    139.     std::cout << "\t 2. 按学号查询" << std::endl;
    140.     std::cout << "\t 3. 按照名字进行查询" << std::endl;
    141.     std::cout << "\t 4. 按照分数进行查询" << std::endl;
    142.     std::cout << "\t 5.自定义SQL命令进行查询" << std::endl;
    143.     int choice;
    144.     std::string sql;
    145.     std::cin >> choice;
    146.     switch (choice) {
    147.         case 1:
    148.         {
    149.             std::cout << "全体学生信息如下：" << std::endl;
    150.             sql = "select * from student1 order by Id";
    151.             std::cout << "查询的SQL语句如下：" << std::endl << sql << std::endl; // 检查语句
    152.             mysql_query(&mysql, sql.c_str());
    153.             break;
    154.         }
    155.         case 2:
    156.         {
    157.             std::cout << "请输入要查询的学生学号" << std::endl;
    158.             int Id;
    159.             std::cin >> Id;
    160.             sql = "select * from student1 where Id = " + std::to_string(Id) + " order by Id";
    161.             std::cout << "查询的SQL语句如下：" << std::endl << sql << std::endl; // 检查语句
    162.             mysql_query(&mysql, sql.c_str());
    163.             break;
    164.  
    165.         }
    166.         case 3:
    167.         {
    168.             std::cout << "请输入要查询的学生姓名" << std::endl;
    169.             std::string name;
    170.             std::cin >> name;
    171.             sql = "select * from student1 where name = '" + name + "' order by Id";
    172.             std::cout << "查询的SQL语句如下：" << std::endl << sql << std::endl; // 检查语句
    173.             mysql_query(&mysql, sql.c_str());
    174.             break;
    175.  
    176.         }
    177.         case 4:
    178.         {
    179.             std::cout << "请输入要查询的学生分数" << std::endl;
    180.             int score;
    181.             std::cin >> score;
    182.             sql = "select * from student1 where score = " + std::to_string(score) + " order by Id";
    183.             std::cout << "查询的SQL语句如下：" << std::endl << sql << std::endl; // 检查语句
    184.             mysql_query(&mysql, sql.c_str());
    185.             break;
    186.  
    187.         }
    188.         case 5:
    189.         {
    190.             std::cout << "请输入SQL命令语句" << std::endl;
    191.             std::cin.ignore(1024, '\n');
    192.             getline(std::cin, sql);
    193.             std::cout << "查询的SQL语句如下：" << std::endl<< sql << std::endl; // 检查语句
    194.             mysql_query(&mysql, sql.c_str());
    195.             break;
    196.         }
    197.         default:
    198.             std::cout << "输入有误请重试" << std::endl;
    199.     }
    200.  
    201.  
    202.     std::cout << "\t\t查询结果如下" << std::endl;
    203.     res = mysql_store_result(&mysql); // 将查询得到的数据存入结果集
    204.     if (res == NULL)
    205.         std::cout << "所有学生均与你查询的数据不匹配" << std::endl;
    206.     else {
    207.         std::cout << "\t学号\t" << "\t姓名\t" << "\t得分\t" << std::endl;
    208.         while (row = mysql_fetch_row(res))
    209.         {
    210.             std::cout << "\t" << row[0] << "\t\t" << row[1] << "\t\t" << row[2] << std::endl;
    211.         }
    212.     }
    213.     mysql_free_result(res);
    214. }
    215.  
    216. void modify(MYSQL& mysql) // 修改学生数据
    217. {
    218.     int choice;
    219.     int Id;
    220.     std::string name;
    221.     int score;
    222.     std::string sql1;
    223.     std::string sql2;
    224.     std::string sql;
    225.     MYSQL_RES* res;
    226.     MYSQL_ROW row;
    227.     std::cout << "您选择了修改学生信息" << std::endl;
    228.     std::cout << "请输入您要修改的学生的信息及数据" << std::endl;
    229.     std::cout << "\t 1. 按学号修改" << std::endl;
    230.     std::cout << "\t 2. 按名字修改" << std::endl;
    231.     std::cin >> choice;
    232.     switch (choice)
    233.     {
    234.         case 1:
    235.         {
    236.             std::cout << "请输入要修改的学生学号" << std::endl;
    237.             std::cin >> Id;
    238.             sql = "select * from student1 where Id = " + std::to_string(Id) + " order by Id";
    239.             // sql = "select * from student1 where Id =" + to_string(Id);
    240.             mysql_query(&mysql, sql.c_str());
    241.             res = mysql_store_result(&mysql);
    242.             row = mysql_fetch_row(res);
    243.             if (row == NULL)
    244.             {
    245.  
    246.                 std::cout << "不存在该学号的学生" << std::endl;
    247.                 break;
    248.             }
    249.             else
    250.  
    251.                 std::cout << "请输入修改之后的学生姓名" << std::endl;
    252.             std::cin >> name;
    253.             std::cout << "请输入修改之后的学生分数" << std::endl;
    254.             std::cin >> score;
    255.             sql1 = "update Student1 set name= '" + name + "' where Id=" + std::to_string(Id);
    256.             sql2 = "update Student1 set score= " + std::to_string(score) + " where Id=" + std::to_string(Id);
    257.             std::cout << "修改的的SQL语句如下：" << std::endl << sql1 << std::endl << sql2 << std::endl; // 检查语句
    258.             mysql_query(&mysql, sql1.c_str());
    259.             mysql_query(&mysql, sql2.c_str());
    260.             break;
    261.         }
    262.         case 2:
    263.         {
    264.             std::cout << "请输入要修改的学生姓名" << std::endl;
    265.             std::cin >> name;
    266.             sql1 = "select * from Student1 where name ='" + name + "'";
    267.             mysql_query(&mysql, sql1.c_str());
    268.             res = mysql_store_result(&mysql);
    269.             row = mysql_fetch_row(res);
    270.             if (row == NULL)
    271.             {
    272.  
    273.                 std::cout << "不存在该姓名的学生" << std::endl;
    274.                 break;
    275.             }
    276.             else
    277.                 std::cout << "请输入修改之后的学生学号" << std::endl;
    278.             std::cin >> Id;
    279.             std::cout << "请输入修改之后的学生分数" << std::endl;
    280.             std::cin >> score;
    281.             sql1 = "update Student1 set Id= " + std::to_string(Id) + " where name='" + name + "'";
    282.             sql2 = "update Student1 set score= " + std::to_string(score) + " where name='" + name + "'";
    283.             std::cout << "修改的的SQL语句如下：" << std::endl << sql1 << std::endl << sql2 << std::endl; // 检查语句
    284.             mysql_query(&mysql, sql1.c_str());
    285.             mysql_query(&mysql, sql2.c_str());
    286.             break;
    287.         }
    288.         default:
    289.             std::cout << "输入有误请重试" << std::endl;
    290.     }
    291.     //mysql_free_result(res);
    292. }
    293.  
    294. void insert(MYSQL& mysql)//添加学生信息
    295. {
    296.     int Id;
    297.     std::string name;
    298.     int score;
    299.     MYSQL_RES* res; // 创建一个结果集
    300.     MYSQL_ROW row; // 二维数组存放记录
    301.     std::string sql1;
    302.     std::string sql2;
    303.     std::cout << "您选择了添加学生信息" << std::endl;
    304.     std::cout << "请输入学生学号" << std::endl;
    305.     std::cin >> Id;
    306.  
    307.     sql1 = "select * from student1 where Id = " + std::to_string(Id) + " order by Id";
    308.     mysql_query(&mysql, sql1.c_str());
    309.     res = mysql_store_result(&mysql);
    310.     row = mysql_fetch_row(res);
    311.     if (row != NULL)
    312.         std::cout << "已存在该学号的学生，系统不允许学号相同，请重新添加或选择修改该学生信息" << std::endl;
    313.     else {
    314.         std::cout << "请输入学生名字" << std::endl;
    315.         std::cin >> name;
    316.         std::cout << "请输入学生得分" << std::endl;
    317.         std::cin >> score;
    318.         sql2 = "insert into Student1 values (" + std::to_string(Id) + "," + "'" + name + "'" + "," + std::to_string(score) + ")";
    319.         std::cout << "插入的SQL语句如下：" << std::endl << sql2 << std::endl; // 检查语句
    320.         mysql_query(&mysql, sql2.c_str());
    321.         // 使用sql命令对数据库进行数据添加
    322.     }
    323.     mysql_free_result(res);
    324. }
    325. void Delete(MYSQL& mysql) // 删除学生数据
    326. {
    327.     int choice = 1;
    328.     int Id;
    329.     std::string name, sql1, sql2;
    330.     int score;
    331.     MYSQL_RES* res;
    332.     MYSQL_ROW row;
    333.     std::cout << "您选择了删除学生信息" << std::endl;
    334.     //std::cout << "请输入您要删除的学生信息" << std::endl;
    335.     std::cout << "\t 1. 按学号删除" << std::endl;
    336.     std::cout << "\t 2. 按名字删除" << std::endl;
    337.     std::cout << "\t 3. 按照分数删除" << std::endl;
    338.  
    339.  
    340.     std::cin >> choice;
    341.     switch (choice) {
    342.         case 1:
    343.         {
    344.             std::cout << "请输入该生学号" << std::endl;
    345.             std::cin >> Id;
    346.             sql1 = "select * from Student1 where Id =" + std::to_string(Id);
    347.             mysql_query(&mysql, sql1.c_str());
    348.             res = mysql_store_result(&mysql);
    349.             row = mysql_fetch_row(res);
    350.             if (row == NULL)
    351.             {
    352.  
    353.                 std::cout << "不存在该学号的学生" << std::endl;
    354.                 break;
    355.             }
    356.             else
    357.                 sql2 = "delete  from Student1 where Id= " + std::to_string(Id);
    358.             std::cout << "删除的SQL语句如下：" << std::endl << sql2 << std::endl; // 检查语句
    359.             mysql_query(&mysql, sql2.c_str());
    360.             break;
    361.         }
    362.         case 2:
    363.         {
    364.             std::cout << "请输入该生姓名" << std::endl;
    365.  
    366.             std::cin >> name;
    367.             sql1 = "select * from Student1 where name ='" + name + "'";
    368.             mysql_query(&mysql, sql1.c_str());
    369.             res = mysql_store_result(&mysql);
    370.             row = mysql_fetch_row(res);
    371.             if (row == NULL)
    372.             {
    373.  
    374.                 std::cout << "不存在该姓名的学生" << std::endl;
    375.                 break;
    376.             }
    377.             else
    378.                 sql2 = "delete  from Student1 where name= '" + name + "'";
    379.             std::cout << "删除的SQL语句如下：" << std::endl << sql2 << std::endl; // 检查语句
    380.             mysql_query(&mysql, sql2.c_str());
    381.             break;
    382.         }
    383.         case 3:
    384.         {
    385.             std::cout << "您确定要使用分数来删除信息吗，该操作可能会影响多名学生" << std::endl;
    386.             std::cout << "1.确定\t 2.取消" << std::endl;
    387.             int c = 0;
    388.             std::cin >> c;
    389.             if (c != 1)
    390.             {
    391.                 std::cout << "您选择了取消操作" << std::endl; break;
    392.             }
    393.             else
    394.                 std::cout << "您依旧选择了使用分数删除信息！" << std::endl;
    395.             std::cout << "请选择要删除的分数" << std::endl;
    396.  
    397.             std::cin >> score;
    398.             sql1 = "select * from Student1 where score =" + std::to_string(score);
    399.             mysql_query(&mysql, sql1.c_str());
    400.             res = mysql_store_result(&mysql);
    401.             row = mysql_fetch_row(res);
    402.             if (row == NULL)
    403.             {
    404.  
    405.                 std::cout << "不存在该分数的学生" << std::endl;
    406.                 break;
    407.             }
    408.             else
    409.                 sql2 = "delete  from Student1 where score= " + std::to_string(score);
    410.             std::cout << "删除的SQL语句如下：" << std::endl << sql2 << std::endl; // 检查语句
    411.             mysql_query(&mysql, sql2.c_str());
    412.             break;
    413.         }
    414.         default:
    415.             std::cout << "您输入的选项有误，请重新输入" << std::endl;
    416.     }
    417. }

    Students.cpp

    1. #include "Student.h"
    2. int Student::getId() {
    3.     return Id;
    4. }
    5. std::string Student::getName() {
    6.     return name;
    7. }
    8. int Student::getScore() {
    9.     return score;
    10. }
    11. void  Student::setId(int &id) {
    12.     Id=id;
    13. }
    14. void Student::setName(std::string& Name) {
    15.     name=Name;
    16. }
    17. void Student::setScore(int &Score) {
    18.     score=Score;
    19. }

    student2.txt

    1. 学号 姓名 成绩
    2. 21 张三 98
    3. 22 李四 45
    CMakeLists.txt

    1. cmake_minimum_required(VERSION 3.20)
    2. project(new_projects)
    3.  
    4. set(CMAKE_CXX_STANDARD 17)
    5.  
    6. include_directories(include)
    7.  
    8. include_directories(D:/develop/MySQL/MySQL\ Server\ 8.0/include)
    9. link_directories(D:/develop/MySQL/MySQL\ Server\ 8.0/lib)
    10. link_libraries(libmysql)
    11.  
    12. aux_source_directory(src DIR_SRCS)
    13.  
    14. add_executable(new_projects ${DIR_SRCS})
    AUTO_INCREMENT是主键，主键具有唯一性
  • 相关阅读:
    【牛客刷题】每日一练——Java语法的强化
    An动画优化之传统引导层动画
    Vue2：使用Vant UI实现网易云评论页上拉和下拉刷新
    Centos实现Mysql8.4安装及主主同步
    Linux--进程间通讯--FIFO（open打开）
    kafka是啥？虽然很难学，但是实验入门很简单
    pytorch nn.Embedding 读取gensim训练好的词/字向量（有例子）
    vue3中使用vue-i18n(ts中使用$t, vue3不用this)
    蓝桥杯实战应用【算法代码篇】-多分支递归：裴波那契序列（附Java、C++和R语言代码）
    SpringMVC：转发和重定向
  • 原文地址：https://blog.csdn.net/m0_61682542/article/details/136392859